Excel based Profit and Loss for multiple activities in one account

Another tool that I have created and would like to share for use, comment, and improvement is a spreadsheet that allows you to track multiple activities running through one bank account for purposes of generating separate profit and loss statements for each activity.  I think it is much quicker and simpler than trying to teach someone to properly use an accounting software and can be a great alternative to the dreaded “green sheets”.

I have noticed others replicate the “green sheet” thinking into Excel spreadsheets used to track profit and loss for light accounting in small business entities.  I think this becomes cumbersome unless you have a monitor 20 miles wide and there is a lot of rework when you want to add an account, thus accounts are often not added and more stuff gets lumped into miscellaneous.

My workbook is set up like a check register where you assign account numbers and activity codes to each transaction.  Then using the power of the “sumifs” function (available in Excel 2007 and newer), Excel can pull the desired information from the activity register into the profit and loss statement.  The version that I made for Excel 2003 and earlier uses the sum function as an array to accomplish the same functionality but runs considerably slower than the version based on the sumifs function.

Also included is a pivot table that summarizes information by account, allowing you to drill down to the detail on an account by account basis.  The 2003 and older version of the PL works with Google Documents rather well, with the exception of the pivot table.

Questions, comments, or suggestions, feel free to let me know what you “really” think.

PL Worksheet with Sumifs, for Excel 2007 or newer

PL Worksheet with Sum array, for Excel 2003 and older