Predicting This Year Sales with Limited Information

[jessfarthing]

Hi,

I'm looking for a way to predict how FY20 Sales Per Client will compare against the total FY19 sales to the same client with a limited amount of information. 

Assume this is based on a fiscal year of 1 July - 30 June.   So FY19 total sales would be represented as sales between 1 July 2018 and 30 June 2019, whilst FY20 would be 1 July 2019 - 30 June 2020. 

Using only fields of Client Name, FY19 Total Sales, and FY 20 Year to Date Sales (As of Nov 2019), does anyone know if it's possible and what tools would be needed? Some of the predicting time series tools I've looked into seem to be dependent on more variables.  I've uploaded a sample file if that helps.

Thanks!

[fmvizcaino]

Hi @jessfarthing ,

There is not enough data to predict anything there. The only possible thing that comes to my mind is to use the rule of 3 to fill what is left for 2020.

To predict something related to the clients, one possibility is to have the sales per week, that way you would be able to use time series methods, such as ETS or ARIMA.

Best,

Fernando Vizcaino

[RolandSchubert]

Hi @jessfarthing ,

iI think, there is no way to predict FY20 sales based on that data. You would need at least data for FY19 YTD November to create a very simple extrapolation (I would not call it a prediction) - you could use a formula like FY20 Estimation = FY19 Total * FY20 YTD / FY19 YTD assuming that the cumulative deviation also applies for the rest of the year.

To create a real prediction model, you'll need at least monthly values for the previous and current year (more periods would be useful) - if that data is available (should be),  you could use ETS or ARIMA to create a forecast based on previous periods (both tools use historic values as a foundation to predict future values).

For a more sophisticated model (depends on business, if that's an appropriate method) you'll need additional information (again depending on the business), basically the factors influencing sales (general economic conditions, development of population, weather, ...). If that's available, a regression model may be a solution.

The basic question seems to be, if you can obtain any additional data. What do you think?

Best,

Roland 

[danilang · Accepted solution]

@jessfarthing 

Like those clever people, @RolandSchubert and @fmvizcaino mentioned, you can't predict anything from the limited data you have.  After all, you can draw infinitely many trend lines through your single 2020 data point.  This doesn't mean that you can't compare with last year's data.  

[FY20 Year to Date Sales(as of November 2019)] /([FY 19 total sales]/4)*100

Gives you a comparison of Nov 2020 sales compared to Nov 2019 (assuming measured at Nov 01st and there's no seasonality in the sales). 

 

Showing that all clients are over last year's sales except 5 and 6, so these sales reps need to step up their game

Dan   

[jessfarthing]

Thanks everyone. I had assumed the information was limited but didn't know if Alteryx community would pull out a useful trick!  Appreciate your timely responses.