USING A COMPUTER SPREADSHEET TO SOLVE MULTIPLEPRODUCT BREAK-EVEN, VARYING SALES MIX

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., Excel™). 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.