CODEWITHSUNDEEP
exceltime-seriesregressionpredict
Mindy
Mindy

Reputation: 19

Excel predicting future value

I have a large excel file that has monthly sales per customer for January - December 2016. I want to predict what their sales will be in January 2017.

Upvotes: 0

Views: 360

Answers (4)

Matthew Kloss
Matthew Kloss

Reputation: 1

Excel has a forecasting function that might help:

FORECAST.ETS function

Calculates or predicts a future value based on existing (historical) values by using the AAA version of the Exponential Smoothing (ETS) algorithm. The predicted value is a continuation of the historical values in the specified target date, which should be a continuation of the timeline. You can use this function to predict future sales, inventory requirements, or consumer trends.

This function requires the timeline to be organized with a constant step between the different points. For example, that could be a monthly timeline with values on the 1st of every month, a yearly timeline, or a timeline of numerical indices. For this type of timeline, it’s very useful to aggregate raw detailed data before you apply the forecast, which produces more accurate forecast results as well.

Syntax

FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])

And you can see it in action in a workbook from the FORECAST.ETS.SEASONALITY page:

Download a sample workbook

Upvotes: 0

dmb
dmb

Reputation: 567

The "AverageIf" solution may be sufficient; however, you may be able to do better if there is in fact any trends/seasonality in the data that could be used to modeling advantage. For each customer, I would check for autocorrelation in the data. "Autocorrelation, also known as serial correlation, is the correlation of a signal with a delayed copy of itself as a function of delay. Informally, it is the similarity between observations as a function of the time lag between them."(https://en.wikipedia.org/wiki/Autocorrelation) For instance, if there is significant autocorrelation at lag = 12, this would suggest yearly seasonality in the data (maybe every January is similar). There is a nice tutorial to analyze autocorrelation in Excel at:

http://www.real-statistics.com/time-series-analysis/stochastic-processes/autocorrelation-function/

If autocorrelation does exist, it would likely then be useful to perform regression with that time component(s). If there is a trend with time in additional to a cyclical component, that should also be factored into the regression (i.e., such as a "Year" variable); or a more sophisticated time series method could be applied that would accomodate trend and autocorrelation such as an Autoregressive Integrated Moving Average (ARIMA) model: https://en.wikipedia.org/wiki/Autoregressive_integrated_moving_average

Upvotes: 0

DMM
DMM

Reputation: 1112

You have several problems to solve:

  1. Determining (a) candidate forecasting model(s) to use.
  2. Organising your existing data to test whether such model(s) are actually suitable, performing such tests and selecting (a) suitable model(s) [There may be more than one model to be used dependent on whether your data are homogeneous or not.]
  3. Organising your existing data to apply your chosen model(s) for the purposes of making your prediction. (A different organisation to 2. may be required.)

Your description talks about "sales" but the data sample you provided mentions "claims". These are very different entities - sales (dependent on what type of sales) may well be as frequent as monthly, but claims are likely to be a lot less frequent. If this is the case and claims are highly infrequent, then there is little sense in trying to predict an individual customer's claim. In such a case it would make more sense to predict the aggregate level of claims across a group of customers.

With all modelling, and particularly with forecasting models, context is highly important in steering towards which particular types of model are likely to be suitable. As it is, you have provided no context about what your data really represents, so are unlikely (beyond random chance) to find that any solution offered to you is actually going to be suitable. A solution might compute but, in the context in which you are operating, will it provide anything like a sensible or justifiable set of forecasts?

Upvotes: 0

Douille
Douille

Reputation: 68

You could average each client's data and ignore the zeros with a formula like

=AVERAGEIF(D2:D12,"<>0)

D2:D12 would be the range of a single client's sales variable and it would give you a monthly average for that client that you could use for January Predicted Sales.

Upvotes: 1

Related Questions