Financial Spreadsheet Quality Control

Developing a quality financial spreadsheet starts with planning. Even the simplest spreadsheet typically starts with a plan even if it is just a sketch in your mind. Some spreadsheets may be developed as a request for information from management who may give you a written request for the information or calculations that are required which would then provide an overall plan for the spreadsheet. Complicated linked spreadsheets may even require a schematic laying out the relationships and links between the spreadsheets in a linked package. A schematic can make it easier to pass on the maintenance of the linked spreadsheet package to another user to maintain.

Financial spreadsheets cover a wide range of end use; for example the spreadsheet could be:

A report or pivot table spreadsheet
An inventory spreadsheet
A commission calculation spreadsheet, etc...

So basic planning would start with determining what the end use of the spreadsheet is and who will be using the end results. The end user of the spreadsheet results should be considered in terms of the best way to render the results of the spreadsheets:

Senior management may want a dashboard of results or charts or pivot tables.
A warehouse manager may want a detail inventory spreadsheet
A sales manage may want a gross sales spreadsheet, etc...

With the knowledge of the end use and end users of the spreadsheet; planning should consider the risks associated if the spreadsheets renders inaccurate results which would include both quantitative and intangible measures such as goodwill or lost goodwill.

Senior management may want an ROI spreadsheet on a prospective billion dollar investment where the ROI calculations will determine if the investment will be made. Calculation errors would result in huge financial losses and likely your job.
A warehouse manager may use a detail inventory spreadsheet for ordering where errors would result in stocking issues which can result in loss sales and goodwill between customers.
A sales manage may use a gross sales spreadsheet to pay commissions where errors could easily be corrected but may leave employees a bit sour.

At this stage in planning if you are to be the developer of the spreadsheet you should assess whether you have the skills to develop a quality spreadsheet that will produce the desired results accurately. You should assess your training and experience in using specific spreadsheet formulas and objects that will be required . For example the spreadsheet may require a pivot table but you may not have any experience or training in developing a pivot table. Depending on the risk assessment you may then decide it is best to hire a professional developer for the project rather than attempt to develop an important spreadsheet in training.

Financial spreadsheets typical require data which will be used in spreadsheet formulas and objects. The data may come from a variety of sources; internal and external. Planning for the source of data is important to quality control. If the data comes from an internal source; how will the data be gathered for input into the spreadsheet. Gathering data from an internal source may require technical skills such as the ability to write an SQL query on a database. If technical skills to assemble the source data is required then once again a risk assessment should be done to assess the best way to gather the data; perhaps the IT department will be required or perhaps you will have to hire an independent developer. Gathering data from an external source may increase risk especially where the results of the spreadsheet may be largely dependant on this external data. For example, the Consumer Price Index (CPI) may be used in spreadsheet calculations; therefore the source of CPI must be considered in planning. Would the best source be a government statistical website or a third party website that writes advertised blogs. Planning for data input should not only include the source but also how the data will be integrated into the spreadsheet; for example can the data be input by an SQL query or does the data have to be input manually. If manual data input is required who will do the input; an entry level accounting clerk or a senior. Any time manual input of data is required risk increases through possible input errors such as transpositions or cell position errors. In summary the planning for data to be used in financial spreadsheets should consider the Garbage In - Garbage Out rule.

The planning stage should also consider whether the spreadsheet will be for one time use, such as a report, or whether the spreadsheet be re-used as a template. A report spreadsheet would result in a one time error for that report. A template spreadsheet error would result in a repetitive errors with accumulating costs.

Finally, planning should consider how quality control for the spreadsheet will be implemented. This will be covered in further articles.

Go to our Articles List.

Go to our Home Page

Financial Spreadsheet Quality Control - Article 2

Planning