Tom Hawk is the production manager at English Garden Company. One of the products he is responsible for producing is its top-selling wheelbarrow. In the past Mr. Hawk has purchased wheels from the Jerk Werx Company for $6.00 each. Jerk Werx recently informed Mr. Hawk that, when the current contract expires, it will be raising the price of wheels to $7.00 each.

In a typical year, English Garden makes 50,000 wheelbarrows. If it decides to make the wheels for its wheelbarrows, it will commit to making them for ten years. Mr. Hawk is investigating the possibility of making the wheels in-house. His initial cost estimates are as follow:

Equipment Purchase:	$250,000 (ten-year life)
Annual Depreciation:	$25,000
Direct Materials (50,000 Units):	$90,000
Direct Labor (50,000 Units):	$75,000
Variable Manufacturing Overhead (50,000 Units):	$60,000
Fixed Manufacturing Overhead (50,000 Units):	$70,000 (does not include depreciation)

Mr. Hawk scheduled a meeting with the executive committee to discuss the issue. He wants you to create a spreadsheet that he can take to the meeting to aid in the discussion of whether English Garden Company should make or buy wheels, as well as the breakeven quantity of wheels and the cost savings involved.

Since Mr. Hawk is not entirely confident in his cost estimates, he wants the workbook to be able to accept changes to any of the estimates. As the estimates change, the Make/Buy decision as well as the cost savings should automatically update. Since the committee wants to focus on the decision and not on the calculations, Mr. Hawk suggests that seeing the calculations would be a distraction and thus the calculations should not be visible.

Bonus (optional, 20 points): Mr. Hawk has become concerned that his equipment purchase estimate is too low, as last week he saw such equipment advertised for $325,000 (with an expected 10-year life). Additionally, in a recent executive meeting, the sales vice president said a new account could become a significant purchaser of wheelbarrows, if EGC could increase its production. On a separate worksheet, make (and explain) an estimate for a per-unit wheel production internal price, and use it to calculate the breakeven production quantity for a more expensive equipment purchase. Make sure that EGC decision makers can see the breakeven quantity update automatically as they change the equipment purchase estimate in this worksheet.

Use Excel