SPREADSHEET EXERCISE: PERFORMING BREAKEVEN ANALYSIS AND SENSITIVITY ANALYSIS
Selmore Collectible Toy Company (SCTC) makes toy sets consisting of collectible trucks, vans, and cars for the retail market. The firm is developing a new toy set that includes a battery-powered tractor-trailer, complete with cab and trailer, sports car, and motorcycle. Each set sells for $100. Table 1 shows the major components of SCTC's annual fixed costs for the toy set. Each component includes the cost of purchases, depreciation, and operating expenses. Table 2 shows the major components of SCTC's variable costs.
Prepare a spreadsheet to support the decision making needs of SCTC's managers. The spreadsheet should show the fixed costs, variable costs per unit, the contribution margin, and the breakeven point for this product. How many sets does SCTC have to sell before it can start turning a profit? Include a data table to show alternative breakeven points, assuming variations in insurance costs and labour costs. How would increasing the sale price to $125 affect the breakeven point?
Table: SCTC Fixed Costs
Category Amount
Land $ 42 500
Buildings 332 500
Manufacturing machinery 532 000
Office equipment 212 800
Utilities 30 500
Insurance $1 250 000
Total $1 250 000
Table 2 SCTC Variable Costs
Category Amount
Labour $15.00
Advertising 1.00
Shipping & receiving 5.00
Total $21.00
Application Software Exercise:
To perform a breakeven analysis, one must first determine all the variable and fixed costs and then calculate the contribution margin per unit. The contribution margin per unit is the difference between the selling price per unit and the variable costs per unit (selling price per unit – variable costs per unit).
This problem has been simplified so that the breakeven point for a single product, such as the proposed new toy set, can be calculated by dividing the total fixed costs by the contribution margin per unit. Once you have determined the breakeven point, the minimum number of units of the new toy set that must be sold for Selmore to break even on the product, you can then perform a sensitivity analysis examining the impact of different cost variables on the breakeven point.
In your sensitivity analysis, examine the impact on the breakeven point when labour costs range from $12 to $17 dollars per hour and when insurance costs range from $90 000 to $110 000 annually. Your data table should show labour costs ranging from $12 to $17 per hour in one dollar per hour increments and insurance costs ranging from $90 000 to $110 000 in $5 000 increments.