Quantitative Assignment- Excel
Mark3433
CopyofM2MACExcelAssignmentTemplateformac.xlsxP3-26 Model
| Manufacturing desks and chairs | |||
| Inputs | |||
| Desks | Chairs | ||
| Unit margins | |||
| Wood usage per unit | |||
| Decisions | |||
| Desks | Chairs | ||
| Units produced | |||
| Constraint on wood | |||
| Wood used | Wood available | ||
| Constraint on chairs | |||
| Chairs produced | Chairs required | ||
| Objective to maximize | |||
| Profit |
Problem 2.2
P3-26 Sensitivity Analysis
| Oneway analysis for Solver model in Model worksheet | Sensitivity of Units_produced_1 to Wood available | |||||||||
| Wood available (cell $D$14) values along side, output cell(s) along top | Data for chart | |||||||||
| Units_produced_1 | Units_produced_2 | Profit | Change in profit | 1 | Units_produced_1 | |||||
| 1000 | 0 | |||||||||
| 1100 | $0 | 0 | ||||||||
| 1200 | $0 | 0 | ||||||||
| 1300 | $0 | 0 | ||||||||
| 1400 | $0 | 0 | ||||||||
| 1500 | $0 | 0 | ||||||||
| 1600 | $0 | 0 | ||||||||
| 1700 | $0 | 0 | ||||||||
| 1800 | $0 | 0 | ||||||||
| 1900 | $0 | 0 | ||||||||
| 2000 | $0 | 0 | ||||||||
| 2100 | $0 | 0 | ||||||||
| 2200 | $0 | 0 | ||||||||
| 2300 | $0 | 0 | ||||||||
| 2400 | $0 | 0 | ||||||||
| 2500 | $0 | 0 | ||||||||
| 2600 | $0 | 0 | ||||||||
| 2700 | $0 | 0 | ||||||||
| 2800 | $0 | 0 | ||||||||
| 2900 | $0 | 0 | ||||||||
| 3000 | $0 | 0 |
1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 2200 2300 2400 2500 2600 2700 2800 2900 3000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Wood available ($D$14)
When you select an output from the dropdown list in cell $K$4, the chart will adapt to that output.
P3-34 Model
| River pollutants | ||||||
| Factory 1 | Factory 2 | Factory 3 | ||||
| Cost/ton | ||||||
| Factory 1 | Factory 2 | Factory 3 | Reduction | Required | ||
| P1 | ||||||
| P2 | ||||||
| Processed | ||||||
| Total cost |
Problem 2.14
P3-34 Sensitivity Analysis
| Oneway analysis for Solver model in Revised model worksheet | Sensitivity of Cost to Percent change in requirements | |||||||||
| Percent change in requirements (cell $J$7) values along side, output cell(s) along top | Data for chart | |||||||||
| Percentage | Processed_1 | Processed_2 | Processed_3 | Cost | 4 | Cost | ||||
| 10% | 0 | |||||||||
| 20% | 0 | |||||||||
| 30% | 0 | |||||||||
| 40% | 0 | |||||||||
| 50% | 0 | |||||||||
| 60% | 0 | |||||||||
| 70% | 0 | |||||||||
| 80% | 0 | |||||||||
| 90% | 0 | |||||||||
| 100% | 0 | |||||||||
| Differences (calculated manually) | ||||||||||
| 0.00 | 0.00 | 0.00 | 0.00 | |||||||
| 0.00 | 0.00 | 0.00 | 0.00 | |||||||
| 0.00 | 0.00 | 0.00 | 0.00 | |||||||
| 0.00 | 0.00 | 0.00 | 0.00 | |||||||
| 0.00 | 0.00 | 0.00 | 0.00 | |||||||
| 0.00 | 0.00 | 0.00 | 0.00 | |||||||
| 0.00 | 0.00 | 0.00 | 0.00 | |||||||
| 0.00 | 0.00 | 0.00 | 0.00 | |||||||
| 0.00 | 0.00 | 0.00 | 0.00 |
Sensitivity of Selected Output to Percent change in requirements
0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1 0 0 0 0 0 0 0 0 0 0Percent change in requirements ($J$7)
When you select an output from the dropdown list in cell $K$4, the chart will adapt to that output.
