Question

A furniture factory produces two types of desks. Type I and type II, in the cutting and assembly departments. the number of hours available in each department is: in department A 624 hours and in department B it is a third (1/3) more hours than in department A. To manufacture a type I desk unit it takes 3 hours in department A and 8 hours in department B. To manufacture a type II desk unit, 10 hours are required in department A and 5 hours in department B. If the utility for each type II desktop is $ 500,000.00 and the utility of the desk Type I is 20% less than Type II. How many units of each type of desk should be manufactured to MAXIMIZE utility? Find the optimal solution and the optimizational intervals c1 and c2(approximate solutions are not accepted)

Answer 1

HI,

Please find the answer as below. If you like the answer, please up vote. It will encourage me to contribute more.

Answer

I have used excel solver to solve the problem. I have attached the screenshot of Input Data, Formulas and Constraints used along with the optimal solution and sensitivity report.

Need to produce 78 Type I desks and 39 Type II desks to achieve maximum utlity of $ 5,07,00,000.

1. Snapshot of Input Data

2. Snapshot of Formulas Used

3. Snapshot of Constraints Used

4. Snapshot of Optimal Solution

5. Snapshot of Sensitivity report and Limit Report

To get the sensitivity report, we need to remove the integer constraint.

For Coefficients of Variables in Optimization formulae

Feasibility interval for R1 is - (4,00,000-250000) to ( 400000+400000) which is 150000 to 800000.

Feasibility interval for R2 is - (5,00,000-250000) to ( 400000+833333.33) which is 250000 to 1233333.33.

For Constraints of Type I and Type II machine hours

Feasibility interval for C1 is - (624-312) to ( 624 + 1040 ) which is 312 to 1664.

Feasibility interval for C2 is - (832-520) to ( 832+832) which is 312 to 1664.

Limit Report

Type I Desks Department Al Department B Utility $ 4,00,000 Type II Desks Total Hours 10 624 832 $ 5,00,000 Answer Max. Utility Type I Desks Type II Desks Constraints Hours Department A Department B Used 0 0 Available 624 832

Type I Desks Type II Desks Total Hours 624 Department A 3 Department B Utility =0.8*E6 =(1+1/3)*F4 500000 Answer Max. Utility =SUMPRODUCT(D6:56, D13:513) Type I Desks Type II Desks Constraints Hours Used Department A =SUMPRODUCT(D4:E4, $D$13:$E$13) Department B =SUMPRODUCT(D5:E5, $D$13:$E$13) Available =F4 =F5

Solver Parameters Get External Data Show Queries From Table New Query - Lo Recent Sources Get & Transform Connections E Properties Refresh All Edit Links Connections ZlSort Filter Sort & F Set Objective: SES10 E10 - x V for To: Max Min O Value of: By Changing Variable Cells: SD$13:SE$13 Subject to the Constraints: $D$13:SES13 = integer SD$17:SD$18 <= SF517:$F$18 Add Total Hours 624 Change Department A Department B Utility Type I Desks 3 8 $ 4,00,000 Type II Desks 10 5 $ 5,00,000 832 Delete Reset All Answer Load/Save Max. Utility $ 5,07,00,000 Type I Desks 78 Type II Desks 39 Make Unconstrained variables Non-Negative Select a Solving Simplex LP Method: v Options Constraints Hours Department A Department B Used 624 Available 624 832 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. 819 Help | Solve Close

G H 10 Department A Department B Utility Type I Desks Type II Desks 3 8 5 $ 4,00,000 $ 5,00,000 Total Hours 624 832 Answer Max. Utility $ 5,07,00,000 Type II Desks Type I Desks 78 Constraints Hours Department A Department B Used 624 819 Available 624 832

E F G H I A B C D 1 Microsoft Excel 16.0 Sensitivity Report 2 Worksheet: [Apr_Plan.xlsx]Sheet20 3 Report Created: 24-04-2020 01:34:40 4 6 Variable Cells Final Value 80 38.4 Cell Name $D$13 Type I Desks $E$13 Type II Desks Reduced Cost 0 0 Objective Allowable Allowable Coefficient Increase Decrease 400000 400000 250000 500000 833333.3333250000 9 10 12 Constraints 13 14 Cell Name 15 $D$17 Department A Used 16 $D$18 Department B Used Final Shadow Value Price 624 30769.23077 832 38461.53846 Constraint Allowable Allowable R.H. Side Increase Decrease 624 1040 312 832 832 520

D E F G H I J K AB 1 Microsoft Excel 16.0 Limits Report 2 Worksheet: [Apr_Plan.xlsx]Sheet20 3 Report Created: 24-04-2020 01:39:36 Objective Cell Name Value $E$10 Max. Utility Type II Desks $5,12,00,000 Value 13 Variable Cell Name $D$13 Type I Desks $E$13 Type II Desks Lower Objective Limit Result 019200000 0 32000000 Upper Objective Limit Result 80 51200000 38.4 51200000 80 14 38.4