The first part of the course just reviews some modelling principles of being lazy with excel formulas; looking at some bad practices in other models and discussing some theory. I also included a couple of files that you can use to prove if certain practices make a file “heavy.” A “heavy” file may be too big or it may be too slow. In the file that you can download by clicking on the button below, you can test whether an excel practice of clicking on an entire line slows excel down or makes your file larger. The answer is that using an entire row or column with LOOKUP and/or INDEX does not. Files associated with the video that prove the size and the time issues can be downloaded by pressing the buttons below.
I am creating these exercises for a course I am teaching so that participants can review the subjects and test their knowledge. The very nice person who arranged the course also asked me to include some modelling behaviours that are disgusting. In the exercise below I hope that you see these practices have been avoided. I have also discussed these practices in more detail on the A-Z page that is linked to this sentence. The only excel function to use in the first example is the LOOKUP function (VLOOKUP and HLOOKUP are illegal). En francais, cette function est RECHERCHER. I have included a sheet with bad practices so you can correct them.
High Crimes and Misdemeanors in Project Finance Models (All of Us Have Been Guilty)
All of the financial modelling companies seem to have their blah, blah, blah acronyms and rules. For what it is worth, here are my rules. I don’t tell you what to do; I tell you what not to do. I tell you this because I have made all of the mistakes and I have stole ideas from many other models. After making mistakes, I believe these things really work. One of my biggest complaints is paying too much attention to the rules and applying so-called best practice rules.
- Obviously, no hard-coded numbers after assumptions in the model. This is a clear crime and an even worse crime is mixing a formula with hard coded numbers like F5 * 1.02. The first can be solved with F5, paste special, constants or with Generic Macros.
- Not putting driving factors in the left column. Why in the hell do people put the sums in the left column. It would be much better to show the factors that are driving the model.
- Not making formulas the same across columns, another obvious one. You can make the formulas the same across the columns with TRUE/FALSE or 1/0 flags, or whatever you call them.
- Wasting time on meaningful or meaningless colours is one of the most irritating things I have to put up with when I watch people make a model in excel. That is why I made the colouring page that allows you to find the location of other sheets and stop wasting time.
- Use IF instead of MAX and MIN functions to connect the cash flow with the debt schedule. There is always a direct or indirect connection of the debt schedule with the balance sheet — no cash flow and no debt repayment. The MIN and MAX (never IF statements) should be in the cash flow waterfall and not in the debt schedule.
- Putting the Flags, Masks, Switches (whatever you want to call them) in a separate page instead of where they are used right next to the calculations. Why force people to make silly traces with those blue arrows to find things that are driving the model (even if they have a fancy add-in).
- Placing the forecast financial statements at the beginning of the model. Structuring is the biggest deal of FAST and other blah, blah, blah acronyms. You should the model with pre-tax cash flows, after-tax cash flows, summary sources and uses, debt sizing, debt schedules with repayment and interest and then, finally the profit and loss and the cash flow statement (the flow should be natural).
- Splitting up pages when you are designing the model instead of letting the model smoothly flow down a single page. I even think you should leave the model on a single page after you are finished and not split it up. Please put yourself in the position of somebody trying to understand the model. Your model should have a natural flow and it is so much easier to follow a model on a single page. Use little columns on the left so you can see the structure of the model.
- Deceiving yourself that you are smart because you make a long and complex formula. Use of formulas that are too long is inexcusable and always avoidable. The key to making models transparent is separating formulas and making them short.
- Showing off your excel prowess by applying unnecessary functions. You can do just about everything with three excel functions — LOOKUP, INDEX and SUMIF. LOOKUP is more efficient and easier to use than INDEX, MATCH and OFFSET is unnecessary. When you use LOOKUP please click on the entire lines rather than wasting time fixing cells.
- No formulas in the balance sheet. When you make a model all or the balances for debt, DSRA, net plant and other things should be in various structured sections of the model. The only balance that is not there in your model is the equity balance, which you should compute after the Formulas in the balance sheet rather than links to closing balance
- Don’t go overboard to show off your auditing skills. Use TRUE/FALSE switches for checking the balance sheet, debt balances and circular references, but you do not need to go overboard.
- Don’t pollute your fundamental models with multiple scenarios. Put the scenarios in a separate sheet (except P99 or P50) and let the scenario sheet be a mess with the index function and code numbers.
- Don’t make summary sheets complex. A simple rule is that output sheets can be deleted without affecting the model. This is pretty obvious that you do not want to mix outputs and inputs. Put your summary on one page so you do not have to look around. Include IRR’s, DSCR, LLCR, a graph of cash flow versus debt service, a sources and uses summary and key drivers such as capacity factor, cost per kW, levelised O&M cost and the plant capacity. Then you can even call it a dashboard if you need to.
- Don’t use the NPV or XNPV formula when interest rates are changing. Use a compound factor when interest rates are not constant and then compute the present value with the SUMPRODUCT function where you divide the cash flow by the compound factor.
- Putting fourty flags at the top of a separate sheet sucks. You need an construction phase and operating phase at the top of your single sheet. But do not put too many of them at the top of a separate sheet (this will be very controversial for the dushbags who believe they are following some kind of best practice standards like SMART).
- Find a good style and don’t listen to me. I like the positive number convention. I like the using underlines for key items. I like using thin columns at the left.
- Take the side of Anti-Range Namers. The range name controversy is a subject at excel parties. There should not be an argument. Except for macros, range name suck. Some companies try to make you put range names in excel cell. This adds a log of time and if you have simple formulas, it adds nothing.
- Don’t chimp on sub-totals in the cash flow waterfall. Sub-totals are important in cash flow waterfalls. Connect the debt schedule with the sources and uses and also the cash flow waterfall. Why would you make people search for basic and obvious links.
- When using the goal seek tool, use the NPV = 0 rather than an IRR difference equals zero. When, the IRR cannot be computed, the goal seek can go crazy like we all know. As you know when the NPV is zero, the IRR is the discount rate. It is much better to use the NPV rule.
- Don’t compute the project IRR from CFADS. That is a basic mistake in finance theory; instead compute the project IRR before any finance equations are in the model.
- Work on the nightmare blue lines from circular references last. While working on your model, try to avoid circular references by doing things like using EBITDA instead of DSCR and construction expenditures instead of total funding uses.