USING A COMPUTER SPREADSHEET TO SOLVE MULTIPLEPRODUCT BREAK-EVEN, VARYING SALES MIX
The following projected income statement for More-Power Company is repeated for your convenience. Recall that the projection is based on sales of 75,000 regular sanders and 30,000 mini-sanders.
|
Regular
|
|
|
|
Sander
|
Mini-Sander
|
Total
|
|
Sales
|
$3,000,000
|
$1,800,000
|
$4,800,000
|
|
Less: Variable expenses
|
1,800,000
|
900,000
|
2,700,000
|
|
Contribution margin
|
$1,200,000
|
$ 900,000
|
$2,100,000
|
|
Less: Direct fixed expenses Product margin
|
250,000
|
450,000
|
700,000
|
|
$ 950,000
|
$ 450,000
|
$1,400,000
|
|
Less: Common fixed expenses
|
|
|
600,000
|
|
Operating income
|
|
|
$ 800,000
|
Required:
1. Set up the given income statement on a spreadsheet (e.g., ExcelTM). Then, substitute the following sales mixes, and calculate operating income. Be sure to print the results for each sales mix (a through d).
|
Regular Sander
|
Mini-Sander
|
|
a.
|
75,000
|
37,500
|
|
b.
|
60,000
|
60,000
|
|
c.
|
30,000
|
90,000
|
|
d.
|
30,000
|
60,000
|
2. Calculate the break-even units for each product for each of the preceding sales mixes.