Calculation Script for Currency Conversion and Budget Transfer

Calculation Script

Scenario: Currency Conversion

Solution:
SET CLEARUPDATESTATUS OFF;
SET UPDATECALC OFF;
SET CALCPARALLEL 3;
SET AGGMISSG ON;
SET CACHE HIGH;
FIX ("Actual", "at Budget Rate", &Curr_Year, @REMOVE(@RELATIVE("Account",0), @RELATIVE("Period
Ending Rates",0)))
CLEARDATA "USD";
CLEARDATA "EUR";
CLEARDATA "GBP";
CLEARDATA "SGD";
CLEARDATA "Local";
ENDFIX
FIX (&Curr_Year, "Local", @RELATIVE("1USPL",0))
DATACOPY "Actual"->"Final" TO "Actual"->"at Budget Rate";
ENDFIX
SET CREATEBLOCKONEQ ON;
FIX (&Curr_Year, "Actual", "at Budget Rate", "No Product", "No Project", "No Entity", "Jan":"Dec")

"USD"
(
"USD"->"USD Per LC" = @XREF("AAA", "Budget", "Final", "USD", "USD Per LC");
"SGD"->"USD Per LC" = @XREF("AAA", "Budget", "Final", "SGD", "USD Per LC");
"HKD"->"USD Per LC" = @XREF("AAA", "Budget", "Final", "HKD", "USD Per LC");
"AUD"->"USD Per LC" = @XREF("AAA", "Budget", "Final", "AUD", "USD Per LC");
"JPY"->"USD Per LC" = @XREF("AAA", "Budget", "Final", "JPY", "USD Per LC");
"EUR"->"USD Per LC" = @XREF("AAA", "Budget", "Final", "EUR", "USD Per LC");
"GBP"->"USD Per LC" = @XREF("AAA", "Budget", "Final", "GBP", "USD Per LC");
"CHF"->"USD Per LC" = @XREF("AAA", "Budget", "Final", "CHF", "USD Per LC");
);
ENDFIX
SET CREATEBLOCKONEQ OFF;
/*
Convert all Local currency input into USD
*/
FIX ("Actual", "at Budget Rate", &Curr_Year, @RELATIVE("Entity",0), @RELATIVE("Product",0),"No
Project")
DATACOPY "Local" TO "USD";
"USD"
(
IF (@ISDESC("Entity"))
@CALCMODE(BOTTOMUP);
@CALCMODE(BLOCK);

IF(@ISMBR(@WITHATTR("Input Currency","==","SGD Source")))
"USD" = "USD" * "USD Per LC"->"SGD"->"No Entity"->"No Product"->"No
Project";
ELSEIF(@ISMBR(@WITHATTR("Input Currency","==","HKD Source")))
"USD" = "USD" * "USD Per LC"->"HKD"->"No Entity"->"No Product"->"No
Project";
ELSEIF(@ISMBR(@WITHATTR("Input Currency","==","AUD Source")))
"USD" = "USD" * "USD Per LC"->"AUD"->"No Entity"->"No Product"->"No
Project";
ELSEIF(@ISMBR(@WITHATTR("Input Currency","==","JPY Source")))
"USD" = "USD" * "USD Per LC"->"JPY"->"No Entity"->"No Product"->"No
Project";
ELSEIF(@ISMBR(@WITHATTR("Input Currency","==","EUR Source")))
"USD" = "USD" * "USD Per LC"->"EUR"->"No Entity"->"No Product"->"No
Project";
ELSEIF(@ISMBR(@WITHATTR("Input Currency","==","GBP Source")))
"USD" = "USD" * "USD Per LC"->"GBP"->"No Entity"->"No Product"->"No
Project";
ELSEIF(@ISMBR(@WITHATTR("Input Currency","==","CHF Source")))
"USD" = "USD" * "USD Per LC"->"CHF"->"No Entity"->"No Product"->"No
Project";
ENDIF
ELSEIF (@ISUDA("Account","Asset") OR @ISUDA("Account","Liability") OR @ISUDA("Account","Other"))
"USD" = #Missing; /* only have PnL rates available at the moment. */
ENDIF
)
ENDFIX

/*
Convert USD to EUR and GBP for EU reporting
*/
FIX ("Actual", "at Budget Rate", &Curr_Year, @RELATIVE("EU Entity",0), @RELATIVE("Product",0),"No
Project")
DATACOPY "USD" TO "EUR";
DATACOPY "USD" TO "GBP";
"EUR"
(
IF (@ISDESC("US PL Entity"))
@CALCMODE(BOTTOMUP);
@CALCMODE(BLOCK);
"EUR" = "EUR" * (1 / "USD Per LC"->"EUR"->"No Entity"->"No Product"->"No
Project");
"GBP" = "GBP" * (1 / "USD Per LC"->"GBP"->"No Entity"->"No Product"->"No
Project");
ENDIF
)
ENDFIX
/*
Convert USD to SGD
*/
FIX ("Actual", "at Budget Rate", &Curr_Year, @RELATIVE("Asia Entity",0), @RELATIVE("Product",0),"No
Project")
DATACOPY "USD" TO "SGD";

"SGD"
(
IF (@ISDESC(“US PL Entity"))
@CALCMODE(BOTTOMUP);
@CALCMODE(BLOCK);
"SGD" = "SGD" * (1 / "USD Per LC"->"SGD"->"No Entity"->"No Product"->"No
Project");
ENDIF
)
ENDFIX

FIX(Budget, @DESCENDANTS(East))

   CALC DIM(Year, Measures, Product);

ENDFIX

/* Consolidate East */

FIX(Budget)

   @DESCENDANTS(East);

ENDFIX
-----------------------------------------------------------------------------------------------------------------

Scenario: Budget Transfer

Solution:
FIX ("Current","V1" ,"None","FY13")

"Actual_Expense"

(

"OED"->"Jan" = "Overheads"->"Jan" + "Shipyard"->"Jan" ;

"OED"->"Feb" = "Overheads"->"Feb" + "Shipyard"->"Feb" ;

"OED"->"Mar" = "Overheads"->"Mar" + "Shipyard"->"Mar" ;

"OED"->"Apr" = "Overheads"->"Apr" + "Shipyard"->"Apr" ;

"OED"->"May" = "Overheads"->"May" + "Shipyard"->"May" ;

"OED"->"Jun" = "Overheads"->"Jun" + "Shipyard"->"Jun" ;

"OED"->"Jul" = "Overheads"->"Jul" + "Shipyard"->"Jul" ;

"OED"->"Aug" = "Overheads"->"Aug" + "Shipyard"->"Aug" ;

"OED"->"Sep" = "Overheads"->"Sep" + "Shipyard"->"Sep" ;

"OED"->"Oct" = "Overheads"->"Oct" + "Shipyard"->"Oct" ;

"OED"->"Nov" = "Overheads"->"Nov" + "Shipyard"->"Nov" ;

"OED"->"Dec" = "Overheads"->"Dec" + "Shipyard"->"Dec" ;

)

ENDFIX

FIX ("Current","V1" ,"FY13")

"Actual_Expense"

(

"Overheads"->"Jan"  = 0;

"Shipyard"->"Jan"  = 0;

"Overheads"->"Feb" = 0;

"Shipyard"->"Feb" = 0;

"Overheads"->"Mar" = 0;

"Shipyard"->"Mar" = 0;

"Overheads"->"Apr" = 0;

"Shipyard"->"Apr" = 0;

"Overheads"->"May" = 0;

"Shipyard"->"May" = 0;

"Overheads"->"Jun" = 0;

"Shipyard"->"Jun" = 0;

"Overheads"->"Jul" = 0;

"Shipyard"->"Jul" = 0;

"Overheads"->"Aug" = 0;

"Shipyard"->"Aug" = 0;

"Overheads"->"Sep" = 0;

"Shipyard"->"Sep" = 0;

"Overheads"->"Oct" = 0;

"Shipyard"->"Oct" = 0;

"Overheads"->"Nov" = 0;

"Shipyard"->"Nov" = 0;

"Overheads"->"Dec" = 0;

"Shipyard"->"Dec" = 0;

)

ENDFIX