50.1K

Verified Solution

Link Copied!

Cost-Volume-Profit Spreadsheet

(CVP Graph)

1. DATA SECTION

Selling price per unit $32.00

Variable cost per unit--manufacturing 15.00

--S&A 6.00

Total fixed costs -- manufacturing 24,000

--S&A 21,000

2. TABLE SECTION

Compute the following for various sales volume levels. (Start with 0 units and increase the units sold by 500 units. Enter the sales volume in a column). You need to go on for whatever it takes to view the breakeven point nicely in the middle of the graph.

Sales revenues

Total variable cost

Total Contribution margin

Fixed cost

Total costs (variable and fixed)

Income before tax

Hint:(1) Construct the first row using appropriate absolute cell references. The first column of first row is the sales volume. The second column is Sales revenues which is Sales volume * Selling price per unit. Use the appropriate formula with correct cell references. Do the same for other columns.

(2) Fill the first column with increasing sales volume in steps of 500. Copy the rest of Row1 down to the end.

(3) Always test check the spreadsheet arithmetic against hand calculations.

(4) It is a good habit to save interim workuse filename CVP.

Required:

1. Plot the Sales Volume in units as independent variable (X axis) versus the following dependent variables (Y axis) (all in one graph)

Sales Revenue dollars

Fixed cost dollars

Total cost dollars

2. Plot the Sales Volume in units as independent variable (X axis) versus Income before tax (Y axis).

Be sure to label the graph axes appropriately, and to include a legend identifying the lines.

3. Print the entire worksheet on one page as a formal business report (i.e., suppress grid lines and row and column headingsdo not include the graphs in this report but attach the two graphs as two separate printouts). Use data given in Section 1 above.

4. Print the entire worksheet on one page again as a formal business report, changing the selling price to $33. No need for the graphs here.

5. Print cell formulas on one page (landscape view) including grid lines and row and column headings. Do not include graph on this page.

Answer & Explanation Solved by verified expert

See Answer