Question

Sandard har Dha 60 000 18 16 == 10 $150 Not 0 50 50 1.000 Totul de 00 Profit Question 3: Sensitivity Analysis (15 Points) மேம்பாயனப்பாடா M&J Furniture Inc. is an office equipment company that produces two types of desks: standard and deluxe. Deluxe desks have oak tops, more expensive hardware, and required additional time for finishing and polishing. Standard desks require 80 square feet of pine and 10 hours of labor, while deluxe desks require 60 square feet of pine, 18 square feet of Numbes produced oak, and 16 hours of labor. For the next week, the company has 5,000 square feet of pine, 750 square feet of oak, and 400 hours of labor available. Standard desks net a profit of $150, while deluxe desks net a profit of $320. All desks can be sold to national chains shops. Instructions: Using the Excel Solver results in Table opposite to address the following questions: What are the Binding Constraints and the Optimum profit? 1. What are the slack values for Pine, Oak, and Labor? 2 3. Looking at the Shadow prices, how much would the objective Uud 1500 25 minutes on slide The Oak Labe Varios Reed Abowe Veve 190 SESEI SCS21 aber produced and bodde Now Find profit increase by if labour is increases by 50 hours? 4. What % of available Oak can be reduced without it affecting the current (given) optimum solution? 5. How much should the standard desk unit profit of $150 be increased by for Standard desks to be produced (1.e. become a positive number)? C4 Pemud 35.36 km 55:7 Lund 49

Answer 1

Using Excel Solver :-

SUM Xfx =SUMPRODUCT(B3:C3,B2:22) A B с E F H 1 Standard Deluxe 2 3 Net Profit 150 320 Total Profit SUMPRODUCT(B3:C3,B2:C2) 4 5 6 Constraints 7 Pine 80 60 0 5000 <= 8 Labor 10 16 0 400 <= 9 Oak 18 0 750 <= 10 11 C SUM Xfx =SUMPRODUCT(B7:07,B2:C2) A с D E F 1 Standard Deluxe 2 3 Net Profit 150 320 Total Profit 0 4 5 6 Constraints 7 Pine 80 60 -SUMPRODUCT(B7:07,02:02) 8 Labor 10 16 0 400 <= 9 Oak 18 0 750 <= 10

X Solver Parameters Set Objective: $F$3 To: Max Min Value of: 0 By Changing Variable Cells: $8$2:$C$2 * Subject to the Constraints: $D$7:$D$9 <= $E$7:$E$9 Add Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth Help Solve Close

D E F N Total Profit 8000 H12 A B 1 Standard Deluxe 2 0 25 3 Net Profit 150 320 4. 5 6 Constraints 7 Pine 80 60 8 Labor 10 16 9 Oak 18 10 1500 400 450 5000 <= 400 <= 750 <=

1.

Following is the Binding constraints

H37 А в с D E F G 14 Objective Cell (Max) 15 Cell Name Original Value Final Value 16 $F$3 Total Profit 0 8000 17 18 19 Variable Cells 20 Cell Name Original Value Final Value Integer 21 $B$2 Standard 0 O Contin 22 $C$2 Deluxe 0 25 Contin 23 24 25 Constraints 26 Cell Name Cell Value Formula Status Slack 27 $D$7 Pine 1500 $D$7<=$E$7 Not Binding 3500 28 $D$8 Labor 400 $D$8<=$E$8 Binding 0 29 $D$9 Oak 450 $D$9<=$E$9 Not Binding 300 30

The optimum profit is $8,000

2)

Following is the slack value

Constraints Final Shadow Constraint Allowable Allowable Cell Name Value Value Price R.H. Side Increase Decrease $D$7 Pine 1500 0 5000 1E+30 3500 $D$8 Labor 400 20 400 266.6666667 400 $D$9 Oak 450 0 750 1E+30 300

3)

If labor is increased by 50 then the profit is increased to $9,000

B2 fx 0 D E F Total Profit 9000 A А B с 1 Standard Deluxe 2 이 28.125 3 Net Profit 150 320 4 5 6 Constraints 7 Pine 80 60 8 Labor 10 16 9 Oak 18 10 1687.5 450 5000 <= 4501C 750 <= 506.25

4) Oak is utilised 506 out of 750. It means oak is used only 67%(506/750). Hence oak can still be reduced by 33%.

5) Producing a standard unit will be a loss for the company. Standard desk should reduce overall cost.