The input required for a model to evaluate the probabilities of future sales is a list of existing clients’ and prospects’ names, together with estimates of what they are expected to spend on the firms’ products over the forecast period. The probability of the sales actually occurring, represented as a percentage value, is entered and the estimated sales value is multiplied by the probability to
EXPECT.XLS
Financial Planning using Excel
66
produce the expected value. The expected value for all the clients and prospects is totalled and this value is used as the forecast of the total sales revenue for the forecast period.
Figure 6.1 shows the spreadsheet on which the data is entered and the expected values are calculated. This data can be found in the file EXPECTon the CD accompanying this book. For the purposes of this example, only a small sample of data has been used, but in a real business situation this would be much larger.
Figure 6.1 Model for analysing expected sales values
This technique relies on the fact that with a large list of clients and prospects, the probabilities will average themselves out and the end result will be close to the forecast expected value. The formula for the expected value is:
Expected value⫽ Forecast value * Probability
67
Financial Planning using Excel
The model does not contain many formulae. A simple multiplication in the Expected value column multiplies the forecast sales value by the probability, and in the totals row the forecast sales and the expected values have been totalled and the probability has been averaged using the ⫽average function.
The totals have been entered using the ⫽subtotal function as opposed the more common ⫽sum function because the ⫽subtotal function allows cells to be totalled subject to a criteria. The formula in cell E33 is⫽SUBTOTAL(9,E4:E32).
Where 9 means that only cells that are on view are to be summed.
The significance of this will become clear in the next section when the list is filtered to show only selected clients and the totals will reflect the totals of the filtered list.
The ⫽subtotal function has options 1–11 which refer to different functions. For example, ⫽SUBTOTAL(1,D4:D32) will average the cells that are on view as opposed to summing them. More information on the use of this function can be found in the Help function within Excel.
Extracting data
Having entered all the data into the spreadsheet it can be used to select subsets of clients based on specified criteria. For example, it might be useful to know the clients who are listed with a probability of more than 0.5.
Excel has a feature called Autofilter that can be used for this purpose.
Autofilter is switched on by placing the cursor on any of the cells in the list area (A3:E33 in this example) and selecting DATA FILTER
AUTOFILTER. This places a small arrow to the right of each column heading in the expected value table. By clicking on the arrow in the Probability column a series of options are displayed which includes Custom. Figure 6.2 shows the custom dialogue box which allows the required criteria to be entered – in this case ‘greater than (⬎) 0.5’.
The results of the above query can be seen in Figure 6.3. Notice that the rows containing probability values that do not satisfy the crite-ria are hidden and the totals have been recalculated to reflect the items currently in the list.
Financial Planning using Excel
68
Figure 6.3 Results of query on probability greater than 0.5 Figure 6.2 Criteria selection dialogue box
If a permanent record is required of items satisfying a particular requirement, a report can be produced using the DATAFILTERADVANCE
FILTERcommand.
On a separate worksheet the column headings should be copied twice – once to be used as a reference for the criteria and once as
69
Financial Planning using Excel
the headings of the final report. In the example here, all the head-ings have been copied across, but it is possible to be selective here providing the syntax of the headings matches the column headings on the original list of data. Figure 6.4 shows the prepared work-sheet and the dialogue box for the DATA FILTER ADVANCED FILTER
command.
The results of the command are shown in Figure 6.5.
Figure 6.4 Data Filter Advanced Filter command
Figure 6.5 A permanent report produced using Data Filter Advanced Filter
Financial Planning using Excel
70
It should be noted that the results of this command are not dynamic (rather like the Data Regression command used in an earlier chapter) and thus if the data in the original list is changed, added to or deleted from, this report will not automatically update.
Summary
The composite of individual estimates using expected values tech-nique is particularly useful in situations where there are a large num-ber of clients and prospects, and when there is a sales force who maintains regular and close contact with the client/prospect base.
In these situations, the large number of clients and prospects ensure that the expected value averages out over the whole range, and that the resulting estimation approximately represents the actual sales.
The main problem with this technique is the subjective values involved in setting the probability levels for each client or prospect. If great care is not taken over these figures then the resulting forecast is likely to be of little value.
OPPORTUNITY FOR GIGO