Its been a while you got any update on Excel but I hope this much awaited template plus complete how to series will follow in next few weeks explaining every bit that how its done from scratch will worth it!
Yes! I have finally achieved an all Excel accounting and bookkeeping solution that is capable enough to expand and flex easily and with as minimal VBA programming as possible. Here is the sneak peak of it:
With this template I hope users especially small scale entity can benefit but also help users understand several techniques along the process and enable them to do a lot more on their with the knowledge acquired.
First and foremost objective of making this accounting solution is to help Excel learners inspire that things as complex as bookkeeping can be done with relative ease in Excel that are quite effective as well.
Second objective was two fold; I want to stay away from VBA as much as possible and rely on tools that are developed and available in friendlier user interface just like Excel itself. I wanted even the very beginner to learn and apply advanced level tools like Power Query and give them confidence that they don’t have to be an advanced level Excel user to use these advanced level tools.
Third and ultimate objective is that users of this template/solution not only get the bookkeeping job done but also learn how to make similar solutions and even make changes to this template to suit their needs.
In short, bookkeeping solution is one of the best example for me to explain Excel techniques in a meaningful way and to understand how different aspects come together to get a particular job done. Instead of explaining formulas, codes and tools separately and leaving it on learners to think how these separately discussed topics can help them.
Lets have a look at parts of this template and briefly mention the techniques used to make part of this excel accounting solution.
Chart of Accounts
Most critical part of accounting and bookkeeping solution. It cannot be ignored as it not only help streamline the records but also reliably controlling and maintaining the financial transactions.
I relied heavily on Excel tables as they do a lot of heavy lifting for us. They are named, have structured referencing system, can dynamically handle the data without breaking the process… I can literally go on and on!
And the best part is that you can feed tables to power query and then manipulate the structure and data itself the way you want by simply clicking most of the time!
Journals is one part which is still bittersweet. Sweet for the reason that I can use Excel tables for this as well and can easily use power query to alter the output the way I want, but bitter because it bland and colorless. I really want it to be aesthetically pleasing.
Its not that I wasn’t able to have it all lit its just that I am unable to do it reliably as Excel tables have a history with conditional formatting. It was an interesting find for me as well and the problem long standing still and has not been fixed even in latest running version of Excel. I will explain it in detail in tutorials.
Until Journal, things were pretty easy and I had all the headroom available. However, I wasn’t expecting that this part is a lot demanding to work flawlessly yet look the way accountants want i.e. formatting.
For this I had to take some help of VBA code but it is still very basic. Automating this part was really interesting and enjoyed the process.
Extracting trial balance from ledgers was easier than I expected. With a little help from VLOOKUP and VBA I was able to get the values for each of the defined accounts in Chart of Accounts list.
Getting this report in proper format with only the concerned items of income and expense required the data design to be carried right from the beginning. With codes and category classes in place, generating and updating this report dynamically got really easy. Here I used name manager to great extent for my ease and in VBA code. It will quite a fun learning this part in tutorials
For income statement I have few more ideas to implement to make it even more flexible and adaptable for different types of users but I fear that it might delay it a little further and I can’t wait to share everything else with you so I will work on this part in the next revision of this template.
This report had one challenge. Getting all the usual balance sheet items was relatively easy, however inclusion of profit/loss figure straight from the income statement made me redesign the approach.
To make this accounting solution, as I mentioned earlier, I used Excel table and Power query to great extent. Dealing with power query was probably the most amazing part of the process for me. Touching the “M” language and writing formulas in it was a new thing for me as well. Even writing formulas in power query interface is not same as writing formulas in Excel. So I hope the learners will enjoy power query part as much as I did.
So far the template is in beta phase as I have couple of ideas to finish and implement to mark it as version 1. And the buyers will receive the revisions to this solution for free for quite a long long time!