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.