Alex's next task is going to involve looking at the project costing model.
The company has put together a model where they have
worked out how many manuals they hope to produce,
the fixed costs involved, the duration,
given the working hours,
and the revenues they hope to make from this.
What they'd like to be able to do is to adjust this model to get some answers from it.
And the first one they want to know is
how many manuals do they have to produce to break even.
In the projected profit,
we have a calculation which relies on many of these inputs and of
course one of them is going to be the number of software manuals we produce.
At the moment, this calculation is producing the answer.
What we want to be able to do is change the answer to zero by adjusting this input.
We can obviously go and do this manually.
So if I click into my number of software manuals and change that to nine,
you can see I'm getting closer to zero.
And if I change that to eight,
definitely getting even closer.
So that is one way you could go about doing it,
but where the model is quite complex and the numbers are quite tricky,
that's going to be very slow.
Excel offers us a way of automating this procedure,
and it is called Goal Seek.
The way Goal Seek works is given a cell that has a calculation in it,
it will adjust that cell to
a specified value by changing one of the inputs that you provide.
Let's see how it works.
So we're going to click into E20,
and we're going to come back to our What-if Analysis on our Data tab.
And from the What-if Analysis we're going to select Goal Seek.
A dialog will pop up,
and the first thing it asks is the Set cell.
That is the cell that contains
your calculation that you want to change the value it's returning.
And because we selected it beforehand,
you'll see it's already populated.
We're going to click in the next box which is asking for
the value we want this to equal and that is a zero.
And then the final box is which input do we want to change to get to that result?
And this value must be in a typed-in value rather than a formula.
So we're going to click into the third box,
select B_5, which is our number of manuals.
Now watch carefully what happens in that cell when I click
OK. Did you see all the numbers changing? Everyone say, "Wow."
That was Excel going through the same iterative process that we did
manually to get to a very accurate value.
While we did the same process,
Excel did it much quicker and obviously much more accurately.
So having worked out what our break-even point is,
we can either accept the solution by clicking OK,
or if we want to get back to our original,
we can click Cancel.
We're actually going to click OK,
and there is our final answer.
Obviously we can't really produce 6.44 of a manual.
So we would need to round that up to seven.
But now at least we know the minimum number of manuals we have to produce,
so we don't make a loss.
One of the guys has come back to us and said thank you for that.
Actually we're hoping to do a little bit better than that.
We were hoping to clear $80,000 profit.
How many manuals will we need to produce to get a minimum of $80,000 profit?
So let's run through the same process again.
Click on our projected profit and come up to What-if Analysis.
Click Goal Seek.
The Set cell is already correct.
But in the To value we're going to change that to 80,000,
and then we're going to do that by changing the cell
B_5 which is our number of software manuals,
and then again click OK.
This time it has calculated that we are going to need to produce a minimum of 11.5.
And I'm going to click OK.
So we now know it's actually going to have to be
12 manuals to make our profit, at least 80,000.
So Goal Seek is a fantastic tool when you have a calculation,
but you want to change the result it's producing by changing one of its inputs.
It has heaps of uses in business.
See if you can work out anywhere you might be able to use that in your workplace.
Dr Yvonne BreyerDirector, Learning and Teaching
