Question

2005 2006 2007 2008 2009 2010 6,5es 7,205 7.30 8,333 8.263 9,368 7,697 8,599 9,607 10,945 10.592 14.,801 Ctr3 8.184 8,950 10,171 11,244 11,080 15,514 Ctr4 10,096 10,383 12,346 2,729 13297 18,155 Click here for the Excel Data Fie (a-1) Use Megastat or MINTAB to deseasonalize Pepsico's quarterly data (Round your answers to 3 decimal places.) (a-2) State the adjusted four quarterly indexes. (Round your answers to 3 decimal places.) Q3 04 (a-3) what is the trend model for the deseasonalize d time series? (Round your answers to 2 decimal places.) (b) State the model found when performing a regression using seasonal binaries (A negative value should be indicated by a minus sign. Round your answers to 4 decimal place 01+ 02 аз (c) Use the regression equation to make a prediction for each quarter in 2011. (Enter your answers in millions rounded to 2 decimal places.) Forecasts S million 2011 01 2011 02 2011 03 2011 04 Data Extra Sheet Quarterly PepsiCo Revenues (millions), 2005-2010 Year Quarter Revenue 2005 In row-column format Qtr1 Qtr2 Qtr3 Qtr4 Qtr1 6,585 7,697 8,184 10,096 7.205 8.599 8,950 10,383 7.350 Quarter 2005 2007 7,350 9,607 10,945 10,592 14,801 2008 8,333 2010 Qtr1 6,585 7,697 8,184 8,950 10,171 11,244 11,080 15514 10,096 10.38312.346 12.729 13.297 18,155 7,205 8.263 Qtr3 2006 Data are from Standard & Poor's Stock Reports, March, 2007,http:/finance yahoo.com and httn /hwsu mernaninnlina.com Qtr4 Qtr1 Qtr2 2007 LearningStats 10,171 12,346 8,333 10,945 11,244 12,729 8,263 10,592 11,080 13,297 9.368 14,801 Copyright 0 2013 by The McGraw-Hit Companies 2008 This spreadsheet is intended solely for educational purposes by licersed usersof LeamingStors. It may not be copled orresols tor profit 2009 Qtr1 2010 Qt315,514 Qtr4 18,155

Answer 1

a3) constauct the Deseascnalized by using Mega stut Excel add-Rn CMega stat) pxoduse steps: n ExceL, selet ad- Tns Mega stat > tfme seata step3: select the data aoge ndes put aonge of seasinal data as cell ciiC25 step6: cick ok 6,S8s 7,697 8,19 9218.o00 0.25 8,25.5 13 3600-9 o.qqG |◆.019 | 1023.2 0.983 2 2 10,383 846500 . 16 1.18, 1839у. 2 10.998 1.013 .019 |9417.0 어983-5 |962.3. ,es ฯ, 67 o, 2,346 0281.2So 1.013 | 10,Sc33 1,2 0 803-973 11戸36.a 16| | У 미5 1815" | 12,129 | loar): 125-11-13у to,18a. |0.986 11.013 1. 012 019 0,459. to |lo, 572 |loヲ3a.oo 2 7.3 ,262 11,239. 2d s 13,2161035 M 1. 13 1-063 .013 019 4 2 4

a-2) 2. 0. 2) 0.3068 01 119 3.2 Adjusted fou。PSaztegly indexes: 2 Adiwsteolo구39 | 1.013 | 1.019 | 1-121 @づ-The hend modd ts :109.114,+2。62-1

b)

SUMMARY OUTPUT Regression Statistics MultipleR R Square Adjusted R Square Standard Error Observations 0.9276 0.8605 0.8311 1171.697.2 24 ANOVA MS gnificance F Regression Residual Total 4 1.61E+08 40211532 29.29003 6.87E-08 19 26084611 1372874 23 1.87E+08 coefficients and ard Err t Stat P-value Lower 95%Upper 95%ower 95.09 pper 95.0% Intercept Q1 02 Q3 8973.2833 684.8835 13.10191 5.8E-11 7539.806 10406.76 7539.806 10406.76 275.7893 35.01115 7.8771842.11E-07 202.5101 349.0685 202.5101 349.0685 -4156.2988 684.5851-6.07127 7.72E-06-5589.15-2723.45-5589.15-2723.45 -1909.2548 680.094 -2.80734 0.011242-3332.71-485.802-3332.71-485.802 1701.3774 677.3851-2.51168 0.021211-3119.16 -283.594-3119.16 -283.594

Regression using seasonal binaries:

yt = 8973.2833+275.78931-4156.2988 Q1-1909.2548 Q2-1701.3774 Q3

c)

Predicted Value for 2011

2011Q1 11711.72 14234.55 14718.22 02 Q3 Q4 16695.38