Fraser Co. is considering a change to its cost structure. Below is the data relating to the current structure as well as the proposed change.

Current Structure

Proposed Structure

Unit Sales

20,000

Unit Sales

20,000

Sales Price Per Unit

$ 100

Sales Price Per Unit

$ 100

Total Variable Costs (based on 20,000 units)

$ 400,000

Total Variable Costs (based on 20,000 units)

$ 700,000

Total Fixed Costs

$ 900,000

Total Fixed Costs

$ 600,000

1.) Prepare a CVP Statement for each cost structure. Incorporate cell references and formulas where indicated. You can instantly create the CVP Statement for the Proposed Structure, by copying and pasting your completed CVP Statement for the Current Structure. Make sure all highlighted areas are completed. Try to avoid the headings when copying. You want to keep the Proposed Structure heading.

CVP Statement - Current Structure

CVP Statement - Proposed Structure

% of Sales

Total

Per Unit

Type a Label Here

formula

cell reference

formula

Type a Label Here

cell reference

formula

formula

Type a Label Here

formula

formula

formula

Type a Label Here

cell reference

Type a Label Here

formula

2.) Use the Contribution Margin technique to calculate the Breakeven point in units and dollars for each scenario. You can save time again by copying from one section to the next. Be careful with the headings.

Breakeven Units - Current Structure

Breakeven Units - Proposed Structure

cell reference

=

formula

cell reference

=

formula

cell reference

cell reference

Breakeven Sales Dollars - Current Structure

Breakeven Sales Dollars - Proposed Structure

cell reference

=

formula

cell reference

=

formula

cell reference

cell reference

3.) Compare the Net Operating Income and Breakeven calculations for both scenarios. What happened to the breakeven point and why?

Type response here

4.) Compute the Degree of Operating Leverage for both scenarios. Save time again.

Degree of Operating Leverage - Current Structure

Degree of Operating Leverage - Proposed Structure

cell reference

=

formula

cell reference

=

formula

cell reference

cell reference

5.) Use the Degree of Operating Leverage to determine how a 10% increase in sales will impact Net Income.

Current

Proposal

Degree of Operating Leverage

cell reference

cell reference

LABEL

cell reference

cell reference

Net Income Impact

formula

formula

Old Net Income

cell reference

cell reference

LABEL

formula

formula

New Net Income

formula

formula

6.) Save and print (face -to-face class )

7.) Copy the entire spreadsheet to the "Revisions" tab (click on the upper left hand corner of the worksheet to highlight the entire page before copying).

8.) Use the "Revision" spreadsheet to prove your calculation from instruction #5 of the "Original" spreadsheet by increasing the sales volume in the data section (gray shaded area) by 10%. Remember to change anything else in the data section which would be affected by a change in sales volume. You should not make any changes below row 6.

6.) Save and print (face -to-face class ) or upload to Blackboard (online class).