A different approach to sales forecasting: Using a spreadsheet

European Management ]ournal Volume 7 No 3 0 European Management journal 1989 LSSN 0263-2373 $3.00

A Different Approach to Sales Forecasting: Using a Spreadsheet X A Proctor Lecturer in Management Sciences University of Keele

The author shows how spreadsheet software, based on IBM and compatible equipment, can be used in sales forecasting. It can be taken further to produce models of stock-holding in the leisure clothes sector, particularly in ladies’ swimsuits, is demonstrated. The reader is taken through the practical steps of this model showing the spreadsheet The power and flexibility of the spreadsheet is clearly shown.

Importance

of Forecasting

A Role for Spreadsheets

A popular subject in marketing is sales forecasting. It plays a very important role in planning, not only with respect to marketing activities, but also with respect to other functional areas of a business. The sales forecast is a key input to the finance and production budgets and provides a guide for determining manpower requirements. It is an essential component of the process of resource allocation in a firm. There are many general treatises on the importance of sales forecasting and overviews of the methods. A good example is to be found in Cohen (1988). Makridakis (1983) provides an excellent discourse on the details of forecasting methods, as does Firth (1977). An earlier text, Battersby (1968), presents useful approaches. There are specialised texts on the subject of “forecasting”, many of which are cited in the abovementioned references. There are several methods of forecasting sales and in essence the methods are divided into two camps: (i)

objective and

estimation

as it appears on the screen.

using statistical

(ii) subjective estimation, commonly the literature as “guesstimation”. The general advice to all forecasters on just one method of forecasting.

methods,

referred to in

is never to rely

In this article, I show how a spreadsheet can be used to help in sales forecasting. First thoughts might suggest that spreadsheets are most suitable for use in statistical forecasting. However, I would hasten to add that this is not entirely true. Computers are primarily information-processing machines and a spreadsheet can easily be used to handle qualitative data, admittedly introducing an element of quantitative analysis - Laric and Stiff (1984) provide an excellent illustration of this. My remarks in this article apply to using computer software which runs on IBM and compatible equipment under the MS DOS operating system. The comments I make, however, can be generalised to most forms of spreadsheet running on different operating systems. One of the more popular spreadsheets available is Lotus 123 (TM). It is expensive, but is generally acknowledged to be the market leader and from the forecaster’s point of view possesses the most Other spreadsheets are all sophisticated features. capable of handling the forecasting routines I discuss here, but most lack the sophisticated “regression” feature which is available on Lotus 123 (TM) and which is an important tool for forecasters interested in using some of the more elaborate forecasting techniques.

A DIFFERENT

APPROACH

Nevertheless, all spreadsheets possess the capability of introducting “regression” subroutines, though the forecaster would have to write the routines as formulae into the cells in the spreadsheet. Other popular packages include VisiCalc (TM), VP Planner (TM), SuperCalc (TM), Symphony (TM), Smart (TM) and Aseasyas (TM). They are just some of the well known names which include spreadsheets as part of or all of the package. Of these, Aseasyas (TM) is a very cheap and reliable spreadsheet, available through most Public Domain Software Houses for a nominal fee.

TO SALES

Jackson (1985,19&3) illustrates the use of spreadsheets for forecasting purposes. In addition she has produced a computer disk which is supplied along with the book and which is of great help to less computer-literate persons and to those who are too lazy to enter the formulae into the spreadsheets via the keyboard. Jackson (1985) provides templates for “trend, seasonal and random components in a time series”, “moving “exponential smoothing”, “trend and averages”, seasonal variations” and “trend curves”. Jackson (1988) extends her previous treatment to include regression analvsis. Laric and Stiff (1984) illustrate how spreadsheets can be used to accommodate “expert opinion forecasting” and include models which look at “trend and seasonality” and the general area of “regression“. The latter also present an excellent chapter which looks at how one can estimate market size and potential. In the case of the Laric and Stiff publicaton, the user has to key in all the spreadsheet data - though a disk is available separately from the authors. Spreadsheets are not the only micro-computerised methods of forecasting. Statistical packages such as NCSS and STATGRAF contain modules which will facilitate standard forecasting procedures. There is also a number of computer packages available through Public Domain Software Houses which will undertake all the calculations required for standard approaches to statistical forecasting.

USING

A SPREADSHEET

359

requirements. In using spreadsheets, the forecaster has a flexibility that is not available in the “black box” statistical programming approach mentioned above. Furthermore, the nature of the process of forecasting, using a spreadsheet approach, is such that it enables the forecaster to gain a greater understanding of the forecasting process, which is extremely important to him.

An Example Swimsuits Examples of Forecasting Models utilising Spreadsheets to be found in the Marketing Literature

FORECASTING:

-

Forecasting

Sales of Ladies’

The example I present is based on illustrations provided by Battersby (1968) - but with modifications. The firm is a manufacturer and marketer of a variety of leisure clothes, including ladies’ swimsuits. The market for ladies’ swimsuits is large but the firm has no idea of the actual size of the market. In this example, the owners of the business are aware that the market has in fact reached saturation level and that there is no overall trend in market demand. The firm’s own sales records exhibit a sales pattern which varies by as much as 20% from one year to the next. Variations appear to be influenced by fads in taste or fashion and it is only by keeping in touch with other members of the trade that company directors can obtain information on whether the current fad will have a positive or a negative effect on sales from one year to the next. Forecasting annual sales is therefore a relatively subjective affair. The Marketing Director of the company estimates what he will sell from one year to the next, based upon feedback from the trade and then sets out specifically to achieve this objective. The main forecasting problems for the firm relate to seasonal variations in demand and to the problem of deciding how much to produce for stock over a given period. It takes the firm two months to deliver against an order. The policy of the firm is to aim at placing orders for replenishment at regular intervals of three months. The model outlined below assumes that the forecaster has derived an estimate of the annual demand but has not estimated seasonal variations or the requirements for buffer stocks, re-order levels or re-order quantities. It is these three things that the model seeks to establish.

The Power of a Spreadsheet The advantage of a spreadsheet approach lies primarily in the fact that one can move beyond forecasting itself and produce models which suggest implications for specific areas of the business. In the example below, I show how the forecast can relate to stock-holding

Figure 1 shows the spreadsheet as it appears on the screen of the micro-computer after all the data and formulae have been entered. I will refer to a single cell by the cell reference letter and number, e.g. B5, and to a range of cells as follows: Bl-B5, meaning cells Bl to B5 inclusive.

360

R.A. PROCTOR

Figure I

The Spreadsheet as It Appears on the Screen

A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56

B

Sales Forecasting

-

Month

Jan

Season2

Variation Sales 1986 305 436 527 1473 1302 2167 2999 2300 1115 1689 1000 711

Feb Mar

AP* May

Jun JuI A%

SeP Ott Nov Dee

C

D

ROL, ROQ 86-88 1988 Total

_ 1987 398 442 1376 669 1988 1366 1324 2700 1208 1203 886 765

771 423 444 589 1105 1800 2345 2167 1409 1875 1087 604

16 024 1335.3

Month

1986

Interpolated Errors 1987

1988

- 273.3 -84.33 - 342.3 475.66 - 248.3 303.66 690.66 - 174.3 -215.3 146.66 - 77.33 - 69.33

- 38.75 63.25 648.25 - 186.7 579.25 -355.7 -842.7 367.25 19.25 -329.7 -49.75 126.25

309.75 19.75 - 308.2 -291.2 - 328.2 53.75 153.75 - 190.2 195.75 317.75 126.75 -59.25

Feb Mar

APr May

Jun JuI Aug

SeP Ott Nov Dee

14 325 1193.7

1474 1301 2347 2731 4395 5333 6668 7167 3732 4767 2973 2080

14 619 1218.2

buffer stock required buffer stock required

May June July August September October November

(1 month) (2 months)

Reorder Level 2106 2787 3653 4411 5022 4044 3244 2991 2097

In Figure 1, cells B5-B16, C5-Cl6 and D5-D16 contain the previous three years sales. Sales figures are shown for each month of the year. If a firm has more than three years sales data to enter, then it is appropriate to have one column for each year, and

86-88 Mean 491.33 433.66 782.33 910.33 1465 1777.6 2222.6 2389 1244 1589 991 693.33

44 968 1249.1

average of the interpolated standard deviation of the interpolated

Month January February March April

F

G

H

Seasonal Variation 757 815 466 338 -215 - 528 - 973 - 1139 5 - 339 258 555

Forecast Sales 343 285 634 762 1315 1628 2073 2239 1095 1439 842 545

Impact on Stocks,

Total Mean

Jan

E

errors

13 204 1100.3

3E-14 318.18

623 710 Reorder Quantity 3705 5016 5940 5407 4773 3376 2826

extra columns have to be inserted. As we will see below, the spreadsheet will contain numbers which are entered directly and numbers which are derived from formulae which have been entered into cells in the matrix instead of data. The formulae are not normally

A DIFFERENT APPROACH

: 3 4 5 6 7 8 9 10 11 12

13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

USING A SPREADSHEET

In Figure 1, cells B18-D18 show the total annual sales for each of the three years, whilst cells B19-D19 show the mean monthly sales for all of the years. These figures are obtained from formulae which have been entered in these cells. The formulae are shown in the key to Figure 2. For cell B18 the formula is QSUM (B5-B16), for cell B19 the formula is @AVG (B5-B16). Formulae for the other four cells may be read off from the key.

Behind the Spreudsheet

The Workings

A B C D E F G H Sales Forecasting - Seasonal Variation - Impact on Stocks, ROL, ROQ Sales 86-88 - 86-88 Month 1987 Total Forecast 1986 1988 Mean Sales Sales Sales 305 398 771 Jan Feb 436 442 423 t;z; Mar 527 1376 444 1473 669 589 t;i,’ APr 1302 1988 1105 [W May 2167 1366 1800 Jun WI VW 2999 1324 2345 Jul WI WI 2300 2700 2167 WI W121 A% 1115 1208 1409 WI WI Sep Ott 1689 1203 1875 W41 Nov 1000 886 1087 WI Dee 711 765 604 WI P161 Total Mn B

Jan

Feb Mar APr May

Jun Jul A%

A

(B5 - (B$19 - $G5)) (B6 - (B$19 - $G6)) (B7 - (B$19 - $G7)) (B8 - (B$19 - $G8)) (B9 - (B$19 - $G9)) (BlO - (B$19 - $GlO)) (Bll - (B$19 - $Gll)) (B12 - (B$19 - $G12)) (B13 - (B$19 - $G13)) (B14 - (B$19 - $G14)) (B15 - (B$l9 - $GlS)) (B16 - (B$19 - $G16)) B C

13 204 (H18112)

VW D

C

Interpolated Errors 1986

SeP Ott Nov Dee

WI

id181 id191

A

1987

1988

(C5 - (019 - $G5)) (C6 - (C$19 - $G5)) (C7 - (C$19 - $G5)) (C8 - (C$19 - $G5)) (C9-(C$19-$G5)) (ClO-(C$19-$G5)) (Cl1 -(C$l9-$G5)) (Cl2 - (C$19 -$GS)) (Cl3 - (C$19 -$GS)) (C14-(C$19-$G5)) (Cl5 - (C$19 -$GS)) (Cl6 - (C$19 - $G5)) D E F

(D5 - (D$19 -$GS)) (W-(D$19-$G6)) (D7-(D$19-$G7)) (D8-(D$19-$G8)) (D9 - (D$19 - $G9)) (DlO-(D$19-$GlO)) (Dll - (D$19 -$Gll)) (D12-(D$19-$G12)) (D13 - (D$19 -$G13)) (D14-(D$19-$G14)) (D15 - (D$19 -$GlS)) (D16 - (D$19 -$G16)) G H

average of the interpolated errors standard deviation of the interpolated errors buffer stock required (1 month) buffer stock required (2 months) Month January

361

reference letter and number are shown and the formulae can be read off from the “key“ to Figure 2. This appears below Figure 2.

visible when we look at a spreadsheet. In the illustration shown in Figure 1, no formulae are shown, although some of the numbers shown are in fact derived from formulae which have been entered into a cell or range of cells. In my description below, I will indicate whether a number has been entered from the keyboard or whether it has been derived from formulae in a cell or range of cells. Where a formula is involved, I will refer readers to Figure 2 where the formula can be found. Figure 2 is an exact replica of Figure 1 except that it shows the formulae for cells and not the numbers generated by the formulae. Where the formulae are too long to show in the figure, the cell Figure 2

TO SALES FORECASTING:

Reorder Level id451

Reorder Quantity if451

362

R.A. PROCTOR 46 47 48 49 50 51 52 53 54 55 56

February March April May June

_

July August September October November

Key:

Q SUM(B5.. D5). @ISUM(B16.. D16) @ AVG(B5. D5). @SUM(B16. D16) ($F$19 - F5). . ($F$19 - F16) ($H$19 - G5). . ($F$19 - G16) @ SUM(B5. .B16). . Q SUM(E5. E16) @ AVG(B5. ,816). . . @AVG(D5. D16) @ AVG(F5. . F16) @ AVG(B25. B36,C25. C36,D25. D36,E25. E36) Q STD(B25. B36,C25. C36,D25. . D36,E25. . E36) @ INT(1.96’G39) @ INT(l.l4*F41) QINT(H6+H7+$F.S42). @INT(H15+H16+$F542) @ INT(H8+ H9 + HlO) . Q + H15 H16)

[e5]. [e16] [f5]. . [f16] k51. kl61 [h5]. [h16] [b18]. . [e18] [b19]. . [d18] If191 ;:z; ;z:; (d45]. (f45].

[d54]

. [f51]

Notes: key above to show

what should entire formula. [e5].

formula and and and Etc.

in in [e6] in [e7] in [e16]

is is is is

in those

.[e16]

where there to cells

insufficient room

the text

to [e16]

@ . D5) Q SUM(B6. D6) @ SUM(B7. . D7) @ SUM(B16. D16)

Some cell references include the “$” sign. The “$” sign is used extensively with spreadsheets to aid in entering and copy formulae across a range of cells. To facilitate understanding of the spreadsheet formula such signs may be ignored. To facilitate entry of formulae into a spreadsheet readers should consult the relevant manual and understand how the inclusion of the “$” sign can make data entry easier.

In Figure 1, cells E5-El6 and F5-F16 sum and average, respectively, monthly data over the three years. Cell E5, for example, is B5+C5+D5. Cell F5 is the mean of these three cells. The numbers shown in cells E5-El6 and F5-F16 are derived from formulae which have been previously entered into the cells. To find the formulae for these cells one has to examine the key to Figure 2. From this it will be observed that E5 contains the formula @SUM (B5-D5), E6 contains the formula @SUM (B6-D6) and so on. Similarly, F5 contains the formula @AVG (B5-D5), F6 contains the formula @AVG (B6-D6) and so on. In order to forecast sales it is necessary to work out what the seasonal variations in the sales will be, assuming that sales are in fact seasonal in nature. In Figure 1, cells G5-G16 contain numbers which reflect the seasonal variation in the data. The formula indicating how these values are obtained are again shown in the key to Figure 2. The formula for cell G5, for example, is E19-F5. Formula for cell G5, for example, is F19-F5. Formula for other cells in the

range can be read off from the key to Figure 2. If we examine Figure 1 it will be observed that cell F19 contains a figure for the mean monthly sales over the three year period. The formula for this cell entry (F19) can also be found in the key to Figure 2 and is @AVG (F5-F16). To obtain the seasonal variation figure for each month we simply subtract the average monthly sales figures (cells F5-F16) from the figure calculated in cell F19. The forecast sales figure for the whole of the next year is entered via the keyboard into cell H18. The forecast monthly sales values, cells H5-H16, take into account the average monthly expected sales figure (H18112 = 1100.3), as calculated by the formula in cell H19, less the seasonal variation figures shown in cells G5-G16. The actual formulae used are to be found in the key to Figure 2. In this example, only three years, previous sales data have been used and, as is pointed out above, these are

A DIFFERENT

APPROACH

entered into cells B5-B16, C5-C16, D5-D16, via the keyboard. These data and the forecast sales for the whole of the year, cell H18, are the only keyboard entries to be made in the whole of the spreadsheet. In order to include more previous sales data, one would need to insert additional columns to the left of B5-B16. The columns headed “Row” and “Average“ would then need to be shifted to the right and the entries in the cells in these columns amended to take account of the extra columns added.

Buffer

Stocks

Having obtained the monthly sales forecast, the next stage is to put formulae into the spreadsheet which will enable buffer stocks to be calculated. These are stocks which the company holds to cater for errors which may arise in producing to forecast. Sometimes the forecast will underestimate demand in any one month and, on such occasions, it will not be possible to safisfy demand. Buffer stocks are held as a contingency to cater for such situations. The first step is to calculate what is known as the “interpolated errors”. This involves making use of the formula which was used to forecast monthly sales, as in cells H5-H16. In this case however, the formula is used to interpolate values for each month in the preceding three years. This means that we are estimating what sales would have beenpredicted for every month on the basis of the formula. The interpolated value is then subtracted from the actual value and the resultant figure is shown in Figure 1, cells B25-B36, C25-C36 and D25-D36. The formula entered into these cells to produce these calculations are shown in Figure 2. Cell H38 is used to check that no errors have been made in setting up the model. The figure which appears in this cell should be close to 0 - a slight difference will usually be found, which reflects rounding off errors in the calculations. Cell H39 calculates the standard deviation of the interpolated errors. We assume that the interpolated errors are normally distributed with a mean of 0 and an estimable standard deviation. We might thus anticipate that 95% of the interpolated errors will lie within 1.96 standard deviations of the mean - i.e. zero. It is the standard deviation of the interpolated errors (cell H39) which we use to determine the appropriate level of buffer stocks. From the point of view of estimating potential stock-out, we are only interested in one-half of the distribution of interpolated errors (i.e. the situation where we would have produced to

TO SALES FORECASTING:

USING A SPREADSHEET

363

forecast but could have sold more than what we forecast), since the other half of the distribution would not involve us in stock-outs (i.e. if we produced to forecast and sold less than what we forecast). In the illustration here, we see that the standard deviation of the interpolated errors is 318.18 (cell H39). Now applying the rule above, 1.96 x 318.18 = 623 (approx.), as is shown in Figure 1, cell F41 (the formula is to be found in the key to Figure 2). Since we are only considering one-half of the distribution of the interpolated errors, we can be 97.5% confident that the interpolated error is unlikely to exceed 623 in any one month. We make use of this assumption in making predictions about the future. We assume that if we produce to forecast and hold buffer stocks of 623 we should be able to meet demand on 97.5% of the occasions. That is, we assume that the forecast will not underestimate demand by more than 623 on 97.5% of the occasions. These figures are based on the assumption that our production lead-time is one month and that the interpolated errors are normally distributed. if it is felt that 97.5% of occasions is an unsatisfactory level of achievement, then we could improve on this by increasing our level of buffer stocks. Multiplying the standard deviation by 3 instead of 1.96, for example, would reduce the probability of a stockout to 0.00135 - i.e. one could meet demand on 99.865% of the occasions. Percentage points of the normal distribution can be found in the statistical tables of the appendix of any good statistics textbook. The level of buffer stocks required is also dependent on the delivery lead-time. In the illustration, we have assumed so far, that this is one month. In fact we will recall that the lead-time is two months. If the lead-time is n months then we must multiply the buffer stock estimate for 1 month by the square root of n. Cell F42 provides an estimate of the required buffer stocks based upon the assumption of a lead-time of two months. The formula for cell F42 appears in the key to Figure 2.

Re-order

Levels and Re-order

Quantities

Re-order levels are obtained by adding the expected sales during the lead-time to the buffer stock. In the model we are assuming that the lead-time is two months, hence at the end of January the expected sales will be the total for February and March. In the example this is 285+634 = 919. If we then add in the buffer stocks of 710, we then obtain a re-order level of 1629. Re-order levels for subsequent months are shown in cells D45-D54 and the formula for these cells will be found in the key to Figure 2.

364 R.A. PROCTOR

Orders placed at the end of January will be received at the end of March and used up during April, May and June. The total expected sales during this period are 762 + 1315 + 1628 = 3705 which represents the reorder quantity for January. Re-order quantities for subsequent months are shown in cells F45-F51. Once again the formulae for these cells will be found in the key to Figure 2.

Using the Model for Stock Control

January but below the 2106 ROL for February) then an order is placed on production for 5016 swimsuits which represents the ROQ for February. Often, it helps to look at things graphically rather than through the medium of tables of numbers. The accompanying graph (Figure 2) can be produced from the spreadsheet. Re-order levels and re-order quantities are depicted graphically, month by month.

Purposes Summary

In any month, as long as the total stock, both on hand and on order, is above the re-order level figure for that month, then no action is necessary. If the level falls below the re-order level in a particular month, then the re-order quantity is triggered. For example, suppose in January stock levels fall to 1500 then an order is placed on production for 3705 swimsuits. If stock levels remain at above 1629 during January then no order is placed. However, should the level at the beginning of February be say 1800 (above the ROL for

Spreadsheets are a powerful computer tool and can be used to advantage when trying to forecast sales and work out a stock holding policy. In this article I have illustrated only one of many models which can be developed to aid in the task of forecasting. Interested readers should refer to the texts I have mentioned in the references to find further examples which they can either use directly or modify to suit their own particular purpose.

Reorder Levels and Quantities

1.5 January _

Figure 3

February

March

Reorder Level

Graph of Spreadsheet Data

-

April

May

Reorder Quantity

June

July

August

September

October

A DIFFERENT

APPROACH

References Battersby A Sales Forecasting, Pelican, 1968. Cohen W A The Practice of Marketing Management, Analysis, Planning and Implementation, Macmillan, 1988. Firth M Forecasting Methods in Business and Management, Edward Arnold, 1977. Jackson M Creative Modelling with Lotus 123 (TM): Chapter 10, John Wiley, 1985.

TO SALES FORECASTING:

USING A SPREADSHEET

365

Jackson M Advanced Spreadsheet Modelling with Lotus 223 (TM), Chapter 6, John Wiley, 1988. Laric M V and R Stiff Lotus 1-2-3 for Marketing and Sales: Chapter 4-5, Prentice Hall, Englewood Cliffs, New Jersey, 1984. Makridakis S, S C Wheelwright and V E McGee Forecasting: Methods and Applications, 2nd Edition, John Wiley, 1983.