# EXCEL HOMEWORK ATTACHING THE FILE! 4-19 on page 141. 4-30 on page 143. 4-32 on pages 143-144. 5-28 on page 179. 5-35 on page 180. 4-19 on page 141. 4-30 on page 143. 4-32 on pages 143-144. 5-28 on p

EXCEL HOMEWORK ATTACHING THE FILE!

4-19 on page 141.4-30 on page 143.4-32 on pages 143-144.5-28 on page 179.5-35 on page 180.

4-19 on page 141.

4-30 on page 143.

4-32 on pages 143-144.

5-28 on page 179.

5-35 on page 180.

4-19 Bus and subway ridership in Washington, D.C., during the summer months is believed to be heavily

tied to the number of tourists visiting the city. During the past 12 years, the following data have been

obtained:

(a) Plot these data and determine whether a linear model is reasonable.

(b) Develop a regression model.

(c) What is expected ridership if 10 million tourists visit the city?

YEAR NUMBER OF

TOURISTS

(1,000,000s)

RIDERSHIP

(1000,000’S)

1 7 15

2 2 10

3 6 13

4 4 15

5 14 25

6 15 27

7 16 24

8 12 20

9 14 27

10 20 44

11 15 34

12 7 17

4-30 In 2012, the total payroll for the New York Yankees was almost \$200 million, while the total

payroll for the Oakland Athletics (a team known for using baseball analytics or sabermetrics) was about

\$55 million, less than one-third of the Yankees’ payroll. In the following table, you will see the payrolls (in

millions) and the total number of victories for the baseball teams in the American League in the 2012

season. Develop a regression model to predict the total number of victories based on the payroll. Use

the model to predict the number of victories for a team with a payroll of \$79 million. Based on the

results of the computer output, discuss the relationship be-tween payroll and victories.

Team Payroll (\$1,000,000s) Number of Victories

Baltimore Orioles 81.4 93

Boston Red Sox 173.2 69

Chicago White Sox 96.9 85

Cleveland Indians 78.4 68

Detroit Tigers 132.3 88

Kansas City Royals 60.9 72

Los Angeles Angels 154.5 89

Minnesota Twins 94.1 66

New York Yankees 198.0 95

Oakland Athletics 55.4 94

Seattle Mariners 82.0 75

Tampa Bay Rays 64.2 90

Texas Rangers 120.5 93

Toronto Blue Jays 75.5 73

4-32 The closing stock price for each of two stocks was recorded over a 12-month period. The

closing price for the Dow Jones Industrial Average (DJIA) was also recorded over this same time period.

These values are shown in the following table:

MONTH DIJA STOCK 1 STOCK 2

1 11,168 48.5 32.4

2 11,150 48.2 31.7

3 11,186 44.5 31.9

4 11,381 44.7 36.6

5 11,679 49.3 36.7

6 12,081 49.3 38.7

7 12,222 46.1 39.5

8 12,463 46.2 41.2

9 12,622 47.7 43.3

10 12,269 48.3 39.4

11 12,354 47.0 40.1

12 13,063 47.9 42.1

13 13,326 47.8 45.2

(a) Develop a regression model to predict the price of stock 1 based on the Dow Jones Industrial

Average.

(b) Develop a regression model to predict the price of stock 2 based on the Dow Jones Industrial

Average.

(c) Which of the two stocks is most highly correlated to the Dow Jones Industrial Average over this time

period?

5-28 Sales of industrial vacuum cleaners at R. Lowenthal Supply Co. over the past 13 months are as

follows:

SALES

(\$1,000s)

MONTH SALES

(\$1,000s)

MONTHS

11 January 14 August

14 February 17 September

16 March 12 October

10 April 14 November

15 May 16 December

17 June 11 January

11 July

(a) Using a moving average with three periods, determine the demand for vacuum cleaners for next

February.

(b) Using a weighted moving average with three periods, determine the demand for vacuum cleaners for

February. Use 3, 2, and 1 for the weights of the most recent, second most recent, and third most recent

periods, respectively. For example, if you were forecasting the demand for February, November would

have a weight of 1, December would have a weight of 2, and January would have a weight of 3.

(c) Evaluate the accuracy of each of these methods.

(d) What other factors might R. Lowenthal consider in forecasting sales?

5-35 A major source of revenue in Texas is a state sales tax on certain types of goods and services.

Data are com-piled, and the state comptroller uses them to project future revenues for the state budget.

One particular category of goods is classified as Retail Trade. Four years of quarterly data (in

\$1,000,000s) for one particular area of southeast Texas follow:

QUARTER YEAR 1 YEAR 2 YEAR 3 YEAR 4

1 218 225 234 250

2 247 254 265 283

3 243 255 264 289

4 292 299 327 356

(a) Compute a seasonal index for each quarter based on a CMA.

(b) Deseasonalize the data and develop a trend line on the deseasonalized data.

(c) Use the trend line to forecast the sales for each quarter of year 5.

(d) Use the seasonal indices to adjust the forecasts found in part (c) to obtain the final forecasts.

EXCEL HOMEWORK ATTACHING THE FILE! 4-19 on page 141. 4-30 on page 143. 4-32 on pages 143-144. 5-28 on page 179. 5-35 on page 180. 4-19 on page 141. 4-30 on page 143. 4-32 on pages 143-144. 5-28 on p
4-19 on page 141. 4- 30 on page 143. 4-32 on pages 143- 144. 5-28 on page 179. 5- 35 on page 180. 4-19 Bus and subway ridership in Washington, D.C., during the summer months is believed to be heavily tied to the number of tourists visiting the city. During the past 12 years, the following data have been obtained: (a) Plot these data and determine whether a linear model is reasonable. (b) Develop a regression model. (c) What is expected ridership if 10 million tourists visit the city? YEAR NUMBER OF TOURISTS (1,000,000s) RIDERSHIP (1000,000’S) RY sY R,Y (Y (Y RSY IY QY RIY EY EY R,Y ,Y REY (,Y QY R,Y (sY sY RQY (EY 0Y R(Y (SY Y REY (sY RSY (SY EEY RRY R,Y IEY R(Y sY RsY 4-30 In 2012, the total payroll for the New York Yankees was almost \$200 million, while the total payroll for the Oakland Athletics (a team known for using baseball analytics or sabermetrics) was about \$55 million, less than one-third of the Yankees’ payroll. In the following table, you will see the payrolls (in millions) and the total number of victories for the baseball teams in the American League in the 2012 season. Develop a regression model to predict the total number of victories based on the payroll. Use the model to predict the number of victories for a team with a payroll of \$79 million. Based on the results of the computer output, discuss the relationship be-tween payroll and victories. Team Payroll (\$1,000,000s) Number of Victories )BfombNaFYSamNfF1 Y 0RTEY IY )N1oNUYXFHYYN2Y RsIT(Y Q Y uymcBONYrymoFYYN2 Y QT Y 0,Y ufFVFfBUHY3UHmBU1 Y s0TEY Q0Y \$FoaNmoYZmOFa1Y RI(TIY 00Y [BU1B1YumoeYXNeBf1 Y QST Y s(Y VN1YTUOFfF1YTUOFf1 Y R,ET,Y 0 Y mUUF1NoBYZ’mU1 Y ETRY QQY 4F’YPNaQYPBUQFF1 Y R 0TSY ,Y SBQfBUHYToyfFomc1 Y ,,TEY EY YFBoofFYBamUFa1Y 0(TSY s,Y ZBbMBY)BeYXBe1 Y QET(Y SY ZF2B1YXBUOFa1 Y R(ST,Y IY ZNaNUoNY)fDFY]Be1 Y s,T,Y sIY 4-32 The closing stock price for each of two stocks was recorded over a 12- month period. The closing price for the Dow Jones Industrial Average (DJIA) was also recorded over this same time period. These values are shown in the following table: MONTH DIJA STOCK 1 STOCK 2 RY RRlRQ0Y E0T,Y I(TEY (Y RRlR,SY E0T(Y IRTsY IY RRlR0QY EET,Y IRT Y EY RRlI0RY EETsY IQTQY ,Y RRlQs Y E TIY IQTsY QY R(lS0RY E TIY I0TsY sY R(l(((Y EQTRY I T,Y 0Y R(lEQIY EQT(Y ERT(Y Y R(lQ((Y EsTsY EITIY RSY R(l(Q Y E0TIY I TEY RRY R(lI,EY EsTSY ESTRY R(Y RIlSQIY EsT Y E(TRY RIY RIlI(QY EsT0Y E,T(Y JBC \$FVFfNM YB YaFOaF11mNU YbNHFf YoN YMaFHmco YoyF YMamcFY NiY1oNcQY RYPB1FHY NUYoyF Y\$N’Y ]NUF1Y 3UHD1oamBf TVFaBOFT JPC \$FVFfNM YB YaFOaF11mNU YbNHFf YoN YMaFHmco YoyF YMamcFY NiY1oNcQY (YPB1FHY NUYoyF Y\$N’Y ]NUF1Y 3UHD1oamBf TVFaBOFT JcC rymcyYNi YoyF Yo’N Y1oNcQ1Y m1YbN1o YymOyfe YcNaaFfBoFH YoN YoyF Y\$N’Y ]NUF1Y 3UHD1oamBfY TVFaBOFYNVFa Yoym1 YombF MFamNHL 5-28 Sales of industrial vacuum cleaners at R. Lowenthal Supply Co. over the past 13 months are as follows: SALES (\$1,000s) MONTH SALES (\$1,000s) MONTHS RRY ]BUDBaeY REY TDOD1oY REY ^FPaDBae Y RsY YFMoFbPFa Y RQY BacyY R(Y ScoNPFa Y RSY TMamfY REY 4NVFbPFa Y R,Y BeY RQY \$FcFbPFa Y RsY ]DUF Y RRY ]BUDBaeY RRY ]DfeY JBC W1mUOYB YbNVmUO YBVFaBOFY ’moyYoyaFF YMFamNH1l YHFoFabmUF YoyF YHFbBUHY iNaYVBcDDb YcfFBUFa1 YiNa YUF2o ^FPaDBaeT JPC W1mUO YB Y’FmOyoFH YbNVmUO YBVFaBOFY ’moyYoyaFF YMFamNH1l YHFoFabmUF YoyF YHFbBUHY iNaYVBcDDb YcfFBUFa1 YiNa ^FPaDBaeTY W1FYIlY(l YBUHY RYiNaY oyFY ’FmOyo1 YNi YoyFY bN1o YaFcFUol Y1FcNUHY bN1oYaFcFUol YBUHY oymaH YbN1o YaFcFU o MFamNH1l YaF1MFcomVFfeTY ^NaYF2BbMfFl Ymi YeND Y’FaF YiNaFcB1omUO YoyF YHFbBUHY iNaY^FPaDBael Y4NVFbPFa Y’NDfH yBVFY BY’FmOyo YNi YRl Y\$FcFbPFa Y’NDfH YyBVFY BY’FmOyo YNi Y(l YBUHY ]BUDBae Y’NDfH YyBVFY BY’FmOyo YNi YIT JcC _VBfDBoF YoyFY BccDaBceY NiYFBcyY NiYoyF1FY bFoyNH1T JHC ryBoYNoyFa YiBcoNa1Y bmOyoYXT YVN’FUoyBf YcNU1mHFaY mUYiNaFcB1omUO Y1BfF1L 5-35 A major source of revenue in Texas is a state sales tax on certain types of goods and services. Data are com -piled, and the state comptroller uses them to project future revenues for the state budget. One particular category of goods is classified as Retail Trade. Four years of quarterly data (in \$1,000,000s) for one particular area of southeast Texas follow: QUARTER YEAR 1 YEAR 2 YEAR 3 YEAR 4 RY (R0Y ((,Y (IEY (,SY (Y (EsY (,EY (Q,Y (0IY IY (EIY (,,Y (QEY (0 Y EY ( (Y ( Y I(sY I,QY JBC uNbMDoFYBY1FB1NUBf YmUHF2 YiNaY FBcyY aDBaoFaY PB1FHYNUYBYuTT JPC \$F1FB1NUBfmbF YoyFY HBoBY BUHYHFVFfNM YB YoaFUHY fmUFYNUY oyFY HF1FB1NUBfmbFH YHBoBT JcC W1FY oyFYoaFUHY fmUFYoN YiNaFcB1o YoyFY 1BfF1Y iNaYFBcyY aDBaoFaY NiYeFBaY ,T JHC W1FYoyFY1FB1NUBf YmUHmcF1 YoN YBH`D1o YoyFY iNaFcB1o1 YiNDUHY mUYMBaoY JcCYoNYNPoBmUY oyFYimUBf YiNaFcB1o1T