Question

LP Graphing

To make stuffed toys, you need fake-fur fabric, filling, and the eyes, plus time on a sewing machine. You currently are making two animals: a spider and a giant mouse (they both use grey fake-fur). The spider uses 2 square feet of fake fur and the mouse uses 1 square foot. The spider uses ¾ lb. of filling and the mouse uses 1.2 lb. of filling. The spider has 8 eyes and the mouse has two. You have 300 square feet of grey fake fur and 300 lbs of stuffing, with new shipments (500 square feet and 250 pounds, respectively) of both expected, so your boss wants to at least use up all the old fur and stuffing on this production run. You have 2,400 eyes. You make a profit of $6.00 on the spiders and $1.00 on the mice. The sewing machines are available 300 hour per week, and the spider needs 30 minutes and the mouse 36 minutes. The formulation, including slack and surplus variables, is shown below:

Max Z = $6.00 S + $1.00 M
s.t. 2 S + 1 M + Su1 > 300 sq. feet of fur
.75 S + 1.2 M + Su2 > 300 lbs. of stuffing
8 S + 2 M + Sl2 < 2,400 eyes
.5 S + .6 M + Sl4 < 300 sewing machine hours
  S,   M    > 0 non-negativity constraint

1) Set up the problem in a spreadsheet and use Solver to the optimal solution. Be sure you get the Answer and Sensitivity reports.
2) Write a brief paragraph giving the solution. Truncate (do not round up) the decision variables to whole numbers, but report the rest of the values as given in the reports.
For the following questions, show the numbers from the reports that prove your answers.
3) How much of the new order of Fur and Stuffing will be used this week?
4) The sewing machines need to go down for maintenance this week. That will cost you 50 hours of machine time. Will this be a problem?
5) Do you need to place an order for any resources?
6) Your boss is thinking the spiders might be over-priced. If the price drops by $1.50, decreasing profits an equal amount, will the production levels change?
7) One box of the eyes (50 eyes per box) are broken. Will this cause a change in production?
8) A new competitor is entering your market. You will have to decrease your price (and profit) by $0.50 for both products. Will this cause a change in production?
9) You could order more eyes to be delivered overnight for an increase in cost of $0.05 per eye. Is this worthwhile, and if so, how many should you order?
10) Your boss has decided to increase the price (and profit) of the Mouse by $0.25 and to sell one sewing machine, decreasing the hours available to 260. Will this change the solution?

Answer 1

1)

To solve the LP in excel spreadsheet, first install the solver add in.

Now the LP is solved by solver by following these steps,

Step 1: Write the variables of LP with initial value zero. The screenshot is shown below,

Step 2: Write the objective function. The screenshot is shown below,

Step 3: Write the constraints.

Constraint 1: 2 S + 1 M >= 300.  The screenshot is shown below,

Constraint 2: .75 S + 1.2 M >= 300. The screenshot is shown below,

Constraint 3: 8 S + 2 M <= 2,400 . The screenshot is shown below,

Constraint 4: .5 S + .6 M <= 300. The screenshot is shown below,

Constraint 5: S >=0. The screenshot is shown below,

Constraint 6: M >= 0. The screenshot is shown below,

Step 4: DATA > Solver > Ok.

Step 5: Set Objective: Objective function. Select To: Max The screenshot is shown below,

Step 5: By Changing Variables Cells: Select Variable value. The screenshot is shown below,

Step 6: Subject to the Constraints > click on Add then Add each constraints. The screenshot is shown below,

Step 7: Tick Make Unconstrained Variables Non-Negative and Select a Solving Method: Simplex LP. The screenshot is shown below,

Step 8: Select Reports : Answer, Sensitivity, Limits > OK. The screenshot is shown below,

The result is obtained. The screenshot is shown below,

The Answer Report,

The sensitivity report

The Limit report,

2)

Variable
S	281.4814815
M	74.07407407
Objective	1762.962963
1	637.037037	>=	300
2	300	>=	300
3	2400	<=	2400
4	185.1851852	<=	300
5	281.4814815	>=	0
6	74.07407407	>=	0

3)

From the result above,

Total fur used = 637 (available = 300, new_order = 500)

total used from new order = 637 = 300 = 137

Total lbs of stuffing used = 300 (available = 300, new_order = 250)

total used from new order = 300 - 300 = 0

4)

From the result summary,

Total machine hours available = 300 - 185.185 = 114.814

Hence no cost will incur due to maintenace.

5)

Total fur used = 637 which is less than 300(old)+500(new)

and total stuffing used = 300 which is less than 300(old)+250(new)

Hence no order need to be placed.

6)

From the sensitivity report, the allowable decrease in objective coefficient of fur is $2 which is greater than $1.50 hence there will be no effect on objective function or production level.

7)

From the sensitivity report all the eyes has bee used and the shadow price = 0.796296 hence due to loss of 50 eyes, total 50*0.796296 = $ 39.8148 will be reduced from objective value.

8)

There will be no effect on optimal value due to decrease in price beacause the decerease in price are within allowable range.

For better understanding change the values in LP and use the excel solver for each problem and see the effect on result.