Share
Explore BrainMass

# Pepsi: probabilities, sensitivity analysis, decision tree

Please construct the decision tree for the problem attached, insert the probabilities and values as given in the scenario (make sure to include in the tree the possibility that the one-month forecast is favourable or not), roll back the tree, and determine the course of action that PEPSI should take.

Perform a sensitivity analysis on the probabilities of there being a significant increase in demand or not.

Describe each step of the analysis (including the sensitivity analysis), and the conclusions you have reached as a result of this analysis: include graphs and tables.

Identify advantages and limitations of this Decision Tree as a method for making decisions. Suggest potential means for overcoming any weaknesses.

PEPSI , a manufacturer of soft drinks, sells directly to restaurants, bars and public houses with sales representing about 20% of the local soft-drinks market. The company sells its products in bottles and cans, but chiefly in kegs to be served in draught form.

In the financial year 2009/10 the company achieved excellent results. The value of sales increased by 28% to £60 million and pre-tax profits of £6 million represented an increase of 55% on the previous year. Much of the company's success was based on sales of draught drinks. The number of outlets selling PEPSI?s product had increased substantially over recent years and the upward trend in sales had been accelerating.

The growth in draught soft-drink sales had, however, created some problems for the company's managers. In particular, there was concern that in the coming August, when sales reached a seasonal peak, there might not be enough kegs available to meet demand. Kegs are 45 litres, stainless steel containers in which the drinks are supplied to customers. After use, empty kegs are returned to the factory where they are inspected for damage, cleaned and refilled. In 2010, PEPSI owned about 100,000 kegs, but in view of the expansion of sales, it was felt by some managers that this stock should be increased.

In January 2011, the recently formed Keg Steering Committee met to consider the position for the coming summer. This committee consisted of managers from the Operations, Accounting, Sales and Marketing departments. John Fleetwood, the Operations Manager, put forward a proposal that 4000 new kegs should be ordered immediately from the manufacturer in Birmingham so that they would be available in time for the peak summer demand (the manufacturer would only supply in batches of 1000 kegs).

Dave Mitchell, the accountant was less sure. At £50 per keg, this would amount to an expenditure of £200,000 and there was no certainty that all these kegs would be needed. However, Fleetwood pointed out that, even if they were not needed this year, the kegs might be required in the following year.

"In that case," retorted Mitchell, "We'll have tied up £200,000 for a year unnecessarily. Given a 5% rate of interest, that would cost us £10,000."

At this point, Sally Martin, the Marketing Manager, intervened. "Surely there are intermediate positions. We could presumably order any number of batches from 0 to 4.
What we need is some estimate of the likely levels of demand in August and the consequences of having a given number of kegs available to meet these levels of demand."

Richard Skills, the Sales Manager, pointed out that the sales forecasts were really only reliable one month ahead, partly because this was linked to the long-range weather forecast. In hot, dry summers, sales rocketed. "Perhaps we should delay our decision until July when we'll get the August forecast," he said.

"If we do that," replied Fleetwood, "it may be too late. Other brewers might be placing orders with the keg manufacturers and I reckon there would be a 10% chance that they could not get the kegs produced for us in time for August. Surely you must have some idea of what sales are likely to do. After all, your reps are constantly touring the country, talking to people in the trade."

Skills were silent for a few moments. "Well, if you really twist my arm, the best I can say is this. I reckon there's about a 70% chance that sales in August this year will be significantly up on August last year, in other words, by at least 5% and a 30% chance that we will see no significant increase. But remember, this is only a very rough estimate."

Martin turned to Mitchell. "OK, can we do some calculations on what the consequences are of having different numbers of kegs available for August assuming first no significant increase in sales, and secondly an increase of at least 5%?"

Mitchell was sceptical, but under pressure, he agreed to do some rough calculations. For simplicity, he assumed that:
i) either 0, 2000 or 4000 kegs would be ordered;
ii) if it was needed, each new keg would make only one journey in August thereby supplying 45 litres to customers and earning a contribution of £8;
iii) if sales increase by at least 5% then at least 4000 kegs could be used in August;
iv) the only costs are those resulting from tying-up the capital expenditure on the kegs (at 5% interest rate) until they will be needed the following year, for example, to replace worn-out kegs.

[Calculate Mitchell?s figures for change in profit on the basis of his assumptions given above]

"I still think it might be worth delaying our decision until we get the August sales forecast," said Skills, "even if that does mean taking a risk that the kegs will not be available."

"They're not bad. If you can give me a couple of days I'll let you have a summary of their recent performance."

After some further discussion, it was agreed to reconvene the committee at the end of the week when Skills' figures could be looked at. A decision would then be made on whether to go ahead and order a specific number of kegs immediately (and if so how many, 2000 or 4000) or to delay the purchasing decision until the sales forecast was available.

On the basis of Skills? data about the accuracy of the one-month forecasts it was possible to recompute some of the probabilities of the outcomes. It was most likely that the one-month forecast would be that sales would increase significantly in August (representing both seasonal characteristics and the long-term trend) ? Skills estimated this probability to be around 0.8 .

Having more accurate forecasts of demand means that you are better able to estimate the actual amount of demand ? using Bayes? Theorem we can now make a new computation of the conditional probability of there actually being a significant increase in sales given a one-month forecast of a significant increase in sales (which is p = 0.9), and the probability of significant sales increase given an unfavourable forecast (which is p = 0.3).

#### Solution Preview

I made the decision tree in Excel using Tree Plan (http://www.treeplan.com/). I used the assumptions from the case below (the probabilities are the ones mentioned at the end of the story):

Choices:
buy 0, 2, 4 batches of kegs (at £50,000 per batch)

Costs:
each keg (there are 1000 in a batch) will earn £8
each batch of kegs will cost 5% in interest

Probabilities:
80% chance that the one-month forecast will be favorable
20% chance that the one-month forecast will not be favorable

given a favorable forecast, 90% chance that sales will increase (10% chance that they wont)
given an unfavorable forecast, 30% chance that sales will increase (70% chance that they wont)

I also assumed that if sales increase, you would ...

#### Solution Summary

The following posting helps with problems involving probabilities, sensitivity analysis and decisions trees for a company.

\$2.19