ACCY 207 EXCEL ASSIGNMENT #1 Fall 2019 CHECK FIGURES: What If#1: NOI $256,000 What If2: Increase in NOI $40,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 What If #2 What If #3 Original $2,400.00 What If #1 S Unit selling price Variable cost per unit Contribution margin per unit Monthly fixed costs Volume sold (in units) per year S S S 400 What If #1 Increase (decrease) What If #2 What If #3 S 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 S There are three What If problems in the data file. Input the changes in your excel file under the D. ... in f. 1. Anna Tiba . 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 What If #1 Units Proposed: Per Total Unit $ ? 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 7 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 each 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 Save your work frequently! Do not be the next person telling horror stories about lost work! Back up your work SAVING YOUR FILE: Save your file according to the following name format: Original data file: (Your Last Name, First Name Initial) Excel#1. For Example: SmithExcell xls or SmithJExcell.xlsx (depending on which version of Microsoft you are using) SUBMISSION OF YOUR EXCEL ASSIGNMENT: Put a footer on each page in the bottom right-hand corner which includes your name and ZID#. Before submitting your Excel assignment, check the Print Preview to make sure your Income Statement is centered (horizontally) in the page and you have included the footer. You will submit your file to Bb. Your file should contain the following items: 1. Data Block 2. Contribution Margin Income Statement Please be sure what you turn in is a unique product. You may work together, but you must each do your own spreadsheet. Do NOT turn in duplicate spreadsheets. We will assume you cheated and you both (or all) will get a zero for the assignment. DO NOT WAIT TO THE LAST MINUTE TO START THIS PROJECT! 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 ($120) 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. What If #2: 2. Refer to the original data. The company's sales manager believes that a reduction of 5% in the sales price ($120) 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? ACCY 207 EXCEL ASSIGNMENT #1 Fall 2019 CHECK FIGURES: What If#1: NOI $256,000 What If2: Increase in NOI $40,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 What If #2 What If #3 Original $2,400.00 What If #1 S Unit selling price Variable cost per unit Contribution margin per unit Monthly fixed costs Volume sold (in units) per year S S S 400 What If #1 Increase (decrease) What If #2 What If #3 S 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 S There are three What If problems in the data file. Input the changes in your excel file under the D. ... in f. 1. Anna Tiba . 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 What If #1 Units Proposed: Per Total Unit $ ? 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 7 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 each 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 Save your work frequently! Do not be the next person telling horror stories about lost work! Back up your work SAVING YOUR FILE: Save your file according to the following name format: Original data file: (Your Last Name, First Name Initial) Excel#1. For Example: SmithExcell xls or SmithJExcell.xlsx (depending on which version of Microsoft you are using) SUBMISSION OF YOUR EXCEL ASSIGNMENT: Put a footer on each page in the bottom right-hand corner which includes your name and ZID#. Before submitting your Excel assignment, check the Print Preview to make sure your Income Statement is centered (horizontally) in the page and you have included the footer. You will submit your file to Bb. Your file should contain the following items: 1. Data Block 2. Contribution Margin Income Statement Please be sure what you turn in is a unique product. You may work together, but you must each do your own spreadsheet. Do NOT turn in duplicate spreadsheets. We will assume you cheated and you both (or all) will get a zero for the assignment. DO NOT WAIT TO THE LAST MINUTE TO START THIS PROJECT! 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 ($120) 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. What If #2: 2. Refer to the original data. The company's sales manager believes that a reduction of 5% in the sales price ($120) 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