10 tips to professionalise your Excel workbooks.

 

People that know me will tell you that I’m an Excel geek and I’m ready to admit that I love finding out all the new funky features Microsoft pile in at every new release.

It’s not just an academic interest though; as an accountant I spend my life in excel whether it be budgeting, forecasting, management accounting or organising a project. Often clients will call me in to sort out issues they are experiencing with budgeting and forecasting or they’ll want me to set up systems to solve a particular problem.

However I’m the first to admit that Excel does have some drawbacks. Some of these are functional problems with the system, but in my opinion the vast majority of problems encountered with the software are actually of our own making.

In this post I’m aiming to give you some tips to help you avoid these issues and hopefully make your complex excel workbooks more robust and accurate.

Tip number 1just because you can do something doesn’t mean you should.

They say a little knowledge is a good thing and as my excel skills have increased over the years I’ve noticed an interesting phenomenon.

When people only have basic knowledge they use simple techniques.

When they start to get good at excel then they are desperate to use their new found knowledge so they pack in as many functions, features and formats as they can.

The interesting thing is that the most advanced excel users will actually use less of these.

Advanced excel users live by the acronym KISS (Keep It Simple Stupid). They understand that whilst they know exactly what is going on most people wont and that often the people inputting into the spreadsheet will be basic users. Consequently they go out of their way to make it as simple and user friendly as they can.

Tip 2Include an info tab.

Due to your stunning Excel work you’re probably going to get promoted or headhunted. You may be producing a workbook for someone else to use or you may go on holiday or (hopefully not) get sick.

In any case whether it’s planned or not the fact remains that there is a 99.99% chance that someone else is going to use your workbook in some capacity at some point.

I always include an ‘info’ tab at the front of the workbook. This tells users what the book is designed to do, where the information comes from, how it is processed and who it goes to. If I’ve made significant assumptions then I’ll also detail these and if there are any processes that people need to follow such as exporting a bank statement for use in the workbook I’ll usually have a ‘how to’ guide in there.

Including an info tab means that your workbook is much more likely to remain in use and your colleagues will be able to work out what’s going on.

Tip 3separate out data, processing and reporting.

If you are guilty of holding these three items all on the same page then you’ve probably come across issues when you try to change the formatting of a report, add or remove columns and rows or just due to the sheer complexity of the sheet.

One of the key methods of reducing the likelihood of errors is to have a separate report tab, a tab for data processing and a tab for data entry.

By keeping things separate you can make changes to your reporting without worrying that you will be compromising any formulas or base data. Similarly if you regularly paste data into your sheet you know that you won’t be pasting over important formulas or ruining your report.

Tip 4Colour code your tabs.

A simple colour coding based on function helps users understand where to find things.

If the whole department (or even better the company) use the same standard then you’ll find that it’s easy for any user to pick up any workbook and instantly know where the base data is held and how to find the reports.

Check out tip number 10 for more.

Tip 5give your workbook to someone else

One of the best ways to work out whether you are on the right track is to hand your workbook to a colleague and ask them to work out what is going on.

Don’t give them any instructions just let them get on with it.

By getting a colleague to look over your workbook you’ll find there will be things they don’t understand that to you seem obvious. This is because you’ve designed the thing and are way too close to see the issues.

This comes from my work as an interim where I know that I won’t be around forever and often people will hand my work on to someone else with no handover. I figure that if anyone can pick up my workbook with no instructions, understand what it is trying to achieve and also work out how to do it then my work is done.

Tip 6Separate out your formulas

Look we know you’re great at formulas and you can nest 16 levels of whatifs.

Just because you can doesn’t mean you should (see tip 1) and you should really stop showing off.

Having massively complex formulas in a single cell just makes it harder to unpick when something goes wrong. Instead look at having formulas in separate cells so that each can be understood and checked when errors arise.

Tip 7Don’t you dare use a white font on a white background.

Just don’t.

It makes it much more likely someone will overtype your data or formulas, will delete the row it is on or won’t be able to find what you are pointing at.

If you have to then hide a row or column but please use tip 3 so that you actually don’t need to hide anything. Hiding stuff should be done sparingly because it just increases the likelihood of unintended consequences.

Using a white font on a white background to hide stuff is as dumb as a dumb thing.

Tip 8enforce version control.

Having many versions of the same spreadsheet flying about the company is a recipe for disaster. The problem is that everyone turns up at a meeting with a different set of numbers that are supposedly from the same source.

If you need to have many people inputting then think about using sharing services like Onedrive or give them individual tabs for their input that you can then paste back into your master version.

It’s sensible to make sure that complex workbooks have a single owner. That person is responsible for making structural changes and making sure that input gets into the right place. They are the go to person for questions problems and issues.

Also make sure that you number your versions so that you know which is the latest.

The original is version 1 (v1), Small changes increment the number after the full stop (V1.1) and large changes mean that it gets a new version number (V2.1). Alternatively at least append the workbook name with the date it was produced.

Tip 9understand where you want to go before you set out.

As with any project it’s best to sit and have a good old think about what you want to achieve from your workbooks before you start building formulas.

I’ll often mock up the reporting page first and get the users to sign up to it or suggest changes.

Once I have my destination set then I know what things I need to do to get there.

I’ll know what data I need to collect, how I need to categorise it, what time frame it needs to span and how I need to manipulate it.

I should also have a clear idea as to how the consumers of the information see the workbook being used in the future.

On a side note, just because people say that the workbook is only going to be used once you shouldn’t believe them.

I find that if you do a great piece of analysis once you’ll get the same request three months down the line so bear that in mind when you are building your ‘one off’ ad hoc report.

Tip 10 – the best one of all; use the FAST standard.

FAST is an attempt to produce an internationally recognised standard for producing complex workbooks in Excel (or any other spreadsheet for that matter).

You can find a link at the end of the article

Although there is some pretty dry stuff in there, there is also some exceptionally useful ideas that make life an awful lot easier if you put them into practice.

At the very least, download the standard, read it and then use it to produce your own company standard. I promise this will reduce the number of errors you experience and make your life easier.

So there we are, a few useful tips to make your workbooks less random and a bit more professional.

If you’ve got any particular questions then please do comment and I’ll do my best to answer.

The Advert

I help my clients with their finance issues.

I’m a professional interim and one of my areas of expertise is in budgeting and forecasting.

If you need some help with your process or want to look at new ways of doing things then contact me here and we can have a chat.

 

The FAST Standard

Isango8 - providing project management and accounting support for SMEs in the South and South West

What should be in the board pack?

A Board of Directors has a series of responsibilities that can only be discharged properly by having access to the right information – but how do you decide what should be in the board pack?

One of the difficulties of sitting on a board, especially if you are a Non Executive Director (or NED) is that the focus needs to change from the day to day running of the company to a more strategic and oversight role. This is where many NEDs suffer because it’s a natural reaction, especially if you have many years experience in a managerial capacity to want lots of in depth material. It’s a reaction that can prove counter-productive because lots of detail can lead to a loss of focus on the job directors are there to do.

So how do you decide what needs to be included in the board pack?

This is almost like a ‘piece of string’ question because companies are all different. Larger companies may have more onerous reporting requirements and a bigger board but there are some general rules of thumb that can help.

Executive Summary – and it really should be a summary. It’s sponsored by the MD whose aim should be to give the board an overall high level view of what has happened since the last board. It’s the entree if you like to the more meaty reports from the leaders in the functional areas. Of course often the MD feels they are the most important person in the room and will try and write a novel but that’s not the point of the exercise. It really is only the precursor to the main event. Keep it to a side of A4 if possible.

KPIs – this again should be a high level report based around the key drivers of the business. The KPIs themselves should be chosen well in advance by the board and should be a result of their understanding of how the business runs and what they key drivers are. A really well designed KPI report will include around half a dozen metrics that will encapsulate exactly how the business is doing and will again fit on one side of A4. In an ideal world each of the functional departments will have a KPI that they will report on in subsequent sections so that the whole pack forms a pyramid.

Finance – Above all the board are the keepers of the shareholders’ money so a finance report is a key component of a board pack. Remember though that your FD is a numbers man/lady and they’ll want to include information down to a low level. They really should only give you the information one level down from the KPI’s and then use this as a method of stimulating questions because it’s the discussion in a board where the real value lies.

Marketing – for any company the driver to success has to be an effective and active marketing department. For companies that practice a customer focused methodology then it’s crucial as marketing will influence everything from product development right through to after sales servicing. Of course marketers are fantastic salespeople and will love to tell you everything in great detail. Again they need to be focused on providing a level of information appropriate for strategic discussion and not how many hits the website has had.

Operations – This is probably the area where companies will differ the most but of course it is also the engine room of the business. This report needs to be all about how the firm goes about it’s work. What’s working well and what not so. What does the company need to do differently and what investment needs to be made to make things better?

Compliance – often firms need to report out to an external body such as OFSTED, CSCI etc. It’s important that these bodies are given comfort that their concerns are given prominence at board level but also that the firm internally considers matters of compliance all year round and not just at reporting time. Again this needs to be at a high level and could take a similar form to the risk register.

Risk/Audit – The Directors are custodians of the firm so they need to be mindful of risks that may turn up in the future, assess these and mitigate where required. A comprehensive and updated risk register is the key here and again forms the spur to discussion. Similarly they need to plan an audit and work on the findings and the level of information will naturally change just before and after an audit.

Projects/special reports – there are points throughout the year where something may be happening that requires The Directors attention. It may be the findings of the remuneration committee or a large company wide project that is occurring but whatever it is the board need a good high level overview of how things are progressing.

PESTLE – this really goes to the heart of what the board are there to do. It doesn’t need to take the form of the ubiquitous PESTLE (Political,Economic,Sociological,Technological, Legal and Environmental) format but there needs to be an appreciation of the firm’s place in the world and the external factors that could affect it. After all if The Directors don’t understand where the rocks are how will they steer the ship?

It’s also really important to think about how this information will be consumed and used. SME NEDs often only get one day a month paid and will be expected to attend a meeting for half a day. The pack needs to be light enough that it can be read and understood within an hour or so. It needs to strike a balance between enough detail for good understanding but not too much that it takes an age to read because trust me – people won’t read it. Remember also that we are all different. I hate paper information, some people love it so be prepared to provide it in whatever way is most comfortable for your individual Directors.

Consistency is important as time spent looking for where the latest sales figures are will put people off but also mean that they have less time to actually understand the numbers. Keep a consistent format and style for each report each month and make sure that the pack is distributed at the same time every month so that the directors know when to expect it.

The aim of the board pack is to educate and inform but also to stimulate debate. As a general rule of thumb one quarter of the meeting should be given over to presentation of the reports for each area and three quarters should be allowed for discussion because as started earlier this is where the real value is added. The overriding message has to be focus,focus,focus.

Although there is no ‘standard’ board pack, following the guidelines above should get you most of the way towards a good level of content for your company to make the most of it’s Board of Directors’ talents.

Isango8 specialise in information presentation. If you’d like us to help you reformat your board pack and identify your KPIs then please get in touch for an informal chat and we can tell you how we can help.

 

Information presentation – how to use groupings

Groupings and space

In this video we show how the way you group your data and your use of space affect how easy it is to read

By using these methods you can ensure that your information is much easier and quicker to read. Removing the need for fancy fonts, lines and graphics through the simple use of space.