MIS Hands on Project: Sales Trend Analysis and Forecasting

The term trend implies a change over time. One type of forecasting is quantitative, and involves analyzing time-series data, and then predicting what the future might be. For example, sales at an ice cream stand at the town park in June of each of the last five years has been good, but in July it was about 20% more than in June. If this year, the stand took in $10,000 in June (a new record), how much would you predict it will take  in July? Well, if we were correct in our assumption based on the historical data, we'd estimate the July figure would be 20% higher, or $12,000.

Microsoft Excel offers some built-in tools for forecasting. One of these allows you to add a trendline to existing data points on a chart. This allows the user to interpolate (i.e., to find a data point between existing points) or to extrapolate (i.e., to find a data point past either end of the current data, either by forecasting foreword, or backcasting to an earlier period). Besides this, MS Excel provides built-in forecast function to predict future. Given the following historical data of a company, MS Excel is used to identify trend and to make prediction of upcoming five years data. Based on this prediction management can make better decisions to develop plans for future.

In the above table value of last five rows are predicted by using MS. Excels FORECAST function. The FORECAST(x, known_y's,known_x's) function returns the predicted value of the dependent variable (represented in the data by known_y's) for the specific value, x, of the independent variable (represented in the data by known_x's) by using a best fit (least squares) linear regression to predict y values from x values. The parameter x must have a numeric value, known_y's and known_x's must be arrays or cell ranges that contain equal numbers of numeric data values. If we plot graph for above data and add trend lines, it looks like below:

XYZ Companies Sales History, Trend Analysis and Prediction of Future Sales

To plot this graph we can flow following steps

® Select the historical data

® Click insert menu and select proper scatter from it

® Select one of the data point representing value of items sold

® Right click on it and select add trendline option in popup menu

® Select proper trendline options. In above graph forecast is set to 5 forward periods

® Repeat previous step for all scatters in the plot