Dynamics 365 Business Central AS 101 Lesson 24: When to change the sign
One of the concepts I see students in my Dynamics 365 Business Central account schedules class get stuck on is exactly how to use the show opposite sign function when constructing an income statement. It can be a bit mysterious why it behaves the way it does, and it seems to defy the rules of accounting and basic arithmetic all at the same time. Once you understand why it works the way it does, you’ll know how to use it in the future. From the example shown here, we see a very basic income statement.
Basic income statement
Without using any numbers yet, let’s all agree on some basic calculations:
Revenue – Cost of Sales = Gross Profit $
Gross Profit $ / Revenue = Gross Profit %
Gross Profit $ – Operating Expenses = Net Operating Income (Loss)
Putting these basic calculations into the design view for this account schedule gives the result below. Notice we’re not going to use the show opposite sign function yet, or make any changes to the basic formulas we just defined.
Example income statement in design view
When we look at the report view for this account schedule we see that we’ve got a significantly wrong result. The revenue is showing as a negative number and all of the calculations below it are incorrect.
Without using show opposite sign, the resulting calculations are incorrect
Now, for the accountants out there, you may already have guessed what the problem is. When accounting for revenue, these numbers are assigned a credit value, which shows up as a negative. Most financial statement end users want to see this reflected as a positive number. This is where we need to use show opposite sign. Let’s add that in to the design view, as shown below.
Applying show opposite sign to Revenue
When we look at our new result, you would think all would be correct. We’ve corrected the accounting problem by flipping the sign. However, when you look at the result, we get the following.
Even though we flipped the sign on Revenue, Gross Profit is still wrong
Why in the heck is that gross profit number not right? It seems we’ve got very simple math we’re dealing with here, what could be the problem? Many folks at this point go for the simple solution – just select show opposite sign on the gross profit $ line.
Many people simply select show opposite sign on the gross profit $ line
Unfortunately, this doesn’t work either, because you are flipping the sign on both the revenue value as well as the cost of sales value and you simply get the opposite of the same wrong number you got above.
This doesn’t fix anything; it only shows the opposite of the number that’s already there
The reason this is happening is because the show opposite sign function is doing exactly what it says it is doing, it is SHOWING the opposite sign. This is really only a presentational mask, and when calculations are done against the value, revenue is still calculating as a negative number, even though it appears on your report as a positive number. This little bit of arithmetic-defying programming can make an accountant a little cranky, but it is easy to solve with a little mathematic trickery. In order to fix this, we need to put into place a two-part solution. The first part we already know; we must select show opposite sign for our revenue accounts. The second part manipulates the Gross Profit $ and Gross Profit % as shown below, changing the value of the revenue account within the equation in order to calculate correctly.
Instead of selecting show opposite sign, change the sign in the calculation itself
The end result gets us correct results every time.
Voila! Everything looks correct.
For those of you who are really paying attention, there is one more math mystery in the show opposite sign puzzle. Why have I chosen to show the calculation for Gross Profit % as -A3/A1 instead of simply using A3/-A1? Again, we’ve got to defy the programming with math tricks. Choosing A3/-A1 gives the error shown below. Get around it by moving the negative value to the other side of the equation and you’re good to go.
If we try putting a minus sign ‘-’ immediately after division ‘/’, Business Central gives an error
This posting is part of the Dynamics 365 Business Central Account Schedules 101 series. Find the entire list of lessons here.
Don’t forget to visit the Account Schedule Formulas and Account Schedule Examples pages if you’re looking for even more ideas on how to improve your financial reporting using account schedules with Dynamics 365 Business Central.