Introduction
This page introduces various cases that I have created to complete project finance cases. I have been working on the cases for a while after a nice man from the Philipines called me years ago and told me how the world bank put him in a room for a six hours and asked him to completely fill out a financial model. I felt bad for him and we tried to practice for the torture chambre.
I have now tried to complete many cases (what a psychological I mess I must be to do this). I have tried different strategies, used different formats and used newer excel techniques. With Hedieh I have even watched how AI can help with the model.
Understandable Comments
Here is one comment: “I’m so keen to get this new job but been modelling corporate finance and not energy for the past few years and not written an exam in 10 years! So I’m stressing out more than a teenager before high school finals.”
I am recently invited for a 1-hour excel modelling test by an Infrastructure PE firm. The model test is based on a typical infrastructure asset acquisition. In total you will need 1h30 for the test which will comprise ~10min briefing call at the start, 60mins to complete the test and ~20min debrief call at the end via video conference (Teams). I have about 1 week to prepare and I’m feeling very nervous as I’m unsure how I should prepare for it. I have done some PF modelling from my university degree but I’ve never done any acquisition/ valuation model.
I’ve just failed a simple project finance modelling test (toll road). I failed mainly on debt sizing (DSCR and leverage ratio).
I really enjoyed watching your “Model test from hell” session. Your relaxed attitude, not talking the financial pro talk really helps. So please continue, swear, show your frustration as we all would in the same situation and share your experience. Is it possible to get the model at it’s start point ..? I like to build it the same way by watch your video.
Organised Resources
If you want some examples I have collected:
If you send an email to edwardbodmer@gmail.com I will send a fairly large collection of exams that includes a lot of big companies including investment banks, private equity firms, energy companies and others.
Here is some advise I gave, you may disagree.
- An acquisition on a project only means that you have one period of CapExp. By this I mean that you can apply all of the project finance modelling you have learnt in university but think of the acquisition price as one period. Do not worry about anything like goodwill.
- When Hedieh and I talk I, we think about the people making the evaluation. I think they want to see that you can make a quick and clearly structure spreadsheet. Please, the university profs are generally really bad at this. You need a clear timeline, a clear separation of project IRR before any financing and a very simple financing to get equity IRR.
- Please do not get too fancy with something you may have seen from Mazars or F1 F9.
- Please also practice doing some basic stuff really fast and if you have a question about something that seems complex, skip to the next item.
I continually update the list and the organisation of in the resource library. I have about 150 cases now separated as follows.
.
Each folder has the case and most of the folders have example files. Here is the folder with completed cases.
.
Excel Basics
Don’t get too fancy with excel and by all means do not use my generic macros or read pdf or any tools. Get used to making models with no add-ins and delete the rows at the right. Use SHIFT, CNTL, –> and then CNTL, R. If you think it is possible that the test will be in a different language, try to practice some excel equations — IRR, NPV, LOOKUP — in that language. You need to get through the excel quickly so they can look at your approach, your ideas, and your presentation skills.
I doubt that you will be able to access the internet and download the files from your torture chamber. But you want to make sure you can operate in excel very quickly so that you can concentrate on the structure of the model and incorporate assumptions that they give you. So, here are some things you can do to make your excel go much faster. I love macros, but for the exercise and for your interview I do not think you should make any, it will waste time and probably reduce your score. A lot of people are anti-macro and don’t get to fancy with any macros.
Cut off the end of the sheet with SHIFT, CNTL, 0 and then you can SHIFT, CNTL, –> and after that press CNTL, R. Please try this and get really fast at it. Learn how to select the entire sheet, press the F5 or CNTL, g and then de-select the text thing. Then you can colour inputs in the sheet. Get really fast at this. The horrible people who made your test may not know this one, but use the EIS short-cut in different ways and do this at lightening speed. Also, make sure you can uses SHIFT,CNTL, 1 for formatting with a comma, SHIFT, CNTL, 2 (@) for time, SHIFT, CNTL, 3 (#) for a date, SHIFT, CNTL, 4 ($) for currency, SHIFT, CNTL 5 (%) for percent.
More important than the short-cuts is to make a well structured, flexible, accurate and transparent model (the FAST letters). Do not worry about silly rules, just keep these ideas in the back of your head somewhere. Flexibility is illustrated by learning how to make time lines in seconds with a differentiation between the pre-COD and post-COD periods. Accuracy is covered by using TRUE/FALSE switches that assure that the balance sheet balances among others. Structured means that you should first compute the project IRR pre-tax; then add taxes; and put the financing in only after the after-tax project IRR is computed. Finally, transparency means that you can see everything with simple formulas using F2 and that you can find the source of the inputs in driver columns to the left and then use the CNTL [. Use the EDATE function and make sure the time line does not use complex formulas. You can see this in the video and you can look at the top of the sheet to see how to do it fast. Start with a periodic time line and don’t be afraid of dates. In this screenshot you can also see that the pre-tax IRR is computed. If the pre-tax IRR is below the interest rate, you should be worried that something is wrong; maybe the person writing the exam made some mistakes. The excel file attached to the button below demostrates a case that was developed for an excel interview case that applies the FAST modelling standard. Note how the inputs are on a separate line, how there is a separate page for flags and how the scenarios are set-up. The test below is really horrible where they seem to be testing your ability to be a bureacurat more than sensible modelling skills. But I have included the example along with a solution.
.
PDF File with Instructions for Creating a Bureacuratic Model Using the FAST Modelling Standard
.
Excel File with example of FAST Standard Applied to Project Finance Model for Solar Case
.
The final part of the model includes a cash flow waterfall and calculation of equity cash flow that includes the initial sources and uses of cash. You can then also derive the DSCR implied by the debt to capital ratio. This last part of the financial model is illustrated in the screenshot below.
.
Fundamental Crimes
Even though some of your prospective employers may disagree, if you want to do things really fast, don’t commit the following crimes:
- Hard-coded numbers after assumptions in the model
- Not putting driving factors in the left column
- Putting the forecast of Profit and Loss and Balance Sheet at the beginning of the model
- No explicit debt and cash section that connects the financing with the balance sheet
- Meaningless colours that do not provide a guide
- Use of formulas that are too long (a life sentence crime)
- Use of too many different excel functions (other than LOOKUP, INDEX and AVERAGEIF)
- Formulas that are not the same across the column
.
Fighting modelling arrogance is a big problem with modelling in general and with modelling exams in particular. Arrogance of modellers results in poorly written modelling exams and it results in people failing exams. At the outset I admit that I have been arrogant about modelling in the past. I thought I had a good style and I was reluctant to look at other modelling methods. I am probably still arrogant but I have reviewed very many models over the past 40 years and I have tried to steal methods from others. I have also tried differnt methods which have not been good and finally arrived at methods that seem to be good. For example in consoldiating projects I have tried different methods and finally arrived at a method with a template and a couple of simple macros that I think works well. I have had the same experience of doing things badly and then trying to improve with debt sculpting, tax partnerships, circular references, conversion of models from monthly time lines to semi-annual time lines, writing macros and many other subjects. The final way that I understood that I have been arrogant is to try and complete interview exams on a wide variety of cases. I have seen how important it is to have an idea of what the case objectives are and to re-struture the model inputs.
On this page I include some discussion of modelling arrogance of people who come out of university and may have a year or two of modelling and then take one of the modelling exams. If you are somebody like this I argue that you should practice simple examples over and over again and you should figure out how not to waste time on modelling formats. I have included some examples that you should be able to finish very quickly and that you should practice even if you consider yourself (arrogantly) to be a good modeller.
I also discuss my theory with respect to the arrogant people who construct the exams (which is in my imagination). I show examples of how the exam writters have apparently not tried their own write-ups to see if it is possible to complete the exams.
Arrogant Modellers
When teaching courses I often run into arrogant modellers. I have this feeling that they see me and think that such an old man cannot tell us anything about modelling. In the case exercise, completed excel file and the video below I try to show you what is important in completing a model. The case below seemed intimidating and had terms like MoM for money multiple where the total inflow to equity is divided by the total outflow of equity capital or HtM which means hold to maturity. At the end you as usual have to compute the equity cash flows, the equity IRR and the cash flow statement. The case looks stressful and will take a while but if you can work through it quickly I think it is good practice. I show the case excerise connected to the first button below and then the completed exercise attached to the second button. This is followed by a boring three hour video that goes through the exercise. An illustration of the InputC page that drives the offshore wind portion of the model is shown below.
.
.
.
.
.
.
Excel Test
Thw two files below are files I recieved from somebody about excel exercises for financial models. I found the exercises challenging and pretty comprehensive. The first file below is the file that is not complete and the second file is the file I have completed (I am sure I made some mistakes). For me, while this exercise seemed to ask for long and complex formulas, it demonstrates instead that using a whole lot of TRUE/FALSE flags is essential. It also shows that you can find duplicates with COUNTIF and it makes you work through dates and charcters.
.
.
Excel File with Completed Exercises Made By Me With a Lot of TRUE FALSE Flags and LOOKUP Functions
.
……………………………………………………………………………………..
.