Microsoft Excel 8.0 Answer Report
Worksheet: [wid-supp.xls]Supplementary
Report Created: 7/1/2002 12:37:11 PM
Target Cell (Max)
Cell Name Original Value Final Value
$I$10 Contribution to Profit and Overhead Total Dollars $ 8,000 $ 9,025
Adjustable Cells
Cell Name Original Value Final Value
$B$2 Number to Produce Widgets 20 40.00000001
$C$2 Number to Produce Gadgets 80 70
$D$5 Assembly Hours Used 720 720
$D$6 Painting Hours Used 120 150
$D$7 Assembly Overtime Used 0 0
$D$8 Painting Overtime Used 0 30
Constraints
Cell Name Cell Value Formula Status Slack
$E$5 Assembly Hours Total Hours Used 720 $E$5<=$G$5 Binding 0
$E$6 Painting Hours Total Hours Used 150 $E$6<=$G$6 Binding 0
$D$5 Assembly Hours Used 720 $D$5=$E$5 Binding 0
$D$6 Painting Hours Used 150 $D$6=$E$6 Not Binding 0
$D$9 Total Overtime Used 30 $D$9<=$G$9 Binding 0
Microsoft Excel 8.0 Sensitivity Report
Worksheet: [wid-supp.xls]Supplementary
Report Created: 7/1/2002 12:37:12 PM
Adjustable Cells
Final Reduced Objective Allowable
Cell Name Value Cost Coefficient Increase
$B$2 Number to Produce Widgets 40.00000001 0 198 50
$C$2 Number to Produce Gadgets 70 0 226 77.00000004
$D$5 Assembly Hours Used 720 0 -17 38.5000001
$D$6 Painting Hours Used 150 0 -15 1E+30
$D$7 Assembly Overtime Used 0 -38.5 -8.499999967 38.5
$D$8 Painting Overtime Used 30 0 -7.499999992 1E+30
Constraints
Final Shadow Constraint Allowable
Cell Name Value Price R.H. Side Increase
$E$5 Assembly Hours Total Hours Used 720 4.166666667 0 480.0000001
$E$6 Painting Hours Total Hours Used 150 41.66666667 0 210
$D$5 Assembly Hours Used 720 -17 0 1E+30
$D$6 Painting Hours Used 150 -15 0 1E+30
$D$9 Total Overtime Used 30 34.16666669 30 209.9999999
Allowable
Decrease
51.25000001
25
4.166666667
34.16666668
1E+30
34.16666669
Allowable
Decrease
420
60.00000001
720
150
30
Total Straight
Hours Hours Hours Hourly Total
Widgets Gadgets Used Used Avail Available Cost Dollars
Number to Produce 40 70
Selling Price $ 248 $ 266 $ 28,540
Raw Materials Cost $ 50 $ 40 $ 4,800
Assembly Hours 4 8 720 720 720 720 $ 17 $ 12,240
Painting Hours 2 1 150 150 120 150 $ 15 $ 2,250
Assembly Overtime 0 $ 8.50 $ -
Painting Overtime 30 $ 7.50 $ 225
Total Overtime 30 30
Contribution to Profit and Overhead $ 9,025
Cost per unit (straight) $ 148 $ 191
Contribution (straight) $ 100 $ 75