Question

Austin Enterprises makes and sells two types of phones. Management is trying to determine the most profitable mix. Sales prices, demand and use of manufacturing inputs follows:

Sales Price:

Standard	Premium
$400	$540

Input requirements per unit:

Direct Material	$20	$40
Direct labor (hours)	1.2	2.0
Machine Hours	1.5	1.0

Costs:

Variable Costs

Direct Labor (cost per hour)	25
Factory Overhead(per MH)	3
Marketing (percent of sales price)	10%

Fixed Costs:

Manufacturing	$230,000
Marketing	80,000
Administration	120,000

The company faces the following constraints

Minimum Production Demand (Units)	1,000	1,000
Machine Hours (MH)	5,000
Direct Labor Hours	4,000

What Sales mix will optimize profits?

What is the profit at this level of sales?

Use Solver for Constraints. Please show each step

Answer 1

The model is shown below

x fc B =121-122 C D Standard Premium 400 540 E F I Total J K L M N O P Q G H Standard Premium 0 Ε Mix 40 20 1.2 1.5 2 1 0 25 3 10% 2 Sales price 3 Requirement 4. Direct material 5 Direct labor 6 Machine hours 7 Variable cost 8 Direct labor 9 Factory overhead 10 Marekting 11 Fixed cost 12 Manufacturing 13 Marketing 14 Administration 15 16 Constraint 17 Demand 18 Machine hour 19 Direct labor hours 230000 80000 120000 230000 80000 120000 1000 1000 5000 4000 0 Revenue Cost Profit 0 430000 -430000

The formulas shown below

123 : x fc =121-122 A H Premium Standard Premium Standard Total 400 540 Mix 10 20 =C4*G$2 =C5*G$2 =C6*G$2 =D4*H$2 =D5*H$2 =D6*H$2 =H4+G4 =H5+G5 =H6+G6 2 Sales price 3 Requirement 4 Direct material 5 Direct labor 6 Machine hours 7 Variable cost 8 Direct labor 9 Factory overhead 10 Marekting 11 Fixed cost 12 Manufacturing 13 Marketing 14 Administration =15*B8 =16*3 =0.1*SUMPRODUCT(G2:H2, C2:02) 0.1 230000 80000 120000 =B12 =B13 =B14 15 1000 1000 16 Constraint 17 Demand 18 Machine hour 19 Direct labor hours 5000 4000 =G2*C2 =H2*D2 Revenue Cost Profit =H21+G21 =18+19+I10+112+13+14+14 = 21-122

The solver parameters shown below

123 fr =121-122 Solver Parameters E F D Standard Premium 400 540 G H Standard Premium 0 0 Total Set Objective: SI523 Mix To: O Max Min O Value of: 40 20 1.2 1.5 2 By Changing Variable Cells: $G$2:$H$2 1 2 Sales price 3 Requirement 4 Direct material 5 Direct labor 6 Machine hours 7 Variable cost 8 Direct labor 9 Factory overhead 10 Marekting 11 Fixed cost 12 Manufacturing 13 Marketing 14 Administration 25 Subject to the Constraints: SG$2:$H$2 = integer SG$2:$H$2 >= $C$17:$D$17 SI$5:SI56 <= SBS18:SBS19 Add Change 10% Delete 230000 80000 120000 230000 80000 Reset All 120000 Load/Save 15 ✓ Make Unconstrained variables Non-Negative 1000 1000 Select a Solving Method: Simplex LP Options 16 Constraint 17 Demand 18 Machine hour 19 Direct labor hours 5000 4000 Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. 0 0 Revenue Cost Profit 430000 -430000 Help Solve Close 23 24

The result is shown below. Optimum sales mix is 1665 standard and 1501 premium units. The maximum profit is 668550.5.

123 1 x A B fc =121-122 C D Standard Premium 400 540 E F L M N O P Q A G H Standard Premium 1665 1501 I Total Mix Solver Results 20 1.2 1.5 40 2 33300 1998 2497.5 60040 3002 1501 93340 5000 3998.5 1 Solver found an integer solution within tolerance. All Constraints are satisfied. Reports Answer Keep Solver Solution 25 125000 11995.5 147654 O Restore Original Values 10% 2 Sales price 3 Requirement 4 Direct material 5 Direct labor 6 Machine hours 7 Variable cost 8 Direct labor 9 Factory overhead 10 Marekting 11 Fixed cost 12 Manufacturing 13 Marketing 14 Administration 15 16 Constraint 17 Demand 18 Machine hour 19 Direct labor hours Return to Solver Parameters Dialog Outline Reports 230000 80000 120000 230000 80000 120000 Save Scenario... OK Cancel Solver found an integer solution within tolerance. All Constraints are satisfied. It is possible that better integer solutions exist. To make sure Solver finds the very best solution, set the integer tolerance in the options dialog to 0%. 1000 1000 5000 4000 Revenue 666000 810540 Cost Profit 1476540 807989.5 668550.5