need this to be redone
habg1
bus_308_0.docxRunning Head: PREDICTIVE SALES REPORT 1
PREDICTIVE REPORT 8
Predictive Sales Report
BUS 308: Statistics for Managers
Author’s Name
Institutional affiliation
PREDICTIVE SALES REPORT
A retail store has recently hired you as a consultant to advice on economic conditions. One important indicator that the retail store is concerned about is the unemployment rate. The retail store has found that an increase in the unemployment rate will cause a lack of consumer spending in their stores. Retail stores use the unemployment rate to estimate how much inventory to keep at their stores, which is important in maintaining cost effectiveness. In this consultant role you will apply calculations and research to create a predictive sales report.
Part I
|
Year |
Jan |
Feb |
Mar |
Apr |
May |
Jun |
Jul |
Aug |
Sep |
Oct |
Nov |
Dec |
Annual |
|
1948 |
3.4 |
3.8 |
4 |
3.9 |
3.5 |
3.6 |
3.6 |
3.9 |
3.8 |
3.7 |
3.8 |
4 |
3.75 |
|
1949 |
4.3 |
4.7 |
5 |
5.3 |
6.1 |
6.2 |
6.7 |
6.8 |
6.6 |
7.9 |
6.4 |
6.6 |
6.05 |
|
1950 |
6.5 |
6.4 |
6.3 |
5.8 |
5.5 |
5.4 |
5 |
4.5 |
4.4 |
4.2 |
4.2 |
4.3 |
5.21 |
|
1951 |
3.7 |
3.4 |
3.4 |
3.1 |
3 |
3.2 |
3.1 |
3.1 |
3.3 |
3.5 |
3.5 |
3.1 |
3.28 |
|
1952 |
3.2 |
3.1 |
2.9 |
2.9 |
3 |
3 |
3.2 |
3.4 |
3.1 |
3 |
2.8 |
2.7 |
3.03 |
|
1953 |
2.9 |
2.6 |
2.6 |
2.7 |
2.5 |
2.5 |
2.6 |
2.7 |
2.9 |
3.1 |
3.5 |
4.5 |
2.93 |
|
1954 |
4.9 |
5.2 |
5.7 |
5.9 |
5.9 |
5.6 |
5.8 |
6 |
6.1 |
5.7 |
5.3 |
5 |
5.59 |
|
1955 |
4.9 |
4.7 |
4.6 |
4.7 |
4.3 |
4.2 |
4 |
4.2 |
4.1 |
4.3 |
4.2 |
4.2 |
4.37 |
|
1956 |
4 |
3.9 |
4.2 |
4 |
4.3 |
4.3 |
4.4 |
4.1 |
3.9 |
3.9 |
4.3 |
4.2 |
4.13 |
|
1957 |
4.2 |
3.9 |
3.7 |
3.9 |
4.1 |
4.3 |
4.2 |
4.1 |
4.4 |
4.5 |
5.1 |
5.2 |
4.30 |
|
1958 |
5.8 |
6.4 |
6.7 |
7.4 |
7.4 |
7.3 |
7.5 |
7.4 |
7.1 |
6.7 |
6.2 |
6.2 |
6.84 |
|
1959 |
6 |
5.9 |
5.6 |
5.2 |
5.1 |
5 |
5.1 |
5.2 |
5.5 |
5.7 |
5.8 |
5.3 |
5.45 |
|
1960 |
5.2 |
4.8 |
5.4 |
5.2 |
5.1 |
5.4 |
5.5 |
5.6 |
5.5 |
6.1 |
6.1 |
6.6 |
5.54 |
|
1961 |
6.6 |
6.9 |
6.9 |
7 |
7.1 |
6.9 |
7 |
6.6 |
6.7 |
6.5 |
6.1 |
6 |
6.69 |
|
1962 |
5.8 |
5.5 |
5.6 |
5.6 |
5.5 |
5.5 |
5.4 |
5.7 |
5.6 |
5.4 |
5.7 |
5.5 |
5.57 |
|
1963 |
5.7 |
5.9 |
5.7 |
5.7 |
5.9 |
5.6 |
5.6 |
5.4 |
5.5 |
5.5 |
5.7 |
5.5 |
5.64 |
|
1964 |
5.6 |
5.4 |
5.4 |
5.3 |
5.1 |
5.2 |
4.9 |
5 |
5.1 |
5.1 |
4.8 |
5 |
5.16 |
|
1965 |
4.9 |
5.1 |
4.7 |
4.8 |
4.6 |
4.6 |
4.4 |
4.4 |
4.3 |
4.2 |
4.1 |
4 |
4.51 |
|
1966 |
4 |
3.8 |
3.8 |
3.8 |
3.9 |
3.8 |
3.8 |
3.8 |
3.7 |
3.7 |
3.6 |
3.8 |
3.79 |
|
1967 |
3.9 |
3.8 |
3.8 |
3.8 |
3.8 |
3.9 |
3.8 |
3.8 |
3.8 |
4 |
3.9 |
3.8 |
3.84 |
|
1968 |
3.7 |
3.8 |
3.7 |
3.5 |
3.5 |
3.7 |
3.7 |
3.5 |
3.4 |
3.4 |
3.4 |
3.4 |
3.56 |
|
1969 |
3.4 |
3.4 |
3.4 |
3.4 |
3.4 |
3.5 |
3.5 |
3.5 |
3.7 |
3.7 |
3.5 |
3.5 |
3.49 |
|
1970 |
3.9 |
4.2 |
4.4 |
4.6 |
4.8 |
4.9 |
5 |
5.1 |
5.4 |
5.5 |
5.9 |
6.1 |
4.98 |
|
1971 |
5.9 |
5.9 |
6 |
5.9 |
5.9 |
5.9 |
6 |
6.1 |
6 |
5.8 |
6 |
6 |
5.95 |
|
1972 |
5.8 |
5.7 |
5.8 |
5.7 |
5.7 |
5.7 |
5.6 |
5.6 |
5.5 |
5.6 |
5.3 |
5.2 |
5.60 |
|
1973 |
4.9 |
5 |
4.9 |
5 |
4.9 |
4.9 |
4.8 |
4.8 |
4.8 |
4.6 |
4.8 |
4.9 |
4.86 |
|
1974 |
5.1 |
5.2 |
5.1 |
5.1 |
5.1 |
5.4 |
5.5 |
5.5 |
5.9 |
6 |
6.6 |
7.2 |
5.64 |
|
1975 |
8.1 |
8.1 |
8.6 |
8.8 |
9 |
8.8 |
8.6 |
8.4 |
8.4 |
8.4 |
8.3 |
8.2 |
8.48 |
|
1976 |
7.9 |
7.7 |
7.6 |
7.7 |
7.4 |
7.6 |
7.8 |
7.8 |
7.6 |
7.7 |
7.8 |
7.8 |
7.70 |
|
1977 |
7.5 |
7.6 |
7.4 |
7.2 |
7 |
7.2 |
6.9 |
7 |
6.8 |
6.8 |
6.8 |
6.4 |
7.05 |
|
1978 |
6.4 |
6.3 |
6.3 |
6.1 |
6 |
5.9 |
6.2 |
5.9 |
6 |
5.8 |
5.9 |
6 |
6.07 |
|
1979 |
5.9 |
5.9 |
5.8 |
5.8 |
5.6 |
5.7 |
5.7 |
6 |
5.9 |
6 |
5.9 |
6 |
5.85 |
|
1980 |
6.3 |
6.3 |
6.3 |
6.9 |
7.5 |
7.6 |
7.8 |
7.7 |
7.5 |
7.5 |
7.5 |
7.2 |
7.18 |
|
1981 |
7.5 |
7.4 |
7.4 |
7.2 |
7.5 |
7.5 |
7.2 |
7.4 |
7.6 |
7.9 |
8.3 |
8.5 |
7.62 |
|
1982 |
8.6 |
8.9 |
9 |
9.3 |
9.4 |
9.6 |
9.8 |
9.8 |
10.1 |
10.4 |
10.8 |
10.8 |
9.71 |
|
1983 |
10.4 |
10.4 |
10.3 |
10.2 |
10.1 |
10.1 |
9.4 |
9.5 |
9.2 |
8.8 |
8.5 |
8.3 |
9.60 |
|
1984 |
8 |
7.8 |
7.8 |
7.7 |
7.4 |
7.2 |
7.5 |
7.5 |
7.3 |
7.4 |
7.2 |
7.3 |
7.51 |
|
1985 |
7.3 |
7.2 |
7.2 |
7.3 |
7.2 |
7.4 |
7.4 |
7.1 |
7.1 |
7.1 |
7 |
7 |
7.19 |
|
1986 |
6.7 |
7.2 |
7.2 |
7.1 |
7.2 |
7.2 |
7 |
6.9 |
7 |
7 |
6.9 |
6.6 |
7.00 |
|
1987 |
6.6 |
6.6 |
6.6 |
6.3 |
6.3 |
6.2 |
6.1 |
6 |
5.9 |
6 |
5.8 |
5.7 |
6.18 |
|
1988 |
5.7 |
5.7 |
5.7 |
5.4 |
5.6 |
5.4 |
5.4 |
5.6 |
5.4 |
5.4 |
5.3 |
5.3 |
5.49 |
|
1989 |
5.4 |
5.2 |
5 |
5.2 |
5.2 |
5.3 |
5.2 |
5.2 |
5.3 |
5.3 |
5.4 |
5.4 |
5.26 |
|
1990 |
5.4 |
5.3 |
5.2 |
5.4 |
5.4 |
5.2 |
5.5 |
5.7 |
5.9 |
5.9 |
6.2 |
6.3 |
5.62 |
|
1991 |
6.4 |
6.6 |
6.8 |
6.7 |
6.9 |
6.9 |
6.8 |
6.9 |
6.9 |
7 |
7 |
7.3 |
6.85 |
|
1992 |
7.3 |
7.4 |
7.4 |
7.4 |
7.6 |
7.8 |
7.7 |
7.6 |
7.6 |
7.3 |
7.4 |
7.4 |
7.49 |
|
1993 |
7.3 |
7.1 |
7 |
7.1 |
7.1 |
7 |
6.9 |
6.8 |
6.7 |
6.8 |
6.6 |
6.5 |
6.91 |
|
1994 |
6.6 |
6.6 |
6.5 |
6.4 |
6.1 |
6.1 |
6.1 |
6 |
5.9 |
5.8 |
5.6 |
5.5 |
6.10 |
|
1995 |
5.6 |
5.4 |
5.4 |
5.8 |
5.6 |
5.6 |
5.7 |
5.7 |
5.6 |
5.5 |
5.6 |
5.6 |
5.59 |
|
1996 |
5.6 |
5.5 |
5.5 |
5.6 |
5.6 |
5.3 |
5.5 |
5.1 |
5.2 |
5.2 |
5.4 |
5.4 |
5.41 |
|
1997 |
5.3 |
5.2 |
5.2 |
5.1 |
4.9 |
5 |
4.9 |
4.8 |
4.9 |
4.7 |
4.6 |
4.7 |
4.94 |
|
1998 |
4.6 |
4.6 |
4.7 |
4.3 |
4.4 |
4.5 |
4.5 |
4.5 |
4.6 |
4.5 |
4.4 |
4.4 |
4.50 |
|
1999 |
4.3 |
4.4 |
4.2 |
4.3 |
4.2 |
4.3 |
4.3 |
4.2 |
4.2 |
4.1 |
4.1 |
4 |
4.22 |
|
2000 |
4 |
4.1 |
4 |
3.8 |
4 |
4 |
4 |
4.1 |
3.9 |
3.9 |
3.9 |
3.9 |
3.97 |
|
2001 |
4.2 |
4.2 |
4.3 |
4.4 |
4.3 |
4.5 |
4.6 |
4.9 |
5 |
5.3 |
5.5 |
5.7 |
4.74 |
|
2002 |
5.7 |
5.7 |
5.7 |
5.9 |
5.8 |
5.8 |
5.8 |
5.7 |
5.7 |
5.7 |
5.9 |
6 |
5.78 |
|
2003 |
5.8 |
5.9 |
5.9 |
6 |
6.1 |
6.3 |
6.2 |
6.1 |
6.1 |
6 |
5.8 |
5.7 |
5.99 |
|
2004 |
5.7 |
5.6 |
5.8 |
5.6 |
5.6 |
5.6 |
5.5 |
5.4 |
5.4 |
5.5 |
5.4 |
5.4 |
5.54 |
|
2005 |
5.3 |
5.4 |
5.2 |
5.2 |
5.1 |
5 |
5 |
4.9 |
5 |
5 |
5 |
4.9 |
5.08 |
|
2006 |
4.7 |
4.8 |
4.7 |
4.7 |
4.6 |
4.6 |
4.7 |
4.7 |
4.5 |
4.4 |
4.5 |
4.4 |
4.61 |
|
2007 |
4.6 |
4.5 |
4.4 |
4.5 |
4.4 |
4.6 |
4.7 |
4.6 |
4.7 |
4.7 |
4.7 |
5 |
4.62 |
|
2008 |
5 |
4.9 |
5.1 |
5 |
5.4 |
5.6 |
5.8 |
6.1 |
6.1 |
6.5 |
6.8 |
7.3 |
5.80 |
|
2009 |
7.8 |
8.3 |
8.7 |
9 |
9.4 |
9.5 |
9.5 |
9.6 |
9.8 |
10 |
9.9 |
9.9 |
9.28 |
|
2010 |
9.8 |
9.8 |
9.9 |
9.9 |
9.6 |
9.4 |
9.5 |
9.5 |
9.5 |
9.5 |
9.8 |
9.3 |
9.63 |
|
2011 |
9.1 |
9 |
8.9 |
9 |
9 |
9.1 |
9 |
9 |
9 |
8.9 |
8.6 |
8.5 |
8.93 |
|
2012 |
8.3 |
8.3 |
8.2 |
8.1 |
8.2 |
8.2 |
8.2 |
8.1 |
7.8 |
7.9 |
7.8 |
7.8 |
8.08 |
|
2013 |
7.9 |
|
|
|
|
|
|
|
|
|
|
|
|
In any statistical analysis, the average results contribute to a greater accuracy where predictions have to be made or decisions have to be made. For example, from the above information, the last column in the table represents the average early unemployment rated by months. The two sample from different years proves the concept.
|
Year |
Jan |
Feb |
Mar |
Apr |
May |
Jun |
Jul |
Aug |
Sep |
Oct |
Nov |
Dec |
Annual |
|
1948 |
3.4 |
3.8 |
4 |
3.9 |
3.5 |
3.6 |
3.6 |
3.9 |
3.8 |
3.7 |
3.8 |
4 |
3.75 |
Sum of monthly rate divided by number of months per year results in the monthly rate for that year (3.4+3.8+4+3.9+3.5+3.6+3.6+3.9+3.8+3.7+3.8+4)/12 = 3.75
|
Year |
Jan |
Feb |
Mar |
Apr |
May |
Jun |
Jul |
Aug |
Sep |
Oct |
Nov |
Dec |
Annual |
|
1958 |
5.8 |
6.4 |
6.7 |
7.4 |
7.4 |
7.3 |
7.5 |
7.4 |
7.1 |
6.7 |
6.2 |
6.2 |
6.84 |
(5.8+6.4+6.7+7.4+7.4+7.3+7.5+7.4+7.1+6.7+6.2+6.2)/12 = 6.84
Below is the Scatter Plot that includes the fitted linear regression equation.
Usage of the Data Analysis Tools in Excel :
|
|
Coefficients |
Standard Error |
t Stat |
P-value |
Lower 95% |
Upper 95% |
|
Intercept |
-61.4859 |
19.9236 |
-3.0861 |
0.0030 |
-101.3001 |
-21.6717 |
|
Year |
0.0340 |
0.0101 |
3.3776 |
0.0013 |
0.0139 |
0.0541 |
You can see from the table above that the fitted linear regression equation is Y = B0 + B1*X, Y being the unemployment rate and X is year. B1 is the regression coefficient of Y on X and Bo is the intercept.
The best fitted linear regression equation is given as:
Unemployment rate = -61.4859 + 0.0340 * Year.
We also have the information from the above table as the p-values for both the coefficients (which is intercept and year) are lesser than 0.05, it is noted that both the regression coefficients are significantly different from zero. Therefore, the best fitted linear regression equation is to be predicted with unemployment rate based on year as:
Unemployment rate = -61.4859 + 0.0340 * Year.
The Y-intercept is B0 = -61.4859.
The equation in slope intercept form is given as:
Unemployment rate = 0.0340* Year – 61.4859
Actual Sufficiency of the Model:
|
ANOVA |
|
|
|
|
|
|
|
df |
SS |
MS |
F |
Significance F |
|
Regression |
1 |
26.4258 |
26.4258 |
11.4079 |
0.0013 |
|
Residual |
63 |
145.9367 |
2.3165 |
|
|
|
Total |
64 |
172.3625 |
|
|
|
Information from the ANOVA table listed above, the significance F value (0.0013) is lesser than the 0.05, and the fitted model is competent to the assumed data. This means the regression coefficients are significantly different from zero.
|
Simple Linear Regression Analysis |
|
|
Regression Statistics |
|
|
Multiple R |
0.3916 |
|
R Square |
0.1533 |
|
Adjusted R Square |
0.1399 |
|
Standard Error |
1.5220 |
|
Observations |
65 |
Looking at the Simple Linear Regression Analysis table above, we see that the R-square value is 0.1533. It specifies that 15.33 % of the variance in unemployment rate is explained by the independent variable year. So the remainder of the variation is possible due to some other independent variables or due to some unplanned reason.
Prediction of the unemployment rate for 2016:
The prediction of the unemployment rate for the year 2016 based on the fitted linear regression line will be calculated as:
Unemployment rate = 0.0340* 2016 – 61.4859 = 7.03.
Residuals are calculated in Residuals sheet of excel file.
The most updated unemployment rate (that is for the year 2013) is
Unemployment rate = 0.0340* 2013 – 61.4859 = 6.93.
The regression coefficient is 0.0340 which is positive; it specifies that every year the unemployment rate increases and/or grows.
Therefore we see that as the unemployment rate increases, it will cause a dearth of consumer spending in retail stores.
Any individual that has been laid off from his or her work temporarily or any individual who has been unemployed for a period of four weeks or more contributes to unemployment rates calculation. For those who do not search for work, they do not contribute to unemployment rate. When the number of people searching for work and not employed at the same time are in high volumes, the retail store owners will experience declined numbers based on less spending budgets, funds and capabilities. As the unemployment rate raises the retail stores will see more consumers willing and able to spend. In 2009 the United States the sales for retail took a spike overall by 37%, (Rogers, 2009). In 2009 is when it took its major peak. Our projections and predictions show that the unemployment rate will stay at or around the 8% mark throughout 2013. This shows a consistency of this rate and can give retail stores a more balance on what to expect and business planning for the budgeted year.
References
Bureau of labor statistics.(n.d.). Retrieved from website: http://www.bls.gov/lau/
Misonzhnik, E. (2011). Building Tension: The pace of retail development remains anemic. Retail Traffic, 40(2), 42-44.
Rogers, D. (2009). RECENT TRENDS IN AMERICAN RETAILING.Retail Digest, 50-53.
Tanner, D., & Youssef – Morgan, C. (2013).Statistics for Managers. San Diego, CA: Bridgepoint Education, Inc.
Scatter Plot that includes
Average Unepmployment Rate
Annualy = 0.034x - 61.486 r² = 0.1533
1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 3.75 6.05 5.2083333333333393 3.2833333333333363 3.0250000000000004 2.9250000000000003 5.5916666666666694 4.3666666666666671 4.1249999999999938 4.3 6.8416666666666694 5.45 5.5416666666666714 6.6916666666666664 5.5666666666666673 5.6416666666666684 5.1583333333333332 4.5083333333333391 3.7916666666666665 3.8416666666666663 3.5583333333333331 3.4916666666666667 4.9833333333333414 5.95 5.6000000000000005 4.8583333333333334 5.6416666666666684 8.4750000000000068 7.6999999999999975 7.0500000000000007 6.0666666666666664 5.8500000000000005 7.1750000000000007 7.6166666666666671 9.7083333333333215 9.6 7.5083333333333391 7.1916666666666664 7 6.1750000000000007 5.4916666666666707 5.2583333333333382 5.6166666666666663 6.8499999999999988 7.4916666666666716 6.9083333333333394 6.1000000000000005 5.5916666666666694 5.4083333333333412 4.9416666666666709 4.5 4.2166666666666694 3.9666666666666663 4.7416666666666707 5.7833333333333412 5.9916666666666716 5.5416666666666714 5.0833333333333393 4.6083333333333334 4.6166666666666671 5.8 9.2833333333333332 9.625 8.9250000000000025 8.0750000000000028The Year
Average Unemployment Rate
