Please be sure to include the formulas that you used in the data cells. Thank you!
CHECK FIGURES: What If #1: NOI S256,000 What If#2: Increase in NOI S40,000 REQUIREMENTS: Use the data in the posted problem for this Excel assignment. For the Year Ended December 31, 2019 prepare a Contribution Income Statement for Garage Doors.com using the following format for your data block page: Garage Doors.com For the Year Ended December 31, 2019 Operating results: Unit selling price Variable cost per unit Contribution margin per unit Annual fixed costs Volume sold (in units) per year 1 Original What If 41 What If 12 What If 13 S2.400.00 S SS S S SS SS 400 Increase (decrease) What If 41 What If 12 What If 13 Proposed changes: Increase (Decrease) in Volume (in units) Increase (Decrease) in Unit Sales Price Increase (Decrease) in Variable Cost Per Unit Increase (Decrease) Fixed Cost There are three What If problems in the data file. Input the changes in your excel file under the Proposed changes section of the file (see above). If it is a decrease to one of these items then show the decrease in parenthesis. If there is no new data (no change to that item) to type into the cell then just type in 0. Remember each change is independent of the others unless otherwise noted in the problem. You will use formulas in the Operating results area (columns What If #1, #2, and #3) to adjust the original data using the changes under the Proposed changes area to calculate the new what if data in those columns. Make sure you are using formulas and cell references in your formulas between the two sections to calculate the new data. See the next page for the Analysis format for your spreadsheet. GENERAL INFORMATION: There will be two spreadsheets in your workbook as follows: Data Block (above) and Contribution Income Statement format illustrated below: Original Data Units Last Year ? Per Total Unit $ ? What If #1 Units Proposed: ? Per Total $ ? S ? Unit Sales Less variable expenses Contribution margin Less fixed expenses Net income Contribution Margin Ratio Breakeven point in Dollars Breakeven Point in Units Margin of Safety S ? Operating Leverage 20.0 ? 0.0 Increase (Decrease) in NOI after proposed changes: Add additional columns next to What If#1 for What If #2, and What If #3 using the same format above as for What If#1. Separate cach column in some manner so it is easy to read. You don't have to use highlighting but could use borders instead or some other way based on your preferences. Just be sure it is professional and easy to read. You will need to use formulas to calculate Contribution Margin Ratio, Breakeven Points. Margin of Safety, and Operating Leverage. You must use a Data Block area and cell reference the appropriate data from the Data Block page to the income statement and/or use formulas. You should cell reference last year's per unit data also and use this information to calculate the original total column. It should not just be typed into the Contribution Income Statement directly. Don't forget to show dollar signs and percentages as per the format above. Add a heading to the Contribution Income Statement. Your heading should have the company name, the name of the statement, and the time period it covers (For the Year Ended December 31, 2019). You should use cell references from your data block page for all proposed changes in the "What If Analysis" section. Do not type in changes directly to the Contribution Income Statement. Save your work frequently! Do not be the next person telling horror stories about lost work! Back up your work. Excel #1 Problem and Data Garage Doors.com is a family owned business that produces a premium garage door for residential customers. The company recently opened a new production facility in Oak Ridge, Tennessee in 2019. This facility produces the company's premium garage door, Classical Carriage House, for residential customers. Garage Doors.com is exploring ways to improve the efficiency of its production process and increase profitability. They produced 400 garage doors in 2019. Garage Doors.com contribution margin income statement for the year ended December 31, 2019 is given below: Per Unit $2,400.00 Sales Variable expenses Contribution margin Fixed expenses Net operating income Total $960,000 440.000 520.000 240.000 $280.000 What If #1: 1. The company has undertaken several sustainability projects over the past couple of years. Currently, management is evaluating whether to develop a comprehensive software control system for its manufacturing operations that would significantly reduce scrap and waste generated during the manufacturing process. If the company were to implement this software control system in its manufacturing operations, the use of the software control system would result in an increase of $120,000 in its annual fixed costs while the variable manufacturing cost per door would drop by $240. If the software control system were to be developed and implemented, determine the company's new break-even point in sales dollars and units, contribution margin ratio, margin of safety, operating leverage, and net income. Explanation: (2 pts.) On your data block page: Do you think the company should implement the software control system? Why or why not? What factors should the company consider before implementing the software control system? What If #2: 2. Refer to the original data. The company's sales manager believes that a reduction of 5% in the sales price (S120) combined with a $30,000 increase in advertising would increase annual sales by 100 units (garage doors). Recalculate the company's new break- even point in sales dollars and units; contribution margin ratio, margin of safety, operating leverage, and net income. Explanation (2 pts.) On your data block page: Would you recommend that the company do as the sales manager suggests? Why or why not? Explain. What If #3: 3. Refer to the original data. The president does not want to change the selling price. Instead, he wants to increase the sales commission by $100 per unit (garage door). He thinks this move, combined with just a $20,000 increase in advertising, would increase annual sales by 100 units. Explanation: (2 pts.) On your data block page: Would you recommend that the company do as the president suggests? Why or why not? Explain. Final Decision: (2 pts.) Which option would you choose and why? E Contribution Income Statement Garage Doors.com For the Year Ended December 31, 2019 Original Data Units Last Year 400 Total Per Unit What if #1 Units Proposed: 500 Total Per Unit What if #2 Units Proposed: 400 Total Per Unit What if #3 Units Proposed: Total Per Unit 10 Sales 11 Less: Variable Expenses 12 Contribution Margin 13 Less: Fixed Expenses 14 Net Income 16 Contriution Margin Ratio 17 18 Breakeven Point in Dollars 19 20 Breakeven Point in Units 22 Margin of Safety 24 Operating Leverage 25 26 Increase (Decrease) in NOI after proposed Changes: 1 Given Data Garage Doors.com 4 For the Year Ended December 31, 2019 5 Operating Results: 6 Unit Price 7 Variable Cost per Unit 8 Contribution Margin 9 Annual Fixed Costs 10 Volume sold in Units) per year Original $ 2,400.00 $ 1,100.00 $ 1,300.00 $ 240,000.00 400 Proposed Changes What if #1 What If #2 $ 2,400.00 $ 2,400.00 $ 1,100.00 $ 1,100.00 $ 1,300.00 $ 1,300.00 $ 240,000.00 $ 240,000.00 400 400 What if #3 $ 2,400.00 $ 1,100.00 $ 1,300.00 $ 240,000.00 400 11 Increase (Decrease) What if #1 What If #2 What if #3 13 Proposed Changes 14 Increase (Decrease) in volume (in Units) 15 Increase (Decrease) in Unit Sales Price 16 Increase (Decrease) in Variable cost per Unit 17 Increase (Decrease) Fixed Cost 24 CHECK FIGURES: What If #1: NOI S256,000 What If#2: Increase in NOI S40,000 REQUIREMENTS: Use the data in the posted problem for this Excel assignment. For the Year Ended December 31, 2019 prepare a Contribution Income Statement for Garage Doors.com using the following format for your data block page: Garage Doors.com For the Year Ended December 31, 2019 Operating results: Unit selling price Variable cost per unit Contribution margin per unit Annual fixed costs Volume sold (in units) per year 1 Original What If 41 What If 12 What If 13 S2.400.00 S SS S S SS SS 400 Increase (decrease) What If 41 What If 12 What If 13 Proposed changes: Increase (Decrease) in Volume (in units) Increase (Decrease) in Unit Sales Price Increase (Decrease) in Variable Cost Per Unit Increase (Decrease) Fixed Cost There are three What If problems in the data file. Input the changes in your excel file under the Proposed changes section of the file (see above). If it is a decrease to one of these items then show the decrease in parenthesis. If there is no new data (no change to that item) to type into the cell then just type in 0. Remember each change is independent of the others unless otherwise noted in the problem. You will use formulas in the Operating results area (columns What If #1, #2, and #3) to adjust the original data using the changes under the Proposed changes area to calculate the new what if data in those columns. Make sure you are using formulas and cell references in your formulas between the two sections to calculate the new data. See the next page for the Analysis format for your spreadsheet. GENERAL INFORMATION: There will be two spreadsheets in your workbook as follows: Data Block (above) and Contribution Income Statement format illustrated below: Original Data Units Last Year ? Per Total Unit $ ? What If #1 Units Proposed: ? Per Total $ ? S ? Unit Sales Less variable expenses Contribution margin Less fixed expenses Net income Contribution Margin Ratio Breakeven point in Dollars Breakeven Point in Units Margin of Safety S ? Operating Leverage 20.0 ? 0.0 Increase (Decrease) in NOI after proposed changes: Add additional columns next to What If#1 for What If #2, and What If #3 using the same format above as for What If#1. Separate cach column in some manner so it is easy to read. You don't have to use highlighting but could use borders instead or some other way based on your preferences. Just be sure it is professional and easy to read. You will need to use formulas to calculate Contribution Margin Ratio, Breakeven Points. Margin of Safety, and Operating Leverage. You must use a Data Block area and cell reference the appropriate data from the Data Block page to the income statement and/or use formulas. You should cell reference last year's per unit data also and use this information to calculate the original total column. It should not just be typed into the Contribution Income Statement directly. Don't forget to show dollar signs and percentages as per the format above. Add a heading to the Contribution Income Statement. Your heading should have the company name, the name of the statement, and the time period it covers (For the Year Ended December 31, 2019). You should use cell references from your data block page for all proposed changes in the "What If Analysis" section. Do not type in changes directly to the Contribution Income Statement. Save your work frequently! Do not be the next person telling horror stories about lost work! Back up your work. Excel #1 Problem and Data Garage Doors.com is a family owned business that produces a premium garage door for residential customers. The company recently opened a new production facility in Oak Ridge, Tennessee in 2019. This facility produces the company's premium garage door, Classical Carriage House, for residential customers. Garage Doors.com is exploring ways to improve the efficiency of its production process and increase profitability. They produced 400 garage doors in 2019. Garage Doors.com contribution margin income statement for the year ended December 31, 2019 is given below: Per Unit $2,400.00 Sales Variable expenses Contribution margin Fixed expenses Net operating income Total $960,000 440.000 520.000 240.000 $280.000 What If #1: 1. The company has undertaken several sustainability projects over the past couple of years. Currently, management is evaluating whether to develop a comprehensive software control system for its manufacturing operations that would significantly reduce scrap and waste generated during the manufacturing process. If the company were to implement this software control system in its manufacturing operations, the use of the software control system would result in an increase of $120,000 in its annual fixed costs while the variable manufacturing cost per door would drop by $240. If the software control system were to be developed and implemented, determine the company's new break-even point in sales dollars and units, contribution margin ratio, margin of safety, operating leverage, and net income. Explanation: (2 pts.) On your data block page: Do you think the company should implement the software control system? Why or why not? What factors should the company consider before implementing the software control system? What If #2: 2. Refer to the original data. The company's sales manager believes that a reduction of 5% in the sales price (S120) combined with a $30,000 increase in advertising would increase annual sales by 100 units (garage doors). Recalculate the company's new break- even point in sales dollars and units; contribution margin ratio, margin of safety, operating leverage, and net income. Explanation (2 pts.) On your data block page: Would you recommend that the company do as the sales manager suggests? Why or why not? Explain. What If #3: 3. Refer to the original data. The president does not want to change the selling price. Instead, he wants to increase the sales commission by $100 per unit (garage door). He thinks this move, combined with just a $20,000 increase in advertising, would increase annual sales by 100 units. Explanation: (2 pts.) On your data block page: Would you recommend that the company do as the president suggests? Why or why not? Explain. Final Decision: (2 pts.) Which option would you choose and why? E Contribution Income Statement Garage Doors.com For the Year Ended December 31, 2019 Original Data Units Last Year 400 Total Per Unit What if #1 Units Proposed: 500 Total Per Unit What if #2 Units Proposed: 400 Total Per Unit What if #3 Units Proposed: Total Per Unit 10 Sales 11 Less: Variable Expenses 12 Contribution Margin 13 Less: Fixed Expenses 14 Net Income 16 Contriution Margin Ratio 17 18 Breakeven Point in Dollars 19 20 Breakeven Point in Units 22 Margin of Safety 24 Operating Leverage 25 26 Increase (Decrease) in NOI after proposed Changes: 1 Given Data Garage Doors.com 4 For the Year Ended December 31, 2019 5 Operating Results: 6 Unit Price 7 Variable Cost per Unit 8 Contribution Margin 9 Annual Fixed Costs 10 Volume sold in Units) per year Original $ 2,400.00 $ 1,100.00 $ 1,300.00 $ 240,000.00 400 Proposed Changes What if #1 What If #2 $ 2,400.00 $ 2,400.00 $ 1,100.00 $ 1,100.00 $ 1,300.00 $ 1,300.00 $ 240,000.00 $ 240,000.00 400 400 What if #3 $ 2,400.00 $ 1,100.00 $ 1,300.00 $ 240,000.00 400 11 Increase (Decrease) What if #1 What If #2 What if #3 13 Proposed Changes 14 Increase (Decrease) in volume (in Units) 15 Increase (Decrease) in Unit Sales Price 16 Increase (Decrease) in Variable cost per Unit 17 Increase (Decrease) Fixed Cost 24
