Question

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:

	Preparation	Cooking	Freezing
Hours Required -
Premier cuisine	3	2	1
Haute cuisine	1	3	0
Hours available	80	120

Review the Variable Cells Table in your Sensitivity Report. What is the highest per-unit Contribution Margin for the Haute Cusine product at which the optimum solution holds?

Answer 1

Solution:-

Given Data			Preparation	Cooking	Freezing
		Hours required:
		Premier cuisine	3	2	1
		Haute cuisine	1	3	0
		Hours available/month	80	120	45

1.

Solve for all three constraints: the solution is 17 units of Premier and 29 units of Haute, as shown in Exhibit 11-48C, cells B5 and B6. The Solver set up for this solution is shown in Exhibit 11-48A. The completed dialog box ("Solver Parameters") is presented in Exhibit 11-48B.

Exhibit 11-48A: Problem Set Up

Solution to 11-455) - Microsoft Excel File Home Insert Page Layout Show Det Formulas Data Connections Properties Refresh All Connections Data Analysis Solver 1 Sort Group Ungroup Subtotal Text to Remowe Data Consolidate What- Advanced Columns Duplicates Validation Analysis Sort & Filter Data Tools Outline from From from From Other Access Web Test Sources Connections Get External Data HII so A B Home Cooking Company c D E F H K M N 0 4 Product 5 Premier Cuisine 6 Haute Cuisine 7 Volume CM per unit 0 $150 0 $100 Resource Requirements (per unit) Prep Cook Freeze 3 2 1 1 3 0 Total CM $0 $0 $0 Capacity Usage Prep Cook Freeze 0 0 0 0 0 0 0.00 0.00 0.00 Sheet + H Sheet1 Sheet2 Ready 100N

Formulas for total contribution margin must be entered into cells G5 and G6 (cell G7 contains the sum of the individual product contribution margins). As well, formulas for capacity usage must be entered into cells H5:J6. A separate formula for the use of each resource is entered in cells H7:H9.

Exhibit 11-48B: Solver dialog window ("Solver Parameters")

Solver Parameters Х Th Set objective: $G$7 To: Max OMO O Value of: 0 By Changing Variable Colls: $8$5:$B$6 Add Subject to the Constraints: $8$50 $8$60 $H$7 80 $1$7 120 $J$7 ( 45 Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for Inear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

Exhibit 11-48C: Optimum Solution

U File Home Data View A Σ AutoSun MO Clear Sort & Find & Filter Select Editing K L Solution to 11-45 (6) - Microsoft Excel Insert Page Layout Formulas Review % Cut Calibri A Wrap Text General Copy Paste BIU A E Merge & Center $ %. Conditional Format Cell Insert Delete Format Format Painter Formatting as Table Styles Clipboard Font Alignment Number Styles Cells G13 & А B С D E F G H 1 Home Cooking Company 2 Resource Requirements 3 (per unit) Capacity Usage 4 Product Volume CM per unit Prep Cook Freeze Total CM Prep Cook Freeze 5 Premier Cuisine 17 $150 3 2 1 $2,571 51 34 17 6 Haute Cuisine 29 $100 1 3 0 $2,857 29 86 0 7 $5.429 80 120 17

2. Sensitivity Report

E F G T A B С D 1 Microsoft Excel 14.0 Sensitivity Report 2 Worksheet: [Solution to 11-45 (6e).xlsx]Sheet1 3 Report Created: 7/9/2011 11:34:07 AM 4 5 6 Variable Cells 7 Final 8 Cell Name Value 9 $B$5 Premier Cuisine Volume 17.14 10 $B$6 Haute Cuisine Volume 28.57 11 12 Constraints 13 Final 14 Cell Name Value 15 $H$7 Prep 80.00 16 $I$7 Cook 120.00 17 $J$7 Freeze 17.14 Reduced Cost 0 0 Objective Allowable Allowable Coefficient Increase Decrease 150 150 83.33 100 125 50.00 Shadow Price 35.71 21.43 0.00 Constraint Allowable Allowable R.H. Side Increase Decrease 80 65 40.00 120 120 66.67 1E+30 27.86 45

Notes--Sensitivity Report:

1. Reduced costs: these pertain to the two decision variables (Premier and Haute). If all such variables are in the optimum solution (as in the present case), then these values will be zero. Technically, the "reduced cost" for a variable not in the optimum solution represents the amount by which the per-unit contribution margin would have to change in order for the variable to enter the optimal solution.

2. For each decision variable, the "Allowable Increase" and "Allowable Decrease" provide a range over the objective function coefficents (here, per-unit contribution margins) over which the optimum solution holds. For example, the current optimum solution holds as long as the contribution margin per unit is within the range $66.26 - $300.00.   

3. For each constraint (Prep, Cook, and Freeze) the Final Value represents the amount of the resource used under the optimum solution. If you look at cell H7 and I7 you see that the entire time allotment for Prep and Cook are used up under the optimum solution. As such, each of these two constraints has a "shadow price," which represents the maximum amount Home Service Company would be willing to pay for one additional unit (here, hour) of each constraint. You will note that under the optimum solution the entire amount of Freeze hours is not used up. Thus, by definition the "shadow price" for this constraint must be zero.      

4. Finally, for each constraint there is an "Allowable Increase" and an "Allowable Decrease" show the range, around the value of each constraint, over which the indicated "shadow prices" hold.

3. Results after removing the preparation time constraint: the constraint to remove from the "Solver Parameters" dialog box is" H7 <= 80. The solution is 45 units of Premier and 10 units of Haute, as shown in Exhibit 11-48D, cells B5 and B6."

Exhibit 11-48D: Optimal Solution after removal of Preparation Time Constraint

с D F G H B 1 Home Cooking Company 2 3 4 Product 5 Premier Cuisine 6 Haute Cuisine Volume 45 10 CM per unit $150 $100 Resource Requirements (per unit) Capacity Usage Prep Cook Freeze Total CM Prep Cook Freeze 3 2 1 $6,750 135 90 45 1 3 0 $1,000 10 30 0 $7.750 145 120 45 7