GENERAL INFORMATION: There will be two spreadsheets in your workbook as follows: Data Block (above) and Contribution Income Statement format
View the step-by-step solution to:

Question

ACC207 1.pngACC 207 2.png

/>ACC 207 3.pngACC 207 4.pngACC 207 5.png

Please Help me with this ACC 207 assignment it would be much appreciated

ACC 207 2.png

GENERAL INFORMATION:
There will be two spreadsheets in your workbook as follows: Data Block (above) and
Contribution Income Statement format illustrated below:
Original Data
What If #1
Units
Jnits
Last Year
?
Proposed:
?
Per
Per
Total
Unit
Total
Unit
Sales
?
E
?
$ ?
Less variable expenses
7
Contribution margin
7
?
Less fixed expenses
Net income
7
$ ?
Contribution Margin
Ratio
7%
7%%
Breakeven point in
Dollars
$ ?
Breakeven Point in Units
?
Margin of Safety
$ ?
Operating Leverage
70.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,

ACC 207 3.png

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 datablock page for all proposed changes in the
“IHhat 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. SAVJNG Y OUR FILE: Save your file according to the following name format:
Original data fileQYour Last Name, First Name Initial) Excel#l. For Example: SmithJExcellxls or SmithJExcellxlsx (depending on which version of Merosoft
you are using). SUBMISSION OF YOUR EXCEL ASSIGNEIENT:
Put afooter on each a ein the bottom ri t—hand corner which includes our name and Zfl)#. 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 Eb. Your file should contain the following items: 1. DataElock 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!

ACC 207 4.png

Excel #1
Problem and Data GarageDoorscom is afamily owned business that produces apremium garage doorfor
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. GarageDoorscom is exploring ways to improve the efficiency of its production process andincrease profitability. They produced 400 garage doors in 2019. GarageDoorscom
contribution margin income statement for the year ended December 31, 2019 is given below: Total Per Unit
Sales $960,000 $2,400.00
1iJariable expenses 000 000
Contribution margin 020,000
Fixed expenses 200,000
Net operating income $280,000 What If #1: l. 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 andimplemented, 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: 12 Eta! On your datablock 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?

ACC 207 5.png

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?

ACC207 1.png

ACCY 207
EXCEL ASSIGNMENT #1
Fall 2019
CHECK FIGURES: What If #1: NOI $256,000
What If#2: 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 GarageDoors. com using the following format
for your data block page
GarageDoors.com
For the Year Ended December 31, 2019
Operating results:
Original
What If #1 What If #2 What If #3
Unit selling price
$2,400.00
Variable cost per unit
$
$
$
Contribution margin per unit
$
Annual fixed costs
Volume sold (in units) per year
400
Increase (decrease )
Proposed changes:
What If #1 What If #2
What If #3
Increase (Decrease) in Volume (in units)
Increase (Decrease) in Unit Sales Price
Increase (Decrease) in Variable Cost Per Unit
Increase (Decrease) Fixed Cost
leale
There are three What If problems in the datafile. 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 O. 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 spread sheet.

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask Expert Tutors You can ask You can ask ( soon) You can ask (will expire )
Answers in as fast as 15 minutes