Sign in or start a free trial to avail of this feature.
7. Risk-Weighted Cashflows
With revenue and production costs created, we can now calculate our project cashflows and the estimated return on initial investment.
- Simply sum all the cells that contain cost or revenue entries
- Copy and paste this formula for every year of annual cashflows
- Risk-weighted cashflows multiply the commercial plant cashflows by probability of success
- We can use an IF function to isolate the commercial plant cashflows
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
CTRL + SHIFT + →: Select all cells within data region
SHIFT + →: Select next cell
ALT + E , S , F: Paste formulas
We're now ready to calculate our annual cash flows from the model. To calculate the annual cash flows for each year, we simply add the reference plant cost, the commercial plant cost, the revenue and the production cost. So I'll write equals, select production cost, revenue, commercial plant cost and reference plant cost. Then I'll copy this formula for the remaining cells.
This calculation appears to be working correctly. However, we're missing an important constraint, which is the 10 year period at which we must return five times the original investment. If it takes two years to build and test the reference plant, and another year to construct the commercial plant, then we can only have a maximum of seven years operating life that will contribute to the return calculation. Here, we're showing nine. So let's return to the control panel. And off camera, I let a new named cell called target time duration. Now I'll set this to 10 years. Now I can return to the model and apply this constraint. So I'll write an if function. Double check if the current year is less than the ref plant start year, plus the target time duration.
If it is, I'll return my annual cash flows, which are production cost, plus revenue, plus commercial plant cost, plus reference plant cost. And if doesn't satisfy this logical test, I'll return zero. I'll then close the bracket and copy for the remaining cells.
And as you can see, we now have this constraint applied with two cash flows at the end, chopped off, because they're outside the ten year time window. With this constraint now in place. Let's calculate our project cash flows. So on the control panel, I'll simply write equal sum and then sum, the annual cash flows.
And when I do this, I get a huge number of 1.1 billion. To calculate the return on investment. I simply divide this number by the reference plant cost. But the sign must change on the reference plant cost because it's currently a negative number. So I'll write minus ref plant cost. When I do this, I get a huge return on investment, of 77 times. Even allowing for the fact that taxes aren't yet included. This number looks very large. Let's return to our influence diagram and see if we've left something out of our calculation. And if I look at the project cash flows intermediate variable. I can immediately see that I've left out the probability of success, which explains why our project cash flow number is so big. In our model, let's now create a new line called risk weighted cash flows that will take into account the probability of success. (silence) From our project cash flows equation back in the decision tree lesson, you might remember that the initial 15 million had to be spent but the remaining cash flows will need to be multiplied by the probability of success. So in our model, I'll write an if function, that checks if the current year is equal to the ref plant start year.
And if it is, I'll simply take the annual cashflow and if it isn't I'll simply multiply the cashflow by the success probability.
Then I'll close the bracket and copy for the remaining cells.
(silence) And I now I have a set of cash flows that looks much more reasonable. I'll return to the control panel and then replace the formula for project cash flows with these new risk weighted numbers.
And this gives me a return on investment of 10.7 times which is much more reasonable. To decide if this decision is yes or no. We simply write an if function that checks if the return on investment is greater than the target return.
And if is, I'll return yes.
And if it isn't, I'll return no. And given that 10.7 is greater than five, the decision is currently yes. With the first iteration of our model now complete, in the next lesson, we'll begin to generate some insights from the model, using a new tool called the tornado chart.