In this episode of project finance storytelling, I tell you the story of Joseph, a financial modeler working for a private equity fund investing in wind power projects. through this story, I will tell you how to:
- change the model structure to have tariff as an output by defining a target equity IRR.
- create a simple goal seek macro to make the process dynamic
Joseph just got back from vacation. He’s been away for almost a month. Although he had a lot of fun during his holiday, but now he’s glad to be back. He loves his job. He works as a financial modeler for a private equity fund investing in wind power projects.
While he is having morning coffee, he goes through his email and sees an urgent email from Joshua.
The first thought that comes to Joseph’s mind is: what is he talking about? What is this project?
he took another sip on his coffee and remembers:” Ok, I remember now! but we fixed the tariff a long time ago and I completely removed the tariff setting mechanism from that model!”
So he analyzes his options.
Option 1: I take the old model with the tariff setting mechanism and update it
Option 2: I can built-in the tariff setting mechanism into the new model
He remembers all the different changes he made to that model and it will be much harder and time consuming to make all those changes to the old model than rebuilding the tariff setting mechanism into the new model, so his decision is made. He goes with option 2.
He takes a deep breath and another sip on his coffee, He pulls up his down-tempo playlist and starts to work on the model.
Step 1: on top of the summary sheet he creates a new section and labels it as “Tariff Setting mechanism”. His aim is to set the tariff based on a target equity IRR. So target equity IRR is an input and tariff will be an output of the model.
He creates a hard-coded input for Target equity IRR and copies the calculated equity IRR from the “return and ratios” sheet. He needs another hard-coded value which is the tariff which will be determined using the “Goal Seek” function under Excel. So, he creates that but color-codes it differently so that the user knows that this is an output of the macro and not an input.
Step 2: He plugs-in a number for tariff for now and makes the link to the model. Because he already had an input for tariff in his “Inputs” sheet. He will just replace that input or make a link so that the model uses the output from the macro as tariff.
Step 3: Now he needs to record a goal seek macro. but before he does that he will do another adjustment. He normally doesn’t like to do a goal seek on IRR. IRR can be quite unpredictable, so just to be on the same side, he will use the NPV function using the target IRR as a discount rate and creates a goal seek that sets the NPV to zero by changing Tariff. This way, it will be exactly the same as if he was setting the calculated equity IRR equal to target equity IRR.
He will then press record on Developer tab to record the macro and then does the goal seek:
-
On the Data tab, in the Data Tools group, he clicks on What-If Analysis, and then clicks Goal Seek.
-
In the Set cell box, he enters the reference for the cell that contains the NPV.
- In the To value box, he types zero
-
In the By changing cell box, he enter the reference for the cell the tariff.
He stops recording the macro. He just needs to do a minor change in the code.
- He will create name ranges for the cells containing “NPV” and “Tariff” and give them a name and then goes back to the code and replace the cell references with the name ranges he defined for these cells.
Sub Goalseektariff()
' Goalseektariff Macro
Range("Equity_NPV").GoalSeek Goal:=0, ChangingCell:=Range("Tariff")
End Sub
- He has a copy and paste macro that he wants to run while the goal seek is running, he will just call that macro within the code.
Sub Goalseektariff()
' Goalseektariff Macro
Range("Equity_NPV").GoalSeek Goal:=0, ChangingCell:=Range("Tariff")
Call UsesCopyPaste
End Sub
Now he can copy the code to different buttons and switches and the macro will not be limited to this worksheet only.
He does a different rounds of test by changing different parameters and see if the model works properly:
Test 1: he increases EPC and expects tariff to go up —> OK
Test 2: He decreases Opex and expects tariff to go down —> OK
Test 3: He changes the debt terms and check if the tariff changes accordingly —> OK
Now, he feels comfortable and excited to go for tomorrow’s meeting.
