Manipulate the baseline case model parameters in three different ways:
- Complete a CVP analysis for the baseline case;
- complete four other scenarios (i.e., what-if analyses), and recommend the best scenario; and
- prepare a graph and derive information from the graph.
- Make CVP Calculations for the Baseline Case
On the first sheet of your workbook (the sheet labeled ‘baseline case’), do a-d below.
(a) Prepare a contribution margin income statement (also called a variable-costing income statement) for the
manufacturing company for the upcoming year. Examples of contribution income statements are on page 68,
Exhibit 3-1 of the Datar and Rajan textbook and in the Chapter 3 course notes. Create this income statement
below the baseline case parameters in the Excel file you download. Key in proper headings.
Check figure: Operating profit (operating income) of $28,570,000.
(b) Compute the company’s contribution margin per unit and contribution margin percentage for the upcoming
year. The contribution margin percentage is calculated as contribution margin per unit / selling price per unit or
as total contribution margin / total revenue. Make these calculations below your income statement. Clearly
label these calculations.
(c) Calculate the company’s breakeven point in units for the upcoming year. Make this calculation below your
contribution margin calculations. Use Excel’s “round” function to round up to the nearest whole number. To do
this, move your curser to the cell beside the decimal number and key in the following formula: =ROUNDUP(cell
reference,0). The italicized cell reference means you need to key in the cell where the decimal number is
located (e.g., F12). The number 0 means zero decimal places. Clearly label this calculation.
(d) Calculate the company’s breakeven point in sales dollars for the upcoming year. Make this calculation
below your breakeven calculation in units and use the company’s contribution margin percentage to make this
calculation. Clearly label this calculation