Question 2 [14 Marks]

The problem in Question 1 is modified due the expansion plan of the company as follows. A manufacturer produces three models of heating radiators: Model A, Model B, Model C. The profit per unit are RO 57, RO 89, and RO 150, respectively. The production requirements per unit are as follows.

Material Material Manufacturing (M1) (M2) Time (hours) Model A 2 3 6 Model B 1 2 3 Model C 3 3 7

The company has 100 units of Material (M1) available, 270 of Material (M2), 1800 hours of manufacturing time (M3) available. The problem is to decide how many units of Model A, Model B, and Model C should the company produce in order to maximise profit.

The above problem is solved by developing a spreadsheet model and using Solver. The computer solution Excel Sensitivity Report is produced as shown in Table 1 below.

Table 1: Microsoft Excel 16.0 Sensitivity Report Variable Cells Final Reduced Objective Allowable Allowable Cell Name Value Cost Coefficient Increase Decrease $C$7 A 0 -2.33 57 2.33 1E+30 $D$7 B 50 0 89 1E+30 3.5 $E$7 C 0 -28 150 28 1E+30 Constraints Final Shadow Constraint Allowable Allowable Cell Name Value Price R.H. Side Increase Decrease $F$4 M1 150 29.67 150 255 150 $F$5 M2 100 0 270 1E+30 170 $F$6 M3 100 0 1800 1E+30 1700

Use the information in Table 1 above answer the following questions from a-f