Marketing data driven techniques

RFM Analysis and Optimizing Direct Mail Campaigns 469 customer ranks on each attribute. Thus if you rank customers so that the largest value of an attribute gets a 256, then anyone with a rank of 193–256 gets a 4 rating; a rank of 129–192 gets a 3 rating; 65–128 gets a 2 rating; and a rank of 1–64 gets a 1 rating. Assume that each customer has received 80 mailings and a mailing costs $0.50. c. In E8:E71 use conditional formatting to highlight in yellow each profit- able RFM combination. 2. For the file RFMtop10%.xls develop a strategy to maximize revenue generated by mailing to 20 percent of the customers.



31 Using the SCAN*PRO Model and Its Variants Retailers have many available strategies that can be used to increase profits, including price adjustments, product placement on display, advertising, and so on. The difficulty in determining the profit-maximizing mix of marketing strategies is that factors such as seasonality and just plain random variation can make it dif- ficult to isolate how changes in the marketing mix affect unit sales. In this chapter, you learn how to use the GRG (Generalized Reduced Gradient) multistart Solver Engine to develop forecasting models estimate variants of the SCAN*PRO model that tell the retailer how each portion of the marketing mix affects sales. Once firms understand how all these factors influence sales, they can more efficiently allocate resources to the factors that they control. Firms can also understand how external factors that they cannot control (such as competitor’s price, seasonality, competitor’s product on display, and so on) hinder the firm’s effectiveness. Introducing the SCAN*PRO Model Suppose you want to predict weekly sales of Snickers (the world’s best-selling candy bar) at your local supermarket. Factors that might influence sales of Snickers include: ■ Price charged for Snickers bars ■ Prices charged for competitors (Three Musketeers, Hershey’s Chocolate, and so on) ■ Was the Snickers bar on display? ■ Was there a national ad campaign for Snickers? ■ Was there an ad for Snickers in the local Sunday paper? ■ Seasonality: Perhaps Snickers sell better in the winter than the summer.

472 Part VIII: Retailing Untangling how these factors affect unit sales of Snickers is difficult. In his paper “A Model to Improve the Baseline Estimation of Retail Sales,” (1988, see http://centrum.pucp.edu.pe/adjunto/upload/publicacion/archivo/1amodelto improvetheestimationofbaselineretailsales.pdf) Dirk Wittink et al. developed the widely used SCAN*PRO model to isolate the effect of these (and other factors) on sales of retail goods. The SCAN*PRO model and its variants (see Dirk Wittink et al. Building Models for Marketing Decisions, Kluwer Publishing, 2000) have been widely used by A.C. Nielsen and other organizations to analyze retail sales. Modeling Sales of Snickers Bars The SCAN*PRO model is often used to model the impact of various portions of the marketing mix. To predict sales you can model the effect of each part of the mar- keting mix as described in this section’s example and create a final prediction for sales. You do this by multiplying together the terms for each part of the marketing mix and throwing in a constant to correctly scale your final prediction. To create this model you can use the data from the Snickers.xlsx file that shows the weekly sales of Snickers bars at a local supermarket as well as the Snickers price, the price of the major competitor, and whether Snickers is on display. A subset of the data is shown in Figure 31-1. For example, in Week 1, 986 Snickers bars were sold; the price of Snickers was $1.04; the price of the main competitor was $0.81; and Snickers was on display (1 = Snickers on display, 0 = Snickers not on display). To simplify matters assume that Snickers sales do not exhibit seasonality. As you will see later in the chapter, the assumption that seasonality is not present can easily be relaxed. Figure 31-1: Snickers sales data

Using the SCAN*PRO Model and Its Variants 473 To create a model to predict weekly sales, complete the following steps: 1. Raise the price to an unknown power. (Call this power OWNELAS.) This creates a term of the form (Our Price)OWNELAS. The value of OWNELAS can estimate the price elasticity. You would expect OWNELAS to be negative. For example, if OWNELAS = −3, you can estimate that for any price you charge, a 1 percent price increase reduces demand 3 percent. 2. Raise the competitor’s price to an unknown power (COMPELAS). This creates a term of the form (Comp Price)COMPELAS. The value of COMPELAS estimates a cross-elasticity of demand. You would expect COMPELAS to be positive and smaller in magnitude than OWNELAS. For example, if COMPELAS = 0.4, then a 1 percent increase in the competitor’s price increases demand for Snickers (for any set of prices) by 0.4 percent. 3. Model the effect of a display by a term that raises an unknown parameter (Call it DISPLAYEFFECT.) to the power DISPLAY# (which is 1 if there is a display and 0 if there is no display). This term is of the form (DISPLAYEFFECT?)DISPLAY#. When there is a display, this term equals DISPLAYEFFECT, and when there is no display, this term equals 1. Therefore, a value of, say, DISPLAYEFFECT=1.2 indicates that after adjusting for prices, a display increases weekly sales by 20 percent. NOTE In Chapter 34, “Measuring the Effectiveness of Advertising,” you learn how to model the effect of advertising on sales. Putting it all together, the final prediction for weekly sales is shown in Equation 1: (1) Constant * (Our Price)OWNELAS * (Comp Price)COMPELAS * (DISPLAYEFFECT?)DISPLAY# You can now use the GRG multistart Solver Engine to determine values of CONSTANT, OWNELAS, COMPELAS, and DISPLAYEFFECT that minimize the sum of the squared weekly prediction errors. Proceed as follows: 1. Copy the formula = c o n s t a n t * ( D 4 ^ o w n e l a s ) * ( E 4 ^ c o m p e l a s ) * (displayeffect^F4) from H4 to H5:H45 to use Equation 1 to create a forecast for each week’s demand. 2. Copy the formula =(G4-H4)^2 from I4 to I5:I45 to compute the squared error for each week’s forecast. 3. In cell J1 compute the sum of the weekly squared errors with the formula =SUM(I4:I45).

474 Part VIII: Retailing 4. In cell J2 compute the R-squared value between your predictions and actual sales with the formula =RSQ(G4:G45,H4:H45). 5. Using the Solver window shown in Figure 31-2, find the parameter estimates that minimize the sum of the squared forecast errors. Check Use Automatic Scaling from Options. This improves the performance of the Solver on sales forecasting models. Figure 31-2: Snickers sales Solver window The GRG Multistart Engine requires bounds on the changing cells. Choosing “tight” bounds improves the performance of the Solver. You can assume the display effect would be between 1 and 2, the competitor’s price elasticity is assumed to be between 0 and 2, the Snicker’s price elasticity is assumed to be between 0 and –10, and the constant is assumed to be between 0 and 5,000. If the Solver finds a value

Using the SCAN*PRO Model and Its Variants 475 for a changing cell near its bound, the bound should be relaxed because Solver tells you that violating the bound can probably improve your target cell. Solver finds that the following parameters make Equation 1 a best fit for your sales data: ■ The display effect = 1.198. This implied that after adjusting for prices, a Snickers display increases weekly sales by 19.8 percent. ■ The price elasticity for Snickers is −3.19, so a 1 percent increase in the price of Snickers can reduce Snicker’s sales by 3.19 percent. ■ The cross-price elasticity for Snickers is 0.40, so a 1 percent increase in the competitor’s price can raise the demand for Snickers by 0.40 percent. Substituting your parameter values into Equation 1, you can find your prediction for weekly sales is given by: 893.77 * (Snickers Price)–3.19 * (Comp Price)0.40 * (1.198)Display? From cell J2 you find that your model explains 92 percent of the variation in weekly sales. Forecasting Software Sales Intelligent modification of the SCAN*PRO approach can enable you to build insight- ful and accurate sales forecasting models that include factors other than price and product displays. To illustrate the idea, suppose you are given quarterly software sales that depend on quarterly PC shipments. Sales are seasonal and increase after a launch and drop off prior to a launch. How can you build a model to forecast sales? The work for this example is in the file softwaresales.xlsx (see Figure 31-3). Data is given for 48 quarters of software sales. For example, 700,000 units were sold in Quarter 11. During this quarter there was a software launch, the quarter was the third quarter of the year, and 4.8 million PCs were shipped. The equation to forecast sales involves the following parameters: ■ A seasonal index for each quarter. These seasonal indexes should average to 1. A seasonal index of, say, 1.3 for Quarter 4 means that after adjusting for other factors, sales in the fourth quarter average 30 percent more than an average quarter ■ Factors (similar to the effect of the display in the Snickers example) that measure the bump up in sales the quarter of a launch (LAUNCH1) and the quarter after the launch (LAUNCH2)

476 Part VIII: Retailing ■ A factor (LAUNCH-1) that measures the decline in sales the quarter before a launch ■ Because it seems reasonable to assume that an increase in PC shipments will increase sales of your product, you can include a term of the form BASE*PCSALES in your forecast. Here (BASE) is a changing cell that scales your forecast to minimize MAPE. Figure 31-3: Software sales data NOTE Recall from Chapter 14, “Winter’s Method,” that MAPE is the average of absolute percentage errors. Putting it all together, your model for predicting quarterly sales (in millions) is given by Equation 2. (2) BASE * PCSALES * (SEASONAL INDEX) * (LAUNCH EFFECT) In Equation 2, BASE is analogous to the constant term in Equation 1. When building your sales forecasting model, you can assume that in lieu of mini- mizing the sum of squared errors you can minimize the average absolute percentage error (MAPE). Minimizing SSE emphasizes avoiding large outliers, and minimizing MAPE emphasizes minimizing the magnitude of the typical prediction error. Also, many practitioners prefer MAPE over SSE as a measure of forecast accuracy. This is probably because MAPE is measured as a percentage of the dependent variable,

Using the SCAN*PRO Model and Its Variants 477 whereas it is difficult to interpret the units of SSE (in this case units2). Before using the GRG multistart Solver Engine, proceed as follows: 1. Copy the formula =base*E11*VLOOKUP(G11,lookup,2)*VLOOKUP(I11,launch, 3,FALSE) from J11 to J12:J58 to compute your forecasts for all 48 quarters of data. 2. Copy the formula =(F11-J11)/F11 from K11 to K12:K58 to compute the percentage error for each quarter. For example, in Quarter 1 actual software sales were 3.0 percent higher than your prediction. 3. Copy the formula =ABS(K11) from L11 to L12:L58 to compute each week’s absolute percentage error. 4. Compute the MAPE in cell L8 with the formula =AVERAGE(L11:L58). The Solver window shown in Figure 31-4 finds the values of base, seasonal indexes, and launch effects that yield the best forecasts. The Target cell minimizes MAPE (cell L8) by changing the following parameters: ■ Seasonal indices in cells D2:D5 (which must average to 1) ■ A Base (in cell G2) which in effect scales PC Sales into sales of our software ■ LAUNCH-1 (in cell G5) which represents the drop in sales in the quarter before a launch ■ LAUNCH1 (in cell G3) which represents the increase in sales during a launch quarter ■ LAUNCH2 (in cell G4) which represents the increases in sales the quarter after a launch ■ The lower bound on each changing cell is 0 and the upper bound 2. The upper bound of 2 was used because it seems unlikely that sales would more than double in a quarter due to a launch and it seems unlikely that one PC sale would lead to more than two sales of the software. Model Interpretation You can combine the Solver solution with Equation 2 to yield the following market- ing insights: ■ Quarter 4 has the highest sales with sales 29 percent better than average, and Quarter 1 has the lowest sales (28 percent below average). ■ All other things being equal (ceteris paribus) the quarter of a launch, sales increase by 15 percent. ■ The quarter after a launch, sales increase (ceteris paribus) by 10 percent. ■ The quarter before a launch, sales decrease (ceteris paribus) by 22 percent.

478 Part VIII: Retailing ■ During an average quarter, an increase in PC shipments of 100 PCs leads to 9.8 more units sold. ■ In cell J8 you can compute the standard deviation of percentage errors with the formula =STDEV(K11:K58). The standard deviation of 5 percent tells you that approximately 68 percent of your forecasts should be accurate within 5 percent, and approximately 95 percent of your forecasts should be accurate within 10 percent. ■ Any quarter in which your forecast is off by 10 percent or more is an outlier. As shown in Figure 31-5, Quarter 44 is an outlier because your forecast was off by 12 percent. Figure 31-4: Solver window for software sales Predicting Future Sales Assuming 6 million PC shipments in Quarter 50, suppose you are asked to forecast Quarter 50 sales and there is no launch planned in quarters 48–51. To predict

Using the SCAN*PRO Model and Its Variants 479 Quarter 50 sales, you simply copy the forecast formula in cell J58 to J59. Your fore- cast for Quarter 50 sales is .495153 million or 495,153 units. Figure 31-5: Software outlier and Quarter 40 forecast Checking for Autocorrelation Recall from Chapter 10, “Using Multiple Regression to Forecast Sales,” that a good forecasting method should see the sign of forecast change approximately one half the time. Copying the formula =IF(K12*K11<0,1,0) from M12 to M13:M58 yields a 1 each time there is a sign change in the errors. There were 20 sign changes in the errors. The cutoff for “too few” sign changes in errors is given by 48-1 - √ 48-1 = 16.6. 2 Because 20>16.6 the forecasts exhibit random changes in sign, and no correction for autocorrelation is needed. Modeling a Trend in Sales If you felt there was a possible trend in software sales, you could generalize Equation 2 to account for that trend. To model a trend change, change the forecast equation in J11 to Equation 3: (3) Predicted Sales = base * (TREND^D11) * E11 * VLOOKUP(G11,lookup,2) * VLOOKUP(I11,launch,3,FALSE) In Equation 3, TREND is added as a changing cell. A value of TREND = 1.07 means that sales were increasing 7 percent per quarter (after adjusting for all

480 Part VIII: Retailing other variables), while if TREND = 0.91 this would indicate sales were decreasing 9 percent per quarter. Summary In this chapter you learned the following: ■ You can use the GRG multistart Solver Engine to determine how aspects of the marketing mix impact retail sales. ■ The effect of your product’s price can be modeled by a term of the form (Your Price)OWNELAS. The value of OWNELAS can estimate your price elasticity. ■ The effect of a competitor’s price can be modeled by a term of the form (Comp Price)COMPELAS. The value of COMPELAS estimates a cross-elasticity of a demand set of prices by 0.4 percent. ■ The effect of a display can be modeled by a term of the form (DISPLAYEFFECT?)DISPLAY#. ■ Seasonality can be incorporated into forecasts by multiplying the seasonal index by the terms involving the marketing mix. ■ Other factors such as a trend and the effect of a product launch can also be incorporated in forecast models. Exercises 1. The file Cranberries.xlsx includes quarterly sales in pounds of cranberries at a supermarket, price per pound charge, and the average price charged by competition. a. Use this data to determine how seasonality, trend, and price affect quarterly sales. b. Use a Multiplicative Model for seasonality. (The average of seasonal indexes should equal 1.) c. Introduce a trend and set up the model so that you can estimate price elasticity. d. If you charge $5 a pound in Q1 2012 and the competitor charges $5 per pound, predict the Q1 2012 sales. e. Fill in the blank: You are 95 percent sure the Q1 2012 sales are between _____ and ___. Problems 2–4 use the workbook Snickers.xlsx.

Using the SCAN*PRO Model and Its Variants 481 2. If Snickers had more than one major competitor, how would you modify the forecast model? 3. If Snickers knew whether the competitor’s product was on display, how could the model be modified? 4. Suppose you believe that if Snickers had cut its price at any time in the last four weeks, the consumer would become more price-sensitive. How would you incorporate this idea into your forecast? 5. The file POSTITDATA.xlsx contains daily information on sales of Post-it Notes. A sample of this data is shown in Figure 31-6. Figure 31-6: Post-it sales data forecast The following factors influence daily sales: ■ Month of the year ■ Trend ■ Price (Seven different prices were charged.) ■ Whether product is on display (1 = display, 0 = no display) a. Build a model to forecast daily sales. Hint: Look at the values of price that are charged. Why is a model including terms of the form (Price)elasticity inappropriate?

482 Part VIII: Retailing b. Examine your outliers and determine a modification to your model that improves your forecasts. Then describe how price, trend, display, and seasonality affect daily sales. 6. Fill in the blank: In Exercise 5 you would expect 95 percent of your daily forecasts to be accurate within ______. 7. For many products the effect of a price change on sales is actually linked to the change in the product price relative to a reference price, which represents the price customers feel they typically pay for the product. For example, the sales of Cheerios may be based on how the price of a box of Cheerios differs from a reference price of $3.50. How would you modify the SCAN*PRO model to include the idea of a reference price? 8. The neighborhood price effect states that brands priced closer together exhibit a greater cross-elasticity than brands priced farther apart. How would you use the SCAN*PRO model to test this hypothesis? 9. How would you use the SCAN*PRO model to determine which is larger: ■ The effect of a 1 percent price cut for a national brand on sales of a generic brand. ■ The effect of a 1 percent price cut for a generic brand on sales of a national brand. 10. When a company promotes their product with a short-term price cut, their sales will increase for two reasons: customers switch from another brand and the product category exhibits temporary sales growth. How could you use the SCAN*PRO model to decompose these two components of increased product sales?

32 Allocating Retail Space and Sales Resources Often marketing managers must determine the profit-maximizing allocation of scarce resources such as advertising dollars, shelf space in a grocery store, or a drug company’s sales force. The key to optimal allocation of marketing resources is to develop an understanding of how a change in the resources allocated to a prod- uct affects product sales. To achieve this understanding, you must have the ability to model the relationship between the level of the scarce allocated resource and the achieved response. These relationships are typically modeled using nonlinear functions. This chapter begins by identifying the relevant types of marketing efforts and responses, and then discusses the curves that are commonly used to model the relationship of sales to marketing effort. You then see how the marketing analyst can determine the curve that best describes the effort response relationship. Finally, you see an example of how the Solver can be used to determine a profit-maximizing allocation of a drug company’s sales force. Identifying the Sales to Marketing Effort Relationship In many situations the marketing manager must determine how to allocate scarce resources. Here are some examples: ■ Eli Lilly must determine the number of salespeople needed and how many calls to doctors should be made for each drug. For example, would more profit be generated by shifting calls from oncology drugs to endocrine drugs? ■ Time Warner must determine how to allocate an advertising budget between its many magazines. For example, should more money be spent on ads for Sports Illustrated or People?

484 Part VIII: Retailing ■ Target must determine how much shelf space is allocated to each product category. For example, should Target increase the limited space it devotes to jewelry? ■ A supermarket must determine how much space to allocate to each pain reliever. For example, should Extra Strength Tylenol or a generic pain reliever be allocated more space? In each situation you encounter a need to identify the relationship between the amount of resources used and the desired result. Table 35-1 shows the relationships for the first three examples. Table 35-1: Examples of Situations where Marketing Effort Must Be Allocated Situation X = Resource Level Y = Response Eli Lilly Allocated Annual sales of drug AOL Time Warner Sales calls per year pitching New subscriptions to drug magazine $ spent advertising in magazine Target Shelf space allocated to prod- Profit generated by product uct line line These types of relationships between marketing effort and the response to that effort can be modeled visually with three different types of curves: the Power curve, ADBUDG curve, and the Gompertz curve. These are discussed in the following section. Modeling the Marketing Response to Sales Force Effort Three curves are often used to model resource-response relationships: ■ The Power curve: y = axb. Values of a and b that best fit the Power curve to data may be found with the Excel Trend Curve. Assuming 0<b<1, the Power curve exhibits diminishing returns; that is, each additional ad yields fewer extra sales. (b − a)xc ■ The ADBUDG curve: Y = a + (d + xc) . The ADBUDG curve was developed by legendary MIT Professor John Little in his “Models and Managers: The Concept of a Decision Calculus” (Management Science, 1970, pp. B466–B485).

Allocating Retail Space and Sales Resources 485 This curve can be easily fit using the GRG Multistart Engine. In particular this curve has been used to model response to sales effort or advertising. Although the Power curve always exhibits diminishing returns, the ADBUDG curve can exhibit diminishing returns or look like an S-shaped curve (see Chapter 26, “Using S Curves to Forecast Sales of a New Product”). Note that if the ADBUDG curve is S-shaped, then the S shape of the curve implies that for a small amount of marketing effort little sales response is generated, and for an intermediate amount of marketing effort, increasing returns to marketing effort are observed. Finally, beyond a certain point, decreasing returns to marketing effort are observed. ■ The Gompertz curve: y = a * exp(−c * exp(−bx)). This curve is often used to model the change in profit resulting from additional shelf space allocation. Like the ADBUDG curve, the Gompertz curve is an S-shaped curve. Estimation of the Gompertz curve is described in Chapter 26. The following sections detail how data on marketing effort and response to marketing effort can be used to find the curve that best explains the relationship between marketing effort and the response to effort. Fitting the Power Curve Suppose a company feels the number of units of a product sold (in thousands) in a month and the number of ads placed are related, as shown in Figure 32-1 (see Powercurve.xlsx file). Figure 32-1: Fitting a Power curve to ad response data

486 Part VIII: Retailing To fit a Power curve to this data, complete the following steps: 1. Plot the points by selecting the range G6:H13, and from the Insert tab, select Scatter. (Choose the first option: Scatter with only markers.) 2. Right-click any point and select Add Trendline.... The Format Trendline dialog (as shown in Figure 32-2) appears. Figure 32-2: Creating the Power curve 3. Check Power and Create Equation on the chart to obtain the Power curve and equation y = 1.0725x-0.4663 shown in Figure 32-1. Note that this data (and the decreasing slope of the fitted Power curve) indicates that additional ads generate a diminishing response. Fitting the ADBUDG Curve Suppose you want to determine a curve that predicts the sales of a product as a function of the sales effort allocated to the product. Researchers (see Leonard Lodish et al. “Decision Calculus Modeling at Syntex Labs,” Interfaces, 1988, pp. 5–20) have found that the response to a sales force effort can often be well described by the ADBUDG function of the following form. Sales of drug i when x calls are made for drug i = a + (b − a)xc (d + xc)

Allocating Retail Space and Sales Resources 487 Figure 32-3 shows an example of a fitted ADBUDG curve. Figure 32-3: Fitted ADBUDG curve An S curve starts out flat, gets steep, and then again becomes flat. This would be the correct form of the sales as a function of effort relationship if effort needs to exceed some critical value to generate a favorable response. To illustrate how an ADBUDG response curve can be fit to a product, try and determine an ADBUDG curve that shows how unit sales of a drug depend on the number of sales calls made on behalf of the drug. The work for this example is in the syntexgene.xls file (see Figure 32-4). To estimate an ADBUDG curve, use the following five points as inputs: ■ Estimated sales when there is no sales effort assigned to the drug ■ Estimated sales when sales effort assigned to a drug is cut in half ■ Sales at current level of sales-force effort (assumed to equal a base of 100) ■ Estimated sales if the sales-force effort were increased by 50 percent ■ Estimated sales if the sales force “saturated” the market Figure 32-4: ADBUDG curve estimation

488 Part VIII: Retailing Currently 350,000 calls are being made. Syntex Labs estimated that changing sales effort (refer to Figure 32-4) would have the following effects on sales: ■ With no sales force effort, you can estimate sales would drop to 47 percent of their current level. ■ If sales force effort were cut in half, you can estimate sales would drop to 68 percent of its current level. ■ If sales force effort were increased 50 percent, you can estimate sales would increase by 26 percent. ■ If you increased sales force effort 10-fold (a saturation level), you can estimate sales would increase by 52 percent. To estimate the parameters a, b, c, and d that define the ADBUDG curve, proceed as follows: 1. Enter trial values of a, b, c, and d in A13:D13, and name these cells a, b, c, and d, respectively. 2. In cells C5:C9 compute the prediction from the ADBUDG curve by copying the formula =a+((b-a)*A5^c_)/(d+A5^c_) from C5 to C6:C9. 3. In cells D5:D9 compute the squared error for each prediction by copying the formula =(B5-C5)^2 from D5 to D6:D9. 4. In cell D10 compute the sum of the squared errors with the formula =SUM(D5:D9). NOTE With different starting solutions the GRG Solver (without multistart) will find different final solutions. For example, if you start Solver with a = 10, b = 50, c = 5, and d = 1,000, the GRG Solver can obtain only an SSE of 3,875. With a starting solution of a = 1, b = 2, c = 3, and d = 4, you will obtain an error message! Selecting the GRG Multistart Engine ensures that your starting values of a, b, c, and d will not affect the answer found by Solver. Because a equals your predicted sales for no sales effort, a should be close to 47. (You can constrain a to be between 0 and 50.) Because b is the predicted sales with infinite sales effort, b should be close to 150. (You can constrain b to be between 0 and 200.) There are no obvious values for c and d. A large value of c, however, can cause your function to involve large numbers that may crash the GRG Multistart Engine, so constrain c to be between 0 and 5. Finally, there are no obvious limits on d, so constrain d to be between 0 and 1,000,000. The GRG multistart Solver Engine window is shown in Figure 32-5.

Allocating Retail Space and Sales Resources 489 Figure 32-5: Solver window for ADBUDG curve estimation 5. Minimize the sum of squared errors (cell D10) by changing values of a, b, c, and d (cells A13:D13). Bind a, b, c, and d as discussed previously. The GRG Multistart Engine found the solution shown in Figure 32-4. Observe that none of the “predictions” are off by more than 2 percent. After fitting this curve to each drug, the resulting response curves can be used as an input to a Solver model (see the next section for an example) that can determine a profit maximizing allocation of the sales force effort. Optimizing Allocation of Sales Effort Now that you have learned to use different types of curves to model the association between the level of marketing effort and the response, you can use this information to optimize the allocation of marketing resources. To illustrate this process, suppose

490 Part VIII: Retailing you know that a Power curve can model the response to sales effort for four drugs. How would you allocate sales effort between the drugs? In particular, suppose that units of each drug sold during a year (in thousands) are as follows: ■ Drug 1 sales = 50(calls).5 ■ Drug 2 sales = 10(calls).75 ■ Drug 3 sales = 15(calls).6 ■ Drug 4 sales = 20(calls).3 Calls are also measured in 1,000s. For example, if 4,000 calls are made for Drug 1, then Drug 1 sales = 50(4).5 = 100,000 units. Rows 4 and 5 of the spreadsheet summarize the response curves for each drug. In the salesallocation.xls file (see Figure 32-6), you can determine the profit, maximizing the number of sales calls made for each drug. Assume that each call costs $200 and the unit profit contribution for each drug is given in row 2. Figure 32-6: Sales-force allocation model Set up the spreadsheet as follows: 1. Enter a trial number of calls for each drug in B6:E6. 2. Compute the unit sales for each drug by copying the formula =1000*B4*(B6^B5) from B7 to C7:E7. 3. In cell B10 compute the profit (excluding sales call costs) with the formula =SUMPRODUCT(B7:E7,B2:E2). 4. In cell B11 compute the annual sales call cost with the formula =1000*C1*SUM(B6:E6). 5. In cell B12 compute the net annual profit with formula =B10-B11.

Allocating Retail Space and Sales Resources 491 The Solver window shown in Figure 32-7 enables you to compute the profit, maximizing sales call allocation. Figure 32-7: Solver window for sales call allocation NOTE Each number of calls is constrained to be >=.01 instead of 0. This pre- vents the Solver from trying a negative number of sales calls. If Solver tried a negative value for the calls made on behalf of a drug, then the unit sales would be undefined and would result in an error message. A maximum profit of $731,033 is obtained with 1,562 calls for Drug 1, 100 calls for Drug 2, 769 calls for Drug 3, and 663 calls for Drug 4. As shown in Exercise 2, the Solver simply allocates the calls to each drug so that the change in the marginal profit generated by changing a drug’s number of calls by a small amount (say .01) equals the cost of .01 calls. In short, the Solver finds the optimal sales force alloca- tion by invoking the old economic adage that marginal revenue = marginal cost.

492 Part VIII: Retailing Lodish et al. (Interfaces, 1988) used the methodology of this chapter to analyze sales force allocation at Syntex Laboratories. Following their recommendations, Syntex Laboratories hired 200 more salespeople, and in just one year saw a 100- percent return on investment! Because Syntex first used these methods, Lodish et al. reported that at least 10 other pharmaceutical companies have successfully determined their sales force allocation by using the models described in this chapter. Using the Gompertz Curve to Allocate Supermarket Shelf Space In his paper, “Shelf Management and Space Elasticity,” Xavier Dreze et al. (see http://research.chicagobooth.edu/marketing/databases/dominicks/docs/1994- ShelfManagement.pdf) worked with Dominick’s Finer Foods, a Chicago supermarket chain, to determine a profit maximizing shelf space allocation and product layout. They first conducted a cluster analysis using customer demographic information on 60 stores. The authors decided there were two relevant clusters: urban stores and suburban stores, so they built a model for each cluster to predict sales of each brand as a function of the space allocated to the brand, brand price, and brand location (eye level, above eye level, below eye level). The relationship of a brand’s sales to allocated space was modeled by a Gompertz curve, while the effect of brand price and brand location (at eye level, above eye level, or below eye level) was modeled via the SCAN*PRO model (see Exercise 6). The authors then used Solver for a particular product category to allocate available space between brands and determine the profit-maximizing space allocation and loca- tion for each brand. The authors reported a 5 to 6 percent increase in store profits. Summary In this chapter you learned the following: ■ The key to optimizing the allocation of marketing effort is modeling the func- tional relationship between the amount of effort and unit sales. ■ The Power curve, ADBUDG curve, and Gompertz curve are often used to model the relationship between effort and unit sales. ■ When the functional relationship between effort and unit sales is determined, a Solver model, with profit as the target cell and changing cells being the amount of effort assigned to each product, can be used to determine the allocation of marketing effort.

Allocating Retail Space and Sales Resources 493 Exercises 1. You want to allocate 2,000 square feet of space in CVS between seasonal and nonseasonal items. You must allocate at least 400 square feet to each type of item. Estimated profit as a function of floor space is given here. Space Seasonal Profit Nonseasonal Profit 500 357,770.9 1,056,381.404 750 438,178 1,217,454.357 1,000 505,964.4 1,346,422.145 1,250 565,685.4 1,455,793.415 1,500 619,677.3 1,551,719.389 How much space should be allocated to each type of item? 2. Show in the salesallocation.xlsx file that Solver’s optimal resource alloca- tion has the property that the marginal profit generated by a small change in a drug’s calls equals the sales call cost associated with the change in calls. 3. How could the ADBUDG curve be used to determine the optimal allocation of advertising dollars for GM’s car models? 4. In 2009 Target began to devote a much larger portion of its stores to groceries. Why would the methods of this chapter understate the benefit Target would obtain by allocating more space to groceries? 5. Paris Lohan is a marketing analyst for Time Warner. She has fit an ADBUDG curve for each magazine that describes how the number of new subscriptions changes when the money spent on advertising the magazine changes. Paris now wants to determine a profit maximizing allocation of the ad budget for each magazine. In Paris’ target cell she has multiplied the number of new subscriptions by the annual profit per subscriber to the magazine. Can you find an error in Paris’ logic? 6. In Excel, set up a formula that combines the Gompertz curve and SCAN*PRO model to estimate sales of a brand as a function of brand price, brand location (eye level, above eye level, below eye level), and price of competitor’s brand.



33 Forecasting Sales from Few Data Points There are many situations in which a business wants to estimate total sales of a product based on sales during the early portion of the product life cycle. This need brings forth the question, “How many months of sales are needed to make an accurate forecast of product sales during the product’s lifetime?” Chapter 26, “Using S Curves to Forecast Sales of a New Product,” and Chapter 27, “The Bass Diffusion Model,” show how S curves and the Bass model can be used to predict total sales early in the product life cycle. Both the S curve and Bass model require at least five data points, however, to obtain reasonable estimates of future sales. This chapter describes a simpler method that can estimate total product sales from as few as two data points. Predicting Movie Revenues When a new movie comes out, how many weeks of revenue are needed to predict total movie revenue? One might guess 5 or 6, however, it is actually possible to predict a movie’s total revenues from as few as 2 weeks of revenues. The 2weeks MAD worksheet of the finalresultmovie.xls file (see Figure 33-1) contains total revenues for 76 movies and revenues for each of the first 3 weeks of the movie’s release. Using this data, the goal is to develop a simple model that can accurately predict total revenues from either the first 2 or 3 weeks of revenues. In many industries (such as video games) analysts begin with a simple rule such as the following equation: (1) (Estimated Total Revenue) = (Some multiple of first two weeks of revenue)

496 Part VIII: Retailing Figure 33-1: Movie revenue data For example, you may try to estimate total revenue for a movie by simply doubling the revenues earned by the movie during its first 2 weeks. To see the weakness of this type of rule though, consider the revenues (in millions of dollars) for the two movies shown in Table 33-1. Table 33-1: Weakness of Equation 1 Movie Week 1 Revenue Week 2 Revenue 40 1 80 60 2 60 Because each movie made $120 million during the first 2 weeks, any prediction for total revenue generated from Equation 1 will be the same for each movie. Looking at the data tells you, however, that revenue for Movie 1 is dropping fast, but revenue for Movie 2 is holding up well. This would lead you to believe future revenue for Movie 2 will exceed Movie 1. Predicting total movie revenue with Equation 2 will create higher forecasts (after adjusting for total revenue during the first 2 weeks) for Movie 2, as wanted. (2) Predicted Total Movie Revenue = a * (1st 2 weeks of revenue) * (Legs)alpha In Equation 2, Legs = Week 2 Revenue / Week 1 Revenue. If Legs = 1 (which is the case for Movie 2 in Table 33-1), for example, the movie has excellent staying power

Forecasting Sales from Few Data Points 497 because Week 2 revenue = Week 1 revenue. The average value of Legs for the movies in this data set was 0.63, indicating that Week 2 revenues average 37 percent lower than Week 1 revenues. If you hold a movie’s first two weeks of revenue constant, then the forecasted total revenue from Equation 2 is an increasing function of Legs, which means a movie’s forecasted revenue is an increasing function of the movie’s staying power. NOTE The term “legs” is being used as a synonym for the movie’s staying power at the box office. The worksheet 2weeks MAD uses the GRG Multistart Solver engine to determine the values of a and alpha that minimize average absolute error. Note that some mov- ies (such as Lord of the Rings) make a lot of money relative to other movies (such as Orange County). If you minimize MAPE (Mean Absolute Percent Error), then movies that have low revenue can exert an inordinate influence on the target cell. For example, if you predicted $6 million revenue for Orange County, you were fairly close in absolute terms to the actual revenue (near $4 million) but your percentage error is nearly 50 percent. In general, choosing SSE (Sum of Squared Error) as a goodness of fit criteria for a model causes the outliers to have a large effect on the model’s estimated parameters. In contrast, if MAD (Mean Absolute Deviation) or MAPE is used as a goodness of fit criteria, then the influence of outliers on the model’s estimated parameters is less and Solver concentrates on making the model fit “typical” values of the dependent variable. To find the values of a and alpha that minimize MAD, proceed as follows: 1. Copy the formula =a*SUM(D4:E4)*(H4^alpha) from J4 to J5:J79 to generate the forecast for each movie’s total revenue implied by Equation 2. 2. Copy the formula =ABS(G4-J4) from K4 to K5:K79 to compute the absolute error for each movie’s prediction. 3. In cell K2 compute the average absolute error (the target cell) with the for- mula =AVERAGE(K4:K79). 4. Using the Solver window in Figure 33-2, find values of alpha and a that minimize MAD.

498 Part VIII: Retailing Figure 33-2: Solver window for two-week movie revenue data The resulting prediction equation is as follows: Predicted Total Movie Revenue = 1.79 * (First 2 week Total) ^ 0.30 The average error was $5.7 million. The average revenue of the listed movies is $50 million, so using 2 weeks of revenues you conclude that the forecasts are off by approximately 11 percent. Modifying the Model to Improve Forecast Accuracy There are a few additional measures you can take to ensure for the most accurate predictions possible. This section covers some of the most common ways you can increase the accuracy of the relatively simple model defined by Equation 2.

Forecasting Sales from Few Data Points 499 Finding Outliers Recall a forecasted observation is an outlier if the absolute value of the forecast error exceeds 2 * (Standard deviation of the forecast errors). Copying the formula =G4- J4 from L4 to L5:L79 computes the forecast error for each observation. In cell M2 you can compute the standard deviation ($9.7 million) of the forecast errors with the formula =STDEV(L4:L79). Column K highlights the movie outliers, which are all movies whose forecast errors exceed 2 * 9.68 = $19.36 million. The outliers are The Lord of the Rings, Star Wars Episode II, Ocean’s Eleven, Spider-Man and Monsters, Inc. If you could figure out a common thread that explained the model’s lack of accuracy in predicting the revenue from these outliers, incorporating this common thread into the forecast equation would greatly improve the model’s forecasting accuracy. Minimizing Squared Errors The 2 weeks sq error worksheet of the finalresultmovie.xls file estimates the model using the criteria of minimizing squared errors. And a = 1.84 and alpha = 0.29, which are virtually identical to the previous estimates of a and alpha. If outli- ers were having a large effect on the model’s estimated parameters, then SSE and MAD would yield substantially different parameter estimates. The similarity of the parameter estimates for the MAD and SSE criteria shows that outliers are not having an outlandish effect on the estimates of the model’s parameters. Ignoring Staying Power To demonstrate the improvement in forecast accuracy gained by including LEGS in the forecast model, set alpha = 0 and have Solver find the value of a that minimizes MAD (see the Simple model worksheet). The best forecast is obtained by multiplying the total revenue for the first 2 weeks by 1.53. The MAD = $6.57 million, which is 20 percent larger than the MAD obtained by including staying power in the forecast model. This calculation demonstrates the size of the benefit gained from including LEGS in the model. Using 3 Weeks of Revenue to Forecast Movie Revenues The preceding methods are great ways to help minimize error when you are lim- ited to two weeks of data from which to forecast. Using three weeks of revenues, however, will clearly result in a more accurate forecast. The question is whether

500 Part VIII: Retailing the additional week of data results in a substantial enough improvement in forecast accuracy. To see if it does, in the Use 3 weeks worksheet you will develop a model that forecasts total movie revenue from the first three weeks of revenue. The key issue here is how to define a movie’s “staying power.” Given the first 3 weeks of a movie’s revenues, you can define the following equation: (3) Legs = wt * (Week 2 Legs) + (1−wt) * (Week 3 Legs) In Equation 3 Week 2 Legs = Week 2 Revenue / Week 1 Revenue, Week 3 Legs = Week 3 revenue / Week 2 revenue, and wt is a weight that defines the weighted average of Week 2 and Week 3 Legs that minimize MAD. The rationale behind Equation 3 is that (Week 2 Revenue / Week 1 Revenue) and (Week 3 Revenue / Week 2 Revenue) are two different estimates of a movie’s staying power, so when averaged, you get a single estimate of a movie’s staying power. The prediction equation is the following: (4) Predicted Total Revenue = a * (1st three weeks revenue) * Legsalpha. To find the values of a, alpha, and wt that minimize MAD, proceed as follows: 1. Copy the formula =wt*(E4/D4)+(1-wt)*(F4/E4) from H4 to H5:H79 to com- pute the weighted estimate of Legs for each movie. 2. Copy the formula =a*I4*(H4^alpha) from J4 to J5:J79 to compute the forecast for each movie. The absolute error and MAD formulas are as before, and the new Solver window (reflecting the fact that wt is a changing cell) is shown in Figure 33-3. 3. Use the following equation to predict Total Revenue: Total Revenue = 1.63 * (0.54 * Week 2 Legs + 0.46 * Week 3 Legs) ^ 0.49 The MAD has been reduced to $3.7 million. Because most movies make most of their revenue within three weeks, it appears that including the third week in the model does not result in a substantial enough improvement in forecast accuracy to make it worth your while. Having the ability to quickly predict future sales of a product is important in industries where demand for a new product is highly uncertain. For example, for stores that specialize in selling clothing to teens (such as PacSun and Hot Topic), ability to quickly predict future sales is vital. This is because before a new apparel item is introduced, there is great uncertainty about future product sales so a retailer can lose a lot of money if they order too much or too little. If a few weeks of data narrows the “cone of uncertainty” on future sales, then a retailer can begin with a small order and use information from a few data points to derive a reorder quantity that reduces the costs associated with overstocking or understocking the product.

Forecasting Sales from Few Data Points 501 Figure 33-3: Solver window for three-week movie revenue model data Summary In this chapter you learned the following: ■ To forecast total sales of a product from the first n periods of sales, take a * (first n periods of sales) * (Legsalpha) where Solver is used to find the values of a, alpha, and (if necessary) the parameters needed to compute Legs. ■ The Target Cell can be to minimize MAD or SSE. Exercises 1. The Newmoviedata.xlsx file contains weekly and total revenues for several movies. Develop a model to predict total movie revenues from 2 weeks of revenues. 2. For your model in Exercise 1, find all forecast outliers.

502 Part VIII: Retailing 3. Think of some additional factors that could be added to the movie forecasting model that might improve forecast accuracy. 4. Suppose Microsoft asks you to determine how many weeks of sales for an Xbox game are needed to give a satisfactory forecast for total units sold. How would you proceed? 5. Why is predicting total sales of an Xbox game from several weeks of sales a more difficult problem than predicting total movie revenue from several weeks of movie revenues?

IX Advertising Chapter 34: Measuring the Effectiveness of Advertising Chapter 35: Media Selection Models Chapter 36: Pay Per Click (PPC) Online Advertising



34 Measuring the Effectiveness of Advertising Companies have trouble measuring the effectiveness of advertising. This is due primarily to the lag between exposure to an ad and the consumer response to an ad. Simply put: past ads can (and usually do) affect present and future sales. For example, the author taught army analysts at Fort Knox who analyzed the dispo- sition of the Army recruiting budget. They found that many visits to Army recruiting offices could be traced to an ad that was shown on TV up to six months before. John Wanamaker, a 19th-century Philadelphia department store merchant, sum- marized how difficult it is to measure the benefits of advertising when he said, “Half the money I spend on advertising is wasted; the trouble is I don’t know which half.” This chapter develops several models that you can use to determine if a firm’s adver- tising is worthwhile. You learn how to incorporate the lagged effect of advertising into your forecast of product sales. You also learn that the optimal allocation of the ad budget over time may involve either pulsing (quick bursts of intensive advertis- ing followed by a period of no ads) or continuous spending (advertising all the time at a fairly constant rate). The Adstock Model Chapter 31, “Using the SCAN*PRO Model and Its Variants,” developed models that you can use to determine how price and display affect sales. These models are not too difficult to set up because you can assume that past prices and displays have no effect on current sales. However, this is not the case when it comes to advertising. To account for the lag between exposure to an ad and the consumer response to an ad, Simon Broadbent developed the Adstock Model, detailed in his article “One Way TV Advertisements Work” (Journal of Marketing Research, 1979.) This model essentially provides a simple yet powerful way to model the fact that ads do affect present and future sales. The Adstock Model also has the virtue of being easily combined with the versions of the SCAN*PRO model discussed in Chapter 31.

506 Part IX: Advertising The key idea behind the Adstock Model is the assumption that each given sales period or quarter you retain a fraction (lambda) of your previous stock of advertising. For example, if lambda = 0.8, then an ad from one period ago has 80 percent of the effect of an ad during the current period; an ad two periods ago has (0.80)2 = 51.2 percent as much effect as an ad during the current period; and so on. Lambda will therefore be a changing cell that is determined with Solver. In a sense the Adstock Model assumes that the effect of advertising “depreciates” or wears out in a manner similar to the way a machine wears out. To try out an analysis using the Adstock Model, suppose you want to model sales of a seasonal price-sensitive product for which sales are trending upward. You can see in the Adstock.xlsx file that for each quarter, you are given the product price, amount of advertising, and units sold (in thousands). For example, during the first quarter of data (which was also the first quarter of the year), the price was $44.00, 44 ads were placed, and 2,639 units were sold. Coming into the first period, you do not know the current Adstock level, so you can make the period 1 level of Adstock a changing cell (call it INITIAL ADSTOCK). Then each period’s Adstock value can be computed using Equations 1 and 2: (1) Quarter 1 ADSTOCK = LAMBDA * INITIALADSTOCK + QUARTER 1 ADS (2) Quarter T ADSTOCK = LAMBDA * QUARTER T-1 ADSTOCK + QUARTER T ADS Of course, you need a mechanism by which the Adstock level influences sales. You can assume that the Adstock level in a quarter has a linear effect on sales through a parameter ADEFFECT. Use the following model to forecast each quarter’s sales: (3) Predicted Sales = (CONST * (TRENDQuarter# + ADEFFECT * ADSTOCK) * (PRICE)-elasticity * (Seasonal Index) In Equation 3 CONST * (TRENDQuarter# + ADEFFECT * ADSTOCK) “locates” a base level for sales in the absence of seasonality and advertising and adjusts this base level based on the current Adstock level. Multiplying this base level by (PRICE)-elasticity * (Seasonal Index) adjusts the base level based on the current price and quarter of the year. In your analysis, find the parameter values that minimize the MAPE associated with Equation 3. You could also, if desired, find the parameters that minimize SSE. The following steps describe how to use Solver to minimize MAPE. 1. In cell F6 compute quarter 1’s Adstock level with the formula =E6+intialadstock*lambda using Equation 1.

Measuring the Effectiveness of Advertising 507 2. Copy the formula =E7+lambda*F6 from F7 to F8:F29 to use Equation 2 to compute the Adstock level for the remaining quarters. 3. Copy the formula =(const*(trend)^D6+adeffect*F6)*VLOOKUP(C6,season,2) *(G6)^(-elasticity) from H6 to H7:H29 to use Equation 3 to compute a forecast for each quarter’s sales. 4. Copy the formula =ABS(I6-H6)/I6 from J6 to J7:J29 to compute each quarter’s absolute percentage error. 5. In cell I4 compute the MAPE with the formula =AVERAGE(J6:J29). 6. Use the Solver window in Figure 32-1 with the GRG (Generalized Reduced Gradient) Multistart engine to find the parameter values that minimize the MAPE associated with Equation 3. Most of the upper bounds on the changing cells are “intelligent guesses.” Of course, if the Solver set a changing cell value near its upper bound, you need to relax the bound. The constraint $M$11 = 1 ensures that the seasonal indices average to 1. The solution found by Solver is shown in Figure 34-2. Figure 34-1: Solver window for Adstock Model

508 Part IX: Advertising Figure 34-2: Adstock Model The forecasts are off by an average of 2.2 percent. The optimal values of the model parameters may be interpreted as follows: ■ From cell M3 you find that sales are increasing at a rate of 9.7 percent per quarter. ■ From cell M4 you find that during each quarter 17 percent (1 − 0.83) of advertising effectiveness is lost. ■ From cell M2 you find that for any price a 1 percent increase in price reduces demand by 1.49 percent. ■ From the values of the seasonal indices in M6:M9 you find that first quarter sales are 20 percent below sales during an average quarter; second quar- ter sales are 30 percent below average; third quarter sales are 18 percent below average; and fourth quarter sales are 67 percent above average. The large fourth quarter seasonality observed in this data is typical of companies (Mattel and Amazon, for example) whose sales increase greatly during the holiday season.

Measuring the Effectiveness of Advertising 509 Another Model for Estimating Ad Effectiveness Another model used to measure advertising effectiveness is suggested by Gary Lilien, Phillip Kotler, and Sridhar Moorthy in their book Marketing Decision Models (Prentice-Hall, 1992). The model is described by Equation 4: (4) Qt = a + λQt - 1 + bLN (At ) + c max (0, ΔAt ) t In Equation 4 the following parameters are true: ■ Qt = Period t sales ■ At = Period t advertising ■ ΔAt = Percentage increase in advertising for period t compared to period t –1 ■ b, c, a, and λ are parameters that must be estimated. The LN (At) term incorporates the fact that the effectiveness of advertising diminishes as more advertising is done. The last term in the equation gives you an opportunity to model the effect of a change in advertising on sales. The λ represents the fraction of past sales used to predict current sales. Because past sales have been built up by past advertising, this term incorporates the fact that past loyalty built up through advertising affects current sales. In the next section you will see that the model for sales described by Equation 4 incorporates enough flexibility to enable two very different but interesting adver- tising strategies (pulsing and continuous spending) to be optimal in different situations. NOTE In contrast to the multiplicative SCAN*PRO model discussed in Chapter 31, the model defined by Equation 4 is an additive model. You can now use this new model to measure the effectiveness of advertising based on data in the file addata.xls. This file contains 36 months of sales (in thousands of dollars) and advertising (in hundreds of dollars) for a dietary product. For variety, find the parameters defined by Equation 4 that minimize SSE instead of MAPE. The following steps enable you to fit Equation 4 to this data (see Figure 34-3): 1. For each month compute ΔAt by copying from F8 to F9:F42 the formula =(D8-D7)/D7. 2. Copy the formula =a+lambda*E8+b*LN(D8)+c_*MAX(0,F8) from G8 to G9:G42 to generate the forecast for sales during months 2–36.

510 Part IX: Advertising Figure 34-3: Diet product ad data 3. Copy the formula =(G8-C8)^2 from H8 to H9:H42 to compute the squared error for months 2–36. 4. In H5 compute SSE with the formula =SUM(H8:H42). 5. Copy the formula =C8-G8 from I8 to I9:I42 to compute the error for each month. 6. Use the Solver window in Figure 34-4 to estimate the parameter values. You can find the fitted version of Equation 4 to be as follows: Qt = 2.37 + .56Qt - 1 + 2.95LN (At) - .43 max (0, ΔAt) The formula =RSQ(G8:G42,C8:C42) in cell I5 shows that the model explains 69 percent of sales variation. The formula =STDEV(I8:I42) in cell J5 shows that 68 percent of the forecasts should be accurate within $3,264 and 95 per- cent accurate within $6,528. Of course, if data were seasonal you would have to modify the model to account for the effects of seasonality as you did in the “Forecasting Software Sales” section of Chapter 31.

Measuring the Effectiveness of Advertising 511 Figure 34-4: Solver window for ad data Optimizing Advertising: Pulsing versus Continuous Spending Now that you have modeled the relationship between advertising and monthly sales, you may use Solver to calculate a profit-maximizing advertising strategy. The work for this example is in the optimize diet worksheet of the addata.xls file (see Figure 34-5). The following assumptions are made: ■ 30 percent profit margin on sales ■ Unit product price of $1,000 ■ Cost of $100 per ad ■ Month 1 sales = 12 units ■ Month 1 ads = 9

512 Part IX: Advertising Figure 34-5: Continuous spending example The goal is to use the GRG Multistart Solver engine to determine the number of ads during months 2–36 to maximize the total profit earned during months 2–36. Proceed as follows: 1. Determine the profit for months 2–36 by copying the formula =price*C9* profit_margin-100*D9 from F9 to F10:F43. 2. Generate sales in Column C during months 2–36 by copying the forecasting formula =a+lambda*C8+b*LN(D9)+c_*MAX(0,E9) from C9 to C10:C43. The Solver window shown in Figure 34-6 determines a profit-maximizing adver- tising strategy: You can constrain each month’s advertising to be at least .10 because an adver- tising level of 0 causes the LN function to be undefined, and for some reason, if you constrain a changing cell to be non-negative, Solver may try negative values for the changing cell. A relatively constant amount should be spent on advertising

Measuring the Effectiveness of Advertising 513 each month. This is known as a continuous spending strategy. During later months advertising drops because you have less of “a future” to benefit from the advertis- ing. This is similar to the drop in retention and acquisition spending near the end of the planning horizon that was observed in Chapter 22, “Allocating Marketing Resources between Customer Acquisition and Retention.” Figure 34-6: Solver window for continuous spending example The pulse worksheet shows an example where several of the problem parameters are changed, and in doing so, Solver found the optimal advertising policy shown in Figure 34-7. In Figure 34-7 you can observe a pulsing strategy in which you can alternate between a high and low level of advertising. Vijay Mahajan and Eitan Muller (“Advertising Pulsing Policies for Generating Awareness for New Products,” Marketing Science, 1986, pp. 89–106) discuss in more detail the conditions under which a continuous spending strategy is optimal. Prasad Naik Murali Mantrala and Alan Sawyer (“Planning Media Schedules in the Presence of Dynamic Advertising Quality,” Marketing Science, 1998, pp. 214–35) discuss conditions under which pulsing is optimal. The general consensus is that in actual situations a continuous spending strategy is much more likely to be optimal than a pulsing strategy.

514 Part IX: Advertising Figure 34-7: Example of pulsing Summary In this chapter you learned the following: ■ The following Adstock Model (specified by Equations 1 and 2) enables the mar- keting analyst to model the fact that the effect of advertising decays over time. (1) Quarter 1 ADSTOCK = LAMBDA * INITIALADSTOCK + QUARTER 1 ADS. (2) Quarter t ADSTOCK = LAMBDA * QUARTER T-1 ADSTOCK + QUARTER T ADS.

Measuring the Effectiveness of Advertising 515 ■ Fitting the model shown in Equation 3 allows the marketing analyst to deter- mine how seasonality, price, trend, and advertising affect sales. (3) Predicted Sales = (CONST * (TRENDQuarter# + ADEFFECT * ADSTOCK) * (PRICE)-elasticity * (Seasonal Index) ■ If sales are modeled by Equation 4, then either a continuous spending or pulsing strategy can be optimal. (4) Qt = a + λQt - 1 + bLN (At ) + c max (0, ΔAt ) t Exercises 1. If the Adstock Model is fitted to monthly data and yields lambda = 0.7, then what would be the “half life” of advertising? 2. How could the effect of a product display on sales be incorporated into Equation 3? 3. Suppose you model daily sales of 3M painter’s tape at Lowe’s. 3M runs a national ad campaign for painter’s tape on the Home and Garden Channel, whereas Lowe’s sometimes runs ads in the local Sunday paper. How can you determine which type of advertising is more effective?



35 Media Selection Models A ll companies that advertise must choose an allocation of their ad budgets from a large number of possible choices. These choices include allocating how much money should be spent in different ad channels such as radio, print, TV and the Internet, as well as deciding on which channels, networks, or publications each product should be advertised, and for daily shows, such as the NBC Evening News, on what days of the week the ads should be placed. To put the importance of advertising in the U.S. economy in perspective, consider that in 2013 U.S. companies spent $512 billion on advertising. The breakdown of these expenditures is as follows: ■ Newspapers: $91 billion ■ Magazines: $42 billion ■ TV: $206 billion ■ Radio: $35 billion ■ Movies: $3 billion ■ Outdoor: $33 billion ■ Internet: $101 billion A method used to allocate ad spending among available media vehicles that maximizes the effectiveness of the advertising is known as a media selection model. In this chapter you will learn about several widely used media selection models that can help U.S. companies allocate this $512 billion dollars more effectively. A Linear Media Allocation Model A.M. Lee, A.J. Burkart, Frank Bass and Robert Lonsdale were among the first researchers to use linear optimization models for media selection. Using the concepts outlined in their respective articles, “Some Optimization Problems in Advertising Media,” (Journal of the Operational Research Society, 1960) and “An Explanation of Linear Programming in Media Selection,” ( Journal of Marketing

518 Part IX: Advertising Research, 1966) you can develop a linear Solver model that can be used to allocate a TV advertising budget among network TV shows to ensure that a company’s ads are seen enough times by each demographic group. Assume Honda has decided it wants its June 2014 TV ads to be seen at least this many times by the following demographic groups: ■ 100 million women 18–30 ■ 90 million women 31–40 ■ 80 million women 41–50 ■ 70 million women more than 50 ■ 100 million men 18–30 ■ 90 million men 31–40 ■ 80 million men 41–50 ■ 70 million men more than 50 Honda can advertise on Oprah, Jeopardy!, the Late Show with David Letterman, Notre Dame Football, Saturday Night Live, The Simpsons, Seinfeld, ER, and Monday Night Football (MNF). The costs and demographic information for each show are shown in Figure 35-1, and the file Media data.xls (see the basic solver worksheet). For example, a 30-second ad on Oprah costs $32,630 and reaches 6 percent of all women 18–30, and such. There are 20 million women 18–30, and so on. Figure 35-1: Honda’s media allocation An exposure to a group occurs when a member of the group sees an ad. Honda’s exposure goal for each age and gender group is given in Row 17. For example, one goal is to have your ads seen by at least 100 million women 18–30, and so on. These numbers are usually determined by either the firm’s prior ad experiences or for a new product using the ad agency’s prior experience.