FORMAT==100
#***************************************************************************
# STETs
#***************************************************************************
['Budget Approved']=STET;
['Budget Entry']=STET;
['Top Down']=STET;
['Forecast Q1']=STET;
['Forecast Q2']=STET;
['Forecast Q3']=STET;
['Forecast Mixed']=STET;
['Forecast YTD Trend']=STET;
['Forecast Hold Annual Plan']=STET;
['What If 1']=STET;
['What If 2']=STET;
['What If 3']=STET;

#***************************************************************************
# Var Act vs Target
#***************************************************************************
['Var Act vs Target','Expenses']=N:
  ['Target']-['Actual'];

['Var Act vs Target']=N:
  ['Actual']-['Target'];

#***************************************************************************
# % Var Act vs Target
#***************************************************************************
['% Var Act vs Target']=
  ['Actual'] \ ['Target'];

#***************************************************************************
# Sales
#***************************************************************************
['Local', 'Actual','Sales', 'Amount'] =N:
  ROUND(DB('GL',!Currency, !Version,!FiscalYear,!Period,'All Departments',!Region,'0100','Total Amount'));

['Local', 'Target','Sales', 'Amount'] =N:
  IF(!FiscalYear @= '2010',
    ROUND(DB('GL',!Currency, 'Budget Entry',!FiscalYear,!Period,'All Departments',!Region,'0100','Total Amount')),
    ROUND(DB('GL',!Currency, 'Budget Approved',!FiscalYear,!Period,'All Departments',!Region,'0100','Total Amount'))
  );

#***************************************************************
# Expenses
#***************************************************************
['Local', 'Actual','Expenses', 'Amount'] =N:
    ROUND(DB('GL',!Currency, !Version,!FiscalYear,!Period,'All Departments',!Region,'Total Expenses','Total Amount'));

['Local', 'Target','Expenses', 'Amount'] =N:
  IF(!FiscalYear @= '2010',
    ROUND(DB('GL',!Currency, 'Budget Entry',!FiscalYear,!Period,'All Departments',!Region,'Total Expenses','Total Amount')),
    ROUND(DB('GL',!Currency, 'Budget Approved',!FiscalYear,!Period,'All Departments',!Region,'Total Expenses','Total Amount'))
  );

#***************************************************************
# EBIT
#***************************************************************
['Local', 'Actual','EBIT', 'Amount'] =N:
  ROUND(DB('GL',!Currency, !Version,!FiscalYear,!Period,'All Departments',!Region,'EBIT','Total Amount'));

['Local', 'Target','EBIT', 'Amount'] =N:
  IF(!FiscalYear @= '2010',
    ROUND(DB('GL',!Currency, 'Budget Entry',!FiscalYear,!Period,'All Departments',!Region,'EBIT','Total Amount')),
    ROUND(DB('GL',!Currency, 'Budget Approved',!FiscalYear,!Period,'All Departments',!Region,'EBIT','Total Amount'))
  );

#***************************************************************
# Gross Profit
#***************************************************************
['Local', 'Actual','Gross Profit', 'Amount'] =N:
  ROUND(DB('GL',!Currency, !Version,!FiscalYear,!Period,'All Departments',!Region,'Gross Margin','Total Amount'));

['Local', 'Target','Gross Profit', 'Amount'] =N:
  IF(!FiscalYear @= '2010',
    ROUND(DB('GL',!Currency, 'Budget Entry',!FiscalYear,!Period,'All Departments',!Region,'Gross Margin','Total Amount')),
    ROUND(DB('GL',!Currency, 'Budget Approved',!FiscalYear,!Period,'All Departments',!Region,'Gross Margin','Total Amount'))
  );

#***************************************************************
# NPAT
#***************************************************************
['Local', {'Actual', 'Budget Approved'},'NET PROFIT AFTER TAX', 'Amount']=N:
  ROUND(DB('GL',!Currency, !Version,!FiscalYear,!Period,'All Departments',!Region,'NET PROFIT AFTER TAX','Total Amount'));

['Local', 'Target','NET PROFIT AFTER TAX', 'Amount'] =N:
  IF(!FiscalYear @= '2010',
    ROUND(DB('GL',!Currency, 'Budget Entry',!FiscalYear,!Period,'All Departments',!Region,'NET PROFIT AFTER TAX','Total Amount')),
    ROUND(DB('GL',!Currency, 'Budget Approved',!FiscalYear,!Period,'All Departments',!Region,'NET PROFIT AFTER TAX','Total Amount'))
  );

#***************************************************************
# NET OPERATING PROFIT AFTER TAX
#***************************************************************
['Local', {'Actual', 'Budget Approved'},'NET OPERATING PROFIT AFTER TAX', 'Amount']=N:
  ROUND(DB('GL',!Currency, !Version,!FiscalYear,!Period,'All Departments',!Region,'NET PROFIT AFTER TAX','Total Amount')
  - DB('GL',!Currency,!Version,!FiscalYear,!Period,'All Departments',!Region,'Interest Received/(Paid)','Total Amount'));

['Local', 'Target','NET OPERATING PROFIT AFTER TAX', 'Amount'] =N:
  IF(!FiscalYear @= '2010',
    ROUND(DB('GL',!Currency, 'Budget Entry',!FiscalYear,!Period,'All Departments',!Region,'NET PROFIT AFTER TAX','Total Amount')
    - DB('GL',!Currency,'Budget Entry',!FiscalYear,!Period,'All Departments',!Region,'Interest Received/(Paid)','Total Amount')),
    ROUND(DB('GL',!Currency, 'Budget Approved',!FiscalYear,!Period,'All Departments',!Region,'NET PROFIT AFTER TAX','Total Amount')
    - DB('GL',!Currency,'Budget Approved',!FiscalYear,!Period,'All Departments',!Region,'Interest Received/(Paid)','Total Amount'))
  );

#***************************************************************************
# Units
#***************************************************************************
['Local', 'Actual','Units', 'Amount'] =N:
  DB('Revenue',!Currency,!Version,!FiscalYear,!Period,!Region,'All Departments','All Channels','All Products','Units');

['Local', 'Target','Units', 'Amount'] =N:
  IF(!FiscalYear @= '2010',
    DB('Revenue',!Currency,'Budget Entry',!FiscalYear,!Period,!Region,'All Departments','All Channels','All Products','Units'),
    DB('Revenue',!Currency,'Budget Approved',!FiscalYear,!Period,!Region,'All Departments','All Channels','All Products','Units')
  );

#***************************************************************************
# Employees
#***************************************************************************
['Local', 'Actual','Employees', 'Amount'] =N:
  DB('HeadcountMetrics',!Version,!FiscalYear,!Period,'All Departments',!Region,'End Headcount');

['Local', 'Target','Employees', 'Amount'] =N:
  IF(!FiscalYear @= '2010',
    DB('LabourTotalCosts',!Currency,'Budget Entry',!FiscalYear,!Period,'All Departments',!Region,'Total FTEs'),
    DB('LabourTotalCosts',!Currency,'Budget Approved',!FiscalYear,!Period,'All Departments',!Region,'Total FTEs')
  );

#***************************************************************************
# Sales/Unit
#***************************************************************************
['Local',{'Actual', 'Target'},'Sales/Unit','Amount']=
  ['Sales']\['Units'];

#***************************************************************************
# Gross Profit/Unit
#***************************************************************************
['Local',{'Actual', 'Target'},'Gross Profit/Unit','Amount']=
  ['Gross Profit']\['Units'];

#***************************************************************************
# Expenses/Unit
#***************************************************************************
['Local',{'Actual', 'Target'},'Expenses/Unit','Amount']=
  ['Expenses']\['Units'];

#***************************************************************************
# Net Profit/Unit
#***************************************************************************
['Local',{'Actual', 'Target'},'Net Profit/Unit','Amount']=
  ['NET PROFIT AFTER TAX']\['Units'];

#***************************************************************************
# Sales/Employee
#***************************************************************************
['Local',{'Actual', 'Target'},'Sales/Employee','Amount']=
  ['Sales']\['Employees'];

#***************************************************************************
# Expenses/Employee
#***************************************************************************
['Local',{'Actual', 'Target'},'Expenses/Employee','Amount']=
  ['Expenses']\['Employees'];

#***************************************************************************
# Gross Profit/Employee
#***************************************************************************
['Local',{'Actual', 'Target'},'Gross Profit/Employee','Amount']=
  ['Gross Profit']\['Employees'];

#***************************************************************************
# Net Profit/Employee
#***************************************************************************
['Local',{'Actual', 'Target'},'Net Profit/Employee','Amount']=
  ['NET PROFIT AFTER TAX']\['Employees'];

#***************************************************************************
# Balance Sheet
#***************************************************************************
['Local', 'Actual','CAPITAL', 'Amount'] =N:
  (DB('BalanceSheet',!Currency,!Version,!FiscalYear,!Period,'All Departments',!Region,'TOTAL LIABILITIES','Period Balance')
  + DB('BalanceSheet',!Currency,!Version,!FiscalYear,!Period,'All Departments',!Region,'EQUITIES','Period Balance'))
  - DB('BalanceSheet',!Currency,!Version,!FiscalYear,!Period,'All Departments',!Region,'Creditors-Trade','Period Balance')
  - DB('BalanceSheet',!Currency,!Version,!FiscalYear,!Period,'All Departments',!Region,'Creditors-Other','Period Balance');

['Local', 'Target','CAPITAL', 'Amount'] =N:
  IF(!FiscalYear @= '2010',
    (DB('BalanceSheet',!Currency,'Budget Entry',!FiscalYear,!Period,'All Departments',!Region,'TOTAL LIABILITIES','Period Balance')
    + DB('BalanceSheet',!Currency,'Budget Entry',!FiscalYear,!Period,'All Departments',!Region,'EQUITIES','Period Balance'))
    - DB('BalanceSheet',!Currency,'Budget Entry',!FiscalYear,!Period,'All Departments',!Region,'Creditors-Trade','Period Balance')
    - DB('BalanceSheet',!Currency,'Budget Entry',!FiscalYear,!Period,'All Departments',!Region,'Creditors-Other','Period Balance'),
  (DB('BalanceSheet',!Currency,'Budget Approved',!FiscalYear,!Period,'All Departments',!Region,'TOTAL LIABILITIES','Period Balance')
  + DB('BalanceSheet',!Currency,'Budget Approved',!FiscalYear,!Period,'All Departments',!Region,'EQUITIES','Period Balance'))
  - DB('BalanceSheet',!Currency,'Budget Approved',!FiscalYear,!Period,'All Departments',!Region,'Creditors-Trade','Period Balance')
  - DB('BalanceSheet',!Currency,'Budget Approved',!FiscalYear,!Period,'All Departments',!Region,'Creditors-Other','Period Balance')
  );

#***************************************************************************
# Debt Ratio
#***************************************************************************
['Local', 'Actual','Debt Ratio', 'Amount'] =N:
  1 - DB('BalanceSheet',!Currency,!Version,!FiscalYear,!Period,'All Departments',!Region,'EQUITIES','Period Balance')\
  (DB('BalanceSheet',!Currency,!Version,!FiscalYear,!Period,'All Departments',!Region,'TOTAL LIABILITIES','Period Balance')
  + DB('BalanceSheet',!Currency,!Version,!FiscalYear,!Period,'All Departments',!Region,'EQUITIES','Period Balance'));

['Local', 'Target','Debt Ratio', 'Amount'] =N:
  IF(!FiscalYear @= '2010',
    1 - DB('BalanceSheet',!Currency,'Budget Entry',!FiscalYear,!Period,'All Departments',!Region,'EQUITIES','Period Balance')\
    (DB('BalanceSheet',!Currency,'Budget Entry',!FiscalYear,!Period,'All Departments',!Region,'TOTAL LIABILITIES','Period Balance')
    + DB('BalanceSheet',!Currency,'Budget Entry',!FiscalYear,!Period,'All Departments',!Region,'EQUITIES','Period Balance')),
    1 - DB('BalanceSheet',!Currency,'Budget Approved',!FiscalYear,!Period,'All Departments',!Region,'EQUITIES','Period Balance')\
    (DB('BalanceSheet',!Currency,'Budget Approved',!FiscalYear,!Period,'All Departments',!Region,'TOTAL LIABILITIES','Period Balance')
    + DB('BalanceSheet',!Currency,'Budget Approved',!FiscalYear,!Period,'All Departments',!Region,'EQUITIES','Period Balance'))
  );

#***************************************************************************
# CAPITAL COST RATE
#***************************************************************************
['Local', {'Actual', 'Target'},'CAPITAL COST RATE', 'Amount'] =N:
  (['Debt Ratio']*['Interest Rate'])
  +
  ((1-['Debt Ratio'])*['Cost of Capital (Hurdle Rate)']);

#***************************************************************************
# EVA
#***************************************************************************
['Local', {'Actual', 'Target'},'EVA (Economic Value Added)', 'Amount'] =N:
  ['NET OPERATING PROFIT AFTER TAX']
  - (['CAPITAL']*['CAPITAL COST RATE']\100\12);

['Local', {'Actual', 'Budget Approved'},'EVA % pa', 'Amount'] =N:
  ['EVA (Economic Value Added)']\['CAPITAL']*100\12;
