View the step-by-step solution to:

TUTORIAL FOR ADBUDG ADVERTISING BUDGETING (ADBUDG) ADBUDG is an advertising sales response model developed by Little (1970) that uses judgmental...

Running Adbudg model in Excel (Blue Mountain Coffee Company Case). In the attached file, it starts from page 8.

This is in the field of Marketing Modeling (advertising budget with ADBUDG MODEL)
Blue Mountain Coffee Company case.pdf

TUTORIAL FOR ADBUDG ADVERTISING BUDGETING (ADBUDG)
ADBUDG is an advertising sales response model developed by Little
(1970) that uses judgmental inputs on market response to determine the
best level and timing of advertising expenditures. This implementation
of ADBUDG is designed to accompany the Blue Mountain Coffee case.
On the Model menu, select Advertising Budgeting (adbudg.xls) to
see the Introduction screen. Click Next to bring up the Main Menu.
First choose Calibration Based on Managerial Inputs to calibrate
parameter values for the ADBUDG Model. Provide the six input values for calibrating the response model. Then
click Calculate to see the estimated parameters of the response function
(c, d, and “Persistence”). The response curve changes as you change
your input values. 224 Chapter 8 Tutorials Whenever you have a set of inputs that you want to retrieve later,
you can save them by clicking Save As and providing a name for the selected inputs. Once you have saved and named a set of inputs and the corresponding coefficient estimates, you can activate it by marking it in the dropdown menu (e.g., “Reset,” as shown in the above example). Notice that
the name of the data set for the current calibration will then be shown in
the left hand corner of the main sheet. When you have finished the model calibration, click Next to get
back to the main worksheet.
Next, on the Model menu, click Main Menu and choose Forecast
Analysis; click OK. Advertising Budgeting (ADBUDG) 225 Use the dialog box to set the parameters for the forecasting task. Except for “Initial Share Previous Period,” the parameter values will apply
to all 12 periods. If your values for “Copy Effectiveness” and “Media
Efficiency” vary over time, you have to enter values for each period directly on the spreadsheet. Click Base Case to restore the parameter values built into the
ADBUDG spreadsheet.
Once you have entered all the data relevant to deciding the advertising budget, you can experiment by entering different values for each
period and observing the effects on market share and profit. To try out
alternative advertising plans enter these values directly on the sheet in
the blue-colored columns. The table will then show how changes in such
variables as advertising expense (“AD”) and copy efficiency (“Copy”)
affect market share (“Share”) and profit contribution (“Contribution”)
over time.
For instance in the partial screen shown, advertising expenditure
(“AD”) increases in the third period whereas “Media” (Media Effi- 226 Chapter 8 Tutorials ciency) and “Copy” (Copy Effectiveness) drop, mitigating the effect of
the increase in market share for this period. Built into the ADBUDG spreadsheet is a Multicriteria Goalseek feature. On the Model menu, choose Main Menu and then click
Multicriteria Goalseek. This feature uses Excel’s Solver tool to help
you decide the advertising budget. You can set the goal criterion by providing decision weights for the
short-term (four periods) and long-term (12 periods) market share and
cumulative profit. Solver will then try to optimize the advertising budget
subject to the constraints you impose on the yearly (four periods) budgets.
Weights can be any value. Their relative values are what count. For
example in the picture short-term profit (“4”) is twice as important as
short-term share. You can give weights of 0 to objectives that are irrelevant in a specific decision situation. Advertising Budgeting (ADBUDG) 227 The information you provide is then passed into the worksheet. The
objective function for the optimization procedure as implemented in
ADBUDG is of a simple additive functional form—it is just one example of how to deal with multiple objectives. In this implementation the
criteria (that is, profit and market share) are measured on different scales
and have to be normalized before optimization. We do this by computing
a scaled value for each criterion. We have chosen the scaling factor arbitrarily such that the scaled values for all of the criteria will be equal to
1.0 if the company freezes the advertising budget at the maintenance
level of $2M.
NOTE: Higher values of goals are more valuable than lower values. Clearly there are other ways to specify the multicriteria objective function. 228 Chapter 8 Tutorials Sometimes the optimization procedure produces an apparently
suboptimal solution for allocating the ad budget over timeSolver may
have settled on a local maximum. In other cases, running Solver may not
lead to convergence at all. You may sometimes obtain a (better) new result by providing different starting values for the advertising
expenditures in each period. Please see the appendix to Chapter 2 in the
text.
If you want to conduct additional analyses with Solver directly on
the main worksheet, you must first unprotect the spreadsheet: on the
Tools menu select Protection and then Unprotect Sheet. Go to the Model menu, choose Main Menu, select Graph, and
click OK to view a chart showing your advertising plan and its impact
on market share and contribution. Advertising Budgeting (ADBUDG) 229 Reference
Little, John D. C. 1970, “Models and managers: The concept of a
decision calculus,” Management Science, Vol. 16, No. 8, pp.
B466-B485. 230 Chapter 8 Tutorials BLUE MOUNTAIN COFFEE COMPANY CASE*
Blue Mountain’s share of the coffee market had slipped badly during the
past decades, although brand share has recently stabilized. The advertising manager was concerned because the increased advertising budget he
had obtained the previous year had been cut back in midyear because top
management was dissatisfied with the results. In addition, he thought it
was vital to increase Blue Mountain’s share so it would not lose distributors.
The advertising manager faced the problem of preparing and justifying an advertising budget for the coming fiscal year. He was considering
using the ADBUDG model to help him.
In May 1994, Reginald Van Tassle, advertising manager for the Blue
Mountain Coffee Company, tugged at his red mustache and contemplated the latest market share report. This was a dismal moment.
“Blimey,” he muttered, “I’ve got to do something to turn this darned
market around before it’s too late for Blue Mountain and for me. But I
can’t afford another mistake like last year’s...”
Reginald Van Tassle had been hired by James Anthoney, vice president of marketing for Blue Mountain, in the summer of 1992. Prior to
that time he had worked for companies in the Netherlands and in Singapore and had gained a reputation as a sharp and effective advertising
executive. Now, in the spring of 1994, he was fighting to reverse a longterm downward trend in Blue Mountain’s market position.
Indeed, Lucinda Pogue (the president and a major stockholder of the
Blue Mountain Company) had been dismayed to hear that Blue Mountain Coffee’s share of the market was dropping back toward 5.4 percent
where it had been a year before. She had remarked rather pointedly to
Reggie that if market share and profitability did not improve during the
next fiscal year she might have to take “some rather drastic actions” and
had murmured something about “a ticket back to Singapore.” Blue Mountain’s market position
Blue Mountain Coffee was an old, established company in the coffee
business, with headquarters in Squirrel Hill, Pennsylvania. Its market
area included the East Coast and Southern regions of the United States
and a fairly large portion of the Midwest. During its “good old days” in
the 1950s, the company had enjoyed as much as 15 percent of the market
in these regions. The brand had been strong and growing, and the company sponsored such popular radio and TV programs as “The Blue
Mountain Comedy Hour” and “Blue Mountain Capers.”
* This is a revised version of a case written by Professors William F. Massy, David B. Montgomery, and
Charles B. Weinberg which appeared in G.S. Day, G.J. Eskin, D.B. Montgomery, and C.B. Weinberg,
Planning: Cases in Computer and Model Assisted Marketing (Scientific Press, Redwood City, California,
1973). The case was revised by Gary L. Lilien and Katrin Starke. The new revised software was designed
by Katrin Starke and John Lin under the supervision of Professor Gary L. Lilien. Advertising Budgeting (ADBUDG) 231 Blue Mountain began to experience some difficulties in the 1960s:
TV production and time costs rose and competition stiffened as many
other regional old-line companies were absorbed by giant marketers,
such as General Foods and Proctor & Gamble. Furthermore, the advent
of freeze-dried products and the increasing promotion and popularity of
instant coffee put additional pressure on Blue Mountain which stuck
with its traditional ground vacuum-packed coffee as its only line.
Nonetheless, Blue Mountain’s troubles had only started. U.S. coffee
consumption dropped steadily in the 1970s and 1980s after peaking in
1962 when 74 percent of Americans drank about three cups per day. By
the end of the ‘80s only about half of all Americans drank coffee and
they were drinking an average of 1.7 cups per day. At the same time, the
coffee market became more oligopolistic, and the “Big Three,” i.e.,
Procter & Gamble (Folgers), Kraft General Foods (Maxwell House), and
Nestle (Nescafe) together controlled over three quarters of the national
market. Coffee was considered a commodity and competition was
mainly on price. Under these circumstances, Blue Mountain’s share
slipped from 12 percent at the beginning of the 1980s to about five and
half percent at the end of the decade.
Since then, however, its share has been fairly stable. Management attributed this to a hard-core group of loyal buyers combined with an
active (and expensive) program of consumer promotions and price-off
deals to the trade. Jim Anthoney, the vice president of marketing, believed that they had halted the erosion of share just in time. A little more
slippage, he said, and Blue Mountain would begin to lose distributors.
This would have been the beginning of the end for this venerable company. Operation breakout
When Lucinda Pogue became president in 1990, her main objective was
to halt the decline in market position and, if possible, to bring about a
turnaround. She had succeeded in the first objective. However, she and
Anthoney agreed the strategy they were using—intensive consumer and
trade promotion—would not win back much of the lost market share.
They both thought that they needed to increase consumer awareness
of the Blue Mountain brand and develop more favorable attitudes about
it to improve its market position. This could be done only through advertising. Since the company produced a quality product (it was noticeably
richer and more aromatic than many competing coffees), they thought
that a strategy of increasing advertising weight might succeed. They initiated a search for a new advertising manager, eventually hiring Reginald
Van Tassle.
After a period of familiarizing himself with the Blue Mountain Company, the American coffee market, and the advertising scene, Van Tassle
began developing a plan to revitalize Blue Mountain’s advertising program. First, he released the company’s current advertising agency and
requested proposals from a number of others interested in obtaining the
account. While he told them that the amount of advertising might in- 232 Chapter 8 Tutorials crease somewhat, he emphasized that he was most concerned with the
kind of appeal and copy execution. The company and the various agencies agreed that nearly all the advertising budget should go into spot
television. Network sponsorship was difficult because of the regional
character of Blue Mountain’s markets, and no other medium could
match the impact of TV for a product like coffee.
The team from Aardvark Associates, Inc., won the competition with
an advertising program built around the theme “Blue Mountain Pure.”
Aardvark recommended a 30 percent increase in the quarterly advertising budget to give the new program a fair trial. After considerable
negotiation with Lucinda Pogue and Jim Anthoney and further discussion with the agency, Van Tassle compromised on a 20 percent increase.
The new campaign was to start in the autumn of 1993, which was the
second quarter of the company’s 1994 fiscal year (the fiscal year started
July 1,1993 and would end June 30, 1994). It was dubbed “operation
breakout.”
Blue Mountain had been advertising at an average rate of $2.0 million per quarter for the last several years. This seemed to be enough to
maintain market share at about its current level of 5.4 percent. Neither
Reggie Van Tassle nor Jim Anthoney anticipated that competitors’ expenditures would change much during the next few years regardless of
any increase in Blue Mountain’s advertising.
One basis for the 1994 plans was the expectation that the quarterly
spending level of $2 million (ignoring several variations) would be
enough to maintain market share at its current 5.4 percent level. Reggie
felt that increasing advertising by 20 percent would increase market
share to six percent.
This projected result sounded pretty good to Lucinda Pogue, especially after she had consulted the company’s controller. The controller
wrote her a memorandum about the advertising budget increase and its
results (Exhibit 1). August 1, 1993
To:
From:
Subject: CONFIDENTIAL MEMO Lucinda Pogue, President
I. Figure, Controller
Proposed 20 percent increase in advertising I think that Reggie’s proposal to increase advertising by 20 percent (from a
quarterly rate of $2.0 million to one of $2.4 million) is a good idea. He predicts
that we will achieve a market share of six percent, compared to our current 5.4
percent. I can’t comment about the feasibility of this assumption: that’s Reggie’s
business, and I assume he knows what he’s doing. I can tell you, however, that
such a result would be highly profitable.
As you know, the wholesale price of coffee has been running about $17.20
per 12-pound case. Deducting our average retail advertising and promotional allowance of $1.60 per case and our variable costs of production and distribution
of $11.10 per case leaves an average gross contribution to fixed costs and profit
of $4.50 per case. Figuring a total market of about 22 million cases per quarter Advertising Budgeting (ADBUDG) 233 and a share change from 0.054 to 0.060 (a 0.006 increase), we would have the
following increase in gross contribution:
change in gross contribution = $4.50 × 22 million × .006
= $0.60 million By subtracting the amount of the increase in advertising expense due to the
new program and then dividing by this same quantity, we get the advertising
payout rate:
Advertising Payout Rate = change in gross contribution - change in ad expense
change in ad expense
= $0.10 million = 0.50
$0.20 million
That is, we can expect to make $.50 in net contribution for each extra dollar
spent on advertising. You can see that as long as this quantity is greater than zero
(at which point the extra gross contribution just pays for the extra advertising),
increasing our advertising is a good deal.
I think Reggie has a good thing going here, and my recommendation is to go
ahead. Incidentally, the extra funds we should generate in net contribution (after
advertising expense is deducted) should help to relieve the cash flow bind which
I mentioned last week. Perhaps we will be able to maintain the quarterly dividend after all.
EXHIBIT 1 Reggie had, of course, warned that the hoped-for six percent share
was not a sure thing and, in any case, that it might take more than one
quarter before the company saw the full effects of the new advertising
program.
The new advertising campaign broke as scheduled on October 1,
1993, the first day of the second quarter of the fiscal year. Reggie was a
bit disappointed with Aardvark’s commercials and a little worried by the
early reports from the field. The store audit report of market share for
July, August, and September showed only a fractional increase in share
over the 5.4 percent of the previous period. Nevertheless, Van Tassle
thought that, given a little time, things would work out and the campaign
would eventually reach its objective.
The October, November, and December market share report came
through in mid-January. It showed Blue Mountain’s share of the market
to be 5.6 percent. On January 21, 1994, Reggie received a carbon copy
of a memorandum to Lucinda from I. Figure (Exhibit 2). 234 Chapter 8 Tutorials January 20, 1994
MEMO
To:
Lucinda Pogue, President
From:
I. Figure, Controller
Subject: Failure of Advertising Program
I am most alarmed at our failure to achieve the market-share target projected
by Reginald Van Tassle. The 0.2 point increase in market share we achieved in
October-December is not sufficient to return the cost of the increased advertising. Ignoring the month of October, which obviously represents a start-up period,
a 0.2 point increase in share generates only $200,000 in extra gross contribution
per quarter. This must be compared to the $400,000 we have expended in extra
advertising. The advertising payout rate is thus only -0.50: much less than the
break-even point.
I know Mr. Van Tassle expects shares to increase again next quarter, but he
has not been able to say by how much. The new program projects an advertising
expenditure increase per quarter of $400,000 over last year’s winter-quarter
level. I don’t see how we can continue to make these expenditures without a better prospect of return on our investment.
cc: R. J. Anthoney
R. Van Tassle EXHIBIT 2 On Monday, January 24, Jim Anthoney telephoned Reggie to say
that Lucy wanted to review the new advertising program immediately.
Later that week, after several rounds of discussion during which Reggie
failed to convince Lucy and Jim that the program would eventually be
successful, they decided to return to fiscal 1993 advertising levels.
Reggie renegotiated the TV spot contracts and by the middle of February
had cut advertising back toward the $2 million per quarter rate. Aardvark
Associates complained that the efficiency of their media buy suffered
during February and March, because of Blue Mountain’s abrupt reduction in advertising expenditure. However, they were unable to say by
how much. Blue Mountain also set the spring 1994 rate at the normal
level of $2.0 million. Market share for the quarter beginning in January
turned out to be slightly over 5.6 percent, while that for the one starting
in April was about 5.5 percent. Planning for fiscal year 1995
In mid-May of 1994, Reginald Van Tassle faced the problem of recommending an advertising budget for the four quarters of fiscal 1995. He
was already very late in dealing with this assignment, since the company
would have to up its media buys soon if it was to effect any substantial
increase in weight during the summer quarter of 1994. Alternatively, it
would have to act fast to reduce advertising expenditures below its tentatively budgeted “normal” level of $2.0 million. Advertising Budgeting (ADBUDG) 235 During the past month, Van Tassle had spent a lot of time reviewing
the difficulties of fiscal 1994. He remained convinced that a 20 percent
increase in advertising should produce somewhere around a six percent
market-share level. He based this partly on hunch and partly on studies
performed by academic and business market researchers.
One lesson he had learned from his unfortunate experience the previous year was that presenting too optimistic a picture to top
management was unwise. On the other hand, if he had made a conservative estimate he might not have obtained approval for the program.
Besides, he still believed that the effect of advertising on share was
greater than implied by the company’s performance in the autumn of
1993. This judgment should be a part of top managers’ information set
when they evaluated his proposal. Alternatively, if they doubted his
judgment and had good reasons to do so, he wanted to know about them.
After all, Lucinda Pogue and Jim Anthoney had been in the coffee business a lot longer than he had, and they were pretty savvy.
Perhaps the problem lay in his assessment of the speed with which
the new program would take hold. He had felt it would take a little time
but had not tried to pin it down further. That’s pretty hard, after all. He
had said nothing very precise about this to management. Could he blame
Mr. Figure for adopting the time horizon he did?
As a final complicating factor, Van Tassle had just received a report from Aardvark Associates about the quality of the advertising copy
and the appeals used the previous autumn and winter. Contrary to
expectations, these ads rated only about 0.95 on a scale that rated an
“average ad” at 1.0. These tests were based on the so-called theater
technique, in which the agency inserted various spots into a filmed
“entertainment” program and determined their effects on the subjects’
choices in a lottery designed to simulate purchasing behavior.
Fortunately, the ads currently being shown rated about 1.0 on the same
scale. A new series of ads scheduled for showing during the autumn,
winter, and spring of 1995 appeared to be much better. The agency could
not undertake theater testing until it completed production during the
summer, but experts in the agency were convinced that the new ads
would rate at least 1.15. Reggie was impressed with these ads, but he
knew that such predictions were often optimistic. In the meantime, he
had to submit a budget request for all four quarters of fiscal 1995 to
management him with this problem, Reggie decided to use a marketing
To help within the next week.
planning model, an adaptation of Little’s (1970) ADBUDG model. The marketing planning model
After describing his problem to Jill Stillman, director of research for
Blue Mountain, Van Tassle asked Stillman to give him a list of the basic
inputs the model required. After much tugging at his red mustache and
several conferences with Stillman, Van Tassle arrived at a preliminary
set of estimates for the basic inputs (Exhibit 3). Only the estimates relating to market share and the advertising plan itself required a lot of head
scratching. 236 Chapter 8 Tutorials Market share at start of period
Maintenance advertising per period ($MM)
Market share at period end with
Saturation advertising
20 percent increase in advertising
No advertising
Market share in long run with no advertising
Copy effectiveness
Media efficiency
Previous period market share
Brand price ($/unit)
Contribution ($/unit)
Average product price ($/Unit)
Product sales per period (MM cases)
Product sales growth rate per period 5.4%
2.0
6.3%
5.7%
4.7%
0.0%
1.0
1.0
5.4%
$17.20
$4.50
$17.20
22.0
1.0% EXHIBIT 3
ADBUDG basic input values After some thought Van Tassle concluded that, if his advertising
budget were reduced to zero, he would lose perhaps half his market
share in the next year or about an eighth of it in the next quarter. He settled on the figure 4.7 percent as the market share at the end of the first
quarter with no advertising. Similarly, he arrived at the figures of 6.3
percent for a saturation advertising program and 5.7 percent for a 20
percent increase in advertising (figuring it would take him about three
quarters of this 20 percent increase to reach a six percent share).
He then began experimenting with different values for his market
share estimate and media—and copy-effectiveness estimates. He was
hoping he could use the results of his analysis to explain past results and
to help prepare his 1995 plan. He constructed Exhibit 4—a table of
events—to help him recall the history of the program. Advertising Budgeting (ADBUDG) ADBUDG
Period
Month
0
1
1
1
2
2
2
3
3
3
4
4
4
1
2 July
Aug.
Sept.
Oct.
Nov.
Dec.
Jan.
Feb.
Mar.
Apr.
May
June
July
Oct. Yr
93
93
93
93
93
93
94
94
94
94
94
94
94
94 Fiscal
Year
94/95 237 AD
Budget Copy
and
Media 1st qtr
(FY 94) $2.00 1.00 5.4 %
5.4 % 2nd qtr $2.40 0.95 5.6 % Operation “Breakout” 3rd qtr $2.10 0.95 5.6 % Budget cut 4th qtr $2.00 1.00 5.5 %
→ 5.4 % Market
Share Events Copy effectiveness up
Now! 1.00
>1.00 EXHIBIT 4
Blue Mountain table of events Recent developments: The U.S. coffee market in transition
Even though advertising budgeting was preoccupying her, Pogue kept
thinking that in the light of recent trends more could be done to help
Blue Mountain’s long-term position in the coffee market. What for a
long time seemed to be a rather static market was now undergoing profound structural changes. In recent years, the coffee bean had had a
renaissance as consumption in the U.S. slowly climbed after more than
two decades of decrease—but Blue Mountain, along with such other traditional roasters as Procter & Gamble, Philip Morris, and Nestle, had
been unable to capitalize on this trend. The growth was primarily due to
specialty brews and the expansion of coffee and coffee accessory chains,
such as Starbucks Coffee Company of Seattle. Pogue was wary that specialty coffees, growing at about 20 p...

This is the end of the preview. Download to see the full text

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question