Wedgewood Candle Co.
Container Candle Division
Operating Results for Quarter 2 (Following Year)
Revenues		$ 5,193,500
Cost of Goods Sold:
DM	$ 1,419,075
DL	$ 354,025
MOH	$ 1,294,125	$ 3,067,225
Gross Margin		$ 2,126,275
Selling and Admin		$ 1,017,500
Division Operating Income		$ 1,108,775
% Variable Manufacturing Overhead rate		40%
% Fixed selling and Administrative costs		90%
Increase in sales		30%
Units sold		325,500

Create a new spreadsheet and name it CVP. (6 points) Using all formulas, starting in row 4 Column A where you list the label:

Calculate the contribution margin ratio

Calculate the breakeven in units

Calculate the breakeven in dollars

Calculate margin of safety in dollars

Calculate margin of safety %

Calculate degree of operating leverage

Calculate the increase in net income % if sales increase by 30%

Calculate the dollar increase in net income if sales increase by 30%

Total net income after increase in sales of 30%

Create a new spreadsheet and name it CVP-Chart. (5 points)Create a table listing quantities and calculating revenues, total costs, fixed costs and variable costs for the quantity listed. Start with quantities of 0 and increase by increments of 50,000 until you get to 600,000.

Use absolute references when calculating sales, variable costs and fixed costs

Using this table, create a breakeven chart clearly labeling the following

Fixed costs

Total costs

Total revenue

Breakeven point

Loss area

Gain area

X axis

Y axis

Chart title

Create a new spreadsheet and name it Data Table and create a what-if data table. (5 points)

Use increments of 45,000 for quantity starting from 0 to 495,000

Use increments of $1 for selling price, starting from 1 to 13

Use conditional formatting to highlight in red the all unprofitable combinations.

Format this spreadsheet with difference colors and labels for each analysis and ensure that it is easy to read. Highlight all final answers in yellow. (1 points)

On each tab of your spreadsheet, in row 1, type in the purpose, procedure and conclusion. You should use center across all columns and wrapping if appropriate. (1 points)

Check Figure: Operating leverage 2.53 Please attach Excel file for the solution.