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.
Dear,
Thanks for a very interesting article. For my course, I was wondering in which way the balance sheet composition will be different for projects instead of going concerns? In this case, I am trying to make the balance sheet for a 20 year power project. As the main assets are the plant itself, and the value reduces every year with depreciation, where does the new value come from? Intangible assets? The plant initially is valued at fixed assets of 20mln $, depreciating it in straight line over 20 years. The debt is paid of over 15, and the equity ratio is 30% of the total value.
Thanks for any help.
Br,
Jason
Hi John,
Hope this is useful.
For simplicity let’s assume that the opening balance sheet for this project consists of Fixed asset (Non-current asset) of $20 M, Debt (Liability) of $15 M and Share capital (Equity) of $5 M. We recently covered a topic on how to model an opening balance sheet in our blog including the essential modeling tips on this topic.
http://www.navigatorpf.com/blog/modelling-opening-balance-sheet
Balances sheet layout can change from country to country but they are essentially the same once you undertand them. To structure the Balance Sheet the essential breakdown is
• Non-current Assets – Any fixed asset such as the power plant, EPC or other capital expenditure will appear under this heading. Generally, it is categorised / grouped based on the economic life. The power plant in this example is initially valued at $20 M. It is depreciated over 20 years, assuming straight line depreciation, then the depreciation cost would be $1M per year. Hence the balance carry forward of the Power Plant value reduces by the depreciation cost of $1M each year, i.e. at the end of Yr-1 would be $19M, at the end of Yr-2 would be $18M, at the end of Yr-3 would be $17M, and so forth. If you need more information on depreciation modeling, you may want to check this out our tutorial on straight line depreciation. Every man and his dog claims to have invented this approach so we won’t take credit for it! http://www.navigatorpf.com/tutorials/straight-line-depreciation-reverse-ticker
• Current Assets – Typically consists of Cash-in-bank, Reserve accounts, Trade debtors.
• Liabilities – Consists of Debt Facility, Trade Creditor, Tax Creditor account. In this example the $15M debt is repaid over 15-year, if the repayment method is assumed to be equal principal repayment then the debt repayment is $1M per year. Hence the Debt balance at the end of Yr-1 would be $14M, at the end of Yr-2 would be $13M, at the end of Yr-3 would be $12M, and so forth. We have prepared a tutorial on how to build a model with multiple debt repayment methods as it is often required during structuring or credit approval process. http://www.navigatorpf.com/tutorials/debt-repayment-modelling-multiple-methods
• Equity – Consists of Share capital and Retained Earnings. In this example the Share capital will be constant at $5M throughout the years.
The step-by-step process of modelling tax, depreciation and integrated financial statement (Cashflow, Profit & Loss Statement and Balance Sheet) are covered with a comprehensive project case study on our popular Project Finance Modelling B course. http://www.navigatorpf.com/courses/project-finance-modelling-b
Hope that clears a few things up and paints a bit more colour around it too.
Nick
There are two ways of constructing a linked balance sheet model;
1- First your link your detailed notes and workings with Balance sheet and then you linke P& L with the balance sheet. As a rule of thumb asset side must be equal to liability side. However just the check arithmetic accurancy of your assumptions what you do is you just leave blank the cash cell or the shorterm borrowing cell. Therefore, there will arise a differenc in the balance sheet. Now if this difference is equal to the cash flow generated+opening balance then not only arithmectically your balance sheet is correct but also you can linke that cell with balance sheet so that a circular referencing can be completed.
say you have assets of 100 M and liab of 110 then there is a difference of 10 M now if your cash flow as above is also giving that a net cash at end be 10 M then arithmetic check is complete and that ensures you your balance sheet is ok
2- Now a days since in TOR clients normally asks the financial consultants to complete the link referencing therefore, they also include that a third party certification for arithmetic and formula accuracy be also obtained.
Forget to mention if the cash cell is blank then the above will apply