OK, today we are talking about tracking projects in Excel for financial purposes.
Accounting software and ERP software is usually time-consuming to implement and costs a lot. For small operations, using Excel can be enough, as long as your country’s transaction documentation legislation allows that. What I mean is that most countries would require you to use a database that wouldn’t allow for the direct deletion of data. And spreadsheets allow that. But anyway, if it’s a small operation or just an internal project we’re tracking, let’s see how we are gonna approach it.
A few years back I was doing a freelance project, which was to be included in the books of a bigger company, so they required me to prepare documentation for the project, as if it was a separate entity. As I had no access to accounting software and am pretty comfortable in Excel, this is the approach I took.
I had a few files set-up. Let’s start with our List of accounts.
Here’s our List of accounts and their mapping. All accounts get a numerical id (e.g. petty cash will be 501, cash in bank will be 502, etc.). Then accounts get their name and mapping – I separated the mapping in 3 categories – Type (BS/IS – whether it’s a balance sheet or an income statement account), Class (Assets, Equity, Liabilities, Revenues, Expenses – the main categories in the BS and IS) and FS Account Line (those will be the IS/BS accounts – Receivables, Payables, Sales, Administrative expenses, etc.).
This will be the source of categorical data for all other files, thus ensuring we are properly using the same accounts everywhere.
Our second file will be for the actual Transactions.
Here is where we record all transactions, by typing journal entries – type the accounts, a description, the amount, using Debit and Credit columns – i.e. Dt 601 Stationary expenses, Ct 501 Cash on hand, amount 5.50 euro, description New stapler, etc. To keep it consistent with our List of accounts, we set it up so that only the account numbers are written here, and we use VLOOKUP to get the account names.
We include an initial financing transaction with the allocated cash for the project. However, when we copy the formulas down, it gives a rather annoying #N/A error. Therefore let’s add a IFERROR check to our formula.
Now there are no more unpleasant errors and our transactions file looks nice and clean.
And also, let’s add a date column, as it’s important to keep track of when transactions occurred.
The third file is our Trial balance – it would get all accounts from the List of accounts and then SUMIFS all the transactions for this account that we booked in the transactions file, by having a Debit Turnover and Credit Turnover columns and then a calculated Closing balance. The check here is that the Debit and Credit balances should always equal out, as in regular trial balances.
First we just take all the account numbers. We even let some 0’s at the bottom, so that if we add a new account it will appear here automatically.
Then we get the names of the accounts.
We are starting with opening balances of 0, and we get the turnovers with SUMIFS from the Transactions file.
And let’s calculate the closing balance and check if it equals out to zero.
Let’s now check if adding a new transaction will update our Trial balance properly. We go back to our Transactions file and ass 25 euro expense for Pencils.
And here’s our Trial balance, automatically updated!
OK, let’s play around and add some more transactions.
And here’s our updated Trial balance.
Our fourth and final file will include a Balance Sheet and an Income Statement, which we construct by the same Accounts used in our List of accounts for the mapping.
And after we setup our financial statements, we see that we made a mistake. In order for our Trial balance to provide data to our financial statement file, we need to include the mapping from our List of Accounts in the Trial Balance.
We then use SUMIFS again to get the closing balances of the accounts from the transactions. We use the same logic to get the Debit and Credit turnovers for the Income Statement.
Remember, that Liabilities and Equity should be with a (-) sign in front, the same for Expenses.
It is still a pretty basic setup, which we can later improve by adding analytical accounts for Suppliers, Inventory types, etc., but it’s the same idea as described above.
I would still only suggest this approach if you are looking to keep track of small amounts of transactions for a simple operation. Otherwise, it can get quite complex and get out of hand. Also, be sure to check your local legislation requirements, to avoid getting into trouble.
Other than that, it’s fun to create such small apps with Excel, as you can constantly improve on them and grow them, to be more inclusive of all your documentation needs.
I hope that was helpful for you. Look forward for other Excel project ideas and if you want me to focus on something more specific, drop me a line.
Enjoy and happy hacking 🙂