USA: +1(669)289-8683 
Sign in
Predictive Sales Report
A retail store has recently hired you as a consultant to advise 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.
You will complete this project in two parts, but will submit your work as one Word document. Copy and paste your calculations from your Excel workbook into the Word document.
TIP: For help copying and pasting information from Excel to Word go to
http://office.microsoft.com/en-us/word-help/copy-excel-data-or-charts-to-word-HP010198874.aspx
or watch the “Excel Tips – Tip#48: Copy from Excel to Word” found in Week One Recommended Resources.
The Final Project must be eight to ten pages in length, excluding title page and reference page(s) and must include at least three scholarly sources, in addition to the Job and Labor Statistics site. Be sure to format your work in accordance with APA guidelines as outlined in the Ashford Writing Center.
Part I
Reference the data in this
Excel Workbook
to complete the following quantitative components of the predictive sales report. You will complete the calculations below in your own Excel workbook and then copy and paste from your Excel workbook into the Word document.
- Calculate the mean yearly value using the average unemployment rate by month found in the “Final Project Data Set.”
- Using the years as your x-axis and the annual mean as your y-axis, create a scatter plot and a linear regression line.
- Answer the following questions using your scatter plot and linear regression line:
- Compute the slope of the linear regression line.
- Identify the Y-intercept of the linear regression line.
- Identify the equation of the linear regression line in slope-intercept form.
- Calculate the unemployment rate in 2016, based on the linear regression line.
- Calculate the residuals of each year. Find the latest unemployment rate in your state. You will need to go to the Bureau of Labor Statistics Website (www.bls.gov)and hover over “Subject Areas” in the top menu panel then select “State and Local Unemployment Rates” from the drop down menu under “Unemployment Rate”. Determine whether the rate in your state is within the range of the linear regression line or if it is an outlier.
- Interpret your results of the model and explain how a company could use the results to drive decision making.
PART II
Next interpret the analysis from Part I to complete the following qualitative components of the predictive sales report:
- Introduce the project and its significance to the retail store.
- Reference the statistical analysis that you completed in Part I and explain where the data came from, what type of analysis was done, what the findings were, and whether or not you believe the data to be accurate.
- Explain your data-driven conclusions regarding the effects of the changing unemployment rate on the retail store.
- Predict what could occur in the future that would change your linear regression line and therefore your prediction of sales.
PREDICTIVE
9
Name
Predictive Sales Report
BUS 308: Statistics for Managers
Instructor
Date
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
It is viewable that the last column in the above table is the average yearly value when using the average unemployment rate by months.
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
What is noted from the above table, the best fitted linear regression equation is Y = B0 + B1*X, meaning Y is the unemployment rate and X is year. B0 is the intercept and B1 is the regression coefficient of Y on X.
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, 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 unplannedreason.
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.
Unemployment rates include anyone who has not had a job for four weeks or more. This number also includes any person that is laid off on temporary basis. There are some people that just stop looking for employment; those individuals are not included in the unemployment rate. When there are people are in high volume looking for work and not working at all the retail stores will suffer with declining 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.
In 2011 article developers felt that putting up new centers as long as the unemployment rates where in the high single digits would not be good (Misonzhnik,2011). If developers do not want to put up new shops this can hinder growth of new retailers and also introduction of new vendors.This also serves as a plus for existing retailers who do not have to worry about competitors and can have all business located in one shop.
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
Annual
y = 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.0750000000000028
The Year
Average Unemployment Rate
