ABC Furniture Company is a client who has hired the services of the Excellent Consulting Group. The client believes that there is a relationship between the number of customers who visit the store during any given month (“customer traffic”) and the total sales for that same month. The client has observed that the greater the customer traffic, the greater the sales for that month.
To confirm this, the owner of ABC Furniture Company collected customer traffic data over the past 12-month period and monthly sales for that same 12-month period. It is the responsibility of the Excellent Consulting Group to determine if there exists a relationship between the number of customers and the total number of sales. If there is a relationship, then the Excellent Consulting Group should define an equation that describes it. With the equation, then the owner of ABC Furniture Company will be in a position to forecast the sales expected in the following year.
Delegate your assignment to our experts and they will do the rest.
It is beneficial for the owner of ABC Furniture Company to develop a forecast of the expected future sales based on the number of customers received. Excellent Consulting Group has several different methods of making forecasts to choose from. In this case, a linear trend is expected, which makes the linear regression forecasting method the most appropriate. Linear regression forecasting is a time-series method which applies basic statistics to forecast future values of a variable of interest. This method of forecasting assumes that the conditions in the present will still hold in the future. The model uses the line of best fit in the historical performance of the variable and applies this in the future forecasting. It, therefore, does not account for seasonality and non-linearity (Nguyen, 2017).
ABC Furniture Company has already collected the data for the number of customers who visit the stores each month and the corresponding number of sales for a year. Therefore, seeing that this is a time series pattern, the next step is to use this data to identify and fit any present systematic time patterns. Therefore, after tabulating the data on Excel, we will insert a scatter diagram. From the diagram, it is evident that the local mean value is higher at the end than at the beginning of the series. Therefore, this is indeed a time series. Next, we will insert a line of best fit, or the trend line, which is a line that is measured in a vertical direction, and it minimizes the sum of squared deviations ("Linear Regression Forecasting Method by Companies," 2017).
The data used is given below,
Year 1 | Customers | Sales ($000) |
January | 185 | 230 |
February | 241 | 301 |
March | 374 | 310 |
April | 421 | 389 |
May | 425 | 421 |
June | 259 | 300 |
July | 298 | 318 |
August | 321 | 298 |
September | 215 | 202 |
October | 282 | 265 |
November | 235 | 312 |
December | 300 | 298 |
Using Excel to plot the data is easy. Once you have inserted the scatter diagram, right-click on one of the points then select ‘add trendline’ from the menu. Using the trend line options, you can also display the equation of the line and the value of R-squared. For the data collected by ABC Furniture Company, the trend line intercepts the y-axis at 111.65, and the slope is 0.648. The equation of the line from Excel is as below:
The scatter diagram obtained is as below
We can also determine the equation of the trend line manually using the data provided.
The basic formula for this equation is given by
Y is the dependent variable, X is the independent variable, b 1 is the slope of the line, and b 0 is the y-intercept.
When obtaining the linear regression equation, the first step is to determine if a relationship exists between the x and y variables. Determining this is usually a judgment call of the researcher. The researcher should then list the data in an x-y format (i.e. dependent and independent variables).
With these key values, the following equations are used to determine the values of b 1 and b 0.
("Linear trend model for forecasting," 2017).
The formula gives the same equation as the one obtained through the Excel analysis. We use the formula to analyze the expected sales for the following year. The number of customers (x) is already known, and the task is to make a forecast of the expected sales. The total number of customers expected in year 2 is 259.08. However, the actual number of customers that ABC Furniture Company receives in year 2 is 262.52, which is a variance of -16.95.
As stated earlier, the value of the y-intercept is 111.65. This gives the total number of sales that ABC Furniture Company is expected to make even when no customers are received i.e. when the change in GDP is zero. Another major output of interest in the linear regression forecasting method is the value of R-squared, which in this case is 0.718. This value is an indication how well the model forecasts or predicts future sales. In this case, our model is moderately strong. The slope of the graph (b 1 ) in this case is 0.648. It tells us that if the GDP rises by one percent, then the sales are likely to go up by 0.648 units.
From the analysis, it is evident that the linear regression model comes in handy in forecasting for ABC Furniture Company. If the owner of the company performs research which directs that the next GDP change will be by a certain percentage, then that percentage can be plugged into this LR model. It is, therefore, easy to generate a sales forecast. As a result, it will be easy for ABC Furniture Company to develop a more objective budget and plan for the following year.
Clearly, the simple regression model used for the present situation of ABC Furniture Company is effective. However, it is important to note that this model uses economic data and it is prone to multiple trends, non-linearity, and cycles. As a result, this simple linear regression model is not always reliable when it comes to time-series work such as predicting future sales. For this reason, it is more recommendable for ABC Furniture Company to consider using different models such as multiple linear regression and other statistical approaches that use causal models. These approaches are more appropriate than the simple linear regression because of their capability to take into account various affecting factors and also evaluate the effect of each of them.
However, such forecasting methods are more complicated, and they pose several issues that the forecaster should put into consideration. The appropriate use of the linear regression models is dependent on the goals of the forecaster and the type of data being used (Nguyen, 2017).
References
Linear Regression Forecasting Method by Companies . (2017). Smallbusiness.chron.com . Retrieved 2 March 2017, from http://smallbusiness.chron.com/linear-regression-forecasting-method-companies-73112.html
Linear trend model for forecasting . (2017). People.duke.edu . Retrieved 2 March 2017, from http://people.duke.edu/~rnau/411trend.htm
Nguyen, J. (2017). Regression Basics For Business Analysis . Investopedia . Retrieved 2 March 2017, from http://www.investopedia.com/articles/financial-theory/09/regression-analysis-basics-business.asp
