ExcelZone spreadsheet seminar tips: Think first, Excel second
ExcelZone contributor Simon Hurst was on hand to pass on tips for smoother working with accounting data and spreadsheets.
"Before we get on to the practical stuff, the first thing you need to do when reporting with Excel is to design a good spreadsheet," says Simon Hurst, AccountingWEB contributor. "Most spreadsheets aren't good and most spreadsheets aren't designed. This isn't about designing a worksheet with pretty fonts and colors, but making sure it comes up with the right answers."
According to Hurst, the first rule of reporting with Excel is, "Don't use spreadsheets as much as you do now. The most error-prone and badly designed spreadsheets are those that contain things that shouldn't be on a spreadsheet.
"Spreadsheets are unstructured and make it very difficult to control the way you input, store, and access data. If you want to work with large quantities of data, a spreadsheet isn't the only tool you need. You may want to get data by bringing it across from a third party database."
One reason Excel presents so many traps for the unwary management accountant is because Microsoft has added database functions such as sort, filter, and lookup that lure users into applying Excel to inappropriate tasks, Hurst explained. He touched in his talk on Excel's Data Validation functions, but advised that if you are using spreadsheets to input data, it takes a lot of time and effort to structure them properly, for example to make sure a field that requires a date will only accept a date.
From the reliability standpoint, one-off worksheets do not pose such a huge risk. But the minute you start to use Excel for reporting and want to add figures every month is when you need to start paying attention, he explained.
"Most people have some sort of idea of what they want to do with a spreadsheet. After a couple of hours setting the spreadsheet up, they realize they could have done it better another way," he said. Instead, he told the audience to "think first, Excel second".
"You need to sit down and think about your design and to ensure it will be adaptable when things change such as inflation, or the way you might deal with changes in sales growth rates for example."
Instead of turning on the computer, Hurst advised accountants to get a large sheet of paper and a pencil and then "sketch out what the spreadsheet should do." Afterwards you can screw up the piece of paper and throw it in the bin, but at least you will have thought about the worksheet before you started working on it.
"You will have made your mistakes on the paper. You will end up with a clearer, more reliable and adaptable spreadsheet – and may do it quicker," Hurst said.
Practical Excel Reporting Tips
Hurst then fired up his demonstration spreadsheet and suggested a series of practical tips for accountants from his guide, 100 ways to save time with Microsoft Office.
1. Use a lot of worksheets
Put different things on different sheets and clearly name the sheets so other people might be able to tell what they're about.
2. Make sure your spreadsheet is well documented
Misunderstandings can arise when the person who designed a sheet leaves the organization. "When you come back to a spreadsheet you worked on a few weeks or even days ago, you can forget yourself what you were trying to do," Hurst said. "It's a pretty good idea to have an 'About' sheet explaining who wrote the report, what it sets out to achieve and any sources of data to which it is linked."
3. Document your assumptions
Everyone makes assumptions and different people have different ideas - for example to build inflation into a formula or not. "Try to force yourself to think about the assumptions you make and write them on a worksheet," said Hurst.
4. Build in checks and controls and create an exception reporting sheet
Usually figures should fall within a certain range of each other, or be the same as a built-in checksum. "There are lots of checks you can build in – they're very simple and very important. Don't hide them. Put them in a sheet at the front with exception reports. Someone can look at the sheet and will have confidence in it."
5. Never mix data and logic
Rigorously separate the data in your sheet from any logic. Entered figures and constants need to be in their own separate cells, clearly labeled and easy to change. "Never have a formula that includes data. A formula such as =C4*1.175 is a disaster waiting to happen," he said From a practical point of view, it makes it awkward to change the data. You've got to find the cell and fiddle with the formula. Conceptually it's a bad idea because they might change the formula itself as well as the data so you will always need to completely re-check the sheet to make sure it is giving the right answer. "You give it to someone else to change some figures, which they can do in five minutes. But it could take you hours to check what they have done," Hurst warned. If you ensure that any cell in your spreadsheet containing a formula is locked, you can stop them from being changed. A well designed spreadsheet must be able to protect every cell with a formula in it.
6. Work vertically
This helps users understand where to look for totals.
7. Keep your formulae consistent
Once you have devised a formula, apply it methodically to your data cells. If the formulae are consistent, it's easier to check that they're right. Tools in Excel XP and later can help highlight any inconsistent formulae for you.
8. Use 'Names'
The Name box to the left of the Formula box at the top of each spreadsheet can be used to name individual cells or selected blocks. Click the cell(s) you want to use, then type in a suitable label in Name box. Make sure to press Return to make the name "stick." The name will now be stored in a pull down menu accessible from the Name box. When you select a name from this menu, the cursor will go straight to that area. "Naming is very good for tracking things – especially for workbook to workbook links or if you are linking another application to Excel," Hurst said. "For example, if you report on figures in Excel in a Word document, you can give the area (or single cell) a name, and your Word document should be able to reflect changes in the structure of the Excel sheet that it's linked to."
With time running short on his presentation, Hurst offered a final tip: "Click Insert-Function and look at all the different options that are there. The list may include the answer to something that you've done in a very long-winded way. But do make sure you fully understand how a function works and check that it gives the correct answer before you use it."
As a quick example, Hurst showed how the INDIRECT function lets you construct a formula that uses a text string as part of a reference. In a reporting spreadsheet on a series of local offices, he explained, you could use Indirect to refer to the Dorking, Maidstone, or Chatham worksheet to see their results. "You don't want to have to point a formula at every place and get a different sheet. Changing the INDIRECT reference to Dorking changes the sheet reference from which you view the data."