finance assignment
Leila@
data_for_forecasting.xlsxSheet1
| forecasts from class | |||||||||||||
| year | annual sales | 30968.6 | 2015 sales *(1+ average rate of change (geometric) for all years) | ||||||||||
| 2016 | lagged sales | 32451.1 | exponental smoothing (forecast.ets) | ||||||||||
| 2015 | 28862.8 | 36066.9 | 37281.0 | proprietary method | |||||||||
| 2014 | 36066.9 | 37795.0 | 30756.0 | geomean | |||||||||
| 2013 | 37795.0 | 36157.0 | 31668.9 | arithmetic average (10 years) | |||||||||
| 2012 | 36157.0 | 32013.0 | 29166.7 | linear regression (sales on lagged sales) | |||||||||
| 2011 | 32013.0 | 26004.0 | 36003.7 | linear regression (sales on years) | |||||||||
| 2010 | 26004.0 | 23112.0 | 32989.5 | multivariable regression (see next page) | |||||||||
| 2009 | 23112.0 | 28438.0 | 32660.7 | average of other forecasts | |||||||||
| 2008 | 28438.0 | 24082.0 | |||||||||||
| 2007 | 24082.0 | 22148.0 | SUMMARY OUTPUT | ||||||||||
| 2006 | 22148.0 | 21930.0 | |||||||||||
| 2005 | 21930.0 | 19986.1 | Regression Statistics | ||||||||||
| 2004 | 19986.1 | 15534.0 | Multiple R | 0.9523924331 | |||||||||
| 2003 | 15534.0 | 13947.0 | R Square | 0.9070513467 | |||||||||
| 2002 | 13947.0 | 13292.0 | Adjusted R Square | 0.9026252203 | |||||||||
| 2001 | 13292.0 | 13137.0 | Standard Error | 2934.6791293305 | |||||||||
| 2000 | 13137.0 | 11751.0 | Observations | 23 | |||||||||
| 1999 | 11751.0 | 13882.0 | |||||||||||
| 1998 | 13882.0 | 12790.0 | ANOVA | ||||||||||
| 1997 | 12790.0 | 11229.0 | df | SS | MS | F | Significance F | ||||||
| 1996 | 11229.0 | 10291.0 | Regression | 1 | 1764937424.68444 | 1764937424.68444 | 204.9311915702 | 0 | |||||
| 1995 | 10291.0 | 8977.0 | Residual | 21 | 180859173.434691 | 8612341.59212815 | |||||||
| 1994 | 8977.0 | 7754.0 | Total | 22 | 1945796598.11913 | ||||||||
| 1993 | 7754.0 | 6691.0 | |||||||||||
| 1992 | 6691.0 | Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | ||||
| Intercept | 2324.7751183499 | 1403.0494602809 | 1.6569445227 | 0.1123967899 | -593.0259640879 | 5242.5762007877 | -593.0259640879 | 5242.5762007877 | |||||
| X Variable 1 | 0.9299831374 | 0.0649637433 | 14.315417967 | 0 | 0.7948836375 | 1.0650826372 | 0.7948836375 | 1.0650826372 | |||||
| sales (t+1) = intercept + beta * sales (t) | |||||||||||||
| 29166.6924150904 | |||||||||||||
| SUMMARY OUTPUT | |||||||||||||
| Regression Statistics | |||||||||||||
| Multiple R | 0.9517744445 | ||||||||||||
| R Square | 0.9058745932 | ||||||||||||
| Adjusted R Square | 0.9015961656 | ||||||||||||
| Standard Error | 3015.8608446486 | ||||||||||||
| Observations | 24 | ||||||||||||
| ANOVA | |||||||||||||
| df | SS | MS | F | Significance F | |||||||||
| Regression | 1 | 1925779199.70408 | 1925779199.70408 | 211.7307295682 | 0 | ||||||||
| Residual | 22 | 200099165.954255 | 9095416.6342843 | ||||||||||
| Total | 23 | 2125878365.65833 | |||||||||||
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | ||||||
| Intercept | -2572820.77901449 | 178178.097543656 | -14.4396018056 | 0 | -2942339.53680066 | -2203302.02122833 | -2942339.53680066 | -2203302.02122833 | |||||
| X Variable 1 | 1294.0597391304 | 88.9328844852 | 14.5509700559 | 0 | 1109.6242251456 | 1478.4952531153 | 1109.6242251456 | 1478.4952531153 | |||||
| sales (2016) = intercept + beta * 2016 | |||||||||||||
| 36003.6550724637 |
Sheet2
| sales | assets Windows User: Windows User: data from company financial statements | PPI food and feedstuffs Windows User: Windows User: Data from Federal Reserve Economic Database | S&P index Windows User: Windows User: Data for s&P500 iindex from finance.yahoo.com | 3 month t-bill rate (%) Windows User: Windows User: Data from Federal Reserve Economic database | corn, acres (million) Windows User: Windows User: Data from US Department of Agriculture Economic Research Service | corn, yield (bu/acre) Windows User: Windows User: Data from US Department of Agriculture Economic Research Service | corn price (per bu. Windows User: Windows User: Data from US Department of Agriculture Economic Research Service |
||||||||||||||||||||||
| 2016 | 2016 | 57947.6 | 157.4 | 2134.0 | 0.26% | 94.15 | 174.4 | 3.2 | SUMMARY OUTPUT | ||||||||||||||||||||
| 2015 | 28862.8 | 2015 | 61336.4 | 167.5 | 2080.4 | 0.12% | 88.00 | 167.2 | 3.6 | ||||||||||||||||||||
| 2014 | 36066.9 | 2014 | 59521.0 | 210.7 | 2067.6 | 0.02% | 90.90 | 173.4 | 3.7 | Regression Statistics | |||||||||||||||||||
| 2013 | 37795.0 | 2013 | 56266.0 | 192.7 | 1805.8 | 0.07% | 95.37 | 158.8 | 4.7 | Multiple R | 0.9759122013 | ||||||||||||||||||
| 2012 | 36157.0 | 2012 | 48207.0 | 204.3 | 1416.2 | 0.09% | 97.16 | 123.4 | 6.9 | R Square | 0.9524046247 | ||||||||||||||||||
| 2011 | 32013.0 | 2011 | 43266.0 | 188.6 | 1247.0 | 0.01% | 91.94 | 147.2 | 6.2 | Adjusted R Square | 0.9301934496 | ||||||||||||||||||
| 2010 | 26004.0 | 2010 | 41132.6 | 162.3 | 1180.6 | 0.14% | 88.19 | 152.8 | 5.2 | Standard Error | 2484.765841445 | ||||||||||||||||||
| 2009 | 23112.0 | 2009 | 38734.6 | 134.0 | 1095.6 | 0.05% | 86.38 | 164.7 | 3.6 | Observations | 23 | ||||||||||||||||||
| 2008 | 28438.0 | 2008 | 38575.7 | 144.2 | 896.2 | 0.19% | 85.98 | 153.9 | 4.1 | ||||||||||||||||||||
| 2007 | 24082.0 | 2007 | 34767.8 | 152.9 | 1481.1 | 3.27% | 93.53 | 150.7 | 4.2 | ANOVA | |||||||||||||||||||
| 2006 | 22148.0 | 2006 | 33636.8 | 127.5 | 1400.6 | 4.94% | 78.33 | 149.1 | 3.0 | df | SS | MS | F | Significance F | |||||||||||||||
| 2005 | 21930.0 | 2005 | 28754.0 | 120.9 | 1249.5 | 3.88% | 81.78 | 147.9 | 2.0 | Regression | 7 | 1853185678.81695 | 264740811.259564 | 42.8795243457 | 0.0000000084 | ||||||||||||||
| 2004 | 19986.1 | 2004 | 26258.0 | 119.5 | 1173.8 | 2.07% | 80.93 | 160.3 | 2.1 | Residual | 15 | 92610919.3021814 | 6174061.2868121 | ||||||||||||||||
| 2003 | 15534.0 | 2003 | 23768.0 | 125.7 | 1058.2 | 0.93% | 78.60 | 142.2 | 2.4 | Total | 22 | 1945796598.11913 | |||||||||||||||||
| 2002 | 13947.0 | 2002 | 12355.9 | 99.7 | 936.3 | 1.23% | 78.89 | 129.3 | 2.3 | ||||||||||||||||||||
| 2001 | 13292.0 | 2001 | 20469.4 | 98.5 | 1139.4 | 1.87% | 75.70 | 138.2 | 2.0 | Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | ||||||||||||
| 2000 | 13137.0 | 2000 | 17578.2 | 100.4 | 1314.9 | 6.17% | 79.55 | 136.9 | 1.9 | Intercept | -24381.5047847477 | 13454.5865585782 | -1.812133333 | 0.0900294264 | -53059.2771907238 | 4296.2676212285 | -53059.2771907238 | 4296.2676212285 | |||||||||||
| 1999 | 11751.0 | 1999 | 8903.1 | 99.5 | 1388.9 | 5.07% | 77.39 | 133.8 | 1.8 | assets Windows User: Windows User: data from company financial statements | 0.1662995085 | 0.137052425 | 1.2134007009 | 0.2437385401 | -0.1258208204 | 0.4584198375 | -0.1258208204 | 0.4584198375 | |||||||||||
| 1998 | 13882.0 | 1998 | 16319.8 | 102.4 | 1163.6 | 4.41% | 80.17 | 134.4 | 1.9 | PPI food and feedstuffs Windows User: Windows User: Data from Federal Reserve Economic Database | 68.4171507914 | 55.7855407789 | 1.2264316136 | 0.2389408862 | -50.4869147505 | 187.3212163333 | -50.4869147505 | 187.3212163333 | |||||||||||
| 1997 | 12790.0 | 1997 | 14652.7 | 110.4 | 955.4 | 5.14% | 79.54 | 126.7 | 2.4 | S&P index Windows User: Windows User: Data for s&P500 iindex from finance.yahoo.com | 1.8164275608 | 2.6724930008 | 0.6796753295 | 0.5070642643 | -3.8798564312 | 7.5127115529 | -3.8798564312 | 7.5127115529 | |||||||||||
| 1996 | 11229.0 | 1996 | 13847.4 | 117.7 | 757.0 | 5.03% | 79.23 | 127.1 | 2.7 | 3 month t-bill rate (%) Windows User: Windows User: Data from Federal Reserve Economic database | -17837.081639961 | 39705.9963180874 | -0.4492289149 | 0.6596891336 | -102468.409448146 | 66794.2461682243 | -102468.409448146 | 66794.2461682243 | |||||||||||
| 1995 | 10291.0 | 1995 | 6446.4 | 114.2 | 605.4 | 5.36% | 71.48 | 113.5 | 3.2 | corn, acres (million) Windows User: Windows User: Data from US Department of Agriculture Economic Research Service | 220.1133809944 | 196.2384615407 | 1.1216627937 | 0.2796414155 | -198.158998678 | 638.3857606667 | -198.158998678 | 638.3857606667 | |||||||||||
| 1994 | 8977.0 | 1994 | 11467.2 | 100.4 | 453.7 | 5.29% | 78.92 | 138.6 | 2.3 | corn, yield (bu/acre) Windows User: Windows User: Data from US Department of Agriculture Economic Research Service |
Windows User: Windows User: data from company financial statements | 53.8200180944 | 63.057760145 | 0.8535034859 | 0.406803856 | -80.5844161106 | 188.2244522995 | -80.5844161106 | 188.2244522995 | ||||||||||
| 1993 | 7754.0 | 1993 | 11445.6 | 110.2 | 461.8 | 3.10% | 73.24 | 100.7 | 2.5 | corn price (per bu. Windows User: Windows User: Data from US Department of Agriculture Economic Research Service |
Windows User: Windows User: Data from Federal Reserve Economic Database |
Windows User: Windows User: Data for s&P500 iindex from finance.yahoo.com |
Windows User: Windows User: Data from Federal Reserve Economic database |
Windows User: Windows User: Data from US Department of Agriculture Economic Research Service |
Windows User: Windows User: Data from US Department of Agriculture Economic Research Service |
Windows User: Windows User: Data from US Department of Agriculture Economic Research Service |
Windows User: Windows User: data from company financial statements | 945.5242081387 | 1122.2327990537 | 0.8425383832 | 0.4127197364 | -1446.4583814167 | 3337.5067976941 | -1446.4583814167 | 3337.5067976941 | ||||
| forecast = a + b1*value of variable 1 + b2*value of variable 2 + . . . | |||||||||||||||||||||||||||||
| -24381.5047847477 | 0.1662995085 | 68.4171507914 | 1.8164275608 | -17837.081639961 | 220.1133809944 | 53.8200180944 | 945.5242081387 | ||||||||||||||||||||||
| 57947.6 | 157.4 | 2134.0 | 0.26% | 94.15 | 174.4 | 3.2 | |||||||||||||||||||||||
| 32989.5 | -24381.5047847477 | 9636.66 | 10768.86 | 3876.26 | -46.38 | 20723.67 | 9386.21 | 3025.68 | |||||||||||||||||||||
