Controller profitability analysis Role center/KPI in Dynamics AX 2009 – Formulas for calculation of the Actaul and Goal values

Hi There,

In this post, i am going to explain the business logic and the setup behind the controller profitability role center/KPI in Microsoft Dynamics AX 2009.

The Controller Profitability Analysis KPI report/role center displays management’s effectiveness in generating profits. The values displayed under the Actual column on the role center are always for the current month and the values for the GOAL, Trend and Status are calculated based on conditions in business logic.  These setups are done in the General ledger cubes.

The screenshot below depicts how a standard controller profitability KPI looks like.

Now, let us discuss these indicators and columns one by one.

1. Revenue:

Actual: It shows the sum of balances for all revenue accounts.

Calculation: Sales (33) + Other Income (48) +Sales credits (34) + Gain/Loss on asset disposal (51). (33, 34, 48 and 51 above refers to the Ledger account categories which are setup at GL > Setup > Ledger Accnt. Categories.)

Goal: The Goal is always set as a % of the Actual value. For e.g. if the total actual revenue is 100% in a month, then Goal can be set to 105%. The expression for the Goal value is set in the KPI for the role center. Once the user decided the Goal for a month, it can be setup in the KPI expression back in the cube which is accessible by opening visual studio.

Default value for the Goal in KPI = Actual Value * 1.05(If the Goal % assumed is 105%).

Trend: Upwards arrow if the actual revenue in current period (month) is greater than or equal to equal to the actual revenue in the previous period (month) else downwards.

Status: Green (If Actual > Goal), Yellow (If Actual > 90% of Goal) and Red (Otherwise).

2. Expenses:

Actual: It shows the sum of balances for all expense accounts.

Calculation: Cost of goods sold (35) +Selling expense (ref id = 36) + Admin exp (37) + Manu exp (38) +Travel and enter exp (39) + Project expenses (40) + Salaries exp (41) + Other Emp exp (42) + Interest exp (43) + Tax exp () + Depr exp (45) + Income tax exp (46) +Other expenses (= 47) + Amortization of intangible assets (ref id = 52) + Gain/loss on asset disposal (ref id = 51) .

The numbers in brackets above refers to the ledger account categories .

Goal: Default value for the Goal = Actual Value * 1.20

Trend: Upwards arrow if the actual expenses in current period (month) are less the actual expenses in the previous period (month) else downwards.

Status: Green (If Actual <= Goal), Yellow (If Actual <= 90% of Goal) and Red (Otherwise).

3. Cost of Goods sold :

Actual: It shows the total cost of goods sold.

Calculation: The value from the Ledger account category 35 which represents COGS.

Goal: Value for the goal of the COGS is set to 99% of the Actual COGS in previous period (Previous Month).

Trend: Upwards arrow if the actual COGS in current period (month) are less the actual expenses in the previous period (month) else downwards.

Status: Green (If Actual <= Goal), Yellow (If Actual <= 90% of Goal) and Red (Otherwise).

4. Gross profit :

Actual: It shows the value of the gross profit.

Calculation: It is the value obtained by Sales (33) – Sales credits (34) – Cost of goods sold (35).(33,34 and 35 are the ledger account categories.)

Goal: Value for the goal of the gross profit is set to 105% of the Actual Gross profit in previous period (Month).

Trend: Upwards arrow if the actual gross profit in current period (month) are greater than or equal to the actual expenses in the previous period (month) else downwards.

Status: Green (If Actual >= Goal), Yellow (If Actual >= 90% of Goal) and Red (Otherwise).

5. Gross profit margin:

Actual: It is the (Net Sales (33-34) – Cost of goods sold (35)) / Net sales (33-34). (Net Sales = Sales – Sales credits)(33, 34 and 35 refers to the ledger account categories.)

Goal:  Value for the goal of the gross profit margin is set to 105% of the Actual Gross profit margin in previous period (Month).

Trend: Upwards arrow if the actual gross profit margin in current period (month) are greater than or equal to the actual gross profit margin in the previous period (month) else downwards.

Status: Green (If Actual >= Goal), Yellow (If Actual >= 90% of Goal) and Red (Otherwise).

6. Net Income :

Actual: It is the (Revenue – Cost of goods sold – sales credits – Expenses).

Goal: Value for the goal of the net income is set to 105% of the Actual Gross profit in previous period (Month).

Trend: Upwards arrow if the actual net income in current period (month) are greater than or equal to the actual net income in the previous period (month) else downwards.

Status: Green (If Actual >= Goal), Yellow (If Actual >= 90% of Goal) and Red (Otherwise).

Thanks , Keep DAXING. 🙂