Goal Seek

Loading...

来自 PwC 的课程

Data Visualization with Advanced Excel

1456 个评分

PwC

Data Visualization with Advanced Excel

1456 个评分

课程 3（共 5 门，Specialization Data Analysis and Presentation Skills: the PwC Approach）

In this course, you will get hands-on instruction of advanced Excel 2013 functions. You’ll learn to use PowerPivot to build databases and data models. We’ll show you how to perform different types of scenario and simulation analysis and you’ll have an opportunity to practice these skills by leveraging some of Excel's built in tools including, solver, data tables, scenario manager and goal seek. In the second half of the course, will cover how to visualize data, tell a story and explore data by reviewing core principles of data visualization and dashboarding. You’ll use Excel to build complex graphs and Power View reports and then start to combine them into dynamic dashboards. Note: Learners will need PowerPivot to complete some of the exercises. Please use MS Excel 2013 version. If you have other MS Excel versions or a MAC you might not be able to complete all assignments. This course was created by PricewaterhouseCoopers LLP with an address at 300 Madison Avenue, New York, New York, 10017.

从本节课中

Advanced Scenario Analysis

This week, we are going to explore three different analytical methods used to help model different scenarios and deal with variable uncertainty. These methods are scenario analysis, sensitivity analysis and simulation. We’ll look at what each method is and then go deeper into why and how you use each. Following some guided demonstration, you’ll be given a chance to practice in an Excel workbook and demonstrate what you’ve learned.

Scenario Manager9:09

One-way Data Tables4:29

Two-way Data Tables4:20

Simulations13:48

与讲师见面

Alex Mannella
Alumni / Former Principal

[MUSIC]

To demonstrate how Goal Seek can be used,

let's look at a real world example together.

An airline company you're working for

is considering expanding their portfolio by adding Atlanta as a new destination.

This would require an initial investment of $250,000 to secure capacity

at the Atlanta airport.

Your market research team estimates that the additional route would generate

$100,000 in incremental profit in year one.

And the additional $50,000 in profit during years two through five.

In order to determine whether or not this is a worthwhile investment,

let's look into net present value or NPV and internal rate of return or IRR.

NPV accounts for the time value of money and calculates how much the returns

are worth in today's terms if you were to receive that in the future time period.

In simple terms, NPV is how much a dollar given to you in 5 years is worth today.

NPV also includes any cost associated with the investment.

IRR looks at the same problem from a different angle.

IRR seeks to find what the interest rate what equal to reach an NPV of zero.

In summary NPV can be viewed as the profitability of a project in absolute

terms, where IRR is the profitability of a project in relative terms, or percentages.

Now that we understand what we're trying to find,

let's get back to the problem at hand.

Based on the financial outlaid table, we can calculate NPV and the IRR in two ways.

First, we'll use the NPV formula before showing how Goal Seek

can help us arrive at the same answer much faster and more precisely.

First, we'll set up a formula for

NPV in Excel which is dependent on the value of our discount rate.

Let's type in the formula.

As a reminder from course two, the syntax for

NPV is the discount rate followed by

the cash flows starting at year one which

equals NPV(H27,B26:B31).

As you can see here, if the discount rate is 10%,

the NPV is approximately $429,000.

To calculate what discount rate will force the NPV

to equal zero, we also have a few options.

The brute force way is to use trial and error with the input value and

see when you're getting close to the desired answer.

We can try it with 25%.

35%, 55%.

And as you can see

as a discount rate becomes larger, the NPV is getting closer to zero.

So we know that the IRR must be around 55%.

However, if you want to save the time and

skip straight to the results, Goal Seek is a perfect tool.

In order to quickly determine what the internal rate of return is or

in other words, what the discount rate is to make the NPV equals zero.

Let's go the Data tab > What-If Analysis > Goal Seek.

In the pop-up window, you will see that there are three input

parameters, set cell, to value, and by changing cell.

Set cell is where we specify which cell is the outcome we're trying to monitor.

In our case, it is the NPV.

In the To Value field, type in the value of the desired outcome,

which in our case, we want the NPV to be 0.

Finally, by changing cell field is where we can specify which

variable we would like to manipulate to get the result we want.

Here is the IRR.

After all three fields are filled out let's click on OK.

Excel will then automatically cycle through different values of an IRR to

make the NPV equal to 0.

The final answer Goal Seek came up with is 57%.

In other words, if the discount rate is 57% the NPV of the investment will be 0.

Now, let's double check our answers.

Luckily for us, Excel already has a built-in formula specifically for

calculating IRR as long as you have the financial outlay of the investment.

In cell H30 let's

type in the IRR formula

=IRR(B26:B31).

This refers to the same financial outlay table that we

used earlier to build out our goal seek tool.

As you can see, the formula returns 57%,

which is exactly the same result that the goal seek tool has arrived at.

Now, you can see Goal Seek allows you to experiment with an input variable

to get to and desired answer.

We use this all the time to help define our scenarios.

For example, Goal Seek is a perfect tool to determine what the price of electricity

needs to be to make a natural gas power plant economic at different gas prices.

To summarize, Goal Seek is located under Data > What-if Analysis > Goal Seek.

In order to use Goal Seek,

there needs to be a formula linking the cell you are changing to the end results.

As long as you have established an Excel formula or

math equation to define how the variable and the end result are related.

Goal Seek can help you take the guess work out of your calculation by quickly

finding what variable value you need to achieve the end result you want.

Now it's your turn to practice.

[MUSIC]

探索我们的目录

免费加入并获得个性化推荐、更新和优惠。

Coursera 致力于普及全世界最好的教育，它与全球一流大学和机构合作提供在线课程。

© 2019 Coursera Inc. 保留所有权利。

通过 App Store 下载

通过 Google Play 获取