Why won’t my financial model balance?

The creation of a best practice financial model can be both time-consuming and immensely challenging. Although it is important to create a credible forecast or plan, the financial modeler must ensure the financial model is correct and not out of balance. There are some basic things to always consider when composing a financial model for a client or internal purposes.

 

Strong attention to detail

We are human. We can all make basic mistakes; particularly after working the one same spreadsheet for several hours and into the night. Re-sum totals across the financial statements, because the financial model might contain inconsistent formulas or cell referencing.

For example, when calculating the change in Other Current Assets for Cash Flow Statement purposes for 2013, ensure the model’s formula references the corresponding 2013 and 2012 time series on the Balance Sheet worksheet.

When forecasting out the income statement items by business unit, ensure you are referring to the correct cell, row or column of the forecast assumptions worksheet.

Strong attention to detail

 

 

Avoid hard-coding and referencing temporary email files

Hard-coding should be restricted to the model assumptions sheets, otherwise updates to source financial statements, will make the task of locating randomly inputted numbers difficult to locate and reconcile across the entire workbook.

As I have stated before, sourcing financial information is a common error in corporate financial models. The model user will have no idea of the data’s source, because it is referenced from a file attached to a historical email from someone (model developer) else’s computer. There is a strong chance these links will break over time and cause #REF! errors across the financial model.

 

Error Checks

The addition of checks will greatly increase stakeholder confidence with the financial model. Detailed error checks will assist the model developer in understanding why the model is out of balance. Additionally, alert checks should be applied to items to items that clearly should always be positive, such as receiveables, inventory or payables.

There can never be too many error checks, but certain aspects of a financial model should be checked:

● Key bottom line items across the sourced actual financial statements

● Working capital items of receiveables, inventory or payables

● Accumulated depreciation & depreciation expense for the period

● If consolidating multiple business units, assets or management reporting entities, ensure the aggregate totals tie up, or make sense in the context of  certain balance sheet or cash flow statement items.

 

Decimal Rounding in Error Checks

Recalculating specific line items like the net fixed asset value, could result in some small rounding decimal errors. When composing your error checks, ensure to keep the formulas to decimal places ie 

=IF(ROUND(H205,2)-ROUND(Model_Import!G21,2)<>0,1,0)

Decimal Rounding in Error Checks

 

Excel features are all turned on

During the model build if these features, auto calculation is set to “manual” & “enable iterative calculation” is ticked off, it is possible you will incorrectly assume the model is out of balance. Unless the financial model is large in file size, whereby these features slow down the performance and the ability to operate the model, it is best to ensure these features are enabled.

Excel features are all turned on

 

Model Archiving

A financial model build is often an evolutionary experience. Hence it is prudent to archive the model at least once per day. Errors causing your model to be out of balance might occur for some inexplicable reason, hence the ability to A/B test or retrack to an earlier model could be easier. 

 

In the Balance

The composition of a financial model can be a challenging exercise. One challenge is ensuring a balanced Balance Sheet. Adherence to applying decimal rounding in error checks, enabling iterative and automatic calculation features, adding error and alert checks, avoiding hard-coding and referencing temporary email files, and upholding a strong attention to details should greatly improve the probability of balancing the financial model’s balance sheet.