Question

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

Answer 1

a)

If English garden company buys from Jerk Werx Company, cost would be $7 per each unit.

At 50,000 units, total cost = 50,000 *$7 = $350,000

b)

Constructing spreadsheet:

Build following parameters from the information provided in the question in excel spreadsheet. These inputs can be changed according to new information, the calculations will change automatically.

Period of useful life

10

Cost of Equipment	250000
No. of units	50000
Direct Material Cost Per Unit	1.8
Direct abour Cost Per Unit	1.5
Variable Manufacturing Overhead per unit	1.2
Total Variable Costs per unit	4.5
Depreciation	25000
Fixed Manufacturing Overhead	70000
Price in the market	7
(As Wheel is an input, market price is assumd as price)

Depreciation formula = cost of equipment/ useful life

Ex: Variable material cost per unit = 90000/50000 = 1.8

From above table, build working sheet of production costs.

Production costs of wheels
No. of Units to be produced	50000	insert reference to no. of units
Direct Material Cost	90000	insert reference to no. of units multiplied by cost per unit
Direct abour Cost	75000	insert reference to no. of units multiplied by cost per unit
Variable Manufacturing Overhead	60000	insert reference to no. of units multiplied by cost per unit
Total Variable Costs	225000	sum the variable costs
Depreciation	25000	insert reference to depreciation
Fixed Manufacturing Overhead	70000	insert reference to fixed overhead
Total Fixede Costs	95000	sum the fixed costs
Total Costs(fixed + Variable)	320000	sum the fixed and variable costs
Cost per unit	6.4	total cost/no. of units
Break Even point	38000	(total fixed costs)/(price per unit -total variable cost per unit)

If Mr Hawk wants calculations should not be visible, hide the rows which contain calculations and sjhow the final results only.

Production costs of wheels
No. of Units to be produced	50000
Total Costs(fixed + Variable)	320000
Cost per unit	6.4
Break Even point	38000

For revised equpment cost, just change the input value from 250000 to 325000. Data will automatically change in the cost worksheet.

Cost of Equipment	325000
No. of units	50000
Direct Material Cost Per Unit	1.8
Direct abour Cost Per Unit	1.5
Variable Manufacturing Overhead per unit	1.2
Total Variable Costs per unit	4.5
Depreciation	32500
Fixed Manufacturing Overhead	70000
Price in the market	7
(As Wheel is an input, market price is assumd as price)
Period of useful life	10

Production costs of wheels
No. of Units to be produced	50000
Direct Material Cost	90000
Direct abour Cost	75000
Variable Manufacturing Overhead	60000
Total Variable Costs	225000
Depreciation	32500
Fixed Manufacturing Overhead	70000
Total Fixede Costs	102500
Total Costs(fixed + Variable)	327500
Cost per unit	6.55
Break Even point	41000

Conclusion:

• If annual requirement of wheels is above 38,000 units for equiment at $250,000 or 41,000 units for equipment at $325,000, the company may go for wheels internal production.
• As there is prospect of new customer with the production increase, company may go for internal production as it will reduce per unit cost.
• Supply bottlenecks of wheels can be avoided with internal production.
• Comparing cost of $350,000 from outside purchase, internal costs are less and can go for making decision.