All Excel Accounting and Bookkeeping Solution – Template

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!

Journal

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.

Ledger

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.

Trial Balance

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.

Income Statement

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.

Balance Sheet

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!