5. Make Your Model Responsive
Building flexibility into the model will allow us to control everything from the input panel, even time-series data.
Make your model responsive
- All changes to the model parameters should be completed in the control panel
- More difficult to do complete with dates and time periods
- Easiest method is to use IF function to check against current year
TAB: Finish cell name when writing formulas
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
SHIFT + →: Select next cell
ALT + E , S , F: Paste formulas
CTRL + Spacebar: Select full column
ALT + O , C , W: Manually chance column width
We're now ready to start building our model. For the reference plant cost, I know that the cell name is equal to REF_PLANT_COST so I'll select it, press TAB and then press Enter. And this gives me $15 million dollars. However, entering numbers in this way raises two problems. The first is the sign of the number. A cost should be a negative cash flow, but in the current model, this is appearing as a positive number. To solve this, we could enter the formula and write, equals minus reference plant cost or we could simply change the parameter to be a negative number. I like to keep my formulas as simple as possible so I'll change the parameter to be a negative. So I'll go back to the control panel, and change the reference plant cost to minus $15 million. And off camera, I'll also change the commercial plant cost and the unit variable cost. Now when we return to the model, you can see that the reference plant cost has switched to a negative number. The second problem with entering formulas like this is that the model isn't responsive. Say I wanted to change the start year to 2015. Then I'd have to delete the current formula, go to 2015 and write equals ref plant cost again. This can become pretty tedious over time, particularly if you have lots of variables that need to be changed in this way. A better solution is to write a formula that allows us to change the start date of the reference plant cost back in the control panel. This formula is pretty simple to write. It will simply check if the current year is equal to the reference plant start year. If it is, it will return the reference plant cost. And if it isn't, it will return zero. So I'll go back to my first cell. I'll write equals IF, and the logical test will be, if the current year is equal to the ref plant start year.
Now back in my control panel if I decided to change the reference plant start year say to 2017, now when I return to my model you can see that the reference plant cost has moved to the new start date. Close the bracket and press Enter.
I'll just switch this back to 2014 for now. Now let's move on to commercial plant cost where we'll take the same approach. The start year for the commercial plant cost would be the start year for the reference plant plus the number of years the reference plant will be in operation. So I'll write equals IF, the current year is equal to open bracket, the ref plant start year, plus the ref plant duration. I'll then close the bracket and if this is true I'll return the commercial plant cost, and if it's not true I'll return zero. I'll then close the bracket and press Enter. I'll then copy this formula for the remaining cells. And the commercial plant cost appears correctly under 2016. I will need to increase the width of these columns so this number can be seen. So I'll select it with Ctrl + Spacebar, select the remaining columns and then Alt + O, C, W to manually adjust their width. And I'll adjust the width to 11.5 And now we can see our commercial plant cost. When we now change the reference plant date in our control panel again to 2017, both our numbers should now move in unison. And as you can see they do. While this approach takes more time initially to set up, creating flexibility in your model so that everything can be done from the control panel saves huge amounts of time in the future. If you have a complex model, the time taken to go back and retrofit for flexibility can be quite substantial. It's much better to build in flexibility at the start despite the initial time costs. Next up are revenues and production costs, which I'll complete in the next lesson.