Part I. Computer Lab Exercise ---Complete and turn in the computer lab exercise for forecasting. This will be handed out in class on Friday, June 29 (we will meet for part of the class on June 29 in the computer lab). In addition, you need to complete and turn in answers to Parts II and III below.
Use the data below for parts II and III of the problem set. A,
B, C, and D are the last four non-zero digits of your
social security number. Parts II and III will be completed outside
of class.
PART II Forecasting with Management Scientist
| Year | Period | Quarter | Sales |
| 1997 | 1 | 1 | 1A |
| 2 | 2 | 1B | |
| 3 | 3 | 1C | |
| 4 | 4 | 2D | |
| 1998 | 5 | 1 | 2A |
| 6 | 2 | 2B | |
| 7 | 3 | 2C | |
| 8 | 4 | 3D | |
| 1999 | 9 | 1 | 1A |
| 10 | 2 | 2B | |
| 11 | 3 | 3C | |
| 12 | 4 | 3D | |
| 2000 | 13 | 1 | 2A |
| 14 | 2 | 3B | |
| 15 | 3 | 3C | |
| 16 | 4 | 4D |
where A,B,C and D are the last four non-zero digits of your social security number.
a) Using Management Scientist, determine the best moving average forecast
for the above sales data
(only consider two-period and four-period moving averages). Provide
the Management Scientist printout
for each of the above moving average periods.
b) Using the Management Scientist software, determine the "best" exponential
smoothing model for the sales
data by reporting MSE's. Provide a listing of alternative MSE's
based on smoothing constants (0,.1,.2,...,.9,1).
Also, provide the Mgt. Scientist printout of the best exponential
smoothing model.
c) Using Management Scientist, compute a linear trend forecast for the
above sales data. What is the slope
and the intercept of this forecasting equation? What does the slope
value suggest about these data? Include
a copy of this forecast.
d) Using Management Scientist, compute a trend and seasonal forecast
for the above sales data. How many
seasons did you select? Why? How much are sales above average
in the strongest quarter? Include a copy
of this forecast also.
e) Based on the above (parts 1-4), which forecasting technique
is best for the above data? Why?
PART III Linear Regression Forecasting with Excel
A real estate sales conmany is attempting to determine what factors
impact the sales price of homes. Data
for 10 homes is reported below:
Sales Price
Home Size Condition
Rating
Y, $ thou
X1, hundreds X2, 1 (poor) to 10
of square ft. 10 (best)
60.0
2A
5
32.7
1B
2
57.7
20
9
45.5
17
3
47.0
1D
8
55.3
2B
4
64.5
24
7
42.6
1A
6
54.5
1C
7
57.5
2D
2
where A,B,C and D are the last four non-zero digits of your SS#.
a) Find the "best fit regression line" for your data. Assume Sales
Price is the dependent variable. To do a
regression in EXCEL, select "Tools" then "Data Analysis." Provide
a printout of your data and the regression
output?
b) Based on the t-test, are the individual independent variables significant?
Conduct the t-tests for each of the
independent variables at the .05 level of significance.
c) Based on the value of the r-squared term, what can you say about
the relationship between the independent
variables and the dependent variable? Can the regression equation be
used to accurately forecast sales price?
d) Based on your regression, what would be the forecasted sales price
for a house with a home size
of 20 (2000 sq. ft.) and a condition of 7?
NOTE: PLEASE STAPLE THE PAGES IN ORDER.