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...
-
There are three possible alternative hypotheses in a hypothesis test for a population mean. Identify them, and explain when each is used.
-
A new police car costs the Bay City Police Department $26,000. The annual maintenance cost for a car depends on the age of the car at the beginning of the year. (All cars accumulate approximately the...
-
As in Example 2.1, camera recycle times might use the sample space \(S=R^{+}\), the set of positive real numbers. Let \[ E_{1}=\{x \mid 10 \leq x <12\} \quad \text { and } \quad E_{2}=\{x \mid 11 0\}...
-
1. Are the auditors guilty of malpractice? Explain. 2. What defenses are available to Joker & Wild in this case? Explain what they must prove to successfully assert these defenses. 3. Assume you are...
-
ME 4451 Lab 2 Handout RRR Serial Robot Servo Control
-
Devon Bishop, age 45, is single. He lives at 1507 Rose Lane, Albuquerque, NM 87131. His Social Security number is 111-11-1117. Devon does not want $3 to go to the Presidential Election Campaign Fund....
-
Under the percentage of sales basis to estimate uncollectible accounts using the allowance method, management establishes a percentage relationship between the amount of credit sales and expected...
-
True Or False The courts are not at all uniform in their assessment of extreme and outrageous conduct.
-
The ____________ ____________ doctrine assumes that the head of the family is the most financially responsible person in the family.
-
Examples of exceptions to the non-liability of bailors are a. family purpose doctrine. b. automobile consent statute. c. joint enterprise. d. all of the above.
-
What have courts and legislatures done to avoid the bailor-nonliability rule? a. Describe the family-purpose doctrine. b. What is an automobile-consent statute? c. What is an omnibus clause, and how...
-
What are the differences between public and private necessity?
-
What strategies can you utilize to be an effective team member now and in the future?
-
Write a paper about medication error system 2016.
-
Christian Dior S.A. is a successful family business with its famous fashion and perfume brands. Its latest effort in growing the business involves opening a series of international outlets to...
-
A group of retired engineering professors from Singapore Polytechnic has decided to form a small manufacturing firm that will produce a full line of automation equipment. The investors have proposed...
-
Footwear, Inc. manufactures a complete line of mens and womens formal shoes for independent merchants. The average selling price of its finished product is \($85\) per pair. The variable cost for...
Study smarter with the SolutionInn App