5 Ways to Sort out your Spreadsheets
Business systems get updated periodically, every 5-10 years and in between times, Excel fills in as an easy quick way of getting an answer. But that quick ad hoc answer becomes the long term solution.
You’d expect integrated systems at a company like Orange. But when I worked there, they used huge complicated spreadsheets to do their budgets. Similarly I can’t think of a business who doesn’t do their management accounts in Excel.
But how effective are these spreadsheets? One clients’ poorly constructed management accounts took 3 days to finalise each month after much inputting and cross checking. After spending a few hours to restructure it, it now takes only 3 hours for my client to complete the accounts.
At a networking meeting last week, a director of a timber merchant admitted that taking on a 17 year old to improve their spreadsheets transformed the way many of his staff were able to work.
Can it help your company? Here are 5 ways to sort out your spreadsheets.
1. Create a Project for a Graduate
University students have a good grasp of Excel, yet many fail to mention it on their CV’s.
University students have a good grasp of Excel, yet many fail to mention it on their CV’s. All graduates I’ve worked with have been comfortable with the Microsoft suite of solutions including Excel and are very quickly asked by clients to provide support in this area.
You don’t need to hire an Excel expert, but rather someone who has enough knowledge to improve your solutions, suggest better processes and ensure the buy in of your staff to the changes.
2. Put a Limit on Complexity
I don’t go beyond the use of “Lookup” tables and “SumIf”‘s. I need to be sure the staff can take over and understand the spreadsheet so it doesn’t need my ongoing support. Usually it involves some training & instruction on some of the functions.
I’d consider Macros and Pivot tables or combining with Microsoft Access too complex, requiring the need for ongoing support from the creator.
3. Think Inputs and Outputs
Design the spreadsheet so it minimises the data you input and automates all your outputs.
Think of an Excel spreadsheet in terms of inputs and outputs. Design the spreadsheet so it minimises the data you input and automates all your outputs.
The raw data should be input and captured in one place – it’s only data at this stage so it doesn’t matter how it looks. This creates a single database.
Create new tabs for presenting information and link from the data in the database. Only input data into the database, not into these “output” presentation tabs.
4. Create Trust through Cross Checks
Can the reader trust your figures? If confidence is low, they will go off and repeat the work you’ve done because they don’t trust your figures. You then get into wasted time trying to reconcile the two sets of figures
Better to nip it in the bud beforehand. When you present information, you need to be confident that a figure on one sheet agrees to the figure representing the same thing on another.
So create a separate tab which checks that all the figures on different tabs agree to one another.
5. Use Version Controls
There’s no need to worry about this if you use Google Documents. There is only one file which is overwritten when someone updates it. But most businesses don’t and save the Excel file on their local C: drive.
If a file has a series of updates, it is worth giving each a new version number e.g. v1.0, v1.1 so everyone is viewing the same version.