Dynamics NAV Financials

account schedules, financial reporting, dimensions, budgets and all the rest of the fun, fun world of Microsoft Dynamics NAV from a (former) Controller who still uses the software every day

  • Account Schedule Examples
  • Account Schedule Formulas
  • Popular Series
  • Services
  • About

Blogiversary Top 20 (#20) Use show opposite sign to reflect net income correctly on an income statement using NAV account schedules

Posted: April 2, 2013 | Author: kpeters | Filed under: Uncategorized | Tags: Account Schedules, financial statement, income statement, net income, show opposite sign, tips and tricks | Leave a comment

We’re celebrating our one year blogiversary by reposting the Top 20 Most Viewed in the last year, as determined by you, our readers! Follow this link to see the entire list. Enjoy!

One of the concepts I see students in my NAV 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.

oppsign1

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.

oppsign2

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.

oppsign3

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.

oppsign4

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.

oppsign5

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.

oppsign6

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.

oppsign7

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.

oppsign8

The end result gets us correct results every time.

oppsign9

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.

oppsign10


Use show opposite sign to reflect net income correctly on an income statement using NAV account schedules

Posted: December 11, 2012 | Author: kpeters | Filed under: Uncategorized | Tags: Account Schedules, accounting, financial statement, income statement, net income, show opposite sign, tips and tricks, two consecutive operators | Leave a comment

One of the concepts I see students in my NAV 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.

oppsign1

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.

oppsign2

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.

oppsign3

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.

oppsign4

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.

oppsign5

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.

oppsign6

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.

oppsign7

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.

oppsign8

The end result gets us correct results every time.

oppsign9

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.

oppsign10

This posting is one of the Top 20 Most Viewed in the last year! Follow this link to see the entire list.


TwitterYouTubeFacebookGoogleLinkedInEmailRSS
MVP_Logo_Preferred_Cyan300_RGB_72ppi
All Stars_NAVUG(1)
400dpiLogo

Blog Stats

  • 607,353 visits

Enter your email address to follow this blog and receive notifications of new posts.

Join 475 other followers

Top Posts & Pages

  • Account Schedule Formulas
  • Account Schedule Examples
  • NAV AS 101 Lesson 10: Comparison Period v. Comparison Date
  • NAV Account Schedules 101
  • NAV AS 101 Lesson 14: Exporting to Excel
  • Month to Date vs. Year to Date
  • NAV default dimensions and value postings applied to master data (part 6 of 15)
  • Detailed Budget
  • NAV Error Message: G/L Account No. '' does not exist
  • Tips and tricks for a flawless budget load in NAV
My Tweets

Account Schedules accounts payable accounts receivable aging analysis by dimensions Analysis Reports analysis views balance at date BREP budgets chart of accounts Classic Client code mandatory column layout community comparison date formula comparison period formula consolidation Convergence CustomerSource dimensions document date dual monitors due date Dynamics ERP Excel export to excel financial reporting financial statement fiscal year general ledger global glued column increase header item ledger entries JET journal entry keyboard shortcut learning Management Reporter manuals Microsoft NAV NAV 2013 NAVUG NAVUG Academy net change networking Office2013 paperless payment terms pivot table posting accounts PowerPivot productivity query tool reconciliation account reporting tool ROI row setup RTC set base for percent setup options shortcut show opposite sign SSRS tips and tricks totaling type training trial balance user group user level configuration user poll video

Blogroll

  • Microsoft Dynamics NAV Team Blog
  • Microsoft Connect
  • Microsoft Dynamics CustomerSource
  • I Love NAV
  • Microsoft Dynamics Community
  • NAVUG

Archives

  • December 2020
  • September 2020
  • November 2019
  • October 2019
  • August 2019
  • May 2019
  • March 2019
  • April 2018
  • December 2017
  • October 2017
  • April 2017
  • March 2016
  • December 2015
  • October 2015
  • September 2015
  • August 2015
  • July 2015
  • June 2015
  • May 2015
  • March 2015
  • January 2015
  • December 2014
  • September 2014
  • July 2014
  • June 2014
  • May 2014
  • April 2014
  • February 2014
  • January 2014
  • December 2013
  • November 2013
  • October 2013
  • September 2013
  • August 2013
  • July 2013
  • June 2013
  • May 2013
  • April 2013
  • March 2013
  • February 2013
  • January 2013
  • December 2012
  • November 2012
  • October 2012
  • September 2012
  • August 2012
  • July 2012
  • June 2012
  • May 2012
  • April 2012
Blog at WordPress.com. Mid Mo Design.
  • Follow Following
    • Dynamics NAV Financials
    • Join 475 other followers
    • Already have a WordPress.com account? Log in now.
    • Dynamics NAV Financials
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...