Philco Manufacturing (PM) operates a factory in Strathcona County. PM can make trucks, cars, minivans, motorcycles, buses and go-karts. PM has no problem getting all the resources it needs except for bench seats, steel, labor, wheels and headlights.

The following table shows information available to PM management about resources required for each product:

	Trucks	Cars	Minivans	Motor Cycles	Buses	Go Karts
Seats	1.0	2.0	4.0	0.0	16.0	2.0
Steel	3.0	1.0	2.0	0.2	4.0	0.5
Labor	1.5	1.0	1.0	0.5	3.0	3.0
Wheels	18.0	4.0	4.0	2.0	6.0	2.0
Lights	6.0	4.0	4.0	1.0	4.0	4.0

The accounting department has calculated that each product adds the following contribution per unit:

	Trucks	Cars	Minivans	Motor Cycles	Buses	Go Karts
CM per unit	$12,000	$9,800	$10,000	$2,400	$13,000	$2,500

The production manager has stated that she is having problems purchasing resources due to supply chain issues. The maximum units available are as follows:

	Seats	Steel	Labor	Wheels	Lights
Max Units Available	340	380	540	1,100	360

The marketing manager has stated that PM can sell anything it produces as there is strong demand for all their products. However there are some contract agreements that must be met as follows:

1. There are contracts to sell 10 minivans to the City of Edmonton and 15 go-karts to Fun City in Sherwood Park next month.

1. Municipal and provincial permits and regulations limit production of some products. PM is limited to producing no more than 10 motorcycles and 5 busses per month.

Note - you must use EXCEL to solve this scenario and answer the questions below. When finished, drop your EXCEL sheet and the WORD doc with your answers into the Dropbox provided on Moodle.

Required:

Use Solver to calculate the optimal production rates and expected contribution margin for this scenario. (1 mark)

Hint: Pre-solved CM = $49,700 Solved CM = $797,500

Answer the questions below:

1. How many of each item should Philco produce?

# Trucks: _______ # Cars: ________

# Minivans: _______ # Motor Cycles: ________

# Busses: _______ # Go Carts: ________

2. What is the maximum contribution possible?

3. What raw materials (inputs) are binding?

4. How many seats remain unused?

5. How many wheels remain unused?

6. How many headlights are used?

7. Explain the bus binding constraint of 5 units:

8. What is the allowable range for the CM of a car within which the Solver model remains valid (ie. Philco still produces the same of each unit):

9. What is the allowable range for seats within which the Solver model remains valid (ie. shadow price does not change):

10. What is the most you should be willing to pay for one additional headlight?

11. What is the most you should be willing to pay for one additional labor hour?

12. If Philco insisted on producing 1 truck, how would the total CM change?

Here is the correct Answer Report for this problem (Philco Manufacturing):

A B C D E F G 1 Microsoft Excel 12.0 Answer Report 2 Worksheet: [Eg #3 - Philco Manufacturing.xIs]Main 3 Report Created: 5/25/2009 11:59:48 AM A 5 6 Target Cell (Max) 7 Cell Name Original Value Final Value 8 SH$17 Contribution Margin Available 49,700 797,500 9 10 Adjustable Cells 11 Cell Name Original Value Final Value 12 $B$16 Units to Produce Trucks 1.00 0.00 13 SC$16 Units to Produce Cars 1.00 25.00 14 SD$16 Units to Produce Minivans 1.00 45.00 15 $E$16 Units to Produce Cycles 1.00 0.00 16 SF$16 Units to Produce Buses 1.00 5.00 17 $G$16 Units to Produce Go Karts 1.00 15.00 18 19 Constraints 20 Cell Name Cell Value Formula Status Slack 21 $H$21 Seats Total 340.00 SH$21=$D$5 Not Binding 35.00 28 $G$16 Units to Produce Go Karts 15.00 $G$16>=$G$5 Binding 0.00 29 SE$16 Units to Produce Cycles 0.00 $E$16