Practice Video: Goal Seek

Loading...

Do curso por Macquarie University

Excel Skills for Business: Intermediate II

415 classificações

Macquarie University

Excel Skills for Business: Intermediate II

415 classificações

Curso 3 de 4 em Specialization Excel Skills for Business

Spreadsheet software remains one of the most ubiquitous pieces of software used in workplaces across the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. In this third course of our Excel specialization Excel Skills for Business you will delve deeper into some of the most powerful features Excel has to offer. When you have successfully completed the course you will be able to Check for and prevent errors in spreadsheets; Create powerful automation in spreadsheets; Apply advanced formulas and conditional logic to help make informed business decisions; and Create spreadsheets that help forecast and model data. Once again, we have brought together a great teaching team that will be with you every step of the way. Nicky, Prashan and myself will guide you through each week. As we are exploring these more advanced topics, we are following Alex who is an Excel consultant called in by businesses that experience issues with their spreadsheets.

Na lição

Data Modelling

This module is all about data modelling. Learn to model different scenarios based on input, assumptions and/or outcomes. Also learn the use of functionalities like Goal Seek, Data Tables and the Scenario Manager to make your models more robust.

Practice Video: Modelling Functions: SUMPRODUCT5:50

Practice Video: Data Tables7:01

Practice Video: Goal Seek5:12

Practice Video: Scenario Manager8:50

Practice Video: Solver9:48

Week 5 Wrap-Up1:30

Conheça os instrutores

Dr Yvonne Breyer
Director, Learning and Teaching
Faculty of Business and Economics

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.

Explore nosso catálogo

Registre-se gratuitamente e obtenha recomendações, atualizações e ofertas personalizadas.

O Coursera proporciona acesso universal à melhor educação do mundo fazendo parcerias com as melhores universidades e organizações para oferecer cursos on-line.

© 2018 Coursera Inc. Todos os direitos reservados.

Baixar na App Store

Baixar no Google Play