Spreadsheet Assignment Mastering Excel Required Build the spreadsheet pictured in Exhibit 3.2. Be sure to use formulas that will automatically calculate profitability if fixed cost, variable cost, or sales volume is changed.

Spreadsheet Tip 1. The shading in column D and in row 6 can be inserted by first highlighting a section to be shaded, choosing Format from the main menu, then Cells, and then clicking on the tab titled Patterns, and then choosing a color for the shading. The shortcut method to accomplish the shading is to click on the fill color icon (it looks like a tipped bucket and is in the upper right area of the screen).

2. Similar to basic math rules, the order of calculation within a formula is multiplication and division before addition and subtraction. Therefore, if you wish to subtract variable cost from selling price and multiply the difference by units sold, the formula must be (28  C8)*E5.
3. The quickest way to get the correct formulas in the area of E8 to I16 is to place the proper formula in cell E8 and then copy this formula to the entire block of E8:I16. However, the formulas must use the $ around the cell addresses to lock either the row or the column, or both. For example, the formula 2*$B$8 can be copied to any other cell and the cell reference will remain B8 because the $ symbol locks the row and column. Likewise, $B8 indicates that only the column is locked, and B$8 indicates that only the row is locked.
Analysis of Cost, Volume, and Pricing to Increase Profitability 143 COMPREHENSIVE PROBLEM Use the same transaction data for Magnificant Modems, Inc., as was used in Chapter 1. (See page 52.)
Required

a. Use the following partially completed form to prepare an income statement using the contribution margin format.
Sales Revenue $600,000 Variable Costs:
Contribution Margin 225,000 Fixed costs Net Income $31,050

b. Determine the break-even point in units and in dollars.

c. Assume that next year’s sales are budgeted to be the same as the current year’s sales. Determine the margin of safety expressed as a percentage.