Introduction
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/
Initial Case
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.
.
.
.
Standard Solar Case
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.
.
.
.
Bond Financing Case
.
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
.
Toll Road Case
.
The example below is 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.
.
.
.
.
.
.
US 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
.
.
.
.
Conventional Gas 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.
.
.
.
.
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.
.
.
Shipping Case
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
.
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
.
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 Case
This private equity 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.
.
.
.
.
.
.
.
.
.
…………………………………………………………………………………………………………………………
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.”