I’ve made an Excel template for filling out T accounts. For you non-accounting folks, a T account is a method to trace accounting transactions through the accounts on the general ledger. Hey, wake up! This post isn’t finished yet!
I’ve made a T account template at least three times in my Excel career. They never work out. They never make it easier than pencil-to-paper or marker-to-whiteboard. It doesn’t seem to stop me from trying, though, and now I’m making my latest attempt available to you. First, let’s look at this huge image (click to embiggen).
The top left section (C2:F11) is where you write a brief description for up to 10 transactions. Column C is the transaction number and can’t be edited. Column D is where you type the brief description. Column E is overflow for column D because I hate merged cells. Column F is the general ledger period. You can put them all in the same GL Period if it’s not applicable to your transactions. You don’t have to use this section. That is, you can leave off the descriptions and the periods, but good luck trying to figure out what you were thinking.
In this example, the transactions are listed in the order they will happen over time. This series of transactions represent a sale where we get a kickback from the vendor and we’re passing that savings on to the customer. First, we buy the inventory from the vendor. Next, we ship it to the our customer. At that point we need to make our costs correct by accruing the rebate we’re entitled to from the vendor. We pay the vendor, get paid by the customer, and finally get our rebate from the Vendor.
The main section of the workbook is a 3×3 grid of T accounts. This is where the magic happens. The top left cell of each T account is a financials statement classification number. It’s a data validation dropdown containing the numbers 1-7.
- Assets
- Liabilities
- Equity
- Sales
- Cost of Goods Sold
- Expenses
- Other Income and Expenses
That financial statement classification number is replicated down the hidden column B (and H and N) for formula purposes.
To the right of the financial statement classification number is the (merged) cell where you type the account name. The account name is only for your reference and has no bearing on any formulas in the model.
The lighter green section down the left side of the T account is a series of data validation dropdowns that allow you to select the transaction number. The main white area of the T account that’s divided vertically is the area where you record your debits and credits. At the bottom of the T account, the debits and credits are summed up and the net debit or credit is displayed.
There are two other areas to the right of the T accounts grid. The top area shows the effects on the major sections of your balance sheet and income statement. It expands to the right for however many periods you have. The financial statement classification number at the top left of the T account determines where your transaction ends up in this area. And, obviously, the period you identify in the transactions section determine the column.
The bottom area is a list of the 10 transactions and the net debit or credit by transaction. If any of these are not zero, you’re missing a piece of the transaction.
Please leave your thoughts and suggestions in the comments.
You can download TAccounts.zip
I’m sorry, what did you say? I fell asleep.
Too much financial stuff for this former English Lit major. Can you put it in terms I can understand? Like, Beowulf and his peeps party, they fall alseep, a nasty beastie creeps into the hall, kills a bunch of guys, Beowulf rallies his folks, they go a-huntin’, and come back with the arm of said dead beastie? Financial transactions using a lit vernacular I can dig… Beowulf is the Inventory, Grendel is the customer, Grendel’s yanked out arm is the Cost Of Goods Sold, that kind of thing.
:-)
Dick, that’s pretty cool, and some of the most gripping content you’ve posted to date. :p (I think I’m on my 3rd incarnation of a T-Account form as well.)
One of my versions (not the current one) had a spin button that I could use to highlight the transaction across all the T-Accounts as well. (So if you wanted to see where the Pay Vendor transaction showed up, you’d spin it to 4 and it would highlight all the transactions.) This become important to us when we started to try and figure out the who was collecting tax in a contra arrangement. I think each transaction was posted against 4-8 accounts at a time, very messy.
I like the balance sheet/income statement and period aspects too. That’s pretty clever, and something my set probably should have.
In practice, I found that I needed more than 9 accounts to run through a scenario complicated enough to warrant me tracking it in Excel, although generally less lines of transactions per account. Our T-Account template holds 15 accounts, but only 6 lines of detail in each. It would make the sheet wider, but you could easily move your balance check next to your note table, and also move the Balance Sheet/Income Statement summary up to the top if you wanted to add accounts horizontally.
Sorry @Scott, I’ve only read one non-fiction book so my options are a bit limited. I’ll try to come up with a Holmes/Watson dialog the next time.
@Ken: I’ve been resisting putting macros in this workbook, but that spin button idea may tip the scales. And with macros I could make the grid more dynamic – 15 shorter accounts that could expand when necessary. On to version 2.
Great job. Can’t wait to see version 2!
I have a version of a T account that I created that will do what you are looking for! Email phxcoyotes2013@gmail.com if you would like a copy.
Dick, this is awesome. Thank you for sharing. How’s version 2 coming along?
I use this all the time, but I haven’t made any changes to it. Perpetual beta, I guess.
Hi Dick,
This is an amazing tool – thank you for sharing!
While it’s less spiffy than a spin button, if you wanted to incorporate Ken’s suggestion without using a macro, you could simply have a cell drop-down where users can select a transaction number to highlight. Then, you can use conditional formatting “Use a formula to determine which cells to format” so that it matches the transaction number in the T account with the selected number.
For example, I used cell K6 to select the transaction number to highlight. For the field =$D$16:$E$53, my rule is:
=AND($C16=$K$6,ISNUMBER($F16))
Then there are similar rules for columns I:K and O:Q. The ISNUMBER part is important because otherwise, if you select transaction 1-7, all your account names (currently green) of that classification will change color.
A second adaptation that I made is in column J, I put in “Type X to show” and for each row 2:11, I can indicate whether or not it is visible in the T accounts and incorporated in your BS/IS summaries. Then when I entered my amounts in the T accounts, I used an IF statement. Using the first debit in your top left T account as an example, the formula would be =IF(VLOOKUP(C16,$C$2:$J$11,8,FALSE)=”X”,amount,0)
The zero was necessary so I didn’t break the BS/IS formulas, but I didn’t want a lot of zeros either so I also added conditional formatting where zero values show up with white text so they are camouflaged.
Then I put in this guy to tell you the description of your selected transaction, or tell you that you can’t highlight a transaction that isn’t visible:
=IF(K6=””,””,IF(VLOOKUP(K6,C2:J11,8,FALSE)=””,”Error: Cannot highlight transactions not shown.”,VLOOKUP(K6,C2:D11,2,FALSE)))
Oh – as part of my “Type X to show” thing, I also had to change the formula to find the period.
Before:
=IF(NOT(ISBLANK(C16)),VLOOKUP(C16,$C$2:$F$11,4,FALSE),””)
After:
=IF(SUM(D16:E16)>0,VLOOKUP(C16,$C$2:$F$11,4,FALSE),””)
I hope those changes are a fraction as helpful as your template! Thank you again!
This is so perfect, user friendly once you figure it out by the instructions, and EXACTLY what I was looking for. You are a genious! LOL Thank you for sharing this for people like me. I love T-Accounts.
How can I make the font larger?
The sheet is protected, but there’s no password. Alt+t+p+p will unprotect it and you can modify how you like. The protection is only there so I don’t mess stuff up on accident.
Although if you can’t read it, you might consider changing the zoom on the worksheet rather than changing the font size.
This was a really useful excel template once you figure out how to enter the data and notes.
Thanks. Every time I use that (several times a year) I think how non-intuitive the UI is because I have to relearn it every time – and I wrote it.
Hey there. I just started school again after many years off, and of course my printer breaks so I can’t print out my T account sheets to manually fill in. This template definitely helps. How can I add an additional T account?
@Eric – it’s a tedious process. First, it’s best to increase the T’s vertically. Copy A42:R56 and paste in R56. Then all the formulas in V:AG reference each T account individually so you have to add three +SUMPRODUCTS() on to the end of each. Then some of the formulas in U also refer to each T account separately.
Somebody should really write a macro to do this.
Here’s round 1 of a configurable TAccounts template. I’m sure there are plenty of bugs, but I wanted to get it out there so people could find them for me. If you’re interested in more Ts that the current template…
http://dailydoseofexcel.com/excel/TAccounts.xlsm
Thank you Dick. It is really helpful to have this template.