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:
- Try to start with a balanced balance sheet from a prior year if there’s one available, this will be much easier than starting from scratch.
- 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 should be calculated elsewhere, with assumptions clearly labelled, and pulled through to the balance sheet.
- Resist the urge to “cheat” and add in plug 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 – and your scenarios won’t work.
- 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. Use grouping to hide working rows.
- Having said that, don’t try and do all the calculations within the balance sheet; lay out your calculations clearly on separate schedules. At minimum, you’ll need schedules for working capital, depreciation, debt and equity.
- The use of in-built error checks can alert the user when assets do not equal liabilities + equity. 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! A simple error check or use of the live watch window can help keep an eye on this.
- For this reason, you might choose to wait until towards the end of the modelling process until finalising the balance sheet. 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.
- 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 (otherwise known as the Profit & Loss, or Income Statement) 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.
Strategies for Finding a Balance Sheet Error
If you’ve completed the model and you can’t get the balance sheet to balance, here are some strategies that might help you to find the error:
- Look down the columns and check prior year comparatives. Often, you’ll notice a missing number, or one that looks odd compared to prior years.
- Take a look at the size of the error. Does the number look familiar? Sometimes it’s a number you recognise because you’ve seen it elsewhere.
- Strip the model back. Go back to the prior year (if you have one) when the model balanced, and add each line item one at a time, balancing debits and credits as you go.
- Don’t panic! If you’re taking the FMI Level 1 exam, and you’re under time pressure, spend about ten minutes trying to balance it and then move on and come back to it once you’ve finished everything else. Don’t get stuck on finding the error because you won’t pass the exam with an incomplete model.
Common Balance Sheet Errors
Here are some of the most common errors, and the most likely reasons your balance sheet doesn’t balance. Mostly they are minor linking errors, and easily fixed. Rest assured that once you’ve made one of these mistakes, you’re unlikely to make it again, because every model you build makes you a better modeller.
- Missing a row. You might have simply forgotten to pick up a number from one of the schedules where it was calculated. You’ll notice it if you run your eye down the statements comparing prior years.
- Picking up the wrong year. If you’ve gone with a horizontal design rather than a vertical design, it’s important to keep your columns identical on each tab. When linking from one tab to another picking up the wrong year is very easy to do.
- Signs around the wrong way. Showing the number as a positive instead of a negative on the balance sheet or vice versa is another easy mistake to make. Looking at prior years is the best way to pick this up.
- Showing annual instead of cumulative balances. Because the balance sheet is a snapshot as at the end of each year, the numbers are balances (as the name suggests). If you haven’t calculated it as a corkscrew balance on the schedule, you may need to add the number to the prior year to make it cumulative. For example, when calculating retained earnings, don’t just pull through the net income from the P&L, you need to add it to retained earnings balance from the prior year.
This is by no means an exhaustive list as there are plenty more sources of errors but these are the most common ones and are a good place to start when balancing your balance sheet. Good luck!
The Level 1 AFM Certification from FMI requires candidates to build a full set of financial statements, including the balance sheet with scenario analysis in a four-hour time period. Find out more about the online prep course from Plum Solutions.