Financial spreadsheets 101

Simon Selkrig, Strategize Financial Modelling, simon@strategizefm.com, Montreal, QC

The creation of a financial spreadsheet or model may seem a daunting task for most. It is important to write a game plan or have an idea about the information output requirements for users. That way you will better understand your information needs.

A base financial spreadsheet or model should have the following:

• Cover and Table of Contents. Cover worksheet to state the nature of the spreadsheet. i.e. XYZ Inc., 2012 Budget, and the Table of Contents worksheet should list all the worksheets in your workbook; with hyperlinks that enables users to click directly to the specific worksheet i.e. Income Statement.

• Assumptions. These worksheets will contain the data entry or number crunching, such as the time series information (financial year end), company specific information such as the name of the subsidiary, assets or projects.

• Calculations. The detailed calculation of all financial numbers will be undertaken here. Some of the information will be taken from the Assumptions and Model Import (see below) worksheets and presented in a succinct and audit-friendly way.

• Output Summary and Dashboards. A high-level presentation of the financial numbers on one worksheet, coupled with the use of graphical charts to depict the drivers of financial returns.

• Model Imports. The sole repository of all external spreadsheet referencing, which the financial spreadsheet will reference in the Assumptions or Calculations worksheet.

• Ancillary worksheets, Checks and Lookup worksheets. There should be a separate worksheet, similar to the idea of the Table of Contents, which should summarise or Error or Alert Checks in the spreadsheet. An example of an Error Check would be the computed Net Profit after Tax (NPAT) equals the number sourced from a company’s database, and is warehoused in the Model Import worksheet. An Alert Check would ensure that Fixed Assets (Net) on the Balance is not negative.

The Lookup worksheet stores in the customized tables for drop-down inputs in the financial model, for example a lookup table with the months of the year, which is used in the Assumptions worksheet to stipulate the financial year-end of the financial spreadsheet.

As I discussed in 10 ways to improve your financial spreadsheets, it is vital to keep things uniform, simple and concise. There are two prime factors why stakeholders of a financial spreadsheet or model send it to the scrap-heap; even after someone has spent a lot of time developing the spreadsheet. First, users may not understand how the spreadsheet works and where it is sourcing information from. Adopt a logical process flow with a user guide, and place all external sources in the Model Import worksheets.

Second, users may not trust financial outputs, i.e. they may view the spreadsheet to be an audit risk. Once again a user guide would help, but more importantly implementing comprehensive Error and Alert checks across the spreadsheet, would both mitigate against such audit risk concerns.

The development of a financial spreadsheet or model should be kept simple, clear and concise. The user should be able to readily understand the spreadsheet, and refer to a user guide, if need be. Users of the spreadsheet must have total confidence in the calculated outputs of the spreadsheet. Hence there should be a comprehensive roll-out of error and alert checks to verify and validate outputs across the financial model or spreadsheet.