• Home   /  
  • Archive by category "1"

Finance Case Study Cash Flow And Npv Table

What is the NPV formula?

The NPV formula is a way of calculating the Net Present Value (NPV) of a series of cash flows based on a specified discount rate.  The NPV formula can be very useful for financial analysis and financial modeling when determining the value of an investment (a company, a project, a cost-saving initiative, etc.).

Below is an illustration of the NPV formula for a single cash flow.

Screenshot of CFI’s Corporate Finance 101 Course.

 

NPV for a series of cash flows

In most cases, a financial analyst needs to calculate the net present value of a series of cash flows, not just one individual cash flow.  The formula works in the same way, however, each cash flow has to be discounted individually, and then all of them are added together.

Here is an illustration of a series of cash flows being discounted:

Souce: CFI’s Free Corporate Finance Course.

What is the math behind the NPV formula?

Here is the mathematical formula for calculating the present value of an individual cash flow.

NPV = F / [ (1 + i)^n ]

Where,

PV = Present Value

F = Future payment (cash flow)

i = Discount rate (or interest rate)

n = the number of periods in the future the cash flow is

How to use the NPV formula in Excel

Most financial analysts never calculate the net present value by hand nor with a calculator, instead, they use Excel.

=NPV(discount rate, series of cash flow)

(See screenshots below)

Example of how to use the NPV function:

Step 1: Set a discount rate in a cell.

Step 2: Establish a series of cash flows (must be in consecutive cells).

Step 3: Type “=NPV(“  and select the discount rate “,” then select the cash flow cells and “)”.

Congratulations, you have now calculated net present value in Excel!

Download the free template.

 

 

 

 

Source: CFI’s Free Excel Crash Course.

 

If you need to be very precise in your calculation, it’s highly recommended to use XNPV instead of the regular function.

To find out why, read CFI’s guide to XNPV vs NPV in Excel.

 

DCF Modeling

The main use of the NPV formula is in Discounted Cash Flow (DCF) modeling in Excel.  In DCF models an analyst will forecast a company’s three financial statements into the future and calculate the company’s Free Cash Flow to the Firm (FCFF). Additionally, a terminal value is calculated at the end of the forecast period. Each of the cash flows in the forecast and terminal value are then discounted back to the present using a hurdle rate of the firm’s weighted average cost of capital (WACC).

Below is an example of a DCF model from one of CFI’s financial modeling courses.

 

 

Screenshot: CFI financial modeling courses.

 

More helpful resources

Thank you for reading this guide to calculating net present value.  CFI’s mission is to help anyone become a world-class financial analyst.  To keep learning and advancing your career, these additional financial resources will be a big help:

Welcome back to corporate finance.

Last time we applied our different decision criteria to our forecasted free

cash flows to come up with a decision as to what to do with our tablet project.

This time I want to push those assumptions around.

Forecast drivers and other assumptions.

See how robust our decision is in a process called sensitivity analysis.

Let's get started.

Everyone, welcome back to corporate finance.

Today we're going to be talking about decision criteria.

But as always, let's start with a recap of our last lecture.

So last time we talked about decision criteria.

In particular, we asked, what do we do with our free cash flow.

And there were several things we could do.

We could compute an NPV, we could compute an IRR,

or we could compute a payback period.

And we talked about how to implement those decision criteria and

what decision to make, and we also alluded to some of the shortcomings of

some of those decision criteria.

Today I want to turn to sensitivity analysis.

In other words, having set up our DCF and

having completed all the inputs, now, let's push it around a little bit and

just see how robust and sensitive our valuation is.

So we can make the most informed decision possible.

So let's get started.

And we're going to start with something called break even analysis.

Break even analysis finds the parameter value that sets the NPV of

the project equal to zero holding fixed all other parameters.

So let's look at our cost of capital and initial investment forecast drivers.

The base, and I've got base up here to represent the base case.

We're going to look at alternative cases in just a minute.

You can see our base case assumption for the cost of capital was 12,

just a little over 12%.

And our initial investment was $227.7 million.

The corresponding NPVs for those assumptions were 708.42.

Right, it's the same as the base case.

That's what we computed in our previous lectures.

Okay, the Break Even values, in other words the Value for

the cost of capital, such that the NPV, right here, is zero, is 43.72.

But that should look familiar because the Break Even point for

the cost of capital is nothing more than than the IRR.

And as we discussed in the last lecture, on decision criteria,

you can see there's a lot of room here before this project looks bad,

at least on the cost of capital dimension.

For the initial investment, our initial investment would have to be closer to $1

billion before this project started to turn negative.

But what I like to do when looking at the break even [COUGH] estimates is

gauge how much room I have at least in a partial equilibrium notion.

Because remember I'm only changing one parameter at a time and

I'm going to emphasize that again in a second.

I'm going to look at how much breathing room I have until the project turns south

or becomes negative MPV.

And so you can see these purple arrows identify parameters for

which there seems to be a fair amount of scope for error.

A large margin for error.

Take a look at the PPE liquidation value here.

I'm assuming I'm getting $0.50 on the dollar when I liquidate all of my plant

and equipment at the end of the five years.

It would take a loss of 2253% before

this thing turned negative MPV.

I'd have to be dealing with some kind of, maybe, nuclear waste, right,

something like that.

But that's not the case here.

Similarly, you know, the initial unit price,

unless we're going to price this thing at $77, just over $77 per unit.

This project is positive NPV.

This is in contrast to some of the parameters for

which there doesn't seem to be quite as much a margin for error, right?

So if I look at my initial market size,

I'm looking to the initial market size of a million units.

But if there's not a lot of enthusiasm, say a little over half a million,

this project becomes NPV negative.

Again, holding all other parameters constant.

So, this break even analysis I think is a useful tool for gauging how much room

we have one dimension at a time before the project becomes value destructive.

And I want to emphasize that this is partial equilibrium analysis.

It assumes the parameters are independent, right?

I'm changing one parameter at a time and

in some cases that's an unreasonable assumption.

It's not to say the break even analysis is uninformative,

it's just that we have to recognize the limitation of that analysis.

Now let's turn to some comparative statics.

Comparative statics are going to quantify the sensitivity of the valuation

to variation in a parameter holding fixed all other parameters.

So, what I'm going to do is I'm going to look at how does valuation change

parameter variation from what I'll call a worst case to a best case scenario for

each parameter.

And again I'm going to focus on the cost of capital and the initial investment.

And by my estimates and discussion with treasury, right?

The sort of range of estimates for

my cost of capital vary from a low of 9.61 which is a best case scenario,

right, very inexpensive cost of capital, to a higher 15.01%.

Likewise, my ops people tell me that this the initial investment,

is likely to be $227 million, but

it could go as high as 284, it could go as low as 185,

depending upon short term variation in materials and labor, etc.

What I see underneath each parameter estimate is the corresponding NPV.

So when I change the cost to capital 15.01%,

the NPV of the project is $594 million.

When I Increase the cost of the initial investment from

227 to 284, the NPV moves from 708,

our base case, to 649.47.

So I actually like to look at this table, one, as,

to get some sense of the sensitivity of the valuation's specific,

to changes in specific parameters.

But I also like to use it as a gut check, a reality check to make sure that

the valuation varies sensibly with variation in the parameters.

If I had found that as I increase the cost of capitol

that the valuation here, the numbers actually went up.

I've got an error somewhere in my model.

So how did I get this?

I just used an Excel data table.

So these are inputs right here so all you do is highlight the matrix,

the NPV of the project is here, in the bottom left.

The parameters are in the top row.

And you choose a row input cell and enter the parameter cell.

That's it.

And I did this for all the parameters,

though I don't show you all the parameters here.

And it's just useful to move through them to make sure things make sense.

So, if I look at Initial Market Share, for example, right here.

What I see is a worst case scenario of 15%, or

our penetrations only 15% versus an initial penetration of 35,

sorry, an initial penetration of 35 in a best case scenario.

And you can see that the valuations increase with our penetration,

which makes sense.

We're selling more units.

And notice they actually increase by a lot.

We're going to see that that's an important value driver.

So, what is the elasticity of the valuation with respect to each parameter?

So, it's similar to what we did, but it puts a little bit more structure on

the changes in the parameter, so we can compare the sensitivity

of the valuation to different parameters a little bit more sensibly.

So what is the elasticity?

Well, it's just defined as the percentage change in the NPV divided by

the percentage change in the Parameter, or Delta.

Remember, this is nothing more than change.

What I've done is I've gone computed a cost of

capital that is 1% higher and 1% lower.

And I've computer the corresponding NPVs.

So, to estimate the elasticity of the NPV with respect to the cost of capital,

I simply looked at the delta NPV.

Sorry, hello.

Get rid of that guy.

Delta NPV over delta parameter,

delta in the cost of capital.

So I've taken this guy, 703.53 minus the 713.34,

and I've divided that by the difference by 12.13 minus 11.89.

Then I multiply that times the average of the two numbers.

So this is really just the average of the parameter values.

This is the average of the NPVs, and obviously these halves cancel one another.

But I wanted to put them there to emphasize that I'm just taking the average

of these two points.

And the reason I'm doing this is because the elasticity will vary depending

upon the direction in which we calculated.

Not by much, but by a little, so

this is one possibility or one approach for computing it.

And the elasticity I get is -0.69.

All right, so a 1% increase in the cost of capital.

A 1% relative increase, I should add, in the cost of capital is

associated with a 0.69% decrease in the NPV of the project.

And I can do that for all of the parameters.

Again I'm just showing you a few, but you can see a couple parameters start

popping out very quickly, such as the initial market size,

our assumption on the market growth rate, right?

Our initial market share.

Our assumption on cost of goods sold, right?

These, again, are going to correspond pretty closely to what we saw with our

break even analysis, and they're really identifying that the key value drivers,

the things we should really be focused on when discussing this project.

But, again, like our previous analysis break even and

our little scenario analyses.

Well, not scenario.

Sorry, our comparative statics.

Comparative statics implicitly assume parameters are independent of one another.

We're varying one parameter at a time, and sometimes that just doesn't make sense.

So that's going to lead us to scenario analysis.

What scenario analysis is going to do, it's going to quantify the sensitivity of

the valuation to variation along multiple dimensions.

We can can vary multiple parameters.

So what I've done here, going back to that best and worst case scenario,

is I said, what's the worst cast scenario for all of the parameters here?

And the best case scenario for all the parameters.

Now, that's not necessarily the optimal thing to do, but it's illustrative.

You might want to say,

what's sort of the worst case scenario if the economy goes south, right?

How is that going to effect each parameter?

It may not be the worst case for each parameter individually, but,

jointly, it might correspond to some sort of bad economic state of the world.

Having said that, I've computed what, I've set up each of these scenarios,

worst, base, and best, and I've just done a little scenario analysis.

And what I find is that, in the worst case scenario,

this project is over half a billion dollars negative NPV.

The best case scenario is $33 billion positive NPV and

relative to our base scenario.

Now, I wouldn't read too much into those numbers,

again, because I've taken the worst case scenario for

each parameter independently, and that doesn't make a lot of sense.

Again, what you want to do when you're constructing these best and

worst case scenarios is think, okay.

Let's think about a bad economic state of the world where maybe not everything

goes horrible in the project, but where some things go bad.

And maybe other things are good.

So, if it's a bad state of the world, maybe labor costs go down, right?

And it becomes cheaper to build the plant.

You want to think about these things and how parameters are related and

what really represents a plausible best and worst case scenario.

Now, how did I do this?

I just used scenarios in Excel.

That's one way to do it.

That's how I initially did it, but that's actually really inefficient.

A much better way is to set up a lookup table and

a toggle that you can just switch all the parameters between.

Much more efficient.

So, here's a question.

What else can we do with sensitivity analyses?

We can answer some important questions that are going to come up in discussions.

Here's one for example.

Imagine strategy wants to reduce the price by $30.

In order to increase the initial market penetration from 25% to 30% by their

estimates.

Does this make sense?

Well, we can answer that question.

In this table, what I've done is I've set up a little two way table.

I've got quantity, our initial market share, right here,

our initial penetration, and price here.

So these are all prices.

Here are all my market shares.

Here's our base case scenario in which we're assuming 25% initial market share

at a unit price of $200.

And marketing's asking what's going to happen if we change these.

Well, in particular they want to lower the price $30 from 200 to $170, and

they argue that's going to increase our market share upwards of, say, 30%.

Well, what does that mean from a valuation perspective?

Well, if we look in this box right here, we see that the NPV is going to be

somewhere between $776 million and $851 million.

So what's the answer to the original question?

It's a good thing, right?

NPV is going to go up relative to our base.

Here was our base.

Marketing says drop the price.

That increases penetration, and sure enough, valuations go up.

Marketing, on the other hand, is concerned about uncertainty surrounding the market

for tablets, and wants to understand if we can shed any light on that.

Can we provide some information?

So here's another two way table.

I've got market growth rates here.

And market growth rates there.

I've got market size assumptions here, initial market size.

Right? Here's our base case indicated by

the black outlined boxes.

So the initial market size was going to be a million units.

And we were assuming 2500% growth in that next year,

but what this picture shows is it shows that as that growth varies, and

as that market size vary, what happens to the valuation?

The cells inside are NPVs, and I've color coded them so

that green is at or better than our baseline, right.

You can see our baseline is all along,

I've held the baseline constant along the diagonal here, all 70842.

Yellow is positive NPV, but worse or

below the base case NPV, and red is negative NPV.

And I computed the NPV for

all 500 draws, and I plotted them here in a histogram.

So, I have here the NPV along the horizontal axis, and the y-axis

is the fraction of the 500 simulated scenarios corresponding to each NPV.

And what you can see up here is 77.78% of the simulations

are positive NPV, 22.2% are negative NPV.

So the takeaway is, it looks like a pretty good bet.

This project looks like a pretty good bet.

That said, I want to emphasize that

the parameters were drawn independently of one another, and that's not ideal.

It's going to lead to some implausible outcomes.

And it's not a particularly reasonable assumption especially when it comes to

certain parameter pairs, such as price and quantity.

That's just one.

Ideally you want to draw from some large, multivariate distribution,

but now we're moving way outside the scope of this course into evaluation exercise.

What I want to do is instead emphasize the importance of simulation analysis.

Even small scale simulation analysis,

say you just laid out by hand 10, 15 different scenarios.

Computing power's cheap,

compute the MPV and just look at how those MPV's look across the different scenarios.

All right. So, let's summarize this and

bring it all back together.

I really want to emphasis that no DCF is complete without a sensitivity analysis.

It's really an integral component of any evaluation,

any sort of capital budgeting or broad evaluation I should say.

It's going to help us identify where value is created or destroyed.

It's going to identify the key value drivers, where we should really

focus our time and effort In terms of our discussions for making the decision.

It's also going to help us quantify and assess our risk exposure.

How much can we lose?

How frequently can we lose it?

And it's going to help us understand the robustness of the profitability

of the project.

So, next time we're going to turn to a new topic, return on investment,

which is actually closely related to our decision criteria.

We're really going to hone in on IRR and look at some of its strengths and

weaknesses.

So, thanks again for listening, and I look forward to seeing you in the next lecture.

One thought on “Finance Case Study Cash Flow And Npv Table

Leave a comment

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *