6. Shorten Formulas with New Variables
Don't be afraid to create new variables to keep your formulas short and more readable. They are particularly useful when you have multiple inputs in a formula that can easily combined.
Shorten formulas with new variables
- Long formulas should be avoided at all costs in Excel
- They are harder to understand and debug, particularly for new users of the model
- Creating new variables can help shorten formulas substantially
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
ALT + I , R: Insert row
CTRL + F3: Open name manager
ALT + N: Create a new name
SHIFT + →: Select next cell
CTRL + SHIFT + →: Select all cells within data region
ALT + E , S , F: Paste formulas
F2: Jump back inside a formula
In the previous lesson, we used formulas to add flexibility to our model allowing the start date and duration of projects to be changed directly from the control panel. In this lesson, we'll apply more complex formulas to maintain our flexibility on calculating revenues and the production cost. Revenues will start the year after the commercial plant construction starts. And this corresponds to cell E9. Unfortunately, we don�t have a variable for this construction duration so I'll add one to our control panel. So I'll go to my Control Panel, I'll create a new row and I'll call it construction duration and I'll give it a value of 1. I'll then name this cell, Construction Duration. I can now use this cell and my formulas for my model. So going back to revenues, I'll write an IF statement that says if the current year is greater than or equal to the ref start year plus the ref duration, plus construction duration, then it will return revenues, which are plant capacity multiplied by market price. Otherwise, it will return zero. Then I'll copy and paste for the remaining cells. Unfortunately, the formula for revenue is a bit unwieldy particularly because we add the plant start year, the plant duration and the construction duration in our logical test. 10 00:01:53.25 --> 00:02:03.15 I am going to simplify this formula by creating an intermediate variable in the control panel and the intermediate variable will be called operation start year.
So below the inputs off camera, I'll create a panel for intermediate variables. And the two intermediate variables will be, operation start year and operation end year. Operation start year will equal to the plant duration plus the plant start plus the construction duration. And the operation end year will equal to the operation start year plus the operating life. And off-camera, I'll name these two cells. So we now have operation end year named, and operation start year named. We can now go back to our model and change our revenue formula. Currently, we have revenue starting at the correct time but the revenues are not stopping at the end of the operating life. Here, they're stopping at the very end of the model. So we'll need to change our IF statement to have two logical tests. And to do this, we'll use an AND function. Let's go back to our first cell and I'll rewrite the formula with an AND function and our new intermediate variables. So I'll write equals IF and then I'll write AND so we can write two logical tests. And the first logical test will be, if the current year is greater than or equal to the operating start year. And then the second criteria will be if the current year is less than the operating end year. And if these two logical tests hold, I'll show revenues, which are plant capacity multiplied by market price. And if these conditions don't hold, I'll just show zero. I'll close the bracket and press Enter. When I copy for the remaining cells, you can now see that we have revenues starting on the correct year and running for seven years which is the operating life. If I change the operating life, let's say to nine years, our model should now update accordingly. And as you can see, we now have nine years of revenue. The total production cost will actually be the exact same formula except the market price will be replaced by the variable cost. I'll then move to a production cost cell and copy with Ctrl + V. I am going to jump back into this formula and select it by holding Ctrl + Shift and the left arrow.
I'll then press F2 to jump back into the formula and replace market price with variable cost. I'll then copy this cell for the remaining years. And as you can see we now have production cost and revenues calculated correctly. In the next lesson, we'll calculate our annual cash flows and generate some insights from the initial outcomes in our control panel.