# Microsoft Excel Problems

Topics: Time series analysis, Moving average, Forecasting Pages: 12 (1820 words) Published: December 5, 2012
Excel Problems

1. The following time series represent the total population of the United States, in thousands, over the last 12 years.

|Year |Population (in 000,s) |
|1991 |253,493 |
|1992 |256,894 |
|1993 |260,255 |
|1994 |263,436 |
|1995 |266,557 |
|1996 |269,667 |
|1997 |272,912 |
|1998 |276,115 |
|1999 |279,295 |
|2000 |282,434 |
|2001 |285,545 |
|2002 |288,600 |

a. Use a 2-period moving average to forecast the population of the United States in 2003.

[pic]

b. Use a 3-period moving average to forecast the population of the United States in 2003 c. Which averaging period provides a better historical fit based on the MAD criterion?

[pic]

2. Refer to the data provided in problem 1.

Use a 3-period weighted moving average to forecast the population of the United States in 2003. Use Solver to determine the optimal weights based on minimizing the MAD criterion.

[pic]

3. Refer to the data provided in problem 1.

a. Use exponential smoothing with a smoothing constant of 0.5 to forecast the population of the United States in 2003.

[pic]

b. Use exponential smoothing with a smoothing constant of 0.8 to forecast the population of the United States in 2003. c. Which of the two methods provides a more accurate forecast based on the MAD criterion?

[pic]

4.Refer to the data provided in problem 1. Using Solver to find the optimal alpha that minimizes MAD, use exponential smoothing to forecast the population of the United States in 2003.

[pic]

5.Refer to the data provided in problem 1.
a. What is the linear trend equation that best fits the data? b. What is the forecast of the population of the United States in 2003 using the linear trend equation? c. What is the MAPE for this method?

[pic]

6.The following data, provided by the U.S. Bureau of Citizenship and Immigration Services, represent the number of immigrants admitted to the United States, in thousands, from 1990-2001.

|Year |No. of Immigrants |
| |(in 000,s) |
|1990 |1536 |
|1991 |1827 |
|1992 |974 |
|1993 |904 |
|1994 |804 |
|1995 |720 |
|1996 |916 |
|1997 |798 |
|1998 |654 |
|1999 |647 |
|2000 |850 |
|2001 |1064 |

a. Use a 2-period moving average to forecast the number of immigrants in 2002.

[pic]

b. Use a 3-period moving average to forecast the number of immigrants in 2002. c. Which averaging period provides a better historical fit based on the MAD criterion?

[pic]

7. Refer to the data provided in problem 6.

Use a 3-period weighted moving average to forecast the number of immigrants in 2002. Use Solver to determine the optimal weights based on minimizing the MAD criterion.

[pic]

8. Refer to the data provided in problem 6.

a. Use exponential smoothing with a smoothing constant of 0.3 to forecast the number of immigrants in 2002.

[pic]

b. Use exponential smoothing with a smoothing constant of 0.6 to...