The Balance Sheet can be one of the trickiest financial statements to model, as several line items are the result of decisions you make for the other financial statements. Most importantly, getting your balance sheet to balance (and stay balanced!) can be quite a challenge.
Here are a few tips to help you master the balance sheet:
- As with any financial model, ensure the line items in your balance sheet are linked to other areas of the model. As tempting as it may seem, never enter hard-coded numbers! For example, depreciation can be calculated elsewhere and pulled through to the balance sheet.
- Resist the urge to “cheat” and add in dummy numbers. It may seem appealing when you’re tearing your hair out at 2am, but it’s not good practice and you’ll regret it later.
- Leave finalising your balance sheet numbers to the end of the modelling process. By all means build the structure of the balance sheet first, but wait until the rest of your model is near completion before attempting to balance your balance sheet.
- Don’t be afraid to add hidden rows. If your model is reasonably complex, you’ll probably need more than one row to calculate “Cash at bank”, for example.
- The use of in-built error checks can alert the user when the balance sheet does not balance. Your balance sheet might balance now, but make a few structural changes to another part of your model, and it suddenly doesn’t balance anymore! An error check or use of the live watch window can help keep an eye on this.
- Create a “forecast” balance sheet aligned with your model. For example if your model shows five year projections, your investors will want to know what the balance sheet will look like in five years.
- Unlike the P&L which shows revenue and expenses relevant to each period, a balance sheet shows a snap-shot of what the company owns and what it owes at the end of each year. As basic as it may sound, don’t forget to roll your assets across each year.
How to link the balance sheet to profit and loss and cashflow statement
When modelling your balance sheet, use the following guidelines to help you determine how each line item should be either linked to another financial statement directly, or determined through the creation of an assumption.
- Accounts Receivable can be calculated using revenue and collections expectations. It can be derived from the formula revenue / 365 * debtor days.
- Accounts Payable can similarly be calculated using operating expenses (opex) using the formula opex / 365 * creditor days.
- If you are modelling inventory, it can be calculated based on initial purchases, plus additions to inventory, minus cost of goods sold.
- Non-current Assets will come from your depreciation schedule, where you have calculated capital purchases and their depreciation. This will be driven by assumptions about fixed assets per employee and other company needs.
- Short Term Loans typically cover short term cash needs, which may be driven from the Cash Flow Statement.
- Long Term Loans are used to cover long term funding needs, often in lieu of raising capital. An assessment of your start-up and short term needs will help determine the amount.
- Retained Earnings is an accumulation of your Net Income from the Profit & Loss prior to the current period.
- As the name suggests, a balance sheet MUST balance when completed i.e. Assets = Liabilities + Equity.
[...] each with a different point of view and area of expertise. Some recent articles include: • Tips to master the balance sheet • Applications of Data Validation in a Project Finance Model • Debt sizing for minimum DSCR [...]
Thanks Danielle for this post, very useful! Do you cover this material in your financial modelling courses, or even the online financial modelling courses?
Hi Martin,
A lot of this stuff is just Financial Modelling best practice, which is most certainly covered in the workshops and in online courses too! http://www.plumsolutions.com.au/training
Danielle.
A financial model that does not contain a balance sheet can hardly be called a “financial” model. Constructing a proper functioning integrated balance sheet is therefore a fundamental part of financial modelling.
Most points described in the article above are normal good practice and many more could be added. But I do disagree with one or two points.
1) A proper balancing balance sheet (i.e. not force-balanced) should be in place at the earliest possible point in the modelling process, not towards the end. That way, as other items are added and expanded into more detail the appropriate impact on financial statements (i.e. accounting debits and credits) can be continuously checked, including the most basic check that the balance sheet remains in balance.
As an example from my own experience I once inherited a project from someone else who had been working on that project for 6 months. I was assured that it would take me just a week or two to finalise the balance sheets since the P&Ls and cash flows were supposedly done. It took many more months to finally get the balance sheets to balance properly. (Ok, there were over 100 separate entities with various levels of consolidation and loads of inter-group transactions.) Had they started with even simplified balance sheets, some fundamental flaws in the initial appproach and design would have come to light much earlier in the process.
2) Hidden rows should not be encouraged, and especially not on the face of a balance sheet. In the example used in the article, ‘Cash at bank’ should be a separate calculation section in the model, not a quick balancing calculation on the face of the balance sheet.
3) Although not everyone thinks like an accountant, a balance sheet ultimately represents debits and credits and the underlying calculations should be constructed on the same basis. A balance sheet must always balance because all debits must always equal all credits.
Thanks for your contribution, Gavin!
I don’t agree that every financial model needs a Balance Sheet as one is not always appropriate or necessary – depending on the purpose of your model.
I have recommended to leave adding the balance sheet until the end as there isn’t much point in linking through numbers until you have the design of the model bedded down. I have in the past wasted time balancing the balance sheet, only to find that the entire structure of the model has changed and therefore needed to start again.
I can appreciate the situation you had – it sounds like at least having the structure of the balance in sheet in place first would have certainly saved a lot of time. Such is the joy of the bespoke model!
Yes, the use of hidden or “helper” rows or columns is a rather contentious issue in Financial Modelling – many modellers prefer not to hide columns at all.
I agree with Danielle,
As someone who has built a lot of models and seen even more, the balance sheet doesn’t always make an appearance (I usually build one in out of best practice, but most rookie model builders don’t have the chops to link it properly and/or the accounting knowledge to properly build it). Depends on the purpose honestly, I’m coming from the perspective of working with entrepreneurs/startups who are using their model to present to investors. For the most part they are focused much more on cash balance/burn rate and the P&L. Balance sheet really just falls out from those forecasts, unless there’s substantial A/R or Fixed Assets… since I work with mainly dot.coms there isn’t either.
Point of my ramblings is if the Balance sheet doesn’t tell much of a story, it isn’t the end of the world if it isn’t included. Though the smart people who hire professionals to build their models will usually get a balance sheet just out of principle
Hi Danielle and Chris,
Glad to have a little bit of a debate going…
I agree that investors and the like will seldom focus on the balance sheet itself and will be a lot more interested in the P&L and cash forecasts.
I support the inclusion of a balance sheet early in the model build construction as a modelling control check, not necessarily as something that will be particularly useful to business users. It gives some comfort (i.e. integrity) that the P&L and cash flow items are properly linked to each other.
It is very easy to miss a minus sign here or there, or omit a line item from further calculations. Although it won’t catch everything, an integrated balance sheet will help to highlight such mistakes sooner rather than later. And I am sure everyone will agree that it is easier to fix an error as soon as it is made than trying to find and fix it days or weeks later.