Dynamics Community Forum Thread Details

Calculation for columns in Management Reporter

In Management reporter, working on an income statement. I have a field to calculate the % of variance between our current actual income/expenses and the budget.

Two questions:

Is it possible to have a calculated field (using a formula) that can apply to both income and expense line items with the same formula?

In prior months, the formula I am using prompts inaccurate calculations. For example, there will be no values in the row (0 current expenses, 0 budgeted) but will provide a %value. Why?

This is the current formula i use, along with an example of the columns i'm working with.

IF B<>0 THEN C/B ELSE IF C<0 THEN -1 ELSE 1

A	B	C	D
Actual Expenses	Budgeted Expense	Fav(Unfavorable) (B-A)	%Variance
150	300	=300-150	(formula above here)

Categories:

Microsoft Dynamics GP

Hi,

If you have a CALC column, then the calculation in that column would apply to all rows. Both the income and expense rows would use the calculation that was in that column.

If we look at the example you have, the first part of the calculation is the condition: IF B <> 0. Column B has an amount of 300 so the condition is true. MR will then do the THEN part of the calculation: C/B or 150/300. Column D should have a value of .5.

If the Actual Expenses is 0 and the Budgeted Expense is 0, then the condition is false as B is equal to 0. MR will then do the ELSE part of the calculation: IF C <0 THEN -1 ELSE 1. Here we have another condition: IF C <0. C would be 0 in this case, so the condition is false. MR will do the ELSE part, so column D would display 1.

Quick Links

Leaderboard