Financial modelling excel

Assumptions, facts, and format

By placing my cursor in cell E6, I’ve made clear that the number 462.30 was taken straight from the balance sheet and not generated by a calculation. To put it another way, this figure is hardwired into the spreadsheet.

In the financial modelling world –

The number we’ve hardcoded is accepted as a fact since it’s taken directly from the yearly report.

We can view the figure as an assumption, as we compute it using mathematics.

It is fundamental to be able to differentiate between the facts and assumptions in a financial model, so that the user of the model can comprehend which figures originate from AR and those which are calculated. Now, the errors can also be spotted easily.

To come up with the grand total, we need to add up all the non-current assets first. Here’s how it can be done:

I used the ‘=sum()’ function in excel to come up with the total for the non-current asset, however this is considered an assumption since I calculated it myself. To differentiate between assumptions and facts, I chose to colour code them.

To avoid repetition, one can easily colour-code this by pressing the function and F5 keys simultaneously. A pop-up window will now appear.

Hit ‘Special’ and pick out only constants and numbers, as illustrated below.

Once you select OK, Excel will identify and accentuate any numerical entries or factual values.

Without deselecting the numbers, you may choose whatever colour you’d like. Personally, I prefer light blue, but it’s entirely up to you.

Once you have picked the shade you desire, make sure to keep the entire sum of non-current resources in bold.

Once you have completed the initial step, then the rest of this process should be relatively easy. To get the needed data, simply pull out the figures from your balance sheet and profit & loss statement and insert them into a spreadsheet.

– Other things to note

You may be asking why it’s important to sum up the numbers and assign each one a specific colour. For instance, why not just copy the sum of non-current assets?

It’s important to calculate these figures ahead of time as they will then be used to project line items on the balance sheet. This ensures not only model accuracy but also consistency in our calculations.

Before we finish up this chapter, here are a few formatting pointers –

• Ensure that all figures are aligned to the right and round off the answer to two digits.
• Give all heavy numbers emphasis, usually this would be the subtotal and total figures.
• Double-bordering cells can be used when it’s necessary.

I’ve finished entering the data into the balance sheet. Pay attention to a last few things.

I have tallied the total assets by summing up non-current and current assets. In a similar manner, I have done the same for liabilities.

To ensure my balance sheet stays balanced, I perform a ‘True’ and ‘False’ check. It’s imperative to remember that when assets equal liabilities, the balance sheet is in equilibrium.

My balance sheet is in equilibrium due to the fact that total liabilities match total assets. I won’t go over the specifics of how to extract P&L data, as it’s a similar process. If you come across any issues during the process, though, don’t hesitate to ask – I’d be more than happy to help out. Hopefully, your P&L will mirror this!

When attempting the P&L, you’ll notice I’ve deliberately expanded the ‘other expense’ in the expenses section to highlight that breaking down constituents of a hefty line item is beneficial. By modelling these lines at a more granular level, we can make sure our model is realistic.

Take note this is merely a helper model. It served as an illustration on how to go about transferring data from financial statements into excel. Now, we’ll be moving on to the main model, which will be covered in the following chapter.

Regarding financial modelling, ‘Historical data’ is usually the starting point. However, attaining this goal may involve a great number of individual steps. It’s best to bear in mind that this applies to all subsequent stages too.