Adjust the sales objective values for future months in combo chart

[Haarika]

Hi, 

I need to show a comparion of Sales and sales objectives for the current year. I am using a combo chart for this purpose, in which Sales is a bar and sales objectives is a line. Point to note is that, sales objectives are set for the full year and sales are updated on a daily basis.

The critical requirement here is to calculate YTD difference of the actual sales and sales objectives for a given month and divide the difference with the number of future months remaining. Then add the final value obtained to the sales objectives of the future months. 

Therefore, I am seeking help on how to frame the set analysis for the sales objectives to automatically get updated with each passing month.

I have attached an excel calculation for your reference. 

@sunny_talwar , @Kushal_Chawda   could you please provide your expert advice.

Many thanks in advance !

Haarika 

[Haarika]

Hi @Kushal_Chawda , apologize for the confusion caused

point a) SO for the completed months should be equal to the corresponding month Sales

b) the sum of revised SO should always be equal to sum of the original SO (refer to row 14 in the excel)

hope this clarifies. thank you !

[Haarika]

Hello @Kushal_Chawda, any luck ?  thank you !

[Haarika]

Hello @Kushal_Chawda, any luck with the solution?