Role Center Charts in Dynamics NAV 2017 - Part 6         

Setup the Finance Performance Chart using account schedules.
We continue on the chart adventure, there is quite a lot to cover when it comes to charting in NAV a lot of different charts, but in this particular video we want to go over finance performance. 

This chart over here that we are looking at its similar to the other performance charts that we looked at, the inventory, sales, and purchase, however, there is some significant difference and will get into that as we are creating. But the finance performance chart is based on something called account schedules, so what I want to do us create a finance performance chart that shows the gross profit, a revenue and cost good sold for the coffee mug over periods.

Finance performance chart
I’m gonna go ahead and quickly create that so I’m going to go into select chart, and I obviously don't have this chart because I haven't created it. But in order to do that I’ll just hit new, and I’ll get a new account schedules chart set up, here I’m going to call this "coffee mug gross profit" and the account schedule that I’m going to pick, so when I’m selecting this account schedule here.

I’m actually looking up into a feature that has been within the system for a very long time, something called "account schedules", they were originally used to create financial statements and you can display them live, and now it’s been connected in to the finance performance chart.

So if I drill into here I’m going to get a lot of examples and you can see most of them are financial statement-ish, like balance sheet, income statement etc, but I’m going to create a new one, and I’m going to call it the coffee mug gross profit and in the default column layout and analysis view I’m just going to leave them blank for now and now I actually need to put in the rows so I go into edit account schedule and I get a list of rows.

This is similar to the other performance charts where we put into the item rows we could put in customers or vendors, but in this case, we are actually putting in posting accounts or straight from the chart of accounts. I'm gonna go here and put here in revenue and the totaling type is going to be a total account from the chart of accounts and I get a lookup into chart of accounts and I just pick here the total revenue that's here.

That should get me the entire revenue. And the next line I put in cost good sold that is also a totaling account straight from chart of accounts and this one is going to be total cost. And finally I have the gross profit and that is going to be a formula and that's basically, and bear with me on this one; I’m actually going to do it like: 10 which is row 10, plus row 20, Row 10 is revenue, and 20 is cost good sold.

Why am I adding these together?, the reason is that the revenue is reported on the cart of accounts as negative, and I’m actually going to turn that sign around because I want it to show positive, so I click this button here “show opposite sign” Cost Of Good sold is positive because it's debit, and I’m going to turn that around as well because I want to show the opposite, I want the cost to be negative in the chart. At the gross profit, if I and a negative and a positive, I will get a negative when the revenue is greater.

So, I want to turn that around too, and if you understand the debits and credits and positives in a chart of accounts you’ll understand which side is going into what sign and I would just recommend that you try it out just to see how it looks until you get it right. Hit ok. And ok again.

Now I’ve selected the account schedule that I just created and I need to select the column layout, which columns are now going to show for these three lines, or three rows. I’m going to create a new one here and we are going to call it Net change and the way that’s going to look it I get into layout columns setup is that there's is going to be one column on net change and the column type her is just going to be net change.

Basically what that is, is going to be the amount or the net change within the period that I’m looking at. So on the chart, I’ll probably have periods going across the x-axis and it's going to show me the total amount for those periods, for those three lines. So I only want to see that. I could actually have more, here and I would then just show up next to each other on the chart but for simplicity, we are just going to pick one.

Hit ok. And going here and pick the newly created net change, and now it actually populates the measures, what are the measures I have: the revenue net change this is a column, cost good sold net change and gross profit net change, now I would like to show the gross profit as a line, so you’ll see it in a bit, it looks nicer, and then we have it like this so I hit ok, ok again and here I have my chart.

I’m going to look at months, so now I'm looking at the January 2018 and I can see my revenue is 159,000 and my cost good sold is 196,000 so my gross profit is negative, this is not good. Let's look at previous periods. I can actually scroll intro a previous year over here and go all the way down to February, so you can see that February 2017 things were nice, our gross profit is positive and keeps being positive all the way through until this January moment here and that might just mean that the amount of started out bad and might end up good. But this is a very simple example of how to set up the finance performance chart using account schedules.

I hope you learned something, until next time.