Excel Case #$1$

ALL numerical answers you input should be cell references, NOT hardkeyed$/$typed$-$in numbers.

Data Inputs: Format per unit dollar amounts with $2$ decimal places and percentages as percentages with no decimal places.

Expected unit sales $40,000$

Selling price per unit $$12\mathrm{.}50$ Requirement #$1$:

Variable expense per unit $$7\mathrm{.}50$ Complete the Total, Per Unit, and $\%$ columns in the income statement $($cells B$11,$ B$12,$ B$13,$ B$14,$ and B$15$; cells C$11,$ C$12,$ and C$13$; cells D$11,$ D$12,$ and D$13).$

Total fixed expenses $$180,000$

Requirement #$2$:

Contribution Format Income Statement Calculate the sales dollars, fixed expenses, and total expenses for $0$ units and $50,000$ units $($cells C$20,$ D$20,$ and E$20$; cells C$21,$ D$21,$ and E$21).$

Total Per Unit $\%$

Sales $$500,000$ $$12\mathrm{.}50100\%$ Requirement #$3$:

Variable expenses $300,0007\mathrm{.}5060\%$ Create a CVP graph $($a $2-$D line chart is sufficient$)$ utilizing the data points from Requirement #$2$ and insert it below.

Contribution margin $200,000$ $$5\mathrm{.}0040\%$ Notes:

Fixed expenses $180,000*$Make sure you have three lines on your graph: a total expenses line, a total revenue line, and a fixed expenses line.

Net operating income $$(20,000)*$Be sure to check that the horizontal axis and vertical axis are the proper components $($units or dollars$)$; you can

switch the row$/$column on the chart design if needed!

$*$Be sure to include a chart title and axis labels, edit if necessary! $($Data table and data labels are not necessary.$)$

CVP Graph Information

Sales Dollars Fixed Expenses Total Expenses

Data Points for $0$ units sold and $50,000$ units sold: $-$ $$-$ $$180,000$

$50,000$ $$625,000$ $$180,000$Excel Case #$1$

Data Inputs:

Expected unit sales

Selling price per unit

Variable expense per unit

Total fixed expenses

Sales

Variable expenses

Contribution margin

Fixed expenses

Net operating income

CVP Graph Information

ALL numerical answers you input should be cell references, NOT hardkeyed$/$typed$-$in numbers.

Format per unit dollar amounts with $2$ decimal places and percentages as percentages with no decimal places.

Requirement $H1$

Complete the Total, Per Unit, and $\%$ columns in the income statement $($cells B$11,$ B$12,$ B$13,$ B$14,$ and B$15$; cells C$11,$ C$12,$ and C$13$; cells D$11,$ D$12,$ and D$13).$

Requirement #$2$ :

Calculate the sales dollars, fixed expenses, and total expenses for $0$ units and $50,000$ units $($cells C$20,$ D$20,$ and E$20$; cells C$21,$ D$21,$ and E$21).$

Requirement H$3$:

Create a CVP graph $($a $2-$D line chart is sufficient$)$ utilizing the data points from Requirement $12$ and insert it below.

Notes:

$*$Make sure you have three lines on your graph: a total expenses line, a total revenue line, and a fixed expenses line.

$"$Be sure to check that the horizontal axis and vertical axis are the proper components $($units or dollars$)$; you can

switch the row$/$column on the chart design if needed I

$"$Be sure to include a chart title and axis labels, edit if necessaryl $($Data table and data labels are not necessary.$)$