Operating costs - solution
Here's the step by step build-up of the operating costs block. I find it particularly satisfying that in preparing this calculation block, we use our Core Modelling Skills in order from 1 to 5.
Step 1: Add a heading
I'm creating some space and adding a new Column A section header. It's helpful to keep the word END at the bottom of your calculation sheet so that if you navigate up and down in column A using Ctrl+up and down arrow, you will stop at the bottom of the calculation range.
Now we're ready to gather ingredients:
Step 2: O&M cost input
Use: Skill 1 - How to add an input
Note the label's specificity; we are clear that the O&M cost is annual, fixed and real. It may seem minor, but this helps model review later. I'm entering the amount in the default unit of the model so that I don't have to convert.
Step 3: Periods per year
Use: Skill 2: How to create a link
I already have this ingredient on my input sheet, so I will link straight to that to avoid creating a duplicate.
Step 4: Indexation factor
Use Skill 3: How to create a placeholder
I've already decided that I'm not going to get diverted into modelling indexation at this stage. I'm therefore going to add a placeholder. I'm not going to worry about calculating the placeholder to match the specifications in the case. It's a placeholder. It's OK for it to be wrong. When we model indexation, I'll replace this placeholder with a link to the correct calculation. It's always worth adding a note to the placeholder to remind yourself in the future why you left it here.
Step 5: Operating period flag
Skill 4: How to copy a link
I already have a link to the Operating period flag in row 37:
I'm therefore going to copy that link.
Step 6: Write the calculation
Skill 5: How to create a calculation
When writing the calculation, I'm paying particular attention to column anchoring the two constants.
I know that I'm going to sign switch the calculation before adding to the income statement, so I'm adding "POS" to the label straight away.
Step 7: Sign switch the calculation
As this is the first cost or outflow that we're modelling, I'm copying the sign switcher from the Tmp sheet. The more calculations that we sign switch, the more of these will be around our model, making it quicker to find one to copy.
Sign switching is a simple copy and paste operation. You'll need to refresh the page with Shift+F9 if you are in manual calculation model.
Step 8: Link to the financial statements.
We're now ready to add O&M cost to our income statement.
We are again using Skill 2: How to create a link.
Once it's linked, you'll need to clear out the yellow placeholder shading. Select the row and use Productivity Pack keystroke Ctrl+Shift+c to clear the cell shading.
And that's Operating Costs complete.
A full recalculation of the model will show that we have new changes on the Output track (as expected) and that our balance sheet doesn't balance (also as expected).
In the next chapter, we'll look at the working capital implication of our Operating costs and wire up the cash flow appropriately.
Before moving on, remember to go through the Section Completion Checklist.
Download the model completed to this point:
To obtain the worked example file to accompany this chapter buy the financial modelling handbook.
Comments
Sign in or become a Financial Modelling Handbook member to join the conversation.
Just enter your email below to get a log in link.