web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

The Next Big Bite | Aussie Classics & Starting Small with Clay Wesener | EP04
Check out the Community Spotlight Video Series
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Management Reporter - ...
Microsoft Dynamics GP (Archived)

Management Reporter - IF-THEN-ELSE statement in Column Definitions

(0) ShareShare
ReportReport
Posted on by amd1973 90

I want to use an IF-THEN-ELSE statement in column definitions that references account numbers that are found in row definitions. Basically, I want a statement that would look at the account number and if it in the revenue account range, subtract Budget from Actual, but if not to subtract Actual from Budget. This will allow me to consistently have worse than Budget variances as negative numbers and better than budget variances as positive numbers.

Is this possible within management reporter?

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    We have a code called XCR which is designed to do this. When you use the XCR code in a column, it will automatically flip the sign of budget-actual calculations for any rows that are coded with a normal credit balance (C). To get this to work right, you need to do the following:

    -In the row, verify that each credit balance account has C coded into the Normal Balance column. Any calculations based on these rows should also typically have this C code.

    -The calculation in the column is typically Budget - Actual. Use the Print Control cell to select XCR.

  • bridgetg Profile Picture
    bridgetg 20 on at

    Our Company budget statements were already set up as you have suggested above, however, when we generate the report the variance GP doesn't calculate correctly, see example below.

    Actual    Budget   Var F(U)

    Sales         112         350         238 

    COS           129.9     269.6      139.7

    GP             -18          80.4        98.3

    Please advise. 

  • bridgetg Profile Picture
    bridgetg 20 on at

    Sorry that was a wrong example!!  I've been playing with it for a few days now.  The example below is what I get when I generate it as you have suggested.

    Actual    Budget   Var F(U)

    Sales         112         350         (238)

    COS           129.9     269.6      139.7

    GP             (18)         80.4        (377.7)

     

  • amd1973 Profile Picture
    amd1973 90 on at

    Thanks - The XCR corrected the issue on the P&L through Net Income. However there are now issues with add backs below Net Income that allow us to get to EBITDA. For example, interest expense add backs are a favorable variance when actual is above budget (since it adds back more to income), yet it comes up as a negative (unfavorable) variance since with XCR, the column formula needs to be Budget - Actual for consistency. Has anyone found a way around this so that add back variances come up properly under Net Income?

  • Community Member Profile Picture
    Community Member on at

    It may be a matter of coding the lines in question to C for credit balance. This works on calculation lines as well.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

The Next Big Bite | Aussie Classics & Starting Small with Clay Wesener | EP04
Check out the Community Spotlight Video Series

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the June Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans