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.

Adding categories to data in Excel - how to save you time later

In my previous post, I stated that one of the major problems with how most Excel users lay out their data, is using a column for each category.

In the feedback I have had from that post, it was felt that this point needed further explanation and/or an example, so I thought I would provide both here.

First of all, here is the point as it appeared in the original post (it was point number 3):

Don't group data by putting it in different columns (THIS IS THE ONE THAT ALMOST EVERYONE GETS WRONG)

• Don't split out financial or numerical data into separate columns to categorise the data into months, expense categories, customers, agents, etc.
• Do have one column for the financial or numerical data and create a column for month, expense category, customer or agent, to categorise each row;
• You can use data validation drop-down lists to select the appropriate category for each row;
• This one is counter-intuitive because in any report, you will almost certainly will want a column (or row) for each of these categories - but if you do this in the data you will massively restrict what you can do with it.

Let's look at some sales data laid out the wrong way:

I have left out any extra data (other than the date) to keep it simple. With the data laid out like this, you could use the SUM function to calculate monthly totals, but you can't do a lot more than that. If you were to use the data in a pivot table, you would have to add the data as 12 data fields, making it very cumbersome and inflexible.

Also, if you wanted to do any calculations on this data, such as calculating VAT, or any other Sales Tax for that matter, you would need another 12 calculated columns!

You then get into further problems if you want to analyse the data from another perspective - by salesperson for example.

A better approach 

Now let's see a better way to lay this data out:

You can also automate the month column using the following formula in cell C2:

=CHOOSE(MONTH($A2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

Laid out like this you can use the month as a way of analysing the amount in a pivot table for example:

Its real power however comes when you wish to add additional analysis and calculations, so for example you could add additional analysis columns for Salesperson and Country, and a VAT column (being 20% of amount):

You can then produce all manner of pivot tables, here are just a few examples:

It would be just as easy to show the months as columns - the only reason I haven't is to make best use of the space.

And remember, you can at any stage easily add further analysis or calculation columns as your reporting needs change.

I hope this has explained this point in more detail and even more importantly, highlighted the value of getting it right!

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.

99% of Excel users get this wrong - How do you lay out your data?

"Learn the fundamentals of the game and stick to them. Band-Aid remedies never last."

Jack Nicklaus (Champion US golfer) 

When someone comes to me with a problem in an existing spreadsheet, the problem is invariably in the layout of the data. The spreadsheet is built for one purpose and works OK for that until something slightly different is required and it proves almost impossible to get the report that's needed.

If a few simple rules are followed when laying out your data, then producing additional reports from that data, and using it for different purposes, becomes simple, instead of the nightmare it is for many users.

These rules apply to any lists of data, be it monthly financial information, transactional data (such as lists of sales, purchases, payments or receipts), customer or supplier lists. If you are going to store data in your spreadsheet to produce reports from, you need to follow these rules.

At the heart of these rules is the approach - you are not laying out your final report here, you are laying out the data in a format that can be reported from! These are two very different things (see my OAP approach to reporting in Excel).

The rules to follow:

1. Columns with headings and no gaps
• Every column should have its own UNIQUE heading, in the first row;
• There should be no empty columns;
• These columns represent the fields of a database, e.g. Customer Code, Customer Name, Telephone Number, Email Address, etc.
2. One row per record and no gaps
• Every record should have all of its data on one row. E.g. in the above example, one row per customer;
• There should be no empty rows;
3. Don't group data by putting it in different columns (THIS IS THE ONE THAT ALMOST EVERYONE GETS WRONG)
• Don't split out financial or numerical data into separate columns to categorise the data into months, expense categories, customers, agents, etc.
• Do have one column for the financial or numerical data and create a column for month, expense category, customer or agent, to categorise each row;
• You can use data validation drop-down lists to select the appropriate category for each row;
• This one is counter-intuitive because in any report, you will almost certainly will want a column (or row) for each of these categories - but if you do this in the data you will massively restrict what you can do with it.

The benefits:

• Data following the rules above is perfectly prepared to be analysed using countless tools within Excel, for example: pivot tables, autofilter, SUMIF, COUNTIF, etc.
• Most changes to the data don't require a change to the data layout. New categories, e.g. expense categories, customers, agents, etc. can just be added to the drop-down lists. Any new entries in these columns will be automatically picked up by pivot-tables, autofilter, etc. with no work involved.If you had to create a new column each time, you would also need to edit every report that used the data.
• You can choose to analyse the data by any category you want. It takes seconds to edit a pivot table that has a column for each month and change it to a column for each expense category. This is almost impossible if the data was laid out in those columns.
• You can add additional category columns to the data if needed and these can even be calculated from the data. You might, for example, introduce departments - simply add a department column to the raw data, and your pivot tables can analyse the data by this category as well, or instead of existing categories.

As you can see, if you lay out your data according to these rules, you can do pretty much anything you want with it. The spreadsheet can grow with your business, and with any additional reporting requirements you want to add.

It can take a little bit of time to get your head around point 3, but believe me, you'll be pleased you decided to be among the 1% that get this right.

If you'd prefer me to redesign your spreadsheet for you, just visit www.needaspreadsheet.com and let me know what you need and I will send you a fixed price quote.

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.

Connecting with those we're trying to help

"The more you explain it, the more I don't understand it."

Mark Twain

Constantly, as accountants, we need to communicate important messages to non-accountants and, in doing so, we risk coming across as detached number-crunchers, resulting in our message not really connecting.

No matter what our technical ability, if we do not have this power to connect, we don't get the chance to add the value we know we can!

I experienced this last week at the breakfast networking group that I attend every Thursday morning.

I have been going for around 3 months, with a 60 second talk every week to explain what I do (develop spreadsheets to help businesses streamline their admin). I had so far had a few pieces of business come from it, but not a great deal.

This week, I had a 10-minute slot and chose, rather than wax lyrical about what I do, to demonstrate what I had done the previous week for one of the other members. This was a relatively simple spreadsheet to record customer and sales information, with various reports from the data.

I demonstrated how the business owner could now record each piece of information once and use that same information to (at the click of a mouse) produce his invoices and sales reports, and track the success of his marketing efforts, as well as manage his callbacks.

I could almost hear the pennies dropping around the room.

I picked up three new opportunities straight away, and pretty much everyone (including a visitor who had never been before) said how every business they know would benefit from what I had shown.

The lesson I took from this is that we have to put ourselves in the other person's shoes and (and I think this is the key) demonstrate what the result means to them. Without them seeing this, anything we say will fall on deaf ears.


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.