Question

The Bubble Up Bottling Company of Budapest, Hungary, is interested in forecasting sales
of Bubble Up over the next two years. The company has analyzed Bubble Up's market share
for its service area over the past 20 quarters. Market share has generally been growing as
indicated in the table B below.
During this same five year period, total soft drink sales in the region (as measured in
100,000s of cases) have been as in the table A below.
	Table A (in 100,000s of cases)
			Year
Season	1	2	3	4	5
Winter	114	114	124	132	134
Spring	130	146	151	160	166
Summer	158	177	175	184	205
Fall	131	142	146	144	148
The company would like to forecast Bubble Up soft drink sales for years 6 and 7. To do so,
use a linear trend model to forecast market share for each quarter of years 6 and 7 and
seasonal model to forecast total soft drink sales in these periods. Then multiply
the forecast of total soft drink sales by Bubble Up's sales during this period.
Prepare a business report to Bubble Up management containing your forecast and
explaining your approach.
	Table B
	Year		Season	Bubble Up's Market Share (in Percent)
	1		Winter	6.42
	1		Spring	6.58
	1		Summer	6.99
	1		Fall	6.82
	2		Winter	6.64
	2		Spring	6.46
	2		Summer	6.7
	2		Fall	7
	3		Winter	6.7
	3		Spring	6.84
	3		Summer	6.78
	3		Fall	6.48
	4		Winter	6.93
	4		Spring	6.79
	4		Summer	6.83
	4		Fall	7.21
	5		Winter	7.73
	5		Spring	8.16
	5		Summer	8.36
	5		Fall	8.23

How do you find the slope for the seasonal regression. I have a template to plug the numbers into, but i dont know what to plug in

Bubble Up Case and data (1).xls Compatibility Mode- Saved to my Mac Search Sheet Home Insert Page Layout Formulas Data Review View Share Connections Show Detail From From New Database Refresh HTML Text n FilterてAdvanced Text to Flash Remove Data Consolidate what-if Group Ungroup Subtotal Hide Detal Data Solver AlEdit LinksSo Analysis 114 302 1583 1314 114 1428 124 9 146 12 132 13 160 14 84 15 66 18 14820 148 Add. Model Add. Model (2) Sheet2 Lin. Reg. Lin. Reg. (2+ Select destination and press ENTER or choose Paste 86% 2

Answer 1

SOLUTION;-

Step 1: Determine total sales in year 6 and 7 using multiplicative seasonal method

All the cells containing formula are shown below

Column A,B,C,D do contain any formula, hence not shown.

Step 2: Determine BubbleUp's market share using linear trend model

Market share for each season of the next two years (6 and 7) is determined by the linear trend model. For example, for winter season of year 6, time index (x) = 21. Forecast of market share = 6.2177+0.0766*21 = 7.85

Step 3: Determine BubbleUp's sales forecast for year 6 and 7 by multiplying the Total sales forecast of each season by the forecast of market share for corresponding season.