Tax / Financial Accounting starts in "Bookkeeping Software"

1. Manually download checking, savings and credit card transactions from your bank in CSV, xlsx formats or other delimited formats

2. Copy and paste that data into our "raw transaction worksheets" where custom mapping has been setup for each account to filter out and organize the data columns needed for your journals just to the right of the data you pasted in.

3. Copy and paste the auto-filtered and auto-formatted data to the right of your raw data into your Journals for Categorization

4. Categorization of tranactions in a spreadsheet is far, far faster than anything you've ever done in Quickbooks or another database system. An alternative for memorized transactions exists along with other rapid techniques.

From the Journals, the categorized data automatically flows into the General Ledger, Income Statmeent and Balance Sheet reports with no further work or submit buttons required. Those are all just "views" of the primary data. That's why there is never any lost or corrupt data. This is where the magic happens and you will see how it all happens below.

For Statement Reconciliation and Crosshcheck, a separate worksheet dedicated to statement header informaton is used. Those data tablse are setup to monitor and compare out to the journals in real time at all times and color coding tells you if anything is out of balance at all times.

Pay your income taxes quarterly or annually using this software as your guide with a level of "audit ready confidence" not found in any other digital solution.

1. You will go to a "Raw Txns Worksheet" for an account you are seeking to download data for.

2. You will create a download set header record that specifies the start and end date of the download data you are going to retrieve. (Circle 1 below)

The Data is now formatted, indexed and ready to be pasted to the corresponding Journal.
This entire process described above takes about 30-60 seconds once mastered.

1. Highlight the first row of the formatted data on the right hand side, select ctrl-shift-arrownDown and you will have that entire data set highlighted. Select Ctrl -C to copy

   1. That "beige" colored line denotes the start of a new download set and it comes in handy . That formatting function to do that is a little sensative. you must paste data in and move it around on this page with "paste special - values only " or you will futzed up that colored line formatting system (and then you can learn how to fix it!)

Next, click on the Journal Worksheet for the desired account. (Journal shown below)

1. Sort by column H . (Circle1)

   1. This is for quality control.

2. Put cursor in the first open row below the existing data (Circle 2 - A268 in this case)

3. Ctrl-Shift-V to paste the data into the Journal without overriding the drop down formatting on the Categorization and ZID Colum (hidden in this screen shot) .

   1. Columns A, B, C, and H will be pasted into the Journal .

   2. Columns I and J will auto-populate

4. When you paste in data, the txn count should pickup where the previous left off (Circle 1 - lower arrow)

You will see details on all this in videos. This site is about data flow and believe it or not, at this point in time everything reflects on the Income Statement as soon as a category is assigned, and same with incremental part of the Balance Sheet. That absolute part of the Balance Sheet got the data as soon as it hit the worksheet uncateogorized.

The TxnAggregator worksheet is what we call a "silent worksheet". It's called silent because it does not require any user input and it is not expected to be viewed by the user as part of their regular interaction with the file.

The grey part of this worksheet (circle 2) is being created by a UNION style database call in Circle 1. The data then autopopulates section 3 of the worksheet via the dark red cells in that section much like what transpired on the raw transactions worksheet you learned about above.

In this process, the data on the right is "prepped" to become double entry data. The information for both records is currently contained in a single line of data at this point. Another query that will be made against this data in a moment and it will project each row into two rows as needed for a General Ledger.

In Database Programming lingo, this Data in the grey would be called a VIEW and it was created by the UNION call at Circle 1).

In Database Programming lingo, this Data in the purple would be called a VIEW of a VIEW, and it was created by the dark red cells in the purple section (it's not a single clean queary to create the table as is the case with the grey side).

The UNION call is programmed to receive all the data from each Journal Worksheet and aggregate them into this single data table format. The code for that UNION call is shared below just so you can sense a level of transparency in this system.

IMPORTANT NOTES:

1. By default the file is setup for 2 asset and 2 liabilities journals. it will follow worksheet name changes on those.

2. if you add more journals to the worksheet, you need to update this UNION call to include them in this step of the process

3. Updating this UNION query is as simply as copying a line of code and pasting it back in, then making a reference change to the new sheet name. It takes about 15 seconds.

OTHER IMPORTANT NOTES:

1. As soon as you pasted the data into the Journal from the RawTxns worksheet, it was mirrored to this transaction aggregator worksheet instnatly and before you categorized anything.

2. We actually duplicated more data in the purple part of this txn Aggregator Worksheet and this data conversion process than was needed. We did that to make teaching this easier. The "sign flipped" column is an important column to understand. It is driven by the relationship between the Acct1 and Acct2 as shown as a single notation in column P and this is the alternative solution for positive and negative values that replaced the debits and credits process.

Below is an image of the General Journal, just for reference.

• Notice that columns E and F are hidden in this view.

• Notice the button to turn debits and credits on and off

With E and F expanded, and with Debits/Credits on, you can see teh allocation of the positive and negative entries for reference and crosscheck.

Below is a screenshot of the General Ledger. In general you should see both parts of matching transactions on top of each other. That may not be the case for transfers between asset and liabilty accounts, but both parts would need to be there for the Balance Sheet to show in Balance.

The General Ledger is "just" another VIEW created by a complex "UNION" call at Circle 1.

Formula found in Circle 1 above is shown below...

This UNION call done in spreadsheet lingo has 3 components.

It gets the records from the General Journal (A), and then it reads the txn Aggregator twice, first grabbing the record as it was entered in the Journal with the journal account name, and then grabbing generally the same info but the selected category for the transaction and the second amount that may or may not have the switched sign value. (B and C)

NOTE:

The trick here is excluding the second half of transactions for transfers between asset and liability accounts where they both have journals. That is built into the UNION Query criteria and it uses a flag that was set in the txn Aggregator for such purpose.

Our Income Statement is "special". It does Income Statement functionality as expected, but it also converts to a Cash Flow report and a full txn report with the toggle of an option. This helps a LOT when teaching ppl about the differences in the data that makes up each report. It also has a summary cash delta (cash flow) report that shows at all times. Without this summary report, it's often very confusing for small busienss owners to look at income statements, because things look good, but they have no cash in their account and the forget their owner draws aren't included in an income statement.

To Drill down into the report, there is a section on the same worksheet that allows that.

BUT WHERE DOES THE DATA FOR THE REPORT COME FROM?

You should know by now. It's just another VIEW of the very original Journal data after it was reflected through the txnAggregator and the General Journal into the General Ledger, and then a subset of that data was filtered out based on date and account type to create the Income Statement.

On the far, far right hand side of the Income Statement worksheet, there is a hidden section called "General Ledger Subset"

The start and end date (and optional ZID) for the report is used to create a filtered view of the General Ledger on this worksheet. That data is then summed by account for the Income Statement and it can all be reviewed on a single worksheet.

But wait. There's a LOT More. And it gets better...

With our Balance Sheet your Trial Balance is always adjusting in real time with Journal Entry changes. No submit button or manual report refresh required.

• If it's out of balance you can quickly toggle the end date back until you find the day it fell out of balance.

• When the date is found, you can expand a section to the right and view just the txns on that date to find out exactly what is missing or out of balance.

• It's typically a txn with no categorization or a transfer allocated as an expense, etc. You know what causes those, but you've never had system to find and repair them this fast and slick before.

Even further to the right you can drill down a little more into the G/L Data that makes up the Balance Sheet report. This operates a bit different than the Subset for the Income Statement but the concept is the same. You'll get into that later.

Our Statements Worksheet enables a user to enter statement Header Data, and then an aggregated reconciliation and real time monitoring of data in the Journals transpires automatically and at all times.

Two Green Columns is always the goal.

We do NOT have a flag on the Balance Sheet to indicate when the Statements worksheet is also in balance or one or more on that sheet are out of Balance.

• There's a complex way to do that, but it requires a lot of custom configuration that could lead to false positives and false negatives if incorrect.

• We've left this as a two part cross check on data such that it requires human eyes to confirm that both the B/S and the Statements worksheet are in Balance.

Details related to this interaction and the errors that can transpire are all documented in detail in the "Bookkeepers Secrets" book series along with some other interesting history.

The Chart of Accounts is just a table of data you create. You control the naming and order. No accounts required and no names forced.

There are some configuration fields that are typically left hidden after setup. (B through G in this image)

Multiple Budgets can be configured on the far right...

There's more to this than what was just shared but you just saw every ounce of the data flow.

There is NO PLACE FOR ANY DATA TO GET LOST. IT'S ALL JUST A REFLECTION OF THE JOURNAL DATA TO START WITH.

The Txn Generator enables Life and Business Simulation.

• There is an entire scripting component in Google Script (Java Script with Google Objects) that is NOT required for any of the core accounting shown, but is required for the Txn Generator.

• That's how we'll teach the world a bit or two about technology and financial simulation too.

And there's other reporting tools and such, but all you should be saying to yourself about now is probably something like...

The Process we promote for Bookkeeeping is called "5 Step Bookkeeping." You just saw the gist of it above and how it all works. The diagram below is for reference and should make more sense now than it would have at the top. It could probably get a little better but we're happy with it for now. Everything is a work in process.