Predicting This Year Sales with Limited Information

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!

Alteryx Example.xlsx

Predictive Analysis

Accepted answers

danilang

@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

All comments

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

@jessfarthing

[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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703