Skip to Content
1
Dec 23, 2022 at 06:12 AM

How to calculate depreciation in sac when we have multiple values as capital cost

506 Views

Hi,

There is requirement to calculate the depreciation where asset life and capital cost are planned values, so we will be having 3 input templets

1.Asset_life

here we will be maintaining measure as asset_life , kpi=assetlife and date is hardlocked as 202201

image.png

2.capital cost

here we will be maintaining measure as capital_cost , kpi=capital_cost

image.png

3.depreciation

we will be showing our output in this table as mentioned below

depreciation=capitalcost/asset_life

image.png

the expected output is as mentioned in depreciation sheet

logic:-

1.1 - when we have a single values in capital cost

depreciation= 2000/2

1.2 - if we have multiple values in capital cost table

depreciation= 2000/2 +3000/2

untill the 2nd value its 1.1 logic, from the 2nd planned value it should use 1.2 logic

currently iam using this

MEMBERSET[d/Date]=[d/Version].[p/S_DATE] TO [d/Version].[p/E_DATE]

FLOAT @ASSET_LIFE

FLOAT @CAPITAL_COST

FLOAT @DEPRECIATION

FLOAT @START_COUNT

INTEGER @COUNT

DELETE([d/Measures] = "DEPRECIATION", [d/GLACCOUNT] = "DEPRECIATION")

FOREACH.BOOKED [d/ASSET_CLASS]

@CAPITAL_COST = RESULTLOOKUP([d/Measures] = "CAPITA_COST", [d/GLACCOUNT] = "CAPITAL_COST")

@ASSET_LIFE = RESULTLOOKUP([d/Measures] = "ASSET_LIFE", [d/GLACCOUNT] = "ASSET_LIFE", [d/Date] = "202201")

@DEPRECIATION = @CAPITAL_COST / @ASSET_LIFE

@COUNT = 0

FOREACH [d/Date]

IF RESULTLOOKUP([d/Measures] = "CAPITA_COST", [d/GLACCOUNT] = "CAPITAL_COST") != 0 THEN

@COUNT = 1

@START_COUNT = RESULTLOOKUP([d/Measures] = "CAPITA_COST", [d/GLACCOUNT] = "CAPITAL_COST")

ENDIF

IF @COUNT >= 1 AND @COUNT <= @START_COUNT

THEN

DATA([d/Measures] = "DEPRECIATION", [d/GLACCOUNT] = "DEPRECIATION") = @DEPRECIATION

@COUNT = @COUNT + 1

ENDIF

ENDFOR

ENDFOR

// DATA([d/Measures]="DEPRECIATION",[d/GLACCOUNT]="DEPRECIATION")

// =RESULTLOOKUP([d/Measures]="CAPITA_COST",[d/GLACCOUNT]="CAPITAL_COST")/

// RESULTLOOKUP([d/Measures]="ASSET_LIFE",[d/GLACCOUNT]="ASSET_LIFE",[d/Date]="202201")

// ENDFOR

getting output as below

image.png

can some one help me to get the expected values as output

Attachments

image.png (8.2 kB)
image.png (11.7 kB)
image.png (12.3 kB)
image.png (35.0 kB)