Use Excel to generate invoices and report on sales

From the Not Just Numbers blog:

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.