Cost Management 6th Edition by Edward Blocher,David Stout ,Paul Juras,Gary Cokins

Exercise 18

Profitability Analysis; Linear Programming (Appendix) Home Service Company offers monthly service plans to provide prepared meals that are delivered to customers' homes and need only be heated in a microwave or conventional oven. Home Service offers two monthly plans, premier cuisine and haute cuisine. The premier cuisine plan provides frozen meals that are delivered twice each month; the premier generates a contribution of $150 for each monthly service plan sold. The haute cuisine plan provides freshly prepared meals delivered on a daily basis and generates a contribution of $100 for each monthly plan sold. Home Service's strong reputation enables it to sell all meals that it can prepare.
Each meal goes through food preparation and cooking steps in the company's kitchens. After these steps, the premier cuisine meals are flash frozen. The time requirements per monthly meal plan and hours available per month follow:

For planning purposes, Home Service uses linear programming to determine the most profitable number of premier and haute cuisine meals to produce.
Required
1. Using the Solver function of Microsoft Excel, determine the most profitable product mix for Home Service given the existing constraints and contribution margins.
2. Generate and interpret the information contained in the "Sensitivity Report" associated with the solution in requirement 1.
3. Using the Solver function of Microsoft Excel, determine the most profitable product mix for Home Service given the existing contribution margins and all constraints except the preparation time constraint.