Why is your Company’s financial model plagued with output errors?
A financial model is an important resource for management in companies. It can deliver a high-level snapshot into the financial performance of your Company.
However its value can be compromised by unchecked financial output errors, which a best-practice financial model will normally flag. There are ways to overcome financial output errors.
Reasons for financial output errors in your Company’s financial model
The source financial data has not been updated. This often happens where there are multiple versions of a source file. It is wise to open the latest file and select “Data” -> “Edit Links” and select “Update Values”. This shall guarantee all financial information is updated.
The model is referencing the wrong columns or rows. This will be a greater risk if the time series of reporting periods is modelled differently across the workbook, i.e. the balance sheet is presented from column O to S, whilst the cash flow statement is modelled from column P to R.
This may sound rudimentary but some financial models are incorrectly modelled in different source currencies or units of measure; model users must exercise a strong attention to detail at times. If your Company has one project or subsidiary that reports its financials in US dollars, then state that clearly and create a Canadian dollars version of this schedule.
Sourcing information in the financial model from a fixed sourced such as a pivot table or a nested formula, which although can be overcome with the addition of a macro to update the values, is something that is recommended to be avoided as it is viewed as moving away from best-practice financial modelling.
Avoid the use of hard-coded variables throughout the model. It is more prudent to centralise such variables into an assumptions worksheet, and maybe even give some variables defined cell names such as financial model start date, or number of periods.
The file format and structure of source files is changed, whilst your Company’s financial model is not open can cause significant heart-ache. Not only must the output financial model be open to reflect these changes, but the import sheet may have to be updated to account for changes in rows, column or worksheet changes.
Your Company’s model has overlooked companies, assets or jv entities that contribute to the consolidated financial statements. Either verify the source financials or contact the source data provider in your Company to reconfirm all entities or projects.
It may be simple errors such as a data version error, whereby the wrong versions of certain business unit models are consolidating into your Company’s financial model. Also check under the options section of Excel to ensure the “Auto-calculation” function is ticked to ensure all cell links and calculations are automatically updating.
An Error-free financial model for your Company
Errors in your financial model can be greatly reduced if you remain aware of the above contributors to model errors. The model developer must maintain a strong attention to detail throughout the model build, ensure all financials are accounted for and in a uniform reporting currency, be mindful of updating all source data readily or in a seamless fashion, and avoid the use of hard-coding numbers sporadically throughout the entire financial model.