This web page will give you some ideas about how to deal with an amazingly dreadful thing that seems to be happening these days, where you are put in a room (a torture chamber) and you are given some instructions to complete a model. I am starting to collect some of your stories. Here are a couple of examples: The world bank gave my friend instructions to build a complex model and put him in a room for eight hours. Somebody else was given a really old computer with no internet access. In Germany a friend of mine had to find the IRR im deutsch even though he had always used an English keyboard and English formulas for IRR and NPV. Sometimes the exams will have nothing to do with making a financial model but test your logic abilities (I would fail this completely). I cannot believe that you have to go through such hell to get a job and it makes me feel good to be an old man who has not had to go through such a hellish experience (I had to make sure that I had a proper haircut and socks). So I am giving you some ideas if you have to go through such an experience. On this page, I have made a little exercise file for you to work through and I have also included a video in which I give you some ideas about how you can manage one of these horrible interviews without having a nervous breakdown.
.
I have some additional corprorate model case and LBO cases at the link below;
https://edbodmer.com/hellish-corporate-modelling-interviews-to-make-you-nervous/
Some Examples of 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).
First, 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 am now sending you some of the cases in Chapter 1. But please do not worry about the complex stuff.
.
Strategy for Any Modelling Test
You may think that you learned some excel in university and you can make a pretty good model. You may think that you can then focus on the tricky valuation issues. But if I were judging this stuff the first thing I would want to see is a really good structure with operating inputs clearly laid out and time lines and most of all very simple formulas. Please look over some of the files to make sure you loose your arrogance about learning modelling in school and get to the real world.
First, work through the assignment and find anything with time elements. Put all of the time data at the beginning of an input sheet. Unless the company is really mean, the tests are typically structured with annual data (If they explicitly ask for other quarterly or monthly data, don’t freak out). If you show how you can efficiently set up time lines, this should be a big part of the assessment by the company. Unless they are complete jerks, they are not going to ask for you to put mini-perms and re-financing assumptions into the model. Second make sure you separate operating assumptions from the financing assumptions. Again, get the operating assumptions into the model and work through the revenues, expenses, and capital expenditures (they may ask for working capital). Demonstrate that you can set-up a model with operating analysis and compute the project IRR which is from EBITDA and Capital Expenditure. I suggest constructing the financial statements at the end (if you are worried about time, you can set up all of the titles first). They may include taxes and you can work on this next along with depreciation. Do not get too fancy with depreciation and even in the U.S. they will typically give you straight line depreciation. When working on this part of the model before financing, make a nice and clean model and don’t show off. They want to see if you can efficiently structure a model.
The financing section may have some tricks like sculpting where they try to see if you are really advanced. But before getting worried about advanced financing details, make sure you set-up the financing elements of the model. Any debt financing will include:
- Determining the debt size. They will often give you a debt to capital ratio, but they may give you a DSCR applied to cash flow. One way or another you should have a uses and sources of funds that works through the debt sizing.
- The second item is funding. If they give you an annutal model and one period of construction, you do not have to worry about this. Sometimes they may give you a test with more than one construction period and you have to work through how the debt is financed.
- The third item is debt repayment. They generally give you the debt tenure and they may put the sculpting here.
- The fourth item is the interest rate that can include fees. You need the debt balance to compute this, meaning that the interest and fees should be included after the other debt components are developed.
Once you have the debt schedule, the EBITDA (and maybe taxes) can be used for a cash flow waterfall. The equity cash flow is the amount of the equity investment compared to dividends at the bottom of the waterfall. The examples below generally follow this framework.
.
.
.
Some Excel Basics for Your Interview From Hell
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 Not to Commit in any Model
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
.
A Tough Excel Challenge with Financial Modelling Aspects
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
.
.
Example 1 – Standard Solar Example
Here is one example of an exam. I would think a solar example is the most obvious way to have an exam as it can be completed in a pretty simple way. They tell you that when introducing the model, you should write a short presentation (e.g. for an investment committee.) They may ask for the financial model should include projected income and cash flow statements. But as explained below, put these at the end. Then may want something about:
· The equity returns of the project
· The cost structure of the project
· The risks of the project from an equity investor perspective
If you would like to try this exam yourself, click on the button below and download the exam file. If you want to really learn, do not cheat and go to the finished file below the video. Before or after trying the exam you can watch the video to get some ideas. Remember that there are many ways to do this and my suggestion is not necessarily correct. The video I made to work through this example is just below the button.
.
.
Excel File with Exam for Solar Project with that Contains Given Inputs but Not with the Final Answer
.
I have made an answer to the project in a video using the file that is linked to the button below. I have not used any add-ins and I have based my assumptions from the screenshots shown below. I think you need to start by reading through the case and then begin with the timeline. The button below includes the example of the case.
.
Excel File with Case Study Exercise on Solar Project for Interview Example Used in Video Explanation
.
A second example where I work through more strategic issues associated with getting the inputs ready and structuring the model is attached to the two buttons below. This example has sculpting and you can fix some errors. It also has taxes and a circular reference. The main idea in this example is to do a good job in setting up the case. A test that includes sculpting and taxes with a solar example is shown below. I used this case to show how you can use the generic macro file to solve the cases if you are allowed to access your own tools.
.
.
.
Example 2 — More Complex Example with Bond Financing
.
This bond financing case is a pretty advanced exam. To do it I think it is essential to create a sources and uses of funds analysis. When you first read the case you head may be spinning a bit, but don’t worry and make sure to always begin with the time line and the operations. You can find what I define as crimes at https://edbodmer.com/high-crimes-in-project-finance-modelling/. I have made a video about this one because my friend Jose asked for a bond financing case. The instruction file and the excel file for this case is attached to the buttons below. The first button is attached to test write-up with the bond financing. The second button includes the completed spreadsheet.
.
PDF file with Interview Test using Bonds in Project Finance; Test Solutions is in Video Below
.
Excel File with with Case Study for Interview with PPP and Bond Financing and Monthly Cash Flow
.
.
Some Hints:
- Start with timeline inputs and development
- Put in operating cash flow first — Merton Miller
- Make separate categories for the debt in input page with amount, draws, repayments and interest rate etc.
- Make a summary sources and uses of funds after the project IRR and understand the project IRR
.
.
Example 3 – Toll Road
Here is an example of a test for a PPP project. The person who wrote this test made it pretty comprehensive and included some tricks related to the weight of trucks. In this example, you were provided with a spreadsheet with assumptions. But this sheet is set-up really badly. They even used a merged cell and incredibly mixed up the financing with the operations. In my opinion you should re-structure the assumptions with a timeline at the top and then moving to a nicely set-up operating inputs that you can use with the LOOKUP function. When you fix the assumptions, the model will go much more smoothly.
.
.
Here is what they wrote: The purpose of the financial modelling test is to confirm that you are familiar with spreadsheets and are able to simulate the various financial scenarios accurately and quickly to advise the client demonstrate your understanding of the key economic and financing concepts. It is not a competitive examination, nor is there a threshold mark to achieve. No prior knowledge on toll roads structures is necessary for this test.
.
.
Example 4 — U.S. Case with Tax
As the transactions in the U.S. are often complicated by tax considerations, this example with a solar project includes assumptions about how to model tax deprecaion. This has a short set of assumptions, but they ask for monthly construction; investment tax credit; accelerated tax depreciation and other factors. With the assumptions there will be a net operating loss and things are more complicated. The assignement is shown below (I think there was probably more general discussion in the case.
.
PDF File with Instructions for Model that Includes U.S. Tax Complications and Monthly Construction
Example 5 – Conventional Gas Fired Power Plant
This is an example from Africa. The begining of the case states: The power plant will be built in Senegal and the project will have a 25-year power purchase agreement (“PPA”). The power plant is build operate and transfer transaction — it is transferred to the government (“BOT”). If the capacity factor is less, the investor does not take the risk. The Tariff that the project will receive is set at 105 USD/MWh. Make a model for the project and calculate the equity return. The completed excel example and instructions for the case are attached to the two buttons below. The first button contains the assumptions and the second button includes the completed file. The video explains how I would answer the test.
.
.
.
Example 6 – Mini-Grid Case
This case includes some confusing language where you should make some clarifications before you start the case. You should write down assumptions before you start the case. I changed some names and here is how the case begins. Stormy Daniels Mini Grid, a fund focused on rural electrification infrastructure, is considering providing project finance to a mini-grid developer, Bolsanaro-Grid, to support 100 mini-grids in North Korea over 2019. Each mini-grid consists of 10kW solar PV generation, battery storage, and a distribution network serving 120 customers. Each customer is served by a connection. The total capital cost (inclusive of generation and distribution) for each grid is determined by the Capex per customer which is $1,000/connection. The case write-up is included below.
Example 7: Project Finance of Ships
This example is for a port and the answer sheet given is very bad. I have worked throught the case so that you see some bad modelling practice and you can see how some issues with ports are different.
.
Power Point with Instruuctions on How to Create Port Model with Mixed Corporate and Project Analysis
.
Excel Fine for Port Model with Mixed Corporate and Project Analysis with Bad Practise
.
Example 8: Project Finance of Ships
May 2006: Ocean Rig is trying to acquire Eastern Drilling at a price tag of NOK 2.7b (NOK 135/share, 20m shares) or USD415m (exchange rate USD/NOK 6.5). The offer from Ocean Rig is subject to at least 50% acceptance (if not obtained, the offer will fall away). If it obtains the 50% threshold, Ocean Rig is obliged to provide a mandatory offer for 100% of the shares. Hence, Ocean Rig has to formulate a finance plan predicated on it becoming the 100% owner of Eastern Drilling.
Ocean Rig has recently issued bonds in the amount of USD250m and has asked if we are able to step up with the remaining USD165m.
We are already the Lead Arranger of a USD430m loan to Ocean Rig, which was concluded in June 2005 and syndicated to a number of other banks. Our share of the loan after syndication is USD130m.
We are also the Lead Arranger of a USD300m loan to Eastern Drilling, of which we have a USD100m share of after syndication.
PDF File with Instructions for Creating a Model for a Shipping Ocean Rig Case with Corporate Finance
.
Transport Case that I Failed to Complete in Two Hours
.
This example demonstrates a painful case were you compute the subsidy from an after-tax project IRR and then you compute the equity IRR from financing assumptions. As with other cases, you compute some inflation indices and time lines. This time there is a little tricky issue with heavy maintenance expenditures and with residual value which can be time consuming. The way it was written with a terminal sale value that was not equal to the book value was painful from a tax standpoint and the way they wanted you to complete a three statement model instead of a project finance model took a little time.
.
Transport Case Instructions for Three Statement Financial Model with Compuation of Target Subsidy
.
.
..
.
Private Equity Example
This example is for analysts and is a little advanced. It illustrates a transaction and computation of enterprise value and equity value. I made it for a port that has an idefinite life. The example has complexities from cash sweeps and re-financing. I named the case Dr. Evil. I have included the video and the case description and the blank case and the filled case below.
.
.
.
.
.
.
.
.
.
Stupid Assumptions Made by People who Create the Model
Show Some Valuation Modelling Prowess
Use the XIRR
Compute project IRR (pre-tax) and Project IRR after tax to show you know the basic metrics
Make sure you put in a balanced balance sheet.
Always start with some kind of timeline. Sometimes it may be a simple 1,2,3 etc. Other times it may call for dates. Use EDATE and not EOMONTH and simply increment the date. They will probably not make you go from Monthly to Semi-Annual
The three things you want to compute first are capital expenditures, revenues and capital expenditures (very occasionally they may ask for working capital, but I doubt is).
This is the only info provided, “I would like to move you to the excel modeling test round. It will be a 3-hour test where you will be provided with a document to create a short excel model. The test will be evaluated based on three criteria which will carry different weight: Methodology, Presentation, and Answer. An independent person will evaluate the test and based on that we will decide if you are moving to the final round of interview with two of my colleagues.”