Case
Introduction
The ABC Furniture Company believes that there is a relationship between the number of customers that visit its stores and its monthly sales. To test this theory, the company has collected its customer traffic data for year 1 and its monthly sales for the same year. Using the customers’ traffic data, a consultant working for the Excellent Consulting Group matched the client’s traffic data with the monthly sales, created a linear regression equation in Excel program and generated a scatter plot of the same. The linear regression equation that was generated from year 1’s data was utilized to forecast the sales for year 2. The forecast sales were compared to the actual sales, and the variance between the two-data determined. It was established that the variance between the two data was not significant (Chase, 2013). Consequently, it was determined that ABC Furniture Company might use its customer traffic data to forecast future sales. The first part of the case starts with the logic and rationale used to develop the linear regression equation and chart. The second part of the case, on the other hand, provides the results and the recommendations on how the company might use the linear regression equation to forecast future sales.
Logic and Rationale Used to Develop LR Equation and Chart
LR equation
To develop the linear regression equation, the data for the first year was used as it follows. First, for analysis purpose, the number of customers for each year was identified using letter x whereas the sales were identified using letter y. The sales were treated as the dependent variable because they depended on the number of customers that visited the company. Second, the number of customers for each month was multiplied by the sales for each month and the total for the twelve months calculated. Furthermore, the number of clients for each month was squared, and total calculated for the twelve months. The same case was applied to the sales (Camm et al., 2017). Third, the mean sales for the twelve months were calculated as well as the mean number of customers that visited the company. After completing the calculations, the following formula was utilized to obtain the linear regression equation.
= b0 + b1X (Camm et al., 2017)
Where the values for b0 and b1 were obtained using the following formulas
b0 =
b1 = (Kazmier, 2004)
Chart
As for the chart, the number of customers and sales were highlighted, the Insert menu on the Excel program selected and the Scatter plot on the menu selected and clicked. Upon doing this, the scatter plot was developed. To add titles to the horizontal and vertical axes, the scatter plot was clicked to provide the tools menus. From the Layout menu that appeared, the axis title menu was utilized to add titles to the axes. After the exercise of adding titles to the axes was over, the trendline was added to the plot. The process of adding the trendline involved clicking on the scatter plot to open the Tools menu, clicking on the Layout menu that appeared and clicking the trendline on the Layout’s menu (Camm et al., 2017). From the trendline drop down menu, the More Trendline Options at the bottom of the menu was selected to give more options. Doing this gave the Format Trendline option window. From this window, the trendline option was selected as linear, the trendline name was selected as automatic, and the forecast was selected as display equation on chart. Doing this and closing the window produced the trendline that was utilized to forecast the sales for the second year.
Results
The following were the results that were obtained from the above exercise. The value of b0 was found to be 111.65 whereas the value of b1 was found to be 0.648. The two values generated the following equation.
Y = 0.648X + 111.65
The equation was utilized to predict the sales for the second year. The following were the differences between the forecast sales versus actual sales.
| Year 2 | Actual | Forecast | Variance |
| January | 265 | 251 | 14 |
| February | 388 | 279 | 108.5 |
| March | 298 | 322 | -24.2 |
| April | 260 | 341 | -81 |
| May | 263 | 279 | -15.8 |
| June | 402 | 241 | 161.4 |
| July | 320 | 276 | 43.8 |
| August | 310 | 305 | 4.6 |
| September | 307 | 283 | 24.3 |
| October | 302 | 240 | 62.1 |
| November | 225 | 256 | -31.1 |
| December | 361 | 281 | 80.2 |
| Total | 308.42 | 279.53 | 28.89 |
As shown in the table, the variance of the projected figures had a variance of 28.89 from the actual values. This showed that the forecasted values were not significantly different from the actual ones. As a result, the company can utilize the equation to forecast its future sales.
Recommendations
Based on the above analysis, it appears that there is a relationship between the number of customers that visit the company’s store in any given month and the total sales for that month. About this fact, the ABC Furniture Company might use the linear regression equation to forecast its future sales in the following manner.
- First, on a monthly basis, the company should collect the data for the customers that visit its store in the previous month. After collecting this data, the company should feed that data into this equation Y = 0.648X + 111.65 to determine the possible sales. The Y would be the possible sales whereas X would be the number of customers that would have visited the store in the previous month (Ord & Fildes, 2013).
- Second, to determine the possible annual sales, the company should collect the total number of customers that would visit its store in the previous year and feed that data into the same equation. This would involve adding the number of customers that would have visited its store for the past twelve months and feeding that data into the equation as X. The difference between this case and the first one would be that whereas this case would focus its attention on yearly data, the first case would focus its attention on monthly data. The difference between the two cases if any would not be huge because the same data and equation would be utilized though at different levels (Chase, 2013).
Regarding reliability, the linear regression equation would be reliable because the variance between the forecast sales and actual sales is not significantly different from each other. In actual sense, although there is a slight difference between the two, the difference is only 28.89. This indicates that although the forecast sales would not be the actual sales, it would provide an overview of the sales that the company would expect from the customers that would visit its store (Ord, & Fildes, 2013). Therefore, the company might utilize the above linear regression equation to forecast its future sales.
References
Camm, J. et al. (2017). Essentials of business analytics. Boston, MA: Cengage Learning.
Chase, C. (2013). Demand-driven forecasting: A structured approach to forecasting. Hoboken, New Jersey: Wiley.
Kazmier, L. (2004). Schaum’s outline: business statistic. New York: McGraw-Hill.
Ord, K. & Fildes, R. (2013). Principles of business forecasting. South-Western: Cengage Learning.