Excel forecast/trend

[h0b0]

I know this should be possible but I have not worked it out yet. I have a challenge to forecast use based on 2021 numbers. The challenge is that not all months are populated and the data is not formatted in a way that I have been able to copy down.

Here is a very simplified version



In the example, how would I create a forecast for 2022? (Ideally, this would be all 2022, but I will take anything.) I have tried adding 12 rows after each product but due to some products not having the same number of months in 2021 I cant replicate down. I have created new columns for 2022 data, but again I am thwarted by the inconsistency of the months. There are 10k rows to apply the solution to so I can't do this manually.

I have been coming back to this over the last 2 weeks so have not really had any downtime during work to spend on PHs. Who every solves this will be helping my post count...

[Doofus]

Not sure I understand the question, but can you create 12 rows for 2021, and only populate the relevant months for each product?

Then you can create a other 12 rows for 2022 and do whatever you need to do.

[Jenny Tailor]

h0b0 said:

I know this should be possible but I have not worked it out yet. I have a challenge to forecast use based on 2021 numbers. The challenge is that not all months are populated and the data is not formatted in a way that I have been able to copy down.

Here is a very simplified version



In the example, how would I create a forecast for 2022? (Ideally, this would be all 2022, but I will take anything.) I have tried adding 12 rows after each product but due to some products not having the same number of months in 2021 I cant replicate down. I have created new columns for 2022 data, but again I am thwarted by the inconsistency of the months. There are 10k rows to apply the solution to so I can't do this manually.

I have been coming back to this over the last 2 weeks so have not really had any downtime during work to spend on PHs. Who every solves this will be helping my post count...

How many products are you forecasting?
10,000 indeterminate lines of numbers means nothing.

Is there any weighting to the more recent forecasts than those from 1870's?

Is it seasonal?
What forecast algorithm?
You need to provide a little bit more information.

Right now - you have said - "I have some string - how long should it be?"

[h0b0]

Jenny Tailor said:

How many products are you forecasting?
10,000 indeterminate lines of numbers means nothing.

Is there any weighting to the more recent forecasts than those from 1870's?

Is it seasonal?
What forecast algorithm?
You need to provide a little bit more information.

Right now - you have said - "I have some string - how long should it be?"

There are approximately 25 products being purchased by 100 customers. I could have a row for each product, for each month for each customer. But, not all customers buy all products and not all customers buy the products they do every month.

The data I am basing the forecast on is 2021, Jan -NOV. This is only directional information because it doesn't take into account market events so I plan on playing with the algorithm once I have got to the point I can get any forecast.


The update is that I think I have been able to reformat the data by putting it into a pivot table and then bringing it back out. I managed to get one product, for one customer to forecast. Excel then crashed and told me my file was corrupt but it will repair. Got back to where I was and copied the forecast formulas down. and it crashed again. When I was able to repoen I had a bunch of div/0s where I shouldn't. Assuming it was due to having blank cells I foolishly did a replace blank with 0 and applied it to 12 columns. That was 8 hours ago and it is still working on it. I have not been able to cancel the request, or even close the spreadsheet. As it happens, I will not be using this PC tomorrow so will let it run and see what happens.

I will report back in the morning. Thank you for the input.

[h0b0]

Doofus said:

Not sure I understand the question, but can you create 12 rows for 2021, and only populate the relevant months for each product?

Then you can create a other 12 rows for 2022 and do whatever you need to do.

Thank you for your response. It was this that made me rethink the data format and use pivot tables.

[Jenny Tailor]

h0b0 said:

Jenny Tailor said:

How many products are you forecasting?
10,000 indeterminate lines of numbers means nothing.

Is there any weighting to the more recent forecasts than those from 1870's?

Is it seasonal?
What forecast algorithm?
You need to provide a little bit more information.

Right now - you have said - "I have some string - how long should it be?"

There are approximately 25 products being purchased by 100 customers. I could have a row for each product, for each month for each customer. But, not all customers buy all products and not all customers buy the products they do every month.

The data I am basing the forecast on is 2021, Jan -NOV. This is only directional information because it doesn't take into account market events so I plan on playing with the algorithm once I have got to the point I can get any forecast.


The update is that I think I have been able to reformat the data by putting it into a pivot table and then bringing it back out. I managed to get one product, for one customer to forecast. Excel then crashed and told me my file was corrupt but it will repair. Got back to where I was and copied the forecast formulas down. and it crashed again. When I was able to repoen I had a bunch of div/0s where I shouldn't. Assuming it was due to having blank cells I foolishly did a replace blank with 0 and applied it to 12 columns. That was 8 hours ago and it is still working on it. I have not been able to cancel the request, or even close the spreadsheet. As it happens, I will not be using this PC tomorrow so will let it run and see what happens.

I will report back in the morning. Thank you for the input.

Something is wrong.
It should only take a couple of seconds to calculate this.

Think about if you can get a good version - saving it as a CSV file. And starting again.

[h0b0]

I ended up stopping the process and starting again. But, I was able to make the forecast as I had planned. Still working on the formatting but I’m happy with the result.

Thank you for some of the prods that made me think differently.