Articles by CompAcct Solutions Ltd.

Power Excel
Financial Statements

A Training Course in Microsoft Excel for Accountants
PXL4FS logo
Find out more about this e-course

Financial Spreadsheet Quality Control - Article 2


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:
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:

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. 

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

Last updated: May 4, 2022
Copyright © 2022 CompAcct Solutions Ltd. All rights reserved.