If only I knew – financial modeling lessons from a (slightly) grey-haired project finance advisor.

I love project finance. Maybe it’s the engineer in me, but the combination of a physical asset and a highly-structured and complex investment excites me. Add to that the importance of infrastructure and energy to our daily lives, and I am hooked.

Well, that’s part of it. Perhaps the biggest reason (or the geek in me) is that I fell in love with the financial modeling aspect. I didn’t actually touch a financial model in my first year of project finance, but once I did I have never looked back. Other than to rue what I wish I knew when I started.

Since those early days I have trained over a thousand analysts in renewable energy, infrastructure, and mining project finance. Some of those being my colleagues where I worked, and others in training programs that I have delivered globally, or in my class in renewable energy project finance modeling that I teach at Columbia University with Daniel Gross.

Having grown two relatively large financial modeling teams, I also have a view of the type of person that makes a good analyst (for you hiring managers), but that will come in my next blog. For now, I want to focus on my 5 most important lessons for the new analyst, that I try to teach.Here goes…

Lesson 1: Understand the transaction and plan before you start modeling

It’s a common trait of a new analyst to jump into Excel and get started, showing their new found speed. Perhaps they just completed a financial modeling training course and want to show off their favorite shortcuts. But don’t, please don’t. Make sure you read the information memorandum, or other available information first. Who is investing and why? What are the possibly financial structures and which ones are more likely?

Plan the financial model out. Sketch out the structure and the contents. Don’t figure this out later as it may require wholesale restructuring of your model. Your boss (or client) reviewing a constantly changing model structure will not be your biggest fan.

Lesson 2: Best practice financial modeling does actually matter

Best practice is mostly about making a model clear and easy to understand, with the flexibility required for the deal. So keep it simple and follow the common rules widely-recognized in the industry. And for what it is worth, of the more technical of these, the ones that had the most profound impact on me were:

• Learning how to create scenarios and sensitivities properly (see our webinar here)
• Any calculation limited to a specific timeframe or triggered by an event deserves a binary flag – see a course sample on this topic here
• When you have a volume to record (or measure) over time, build a control account – see a course sample on this topic here

It goes without saying that the model needs a great summary page, so an investment decision can actually be made…

Lesson 3: Best practice only goes so far….check your work….and use my checklist if you don’t have one

Garbage in garbage out. Check your work, after EVERY SINGLE LINE. Going back to ‘fix something tomorrow’ will cause you even more pain tomorrow. Fix it now.

These are the guidelines that I wrote down and stuck to my desk as an analyst:

• Press F2 in every row to review the cell precedents, is the cell correctly linked
• Check the beginning and end of the row (do the values stop and start at the right time)
• Check the trend of the line, do the values increase / decrease over time as expected? Create a graph if needed
• Change the inputs:
  • For values directly from an input source:
    • Make the input value negative, zero, and positive (if feasible) to check you still get the right answers
  • For dates directly from an input source
    • Make the input date the 1^st day, middle day of, and the last day of the period
  • For cash flows dependent on other cash flows (i.e. Cash available for debt service is dependent on revenue and costs etc)
    • Consider if this ‘input’ could be negative, zero, or positive
• Run boom/bust scenarios
• Reconcile your results to another model, if available
• Print the outputs and review them….yes, I am old

You will still make errors, we are human. I will never forget the worst error I ever made. Project finance models are complicated. So get your model checked!

Lesson 4: You can run comparisons between versions of a spreadsheet!

This one is surprisingly not well known, particularly in the US. You can run a compare to check every formula and output change between two versions of a model. How every company doesn’t have this I don’t understand.

I use this myself for my own models, because I often forget all of the changes I may have made, and I want to know exactly how much the results changed in each step. I may be a cynic, but I want to see the comparison rather than trust my analyst who had 3 hours sleep the night before and changed a model with over 3,000 formulas….sorry.

If you want to know more about how to run comparisons, please contact me.

There are of course other add-ins that are useful, such as for tracing dependents and precedents, including Macabacus, or BPM Traverse’s tool now under Modano.

Lesson 5: Ask questions, beyond what is solely within the mod

I have met many analysts who have modeled a number of renewable energy projects, but don’t know what a capacity factor really is (other than plugging it into the model). Or perhaps what a dispatch curve is, why capacity or availability payments make sense, or what P50 and P99 forecast really represents. Or they read the legal documents scanning only for the financial terms required for the model. You need to understand the broader picture to be a good financial modeler and certainly good within project finance

If you get a chance, visit the site of the project  context is a wonderful thing.

Summary

You may read all of this, and still make the same mistakes I did (I only hope not quite as many!), but we do learn best from our own mistakes.

One way we can help at Pivotal180 is to provide training that goes beyond just modeling. We provide deal training, helping you understand all of the core documents and concepts behind your renewable energy, or infrastructure project finance deal. Take a look around our courses here if you would like to know more.

Happy modeling!