Dynamics 365 Business Central AS 101 Lesson 25: What else can I do with Account Schedules?
I hope by now in this series you’ve realized there are a lot of other things you can do with account schedules other than produce base financial statements. Just in case you really don’t believe me, here’s my top 10 list of reports I produce using account schedules other than a trial balance, income statement, and balance sheet. Hopefully this list will spark some ideas of what else you might be able to do using account schedules!
#1 – Statement of Cash Flows
Ok, ok, I know this is a base financial statement, but there are soooo many people who are not using account schedules to produce this one. It can be done, I promise! The best advice I can give you is to make sure you understand your SCF well enough to be able to use your trial balance in order to construct it using Excel first. If you can do that, then you know enough to be able to build it in account schedules.
#2 – Gross Margin Reports
Since my company uses dimensions on our items at a high level grouping, as well as at a 1:1 level with the item, I’ve got some great gross margin reports that allow me to see what our margin is a number of different ways. I can see what larger groups of items are doing and I can also get down to the level of an individual item.
#3 – Reports that use the General Ledger account number in the columns
This is one of the shortcomings of Dynamics 365 Business Central account schedules. You simply cannot design column layouts that utilize general ledger account numbers in the columns. I had someone request a general ledger walk forward from gross sales to net sales, showing columns for individual GL accounts like discounts and returns in the columns and then dimensions for our items in the rows. This would be a very ugly report if I was limited to only a few GL accounts in the rows and many many columns full of dimensions. I needed to twist this one around, and I did, by putting it into Excel and using Paste Special and Transpose.
#3 – Reports that answer specific questions like “How much did we spend on this activity for this project for this team”?
This starts to get into heavy dimension territory, and for this report, activity/project/team are all designated dimensions I have in my system in order to make this report possible.
#4 – Lead Schedules
When I review my accounts at the end of the year, and prepare for my auditors to arrive, I prepare lead schedules for both the balance sheet as well as the income statement, so I can easily review groupings of like accounts together and get quick year over year comparisons. I use the same lead schedule to monitor clearing accounts that need to have zero balances and also to make sure blocked accounts stayed blocked with a zero balance throughout the year. The lead schedules gives the auditors a consistent checklist of accounts to reference as they work through the annual audit and makes the inevitable variance discussions easier to handle.
#5 – Consolidated Financial Statements
I have more than one company to keep track of, and I do so using multiple companies in the same Dynamics 365 Business Central database, and then use the consolidation feature to move all the financial detail to a single consolidation company to use for reporting. The consolidation company and the subsidiary companies all have a consistent shared chart of accounts, so it is very simple for each company to have consistent shared account schedules between them. If you look at an income statement in company A it looks exactly the same as company B because I copied and pasted the schedule from one company to another. Even if your chart of accounts is not the same between companies, you can still use consistent account schedules between companies with a little extra work needed to map accounts to the right lines.
#6 – Sales Every Day of the Month
We’ve got some great reports set up that give us daily information in Dynamics 365 Business Central, and can break down, by dimension, what’s going on with things like sales so we can quickly identify trends.
#7 – Many Years of Information at a Time
I think a lot of times we get into the rut of what’s happened this month or this year, but when you start to work on forecasts or annual budgeting, bringing the view out to a higher level is a great tool. Don’t forget that you can look at many account schedules for a longer time frame as long as you’ve been gathering information in Dynamics 365 Business Central for that long.
#8 – Budgets!
Other than once a year when I use the budgeting feature to load a new annual budget, I use account schedules to look at budgets. It’s a great way to get a quick read on what the plan was without having to switch out of my primary reporting tool.
#9 – Ratios
Need to see Accounts Receivable Turns or Days Sales in Receivables? What about Inventory Turns? There are a lot of ratios that can be measured using account schedules!
#10 Simple Queries
I’ve never really formalized this method, but I actually use it a lot. If someone asks me for detail on what we’ve spent on a project or what portion of a certain expense account a particular team has used, I can whip up a quick temporary schedule that will get me the info I need. I’ve found this particularly useful when needing to solve dimension problems. Using the ” (two single quotes) as a designator for finding “blank” dimensions was a great tool back in the day before I understood how to completely prevent that from happening, and by using an account schedule to show designated dimensions as well as blank dimensions, I was able to solve that type of problem quickly.
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.