Welcome to Highline BI248 class.
Hey, if you want to download this workbook file,
this is BI248 Chapter 7 Start.
You can click on the link below the video.
Now we've already completed this sheet right
here quad fixed cost variable cost profit.
We actually added this sheet and created this whole model
in last video, video number 2.
In this video, we want to see how to use Goal Seek.
Now I actually want to insert a new sheet.
So I'm going to click on Calculate Revenue
and use our keyboard Shift F11 and then double click
and call this Goal Seek.
And Enter.
Now, I want to come over to this sheet
and highlight our assumption area and our model area
with our [INAUDIBLE] Control C. Come
over to Goal Seek and Control V. Now I immediately want
to come down to this Smart tag.
Click and I select Keep Source Column Width.
So there I got my column widths.
Control and roll.
And now here's what we want to do.
Here is our total profit.
And obviously, we are allowed to change these inputs.
So I want to figure out what the break even point is.
So I come here.
I'm going to type 3,000.
Just like that, I get a profit much larger than break
even because break even is zero.
So I come down here and I try 2,100.
Well, that's negative.
Well, I could iterate back and forth
till I zero in on units or quantity that
would give me exactly zero.
Control Z. Control Z.
Instead, I am going to use an awesome feature.
Now I want to scroll in here and talk about a feature called
Goal Seek.
Goal Seek will allow us to tell a formula
to evaluate to a particular answer
by changing a formula input.
But in order to use Goal Seek, if I hit F2,
that formula has to be using that formula input.
Now notice this is not directly using it.
It's using total revenue, total cost.
But when I come here and hit F2, you
could see this formula is using the formula inputs from above
and our decision variable.
This one is using-- oh, that one right there.
This one is using, oh, the total variable
cost from above and our decision variable.
So all of these formulas are influencing this last formula
here.
And there's a number of times where we're
using this decision variable.
As long as you have a cell that's
connected either directly or indirectly to a formula input
you can use Goal Seek.
Ready?
We go up to Data.
Data Tools.
What If.
And Goal Seek.
The keyboard in Excel 2007 or later
is Alt, A, W, G. Now that's three letters.
If you want a shorter one, if you know earlier Excel versions
keyboard-- that means 2003 earlier-- then Alt, T, G.
And I like Alt, T, G because it's
the old Tools menu Goal Seek.
So Tools Goal Seek.
Now, Set Cell.
That has to be your formula.
To value.
Notice that this does not have a collapse button.
We cannot link it to a cell.
The value you want your formula to evaluate to
has to be typed in.
Now we're talking about break even here.
That's a when profit is exactly zero.
And tab.
By changing cell, that's our formula input.
When I click OK, just like that, look at that.
It gives me 2,661.
And click OK.
So if I believe the demand is greater than 2,661
then I would consider making the quad product.
Now notice here we changed quantity.
I'm going to change this back.
Control Z to 2,000.
That is not the only variable we can
change because what if we didn't think
we could get demand of 2,601?
Well, other potential variables we
could change if we believed that it would be OK to up the price
and we could still have 2,000 sales then watch this.
We could use Goal Seek to change that formula input.
Alt, T, G. Automatically, I have that cell
selected so it thinks I want to change that formula.
Tab.
To value, that's the value I want
this formula to evaluate to.
Zero.
Tab.
By changing.
That's a formula input.
And any one of these could be OK.
I'm going to click that one.
And click OK.
So 1,871, when I click OK, if that was an acceptable price
then we could use that as our price or more
to get above our break even point.
Now, I'm going to Control Z and leave the original 1,650
and the original 2,000.
Goal Seek.
Incredibly useful as long as you have
a formula with formula inputs.
You want to say what the formula should evaluate to
and Goal Seek will change that formula input.
Now in two videos ahead, we're going
to do data tables, which takes Goal Seek to another level.
But when we come back in our next video,
I want to do a brief video on the formula
for break even number of units.
All right.
We'll see you next video.