account schedule variance sign

LibertyMountain

on our profit/loss account schedule, we have programmed the revenue accounts to "show opposite sign". but when the revenue variance is shown, the revenue variance is now the opposite sign too (i.e. a favorable variance is negative instead of positive). is it possible to get everything I want:

1. revenues show positive numbers on profit/loss account schedule; and
2. favorable revenue variance also shows a positive number? (without affecting cost variance signage ... you can switch the signage in the column layout when you define the variance, but then it makes all the favorable cost variances negative ... we don't want that either).

because right now the sign of the revenue account variances is backwards (i.e. a positive number indicates a unfavorable variance). Thanks.

jannestig

Isn't this just boolean logic ?

In an example cronus database Account 3051 is show with -7000000 for 2010
I open account schedules insert all accounts and use standard column layout making sure to note that the show oppisite mark on the rows is not ticked
I run the overview NAV shows it as +7000000

So if i want it to show the - sign i would just tick it and wow what do you know it works.

This can also be affecte if you are have selected show Only debits or credits etc

What do you mean by we programmed it to show opposite sign ? If you have some code in the system messing things up then thats not the systems fault

LibertyMountain

when I say that we programmed the account schedule to show opposite sign ... maybe programmed was the wrong word. we defined the acc. schedule to show opposite sign, by checking the "show opposite sign" field.

So revenues (credit balance) normally show as a negative number. by checking "show opposite sign", now it shows as a positive number. ... BUT ... say in 2008 we had 700,000 in revenues. and 2009 we had 800,000 in revenues, and I have a column layout defined to show 2008, 2009, and the variance. The variance shows as -100,000 (unfavorable). That's not what I want, when this is indeed a favorable variance.

so it looks like I either have the revenues show a negative number (and my variance will show the correct sign). OR I can "show opposite sign" on my revenue accounts to show as a positive number (but that messes up the variance calculation). Seems I can't have it both ways.

I'm trying to get the accountants to use the account schedule exclusively, but because of this error they insist on wasting time downloading everything to spreadsheets and then manually switching the sign of the revenue variance amounts. Maybe that's our only solution.

bstoyano

hi,
Have in mind that checking the Show opposite sign field does not affect the real values, just the way they are shown. Hence all arithmetic operations are executed using the real values - in your case -800 000 - (-700 000) = -100 000
If you change the sign for the whole line, that will change the sign of the formula result. Are you sure you have the right formula?

Also, if you check the Show opposite sign field for the result column, that will add another minus to your result, and then you will see -100 000.

LibertyMountain

The formulas are setup correctly. I don't think there is a way around my problem. Here's the difference:

do not check "show opposite sign"
2009 2008 Variance
Rev -800 -700 100 (favorable)
Cost 500 450 -50 (unfavorable)
Profit -300 -250 50 (favorable)

(because of the way debits and credits work, a variance should be defined as 2008-2009 so that a positive number shows a favorable variance)

check "show opposite sign" on Rev and Profit:
2009 2008 Variance
Rev 800 700 -100 (wrong sign ... this is really a favorable variance)
Cost 500 450 -50 (sign is ok)
Profit 300 250 -50 (wrong sign ... this is really a favorable variance)

So my conclusion is that "show opposite sign" is a good tool to use when you define your account schedule provide that you don't expect to use a column layout that shows variances. because your variance amount will have the wrong sign on each acc. schedule line where "show opposite sign" = TRUE.

bstoyano

[-X
I think there is a mistake in your methodology. If you try to estimate the change in Revenue, then you should calculate 2009-2008. And a positive sign here will be interpreted as growth (favourable). In your formula, however, you are calculating 2008-2009. In this case if you have growth, the formula shows a negative amount, i.e. you sold less in 2008 in comparison to 2009.
And this has nothing to do with credits and debits, since you use measures of identical type for each line.

So in brief, think over your methodology once again. For me the mistake is there. Navision calculation is fine. Try re-writing your formulas as 2009-2008 and after that adjust the signs of each line.

LibertyMountain

I don't mean to sound argumentative, but I've been over this with 2 accountants, and been through my notes from school. I think the methodology is correct:

revenue is a credit. credits are negative. if 2009 was bigger than 2008, and I use your formula, I get (-800) - (-700) = -100 (suggesting an unfavorable balance). That's not what happened. the variance was favorable because revenue grew.

likewise with costs which are debits. debits are positive. if 2009 was bigger than 2008, and I use your formula, I get (500) - (450) = 50 (suggesting a favorable balance -- also not what happened).

I know it seems backwards. but that's the way variance analysis works.

My conclusion still stands: if you let revenues retain their negative signage, then variance analysis works perfectly (positive variances are favorable, negative variances are unfavorable, regardless of costs/revenues or debits/credits). But the moment you reverse the sign of a line on your account schedule, then the variance analysis gets reversed too. I can't find a way around this. ](*,)

so short answer: tell everyone who looks at the financial statements to just get used to the fact that revenues are negative numbers and profit is a negative number. you have to put up with that if you want the variance analysis to work. (But if you're not showing variances on the statements, then go ahead and switch the signs up to your heart's content.)

bstoyano

But you change the Opposite Sign field only for those of the lines in Account schedule that you need to convert the sign. Somehow I can't follow you. :?:

LibertyMountain

Does anybody else experience the trouble I am having? In short here is the issue:

I have column 1 = this year, column 2 = last year, column 3 = variance. The variance column works perfectly (i.e. positive variance is favorable, negative variance is unfavorable) and shows the data I expect as long as I don't check the "show opposite sign" field on any of the account schedule lines.

But as we know, revenues are credits (negative numbers). And the business users for whom I produce these reports don't want to see a negative number. So for the revenues (and profit lines) on the account schedule, I check the "show opposite sign" field. And the result: column 1 (this year) shows the opposite sign (this is good). column 2 (last year) shows the opposite sign (this is good). But now column 3 (the variance) also shows the opposite sign (this is bad ](*,) ). Because now a favorable variance shows as unfavorable and vice versa.

Example of what I'm talking about is above on my post on Thu Mar 11, 2010 7:46 pm.

this is a major flaw in my opinion of the account schedule. And I wish MS would address this. We are on NAV 2009 R2, and this is still a bug, with no workaround that I am aware of.

rsaritzky

You're right on this one. The reason, as far as I can figure, is that the legacy of the NAV product is that was developed in Denmark. European financial reporting conventions are different, and they are not as hung up about showing revenues as positive numbers as we are here in the US. The FRx financial reporting addon that is also available for NAV does not have this limitation.

The column layout formula capability in NAV is very rudimentary, so it cannot determine that if the difference of 2 revenue column amounts is negative, then it's a positive variance, and if the difference of 2 expense column amounts is negative, it's a negative variance.

I actually did a customization for a client a number of years ago on version 4.0 that addressed this issue. If you are working with a NAV partner, they should be able to modify the reporting module to handle variances.

The only workaround that I can think of (and not a very good one) is to use the "Export to Excel" feature in the Account Schedule Overview and manually flip the signs of the expense variances and Net Income Variance - but you'd have to do that every time you generate the report. You could create Excel formulas and copy them from the prior month's spreasheet to the current month, but again, it's not a great workaround.

Good luck