Question

ACCY 207

EXCEL ASSIGNMENT #2

Spring 2018

CHECK FIGURES: What If #1: NOI $630,000

                                                           Increase in NOI $180,000

REQUIREMENTS:

Use the data in the posted problem for this Excel assignment. For the Year Ended December 31, 2017 prepare a Contribution Income Statement for CedarWorks using the following format for your data block page:

CedarWorks
For the Year Ended December 31, 2017
Operating results:	Original	What If #1	What If #2	What If #3
Unit selling price	$3,000.00	$	$	$
Variable cost per unit	$2,100.00	$	$	$
Contribution margin per unit	$   900.00	$	$	$
Annual fixed costs	$900,000	$	$	$
Volume sold (in units)	1,500
		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		$	$	$
Target Profit		$	$	$

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. You will use formulas in the Operating results area to adjust the original data for the changes under the Proposed changes area to calculate the new what if data. 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			What If #1
		Units			Units
	Last Year	1,500		Proposed:	?
	Total	Per Unit		Total	Per Unit
Sales	$4,500,000	3,000.00		$   ?	$         ?
Less variable expenses	3,150,000	2,100.00		?	?
Contribution margin	1,350,000	900.00		?	$         ?
Less fixed expenses	900,000			?
Net income	$ 450,000			$   ?
Contribution Margin Ratio	30%			? 0%
Breakeven point in Dollars	$3,000,000			$   ?
Breakeven Point in Units	1,000			?
Margin of Safety	$ 1,500,000			$   ?
Operating Leverage	3.0			? 0.0
Increase (Decrease) in NOI after proposed changes:				$    ?

                                              

All amounts must be either cell referenced from the data block page or supported by formulas! This includes the amounts in the Original Data columns.

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, 2017). 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.

SAVING YOUR FILE:

Save your file according to the following name format:

Original data file: (Your Last Name, First Name Initial) Excel#2.

For Example: SmithJExcel2.xls or SmithJExcel2.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 #2

Problem and Data

CedarWorks manufactures playground equipment from Northern White Cedar wood which is free of all chemical additives and never splinters. The current manufacturing process is heavily labor intensive, so the company is studying ways to improve profits given that it currently has a significant amount of unused capacity. CedarWorks contribution margin income statement for the month of December 31, 2017 is given below:

           

                                                                                       Total             Per Unit          

Sales                                                                $4,500,000      $3,000

            Variable expenses                                           3,150,000

            Contribution margin                                        1,350,000

            Fixed expenses                                                     900,000

            Net operating income                                      $ 450,000

What If #1:

1. The company is studying the effect on its financial statements of purchasing some new equipment which would allow it to automate a large portion of its operations. Since direct labor costs will decline, variable costs would decrease by $900.00 per unit. However, total fixed costs would increase by $2,250,000. The volume of sales is expected to increase by 600 units if the new equipment is purchased. If the company operates in an industry that is sensitive to changes in the economy, do you think CedarWorks should purchase the new equipment. Explain.       

What If #2:

2. As an alternative, rather than purchasing the new equipment, the president is thinking about changing the company’s marketing method. Under the new method, the president is proposing that CedarWorks pay its sales people a 5% commission on sales and decrease the monthly fixed salary by $420,000. Paying the sales force commissions is also expected to increase sales volume by 20% (or 300 units) each month. Do you agree with the president’s proposal? Explain.

What If #3:

3. Management is currently in contract negotiations with the labor union. If the negotiations fail and the company does not buy the equipment (part 1) or change the company’s marking method (part 2), direct labor costs will increase by 10% (or $90 per unit) and fixed costs will increase by $25,000 per month. If these costs increase, how many units will the company have to sell to earn a profit of $1,000,000.

Answer 1