Data Forecast

[MASOODAHMAD]

Hello Everyone,

I have a scenario wherein I need to know the forecast based on the previous year data.


[TABLE="width: 845"]
<tbody>[TR]
[TD="class: xl63, width: 87"][/TD]
[TD="class: xl64, width: 87, align: right"]Jan.'17[/TD]
[TD="class: xl64, width: 87, align: right"]Feb.'17[/TD]
[TD="class: xl64, width: 87, align: right"]Mar.'17[/TD]
[TD="class: xl64, width: 87, align: right"]Apr.'17[/TD]
[TD="class: xl64, width: 87, align: right"]May.'17[/TD]
[TD="class: xl64, width: 87, align: right"]Jun.'17[/TD]
[TD="class: xl64, width: 87, align: right"]Jul.'17[/TD]
[TD="class: xl64, width: 87, align: right"]Aug.'17[/TD]
[TD="class: xl64, width: 87, align: right"]Sep.'17[/TD]
[TD="class: xl64, width: 87, align: right"]Oct.'17[/TD]
[TD="class: xl64, width: 87, align: right"]Nov.'17[/TD]
[TD="class: xl64, width: 87, align: right"]Dec.'17[/TD]
[/TR]
[TR]
[TD="class: xl63"]Data[/TD]
[TD="class: xl65, align: right"]1203[/TD]
[TD="class: xl65, align: right"]1344[/TD]
[TD="class: xl65, align: right"]861[/TD]
[TD="class: xl65, align: right"]1132[/TD]
[TD="class: xl65, align: right"]1344[/TD]
[TD="class: xl65, align: right"]1142[/TD]
[TD="class: xl65, align: right"]900[/TD]
[TD="class: xl65, align: right"]1706[/TD]
[TD="class: xl65, align: right"]1552[/TD]
[TD="class: xl65, align: right"]3002[/TD]
[TD="class: xl65, align: right"]1358[/TD]
[TD="class: xl65, align: right"]1367[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl64, align: right"]Jan.'18[/TD]
[TD="class: xl64, align: right"]Feb.'18[/TD]
[TD="class: xl64, align: right"]Mar.'18[/TD]
[TD="class: xl64, align: right"]Apr.'18[/TD]
[TD="class: xl64, align: right"]May.'18[/TD]
[TD="class: xl64, align: right"]Jun.'18[/TD]
[TD="class: xl64, align: right"]Jul.'18[/TD]
[TD="class: xl64, align: right"]Aug.'18[/TD]
[TD="class: xl64, align: right"]Sep.'18[/TD]
[TD="class: xl64, align: right"]Oct.'18[/TD]
[TD="class: xl64, align: right"]Nov.'18[/TD]
[TD="class: xl64, align: right"]Dec.'18[/TD]
[/TR]
[TR]
[TD="class: xl63"]Data[/TD]
[TD="class: xl65, align: right"]2418[/TD]
[TD="class: xl65, align: right"]1133[/TD]
[TD="class: xl65, align: right"]932[/TD]
[TD="class: xl65, align: right"]1473[/TD]
[TD="class: xl65, align: right"]1396[/TD]
[TD="class: xl65, align: right"]1140[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { border: 0.5pt solid windowtext; background: rgb(217, 217, 217) none repeat scroll 0% 0%; }.xl64 { border: 0.5pt solid windowtext; background: rgb(217, 217, 217) none repeat scroll 0% 0%; }.xl65 { border: 0.5pt solid windowtext; }</style>

In the above example, I have the data for the year January 2017 till June 2018. Using this data, what will be the most easiest and more reliable way to get the forecast from July 2018 to December 2018.

Any ideas

 

[MASOODAHMAD]

What I did is:

1) Subtracted the value of the year 2018 from value of year 2017 for the second quarter i.e. April, May and June.
2) Calculated the average of the resulted values.
3) Subtracted the calculated average value from Jul.'17
4) Subtracted the calculated average value from Aug.'17
5) Subtracted the calculated average value from Sep.'17
and so on...

Example:
1) Apr.'17-Apr.'18 i.e. 1132-1473 = -341
May.'17-May.'18 i.e. 1344-1396 = -52
Jun.'17-Jun.'18 i.e. 1142-1140 = 2

2) Average of (-341,-52,2) => (-130)

3) July'18 => 900-(-130) = 1030

4) Aug'18 => 1706-(-130) = 1836

5) Sept'18 => 1552-(-130) = 1682

I'm not satisfied with this method. Can someone help me find a better way of calculating Forecast data. Any other formula… any other trick/method.

Thanking you in anticipation.

 

[MASOODAHMAD]

Is there anyone with a solution or at least let me know if my method is okay.

Looking desperately for your valuable inputs.

Thanks a lot in advance.

 

[sheetspread]

See if any of these help:

https://www.makeuseof.com/tag/excel-forecast-function/

https://corporatefinanceinstitute.com/resources/excel/functions/forecast-trend-extrapolate-excel/

https://exceloffthegrid.com/using-the-forecast-function-with-seasonality/

https://www.flisser.com/how-to-do-forecasting-with-excel-2016/

 

[MASOODAHMAD]

Million thanks buddy for your quick response. I'll definitely check these.

 

[MASOODAHMAD]

Hi Sheetspread,

I finally came up with the Forecast formula, but my Manager is not happy with it. So ultimately, I have to live with my first formula.

Can anyone tell me how to calculate the Weighted Average, with the given scenario

Keeping the previous data, for 2017 and 2018, like this:
April17: 1132
May17: 1344
June17: 1142

April18: 1473
May18: 1396
June: 1140

…If I calculate the Average of their differences (2017-2018), like:

=Average((1132-1473), (1344-1396), (1142-1140))

…it comes out to: (-130).

So, instead of calculating simple Average, I would like to calculate Weighted Average to be more accurate.

Can anyone please shed some inputs on this.

Thanks in advance!

 

[MASOODAHMAD]

Any idea or update on how to calculate Weighted Average here…

 

[sheetspread]

Are you trying to dynamically calculate the average of differences?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Jan.'17	Feb.'17	Mar.'17	Apr.'17	May.'17	Jun.'17	Jul.'17	Aug.'17	Sep.'17	Oct.'17	Nov.'17	Dec.'17
2	Data	1203	1344	861	1132	1344	1142	900	1706	1552	3002	1358	1367
3
4
5		Jan.'18	Feb.'18	Mar.'18	Apr.'18	May.'18	Jun.'18	Jul.'18	Aug.'18	Sep.'18	Oct.'18	Nov.'18	Dec.'18
6	Data	2418	1133	932	1473	1396	1140
7
8
9	Start Month	4
10	# of months	3
11
12		-130.333
Sheet6

Cell Formulas
Range	Formula
B12	{=AVERAGE(OFFSET(INDEX($B$2:$M$2,B9),,,,B10)-OFFSET(INDEX($B$6:$M$6,B9),,,,B10))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MASOODAHMAD]

Once again thanks sheetspread for your inputs.

I have already calculated the Average and my answer exactly matches with yours which is (-130).

However, there's a formula to calculate Weighted Average, which is more accurate than simple average formula. To my knowledge I understand that (-130) is not the correct answer, therefore, to be more precise, we need to calculate Weighted Average here.

Please refer to the this link:
https://support.microsoft.com/en-in/help/214049/how-to-calculate-weighted-averages-in-excel

I tried to use the approach but didn't get the right answer. So I'm looking for some Expert opinions.

 

[sheetspread]

Do you have Excel 2016? If so this might help:

https://support.office.com/en-us/ar...5e5-bfdc-60a7062329fd?ui=en-US&rs=en-US&ad=US