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 in which we must return 5 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'll add a new name cell called target times duration and 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 that will check if the current year is less than the ref plant start year plus the target time duration. And if it is, I�ll return my annual cash flows, which are production costs, plus revenue, plus commercial plant cost, plus reference plant cost. And if it 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 10-year time window. With this constraint now in place, let's calculate our project cash flows. So in 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 in the reference plant cost because it's currently a negative number. So I'll write minus, ref plant cost. And 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 have left something out of our calculation. And if I look at the project cash flows intermediate variable, I can immediately see I have 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. From our project cash flow�s equation back in the decision tree's lesson, you might remember that the initial 15 million had to be spent, but the remaining cash flows would 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? 25 00:03:44.25 --> 00:03:53.19 And if it is, I'll simply take the annual cash flow. And if it isn't I'll simply multiply the cash flow by the success probability.
Then I'll close the bracket and copy for the remaining cells. And 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 the 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 it is, I'll return Yes, and if it isn't, I'll return No. And given that 10.7 is greater than 5, 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 a Tornado Chart.