Watch the recording by clicking on the play button
Transcript
0:01
Welcome, everybody. Thank you for attending this webinar on account schedules, and just a few things to start with. All attendees will be on mute and will remain for the whole webinar.
0:13
Please use the question box if you have any questions related to the topic, and all questions will be answered after the demo.
0:19
If we do run out of time, or if the question requires a longer answer, then we’ll answer those questions offline after the webinar. Use the chat box for any questions related to the actual webinar.
0:32
So, for example, if you can’t hear the audio or the presenter’s going too fast, please note that the link to view the webinar recording will be sent to all attendees after the demo.
0:44
OK, so this is the sixth in a series of the Top five Things Webinars. These are small amount of key points to focus on about Microsoft Dynamics NAV. We do have the whole series that you can sign up for, and we’ll be sending out e-mails to register for these webinars, and I’ll show you a list of them at the end too. OK.
1:06
My name is Karen, and I’ll be facilitating the demo today. I am an Account Manager here at TVision and my background is 17 years of software account management experience working for ERP companies.
1:18
Ian is going to be doing the demo today. He’s a support consultant here at TVision. And he’s been working at TVision for about 1.5 years and been using NAV since 2000.
1:31
OK, so we choose account schedules as this is a very useful way of gaining insight into your financial data. However, it’s not that well known.
1:40
Whilst you might have an overview of them in your training, many people forget about using them in their day-to-day work. Account schedules analyses figures in GL accounts, and compares general ledger entries with general ledger budget entries. The results can also be displayed on your homepage. For example, cash flow chart. So here’s five things that we, at TVision, believe are the best to introduce using account schedules in NAV.
2:06
First of all, what are they?
2:08
Why are they useful?
2:09
What templates are currently available within NAV?
2:13
How do you customize them or create them yourself? And how should you decide which method of reporting to use? Obviously, there are reports within NAV, there’s third party solutions, and there’s accounts schedules, which one is most appropriate for your business?
2:29
OK, so before the actual demo, I’d like to launch a poll just quickly to understand how you’re using account schedules. So, if you wouldn’t mind putting in your results, that’d be brilliant.
3:13
OK, so, it looks like the results of our poll is, some people use them, they know what they’re doing. Some use standard ones, but a good majority don’t use them at all, at the moment.
3:30
OK, so if you wouldn’t mind just holding on for just one second, we’re just going to change over to the demo.
3:55
OK, that’s great, so we’re now going to go over to the demo – thank you. Hi, thanks, Karen. First thing up, what are account schedules? Erm, account schedules, basically, they’re a way of looking at your general ledger. You can look at entries within the general ledger, you can look at budget entries that have been posted in your general ledger.
4:17
Erm, they also understand dimensions. So, transactions that have been posted with dimensions. You can view those, and account schedules as well.
4:26
I’m using a 2018 database to do this demo, so, just quickly, this is the Chart of Accounts that I’m going to be working from. It’s a standard chart of accounts, that come with a Cronos database.
4:37
All the balance sheet entries up here at the top, lets me scroll down, we start coming to the income and expense accounts.
4:45
This is what we’re going to base our demo reports on. Now, I’m logged as the accounting manager.
4:51
When I log in as accounting manager, I have a nice little shortcut button that gets me to account schedules.
4:56
If you’re not logged in as accounting managing, you can always go up to search box, type in account schedules, and it takes you to the same place.
5:05
If we have a look, immediately we see, these are all the accounts schedules that already exist on the system. These come as standard with a Cronos install.
5:17
You can open any of them to see how they work, and to see how they’ve been created. And you can edit them, and make them look the way you want. I’m going to open one, let’s do the income statement.
5:29
If I go to edit account schedule, I ignore the warning, we can see how this account schedule has been set up. It’s a kind of busy looking page, but it’s relatively simple.
5:42
Each row has a unique identifier. And we give it a description.
5:47
We say what we’re going to show on this row. So, on these first accounts, it says posting account.
5:54
So we’re going to show values from our general ledger, and here we put in a filter to say which accounts do we want to show.
6:02
You can put it in a single account, or you can put in multiple.
6:06
Then, at the end, we put in a formula. And there I said, everything from P one to P eight, so those unique identifiers, it’s just going to add them up and put them in a total income row.
6:19
Now, we do the same thing going down.
6:24
This, these descriptions that are on here, you can type those in by hand, you can check the default the system has given you, you can change them.
6:32
What do I want to show on each row? I’m showing the next change in the GL account. And what do I want to show? I can show the net amount, I can show the debits and credits.
6:43
I could flip around the signs, so positive becomes negative, and negative becomes positive.
6:48
And I can choose when I want to show the rows. I get a few interesting options here.
6:54
I can always show them, only show them they’re not zero, show when they’re positive and negative.
7:00
So I’ve get a bit of flexibility on how I present my information.
7:05
And then some formatting, put it in bold, italics, underline.
7:08
Skip to a new page, that only works if you’re actually printing out. And I can filter based on some of
7:16
the dimensions 3 and 4. I can put some filters in.
7:19
And there’s a double underline that I can put in at the end when we’re in filters.
7:23
So, just looking at this, this is how it’s all set up.
7:27
I can close that and go to overview, and we can see what it does with results. So I’m looking at the income statement. That’s the one that I selected. I’m looking at it by year, I’ve chosen 2019.
7:40
I can quickly go back and see 2018 instead. And I’m looking at the net change layout. So it’s giving me a column just for the net change.
7:49
So, there’s those rows, P one to P 8, it’s brought through the values in the general ledger account. And on the total it’s added those up and it’s put in the total for us.
8:02
Scroll to the bottom, and there’s another formula at the bottom… net income.
8:09
This is the default view of net change, we can always change how we want to look at this. We can add more columns, but leave the rows the same.
8:20
So, one of the defaults that come with the system, while we’re looking at net change. We can look at change versus budget. So, let’s select that one. Let’s see what it does.
8:33
Soon as I click off the field, you can see my column headings have changed.
8:37
Let’s just choose the 2018 update.
8:39
And I’m seeing the net change in those accounts…
8:45
For 2018, working exactly the same way as it was. But now the budgets have been entered against those standard accounts side by side. Which helps … with the variance…
8:59
We’ll have a look at how we create column layouts just now. We can see the grand totals down at the bottom, with budget at 78, we got 156, so that’s about 100% variance.
9:12
So, we can see that everything works.
9:16
The gross has stayed the same, even though I did change the column layouts. So I can choose different column layouts. It’s always going to have the same rows.
9:26
Let’s just have a look at another one.
9:29
We’ve got this year compared to last year.
9:32
Let’s have a look at that one.
9:35
Click off the field, and the column headings have changed. I’m looking at, let’s change this to 2019.
9:41
So, in the first column, this is 2019’s numbers. In this column, this is 2018 numbers with a variance and the percentage difference between the two.
9:52
We’ve only got one month for 2019. That’s why its a little number compared to the row of 2018.
9:58
But you can see that you keep looking at the same set of accounts, but you just change the way that you’re presenting the information.
10:07
Um, these are pretty easy to create your own. You can take these standards.
10:13
You can modify them. But I’m going to show you how to create one from scratch. So let’s quickly move on.
10:21
We give it a code, or a name. I’m going to call this one TVTCOA. I’m going to show you the entire chart of accounts. We give it a name ‘demo COA’, and we choose a default column layout to use. Remember once we’re looking in it, we can change the column layout as we want.
10:36
So let’s go to edit account schedule, and see what’s in there.
10:43
And it’s given me a nice blank page. I can manually start adding the lines here, if I wanted to.
10:50
NAVs quite nice. It gives us a shortcut for you to do this. So let’s use insert GL accounts.
10:57
And I want to bring the entire chart of accounts. So I’m going to highlight everything.
11:01
Then say, OK. As soon as I click OK, it populates my account schedule, and it’s brought through everything that was in my Chart of Accounts, in the same order, with the same formatting.
11:15
But these descriptions…
11:17
Perhaps they’re a little bit vague, you could change them if you wanted to.
11:23
For each row, it’s telling us whether it’s bringing, and let’s change the name of this one, just to demonstrate, that we can give it a slightly different name.
11:34
And we can see, it’s bringing through all these posting accounts. We can close this. And let’s have a see what it actually looks like.
11:44
That took us less than a minute to create. Here’s the results of what we’ve created. So it’s brought through all of those descriptions, we can see the one that I’ve changed.
11:55
I’ll change the year to 2018.
11:56
Much more information in there. And it brings through the values that are in those relevant accounts.
12:05
Let’s change the view, and look at it against budget just for fun.
12:11
As soon as I click off, again, I’m looking at my entire chart of accounts versus budget. Doesn’t mean too much until you come down to the income and expense accounts.
12:20
And immediately, I can see, for all my income and expenses, how much did I spend, and what was budgeted, what’s the variance.
12:31
Um, this is a nice view, but it’s very detailed.
12:36
Perhaps I want to present at board level, and they’re not so interested in the nitty gritty of all these individual posting accounts. They might be more interested in just looking at the totals, for a more Executive Overview.
12:50
So let’s go and change this one. I’m going to edit the account schedule we created.
12:57
I’m going to delete all of these roles. Just hit delete.
12:58
Yep.
13:03
And once again, I’m going to use the insert tool, but this time, I’m not going to select everything.13:09
I’m going to put a footer on the existing accounts that I’ve seen. So I need to change this… click on Account types.
13:19
Select account types.
13:21
And I’m going to show the ones that are an end totals.
13:25
So, as you can see, I can choose heading, begin totals, totals across the account. I’ve just chosen the end ones.
13:35
Let me highlight everything.
13:38
Then say OK. And we’ve got the same chart of accounts we’ve been looking at previously, but now we’ve taken out those minor details, but we just got the totals. We can see which accounts it’s going to be pulling from, our chart of accounts. Let’s have a look at that in overview mode.
13:59
There it is, the same information, again, represented in more of a summary form for 2018.
14:05
Those are the same totals we were looking at, and now this is something that I could present at a high level without all those intricate details.
14:16
We’ve got some formulas in calculate totals. That’s come straight from our chart of accounts. Then again, we can change the column layout.
14:26
Look at it in a slightly different way.
14:28
Um, on the system, we do have dimensions posted against our accounts.
14:40
So, instead of looking at these, and maybe there isn’t one that has done the department dimensions that we want to look at, so we could create our own, then you would look at the same Chart of accounts in summary form. But we would have column headings for all the departments that we have.
15:00
It’s pretty easy to create something this. So let’s create a new column layout. And we’ll give it a name.
15:06
And a description.
15:10
And once we’ve got that, we can click on edit the column layout set up.
15:18
Again, it gives us a blank screen, and we can choose what we want. Each row gets a unique number.
15:26
And there’s our two global dimensions – we’re interested in department. So we’ve got three departments here.
15:32
One for Admin.
15:35
One for production.
15:37
And we’ve got another one for Sales.
15:42
So we put all three of those rows on.
15:44
We give them a nice description – something better than ADM. Number two, that’s production.
15:53
Then row number three, is for sales.
15:58
And we can also, we can put in a total at the end. So let’s add another row, we’ll call it total.
16:01
And this time it’s not a net change column. This is formula. And over in the formula bar, I want
16:11
everything from 1 to 3, so those unique rows one to three, we’re going to add them up and show that in the total.
16:18
That’s okay. Make sure it’s highlighted.
16:22
Now choose that column layout. And now we’re looking, again, the same account schedule – but this time we’re looking at it by dimension.
16:32
So, I can see, from my … team, my marketing and sales team.
16:36
The expenses and the total of the expenses. And again, that was pretty quick. We don’t have to show all of the departments, we can just show one or two of them if I want to give a report like this to department heads. So that they can track their expenditure going forward. And I can create a separate one for each department to look at.
17:02
A nice total one, perhaps, for board level or accounting managers to look at to do their reports.
17:07
Let’s create another one, but this time, I’m going to show you that we can use date … in our column headings. So let’s look at one by month.
17:17
It’s going to be monthly view.
17:26
And I need to click on edit column layout. So I’m going to want headings across the top for January to December. My financial year for this company runs from January to December.
17:35
So I just need to add some rows in here.
17:40
May and June.
17:43
I’m going to stop at June. I could carry on all the way down to December. You don’t want to sit there watching me typing.
17:49
And we use a date formula in here. So to figure out what’s January, it’s the first month in my financial year, so I put in this simple formula – FY(1).
18:00
I’m going to copy this down into the other cells. And for January it’s given me two. So I’m just going to change that, two, three, four, five and six.
18:24
And again, we could go all the way down to December. We could put a total at the end, if we gave each column a unique number to use in the formula.
18:28
So let’s make sure that’s highlighted.
18:38
Now I see the same chart of accounts, but this time its split up by months for the financial year 2018…
18:49
We could put in formulas to see month by month growth, but I can check spending on a month by month basis.
19:00
I can highlight all of this copy, copy Excel and paste. On the newer versions, I’ve got the export to Excel button.
19:05
Once I put it into Excel, I can format it, make it look pretty. Use the formulas to make it into a beautiful pie chart or something to include as part of my financial reports.
19:18
Now we’ve looked at just copying straight from the chart of accounts so far. I want to create a kind of sales report where I’m going to have rows for sales and then rows for cost of sales, and at the end work out a basic gross profit.
19:33
So let’s create a new one.
19:36
Again, same formula. Give it a name – TVT Sales.
19:41
Give it a description.
19:46
And we’ll choose a default column layout. We’ll stick with net change – remember you can always change that.
20:01
And let’s go ahead and edit this account schedule. And again, if I knew which accounts I want to use, I can put them in manually, or I can use the assist button. But this time I’m not going to select everything.
20:08
I’m going to scroll down until I come to my sales accounts in the GL.
20:21
Here we are. Here’s my revenue accounts, I’ve got my sales of retail, raw materials, jobs etc.
20:26
I’m going to select these accounts, I’ll choose the first one by clicking on it. Then I just hold down my mouse and drag down to select all the accounts that I want.
20:35
Keep going, my chart of sales are there, here are my Cost of Sales. Keep going down … some variance accounts, we’ll include those. And there’s my total cost of sales. So I’ve got them highlighted. I say okay. I didn’t have to select all of them. I could have just selected the management accounts and put them in one by one.
21:00
But here they are, it’s brought them through with their formatting. And there’s my total revenue, its row number 6995 – that’s important. My total costs is row number 7995.
21:14
So let’s put in that formula at the end to see what my sales plus costs are. And again, this is a formula – so I’ll make a change there. And it shows 6995 – and I need to add to that 7995. Why am I adding instead of subtracting? Because this is the general ledger, so its debits and credits and I add them together to get the total.
21:42
And we’ll see what effect that has now. We’ve got it highlighted.
21:48
Let’s go and have a look at the overview to see what it’s done.
21:52
So, those are the accounts that I was interested in seeing. I’ve got my sales accounts, I’ve got my cost of sales accounts. Way down at the bottom, I’ve put 2019, let’s put 2018. It’s a much more interesting year. And, right at the bottom I’ve calculated that gross profit – which was the sales plus the cost of sales. But you’ll notice this has come through with a negative number. Now an accountant knows that a negative number is actually good. That’s showing your gross profit. If I gave that to a sales manager, he might not be impressed with a negative number, so let’s go and change that.
22:35
So, let’s show opposite sign function. I can go and change the sign on all of these accounts. To make those negative numbers look positive. I’m just going to change the total as a demonstration.
22:47
We’ll say flip the sign, I didn’t have to change the formula at all. I’m just saying present it the opposite way round.
22:54
Now let’s go see what affect that has had.
22:58
Then, once again, scroll down to the bottom.
23:06
And that negative 3.39 million has turned into a positive 3.39 million. And that’s probably going to make my sales director a lot happier seeing they’ve made a profit.
23:18
That’s a pretty simple, basic sales report. We could have presented information in a different order.
23:25
But what I would like to do, because we have different areas within our system, I want to present this by area. Now we created the department view. Which was filtering by dimensions.
23:38
Area is also a dimension. Taking the liberty of already setting up.
23:46
Edit column layout, let’s just see what I get.
23:54
So I’ve got my areas, Europe North, non EU, EU south – North America, South America, and simple total. Using that area dimension I can go through the codes, and I put a formula in that to get up all of them.
24:01
So let’s make sure it’s highlighted.
24:05
Then suddenly, now, I have a simple sales report.
24:09
With the same information it had before, but this time it’s split up by area, with the grand total.
24:16
Um, we can scroll down to this, we can see discounts that have been given by area.
24:24
Very nice way of tracking performance – see all our costs for the different types of sales.
24:30
Then, we can see there are positive numbers, so we know we’ve made profit.
24:35
Not so fantastic in north Europe.
24:37
And we can drill down on any of these numbers, just by clicking on the ellipsis, and we can get the details of where these numbers came from, if we need to do any kind of further kind of analysis.
24:49
I’ve shown here again, all the nitty gritty of the individual accounts. Perhaps, if you’re presenting this to sales director, he would only want to see those summary accounts.
25:01
And we do the same thing as we did with our chart of accounts. And we just exclude the posting accounts and we would only show these grand totals.
25:13
We could have changed the sign on all of these rows up here to show positive numbers. Just to make our presentation a little bit neater.
25:24
But basically, this is the power of account schedules.
25:27
We’ve gone through. We’ve created a couple of different examples.
25:34
We’ve added some different column layouts. We’ve seen how flexible they are. We’ve looked at reverses budget, we’ve put in by date…
25:44
So that’s pretty much all I have. Karen, have we had any questions come through?
25:49
OK, so if you’d like to ask any questions about what you’ve seen, or if there’s any questions about account schedules that we haven’t covered yet, you just want to use the question box and submit them. We’ll just wait a minute or two and then we’ll answer them.
26:09
We have, so there’s a question. What if I add a new account to my Chart of Accounts?
26:15
Good question.
26:17
If you’re just using the begin and end totals in your chart of accounts, and you’re using defaults, you can add a new account and it will appear on your account schedules.
26:29
Or you can manually enter that new account on the line, you saw how easy it was to edit on the account schedule.
26:37
You don’t have to rewrite them from scratch. You can just add the new account that you put in there.
26:45
What’s the benefit of using an account schedules over a Jet Report? There’s an interesting question.
26:53
Jet reports is a third party product that you can use, to get pretty much the same, information you’re getting from your account schedules, but you’d be doing that in Excel.
27:03
Um, nice thing about using account schedules is, it’s one source of information.
27:10
If we’re using Jet Reports and it’s in Excel, people can fiddle with formulas. Not saying it’s on purpose, but by mistake they move a cell and forget to change the formula.
27:22
On an account schedule, it’s very fixed.
27:25
Then you could create a reporting pack, and at the end of every month, you report from your account schedules. Each department might use Jet Reports, but you’ve got some numbers to refer back to.
27:37
To say, you know, this is the Bible, these are the numbers you should be reporting.
27:42
Can you exclude a line with zero balance? Yep. That’s pretty easy to do. That was one of the options when creating our account schedule and there’s a column to say ‘Show when’. You can say ‘Always show’ it. ‘Show it when it’s positive’, ‘Show it when it’s negative’.
27:57
Or show it only when it’s on zero.
28:00
So that makes it nice and neat. And then, what are the benefits of account schedules over Excel? Well, again, that’s very similar to the Jet reports.
28:13
So, if you’re a small company, you might just want to use your chart of accounts, filter for a date range, copy and paste into Excel, and do your reporting from there. And that’s quite a valid thing to do.
28:27
It is very nice if you’ve got an account schedule that does that, and you’ve got any formulas in there that you want to use.
28:33
You can always make sure that those formulas are consistent. So you know, somebody downloads it in Excel, they work out a net profit, but they use it based on mark up, someone else does it next time, and they do it based on margin instead.
28:48
They get slightly different percentages.
28:51
If you’ve got an account schedule, you can say, we’ll always use this formula. So you’re always consistent.
28:59
I see there’s quite a few more questions coming in. I don’t really have time to answer everything, so I’m going to pass back to Karen.
29:06
When I’m finished, I’ll go through those questions, and I’ll respond individually to each of you with an answer to your question.
29:14
OK, that’s great, Thanks very much, Ian.
29:17
So we’d like to leave you with the five key takeaways about account schedules, which we hope will assist you with your analysis and reporting in NAV.
29:26
So, really, when you’re considering what type of reporting to use, you really do need to understand what your desired output is. What you actually want to report on?
29:38
Do you have the right data in NAV? If you haven’t set up your dimensions correctly, then your account schedules may not be accurate.
29:46
So once you’ve done the first two steps, check if there’s a template already created in account scheduled, that could be sufficient for your requirements.
29:54
If you need to amend the template or create your own, do you have the knowledge and the time to actually dedicate doing this?
30:00
If you’d like to learn more about account schedules, or if you have a set of templates that you’d like to have set up for you, please feel free to contact us, because we can provide training or consulting services to create schedules.
30:14
OK, so we hope you found this webinar interesting and informative. As mentioned previously, this is the sixth in a series of webinars. All our webinars are available on our website to actually download previous recordings.
30:27
We always send out e-mails to register to the next one, and we also put in reminders within our newsletters as well.
30:36
So, thank you for attending, once I close this session a survey will appear, and it’d be great if you could respond with some feedback. As Ian was saying, if there’s any questions which we haven’t answered, we will respond back to you directly. Thank you very much.
