Course question: how do I build a financial model?

What are the exact steps in a financial modelling build up? This question was asked recently by a delegate on a financial modelling course. Here's a suggestion regarding how to proceed. You can get fuller details here: modelling steps.

Step 1) Create an assumptions tab in your model first. Use the same structure as in this Excel spreadsheet: modelling start point. Enter your operating inputs onto the assumptions tab one at a time (starting with revenue). Just create the base case first. Each time you add a new operating assumption, create a link from the top of the assumptions tab (the scenario picker, using the choose function) to your calculations tab. You can see the example here: modelling start point.

Later on you are going to copy the base case down to create your stress and growth cases (see the final tab in the modelling start point example). But right now you're just going to concentrate on building the base case.

Step 2) Keep adding more operating inputs onto your assumptions tab, wiring them up to the P&L on the calculations tab (the second tab of the modelling start point shows GM% as the next operating input for example).

Step 3) As you move down the P&L you're going to have to model depreciation. And to model depreciation you're going to have to create a fixed assets schedule, with assumptions around depreciation rates and cap-ex rates.

Step 4) Once you have created your fixed asset schedule and modelled depreciation and cap-ex, you can wire depreciation into the P&L. You can also wire fixed asset balances into your BS, and cap-ex into your cash flow. You can also model amortisation.

Step 5) As you continue to move down the P&L, you can leave the interest calculations alone for now - we're gong to come back to those later. You can fill in the rest though, including the tax charge. You can insert a tax rate assumption into your inputs/ assumptions tab (as discussed on the course, use effective rather than corporate), allowing you to model the tax expense in the P&L.

If you think you're going to need to model tax losses, you can do that now, using this model as a guide: modelling tax losses.

Step 6) Model most of the rest of the balance sheet items. This means you are going to need to insert extra inputs around working capital items (e.g. modelling debtors/ receivables based on debtor days). Other items we can link to the overall size of the business. We can project intangibles at this stage (they will reduce by amortisation). Other items will move in line with the overall growth in the business.

Step 7) Wire up the cash flow. Many of the items (EBITDA, cap-ex for example) link to other parts of the financial statements. You'll have to be careful in modelling working capital movements and other balance sheet movements. If you miss one, or wire it up the wrong way, you'll never get your balance sheet to balance, and you'll spend a long time trying to trace the error (the voice of bitter experience that is).

It's really important to be careful at this stage because mistakes in wiring up the cash flow statement are a common source of error in modelling.

Step 8) If you want to construct a model that allocates all surplus cash flow to repaying debt we recommend following this example/ format: cash flow model. It is designed to map out and help you model the critical cash flows. It will enable to model debt repayments in your cash flow statement, as well as debt and cash balances in the balance sheet.

When you've completed this step you will have filled in the debt and cash balances in your balance sheet. The interest payable and receivable in the P&L and CF will still be empty (zero values, that's fine).

IF you've done everything correctly (it never happens first time) then you might have a balance sheet that's balancing. More likely, you've got a fair bit of checking to do and some differences to iron out. That's fine. That's normal - it's part of the process.

Remember step 8 is all about getting the balance sheet balancing. At the moment interest calculations are blank. Once your balance sheet is balancing, it's time to move on.

Step 9). At this stage you can create the interest calculations. You may be tempted to average opening and closing balances. That's going to result in a circular reference (explained here: circular references). Turn iteration on. Sit back and pat yourself on the back. You've got a balancing balance sheet. Sensible interest calculations. And a model that's projecting cash flow for you! Congratulations.

To do your scenario testing, copy the base case down (as per the final tab on modelling start point). Create some dropdown boxes. Now you can instantly switch your scenarios, and see what happens to your modelling outputs.

If you wanted a model that integrates financial statements, and gives you a cash flow projection - if that was your objective for modelling - you've done that: well done - you've just achieved a result that takes training delegates the best part of three days on our financial modelling course!