Tips for mastering financial modelling of balance sheets

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.

Comments