Sensitivity Analysis

Loading...

En provenance du cours de Indian School of Business

Accounting and Finance for IT professionals

69 notes

Indian School of Business

Accounting and Finance for IT professionals

69 notes

Cours 3 sur 6 dans Specialization Business Technology Management

This course presents an introduction to the basics of financial accounting and finance for IT professionals. The first part of the course will focus on understanding the most important financial statements, namely, the balance sheet, the income statement, and the statement of cash flows. We will then focus on how we can understand the financial health and performance of the company by examining a number of important financial ratios that are derived from the financial statements of the company. The second part of the course will focus on the basics of finance. This includes the concept of time value of money, discounting cash flows, and capital budgeting. The course will also introduce the idea of real options, how they affect a project’s NPV, and their impact of the decision to accept/reject a project. Lectures on concepts will be supplemented with numerical examples.

À partir de la leçon

Capital Budgeting

In this series of videos, we will cover the various aspects related to deciding whether to accept or reject investment in a project. This starts off by discussing the common decision tools, namely, net present value (NPV), internal rate of return (IRR) and payback period (PBP) and how to calculate and interpret them. We will then move on to what free cash flows are and how to estimate them. Finally, we will focus on the flexibilities in projects, how to value them and how they affect the decision to accept or reject projects.

Net Present Value4:55

Internal Rate of Return and Payback Period5:41

Decide Between Two Projects6:31

Drawbacks of Payback Period4:05

Drawbacks of IRR and NPV6:29

Free Cash Flows7:22

Other Relevant Cash Flows4:18

A Capital Budgeting Example7:53

Sensitivity Analysis4:35

Uncertainties in Projects4:10

Resolving Uncertainties7:00

Rencontrer les enseignants

Ramabhadran Thirumalai
Assistant Professor
Indian School of Business

[MUSIC]

In this video we will focus on sensitivity analysis.

We'll have you study how changing the value of some projections

while holding others a constant affects the project's NPV.

You can use the Data Table function in Excel to do this analysis easily.

I should emphasize that for you to be able to use the Data Table function

all cells in the spreadsheet except the input ones highlighted in yellow should be

connected through formulas, otherwise the sensitivity analysis will be wrong.

We will use the same example from last time.

We calculated the expected NPV of the project to be a negative 211,618.

We can expect revenue projections to be a critical input for NPV calculation.

What if our projection of $500,000 in the first year in the best case scenario

is too high or too low?

How does it affect the expected NPV?

Link an empty cell cell in your spreadsheet to the cell with the value for

the expected NPV.

In the column to the left of the cell and

starting one row below, enter a number of values that sales can take.

I'm inputting values from 0 to 800,000 at increments of 50,000.

Next, highlight both columns and go to the data tab.

Under the data tab, look for what if, and click on it.

Select data table.

Leave row input cell empty.

In the column input cell, click on the cell that has the value of 500,000 as

the revenue projection in the first year, click on okay.

You should see a number of values in the second column

below the original expected NPV.

These are the NPVs for various values of first year sales revenues.

As you can see the expected NPV becomes positive when first year sales

revenues in a best case are at $600,000.

At all values less than $600,000, the expected NPV stays negative.

So this is clearly an important input to NPV calculations.

If we are underestimated revenues for

any reason by at least $100,000 we will be rejecting a project that we would have

otherwise accepted if our sales projections had been more accurate.

A second assumption we could look at is a sales growth of 15% per year

over the second and third years.

What if those numbers were higher or lower?

We could again use Data Table for a range of values from 0% to 30% at 3% increments.

We find that NPV's positive for growth rates of 21% and higher.

The project is unprofitable at lower growth rates.

A third assumption to examine could be the perpetual growth rate for

FCF beyond five years.

We assume that number to be 5%.

Let's look at the sensitivity of expected NPV to changes in this growth rate.

We again use Data Table over a range of values between 0% and

9% at increments of 1%.

The expected NPV is positive for growth rates of 7% and higher.

Otherwise it is still negative.

These examples give you an idea as to what is sensitivity analysis.

We can repeat this for pretty much every projection or assumption we have made.

We can do this for values in the worse case scenario also.

Though here we are focused only on the best case of high sales.

We can repeat the sensitivity analysis by changing the values of

two inputs simultaneously while holding other values are constant.

Data Table and Excel will allow us to do this.

Let's do this for first-year sales and sales growth rate of the second and

third years.

Link an empty cell, to the cell with the value for the expected NPV of the project.

In the same column below the cell, input a range of values for first-year sales.

I'm taking values between 0 and 800 thousands.

At increments of 50,000.

In the same row as the cell linked to the expected NPV input a range of values for

the sales growth.

We keep it between 0% and 30% at increments of 3%.

Then select all these cells, click on what if under the data tab and

select Data Table.

Since we have growth rates in the top row of the highlighted table,

grow input cell should reference the cell with the sales growth rate.

Column input sell should reference the cell with the first year sales.

Click on okay.

Now, you should see all cells filled with values of expected NPV.

A large number of these values are negative.

The project is attractive only if revenues are much higher or

sales growth is much higher.

We can do the sensitivity analysis for any pair of assumptions or projections.

A two way analysis like this,

helps us identify the interplay between our projections.

In this video,

we try to understand which are the critical assumptions that affect our NPV.

How confident we are of our cash flow projections, there are a number of factors

beyond the company's control that will impact these cash flows.

[MUSIC]

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2018 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play