67 - Account Schedules in Dynamics NAV 2017 - Part 2

67 - Account Schedules In Dynamics NAV 2017 - Part 2             

We’ve been talking about accounts schedules, this is the second video on that topic. What I’m going to get into today is more refining the account schedule for a particular purpose, and taking a look at the column layouts.

I’m logged in as the account manager, and in that role center if you go into the analysis tab on the ribbon I can click on account schedules, of course, you can look it up on the search up here on the right-hand corner. But that's also a way to get to it.

I got a list of account schedules, I’m gonna create a new one and this one is going to be called the profit for retail just PFR and the default column layer is going to be balance only, just like before and I go into edit account schedule. So if I’m going a little bit fast on this one you can go to the previous video where I’m actually going through how to create this.

I'm gonna insert GL accounts and I'm only interested in this case, in retail accounts so I’m gonna take here the sales retail ok, and I am going to take in the cost retail, so retail is everything else obviously there is a lot of stuff in here, but I'm only going to worry about these two.

So I brought in the sales retail, cost retail, obviously sales retail minus cost retail should give me the gross profit of retail, if I click here on overview now I can see I have the actual amounts in here, so let's add in the profit, the gross profit, so I'm just gonna put that as 80,000 a let's leave that open here to give a line, 80,001 gross profit.

So here I want to use a formula, so I haven't used that before I just used posting accounts out of the chart accounts but we can actually use a lot of different things as rows and in this case I like to use the formula, what the formula does, it references anything that's on the account schedule, so you can see that all of the lines here are numbered or at least the lines that have amounts, and I can reference them in my formula.

In this case, what I want to do is reference the total sales of retail which is 44,500+ the total cost of retail 54,900. Now, why am I adding this? you would think that the way to go would be to take the total sales - the total cost and then you would have profit, but this is coming straight from the chart of accounts, so the total sales are actually gonna be negative, and the total cost is gonna be positive. And so we add that up, we get the differences and if the revenue is more than the cost then is going to be negative, which is a good thing in accounting. But let's take a look at that if it actually works.

Click here overview, and you can see we have gross profit, and it's negative which is good because the sales are 1.8 million the cost is 1.4 and then we have a profit of 332,000 now, we would like to see all the numbers that are good positive, and any credits per se be negative. So, for example, all of these are good sales even though they are showing off as negative because they are credits.

Can we flip the sign around to make it more user-friendly? and I would like to flip this sign around on this one too because a positive profit is a good profit, so I'll go back here, and I show this actually on the previous video, as well I can just go ahead and show opposite sign on all of the sales.

Like that, and some people don't like to have it like that, they actually like to see the true value from accounting, but I think on most cases you wanna see the positive as being good or normal and negative being sort of a reversal and then on the gross profit we turn around as well, the cost will leave as it is, and what I'm gonna do here to make this a little bit nicer, just gonna put this as underline and I want to bold the gross profit and then go ahead and hit overview. So the underline is not gonna show in this but if you print it out it will, and now we actually have a sale and cost and gross profit and numbers look nice.

So what I was gonna go into now, is a different column layer than just balance, click out of here and go into edit column layer, so what that does it brings up a lot of the standard column layouts here, and we have a lot of them in with the demo company, I’m gonna create a new one so I can explain how this works.

I'm creating new. And let's put PYTD Period Year to date. Ok so If I go into here “edit column layout” so what I’m trying to do here is I want to show the Net change for a period, and then I want to see the year to date for those accounts. So if we are looking at one month, I will see that month and I can also see all the year up until that month, so well call the first one “net change NC” ,and all I have to do here is leave it at NC, I have options right here, and then I have another one caller YTD year to date, and in here I want to actually change this to say Balance at date.

Since these are income statement accounts, the year to date is going to be the balance at date because we’ve closed the previous year and we wouldn't see any numbers from there. So will see that change, I’ll hit ok on that. So this is gonna be this one, hit ok.

So if I actually go ahead and hit overview now, I see this, and now I can change the column layout to be the one I just created, this one, and now you can see that if I look at the particular month which is January, I can see that that is that, and then the year to date is much more.

If I go into the next period, In this case actually you can see that it makes more sense to look at something in 2017. If we look at 2018 this is the demo company, it’s obvious that 2017 has not been closed because the year to date is bigger than the net change; so interesting thing to tell Microsoft, but if I go into the previous period I can see that things work perfectly.