Making Baselines Out of Sales Data
You need a way of summarizing individual sales records into a baseline for forecasting. Most companies that are in the business of selling products and services record their sales on a daily basis, whether they’re recording revenues or the number of units that were sold.
You can usually tell from their corporate accounting system how many dollars they brought in on May 4 and on October 12, or how many widgets they sold on February 8 and on August 25.
The accounting system usually breaks out individual sales. So, if the company made ten sales on June 3, you’re going to see a different record for each of the ten sales. Seeing those sales one by one is great if you’re an accountant, or if you have some other reason to need information about individual sales, or if you’re having trouble sleeping. But if you’re forecasting, individual records are a nuisance.
Consider the following ideas while deciding the best way to summarize your sales data:
- You don’t need individual sales records. If your company made three sales on January 5 — one for $2,500, another for $8,650, and another for $4,765 — an important fact you want to know is that, on January 5, you brought in $15,915.
- You don’t forecast sales on a daily basis. If your company is like most, you need a bigger picture. To plan your inventory levels, decide how many salespeople your company needs, and figure out what you can expect in revenue and what your company’s tax liability will be, you need a longer time period such as a month or a quarter for your forecast.
- You do need to match the length of your time period with your reasons for forecasting. Typical time periods are a month, a quarter, or a year, depending on why you’re forecasting. For purchasing materials, you may want to forecast your sales for next month. For estimating earnings, you may want to forecast your sales for the next quarter. For hiring decisions, you may want to forecast your sales for the next year.
The point is that if you’re going to forecast sales for next month you need to organize your baseline in months: how much you sold in January, in February, in March, and so on. If you’re going to forecast sales for next quarter, then that’s how you need to organize your baseline: how much you sold in Q1, in Q2, in Q3, and so on.
You need a much longer baseline than just three periods to make a forecast that won’t embarrass you.
Excel’s pivot tables are ideal for helping you total up your sales data to establish a baseline for forecasting. You feed your raw sales data into Excel, where you can build pivot tables in two primary ways:
- From an Excel table: Suppose your Accounting or IT department can send you sales data in a soft copy format, like a .csv (comma-separated values) file. You can paste that data into an Excel workbook as a list, convert that list to a table, and base a pivot table on it.
- From (what Excel calls) external data: In other words, the underlying data, the individual sales figures, aren’t stored in an Excel worksheet. They’re kept in a separate database or a text file or even another Excel workbook.
Building your pivot tables on external data can be handy because the sales data are usually updated routinely in the external data source (in practice, this is often a true relational database, as distinct from a flat file like a standard Excel list or table). Then when you want to update your forecast, you don’t have to get and paste new data into your workbook. The pivot table can update itself automatically from the external data source.