The rich functionality of Microsoft Excel provides an array of approaches to tighten internal controls of corporate spreadsheets. Some of the below techniques may seem a bit extreme and over the top in some examples, but in other instances they will greatly tighten the management of a financial model.
Auto-close timer
Like a computer, a customised macro can be added in the “VBA Project” (press Alt F11 to open up the window) screen under “Microsoft Excel Objects” and “ThisWorkbook”. The macro will keep time on a workbook’s inactivity and based on the code’s designated time period (i.e. 15 minutes) will save and close the file automatically on the user’s computer. To safeguard the macro’s existence, remember to password protect the VBA Project window, in order to prevent users from changing or deleting the code.
Internal Control Benefits:
- Prevent unauthorised access of the financial model, in case the user steps away from their desk
- Safeguards against a user forgetting the model is open, then moving onto another spreadsheet and then shutting down the Excel application and forgetting to save the idle spreadsheet
Hide & deactivate specific functions and features
This could be controversial among other stakeholders, who will be restricted in their ability to work on a financial model, but it can overcome the problem of errant use of the spreadsheet by other users. Evidently this requires the addition of customised VBA code to force the customisation of Excel, whenever users open and use the financial model in question. This goes above and beyond the boundary of workbook password protection, which can limit users’ ability to use specific Excel features.
Internal Control Benefits:
- It confines users to specific uses of the spreadsheet
Read-only or protect certain ranges
The ability to password-protect or force a read-only version of a spreadsheet on other users is not new. It is clearly a great way to manage spreadsheet integrity and version control. Another protection feature available with Windows 2000 and later is the “Allow Users to Edit Ranges” command. A financial model can be segmented among various users.
Internal Control Benefits:
- It restricts certain users’ ability to edit a spreadsheet or make erroneous changes as in the following
“Radio button” dependent drop down lists
Drop-down lists are a great tool to force spreadsheet to input values from a specific data array. A transaction list of invoice payments may need to restrict users’ to allocate expenses to an operating expense, capital expense or another expense – but not two or all three of them.
Internal Control Benefits:
- Prevents the double or triple accounting of specific line items in a financial model to multiple accounts
Key-log all activity in the financial model
Another customised VBA-driven technique. The owner of the model can keep an eye on the management of the spreadsheet among staff or third parties, which help to prevent erroneous spreadsheet use or fraud.
Internal Control Benefits:
- Helps financial model owners to monitor the use of the spreadsheet, and even identify unauthorised use of the model.
In conclusion
Microsoft Excel offers a rich array of functionality and customised solutions via macros, through which to tighten internal controls around a financial model. Not all of the suggested techniques might be warranted in every company, but for some firms, these advanced financial model controls represent a value-adding approach to improving internal controls.