Intro > Screen Shots > Part A - Income Statement and Transaction Ledger (Schedule C)
ZAP Accounting Software is a two file, open source software system for Small Business Accounting. Part A of the system is this Income Statement and Transaction Ledger file. Part B of the system contains full Balance Sheet Data.
File #1 - aka Part A - is a "template" that is designed to be used for one calendar year. At the end of the year, a copy of this file is made, the new file is cleared of transactions and the process repeats itself. (If there is a newer version of this file available and you'd like to use it instead of the same file from last year, you would download it, make minor changes to the Chart of Accounts as necessary for your setup, and get going.)
File #2 - aka Part B -- The Balance Sheet Data file is not an "annual template" like Part A. It will be added to year over year.
Given Part A , is intended to be used as a "template" for each year, this system was not inherently intended to support multi-year transaction reporting. However, that can in fact be achieved easily if desired. This file "could be used" for multiple years at a time with some small mods or special attention, but we'd actually just recommend copying and pasting the data into an archive file for multi-year reporting. Another option could include importing sheets from each annual file. Most folks do have a large need to do multi-year reporting, and the subtle benefits of starting fresh each year are in fact large, but if multi-year reporting is desired, it is achievable with relative ease.
File #1 - aka Part A - is a "template" that is designed to be used for one calendar year. At the end of the year, a copy of this file is made, the new file is cleared of transactions and the process repeats itself. (If there is a newer version of this file available and you'd like to use it instead of the same file from last year, you would download it, make minor changes to the Chart of Accounts as necessary for your setup, and get going.)
File #2 - aka Part B -- The Balance Sheet Data file is not an "annual template" like Part A. It will be added to year over year.
Given Part A , is intended to be used as a "template" for each year, this system was not inherently intended to support multi-year transaction reporting. However, that can in fact be achieved easily if desired. This file "could be used" for multiple years at a time with some small mods or special attention, but we'd actually just recommend copying and pasting the data into an archive file for multi-year reporting. Another option could include importing sheets from each annual file. Most folks do have a large need to do multi-year reporting, and the subtle benefits of starting fresh each year are in fact large, but if multi-year reporting is desired, it is achievable with relative ease.
Home Tab
Each DIY Software Solution will have a "Home" tab as the first tab in the workbook. This gives the general outline of what is contained in the file. This is all manually configured on this home tab. It is not pro grammatically doing inventory of the tabs. We do in fact have technology built to auto inventory the tabs, but there are some issues with connecting that to the tab descriptions and such, and we want to keep this simple. Further down on this page are custom settings for the user as well as other general info about this particular software solution. The block quote below is found lower on this home page...
The "Tabs" across the bottom are different "worksheets" in this "workbook". The photos below will move one Tab at a time from left to right...
Notes Tab
The Notes tabs is a place for users to take notes... simple enough. You don't have to use this tab. You can make notes where ever you want. Notes might include how you prefer to categorize certain transactions, or what order you prefer to do things in, or when you last downloaded transactions or questions for you CPA, etc etc. |
Chart of Accounts Tab
This Chart of Accounts tab looks very "spreadsheet-ish". We realize that. It gets more software looking as we go along. One reason this looks that way is because this Chart of Accounts is not only a listing of accounts, columns A and C are used for drop down/select boxes (aka Validation Lists in Google Sheets), and Columns A and B together and Columns C and D together are in fact lookup tables for the transaction aggregator. So, while these are totally customizable lists, they also serve several functions and thus, the very "boxy", spreadsheet-ish look.
Checking Ledger
The Checking Ledger is comprised of two sections -- the first three columns are the transaction section and the next 4 columns are the extended info about the transaction you need to add for proper accounting purposes. You can either manually enter each transaction or you can download them from your bank and then paste the proper info into the first three columns. No auto connection to your banks here, and that is NOT a bad thing. |
The $3010.10 number in the last Blue Cell is the starting balance on 1/1/2016. If you print that out, it will show on the top of every page since it is in the header section. That may be a little confusing since it isn't the balance carrying over from the prior page. To get tab level sorting to work this was the best we could come up with.
If you have more than one checking account that is easily accommodated:
If you have more than one checking account that is easily accommodated:
- Add that account to the Chart of Accounts. Maybe you would name them Checking1 and Checking2 or CheckingWorking and CheckingDeposits, whatever works. The names in the chart of Accounts MUST match the tab names.
- Add a Tab to the work book with the same name you entered in the Chart of Accounts
- You would modify the first active cell on the Transaction Aggregator to include the additional tab.
Credit Card Ledger
The Credit Card Ledger is identical in nature to the checking ledger. If you have multiple credit cards you want to track (other than corporate cards under a single account), you add those just like described above with the additional checking accounts. Corporate credit card accounts are very "special". They require some special handling when it comes to downloading and entering transactions. Corporate credit cards are tracked in a single account, but we identify the card holder to the transaction for various reasons. |
Transaction Aggregator
The Transaction Aggregator Tab is where some magic happens.
The "condensing" equation for putting the transactions from various tabs together on this tab is in cell A3. Because A3 was highlighted when this screen shot was taken, the formula is visible in the equation bar in the image below. If an additional credit card was used, the core part of that equation might change from {Checking!A3:F;'Credit Card'!A3:F} to {Checking!A3:F;'Credit Card'!A3:F;'Credit Card2'!A3:F}, for example. For those familiar with SQL Union commands, this is a spreadsheet equivalent. This is like creating a temporary table for reporting purposes.
- The transactions from all Ledgers are condensed into a single table for reporting.
- Account types are assigned to both parts of the transaction (in double entry accounting form), and a value is then designated for Income Expense reporting that is either the same as the transaction value or the same value with the opposite sign. It's this sign flipping that is related to debits and credits that has prevented most from coming up with a simple spreadsheet accounting solution earlier.
The "condensing" equation for putting the transactions from various tabs together on this tab is in cell A3. Because A3 was highlighted when this screen shot was taken, the formula is visible in the equation bar in the image below. If an additional credit card was used, the core part of that equation might change from {Checking!A3:F;'Credit Card'!A3:F} to {Checking!A3:F;'Credit Card'!A3:F;'Credit Card2'!A3:F}, for example. For those familiar with SQL Union commands, this is a spreadsheet equivalent. This is like creating a temporary table for reporting purposes.
Income Statement
The income statement represents a simple but powerful reporting system that is mostly automated. The income and Expense Categories are being read directly from the chart of accounts and those account names are then being feed into a sumif function to get aggregated totals for a given income/expense/equity account. Once you see this done once, it won't seem so far out. When this screen shot was taken, the active cell was D19, and you can see the equation in that cell in the formula bar. The reporting blocks on the right are very powerful. Since this is just a spreadsheet, those can be modified and added to on the fly |
Notice that the Income Statement first row is row 12?
Above that row is a hidden section of the spreadsheet. I refer to these when they are included as "Hidden Headers". the are noted with yellow bars with the words HiddenHeaderStart and HiddenHeaderEnd. You can either show and hide these sections manually or you can use the "Toggle Hidden Header" link in the DIY-1 menu that is just to the right of the Help Menu. Note, the Toggle Hidden Header functionality requires you to authorize a script to run adn it's often times buggy the first time you click it. Just click it a few times and it will start working ( a programmers nightmare, but best we can do at this time). If/when google introduces outline views, this menu functionality would disappear, but no idea if /when those may ever come about. |
This hidden header section doesn't have anything it. It was left there in case more complex reporting tasks are required or if more query options other than start and end date are added (for sub groups as will be seen later on the Schedule E solution).
Transaction Search
Google sheets has a very compact and powerful filtering system which can be used on individual tabs. It is a little confusing to get going with it, but with a little direction, it makes for a very good system.
This transaction reporting tool is a supplement to that filtering functionality. This makes querying data easier and it produces better looking print outs when desired. This Transaction Search tab is the most complex functionality n the workbook by far. More info on that is in the image below... |
This Transaction Search Tab has a hidden header section, like that described above for the Income Statement, but this one isn't empty.
All of this info is used to convert your selections in the query boxes into the search strings needed to execute the search and retrieve your results. At the bottom of this image you can see just the header of this report... so this is almost a full screen of data manipulation to get a single query string for that report. |
Balance Sheet Value -- Checking
One needs the ending checking balance to prepare a balance sheet. The only balance that matters to the IRS is the one retrieved from the paper statements, but if you'd like to be sure your digital books are in fact synched with paper Gospel, this worksheet is for you. This worksheet acts as a crosscheck by using beginning and ending balances together with the transaction ledgers to make sure all transactions for a given period have in fact been accounted for.
|
This is the bottom of the same worksheet. There is a dark blue border near the bottom of the worksheet in cell F46 that is the active cell.
With that active you can see an equation in the formula bar that reads =getCheckingEndBalance(F47). This cell calls a javascript function that gets the running balance from the last record on the checking tab. |
Unfortunately, this type of function is a static function, in that it won't "monitor" that ledger and update in real time. It has to be prompted to look for that info. I could have added an "update now" button, but buttons in this system are a little sketchy and they can get deleted, so I added a hack in which you need to select a new option in F47 and as soon as you do that, it will fire that function and give you the current value. Google sheets Programmers will appreciate this... the rest of you may just scratch your head. There are some huge advantages to this spreadsheet system... but along with the good comes some glitches that have to be dealt with.
Balance Sheet Value -- Credit Cards
Other Expenses
This is an optional tab. If you spend cash on things, or if you use your personal credit card or checking account for a business expense, this would be a good place to track those transactions.
This was originally included in the transaction aggregator tab, but in fact it creates some confusion. In fact it's juts another liability account, so in that sense it can be handled, but I might suggest leaving it out of the aggregator and handling it separately. More on this will be provided later or ask your CPA to help you decide how best to include this info in your Tax preparation. |
Raw Transactions
If you are downloading transactions, we highly suggest storing that data in this file in it's raw form without manipulation. For long term archiving purposes, it doesn't get any better and easy access to this comes in very hand for trouble shooting. If you grab your transactions monthly or quarterly, just keep pasting the entire download data below prior data. You could then aggregate it all above or below the downloads, but always be sure to leave the original records as raw as possible for best results when needed for cross checking.
|
The image above is the checking data. The image to the left is credit card data.
The credit card data from bank of America for corporate cards is very tricky. If you'll notice, this is in reverse chronological order and it is only for the corporate level transactions. A separate download was then also required for each individual card holder, and those are stored on separate tabs. |
Summary
You just got a real eye full, but if you've had experience with Quickbooks, this should look like it has the potential to provide some real accounting relief. Likewise, if you've been using spreadsheets for your own accounting, this should look and feel like an upgrade. And if you've not been handling your own books due to complexity, this should provide you with the feeling that it is not something that is totally beyond your skill set (all be it, a little spreadsheet training may be required).