Sign in or start a free trial to avail of this feature.
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 when calculating revenues and production costs.
Revenues will start the year after the commercial planned construction starts. 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 create a new role, and I call it Construction Duration.
And I'll give it a value of one.
I'll then name the cell Construction Duration.
I can now use this cell in 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 at plant capacity multiplied by market price.
Otherwise, it will return zero.
Then I copy and paste for the remaining cells.
Unfortunately, the formula for revenues is a bit unwieldy. Particularly because we add the plan start year, the plan duration, and the construction duration in our logical test. I'm going to simplify this formula by creating an intermediate variable in the control panel. An 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 be equal to the plant duration, plus the plant start, plus the construction duration.
The Operation End Year will be 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 re-write the formula with an and function and our new intermediate variables. So I 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, that's your 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'm going to jump back into this formula and select it by holding control shift and the left arrow. And then copy it with control C and then escape.
And then move to a production cost cell and copy with control V. I'll then press F2 to jump back into the formula and replace market price with variable cost.
I'll then copy the cell for the remaining years.
And as you can see, we now have production costs 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.