To excel or not to excel. What was the formula?
I have worked in many places that have become spreadsheet city. Excel is now so readily accessible that everyone thinks they are an instant expert, just waiting for an opportunity to build yet another spreadsheet. Even if that person knows what they are doing, others may not be aware of its existence or even if that is the best solution for the organisation. Not only that, but management rely upon that information to make decisions when it may contain formulas errors. I have known a bank manager to approve an overdraft facility even though the cashflow forecast did not actually add up correctly. Excel is flexible and deceptive easy tool to use which is also one of its dangers. What most people don’t realise is that when they create formulas they are actually programming. Each formula is a script that will execute a result upon a command. If you think of it like that then that should highlight a series of checks and balances that come with any computer programing language. The following is a checklist to assist you avoiding some of the most common problems.
1. Scope the job: Work out what it is you are trying to achieve. Even if you write this down, it’ becomes clear what needs to be done. It helps to plan a road map, to define milestones and to have a measure of success. If you work in a large organisation, run it by other people to make sure that fits in with overall goals or if a similar solution is not already in existence. Nothing worse that recreating the wheel. Many a spreadsheet has been built that never gets used as others haven’t been shown how to use it.
2. Is excel the right tool for the job?: Once the job has been scoped properly, make sure that excel is the best tool to use to achieve the result required. Excel is basically a giant calculator with some text functions. It runs in a flexible single user environment, which is good for producing financial forecasts or running scenarios. It is meant to be written over. As soon as you want to keep historical information and have input by multi users (such as an accounting package) then you are talking database, sql, access etc, not excel. Yes you can get excel to do some of these things but it’s not designed for that purpose. Even though you can password protect areas, if you want data to be recorded and not changed, you need the discipline of a locked database in a multi-layered format. I have made this mistake myself in trying to develop a solution that was beyond excels capabilities. Just because something can be done doesn’t mean it should be done. (Related Article: Difference between Accounting packages and enterprise Software)
3. Use the formula builder: Use this function if you are not sure which type of formula to use. They are organised under sections (text, logical, scientific) and provide a step by step guide to help achieve the right result. The explanations and examples are also helpful.
4. Design it in stages and check as you go: Before you begin make sure each section is defined so you can manage each stage. That may mean defining specific range areas or use separate worksheets for each task. At the end of each section double check your work. That may mean proof reading the formulas or doing the sums on a calculator to make sure you get the same result. Once you are satisfied with that section, save and go on.
5. If there is an error: When a cell produces an error message (#N/A, Error) right click on the cell and that will provide you with options and explanations of what the problem may be.
6. Use formulas to help you: Most errors in formulas occur when people manually build a range for calculation (=B2+C3+D5 etc). Where possible start using IFSUM or IFCOUNT formulas. Not only will that force you to have text descriptors against the next cell but the formulas will do the most of the heavy lifting for you, without the need to manually link data from different cells.
7. Use formula tools: If you need to link cells then under the Formula Bar use the “trace precedent” or “trace dependants” functions to highlight which cells are reliant on others. Be aware that if you use separate worksheets it won’t provide the individual cell information.
8. Use conditional formatting: An under used function on the home ribbon. You can get cells to change colour (or text bold, add arrows, ext.) to pre-defined scenarios. Use this to easily see if a result is being produced that is not expected.
9. Make the input stage simple to use: Even if the backend is doing a lot of complicated and nifty stuff, keep that hidden in the background, preferably even on a separate worksheet. The data entry phase needs to be simple to enter information, navigate and understand. Use colour to define areas and “insert comments box” for explanations. One of my first budgets I designed became so overly compacted that the results were too confusing to interpret. Sometimes you get to stage where you can’t see the wood for the trees.
10. Review: Once built put it through its paces and make sure it’s producing the expected results. Compare this against your original scope of the work. If possible physically proof read the formulas with someone as a fresh pair of eyes always comes in handy. If the spreadsheets is used by others in the department ask for suggestions and comments. Even if they are not excel gurus you can always get ideas for improvement.
11. Lock it down: Once complete “password protect” areas and formulas you don’t want changed. Remember it is in reverse of what you think. All cells are default “locked” when “protection” is enabled. You need to go to “cells formatting” to untick or hide the cell formula.
There are examples of what can be accomplished on our downloads page. We also have a “Favourite formulas” section, upon which you can feel free to add your own suggestions. Happy formula building.
Malcolm Ford has been involved in business consultancy for over 25 years and specialises in building bespoke financial planning tools for Small to Medium enterprises.
