Cost$-$Volume$-$Profit Analysis

Using Excel for Cost$-$Volume$-$Profit $($CVP$)$ Analysis

The Oceanside Garden Nursery buys flowering plants in four$-$inch pots for $$8\mathrm{.}70$ each and sells them for $$17\mathrm{.}75$ each.

Management budgets monthly fixed costs of $$7,350$ for sales volumes between $0$ and $25,000$ plants.

Use the blue shaded areas on the ENTERANSWERS tab for inputs.

Always use cell references and formulas where appropriate to receive full credit. If you copy$/$paste from the Instructions tab you will be

marked wrong.

Requirements:

Create a table to compute the revenue, variable costs, fixed costs, and total costs for each volume to use in creating your

graph. $($see below$)$

Use the contribution margin approach to compute the company's monthly breakeven point in units.

Use the contribution margin ratio approach to compute the breakeven point in sales dollars.

Use the contribution margin approach to compute the monthly sales level $($in units$)$ required to earn a target operating

income $($given in EnterAnswers$1).$

Use the contribution margin approach to compute the monthly sales level $($in units$)$ required to fulfill a given scenario

$($given in EnterAnswers$1).$

Use the contribution margin approach to compute the margin of safety $($in units$)$ using the target operating income given

in requirement $4.$

Use the high low method and the table created below in the instruction worksheet to develop the mixed cost formula

for our flowering plants business

Prepare two graphs, one of the company's CVP relationships and a scatterplot graph showing the relationship between

the volume and cost. Include the sales revenue line, the fixed cost line, and the total cost line by completing the table below

in your CVP relationship graph. Create chart titles and label the axes. Display the equation with your trendline on your

scatterplot.