by

Scott M. Smith

and

William R. Swinyard

Internet Text January 1999

May Not Reproduced without the Permission of the Authors

Most firms spend more on personal selling than any other form of marketing communication. A sales force does far more than sell. Allocation of selling effort to customers, to territories, and to developing new business is a key strategic area for firms. In this chapter we examine a way to address some of these problems with a sales call allocation worksheet model.

For many customers, the salesperson is the firm. Consequently, everything the salesperson does reflects on the firm's interest (or lack of interest) in the customer. Aside from the obvious selling tasks, key responsibilities of sales people include establishing accounts, maintaining appropriate and timely customer service, and communicating with the customer about new products and service. Often, sales people also are expected to actively help customers become more successful, and to provide market intelligence for the firm, and to make decisions about customer allocation of scarce products. This chapter discusses a worksheet model called Sales Plan, designed to help sales management allocate those resources better.

A major resource for the sales force in is time. That time is impressively valuable. As long ago as 1980, the typical industrial sales force costs per sales call ranged from a low of $50.40 (in the hospitality industry) to $228.78 (in the transportation industry). Costs to close a sale for these industries were $191.52 and $$1,121.02, respectively. Salespeople must effectively allocate their time to call on accounts, perform necessary administrative functions, and to improve product knowledge and selling skills.

The purpose of sales force allocation models is to improve the effectiveness of the salesperson's time in calling on accounts. Models in this area focus on differences in accounts by allocating different levels of selling effort in order to maximize long-term sales.

The "Sales Plan" model identifies differences in the salesperson's set of accounts. Individual accounts may vary in their need for our products, their loyalty or propensity to buy, and their service requirements. If a salesperson fails to recognize these differences, the firm ignores the potential for a sale or the relative worth of calling on one account over another.

Many accounts will purchase enough to possibly cover the cost of the sales call, but in the long run do not generate enough sales to merit the number of sales calls made to them. Salesperson time would be more efficiently spent by calling on new accounts to add to the customer base, or to key accounts that are the primary source of the company's profit.

Companies developing sales call plans have used several approaches. A simple classification approach might assign all accounts into several categories (A, B, C, or D), based on a single criterion such as the sales potential of the account. The salesperson decides on a specific number of sales calls for each category, and all accounts in each category receive the same number of sales calls. A complex classification approach might model the relationship between the various quantitative and qualitative aspects of a sales call and sales. Sales calls are then allocated using the mathematical function to assign sales calls to accounts in a way that maximizes total sales.

Each approach has its advantages and disadvantages. The simple classification approach is easy for the salesperson to apply. But any improvement in the long run sales over the unmanaged approach may be limited. The complex mathematical decision model approach will probably result in a much better plan for sales call allocation, but may be hard or even intimidating for the salesperson to use.

The Sales Plan model categorizes accounts and allocates sales calls based upon the attractiveness of investing in alternative sales resources. The model considers each attractiveness category independently and develops a specific call plan strategy for that category. Sales call allocation to an individual account within an attractiveness category is based upon its level of attractiveness (i.e., the anticipated level of sales) relative to other accounts in that category.

The attractiveness of an account includes two primary dimensions -- account opportunity and strength of position. "Account opportunity" refers to the accounts' need for our product type and the likelihood of the firm purchasing our product within a particular time period. Account opportunity then, is a measure of market potential.

"Strength of position," assesses the advantages or competencies that our company or salesperson might have with the account. Strength of position could be viewed as a probability assessment; given that the account will purchase the product during the next time period, it is a measure of the probability that the account will purchase from us. Strength of position focuses on the strength of the relationship between the account and our company.

The Sales Plan model identifies four distinct account categories, segments, or portfolios, as indicated in Figure 7-1. Each quadrant in the Figure represents accounts of different attractiveness, and each should have its own sales call allocation plan. The call plan is based upon the characteristics of the accounts in each of the categories.

Portfolio segment 1. Segment (grid) 1, with accounts of high opportunity and high strength tends to include the key accounts for the company. Generally, the primary contribution to the company's profit comes from these accounts. Because of their importance to the selling company, high levels of sales calls should be allocated to this segment.

Portfolio segment 2. Segment 2 contains accounts with high opportunity but whose strength of position is low. Interpretation of this category depends upon the nature of the market in which the selling company operates. Generally, in an expanding or growth market, these accounts represent the new prospects that the company is cultivating. The opportunity for sales is there, however, due to the newness of the account, or to the lack of awareness of the benefits offered by the selling company's product, the selling company has yet to make the account a major client. Investment of sales calls to these accounts is an investment in the future. Sales per call will probably be low, but should not be evaluated as negatively as in other segments. Failure to allocate sales calls to these accounts results in higher short term profits at the expense of long run profitability.

In a mature, stable market, segment 2 accounts reflect more the nature of competition than their being prospects or new accounts. In such a market, any increase in our sales is primarily a result of increase in overall market share rather than an increase in new accounts. In fact, in a stable market, companies in segment two can well be the major source of increased sales and profit for the selling firm. This is the case in an example presented later. The company sells salt, a product well into the mature stage of the product life cycle with little growth in total demand or sales.

Portfolio segment 3. Segment 3, comprised of accounts with low opportunity and high strength, might be referred to as the old friends of the company. Often these are companies that used to be the key accounts of the firm. As their needs or capability to purchase the product have changed, however, they no longer represent our major source of profit. Still, these are companies with which we have long term relationship. It is difficult for the individual salesperson to avoid the temptation to call on these accounts. Generally, they will purchase enough to make the call worthwhile, but the same call could be allocated much more effectively to a segment one or two account.

Portfolio segment 4. Accounts in this segment are marginal accounts. Not only is the probability of a sale is low, but market potential low for the product even if they were to purchase from us. Segment 4 accounts are generally unprofitable when more than a very limited number of sales calls are allocated to them. These accounts are serious candidates for less costly forms of promotion (such as telephone sales), or even for elimination from the portfolio.

Developing the Model. The Sales Plan sales force portfolio allocation model is organized around six sequential steps:

The time period of the analysis depends upon the planning cycle of the selling company and characteristics of the selling situation. One-year periods of analysis usually are appropriate for most firms. If the sales cycle of the firm is unusually long, as with capital equipment for instance, the time period the time period should be adjusted to reflect the situation.

Account opportunity variables should be chosen to reflect the purchasing power of the account and the potential for that account to purchase in the defined time horizon. Variables such as past and expected future sales to the account, growth rate, and financial position of the account would be appropriate. Strength of position variables should assess such matters as the relationship the firm has with the account, and the competitive environment.

Measures of the variables can be obtained using secondary data from our company files, or by surveying our salespeople directly. The advantage of company data, of course, is that it tends to be more accurate and free from individual salesperson bias. The advantage of surveying our sales personnel is that responses can be directly tabulated to generate overall indices for opportunity, and can be indexed across salespeople to adjust for different scales of measurement. The indices are not as easy to interpret, however, as the raw data from the salesperson survey.

Determining the actual segments requires that each index be divided into high and low categories. Once the indices have been calculated and the category break points have been assigned, each account can be categorized into one of the four categories. Summary statistics by category, such as average sales, number of sales calls and sales per call can be generated. These results can be analyzed in comparison with category strategies felt appropriate by management.

Several types of analyses can be performed. Each individual account can be analyzed based on the portfolio results to get a better understanding of its value to the selling firm. The number of accounts in each segment can be analyzed to assess the performance of the salesperson. For example, too many accounts in segment 3 and too few accounts in segment 2 might require counseling about seeking new prospects for future growth. Summary statistics such as average sales calls and sales by segment can be analyzed to evaluate the appropriateness of sales call allocation by the salesperson. Finally, at the district or management level, managers can evaluate appropriateness of territory allocation by considering the categorization of accounts across territories.

The Sales Plan portfolio analysis model is available from the http://marketing.byu.edu web site as an Excel worksheet and includes data to analyze the sales accounts of SaltFlats, Inc., a salt producing company of modest size. In the paragraphs below we discuss the SaltFlats situation, but have simplified it by using only 20 accounts from each of two salespeople.

Three files are used to analyze SaltFlats' sales call performance (STEMP1.XLS, STEMP2.XLS, and SALESPLN.XLS). Only the SALESPLAN.XLS is accessed directly, however. The other two are data files for salespersons #1 and #2, respectively, and are accessed through a normal menu command within the SALESPLN.XLS file. (These two files can be used as templates for developing additional salesperson data files, if you wish.)

SALESPLN.XLS is the file containing summary statistics from the individual files as well as global averages across the salespeople, which are needed to categorize the individual accounts into their respective segments.

It is possible to categorize the accounts into segments based on sales averages within the sales territory. However, doing this would preclude any analysis of differences across territories. Including the global averages file allows the database to be three-dimensional. The first two dimensions can be presented on the screen in a spreadsheet format. The third dimension is created by accessing the global file which contains information across the individual salesperson files. Thus, the global file is dependent upon the two individual salesperson files. In turn, when performing sales allocations, each individual file must have a copy of the global averages, so they are dependent upon the global file as well.

Throughout our analysis of the sales accounts for SaltFlats, keep in mind that they have segmented their sales accounts into the segments discussed above. These segments are called "grids" in the model, as indicated in Figure 7-1.

Running the Worksheet Model. Load Excel, with a backup of the worksheet files on your hard drive. When Excel appears on your computer screen, continue with the commands to Open the File SALESPLN.XLS

The Sales Plan model will be loaded into your computer, and will display its main command in a pop-up menu box:

The commands are invoked by selecting the desired option with the mouse button and then clicking on OK. For example, if you wanted to enter see summary statistics as a percent of all Grids (segments), you would highlight "SUMMARY" and press the OK button. Once you press a menu function a secondary menu appears or the selected feature is initiated. You can initiate another command function by simply selecting another option from the command menu.

Unlike the other models, the "sample" data in the Sales Plan worksheet is not intended to be replaced with new user data. Instead, the data already within the model is permits an analysis of the sales planning appropriate for SaltFlats, Inc. But as with the other models, each function in the command menu invokes a predefined macro sequence which performs a series of operations. Below we briefly discuss each function. Then we will "walk through" an analysis of SaltFlats' sales performance to diagnose areas which need improvement.

Invoking "SUMMARY" displays summary statistics for all four grids (segments). This is also the default screen when the worksheet is first loaded. The percentage distribution of accounts, sales calls, and total sales is displayed, across Grid segments. See Figure 7-2.  

FIGURE 7-2
SALESPLN: SUMMARY SCREEN
        A        B        C        D        E        F        G        H         
1                                                                                
2                         SALES GRID - SALES CALL COVERAGE                       
3   ________________________________________________________________________     
4                     SLSPERSON   CALLS    SALES      OP1      OP2      OP3      
5                                                                                
6   COUNT                    1       20       20       20       20       20      
7   SUM                      1      253   380182   894500    51200       61      
8   SUM OF SQUARES           1    11661  3.5E+10  2.2E+11  4.9E+08      189      
9                                                                                
10  COUNT                    2       20       20       20       20       20      
11  SUM                      2      100   104109   163543    15110       49      
12  SUM OF SQUARES           2     1268  2.7E+09  4.8E+09 17723224      133      
13                                                                               
14  COUNT                  ALL       40       40       40       40       40      
15  SUM                    ALL      353   484291  1058043    66310      110      
16  SUM OF SQUARES         ALL    12929  3.8E+10  2.3E+11  5.1E+08      322      
17                                                                               
18  AVERAGE                       8.825 12107.27 26451.07  1657.75     2.75      
19  STANDARD DEVIATION         15.66347 28365.39 71003.98 3171.091 0.698212      
20  SPREADSHEET DEVELOPED BY DR. CLIFFORD YOUNG, OKLAHOMA STATE UNIVERSITY 

Choosing the "GRID" command displays the original, unsummarized, data behind the information in the "SUMMARY" screen. See Figure 7-3. 

FIGURE 7-3
PORTFOLIO SUMMARY STATISTICS FOR ALL SALESPEOPLE

A        B        C        D        E        F        G        H         
47                                                                               
48                                                                               
49  GRID SUMMARY STATISTICS FOR ALL SALESPEOPLE                                  
50                                 GRID     GRID     GRID     GRID    TOTAL      
51  GRID                              1        2        3        4               
52  COUNT                            12        6       14        8       40      
53  TOTAL SALES CALLS               222       49       48       34      353      
54  TOTAL SALES                  297357   135212    31491    20231   484291      
55  AVERAGE SALES CALLS            18.5     8.16     3.43     4.25    8.825     
56  AVERAGE SALES              24779.75 22535.33  2249.36  2528.86 12107.27      
57  TOTAL REC. CALLS                144       60       84       48      256      
58                                                                               
59                                                                               
60  STATISTICS AS A PERCENT OF ALL GRIDS                                         
61                                                                               
62                               GRID 1   GRID 2   GRID 3   GRID 4               
63  ACCOUNTS                      30.00    15.00    35.00    20.00               
64  CALLS                         62.89    13.88    13.60     9.63               
65  TOTAL SALES                   61.40    27.92     6.50     4.18               
66

Invoking "IMPORT" updates the overall summary statistics portion of this file by merging the individual salesperson files (STEMP1 and STEMP2). This command, and "UPDATE" are only necessary if you have modified the salesperson files (through the "RETRIEVE" command, discussed below).

"RETRIEVE" accesses the individual salesperson files. After invoking this command, you will be given a choice of which file you wish to load. To stay in the SALESPLN model, choose either STEMP1 or STEMP2. Doing so will load and display performance data for either salesperson #1 or #2, respectively, and will display a new screen and a secondary command menu. If you choose STEMP1, the screen will appear as shown in Figure 7-4. The first two columns are just the account number and an arbitrary account name. The next two columns contain total account sales and number of sales calls made during the past year.

The next six columns (use the "QUIT" function to examine those beyond the right edge of the screen) contain data used in calculating the opportunity and strength scores. OP1 is the normal (dollar) demand for salt from the account. OP2 is the account's normal salt inventory. OP3 is an expected growth value for the account (on a 1 to 5 scale, where 5 indicates high growth expectations).

ST1 is a strength measure calculated as 4, less the number of firms selling salt to the account. That is, an ST1 value of 3 indicates that SaltFlats is the sole salt supplier

to the account. ST2 is SaltFlats' market share (percent) with the account, and ST3 is the number of years SaltFlats has dealt with the account. SERVIC represents the level of service required by the account (on a 1 to 10 scale, where 10 indicates high service needs).

OPP and STR are opportunity and strength indices for the account, and GRID indicates the grid segment to which the account has been assigned on the basis of these indices. OPP is calculated as a standard score (a Z-score, having a mean of 0 and a standard deviation of 1) of OP1, OP2, and OP3. Similarly, STR is a standard score of ST1, ST2, and ST3.

ATT is the account's attractiveness, calculated as a slightly modified product of OPP and STR. REC.CALL is the recommended call level, which is calculated as a function of ATT, the GRID, and management judgment about the number of calls appropriate for the account (which is entered manually into the "RECOMMENDED CALLS" row in the "VIEW" screen.

FIGURE 7-4
PORTFOLIO SUMMARY STATISTICS FOR SALESPERSON #1

       A       B         C        D       E        F        G       H            
1                      SALESPERSON #1  DATA  FILE                                
2                     TYPE ALT[M] TO RE-ENTER MENU                               
3   ____________________________________________________________________         
4    ACCT# CLIENT NAM   CALLS    SALES     OP1      OP2      OP3    ST1          
5    18725 AAJ             96   144687  350000    20000        4      1          
6    32530 AAT             35    29992   30000     3000        3      3          
7    28414 AAM             24   111341  300000     5000        3      1          
8    18676 AAI             10     6278  100000     6000        3      2          
9    12795 AAF             10    12801   12000     2000        3      3          
10      90 AAE              6    24949   25000     1000        3      3          
11   31475 AAN              6     3873    5000     1000        3      2          
12   25297 AAL              6     2722    6000     1000        4      3          
13   31575 AAO              6     8375    8000     1500        3      3          
14   32150 AAR              6     2285    6000     1000        3      2          
15   31625 AAP              6     5586    6000     1500        3      2          
16     223 AAB              6     1475    3000      200        3      3          
17    2350 AAC              6      429    2000      500        2      3          
18   32466 AAS              6     8737   10000     1500        3      2          
19   14670 AAG              6      834    1000      350        3      3          
20   32050 AAQ              6     1459    2000      500        3      3

The secondary command menu has these options:

"EDIT" is essentially a "quit" command, which allows you to change or update any of the salesperson data. After editing, you may reinvoke the SALESPLN command menu as above by simultaneously typing <ALT>-<M>.

"VIEW" lets you look at the salesperson's summary statistics, similar to that which you examined in the "GRID" selection, discussed above.

"IMPORT" merges the global averages (from the SALESPLN file) into this salesperson's data base.

"PRINT" prints the salesperson data base. Be sure your printer is turned on and is on-line before invoking this function.

"RETURN" saves the salesperson database and returns you to the SALESPLN worksheet. From there, you can select another salesperson database, or perform other functions.

"QUIT" exits you from the command menu and returns you to the 1-2-3 ready mode, where you can explore the macros. Again, you can re-enter the SALESPLN menu at any time by typing <ALT>-<M>.

UPDATE

Invoking "UPDATE" updates the grid summary statistics portion of this file by merging the individual salesperson files.

This command prints the summary statistics, as in Figure 7-5. Be sure your printer is on, and on-line before invoking this function.

"QUIT" exits from the command menu and permits you to

explore the model and its macros. Once again, you can re-enter the SALESPLN model by simultaneously typing <ALT>-<M>. 

FIGURE 7-5
SALESPLAN: OUTPUT

                      SALES GRID - SALES CALL COVERAGE 
________________________________________________________________________ 
                  SLSPERSON   CALLS    SALES      OP1      OP2      OP3 
 
COUNT                    1       20       20       20       20       20 
SUM                      1      253   380182   894500    51200       61 
SUM OF SQUARES           1    11661  3.5E+10  2.2E+11  4.9E+08      189 
 
COUNT                    2       20       20       20       20       20 
SUM                      2      100   104109   163543    15110       49 
SUM OF SQUARES           2     1268  2.7E+09  4.8E+09 17723224      133 
 
COUNT                  ALL       40       40       40       40       40 
SUM                    ALL      353   484291  1058043    66310      110 
SUM OF SQUARES         ALL    12929  3.8E+10  2.3E+11  5.1E+08      322 
 
AVERAGE                       8.825 12107.27 26451.07  1657.75     2.75 
STANDARD DEVIATION         15.66347 28365.39 71003.98 3171.091 0.698212  

______________________________________________________ 
     ST1      ST2      ST3  SERVICE      OPP      STR 
 
      20       20       20       20   -0.494   -1.364 
      50     1595      222       98   20.670    3.522 
     134   146225     4102      514 
 
      20       20       20       20   -0.651   -1.469 
      52     1635       86      115    0.809    0.420 
     142   150725      524      695 
 
      40       40       40       40 
     102     3230      308      213 
     276   296950     4626     1209 
 
    2.55    80.75      7.7    5.325 -0.65098 -1.46891 
0.630476 30.05307 7.507329 1.367250 20.66967 3.522140

FIGURE 7-5
SALESPLAN: OUTPUT (continued)

SALES GRID Summary Statistics  (Date)
 
GRID SUMMARY STATISTICS FOR SALESPERSON #1 
                               GRID     GRID     GRID     GRID    TOTAL 
GRID                              1        2        3        4 
COUNT                             2        3       10        5       20 
TOTAL SALES CALLS                41      130       54       28      253 
TOTAL SALES                   32714   262306    64218    20944   380182 
AVERAGE SALES CALLS           20.50    43.33     5.40     5.60    12.65 
AVERAGE SALES                 16357 87435.33   6421.8   4188.8  19009.1 
ATTRACTIVENESS TOTALS         12.58    19.41    48.46    15.25 
RECOMMENDED CALL LEVELS          12       10        6        4 
TOTAL REC. CALLS                 24       30       60       20      134 
 
GRID SUMMARY STATISTICS FOR SALESPERSON #2 
                               GRID     GRID     GRID     GRID    TOTAL 
GRID                              1        2        3        4 
COUNT                             2        1       10        7       20 
TOTAL SALES CALLS                39        3       30       28      100 
TOTAL SALES                   50077     6800    28476    18756   104109 
AVERAGE SALES CALLS           19.50     3.00     3.00     4.00     5.00 
AVERAGE SALES               25038.5     6800   2847.6 2679.428  5205.45 
ATTRACTIVENESS TOTALS         12.85     2.73    31.16    15.05 
RECOMMENDED CALL LEVELS          12       10        6        4 
TOTAL REC. CALLS                 24       10       60       28      122 
 
 
GRID SUMMARY STATISTICS FOR ALL SALESPEOPLE 
                               GRID     GRID     GRID     GRID    TOTAL 
GRID                              1        2        3        4 
COUNT                             4        4       20       12       40 
TOTAL SALES CALLS                80      133       84       56      353 
TOTAL SALES                   82791   269106    92694    39700   484291 
AVERAGE SALES CALLS              20    33.25      4.2 4.666666    8.825 
AVERAGE SALES              20697.75  67276.5   4634.7 3308.333 12107.27 
TOTAL REC. CALLS                 48       40      120   

In this chapter we have reviewed sales planning models. Sales planning models can include those to help the sales force in goal setting, to establish an optimum sales force size, and to allocate sales force effort. We have focused on the latter, with the Sales Plan model. Too, sales planning could be helped with models which deal with organizational aspects of personal selling: recruitment and selection of sales people, the span of control of sales offices, compensation schemes, assignment of sales people to customers/territories, sales control, training, etc.