MASOODAHMAD

Board Regular
Joined
Mar 28, 2012
Messages
105
Platform
  1. MacOS
Hello Everyone,

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


Jan.'17Feb.'17Mar.'17Apr.'17May.'17Jun.'17Jul.'17Aug.'17Sep.'17Oct.'17Nov.'17Dec.'17
Data1203134486111321344114290017061552300213581367
Jan.'18Feb.'18Mar.'18Apr.'18May.'18Jun.'18Jul.'18Aug.'18Sep.'18Oct.'18Nov.'18Dec.'18
Data24181133932147313961140

<tbody>
</tbody>
<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 :)
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

MASOODAHMAD

Board Regular
Joined
Mar 28, 2012
Messages
105
Platform
  1. MacOS
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.
 
Upvote 0

MASOODAHMAD

Board Regular
Joined
Mar 28, 2012
Messages
105
Platform
  1. MacOS
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.
 
Upvote 0

MASOODAHMAD

Board Regular
Joined
Mar 28, 2012
Messages
105
Platform
  1. MacOS
Million thanks buddy for your quick response. I'll definitely check these.
 
Upvote 0

MASOODAHMAD

Board Regular
Joined
Mar 28, 2012
Messages
105
Platform
  1. MacOS
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!
 
Upvote 0

MASOODAHMAD

Board Regular
Joined
Mar 28, 2012
Messages
105
Platform
  1. MacOS
Any idea or update on how to calculate Weighted Average here…
 
Upvote 0

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,153
Are you trying to dynamically calculate the average of differences?


Excel 2010
ABCDEFGHIJKLM
1Jan.'17Feb.'17Mar.'17Apr.'17May.'17Jun.'17Jul.'17Aug.'17Sep.'17Oct.'17Nov.'17Dec.'17
2Data1203134486111321344114290017061552300213581367
3
4
5Jan.'18Feb.'18Mar.'18Apr.'18May.'18Jun.'18Jul.'18Aug.'18Sep.'18Oct.'18Nov.'18Dec.'18
6Data24181133932147313961140
7
8
9Start Month4
10# of months3
11
12-130.333
Sheet6
Cell Formulas
RangeFormula
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.
 
Upvote 0

MASOODAHMAD

Board Regular
Joined
Mar 28, 2012
Messages
105
Platform
  1. MacOS
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.
 
Upvote 0

Forum statistics

Threads
1,187,107
Messages
5,961,629
Members
438,556
Latest member
darrylburge

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
Top