Excel forecast

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
644
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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 type2012 Q12012 Q22012 Q32012 Q42013 Q12013 Q22013 Q32013 Q42014 Q12014 Q22014 Q32014 Q42015 Q12015 Q22015 Q32015 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​

 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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!!
 
Upvote 0
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 type2012 Q12012 Q22012 Q32012 Q42013 Q12013 Q22013 Q32013 Q42014 Q12014 Q22014 Q32014 Q42015 Q12015 Q22015 Q32015 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​
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top