Question

In: Statistics and Probability

Suppose a small company is in the business of making golf bags to be sold at...

  1. Suppose a small company is in the business of making golf bags to be sold at a local golf course where state championships are often held. They make 2 types of golf bags, a standard bag and a deluxe bag. The table below gives the information for limitations of materials and labor hours, and also for the profit for each type of bag. The materials and labor hours are the amount available per week, and the profit is the profit per week as well.

Units are per bag

Standard Leather (square feet)

Premium Leather (square feet)

Cutting and Sewing Labor hours

Finishing Labor Hours

Profit

Standard

7

1

3

1

25

Deluxe

0

9

4

2

65

Total available

210

180

120

40

  1. Set up the problem in an excel worksheet, using the sandwich problem done in class as a template. The only difference will be that you have an extra constraint. Each constraint has its own row. Label this worksheet Problem 1
  1. Use the solver window to solve the problem. When solver tells you that you have an answer, click on the answer and sensitivity report options.

  1. Answer the following questions ON YOUR SENSITIVY REPORT WORKSHEET FOR PROBLEM 1.

a. If the profit for Standard bags increases by $5/bag, does the optimal solution change?

Explain why or why not.

b. If the optimal solution does NOT change, will the value of the objective function at

the optimal solution change? If it does, calculate the new profit. If it does not, explain why not.

c. If the profit for Deluxe bags decreases by $5/bag, does the optimal solution change?

Explain why or why not. (Note: this change is made WITHOUT changing the profit for

Standard bags.)

d. If the optimal solution does NOT change, will the value of the objective function at

the optimal solution change? If it does, calculate the new profit. If it does not, explain why not.

e. If the profit for Standard bags increases by $5/bag and the profit for Deluxe bags

decreases by $5/bag, does the optimal solution change? (Hint: Use the 100% Rule)

f. If the optimal solution does NOT change, will the value of the objective function at

the optimal solution change? If it does, calculate the new profit. If it does not, explain why not.

  1. Suppose a small company is in the business of making golf bags to be sold at a local golf course where state championships are often held. They make 2 types of golf bags, a standard bag and a deluxe bag. The table below gives the information for limitations of materials and labor hours, and also for the profit for each type of bag. The materials and labor hours are the amount available per week, and the profit is the profit per week as well.

Units are per bag

Standard Leather (square feet)

Premium Leather (square feet)

Cutting and Sewing Labor hours

Finishing Labor Hours

Profit

Standard

7

1

3

1

25

Deluxe

0

9

4

2

65

Total available

210

180

120

40

  1. Set up the problem in an excel worksheet, using the sandwich problem done in class as a template. The only difference will be that you have an extra constraint. Each constraint has its own row. Label this worksheet Problem 1
  1. Use the solver window to solve the problem. When solver tells you that you have an answer, click on the answer and sensitivity report options.

  1. Answer the following questions ON YOUR SENSITIVY REPORT WORKSHEET FOR PROBLEM 1.

a. If the profit for Standard bags increases by $5/bag, does the optimal solution change?

Explain why or why not.

b. If the optimal solution does NOT change, will the value of the objective function at

the optimal solution change? If it does, calculate the new profit. If it does not, explain why not.

c. If the profit for Deluxe bags decreases by $5/bag, does the optimal solution change?

Explain why or why not. (Note: this change is made WITHOUT changing the profit for

Standard bags.)

d. If the optimal solution does NOT change, will the value of the objective function at

the optimal solution change? If it does, calculate the new profit. If it does not, explain why not.

e. If the profit for Standard bags increases by $5/bag and the profit for Deluxe bags

decreases by $5/bag, does the optimal solution change? (Hint: Use the 100% Rule)

f. If the optimal solution does NOT change, will the value of the objective function at

the optimal solution change? If it does, calculate the new profit. If it does not, explain why not.

Solutions

Expert Solution

variables x1 x2 Description of the Problem

coefficient 30 5 x1 No. of standard bags to be produced.

profit (Z) 1200 x2 No. of delux bags to be produced.

The objective is to maximize profit.

constraints Objective function

1 210 <= 210 Max Z= 25 * x1 + 65 * x2 As the per unit profit on standard and delux bags is 25 and 65 respectively.

2 75 <= 180

3 110 <= 120 The above objective function is to be maximized subject to the following constraints.

4 40 <= 40 7x1<=210 (availability of standard leather)

For the given problem the optimal profit of $1300 can be earned by the producer by manufacturing 20 deluxe bags only. x1+9x2<=180 (availability of premium leather)

3x1+4x2<=120 (availability of cutting and sewing labour hours)

x1+2x2<=40 (availability of finishing labour hours)

Microsoft Excel 15.0 Sensitivity Report

Worksheet: [Book1]Sheet1

Report Created: 3/5/2019 1:00:13 PM

Variable Cells

Final Reduced Objective Allowable Allowable

Cell Name Value Cost Coefficient Increase Decrease

$B$2 coefficient x1 0 0 25 7.5 17.77777778

$C$2 coefficient x2 20 0 65 160 15

1 a) Since the allowbale increase in x1 is 7.5 as per the sensitivity report, the increase of $5 in the profit due to x1 will not change the optimal solution.

1 b) The value of the objective function does not change as the coefficient of x1 is 0 in the optimal solution.

1 c) Since the allowbale decrease in x2 is 715 as per the sensitivity report, the decrease of $5 in the profit due to x2 will not change the optimal solution.

1 d) The value of the objective function does change as the coefficient of x2 is now changed to 60 from 65. The new objective function value is $1200.

1 e) The optimal solution will change as both the coefficients of x1 and x2 have been changed simultaneously. The new profit of $1200 can be earned by producing 30 standard bags and 5 deluxe bags

1 f) The value of the optimal function changes and the new objective function value is $1200.


Related Solutions

Suppose a small company is in the business of making golf bags to be sold at...
Suppose a small company is in the business of making golf bags to be sold at a local golf course where state championships are often held. They make 2 types of golf bags, a standard bag and a deluxe bag. The table below gives the information for limitations of materials and labor hours, and also for the profit for each type of bag. The materials and labor hours are the amount available per week, and the profit is the profit...
Par Inc. is a small manufacturer of golf equipment. It produces two types of golf bags:...
Par Inc. is a small manufacturer of golf equipment. It produces two types of golf bags: Standard and Deluxe. Each bag type requires the following operations (and production times) to produce one unit: Production Time (hours) Time Available Operation Standard Deluxe (hours/month) Cutting and Dyeing 0.7 1 630 Sewing 0.5 5/6 600 Finishing 1 2/3 708 Inspection 0.1 0.25 135 Time available refers to the production capacity for each of the above operations. For example, 630 total hours a month...
Medo Company manufactures bags; it is a small business that Medo developed while in college. He...
Medo Company manufactures bags; it is a small business that Medo developed while in college. He began making bags for his neighbors. As demand grew, he opened his first store; the bags made by medo was very popular, and his business grew and expanded. As customers' demands increased, Medo hired some workers and began to manage the operation. As a new junior accountant at Medo Company. Your boss, the Controller, has given you the latest information for the operation. The...
Medo Company manufactures bags; it is a small business that Medo developed while in college. He...
Medo Company manufactures bags; it is a small business that Medo developed while in college. He began making bags for his neighbors. As demand grew, he opened his first store; the bags made by medo was very popular, and his business grew and expanded. As customers&#39; demands increased, Medo hired some workers and began to manage the operation. As a new junior accountant at Medo Company. Your boss, the Controller, has given you the latest information for the operation. The...
Galaxy Manufacturing Company makes golf balls to be distributed and sold at local golf clubs. The...
Galaxy Manufacturing Company makes golf balls to be distributed and sold at local golf clubs. The golf balls have special features that increase distance and speed at the driving range. The company uses a process costing system to accumulate product costs as the golf balls move through the two stages of production: molding the rubber into cases and stamping. All materials are added at the beginning of both molding and stamping processes, while conversion costs are applied evenly throughout the...
EZ Carry Corp. is the maker of high quality golf bags. The company currently has three...
EZ Carry Corp. is the maker of high quality golf bags. The company currently has three different lines of bags, which it sells to sporting goods stores and golf shops throughout the world. EZ Carry sells a constant mix of 4 small bags for each medium-sized bag and 5 medium bags for each large-sized bag. Total fixed costs for the year are expected to be $2,027,562. (Note: round all decimals to three places; round final answers up to nearest whole...
XYZ is the maker of high quality golf bags. The company currently has three different lines...
XYZ is the maker of high quality golf bags. The company currently has three different lines of bags, which it sells to sporting goods stores and golf shops throughout the world. XYZ sells a constant mix of 1 medium-sized bags for 2 small bag and 4 medium bags for 3 large-sized bag. Total fixed costs for the year are expected to be 2027562 Note: round all decimals to three places in the computation Small Medium Large Selling price per bag...
QUESTION 1 : Fertilizer is sold in bags. Weights of fertilizer in bags are normally distributed....
QUESTION 1 : Fertilizer is sold in bags. Weights of fertilizer in bags are normally distributed. The actual mean weight of a bag is found to be 20 kg. 27 bags were randomly selected, piled on a pallet and weighted. Those 27 bags had a mean of 20.87 kg and a standard deviation of 0.34251 kg. If the variance of weight is less than 0.1225 no adjustment is made. Otherwise, the machine is adjusted. Calculate a 99% confidence interval on...
The Bag Corporation is a distributor of high end golf bags. In the past, The Bag...
The Bag Corporation is a distributor of high end golf bags. In the past, The Bag Corporation produced and sold the golf bags. Over time, they realized it was cheaper to buy the golf bags fully assembled and resell them to the public, therefore eliminating their need for a manufacturing plant. The Bag Corporation pays $60 per golf bag and sells them for $180. Prepare a 2021 (January – December) Revenue Budget (for units and sales dollars) for The Bag...
1. A company that makes organic fertilizer has supplied the following data: Bags produced and sold...
1. A company that makes organic fertilizer has supplied the following data: Bags produced and sold 240,000 Sales revenue $1,896,000 Variable manufacturing expense $804,000 Fixed manufacturing expense $520,000 Variable selling and administrative expense $180,000 Fixed selling and administrative expense $270,000 Net operating income $122,000 The company's margin of safety in units is closest to: Select one: a. 140,000 units b. 202,238 units c. 125,714 units d. 32,105 units 2. Davey Corporation is preparing its Manufacturing Overhead Budget for the fourth...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT