Weighted average, similar to sumproduct in excel

Highlighted
Occasional Contributor

Weighted average, similar to sumproduct in excel

Hi all

I have 4 line items vis. Actual demand, D1, D2,D3, Forecast demand. Having monthly time dimension for FY 20.

Need to calculate Forecast demand for each month based on this calculation

Forecast for April 20 = (Actual demand for Jan 20 * D1+Actual demand for Feb 20 * D2 + Actual demand for Mar 20 * D3)/ sum D1 +D2+D3

Basically its a 3 month moving average, considering weights at the same time.

File is attached for your reference

 

Thanks

6 REPLIES 6
Highlighted
Master Anaplanner/Community Boss

Re: Weighted average, similar to sumproduct in excel

Highlighted
Occasional Contributor

Re: Weighted average, similar to sumproduct in excel

Yes, but the question arises how to incorporate those weights in the calculation.

Highlighted
Master Anaplanner/Community Boss

Re: Weighted average, similar to sumproduct in excel

Hi Anup,

Can you please illustrate further, what would be the forecast for May 20 and Jun 20

Regards,
Anirudh
Highlighted
Super Contributor

Re: Weighted average, similar to sumproduct in excel

Hi @anupkumar.gade,

 

You can accomplish in 2 ways

 

Major task is to get the 3rd, 2nd, 1st month data (Actual).

 

Approach 1:

             1. Create a system Module, where you should map current month with past 3rd month (using formula)

refer this line item for calculating weighted average.

    apply the similar logic for getting past 2nd and 1st month data(Actual).

 

Approach 2:

              1. Try movingsum() function,

i will give you an hint, check if it helps.

 

movingsum(actual, -3,-3,sum) - to get 3rd month data

 

apply similar for getting 2nd and 1st month

 

 

 

Thanks,

Sorna Raja Prabhu

Sorna Raja Prabhu
Highlighted
Super Contributor

Re: Weighted average, similar to sumproduct in excel

Hi @anupkumar.gade - See this image, for better understanding.

 

Capture.png

 

Thanks,

Sorna Raja Prabhu

Sorna Raja Prabhu
Highlighted
Occasional Contributor

Re: Weighted average, similar to sumproduct in excel