Budget/Actual % in Account Schedule (Show Opposite Sign)

LT93

Hello,
On the Income Statement I have "Show Opposite Sign" selected for Revenue Accounts (since they are typically Credits) - This is done on my Row Layout.

The Column Layout has 3 columns. Actual, Budget, % of Budget.
My Percent of Budget Formula is 100*(Actual/Budget).

Both my Actual and Budget amounts are negatives, but are correctly displayed as postive numbers on the Account Schedule Overview.

The issue is that my % of Budget formula is returning a negative.

Example:
Actual: 200,000
Budget: 1,000,000
% of Budget: -20.00

Please help with whatever I am missing as I cannot seem to get the percentage to calculate properly as I am actually at 20% of my budget not -20%.

Thanks

Savatage

-100*(Actual/Budget).

(Actual*-100)/Budget

LT93

That works to resolve the issue on my Revenue lines, however it causes the issue on the Expenses (flipping the sign on the percentage that is). I suppose my confusions is around why it is calculating this way in the first place. 100*(-200/-1000) is the same as 100*(200/1000) but NAV does not agree.

Savatage

are you absolutely sure that both numbers are negative at the time this calculation is being made?

LT93

Yes.

And if I set the "Show Opposite Sign" = FALSE on the Revenue Account Schedule Row in question, NAV calculates the % properly.

I however cannot leave "Show Opposite Sign" = FALSE as the reports need to generate showing Revenue as a positive number.

It is almost as if NAV is seeing only 1 of the columns as having an opposite sign (in the formula) but displays them both on the Overview with opposite signs.

LT93

Anyone else experience this? Seems to me that a lot of companies would show opposite sign on their Revenue accounts in account schedules and it also seems common to do budget comparisons in account schedules.

Thanks,

kriki

[Topic moved from 'NAV 2009' forum to 'NAV/Navision' forum]

dbo15a

LT93 wrote: »

It is almost as if NAV is seeing only 1 of the columns as having an opposite sign (in the formula) but displays them both on the Overview with opposite signs.

Nope, that's a total red herring. Show Opposite Signs - henceforth abbreviated to SOS, fittingly - controls just that: how numbers are displayed. It has no effect on the numbers used in formulae, which are always left as-is, how they're stored in NAV. It just dictates whether the final value is negated, right at the end, just before it's served up for presentation.


And that shows how easily the trees obscure the woods. It's far simpler! Your problem - and, of course, mine, until just now... - is that we were failing to follow this logic all the way. If a given row on the Account Schedule has SOS ticked on, that applies to *all* columns in that row (discounting those that have the Column Layout level SOS *also* on, cancelling out the row's).

That is: the Opposite Signs are Shown for percentages too. Whatever your calculated percentage is - correct as it may be - if the row has SOS on, that percentage will get negated for display. In this case, we don't want that.

Think of it this way: we just typed a formula, which happened to contain a division and then a multiplication by 100. NAV just calculates a number, whatever it may represent, and then (optionally) flips the sign for display. It doesn't know or care that you intend for that to be a percentage - never mind that you consider percentages to be except from SOS.

This also applies to the operator you should use to get percentages - i.e. the % operator, NUMERATOR % DENOMINATOR, which does the same divide/multiply - but then causes a % symbol to be appended in the printable report, which you don't get when rolling your own formula.

So for rows with SOS ticked on, your calculated percentage is correct, but then SOS negates it for display, thus breaking it. Makes sense, right? In its own awful way...


The problem, then, is how to reconcile that with Account Schedules in which only some of the rows have SOS ticked on, while others do not. I've not found any way to do this that doesn't come with fatal drawbacks:

• Ticking SOS on the column too only masks the problem in rows with SOS on, by cancelling the original negation; in rows with SOS off, this new negation on the Column *causes* the problem.
• So then I thought of doing the negation in the formula, instead of using SOS. But we can't negate GL Account Totalling 'formulae'. We can hide the Totalling rows and add extra Formula rows that refer to them and negate then, but that breaks drill-down, as now we only see the formula, like -P001 or something, but not the GL accounts that make it up, which is a severe loss. I guess you could leave the original, pre-negation rows visible but move them to an appendix or something, so users could still look them up and see the GL account list... but this seems unnecessarily tedious.

Without going into the Dev environment, I'm not sure that there's a way to do what we want, i.e. always get the 'right' signs, in a Schedule having both SOSs. So far, *something* always ends up the wrong way around.

My first thought was to add the row-level SOS field into the report dataset and *re*-negate percentages (which depends on the % operator and how it adds a % to the end of ColumnValuesAsText). However, while I can do this in the Report code - and used a new field so as not to change the sense of the standard ColumnValuesAsText field- that wouldn't fix the Overview or Excel export, and only works with my custom report, so it feels like a kludge, not a real solution.

The real solution is to add a controllable way to exempt desired columns from SOS, with the prime example of a use-case being the one that led us both here. My current idea is to change the row-level SOS field from a simple No/Yes to No/Yes/Yes Except %s. But I don't have sufficient Dev access to do that, yet, and I feel like MS should've thought about this in advance anyway... of course we shouldn't have to jump through such hoops to avoid broken percentages.