Discuss the importance of Excel Q5 calculations to Wicked Mikes Manufacturing (BE, TP, CM%, and MOS). Give examples supported by new numbers (new spreadsheet) of how you would use these calculations as the CFO of the company. Fully discuss the differences between the traditional vs CVP format (minimum of 3).

Excel Q5 referred to above, completed in the screenshot below:

5. Prepare three CVP Income Statements using the following yearly volumes: 500, 2000, and 2500. Keep in mind how variable and fixed costs behave. The traditional income statement from #4 should be about the same net income as the 2000 units for the CVP format. In addition, Q1 should agree to the total fixed costs and per-unit variable costs for these schedules.  a) Calculate Break-even in units and sales $ for the company b) Contribution margin ratio c) Calculate units and sales $ if the company wants a profit of $1,000,000. d) Margin of safety in dollars for 2000 units.

 

Average costs are shown below. Manufacturing Overhead Salaries for factory supervisor, janitor and security Factory Rent Deprediation for factory equipment Factory Property Taxes Maintenance for factory equipment Total Fixed Manufacturing Overhead Per Unit Fixed selling and administrative expense Salaries office and administrative ofice Rent $400,000.00 Income Statement $100,000.00 Sales in Units 500 2000 2500 $85,000.00 Sales in Revenue $6,088,912 50 $24,355,650.00 $30,444,562 50 $16,000.00 Less: Variable Cost $80,000.00 Direct Materials $3,250,000.00 $13,000,000.00 $16,250,000.00 $681,000.00 $270,000.00 $1,080,000.00 $1,350,000.00 Direct Labor $340.50 Variable selling and administrative $129,750.00 $519,000.00 $648,750.00 $3,649,750.00 $14,599,000.00 $9,756,650.00 $18,248,750.00 $12,195,812 50 Total Variable Cost $650,000.00 Contribution Margin Less Fived Cost Fixed Manufacturing overhead Variable selling and administrative Total Fixed Cost $2,439,162 50 $350,000.00 insurance office Depredation office equipment Advertising Total Fixed Selling and Administration Expense Per Unit $60,000.00 $681,000.00 $681,000.00 $1320,000.00 $681,000.00 $40,000.00 $1.320,000.00 $1.320,000.00 $220,000.00 $2,001,000.00 $2,001,000.00 $2,001,000.00 $1.320,000.00 $660.00 Net income (oss) $438,162.50 $7,755,650.00 $10,194,812.50 Variable Selling and Administrative otfice utilities and Misc office expenses Sales Travel Sales Commission Total Variable Selling and Administrative Expense Per Unit $45,000.00 $24,000.00 $450,000.00 $519,000.00 $259.50 Computation of Cost Per Unit Direct Material Cost Direct Labor Cost Fixed Manufacturing Overhead Total Cost Per Uinit $6,500.00 $540.00 $340 50 $7,380 50 $4,797 33 Markup at 65% Selling Price Per Unit $12,177.83 A. Break Even Sales Selling Price Per Unit Less: Variable Cost Per Unit $12,177.83 $7,300.00 Contribution Margin Per Unit $4,87783 Total Fixed Cost BEP Unit $2,001,000.00 313 Sales 46.70% BEP Dollar Sales $4,284,796.57 a. Contribution Margion Per Unit Selling Price Per Unit $4,877.83 $12,177.83 40.05% C Units to be sold to earn a profit of $1,000,000 Fixed Cost+Desired Profit / Contr. Margin Per Unit 615.2332238 615 Units Sales required to earn a profit of $1,000,000 Fixed Cost+Desired Profit/Contr Margin Ratio $7,492,202 53 D. Margin of Safety Dollar = Sales at 2,000 level - BEP Sales Sales at 2,000 Units BEP Sales Margin of Safety dollar $24,355,650.00 $4,284,796.57 $20,070,853.43