Description
Calculates depreciation.
Formula
SET CREATENONMISSINGBLK ON;
SET UPDATECALC OFF;
SET AGGMISSG ON;
VAR periodOffset = 0;
VAR deprMethod = 0;
VAR deprRate = 0;
VAR deprConvention = 0;
VAR numDeprPeriods = 0;
VAR totDeprPeriods = 0;
VAR persIn1stYear = 12;
VAR priorAccumDepr = 0;
VAR periodicPriorAccumDepr = 0;
VAR basicCost = 0;
VAR salvageVal = 0;
VAR deprAmt = 0;
VAR deprAmtLast = 0;
VAR persInSection = 0;
VAR life = 0;
VAR lifeIndex = 0;
VAR isAnnual = 0;
VAR inServiceDate = 0;
VAR delayedStartDate = 0;
VAR prematureEndDate = 0;
VAR maintenanceCost = 0;
VAR insuranceCost = 0;
VAR repairsCost = 0;
VAR retirementObs = 0;
VAR retirementCosts = 0;
VAR cashOutflowDate = 0;
VAR fundingDate = 0;
VAR purchaseDate = 0;
VAR cashFlowIncidence = 0;
VAR fundingIncidence = 0;
VAR cashStaggeredPers = 1;
VAR fundingStaggeredPers = 1;
VAR cashAllocPct = 1;
VAR fundingAllocPct = 1;
VAR fundingAmt = 0;
VAR setCashImpact = 0;
VAR setFundingImpact = 0;
VAR retireOption = 0;
VAR saleValue = 0;
VAR netValue;
VAR yearVal;
VAR monthVal;
VAR dayVal;
VAR delayStart;
VAR split1stAmt;
VAR paramsSet = 0;
VAR cashflowChanged = 0;
VAR fundingChanged = 0;
VAR preExistingPers = 0;
VAR setStartDepr = 0;
FIX([AssetClass], [Hidden_Scenario], [Hidden_Version], [Department])
FIX ("BegBalance", "No Year")
"Basic Cost" (
IF ("Asset Status" > 0)
IF ("In Service Date" < "Purchase Date")
"In Service Date" = "Purchase Date";
ENDIF
"Basic Cost" = "Asset Units" * "Asset Rate" + ("Asset Units" * "Asset Rate" * "Taxes %") + "Freight" + "Additional Charges" + "Retirement Obligation" + "Installation";
"Useful Life (in Years)" = "No Scenario"->"No Version"->"No Entity"->"Global"->"Useful Life (in Years)";
"Salvage" = "Salvage Input" * "Asset Units";
ENDIF
)
ENDFIX
FIX (@LEVMBRS("Period", 0), @LEVMBRS("Year", 0))
"Depreciation"(
IF ("No Year"->"BegBalance"->"Asset Status" >= 0)
IF (@ISMBR("BegBalance") AND paramsSet == 0)
paramsSet = 1;
/* Initialized all the variables required to calculate depreciation */
/* eliminate days from date */
delayedStartDate = @INT("No Year"->"Delayed Start Date" / 100) * 100;
prematureEndDate = @INT("No Year"->"Premature End Date" / 100) * 100;
purchaseDate = @INT("No Year"->"Purchase Date" / 100) * 100;
inServiceDate = @INT("No Year"->"In Service Date" / 100) * 100;
cashOutflowDate = purchaseDate;
fundingDate = purchaseDate;
cashFlowIncidence = "No Year"->"No Scenario"->"No Version"->"No Entity"->"Global"->"Cash Flow Incidence";
IF (cashFlowIncidence <> "No Year"->"Cash Flow Incidence")
cashflowChanged = 1;
ELSE
cashflowChanged = 0;
ENDIF
IF (cashFlowIncidence == -1 AND cashflowChanged == 1)
cashStaggeredPers = 4;
ELSE
/* Extract year and month from date to adjust based in incidence values in global assumptions */
yearVal = @INT(cashOutflowDate / 10000) * 10000;
monthVal = cashOutflowDate - yearVal;
monthVal = monthVal + cashFlowIncidence;
IF (monthVal <= 0)
yearVal = yearVal - 10000;
monthVal = 1200 + monthVal;
ELSEIF (monthVal > 1200)
yearVal = yearVal + 10000;
monthVal = monthVal - 1200;
ENDIF
cashOutflowDate = yearVal + monthVal;
cashStaggeredPers = 1;
ENDIF
fundingIncidence = "No Year"->"No Scenario"->"No Version"->"No Entity"->"Global"->"Funding Incidence";
IF (fundingIncidence <> "No Year"->"Funding Incidence")
fundingChanged = 1;
ELSE
fundingChanged = 0;
ENDIF
IF (fundingIncidence == -1 AND fundingChanged == 1)
fundingStaggeredPers = 4;
ELSE
yearVal = @INT(fundingDate / 10000) * 10000;
monthVal = fundingDate - yearVal;
monthVal = monthVal + fundingIncidence;
IF (monthVal <= 0)
yearVal = yearVal - 10000;
monthVal = 1200 + monthVal;
ELSEIF (monthVal > 1200)
yearVal = yearVal + 10000;
monthVal = monthVal - 1200;
ENDIF
fundingDate = yearVal + monthVal;
fundingStaggeredPers = 1;
ENDIF
/* Initialize for depreciation calc in next section */
basicCost = "No Year"->"Basic Cost";
salvageVal = "No Year"->"Salvage Input" * "No Year"->"Asset Units";
saleValue = "No Year"->"Sale Value";
retirementCosts = "No Year"->"Retirement Costs";
retirementObs = "No Year"->"Retirement Obligation";
deprMethod = "No Year"->"No Scenario"->"No Version"->"No Entity"->"Global"->"Depreciation Method";
deprConvention = "No Year"->"No Scenario"->"No Version"->"No Entity"->"Global"->"Depreciation Convention";
insuranceCost = basicCost * "No Year"->"No Scenario"->"No Version"->"No Entity"->"Global"->"Insurance %";
maintenanceCost = basicCost * "No Year"->"No Scenario"->"No Version"->"No Entity"->"Global"->"Maintenance %";
repairsCost = basicCost * "No Year"->"No Scenario"->"No Version"->"No Entity"->"Global"->"Repairs %";
fundingAmt = basicCost * "No Year"->"No Scenario"->"No Version"->"No Entity"->"Global"->"Funding %";
/* check if we have a pre-existing asset */
IF (inServiceDate < "First Date")
yearVal = (@INT("First Date" / 10000) * 10000 - @INT(inServiceDate / 10000) * 10000) / 10000;
monthVal = ("First Date" - @INT("First Date" / 10000) * 10000) - (inServiceDate - @INT(inServiceDate / 10000) * 10000);
IF (monthVal < 0)
IF (yearVal > 0)
yearVal = yearVal - 1;
ENDIF
monthVal = 1200 + monthVal;
ENDIF
preExistingPers = @ROUND(yearVal * 12 + monthVal / 100, 0);
setStartDepr = 1;
ELSE
preExistingPers = 0;
setStartDepr = 0;
ENDIF
/* setup for depreciation */
numDeprPeriods = 0;
periodOffset = 0;
IF (deprMethod > 0)
persIn1stYear = "NumPeriods"; /* Not adjusted - "Cal TP-Index" + 1; */
priorAccumDepr = 0;
periodicPriorAccumDepr = 0;
deprAmt = 0;
/* Add a periodic vs annual property instead of additional deprMethods */
/* Annual Methods SumYearDigits =2 DecliningBalance Year = 3*/
IF (deprMethod == 2 OR deprMethod == 3)
isAnnual = 1;
life = "No Year"->"Useful Life (in Years)";
persInSection = "NumPeriods";
ELSE /* Periodic Methods */
isAnnual = 0;
life = "No Year"->"Useful Life (in Years)" * "NumPeriods";
persInSection = 1;
ENDIF
setCashImpact = 0;
setFundingImpact = 0;
yearVal = @INT(inServiceDate / 10000) * 10000;
/* DecliningBalance methods */
IF (deprMethod == 3 OR deprMethod == 4)
/* depreciation rate = 1 - ((salvage / cost) ^ (1 / life)) */
deprRate = 1 - @POWER(salvageVal / basicCost, 1 / life);
ENDIF
/* Reset depr method to SLN convention to prorate 1st period for preExisting assets */
IF (preExistingPers > 0)
deprMethod = 1;
deprConvention = 1;
ENDIF
ENDIF
ENDIF
IF (@ISMBR("No Year"))
paramsSet = 0;
ENDIF
IF (NOT @ISMBR("BegBalance") AND paramsSet == 1)
/* clear out any previously calculated values */
"Depreciation" = #MISSING;
"Accumulated Depreciation" = #MISSING;
"Property, Plant and Equipment Gross" = #MISSING;
"Loss/(Gain) on Sale of Property, Plant and Equipment" = #MISSING;
"Proceeds from Sale of Property, Plant and Equipment" = #MISSING;
IF (cashFlowIncidence <> -1 OR cashflowChanged == 1)
"Cash Flow Allocator" = #MISSING;
ENDIF
IF (fundingIncidence <> -1 OR fundingChanged == 1)
"Funding Allocator" = #MISSING;
ENDIF
"Cash Outflow from Capital Additions" = #MISSING;
"Cash Inflow from Funding" = #MISSING;
"Long Term Debt" = #MISSING;
"Insurance" = #MISSING;
"Maintenance" = #MISSING;
"Repairs" = #MISSING;
"Retirement Expenses" = #MISSING;
"Capital Expenditure" = #MISSING;
IF ("TP-Date" == purchaseDate AND (delayedStartDate == #MISSING OR (delayedStartDate > #MISSING AND "TP-Date" > delayedStartDate)))
"Capital Expenditure" = basicCost - retirementObs;
ENDIF
/* Check if we should start setting cash flow impact values */
IF ("TP-Date" == cashOutflowDate AND (delayedStartDate == #MISSING OR (delayedStartDate > #MISSING AND "TP-Date" > delayedStartDate)))
setCashImpact = cashStaggeredPers;
cashAllocPct = 1 / cashStaggeredPers;
ENDIF
/* Check if we should start settng funding values */
IF ("TP-Date" == fundingDate AND (delayedStartDate == #MISSING OR (delayedStartDate > #MISSING AND "TP-Date" > delayedStartDate)))
setFundingImpact = fundingStaggeredPers;
fundingAllocPct = 1 / fundingStaggeredPers;
ENDIF
IF (setCashImpact > 0)
"Cash Flow Allocator" = cashAllocPct;
setCashImpact = setCashImpact - 1;
ENDIF
"Cash Outflow from Capital Additions" = basicCost * "Cash Flow Allocator";
IF (setFundingImpact > 0)
"Funding Allocator" = fundingAllocPct;
setFundingImpact = setFundingImpact - 1;
ENDIF
"Cash Inflow from Funding" = fundingAmt * "Funding Allocator";
"Long Term Debt" = fundingAmt * "Funding Allocator";
/* Check if we should start depreciation */
IF ((numDeprPeriods == 0) AND (("TP-Date" == inServiceDate) OR (setStartDepr == 1) OR
(deprConvention == 4 AND "Fiscal TP-Index" == @INT("NumPeriods" / 2)+1) AND "TP-Date" > yearVal))
setStartDepr = 0;
periodOffset = 0;
persIn1stYear = "NumPeriods"; /* Not adjusted - "Cal TP-Index" + 1; */
numDeprPeriods = "NumPeriods" * "No Year"->"BegBalance"->"Useful Life (in Years)";
totDeprPeriods = numDeprPeriods;
dayVal = "No Year"->"BegBalance"->"In Service Date" - inServiceDate;
monthVal = (inServiceDate - @INT(inServiceDate / 10000) * 10000);
delayStart = 0;
IF (deprConvention == 2) /* Prorate Actual Date */
IF (dayVal == 1) /* Treat same as Begin Period */
split1stAmt = 0;
ELSE
numDeprPeriods = numDeprPeriods + 1;
split1stAmt = 1;
ENDIF
ELSEIF (deprConvention == 3) /* Mid Period */
numDeprPeriods = numDeprPeriods + 1;
split1stAmt = 1;
ELSEIF (deprConvention == 4) /* MidYear */
split1stAmt = 0;
delayStart = @INT("NumPeriods" / 2) - "Fiscal TP-Index" +1;
ELSE /* Prorate Begin Period */
split1stAmt = 0;
ENDIF
IF (deprMethod == 3 OR deprMethod == 4)
/* depreciation rate = 1 - ((salvage / cost) ^ (1 / life)) */
deprRate = 1 - @POWER(salvageVal / basicCost, 1 / life);
ELSEIF (deprMethod == 1)
deprRate = (basicCost - salvageVal) / totDeprPeriods;
ENDIF
insuranceCost = insuranceCost / "NumPeriods";
maintenanceCost = maintenanceCost / "NumPeriods";
repairsCost = repairsCost / "NumPeriods";
/* Adjust for pre existing assets */
IF (preExistingPers > 0)
numDeprPeriods = numDeprPeriods - preExistingPers;
IF (numDeprPeriods > 0)
deprAmt = (basicCost - salvageVal) / totDeprPeriods;
periodicPriorAccumDepr = deprAmt * preExistingPers;
ENDIF
ENDIF
ENDIF
/* Depreciation calcuation section */
IF (deprMethod > 0 AND periodOffset < numDeprPeriods AND delayStart < 1)
lifeIndex = @INT(periodOffset / persInSection);
IF (deprMethod == 1) /* Straight Line */
deprAmt = (basicCost - salvageVal) / totDeprPeriods;
ELSEIF (deprMethod == 3 or deprMethod == 4) /* Declining Balance */
IF (lifeIndex == 0)
/* cost * rate * month / 12; for 1st period */
deprAmt = basicCost * deprRate * persIn1stYear /
"NumPeriods" / persInSection;
ELSE /* (cost - total depreciation from prior periods) * rate; for all middle periods */
deprAmt = (basicCost - priorAccumDepr) * deprRate / persInSection;
ENDIF
ELSEIF (deprMethod == 2) /* Sum of Years Digits */
deprRate = (life - lifeIndex) * 2 / (life * (life +1));
deprAmt = (basicCost - salvageVal) * deprRate / persInSection;
ENDIF
IF (split1stAmt == 1)
IF (deprConvention == 2)
/* assume that there are 30 days in each month */
deprAmt = deprAmt * @MAX(30 - dayVal, 1) / 30;
ELSEIF (deprConvention == 4)
;
ELSE
deprAmt = deprAmt - deprAmt / 2;
ENDIF
split1stAmt = 0;
ENDIF
/* Adjust for rounding errors */
IF (periodOffset + 1 == numDeprPeriods)
deprAmt = (basicCost - salvageVal) - periodicPriorAccumDepr;
ENDIF
periodicPriorAccumDepr = periodicPriorAccumDepr + deprAmt;
/* Use accumulated depr as first value for Mid-Year converntion */
IF (deprConvention == 4 AND "TP-Date" == inServiceDate AND monthVal > 600)
deprAmt = periodicPriorAccumDepr;
ENDIF
IF (isAnnual == 1)
IF (@INT((periodOffset + 1) / persInSection) == (lifeIndex + 1))
priorAccumDepr = periodicPriorAccumDepr;
ENDIF
ELSE
priorAccumDepr = periodicPriorAccumDepr;
ENDIF
/* Only assign to member if we are in range */
IF ("TP-Date" >= delayedStartDate AND ("TP-Date" >= inServiceDate OR preExistingPers > 0) AND (prematureEndDate == #MISSING OR "TP-Date" < prematureEndDate))
"Property, Plant and Equipment Gross" = basicCost;
"Depreciation" = deprAmt;
"Accumulated Depreciation" = periodicPriorAccumDepr;
"Insurance" = insuranceCost;
"Maintenance" = maintenanceCost;
"Repairs" = repairsCost;
ENDIF
/* Set the loss/gain if we had a sale or writeoff */
IF ("TP-Date" == prematureEndDate)
IF ("No Year"->"BegBalance"->"Reason Ended" == 2 OR
"No Year"->"BegBalance"->"Reason Ended" == 3)
netValue = basicCost - priorAccumDepr + retirementCosts;
IF ("No Year"->"BegBalance"->"Reason Ended" == 3)
"Loss/(Gain) on Sale of Property, Plant and Equipment" = netValue - saleValue + deprAmt;
ELSE
"Loss/(Gain) on Sale of Property, Plant and Equipment" = netValue + deprAmt;
ENDIF
"Retirement Expenses" = retirementCosts - retirementObs;
"Proceeds from Sale of Property, Plant and Equipment" = saleValue - retirementCosts;
ENDIF
ENDIF
periodOffset = periodOffset +1;
ENDIF
delayStart = delayStart -1;
ENDIF
ENDIF
);
ENDFIX
FIX ("BegBalance", "No Year")
"Cash Flow Incidence" (
IF (cashflowChanged == 1 AND "Asset Status" > 0)
"Cash Flow Incidence" = cashFlowIncidence;
ENDIF
IF (fundingChanged == 1 AND "Asset Status" > 0)
"Funding Incidence" = fundingIncidence;
ENDIF
)
ENDFIX
ENDFIX
FIX([Hidden_Scenario], [Hidden_Version], [Department])
CALC DIM ("Period", "Asset Class");
@ANCESTORS([LineItem]);
ENDFIX