Spreadsheet version control (using only Excel’s inbuilt tools)
It is critical that spreadsheet version control is maintained, especially with Sarbanes Oxley spreadsheets as it is extremely easy to create new, or alternate, versions of the same spreadsheet.
If a good naming convention is used it will be easier to identify which is the correct version, and to keep an effective audit trail of the spreadsheet as it changes over time. Software does exist to address this issue systematically so this is just a manual work around.
For SoX critical spreadsheets, a version must be saved and maintained for each reportable event (e.g. quarter end results, year end journals etc).
How to implement spreadsheet version control
Combining a naming convention with a discipline of recording and saving changes reduces the risk of work being lost, provides an audit trail that is useful in checking the results of changes made, and in aids in recovering from any loss due to technology problems.
Suggested Naming Conventions for spreadsheet files
A naming convention should be adopted which will enable the model file name (e.g. Year End Journals Dec 2005 v3.2.xls) to convey the following information:
- Model name “Year End Journals”;
- Date “Dec 2005” (you may even need to give an exact date and maybe a time for frequent changes)
- Version number “3”, a new version being associated with a significant change to the model e.g. change in method of calculation, inserting new workbooks etc;
- Edition number “.2”, a new edition being an event defined purely for the convenience of the user and developer e.g. different sets of inputs, alternate scenarios etc.
It is equally important to set up a meaningful directory structure to separate different periods and events. For long- lived models it may also be helpful to set up separate directories for different reportable periods or years, and to include the period and year in the names of files.
The filename and directory of a model should be clearly shown on all print outs, and may be automated with a formula such as =CELL (“filename”) or put in the header and footer of the spreadsheet worksheets. This will make sure that even on printouts you will be able to see what version is being viewed. The date should also be shown, e.g. by using a formula such as =NOW () or including it in the header and footer.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.