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 

[Kushal_Chawda]

@Haarika  Sales and SO column in excel are expressions in your chart?

[Haarika]

@Kushal_Chawda thats correct. Sales - sum(sales) and S0 - sum(SO)

[Haarika]

Hi @Kushal_Chawda - I attached an updated excel for your reference. Hope this one explains the requirement better than the previous uploaded file. Please let me know if you have further questions. Thank you very much

[Kushal_Chawda]

@Haarika  Do you want revised SO to be presented on graph?

[Haarika]

@Kushal_Chawda yes, that's correct.. the revised SO needs to be displayed as a trend line

[Kushal_Chawda · Accepted solution]

@Haarika  try below expression for  SO

= if(RowNo(total)=1,
sum(SO),
if(RowNo(total)=2,
rangesum(sum(SO), above(sum(SO)-sum(Sales))/11),
if( RowNo(total)>=3,
rangesum(above(Column(2)), (rangesum(above(Column(2),1, RowNo(total)))-
rangesum( above(sum(Sales),1,RowNo(total))))/(12- RowNo(total)+1)))))

Note:  Column(2) indicates that your SO expression is second expression in graph.

[Haarika]

@Kushal_Chawda, fabulous !!! amazing !! works as expected.... umpteen thanks to you 🙂

[Haarika]

Hi @Kushal_Chawda ,

I hope you are doing well !

There has been a slight modification in the business ask with regards to computing the Sales objective. I have attached an excel demonstrating the new methodology. Please refer to 'Product A' tab for sample data.

Key points to note:

a) for the completed months the Sales Objective should be made equal to the Sales (refer column B in excel)

b) the sum of revised SO for each month should always match the Sum of Original SO (refer column C in excel) 

c) the adjusted SO trend line should display values calculated for the next month with no sales yet , i.e. as per the data provided SO values should be derived for Aug' 2021 (refer Column X in excel for calculations) and plotted as a trend and this should get updated when we receive sales for Aug'2021 and so on.

I find this requirement a bit tricky and hence need your assistance to solve it. Please let me know if you need further inputs

[Kushal_Chawda]

@Haarika  Point a) says  SO should be equal to Sales for completed months which is reflecting in Revised SO but point b) says revised so should equal to original SO, which is not the case.