cancel
Showing results for 
Search instead for 
Did you mean: 

SAC Planning for Expenses based on Income ratio for the store

GavinMonteiro3
Participant
641

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

GavinMonteiro3_0-1724623824468.png

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

GavinMonteiro3_1-1724623839427.png

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/-

GLStoreAmountSales%
ElectricityA10050020%
RentA15050030%
WarehouseA20050040%
SalesA500500100%

 

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.

GLStoreAmountSales%
ElectricityA1000#DIV/0!
RentA1500#DIV/0!
WarehouseA2000#DIV/0!
SalesA500500100%

GavinMonteiro3_0-1726750612013.png

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

 

Regards,
Gavin.

View Entire Topic
SarauShen
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Gavin, you might have a look at %subtotal formula? https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/3b7c99a7a7cd4a29bb1c5... 

Regards,

Sarau

WahidKausar
Explorer
0 Kudos
Hi Sarau,
WahidKausar
Explorer
0 Kudos
Hi Sarau,
WahidKausar
Explorer
0 Kudos
We can use subtotal, only to get store wise sales, but we need to get the expenses that are present against each GL account &then divide store wise sales.
WahidKausar
Explorer
0 Kudos
Hence, subtotal functionality alone does not work