FORECAST.ETS returns negative values for sales forecast

[Nane]

Hi guys,

I have a question regarding FORECAST.ETS and I hope someone can help - the screenshot is attached.

I have a data set of 24 months of sales history and am trying to forecast the next 4 months of sales for each item.

I tried using FORECAST.ETS function, but it often returns negative values, which is puzzling.

I assume that seasonality is 12 months (one calendar year).

I am aware that I should have at least 30 months of data for 12 months of seasonality, so I'm currently working on obtaining the past data.

In the meanwhile, what is your best guess on why is the formula returning negative values?

Should I maybe use a different function/formula?

Many thanks in advance!

 

[vletm]

Nane
There are challenges to see well Your challenge from a snapshot.
Could You send a sample Excel-file with sample data?
Have You checked FORECAST.ETS-function's syntax?
Have You solved those values manually to verify - should those be always positive?

 

[Nane]

@vletm
Thanks for your thoughtful questions.

I uploaded the file with sample data.

I checked the syntax and I can't find any mistakes.

As for the third question, I lack the know-how to solve it manually.

My assumption is if there isn't a single negative sales value in the dataset with historical data, it would imply that the forecast shouldn't be negative either.

 

[Nane]

Nane
There are challenges to see well Your challenge from a snapshot.
Could You send a sample Excel-file with sample data?
Have You checked FORECAST.ETS-function's syntax?
Have You solved those values manually to verify - should those be always positive?

I forgot to ask - which formula would you use if you were in my shoes?

 

[Hui]

Your Data - Series 1 only


If you look at H4 to H7 and then the corresponding periods 12 months later you will see there is a strong negative trend
So going forward 12 months, i'd be expecting a large negative as well

I suspect that you don't have enough data or change the period away from 12 months

 

[vletm]

Nane
Something same thoughts as above ...
eg Series 1 ... Left side Your data with linear trendline / '12' average trendline and right side forecasted results with trendline.
With positive data can get negative forecasting ... or how? No need to guess - that could see too.
Manually solving - some where should find - how this has calculated, but by using very basic values could see same too.

I added to file for testing ... check cell AM1 = 'months' and AM3 = aggreration then You could see - how those works?
You'll need more data to get forecasting ... but, still that would be forecasting ( something same with the Weather ).

 

[Nane]

Your Data - Series 1 only
View attachment 78091

If you look at H4 to H7 and then the corresponding periods 12 months later you will see there is a strong negative trend
So going forward 12 months, i'd be expecting a large negative as well

I suspect that you don't have enough data or change the period away from 12 months

To which period would you change it?