Using Goal Seek and Data Tables for a Break Even Analysis

One Variable Data Table

Show the Possible Outcomes as you Vary a Single

Data Cell

Structure of a One Variable Data Table

Structure of One Variable Data Table

This is the formula (or a link to the formula) that

returns the results you are after. In this example it

is the Profit formula in B12 so we would type: =B12

These are the variables you wish to test. In this

example they are the Units Sold. Each value in

the column will be run through the cell containing

our Units Sold (B6) to see what the corresponding

Profit would be. B6 is our Column Input Cell

because our variables are in a column and they

will be substituted into cell B6.

Exercise 1: One Variable Data Table – Units Vary

Find Profits for Various Units Sold

Part 1

Fill out the Spreadsheet as

shown.

Exercise 1: One Variable Data Table – Vary Units

Find Profits for Various Units Sold

Part 2

a.) Highlight D5:E15

b.) From the menu click: "Data – What If Analysis – Data Table".

c.) Set the "Column input cell" to B6 (Units Sold) and click "OK".

d.) Click "OK".

You should get the profits

shown to the left. It tells

you what your profits

would be for each value

of Units.

Student Exercise: Show Profit for Various Profits

Show what the Profits would be if you Vary Price

Exercise 2: One Variable, Two Formulas

Show Future Value for Various Monthly Deposits

Determine how much money

you would have in 30 years by

depositing a specific monthly

amount.

Show one column if you simply

hid it under your mattress

(12*30*Monthly deposit)

And another column if you

invested it at 4%.

=FV(Rate,Periods,Pmt Amount)

Exercise 2: One Variable, Two Formulas

Show Future Value for Various Monthly Deposits

Part 1

Type the formulas shown

in E4 and F4.

Exercise 2: One Variable, Two Formulas

Show Future Value for Various Monthly Deposits

Part 2

a.) Highlight D4:F15

b.) From the menu click:

Data – What If Analysis – Data Table…

c.) Set the Column Input Cell to B4.

d.) Click “OK”.

You should get the results shown on the

first slide of this exercise.

Two Variable Data Table

Show the Possible Outcomes as you Vary Two Data

Cells

Structure of a Two Variable Data Table

Formula Whose Results You wish to Display

This corner of the structure must contain either the formula whose

results you wish to display or a link to the cell containing the

formula whose results you wish to display. For example, it might be

a formula that returns Profit for given Prices and Units Sold.

The results generated by a Two Variable

Data Table will appear here. For example,

profits.

Column Variable List

This is a list of the different values

you wish to substitute into a cell

that the formula in the right corner

references. For example, if the cell

in the right corner returns Profit,

this row might contain Units Sold.

Column variables should be values

as formulas can produce incorrect

results if they reference the same

celled used to produce your

results.

Row Variable List

This is a list of the different values you wish to substitute into a

cell that the formula in the right corner references. For

example, if the cell in the right corner returns profit, this row

might contain Unit Price. Row variables should be values as

formulas can produce incorrect results if they reference the

same celled used to produce your results.

Exercise: Two Variable Data Table

Show Future Value for Various Monthly Deposits and Various Rates

Exercise: Two Variable Data Table

Show Future Value for Various Monthly Deposits and Various Rates

Part 1: Create the data cells and formulas shown.

Exercise: Two Variable Data Table

Show Future Value for Various Monthly Deposits and Various Rates

Part 2

a.) Highlight D4:L15.

b.) From the menu:

Data – What If Analysis –

Data Table…

c.) Set Row Input Cell to: B6

Set Column Input Cell to B4

d. Click “OK”.

You should get the results shown on the first page of this exercise.

Using Data Tables (and some

algebra) for a Break Even

Analysis

What is Break Even Analysis?

The Point where your Sales Cover your Expenses

• For a given price, how

many units must you sell

to break even?

• For a given number of

units sold, what price

must you charge to

break even?

Exercise: Find Breakeven Prices for Various Units Sold

Using a One Variable Data Table

We wish to generate a list of

breakeven Prices for the Units

listed. For example, at 300

Units the breakeven price is

$21.67.

This example has two main requirements:

Part One: Building the Model

Create a model where typing almost any

number in Units Sold will cause the formula in

Price to return a value that causes the

formula in Profit to return zero.

Part Two: Use of a One-Variable Data Table to

generate Prices.

Exercise: Find Breakeven Prices for Various Units Sold

Algebraically Forcing Profit to Go to Zero when Units Sold Varies (Finding Price)

Price and Units Sold are both variables that affect Profit. If we state any value for Units Sold there

is almost always a corresponding value we can assign to Price to make Profit go to zero.

Profit = Price * Units – Variable Cost Per Unit * Units – Fixed Costs

If we set Profit to Zero and Solve for Price, the equation

will return the Price we need to charge for any given

number of Units.

Exercise: Find Breakeven Prices for Various Units Sold

Part One: Setting Profit to Zero and Solving for Price

Profit = Units

Var. Cost

Per Unit

Fixed

Costs

Price * -

* Units -

Units= Price * -

Var. Cost

Per Unit

Fixed

Costs

* Units -

Units= Price *

Var. Cost

Per Unit

Fixed

Costs

* Units +

= Price

Var. Cost

Per Unit

Fixed

Costs

Units

Break Even

Price

Var. Cost

Per Unit

Fixed

Costs

Units

Price

Var. Cost Per Unit

Fixed Costs

Break Even Units

Note that if you want Break Even Units, use this Formula:

Exercise: Find Breakeven Prices for Various Units Sold

Part one: Formula in Price sets Profit to Zero if Value in Units Sold Changes

1. Copy the data from the

previous exercise and edit

it as shown. (Formula in B5

is the only difference.)

2. Type a value in Units Sold

(B6). Price should update

to force Profit to remain at

zero.

Exercise: Find Breakeven Prices for Various Units Sold

Creating the Data Table Structure (Units Sold Varies)

1. Create the table shown here.

2. In E5 type: =B5

3. Type these values

in D6:D15.

Exercise: Find Breakeven Prices for Various Units Sold

Creating the Data Table Structure (Units Sold Varies)

4. Highlight D4:E15

5. From the menu click:

“Data – What If Analysis – Data

Tables”.

6. Set the Column input cell to

B6 (Units Sold). Leave Row

input cell blank.

7. Click “OK”.

Our variables will be substituted into B6

one by one to produce a corresponding

Price for each. (For a one variable table

you only have a column input cell.)

Exercise: Find Breakeven Prices for Various Units Sold

Find Breakeven Prices for Various Units Sold (Solution)

Excel will produce the price

that will set profit to zero for

each given number of units.

Note that if you type in

different Units in column D your

Prices will update to give you

the corresponding break even

price.