Excel forecast

[Mr2017]

Hi

Has anyone used predictive forecasting in Excel, before?

If so, your thoughts would he appreciated here:

I need to answer two questions using the data below, which apparently has a predictable pattern that should be used to generate forecasts for the different food types.

i) which food type will sell the most in Q1 of 2015? and

ii) which food type will sell the most in the whole of 2015?

Has anyone done something like this before?

TIA

Food type

2012 Q1

2012 Q2

2012 Q3

2012 Q4

2013 Q1

2013 Q2

2013 Q3

2013 Q4

2014 Q1

2014 Q2

2014 Q3

2014 Q4

2015 Q1

2015 Q2

2015 Q3

2015 Q4

Chocolate

21,000​

32,000​

19,000​

21,000​

26,000​

13,000​

6,000​

3,000​

1,000​

1,000​

1,000​

1,000​

Crisps

29,000​

44,000​

27,000​

30,000​

36,000​

18,000​

9,000​

5,000​

3,000​

Peas

43,000​

65,000​

39,000​

43,000​

53,000​

26,000​

13,000​

Pizza

58,000​

87,000​

52,000​

57,000​

69,000​

China

37,000​

55,000​

33,000​

36,000​

UK

28,000​

42,000​

25,000​

Japan

22,000​

33,000​

Brazil

21,000​

Total

21,000​

32,000​

19,000​

50,000​

70,000​

83,000​

101,000​

136,000​

186,000​

198,000​

186,000​

201,000​

​

​

​

​

​

​

​

​

​

​

​

​

 

[offthelip]

The best predictive methods use some sort of “modelling” of the system that you are trying to predict. This usually means having some understanding of how one data measurement interacts with any or all of the other data measurements. Your list seems to have got 4 types of food and 4 different countries all in the same list. We all know that countries are not the same as food types. So even with the data you have given us we can make no assumptions about how the different foods/countries interact. Also there are very few data points ( Brazil has only got one value) so making any predictions so it is impossible to make any prediction about Brazil.

It is possible that you might know about the business that you are analysing such that you know certain foods are seasonal, and some aren’t in which case you can add a seasonal factor into some foods and not others. You might know that some foods are correlated e.g. more pizzas means more peas, or anti correlated e.g. more chocolate means less crisps. All of the factors can be put into a model and the gain factor for each interactions can estimated from the previous data

A good way to start trying to see if any of the methods you are thinking of using is to divide your existing data into two successive sets of data. You can then try to develop your “model” on the first set of data, then when you think you have model that works on that set of data try it on the second set of data and see if it works on this entirely new set of data.

However with the information you have given us I think all you can do use the EXCEL LINEST function individually for each line of data and find out where that estimates the next point to be for that row.

Obviously this won’t work for Brazil with one point!!

 

[Mr2017]

Hi Offtheflip

Thanks for your detailed response.

Apologies - all the data in the food type should be food, rather than a mix of countries and food.

I've pasted a revised version of the table below.

The numerical data apparently forms a predictable pattern.

I tried to use the LINEST function, but it gave me negative numbers for 6 out of 8 rows 2015 Q1. But I don't think the pattern is supposed to generate negative numbers.

The formula I used was this (in cell P5) =LINEST(D5:O5,$D$3:$O$3,,TRUE) and the data below started in cell C4 (the 'Food type' cell). I also gave each quater a number, so 2012 Q1 was 1, 2012 Q2 was 2, all the way up to 2015 Q4, which was 16.

I'd like to edit the data in the original question, but there doesn't seem to be an option to do so?

If you have any other thoughts, they'd be greatly appreaciated!

Thanks in advance!

Food type

2012 Q1

2012 Q2

2012 Q3

2012 Q4

2013 Q1

2013 Q2

2013 Q3

2013 Q4

2014 Q1

2014 Q2

2014 Q3

2014 Q4

2015 Q1

2015 Q2

2015 Q3

2015 Q4

Chocolate

21,000​

32,000​

19,000​

21,000​

26,000​

13,000​

6,000​

3,000​

1,000​

1,000​

1,000​

1,000​

Crisps

29,000​

44,000​

27,000​

30,000​

36,000​

18,000​

9,000​

5,000​

3,000​

Peas

43,000​

65,000​

39,000​

43,000​

53,000​

26,000​

13,000​

Pizza

58,000​

87,000​

52,000​

57,000​

69,000​

Apples

37,000​

55,000​

33,000​

36,000​

Grapes

28,000​

42,000​

25,000​

Bananas

22,000​

33,000​

Flour

21,000​

Total

21,000​

32,000​

19,000​

50,000​

70,000​

83,000​

101,000​

136,000​

186,000​

198,000​

186,000​

201,000​

 

[offthelip]

The numerical data apparently forms a predictable pattern.
Somebody obviously knows something about this data set , so you should be asking them what is the pattern that is predictable and then try to build an equation that models their prediction. As I hinted is it seasonal, is it combinations of foods , what is the supposed predictable pattern? And most important does pattern make any sense or could it be random. e.g. foods with a P in the name increase in the summer is unlikely to be a winner. it much more likely to be governed by a couple of foods with a P in the name increasing in the summer and has got nothing to do with the letter P

 

[offthelip]

Just looking at the data it would appear that every time you introduce a new food you get a peak with that food in the second quarter after the introduction, so that would imply that Flour might sell best in Q1 2015, although pizza is quite competitive, but this is just a visual impression and so is not an analysis. If you have a lot more data you could try that as a hypothesis

 

[Mr2017]

Thanks for your comments.

If I knew the person that had the answer, then I wouldn't be posting the question on this site.

The food / country data is actually irrelevant in the question. But the pattern is in the number data, like a number series in Maths. So I wanted to see if anyone else had used a formula or function to workout patterns and create forecasts from those patterns.

Given that I can't edit the question, I'll repost it with some generic data.

But thank you for your thoughts.