Variance % - Actual vs Budget Management Reporter

(0) Share

Report

Posted on by Community Member

Printing column for Actual, Budget, Variance and Variance %. Using a simple formula to compute the variance % as Variance column/Budget column. However, when Budget = 0, MR prints the variance as 0% (because the variance $/Budget $ = 0 when Budget = 0), though it should be 100% or (100%) depending on if the variance is positive or negative to income. Tried a formula but unable to get it to work. Is there a standard approach to handling this?

*This post is locked for comments

I have the same question (0)

All responses (13)

Answers (2)

Suggested answer

MG-16101311-0 26,225 on at

Like (0)

Report

Change the Variance % formula to:

(IF Budget_Col > 0 THEN Variance_Col / Budget_Col ELSE 1.00)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Mariano, yeah I tried that before I posted this issue. The challenge is that when Actual and Budget are 0, the % should be 0. And unless the % is 0, the row will print - 0 Actual, 0 Budget, 0 Variance and 100%. Then there's the issue where either 100% or (100%) should print, depending on whether the variance is positive or negative to income. Hard to believe there isn't a standard approach to handling this.

Was this reply helpful? Yes No
MG-16101311-0 26,225 on at

Like (0)

Report

In addition to what I suggested previously, change the calculation column print control to XCR. You will also want to change the calculation priority. In the settings view, in the Report Definition select the "Perform row calculation first then column" option.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Had already tried both of those. With or without XCR in the column (which does exist in the Variance $ column and works), the 100% is always positive. The Calculations Priority in Report Settings is already set to perform row calculation first.

Was this reply helpful? Yes No
MG-16101311-0 26,225 on at

Like (0)

Report

Have you set the proper control at the row level for Credits vs Debits?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Yes, works fine, but appears that the IF THEN ELSE statement forces a value of 1 without regard for sign.

Was this reply helpful? Yes No
MG-16101311-0 26,225 on at

Like (0)

Report

You can always tweak the formula, that's the least of the issues:

(IF Budget_Col <> 0 THEN Variance_Col / Budget_Col ELSE IF Variance_Col < 0 THEN -1.00 ELSE 1.00)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Tried that exact IF, but when saving the column format, MR shows an error and won't allow me to save. That's the core, issue, can't do nested IFs or ELSE IFs

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Well, I just entered it again, and got MR to save. Now I just need to add another ELSE to deal with the Variance = 0 scenario with another ELSE path

Was this reply helpful? Yes No
serbach 571 on at

Like (0)

Report

GRG,

Reminds me of a Favorable/(Unfavorable) dodge I tried at my last job. We wanted the comparison of REVENUE amounts between this year and last year to show FAVORABLE when current year revenue was greater than last year; and UNfavorable when current year revenue was less than last year. Also wanted the comparison of EXPENSE amounts between this year and last year to show UNfavorable when current year expenses were greater than last year; and FAVORABLE when current year expenses were less than last year.

This was made a trifle easier because we arranged the Current Year amounts in rows directly above Last Year amounts:

REVENUE Category 1

Current Year 55,000

Last Year 50,000

$ Variance 5,000

% Variance 10%

REVENUE Category 2

Current Year 15,000

Last Year 25,000

$ Variance (10,000)

% Variance (40%)

EXPENSE Category 1

Current Year 25,000

Last Year 20,000

$ Variance (5,000)

% Variance (25%)

EXPENSE Category 2

Current Year 5,000

Last Year 20,000

$ Variance 15,000

% Variance 75%

The SUM rows for $ Variance were computed differently for REVENUE categories vs. EXPENSE categories:

REVENUE current_year_amount - last_year_amount

EXPENSE last_year_amount - current_year_amount

The CAL % Variance rows were all calculated this way:

IF last_year_amount < 0 THEN -(dollar_variance/last_year_amount) ELSE dollar_variance/last_year_amount

Maybe this sort of computation would work for you... though working in Columns vs. Rows could be trickier.

Sincerely,

Steve Erbach

Green Bay, WI

Was this reply helpful? Yes No