Spreadsheet Modeling and Decision Analysis (
Spreadsheet Modeling and Decision Analysis
Sensitivity Report A sensitivity report of the analysis described above was created and can be found in Appendix E. The solution is not degenerate because there are no zero values in the allowable increase or allowable decrease columns of the constraints in the Sensitivity Report. All of the constraints affect the optimal solution. The solution is unique because there are no zero values in the allowable increase or allowable decrease columns in the decision variables section of the Sensitivity Report. This shows that there are no other combinations of production levels that will optimize this solution.
Decrease the Price of Whole If MNC were to decrease the price of the Whole product by $0.25, the optimal solution would change. This decrease in price would cause Whole’s contribution margin to fall to $0.79 and the optimal profit to fall to $439.76, a $100.00 difference. These calculations can be found in Appendix G. The production levels, however, would not change. This is because, as can be seen in the Sensitivity Report, there is an allowable increase of $0.36 and an allowable decrease of $0.31 in the price of Whole. Because $0.25 falls within this range, this change in price would not affect the production levels of the optimal solution.
Appendix A
1
Appendix B
2
Appendix C Constraints X1= Whole Product X2= Cluster Product X3= Crunch Product X4= Roasted Product Machine Time Limit Constraints Hulling: (1*X1)+(1*X2)+(1*X3)+(1*X4) ≤ 3600 minutes Roasting: (2*X1)+(1.5*X2)+(1*X3)+(1.7*X4) ≤ 3600 minutes Coating: (1*X1)+(0.7*X2)+(0.2*X3)+(0.2*X4) ≤ 3600 minutes Packaging: (2.5*X1)+(1.6*X2)+(1.25*X3)+(1*X4) ≤ 3600 minutes Product Availability Constraints Macadamia: (0.6*X1)+(0.4*X2)+(0.2*X3)+(1*X4) ≤ 1100 pounds Chocolate: (0.4*X1)+(0.6*X2)+(0.8*X3)+(0*X4) ≤ 800 minutes Needed Production X1 ≥ 1000 400 ≤ X2 ≤ 500 0 ≤ X3 ≤ 150 0 ≤ X4 ≤ 200 Profit Maximization (0.19*X1)+(1.04*X2)+(1.15*X3)+(1.33*X4)
3
Sensitivity Report A sensitivity report of the analysis described above was created and can be found in Appendix E. The solution is not degenerate because there are no zero values in the allowable increase or allowable decrease columns of the constraints in the Sensitivity Report. All of the constraints affect the optimal solution. The solution is unique because there are no zero values in the allowable increase or allowable decrease columns in the decision variables section of the Sensitivity Report. This shows that there are no other combinations of production levels that will optimize this solution.
Decrease the Price of Whole If MNC were to decrease the price of the Whole product by $0.25, the optimal solution would change. This decrease in price would cause Whole’s contribution margin to fall to $0.79 and the optimal profit to fall to $439.76, a $100.00 difference. These calculations can be found in Appendix G. The production levels, however, would not change. This is because, as can be seen in the Sensitivity Report, there is an allowable increase of $0.36 and an allowable decrease of $0.31 in the price of Whole. Because $0.25 falls within this range, this change in price would not affect the production levels of the optimal solution.
Appendix A
1
Appendix B
2
Appendix C Constraints X1= Whole Product X2= Cluster Product X3= Crunch Product X4= Roasted Product Machine Time Limit Constraints Hulling: (1*X1)+(1*X2)+(1*X3)+(1*X4) ≤ 3600 minutes Roasting: (2*X1)+(1.5*X2)+(1*X3)+(1.7*X4) ≤ 3600 minutes Coating: (1*X1)+(0.7*X2)+(0.2*X3)+(0.2*X4) ≤ 3600 minutes Packaging: (2.5*X1)+(1.6*X2)+(1.25*X3)+(1*X4) ≤ 3600 minutes Product Availability Constraints Macadamia: (0.6*X1)+(0.4*X2)+(0.2*X3)+(1*X4) ≤ 1100 pounds Chocolate: (0.4*X1)+(0.6*X2)+(0.8*X3)+(0*X4) ≤ 800 minutes Needed Production X1 ≥ 1000 400 ≤ X2 ≤ 500 0 ≤ X3 ≤ 150 0 ≤ X4 ≤ 200 Profit Maximization (0.19*X1)+(1.04*X2)+(1.15*X3)+(1.33*X4)
3