SAC Planning for Expenses based on Income ratio for the store

[GavinMonteiro3]

Hi All,

We have a requirement for Expenses planning to happen storewise using the microsoft excel add-in.
Based on the total sales value of the store, the value of each expense GL A/c needs to be divided against the total sales value.  

CURRENT

Expected calculation is shown below 
(GL Expense/ Sales of that store)

Note - I do know there is a way to write the formula for each GL A/c and divide it by the Income GL group (in the GL Dimension)
But this would be hardcoding the formula for each GL A/c.

Hence, is there a way for the calculation to take the Storewise amount present in the row & divide it by the storewise sales GL grouping, without having the need to hardcode the value for each Expense GL Account ?

@N1kh1l  any inputs in terms of achieving this requirement ?

So that whatever GL's the user selects in the filters & those are visible in the rows, system can divide that by the Income GL group for the store?

Expectation is to be bring the sales GL group for that store against all the expenses of that store, so that a % of the expense against the sales of that store can be shown. (below table for reference)

Sales for this store in this month is Rs 500/-. Hence, it is expected that against all the expense GL's, the sales amount is displayed of Rs 500/-

GL	Store	Amount	Sales	%
Electricity	A	100	500	20%
Rent	A	150	500	30%
Warehouse	A	200	500	40%
Sales	A	500	500	100%

Currently, this is happening, by applying LOOKUP or RESTRICT or SUBTOTAL formula's.

https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/3b7c99a7a7cd4a29bb1c5...

System is bringing the sales values against only the sales GL account, but it is not fetching the same sales value for expenses such as electricity.

Hence, we are unable to derive the expense as a % of sales for that respective store.

GL	Store	Amount	Sales	%
Electricity	A	100	0	#DIV/0!
Rent	A	150	0	#DIV/0!
Warehouse	A	200	0	#DIV/0!
Sales	A	500	500	100%

Can you please let us know what way we can achieve this in SAC ?

Regards,
Gavin.

[WahidKausar]

Hi @SarauShen , SAP Analytics Cloud, data modeling , @N1kh1l , @hartmut_koerner 

We too are facing a similar problem in SAP Analytics Cloud (SAC) Planning 

Expectation is to be bring the sales GL group for that store against all the expenses of that store, so that a % of the expense against the sales of that store can be shown. (below table for reference)

Sales for this store in this month is Rs 500/-. Hence, it is expected that against all the expense GL's, the sales amount is displayed of Rs 500/-

GL	Store	Amount	Sales	%
Electricity	A	100	500	20%
Rent	A	150	500	30%
Warehouse	A	200	500	40%
Sales	A	500	500	100%

Currently, this is happening, by applying LOOKUP or RESTRICT or SUBTOTAL formula's.
All Formulas and Calculations | SAP Help Portal

System is bringing the sales values against only the sales GL account, but it is not fetching the same sales value for expenses such as electricity.

Hence, we are unable to derive the expense as a % of sales for that respective store.

GL	Store	Amount	Sales	%
Electricity	A	100	0	#DIV/0!
Rent	A	150	0	#DIV/0!
Warehouse	A	200	0	#DIV/0!
Sales	A	500	500	100%

Can you please let us know what way we can achieve this in SAC ?

Regards,

Wahid.

[SarauShen]

Hi @Wahid, Lookup formula shows the aggregated value for all members (including filtered out members). In your case, I expect the formula is LOOKUP([Amount],[d/GL_Account]=("Sales")), and this formula returns "500" for all GL_Account members. https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/2784376d431e4a7da9564...

[WahidKausar]

Hi Sarau,

[WahidKausar]

Hi @SarauShen , @hartmut_koerner 

How can we bring sales value on the same rows as the expenses ?
For e.g., bring 500 in sales next to Electricity in the table in SAC Planning ?