1. You have been asked to forecast Steel Resources Inc.'s monthly Sales.
Its past monthly sales (starting from the oldest data) over the past 16 months were 22, 23, 25, 24, 27, 28, 28, 29, 31, 32, 33, 34, 34, 35, 36, 38. (These sales data are in $ millions).
a. Run a scatter graph with time on the horizontal. Add trendline, equation and R^2 to graph.
b. Run an excel regression, assuming no seasonality in the data. Write the equation that would enable you to estimate Steel's future sales.
c. Overall does it appear that the regression you ran was a good fit? (Yes/No). On what do you base your answer?)
d. What is your specific sales forecast ($ millions)for each of the next 4 months?
e. Attach your excel spreadsheet with your answers, data used, scatter and regression output.
2. You collected data from the same month on 7 Travel Agencies in order to determine if you could estimate Sales for each agency based on its level of Advertising. (You can copy the data below and paste it in an excel spreadsheet.)
|
Travel Agency
|
Advertising
|
Sales
|
|
1
|
20000
|
150000
|
|
2
|
25000
|
180000
|
|
3
|
45000
|
220000
|
|
4
|
40000
|
210000
|
|
5
|
55000
|
300000
|
|
6
|
60000
|
350000
|
|
7
|
80000
|
400000
|
a. Running a simple excel regression, what is your estimate of sales for each travel agency for next month?
b. Do you believe that Advertising helps explain Sales for the 7 travel agencies? (Why?)
c. Using your estimating equation in part a (above), what would have been your specific estimate of $Sales for each travel agency (1, 2, 3, ..7) for the previous month? (Put them in the Table below.)
d. Under the null hypothesis, the estimate of $Sales for each travel agency (1, 2, 3,....7) is the simple average of past sales for all travel agencies. (Put that estimate in the column of the Table below.)
|
Travel Agency
|
Advertising ($)
|
Actual Sales
|
Predicted Sales (w Advertising)
|
Predicted Sales (under Null)
|
|
1
|
20000
|
150000
|
|
|
|
2
|
25000
|
180000
|
|
|
|
3
|
45000
|
220000
|
|
|
|
4
|
40000
|
210000
|
|
|
|
5
|
55000
|
300000
|
|
|
|
6
|
60000
|
350000
|
|
|
|
7
|
80000
|
400000
|
|
|
e. Using the last 3 columns of the above table, which model appears to provide better estimates: the null (without advertising); or the regression with advertising? (Which model do you believe should be used to make future predictions?) (WHY?)
f. Attach your excel spreadsheet showing data used and regression output that would enable you to estimate Sales for each travel agency based on its level or advertising.
3. You work at Amazon in its worldwide music division and have been tasked with providing quarterly sales estimates for the future. You asked the accounting department to provide you with quarterly sales data starting in the 1st quarter of 2013 and extending to the 3rd quarter of 2016. Those data ($ millions) showed a sale's spike every 4th quarter, so you decide to use excel to run a regression with a dummy variable to estimate quarterly sales. (You can copy the accounting data below and paste it on an excel spreadsheet.)
a. Create a scatter graph using quarterly sales (see data table below).
b. Does it appear that quarterly sales spike every 4th quarter? (Why might this be?)
c. Run a 4th quarter dummy variable regression equation. What is your general estimating equation for quarterly sales revenues?
d. What is your specific estimate of 4th quarter sales in 2016 ($ millions)) AND FOR EACH quarter in 2017?
|
Year
|
Quarter
|
Quarterly Sales ($ millions)
|
|
2012
|
1
|
700
|
|
2
|
750
|
|
3
|
800
|
|
4
|
1500
|
|
2013
|
1
|
800
|
|
2
|
1000
|
|
3
|
950
|
|
4
|
1650
|
|
2014
|
1
|
1000
|
|
2
|
1100
|
|
3
|
1300
|
|
4
|
1800
|
|
2015
|
1
|
1050
|
|
2
|
1150
|
|
3
|
1200
|
e. Attach your excel spreadsheet showing data used and regression output that enabled you to estimate future quarterly sales with 4th quarter spikes.