This web page will give you some ideas about how to deal with an amazingly horrible 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 cannot believe such hell and it really makes me feel good to be an old man who has not had to go through such a hellish experience. I do not blame you if you are freaking out and having a nervous breakdown about this crap. So on this page I am giving you some ideas. I have made a little exercise file for you to work through and I have included a video. Please remember the philosophy of flexible, accurate, structured and transparent. I apply this by setting up any model with a separate input area as well as a model where you put the drivers and the sums at the left. I also suggest that you be careful with a time-line at the start of the process.
Some Excel Basics
Don’t get too fancy with excel and by all means do not use my generic macros or read pdf etc. Here are some things you can do to make your excel go much faster. I love macros, but do not make any. A lot of people are anti-macro.
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. You can look at the video to see how you can do this really really fast.
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 disgusting and evil 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.
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
Fundamental Crimes Not to Commit in Any Model
- 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
- No historic switch and method to flexibly move from history to forecast
- Depreciation methods that do not recognize change in net deprecation rate with change in growth rates
- Formulas in the balance sheet rather than links to closing balance
- Meaningless colours that do not provide a guide
- No audit of balance sheet and history
- Polluting the fundamental models with multiple scenarios(keep scenarios on a separate page)
- 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
.
.
The Basic Structure of Any Corporate Finance Model
.
- Historic Financial Statements
- Computation of ROIC and other key statistics to understand the company
- Assumptions Development
- Computation of Pre-tax Free Cash Flow — EBITDA, Capital Expenditures and Working Capital, Provisions
- Depreciation section with separation between existing assets and new assets and calculation of deferred taxes
- Calculation of after-tax free cash flow
- Addition of non-operating sections of the model — other income, other assets
- Financing balances including cash balance and debt balance and required minimum cash balances
- Financial statements – Profit and Loss, Cash Flow, Balance Sheet
- Financial Ratios
- Valuation
If you are looking for more on corporate models and valuation and models, I have included a few links to other pages on the website that may help.
asked me to come up with a investment analyst for a new acquisition the company is planning, they have provided all the below attached financial and CIM and asked to come up with “
1. Investment proposal
2. Buyout amount
3. Put together financing structures for this deal.
.
.
Leverage Buyout Model with Unrealistic Time Constraint
.
The example attached to the file below has a very unrealistic time constraint. You are supposed to finish the model in two hours. The case has and additional bond issue for capital expenditures, no definition of inflation or of terminal value even though the case is set-up to exit before the project is finished. The case has existing and new debt which is time consuming. These uncertainties in cases are typical frustrating — I think they come from people who make the test do not take the time to work through their own cases and do not realise that they have not included crucial assumptions.
.
.
I have made video for the case below. I did not complete the video in the two hours given, but you can see how I added re-financing and cash to fund capital expenditures and the DSCR test. I cannot see how the case could possibly be finished in two hours. In my opinion, establsihing a good time line with a separate period for the transaction period. As the model is annual and the case asks for careful analysis, you can create dates with different time periods and use XIRR. I understand if you disagree with the way I have worked through this test and I am very interested in your comments.
.
.
The completed case is attached to the button below. This case has some additional items including cash for the capital expenditure funding, re-financing of the bullet bond, inflation and calculation of the DSCR for a lock-up test. When I added these items it took quite a bit of time.
.
.
.
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.
.
.
.
.
.
.
.
.
.
.
.
Case Study – Silly Assumptions Made by People who Create the Model
They will give some cutesy assumptions about people selling coffee and the market share or something like that — these evil people who cannot come up with something better. (They probably had some meeting with HR in a big meeting room or via conference calls to “brainstorm” about the test eech.)
At the end of the case study, you are to compute a corporate model as explained in the screenshot below.
