Do you deny how much your team uses Excel?

"A prudent question is one-half of wisdom."

Francis Bacon

In too many years of experience helping organisations to make better use of Excel, one truth has become evident:

The higher you move up an organisation, the less they think the organisation uses Excel.


I am currently talking to many accountancy practices and this seems even more true in that industry.


Causes of Excel Blindness
There are a number of reasons why this should be true but it can be incredibly damaging for reasons I will go into later in this post.


I believe that the main causes are as follows:

1. The day-to-day experience of those involved - The higher you go in any organisation, the more your role involves relationships and meetings, rather than hands on number-crunching. Naturally those producing the numbers and analysing them use Excel far more than those discussing them;
2. Belief in computer systems - Having invested in accounting and/or ERP packages (or in the case of accountancy practices, accounts preparation software), and been sold the omnipotent nature of these packages by the software companies, it can be difficult to believe (or face) that there are still large amounts of work done on spreadsheets.
3. Over-simplification of what processes involve - A manager, for example, may understand that a member of the team is emailed a particular piece of information from a customer,a supplier, another branch or another department, and that they enter this into the system. The member of the team involved will know that they receive this information as an Excel file and they apply numerous sorts and filters, delete columns and rows, calculate totals, etc. before they enter it (or even import it) into the system. All of this work is done in Excel - and the manager is completely unaware.

Why does it matter?

It matters because it can be incredibly damaging to an organisation in terms of both financial risk and inefficiency

Financial Risk

Not understanding the systems that your business relies upon can lead to errors in systems management don't even realise are being used. The controls in the ERP system or accounting software may be perfectly adequate, but if the information entered is coming from a spreadsheet with an inbuilt error, they won't be of much use.

Inefficiency

In most organisations, this is by far the greater cost. Many staff are using Excel for a great deal of their time and have very little training or understanding of how best to use it. Many hours can be cut from most employees' working weeks, with a little bit of focus on this area.

Let is use the situation described earlier to demonstrate both the efficiency and financial risk elements:

"The member of the team involved will know that they receive this information as an Excel file and they apply numerous sorts and filters, delete columns and rows, calculate totals, etc. before they enter it (or even import it) into the system."

This whole process could be automated in Excel so that a couple of hours of messing about, could be reduced to a couple of clicks. This not only saves those couple of hours every month, week, or even day, but ensures that the same conversion is applied to this spreadsheet each time, significantly reducing the risk of error.

What can be done?

The first step is to acknowledge how much your organisation uses Excel. No matter how sophisticated your systems are, there are many jobs around the edges of the system for which Excel is not only the best tool, it is often the only tool capable of the flexibility required. Acknowledging this can lead to some very quick improvements to efficiency.

Ensure that you have access to someone (either inside the organisation or from outside) who can look at this Excel use and suggest and/or implement improvements and train staff. I offer this type of service to UK accountancy practices via my Excellent Accountancy business, but please feel free to email me if you wish to discuss how to go about it for any other type of organisation.

Excel is not just Word for numbers, it does form parts of your key processes - whether you like it or not.

Imagine if you took the same lax approach to any other key processes in your business.

A little time and/or money invested in improving how you use Excel will go a long way, as it is such an untapped area in most businesses.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Use Excel to generate invoices and report on sales

I come across many small businesses who use Excel (or Word) to produce an invoice template that they can email or print and post to their customers.

Usually the sale detail is entered on the face of the template and this is saved as a separate file for each invoice. The problem here is that to be able to report on this information for accounting purposes, or sales analysis, the information needs to be recorded somewhere else in one file (either another spreadsheet, or an accounting software package).

What is really needed is to be able to enter the sale details once and for this to be used to populate both the invoice template and the sales reports.

If an accounting package is being used, the best answer would usually be to use an invoicing module in the accounting software and ditch the external template, however if this is not an option, or an accounting system isn't used, then we need the invoice template and the sales history to be linked.

Here is my approach to this problem, in the simple scenario where each invoice has one line of detail. If more lines are required a variation of this approach can be used, but this is a little too complicated to go into in this blog post.

General Approach
This approach involves three main worksheets within the one spreadsheet, Customer Data, Sales Data (what accountants often call a Sales Day Book) and the Invoice Template.

Customer Data
This worksheet, as the name suggests, is where all of the customer data is held. The sheet should follow the rules for laying out data that I outlined in an earlier post (How to lay out data in Excel). It should have column headings for name, address 1, address 2, etc. and one row for each customer, new customers are just added to the bottom of the list as needed.

The first column should be a unique identifier for that customer, it may be possible to use the name for this but you would need to add a new customer on a change of name rather than editing the existing one. Often better is to use the approach used by most accounting systems and allocate each customer a unique Account Code. I often use Conditional Formatting on this column to identify any duplicates.

The spreadsheet will use this sheet to provide the customer address details for the invoice.

Sales Data (or Sales Day Book)
This sheet should again follow the rules mentioned above. Here the business will record all sales, one line per sale. Headings should include (at least) Invoice No (in the first column), Date, Amount, Customer Account Code (the unique identifier mentioned in Customer Data above) and Description. Depending on needs the amount may be a calculated column, multiplying entries in columns for price and quantity. The business may also need a column for VAT or any other sales tax. The Customer Account Code column should use Data Validation to restrict entries to those codes in the first column of the Customer Data sheet.

The VLOOKUP function can then be used to pull in the customer name and address details in columns to the right of the entered data, using the customer account code to look that information up from the Customer sheet.


Invoice Template
If the business is already using an Excel template then this can be used as the basis of this sheet, otherwise some work will need to be done to produce a satisfactory layout for the invoice.

Ultimately there should only be one editable cell on these sheet - the Invoice Number which should be a dropdown based on the Invoice Number column of the Sales Data sheet.

VLOOKUP is then used on the rest of the invoice to pull the Customer Name and Address, Date, Description, Amount, etc. into the appropriate cells from the Sales Data sheet, based upon the Invoice Number selected.

I would recommend that to email the invoice to a customer, that it is printed to pdf first. This not only gets round the problem that all of your other sales data is in the same spreadsheet, but looks far more professional than sending a spreadsheet to the customer.

Reporting
As the sales data is all correctly laid out and, depending on the size of the business, at least a year's worth of sales can be stored in the one spreadsheet (usually many more) then Pivot Tables can be used to report on these sales in any way that is required.

Just don't forget to make sure you take regular backups and there is no need to record your sales anywhere else.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.