Music Makers expects to produce and sell 500,000 CDs each month. The sales price per unit is $2.45. Variable costs per unit are $1.20. Total fixed costs per month are $540,000. The com- pany's target profit is $75,000 per month.

Required Use a spreadsheet to prepare a profit analysis for Music Makers. Use the follow- ing format.

Volume, unit cost, and ratio amounts should appear in column B. Total dollar amounts should appear in column C. Data will not appear in the shaded cells. Amounts in cells C4, C5, C7, \(\mathrm{C} 8, \mathrm{~B} 10, \mathrm{~B} 11, \mathrm{~B} 12, \mathrm{C} 12\), and B15 should be calculated using formulas and references to other cells. Cell B12 should report the breakeven point in units. Cell C12 should report the breakeven point in dollars. Appropriate formatting should be used throughout.
The spreadsheet should be designed so that a change in any of Music Makers' price or cost data can be entered and numbers in the spreadsheet will be recalculated automatically. What is the breakeven point if variable costs increase to \(\$ 1.22\) per unit? How many units must be sold to generate a target profit of \(\$ 75,000\) if the variable unit cost is \(\$ 1.22\) ? What is the breakeven point if fixed costs increase to \(\$ 560,000\) ?

Transcribed Image Text:

A B C Unit Total 2 Profit Analysis 3 4 5 Units Sales price Variable costs 6 Fixed costs 7 Total costs 8 Profit 9 Breakeven Analysis 10 Unit contribution margin 11 Contribution margin ratio 12 Breakeven 13 Target Profit Analysis 14 Target profit 15 Units required