DATA SECTION (ONE FOR EACH SCENARIO, SIX SCENARIOS)

Make data labels common for all data, looking somewhat as follows:

SCENARIO #1 SCENARIO #2

Selling price per unit $32.00

Variable cost per unit-- manufacturing 15.00

--S&A 6.00

Total fixed cost -- manufacturing $24,000

--S&A 21,000

Sales volume in units 6,000

Tax rate 40%

Copy the numbers in Scenario1 to each of the other 5 scenarios.

TABLE SECTION

Prepare a contribution margin format income statement for each scenario making cell references to its own column.

SCENARIO #1 SCENARIO #2

Sales Revenue $

(Less) Variable Costs:

manufacturing ( )

S&A ( )

Contribution margin $

(Less) Fixed Costs:

manufacturing ( 24,000)

S&A ( 21,000)

Net Income Before Tax $

Less Income Tax ( )

Net Income After Tax $

Hint. Construct one column first. Use relative cell references only, since each column has its own data. Then copy it to each of the other five scenarios.

ANALYSIS SECTION

Scenario #2: A proposed new machine will reduce variable product cost to $13, increase fixed product cost to $30,000, and keep S&A costs the same. What will be the profit? Hint: If your formulas are correct, then all you need is to change the Data section of Scenario 2.

Use Goal Seek function (search help on goal seek and read the instructions) to find the following:

Scenario #3: Break even sales volume in units and dollars with the old machine as in Scenario #1. Hint: The goal is zero net income. You can achieve the goal by changing the Sales volume.

Scenario #4: Sales volume in units and dollars needed for a Net Income After Tax of $10,000, all else as in #1.

Scenario #5: Break even sales volume in units and dollars with the new machine.

Scenario #6: Sales volume in units and dollars to give with the new machine the same profit before tax as with the current machine in #1.