Discuss the importance of Excel Q5 calculations to Wicked Mikes Manufacturing (BE, TP, CM%, and MOS). Give
Question:
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 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
Expert Answer:
Differences between Traditional Income Statement CVP Income Statement F... View the full answer
Students also viewed these marketing questions
-
In the following examples, state how you would use the principle of marginal analysis to make a decision. a. Deciding how many days to wait before doing your laundry b. Deciding how much library...
-
Using the data in Table 1, explain how you would use appropriate methods of analysis such as regression analysis or correlation analysis to analyze the effect of order size and product variety on the...
-
Discuss the importance of the control environment, or "setting the tone at the top," in establishing a culture of honesty and integrity in a company.
-
There are three mutually exclusive projects, where the basic information is provided below. Assume a DN alternative does not exist. Initial Cost Annual net profit Useful Life Salvage value A $25,000...
-
Develop one argument in support of or refuting the following statement: Viruses are living microorganisms.
-
A partially completed flowchart showing some of the major documents commonly used in the purchasing function of a merchandise business is presented below. Identify documents 1, 3, and4. Purchase Order
-
Install and run the Microsoft Security Baseline Analyzer on your home computer or laptop. Write a report explaining any weaknesses identified and how to best correct them. Attach a copy of the tools...
-
Wong Accounting Services is considering a special order that it received from one of its corporate clients. The special order calls for Wong to prepare the individual tax returns of the corporations...
-
Q1. With the use of appropriate theory (S), explain the justification for or otherwise of the Single Spin Salary Structure (SSSS) Instructions: 8 to 10 pages New time romans Double spacing 2.0 Add...
-
The following account balances are for Lisa Assaad, Certified Public Accountant, as of April 30, 20X1. INSTRUCTIONS Using the accounting equation form, determine the balance for Lisa Assaad, Capital,...
-
Tony is married and has a taxable income of $97,500. He makes monthly contributions of $1000 to a tax-deferred savings plan. His monthly take-home payment is reduced by $
-
Silver nitrate has a molar mass of 169.87 g/mol. To make 250 mLof 1% weight/volume AgNO3 in ethanol, what mass of AgNO3 isrequired?
-
The substitution reaction of molybdenum hexacarbonyl, Mo(CO)6 ,with a variety of other molecules L are believed to occur by thefollowing mechananism. Mo(CO)6----> Mo(CO)5+CO Mo(CO)5-----> Mo(CO)5 L...
-
Computer - assisted audit techniques ( CAATs ) are the use of computers for audit work and comprise mainly audit software and test data. Required: List the benefits of employing CAATs in an audit...
-
What does "information asymmetry mean"? What consequences can it cause? Q2: What does "Market Efficiency" mean? Q3: How does the Value Relevance/Information Approach differ from the Measurement...
-
Calculate the normal cost, least cost and crash duration for the following project: (Indirect costs are $120 per day) Activity IA ABCDEFGH E G H IPA | A, B B C, D D F E, G H, I Duration (days) Normal...
-
Without using finance calculator, Question 2 (a) Haziq invests RM15,000 in a fund at 7% compounded quarterly. After t years, the value of the investment is RM300,000. Determine the value of t. (b)...
-
Write a paper about medication error system 2016.
-
The short-run supply curve of a perfectly competitive firm is a. its MC curve. b. its MC curve above the minimum point of AVC. c. its MC curve above the minimum point of ATC. d. none of the above.
-
Which of the following is true? a. Economic profits encourage the entry of new firms, which shift the market supply curve to the right. b. Any positive economic profits signal resources into the...
-
What are constant-cost industries?
Study smarter with the SolutionInn App