Excel Sales Forecasting For Dummies - Carlberg Conrad

Скачать книгу

smoothing is closely related to moving averages. Just as with moving averages, exponential smoothing uses past history to forecast the future. You use what happened last week, last month, and last year to forecast what will happen next week, next month, or next year.

      The difference is that when you use smoothing, you take into account how bad your previous forecast was – that is, you admit that the forecast was a little screwed up. (Get used to that – it happens.) The nice thing about exponential smoothing is that you take the error in your last forecast and use that error, so you hope, to improve your next forecast.

      If your last forecast was too low, exponential smoothing kicks your next forecast up. If your last forecast was too high, exponential smoothing kicks the next one down.

      The basic idea is that exponential smoothing corrects your next forecast in a way that would have made your prior forecast a better one. That’s a good idea, and it usually works well.

      Method #3: Regression

      When you use regression to make a forecast, you’re relying on one variable to predict another. For example, when the Federal Reserve raises short-term interest rates, you might rely on that variable to forecast what’s going to happen to bond prices or the cost of mortgages. In contrast to moving averages or exponential smoothing, regression relies on a different variable to tell you what’s likely to happen next – something other than your own sales history.

Getting the Data Ready

      Which method of forecasting you use does make a difference, but regardless of your choice, in Excel you have to set up your baseline data in a particular way. Excel prefers it if your data is in the form of a table. In Part 2, I fill you in on how to arrange your data so that it best feeds your forecasts, but following is a quick overview.

      Using tables

      

There’s nothing mysterious about an Excel table. A table is something very much like a database. Your Excel worksheet has columns and rows, and if you put a table there, you just need to manage three requirements:

      ❯❯ Keep different variables in different columns. For example, you can put sales dates in one column, sales amounts in another column, sales reps’ names in another, product lines in yet another.

      ❯❯ Keep different records in different rows. When it comes to recording sales information, keep different sales records in different rows. Put information about a sale that was made on January 15 in one row, and information about a sale made on January 16 in a different row.

      ❯❯ Put the names of the variables in the table’s first row. For example, you might put “Sales Date” in column A, “Revenue” in column B, “Sales Rep” in column C, and “Product” in column D.

Figure 1-1 shows a typical Excel table.

      FIGURE 1-1: You don’t have to keep the records in date order – you can handle that later.

      Why bother with tables? Because many Excel tools, including the ones you use to make forecasts, rely on tables. Charts – which help you visualize what’s going on with your sales – rely on tables. Pivot tables – which are the most powerful way you have for summarizing your sales results in Excel – rely heavily on tables. The Data Analysis add-in – a very useful way of making forecasts – relies on tables, too.

      For years, Excel depended on an informal arrangement of data called a list. A list looked a lot like a table does now, with field names in its first row, followed by records. But a list did not have built-in properties such as record counts or filters or total rows or even a name. You had to take special steps to identify the number of rows and columns the list occupied.

      In Excel 2007, Microsoft added tables as a new feature, and tables have all those things that lists lack. One aspect of tables is especially useful for sales forecasting. As time passes and you get more information about sales figures, you want to add the new data to your baseline. Using lists, you had to define what’s called a dynamic range name to accommodate the new data. With tables, all you need to do is provide a new record, usually in a new row at the end of the table. When you do so, the table is automatically extended to capture the new data. Anything in the workbook – charts, formulas, whatever – is also automatically updated to reflect the new information. Tables are a major improvement over lists and this book makes extensive use of them.

      You find a lot more about creating and using tables in Chapter 6. In the meantime, just keep in mind that a table has different variables in different columns, and different records in different rows.

      Ordering your data

      “Ordering your data” may sound a little like “coloring inside the lines.” The deal is that you have to tell Excel how much you sold in 1999, and then how much in 2000, and in 2001, and so on. If you’re going to do that, you have to put the data in chronological order.

      The very best way to put your data in chronological order in Excel is by way of pivot tables. A pivot table takes individual records that are in an Excel table (or in an external database) and combines the records in ways that you control. You may have a table showing a year’s worth of sales, including the name of the sales rep, the product sold, the date of sale, and the sales revenue. If so, you can very quickly create a pivot table that totals sales revenue by sales rep and by product across quarters. Using pivot tables, you can summarize tens of thousands of records, quite literally within seconds. If you haven’t used pivot tables before, this book not only introduces the subject but also makes you dream about them in the middle of the night.

      Three particularly wonderful things about pivot tables:

      ❯❯ They can accumulate for you all your sales data – or, for that matter, your data on the solar wind, but this book is about sales forecasting. If you gather information on a sale-by-sale basis, and you then want to know how much your reps sold on a given day, in a given week, and so on, a pivot table is the best way to do so.

      ❯❯ You can use a pivot table as the basis for your next forecast, which saves you a bunch of time.

      ❯❯ They have a unique way of helping you group your historical data – by day, by week, by month, by quarter, by year, you name it. Chapter 8 gives you the details, as well as much more information on pivot tables, including troubleshooting some common problems.

Making Basic Forecasts

      Part 3 gets into the business of making actual forecasts, ones that are based on historical data (that is, what’s gone on before). You see how to use the Data Analysis add-in to make forecasts that you can back up with actuals – given that you’ve looked at Part 2 and set up your actuals correctly. (Your actuals are the actual sales results that show up in the company’s accounting records – say, when the company recognizes the revenue.)

      The Data Analysis add-in is a gizmo that has shipped with Excel ever since 1995. It includes a convenient way to make forecasts, as well as to do general data analysis. The three principal tools that the Data Analysis add-in gives you to make forecasts are:

      ❯❯ Moving Averages

      ❯❯ Exponential Smoothing

      ❯❯ Regression

      Those are the three principal forecasting methods, and they form the basis for the more-advanced techniques and models. So it’s no coincidence that these tools have the same names as the forecasting methods mentioned earlier in this chapter.

      

The

Скачать книгу