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. If you are in Germany, your excel may be in German even though you have 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. 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.
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.
Learn how to make time lines in seconds. 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.
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
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
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 case involves a 10 MWp Photovoltaic Utility Scale Solar Plant.
- Installed Capacity – 15 MWp
- Solar yield: 1500 hours per year
- Construction price of the plant: 800 USD/kWp
The plant will have to face the following Operating Expenses during its 20year lifetime:
- Operation and Maintenance price: 12 USD/kWp/year indexed at inflation
- Rental of the land: 20,000 USD/year indexed at inflation
The plant has a PPA of 55 USD/MWh3 and should be indexed at inflation, for the moment fully indexed (100%). Make a simple financial model of the plant. Please make it clear and clean, with for example the following guidelines:
- Use a separate sheet for inputs and calculations,
- Format (figures, colors, font) and respect it in the whole document,
- Don’t use too long formulas, and you will diminish the chances of making errors
3. Project Finance debt on the following terms:
- Debt duration: 16 years
- Interest rate: 4% per annum
- Financing 80% of the total cost
- Repayment in constant instalments from the project’s cashflow.
It is asked to build the financial model and make it evolve with the different questions. Please separate the modelling blocs you add when you try to answer the different questions. Write up some conclusions and discuss risks, strategies and propose alternatives. The screenshot below is an example of some of the inputs you may be given. Make sure can complete this. In this example there are different prices in different periods and you should make some adjustments.
Example 2 – 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.
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..
The financial modelling test will be used purely to check that your understanding of financial modelling commensurate with your experience and background. Accordingly, it is designed to exercise an experienced modeller so it is not unexpected if someone less experienced do not complete the test.
You have about 4 hours for the test. Please state and explain any assumptions you make, especially in situations where you require further clarifications. For this case, they gave you the inputs, but the layout was really horrible:
PROJECT OPERATIONS AND FINANCING
Your project team is currently advising a private sector consortium to bid for a road concession, which the government has indicated could be as long as 70 years if necessary. You are the designated financial modeller of the project team and you have been tasked to simulate the cashflow projections using the assumptions below.
The road in question consists of 50 miles of dual carriageway with grade-separated junctions, costing RM100 million – indeed that is the firm fixed price offered by the building contractor. Construction should take 3 years with 30% of the cost incurred in the first year, 40% in the second year and 30% in the final year.. Operations will start immediately after the completion of the construction (i.e. in year 4).
- The traffic consultants forecast that, at the Start of Operations in year 4, the car traffic would be 3 million vehicles per annum diverted from other routes, plus an additional 1 million vehicles per annum generated by the new route.
- The traffic growth rates for the diverted traffic and additional traffic will be 15% for the first 2 years and 20% for the first 4 years respectively. Beyond this, the traffic is expected to grow by 2% and 4% respectively over the remaining life of the concession.
- Lorry traffic will consist of 0.2 million vehicles per annum diverted, and 0.2 million per annum generated, subject to the same build up as for cars. Beyond the initial build-up period, lorry traffic is expected to grow at 1% and 5% respectively.
- However, due to road capacity constraints, the traffic is capped at 15 million car equivalent per annum. For the purpose of this exercise, assume that 1 lorry is 5 car equivalent.
- The toll tariffs are banded. At the Start of Operations, the car tariffs will be RM3 per car up to the first 3 million cars per annum, RM2 for the next 2 million cars per annum, and RM1 for the next 1 million; thereafter, they pay nothing.
- Lorry tariffs will be RM10 per lorry up to 2 million lorries per annum; thereafter, they pay nothing.
- The toll tariffs will be indexed to the Consumer Price Index (“CPI”), whilst lorry tolls will be indexed to CPI+1%. The CPI is projected to be 3% per annum over the Concession period.
- The consortium have entered into an innovative long-term maintenance contract with an experienced road operator, whereby they accept all the
maintenance risk (including the cost of lane rentals and any other penalties imposed by the Highways Agency in respect of such maintenance) in return for an annual payment based on a simple fixed formula.
- Fixed maintenance costs (essentially weather and climate related) will be RM2 million per annum, escalating at CPI-1.5%.
Variable maintenance costs will be determined solely by lorry traffic, adjusting for their average axle weight, which has recently been surveyed and found to be 5 tonnes. The variable charge will be RM0.10 per lorry-mile at a 5 tonne axle weight, escalating at CPI-1.5%.
- Empirical observation in the past has shown that damage to carriageways is proportional to the fifth power of axle weight, but the operator has agreed to a formula where the exponent is only 2.7. Average axle weights are expected to rise at 2% pa until reaching the statutory limit of 9 tonnes.
- The standard bid set by the government assumes a 50-year concession period (including the construction period). They will assess bids by comparing net present values (“NPVs”) of total toll payments discounted at 8%.
- Capital expenditure on roads qualifies for 4% writing-down allowances against tax; interest is also deductible. The tax rate is 30% of taxable profits. The 25-year Malaysian Government Securities (“MGS”) yield is currently 5%, and the long-term swap premium is 1%.
- The commercial lending banks approached by the project finance team have indicated a maximum loan tenor of 25 years, and a margin over equivalent MGS of 2%. However, despite the team’s best efforts, they insist on a 100% fixed-rate swap. This effectively fixes the all-in interest rate of 8% per annum.
- But the banks are willing to fund 90% of capital expenditure ,up to the maximum facility amount of RM100 million, and are prepared to have the loan interests rolled up (ie loan interests are incurred but not paid), during the first 4 years of the Concession,. A grace period of 2 years means that repayments of loan principal amounts (calculated on an annuity basis) start only after 2 years from the Start of Operations.
The terms and availability of bond financing in current market conditions are less certain. However, it is believed that a 30 year maturity should be achievable, at a spread over the gilt of 2%.
- The bond amount is expected to be RM100 million, borrowed as a single drawing at financial close. It is thought that the bond underwriters
will insist that equity is subscribed in its entirety at financial close to achieve a debt:equity ratio of 9:1. Repayments (calculated on an annuity basis) would start only after 5 years of Concession Start.
- Part of the security package for the bonds will include an escrow account, pledged to the bond-holders, into which all the proceeds of borrowing and equity raising must be placed. Capital expenditure and interest on the bonds will be funded from this account during the 5 year grace period. Short-term interest rates are relatively, resulting in an expected interest rate on the account of only 3%.
- BUILDING THE MODEL
Prepare the cash flow projections for the project on annual basis:
- Project the traffic, taking into account the forecast growth rates and the initial buildup.
- Project the toll levels on the basis of the indexation system.
- Allocate traffic to each toll charging band.
- Calculate the toll payments in each band.
- Calculate the average toll across the total traffic, in nominal and real terms (assume CPI of 3% p.a).
- Project the fixed maintenance cost.
- Project the variable maintenance cost (project the average axle weights for each year over the concession, and then the traffic-related maintenance per lorry using the power law relationship with axle weights).
- Calculate the taxable profit or loss (making provision for interest and taking into account the writing-down allowances on the capital expenditure on the declining balance of the pool).
- Calculate income tax expense.
- Project the capital expenditure of the project based on the construction profile given.
- Calculate Net Cashflows Before Financing (“CFBF”).
- Calculate the loan outstanding, interest payable, and the annuity, making provision for capitalisation and then payment of interest during the grace period.
- Calculate the project cashflows and the equity cashflows, so that you can report the Equity returns to the shareholders, together with the amount of equity required.
- Calculate annually the Project Life Cover Ratio (“PLCR”) and Loan Life Cover Ratio (“LLCR”), together with the Annual Debt Service Cover Ratio (“ADSCR”) and report to the results.
For the purpose of this exercise, the PLCR at a particular period is defined as the net present value of the CFBF over the remaining life of the concession (discounted at the relevant interest rates) divided by the principal outstanding as at that period. Similarly, the LLCR at a particular period is defined as the net present value of the CFBF over the remaining life of the loans (discounted at the relevant interest rates) divided by the principal outstanding as at that period. The ADSCR is defined as the annual CFBF divided by the annual principal and interest payments.
- Repeat for the bond, calculating the escrow account and making provision for interest receivable in the tax calculation.
- ASSESSING THE BASE CASE
For the bank loan the key ratios are that the LLCR must be greater than 1.50, the PLCR greater than 1.75, and the minimum ADCSR greater than 1.25, whilst for the bond the minimum ADCSR must be greater than 1.35.
i. Are those conditions satisfied?
ii. Which financing is more comfortable?
iii. Which financing route gives the higher equity return to the shareholders?
VII. RUNNING SENSITIVITIES
(a) The banks insist that the worst case sensitivity must show a LLCR of at least 1.00 and a minimum ADSCR of 0.90. Their worst case assumes that there is no generated traffic at all, and no growth in the diverted traffic. Report the key ratios for this sensitivity. Are their conditions satisfied?
(b) Does the concession need to be as long as 50 years? What is the lowest concession period which will meet the requirements of the banks? And of the bondholders?
(c) The Highways Agency object to the lorry toll being indexed to CPI + 1%. They insist that they will accept no more than CPI. Can the consortium agree to this? If not, what should the initial toll be? What is the net impact on the levelised toll?
(d) How could the bond structure be improved to reduce the toll levels?
Example 3 — Excel Crimes Prison (Bond Financing)
This 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.
Here is the case: Stormy Daniels (the “Client”) is interested in bidding to design, build, finance and operate a prison being procured by the Belarus Government for people who commit excel crimes. She has approached you at Lehman Brothers, to provide financial advisory services and financing for the Project.
- THE EXCEL PRISON
The prison will be constructed over a 2-year period and have a concession for 10 years thereafter. Financial Close will be April 1, 2022 with construction beginning then. Total construction cost is anticipated to be $1.2 billion, distributed over the duration of the construction period. The only data you get in an excel file is the construction data.
- Halfway through the Construction Period (i.e. in Mach 2023), the Belarus Government will provide a milestone payment of $100 million to the Project. Upon completion of the Project on 30 March, 2024 (“Substantial Completion or at COD”), the Belarus Authority will provide a final payment of $400 million to the Project.
- After COD Stormy will collect revenue for the next 10 years. The forecasted revenue and operating expenses for the first month of operations is expected to be $23 million and $16 million respectively. Revenue is expected to grow at 2.00% per annum as the number of excel prisoners increase , while operating costs are expected to grow at 1.50% p.a.
- The revenue collected will be used to pay operating costs,
service debt, and pay dividends to Stormy. At the end of the Operating Period, Stormy will transfer ownership of the Project to the Authority.
- FINANCING OF THE EXCEL PRISON
There will be three sources of financing:
- Construction Loan Facility (“CLF”) from Lehman Brothers
◼ The Loan Construction Facility will be a revolving credit facility, open for draw beginning on the Financial Close date
◼ The CLF must be repaid in full by the end of construction and cannot be drawn upon during operations
◼ Lehman forbids the Client from receiving any dividends during construction
◼ The Bank will charge a base rate of 1.00% plus a credit spread of 100 bps per annum that is payable monthly on all opening balances of the drawn amounts
◼ The CLF will have an upfront fee of 100 bps, to be applied to the amount of CLF issued
◼ The Bank will charge a commitment fee of 25% of the credit spread on the Senior Construction
Facility that is payable monthly on all opening balances of the undrawn amounts
- Senior Bonds underwritten by Deutsche Bank
◼ The Senior Bonds must be issued at Financial Close date
◼ The Senior Bonds will pay semi-annual interest payments at a benchmark rate of 2.00% per annum plus a credit spread of 200 bps on the opening balance of period
◼ The Senior Bonds will have an upfront fee of 200 bps, to be applied to the amount of Senior Bonds issued
◼ The construction period will be an “interest-only” period (no principal repayments)
◼ The Senior Bonds will begin mortgage-style amortization 6 months after Substantial Completion (June 30th, 2023) and continue on a semi-annual basis
◼ The Senior Bonds must be fully amortized by June 30th, 2034 (6 months before the end of the Operating Period)
- Stormy’s Off-shore Equity (“Equity”)
◼ Stormy has $350 million in her bank account that he must invest into the Project before the start of the Operating Period
◼ Stormy is able to receive dividends in the same months that she makes a principal payment on the Senior Bonds
◼ Stormy is not willing to reinvest any of her money during operations, nor inject more than $350 million throughout the life of the Project
◼ Stormy is seeking to maximize the return on her investment on an Internal Rate of Return (“IRR”) basis calculated from Financial Close
- YOUR TASK
Stormy wants a financial model (starting with the Financial Close Date in the first period, proceeding on a monthly basis thereafter). The Financial Model should assume all cash flows (other than financial close) occur at the end of the month. The purpose of the Financial Model is to calculate the optimal financing structure (propose the ideal amount of Senior Bonds and Senior Construction Facility required) and calculate the return that the Client would earn on this investment.
You should compute (Total nominal returns, IRR, NPV of the Project at a 4% discount rate)
A. What is the average life of the bond? What is the minimum Debt Service Coverage Ratio of the project? Average Debt Service Coverage Ratio?
B. At what operating cost inflation rate would the project default? At what revenue growth rate would the project default?
C. If the Substantial Completion payment was $200 million, instead of $400 million, what would the optimal financing structure (Senior Bonds and Senior Construction Facility sizes) be? What is the resulting IRR?
D. If the price of the bond is $103.3 on April 1st, 2028, what is the implied yield to maturity? What is the PV01?
E. At the time of Substantial Completion, Stormy is contemplating a refinancing of the Senior Bonds. If at that time, the benchmark rate is 1.50%, and similar bonds are trading at credit spread levels of 100 bps, what is the maximum amount of debt Stormy can raise to refinance the Senior Bonds.
- Assume a minimum DSCR target of 1.15x. What is the resulting IRR? (Hint: Debt service does not need to be calculated on a mortgage-style basis.)
- 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 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.
- Capacity: 2500 kWdc
- Solar Yield 1500 kWh/kWdc
- Degradation 0.5% per annum
- 6 months construction timeline
- Operation phase 30 yrs.
- PPA price @ 0.07$/kWh term: 20 yrs
- Wholesale market sale @ 0.04 $/kWh Last 10 yrs
- Construction Cost $1.2/kWdc
- O&M Costs: Maintenance, property tax, insurance, that total $15/kW-year
- Calculate the pre-tax IRR and pre-tax NPV
- Calculate tax benefit line
- Apply 26% ITC applied at the end of 1st yr
- Apply MACRs depreciation for 5 yrs
- Use tax rate of 21%
- Calculate the after tax IRR and NPV;
- Debt sculpting for a DSCR for 1.4
Example 5 – Conventional Gas Fired Power Plant
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.
· Net capacity 200 MW
· Net heat rate 8,000 kJ/kWh (45.0% electrical fuel efficiency)
Capital costs incurred during construction:
· EPC turnkey price of the power plant is 700 €/kW
· O&M mobilization of 2 million USD
· Construction time of the plant is 12 months
· Please consider any other costs the IPP could incur during construction
- Operating costs:
· Gas price of 10 USD/GJ
- · Fixed O&M fee of 150,000 €/month (represents the salaries of the operating staff)
· Other costs during operations
- Variable O&M fee of 5 USD/MWh (represents the maintenance costs of the power plant)
Completing the task with a simple model is more important than building an accurate and complex model that doesn’t leave you time to deliver the outputs.
My answer to this case associated with the video is attached to the button below.
Example 6 – Mini-Grid Case
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 revenues from the grids are driven by the amount of energy customers consume, and the tariff they pay for each unit of energy. On average customers consume 10kWh/month at a tariff of $1/kWh.
The proposed structure will be a corporate structure, the Kim Son Un Company, in North Korea, that the fund will provide equity and debt. The Kim Son Un Company will own the grids for their operating lifetime (20 years).
The developer benefits from a capex subsidy from the North Korean government of $500 per connection, paid on completion of construction. The price paid by the Kim Son Un Company is therefore $500/connection ($1,000/connection in capex – $500/connection in grant).
Stormy Daniels Mini Grid will provide 100% of the debt and equity to the Kim Son Un Company. Bolsanaro-Grid will incur an annual operating expense of $5,500 per grid for operations and capex replacement. Stormy Daniels Mini Grid has sourced debt at 70% leverage from a lender at an annual interest rate of 7% over 8 years, with the principal amortizing on an equal installment basis. Stormy Daniels Mini Grid will evaluate the investment opportunity on the 15-year IRR they expect to achieve on their equity investment, amongst other criteria.
For the purposes of the model, assume that local North Korean inflation and NORTH KOREA devaluation against the dollar apply to all cost and revenues lines i.e. model only in USD.
As an Investment Associate your role is to model the opportunity and develop an initial investment recommendation for the Investment Committee based on the available information. Complete the following outputs; feel free to make additional assumptions where necessary, which can be corrected after further negotiation and data requests from the company.
- Build a financial model in excel of the Kim Son Un Company. Focus on the income statement and cashflow to determine the project IRR. Include a dashboard tab showing the most important assumptions and outputs, including but not limited to:
- Key assumptions: primary drivers which can be easily adjusted to test different scenarios
- Key outputs: metrics you view to be most important in evaluating the opportunity
- Cash flows to equity investors are a priority
- Sensitivity analysis: showing the impact of the primary risk factors affecting the deal (upside and downside risks) such as higher or lower:
- energy consumption by consumers
- Debt terms
- Key outputs: metrics you view to be most important in evaluating the opportunity
- For the follow-up call, prepare to discuss an investment recommendation including but not limited to the following elements:
- Proposed structure evaluation: evaluate the structure
- Risks analysis:
- identify the major risks, including any not captured by the model
- evaluate the strength of any mitigants
- Opportunity evaluation: give your evaluation of the opportunity
- Risks analysis:
Example 2 — Project Finance of Ships
Example 6: 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.
Shitty 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.
Idea 1: Don’t Start with the Income Statement, Balance Sheet or Cash Flow if Your Case is for Project Finance
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).
Hedieh’s 10 Commandments from the Old Testament