This technical appendix includes material about the structure of the ADBUDG Model and program notation used in the ADBUDG spreadsheet.

We seek a simple, robust, easy to control model of sales response to advertising. As a first step brand sales is partitioned into product class sales and brand market share. That is, we separately model what is happening to the whole industry or product class of which the brand is a part and what is happening to the brand's share within the class. Such a breakdown has a number of advantages, not the least of which is that marketing people usually think this way. Consider a given time period. We suppose:

1. If advertising is cut to zero, brand share will decrease, but there is a floor, min, on how much share will fall from its initial value by the end of one time period.

2. If advertising is increased a great deal, say to something that could be called saturation, brand share will increase but there is a ceiling, max, on how much can be achieved by the end of one time period.

3. There is some advertising rate that will maintain initial share.

4. Data analysis or managerial judgment of the effect on share can make an estimate by the end of one period of a 50% increase in advertising over the maintenance rate.

Figure 9-10 gives a pictorial representation of this information.

The same data can also be represented as four points on a share response to advertising curve, as in Figure 9-9. A smooth curve can then be put through the points; for example, the function

(1) Share = min + (max - min)(adv)g /[d + (adv^)g ]

The constants min, max, d and g are implicitly determined by the input data.

Equation (1) represents a versatile but nevertheless restricted set of response relations. Actually I am willing to use anything. The curve could go down or up or loop the loop for all I care. It should be changed when and if a product manager wants it changed. Meanwhile, he can give four numbers, each of which has operational meaning to him and which together will specify a curve. It is doubtful that, as of today, we could specify a sales response curve in any greater detail than represented by a smooth curve through four appropriately chosen points.

I now claim that the above structure is robust. Suppose we do a two level spending test and run a regression that is linear in advertising in order to estimate response. Such a regression might make reasonable statistical sense but by itself would have absurd normative implications (advertising = 0 or ¥ ); it would not be robust. However, if the regression results are used to estimate the +50% point and a reasonable max and min are chosen we can expect reasonable answers. This would be difficult to prove in general, but with a specific manager and product it can usually be demonstrated satisfactorily by sensitivity analysis.

To be sure, more sophisticated models and data analyses can easily be suggested. A quadratic term could be put in the regression, for example, but its coefficient would almost certainly be unstable and normatively alarming. A Bayesian analysis or an adaptive control model ... might restore order, but the intellectual cost of such complications is high. Even if more sophisticated studies are done, they could probably be translated into a set of operational terms like the above. In any case we should start simply.

A person might well ask: Is the structure too robust? Conceivably a model could be so constrained that output would be almost decoupled from input. This is hardly the case here. The value specified for the share increase with + 50% advertising is certain to be an important determinant of advertising rate. The values of max and min play the role of keeping changes in a meaningful range.

Incidentally, the sketch in Figure 9-14 shows an S-shaped curve. This is not required by (1). If g > 1, the curve will be S-shaped, for 0 < g £ 1, a concave function. The particular g will depend on the input data.

A major omission in the description so far is consideration of time delays. To take these into account, the model assumes:

1. In the absence of advertising, share would eventually decay to some long run minimum value (possibly zero).

2. The decay in one time period will be a constant fraction of the gap between current share and the long run minimum, i.e., decay is exponential.

Let long run min denote the long run minimum and persistence denote the fraction of the difference between share and long run minimum that is retained after decay. Under the above assumptions:

Persistence = (min - long run min)/(initial share - long run min)

(2) share (1) - long run min = (persistence) [share I - 1)- long run min] + (max - min) (adv(I)] g /(d + [adv(1)]g )

This is a simple dynamic model. It is explainable and it behaves reasonably. It could be further generalized by permitting some of the constants to change with time, but that does not seem desirable at the moment. But now what does advertising mean? Dollars? Exposures? A product manager worries about spending rates, media, and copy. Let us construct two time varying indices: (1) a media efficiency index, and (2) a copy effectiveness index. Both will be assumed to have reference values of 1.0. The model then hypothesizes that the delivered advertising, i.e., the adv (I) that goes into the response function is given by

(3) adv(I) = [media efficiency (I)] [copy effectiveness (I)] [adv dollars (I)]

The media efficiency and copy effectiveness indices can be determined subjectively, but better alternatives exist. Copy testing is helpful and data on media cost, exposures by market segment, and relative value of market segments can be used to develop a media index.

So far we have taken up share response to advertising, media efficiency, copy effectiveness, and share dynamics. Consider next product class sales. Two important phenomena here are seasonality and trend. These and any similar effects can be combined into a product class index that varies with time. Thus

product class sales (I) = [reference product class sales] [product class sales index (I)]

In addition there may be a product class response to brand advertising and corresponding time lags. The treatment of this is analogous to that for share.

A variety of other factors affect share and therefore indirectly or directly can affect the product manager's thinking about the advertising budget. Some of these factors are promotions, competition, distribution, price, product changes, and package changes. These factors are all treated, but in a simple way, not unlike the way a product manager might handle them without a model.

Upon examining the factors, we find that the product manager has a definite idea about what various changes are likely to do for him. If he plans a promotion he does so with the expectation that something will happen to his sales and share. The same holds for a product change or price change. Therefore we ask him to construct an index of how he believes these factors will affect brand share in each period. The process can be formalized by filling in a table such as Table 9-2, listing all factors deemed by the product manager to be relevant. The composite index of non-advertising effects is simply the product of the numbers in each column. Brand share will then be the product of the non-advertising effect index and the share developed from the advertising response relation. For clarity the latter will be called the unadjusted share:

brand share (I) = [non adv effects index (I)] [unadjusted share (I)]

People often ask how product managers can make judgments like the above. The answer is that managers make such judgments all the time but in a less formal and less numerical way. Whenever they take an action they form some belief about what will happen. As a result, it has not proven difficult for them to make estimates that they feel reasonably comfortable with.

Essentially, the model is now specified. However, as we have added time varying effects such as media efficiency and the non-advertising phenomena, we have created a problem for the inputs that determine share response to advertising. What values of the time varying effects are assumed in the share response inputs? To deal with this question we introduce the concept of a reference case. The reference case is a standard set of values against which changes can be measured. The reference case includes a reference time period.

This is not one of the numbered time periods of the calculation but one set apart to serve as a standard. It can be patterned after a real period or can be constructed as a "typical" period. In any case each time varying effect is assigned a value in the reference period. From this data the sales response parameters min, max, g and d are then inferred.

To summarize the model:

wtd adv (I) = [media efficiency (I)] [copy effectiveness (I)] [adv dollars (I)]/r [reference value of numerator]

The units situation has not been developed in detail here and we have omitted the effect of brand advertising on product class sales....

[One additional component of the model needs to be mentioned. It is an output item labeled CUM. ADV. Payout Rate (cumulative advertising payout rate).] This item is intended to answer the question that a user is most likely to ask: Which way should I change advertising to increase profit? But we must ask: What profit? Profit in that period or, since sales changes persist into the future, profit over several periods? We have chosen to anticipate the answer to be "cumulative contribution after advertising" in the last period of the calculation. But which advertising? We expect the question might be asked about advertising in any period. Thus we calculate

SLOPE (I) = the change in cumulative contribution after advertising in the last period, per unit change in adv dollars in I

A positive SLOPE indicates that advertising increases will be profitable (in the above sense); negative, unprofitable; and zero, indifference.

The basic equations defining the model are really quite few. Nevertheless the structure permits consideration of share response to advertising, copy effectiveness, media efficiency, product class seasonality and trends, share dynamics, product class response to advertising, and a variety of non-advertising effects such as promotion, distribution, and price. I feel that the structure meets the criteria of simplicity, robustness, and ease of control. 

Correspondence Between Little's Notations13 and Spreadsheet Variables

s_t S[I] brand sales rate in period I (units/period)

h_t H[l] brand share in period I

c_t T[I] product class sales rate in period t (units/period)

h_t K[I] unadjusted brand share in I

n_t J[1,I] non-adv. effects index in I

W_t W[I] normalized brand advertising in I

e_1t E(1) brand media efficiency in I

e_*1 E0 brand media efficiency reference value

e_2t F(l) brand copy effectiveness in I

e_*2 F0 brand copy effectiveness reference value

X_t X(l) brand adv. rate in t (dollars/period)

X* X0 brand maintenance adv. rate

d_t J (2,1) product class sales rate index in I

c_t V(I) unadjusted product class sales rate in I (units/period)

V_t V(I) normalized product class adv. rate

v* VO maintenance advertising rate for product class (dollar/period)

Mt C(I) brand contribution per unit in t (dollar/unit)

Pt not named brand contribution rate after adv. in I

T N number of periods

B_t A_(t) cumulative contribution after adv. for periods 1 to t (dollars)

Nt B_(t) rate of change of B_T (or A(N)) with x_t (slope)

Pi brand price per unit in reference period

P(I) brand price per unit in I

P2 product class price in reference period

Q(I) product class price in I

R1 through R0 result variables for printing

Z1 through Z5 control variables

M0 share at start of period (reference)

M1 share at end if zero advertising

M9 share at end if saturation advertising

M5 share at end if advertising is 50% above maintenance

L9 minimum long run share

T0

T1

T9 same as above for product class sales

T5

L8

Formulas for Response Function Parameters

Response function for brand share (program notations)

K(I) = L9 + A9 * (K(I - 1) - L9) + (B9 * W(I)G9) / D9 + W(I)G9

Fifty percent above maintenance W(t) = 1. 5

(4) M5 = L9 + A9 x (M0 - L9) + B9 (1. 5^G9 / D9 + 1.5^G9)

L9 = L9 (input)

A9 = M1 - L9 / MO - L9

B9 = M9 - M1

G9 = 1 Log M9 - M0 X M5 - M1

log 1.5 M0 - M1 M9 - M5

D9 = M9 - M0

M0-M1

1. Set RECALCULATION to MANUAL

2. Branch to "INMAIN" to collect model parameters

3. Perform necessary spreadsheet housekeeping preparatory to actual data collection and analysis.

4. Check if number of periods required is met.

5. Call subroutine T4 to fill data ranges where required.

6. Branch to subroutines to collect data

If B21 = 1 Call subrout. R4 Collect Adv. data by period

If B22 = 1 Call subrout. S4 Collect Sales data by period

If B23 = 1 Call subrout. U4 Collect Brand Share data

If B24 = 1 Call subrout. V4 Collect Maint. Adv. data

If B25 = 1 Call subrout.W4 Collect Media Efficiency data

If B26 = 1 Call subrout. X4 Collect Copy Effect by period

If B27 = 1 Call subrout. Y4 Collect Brand Adv. Rate data

7. If B27 = 1 Call subrout. AB4 to copy ranges into initial setup for analysis.

8. Complete analysis, checking if the proper number of periods is completed. Final cleanup of spreadsheet is completed once the required number of periods to be run is reached.

9. Once analysis is completed, ranges are copied from the computational area to the findings section, and the slope is converted from a formula to a value.

10. The main menu is recalled.