Welcome to the Bookkeeping Template

"Programmer Guide"

Objectives

Provide full technical documentation for the ZAP Accounting Software Bookkeeping Template.

Provide a technical resource for learning about Cloud Spreadsheets as a Database Platform for personal and small business database needs via a working application.

Provide Database Schema ideas for those wishing to build their own Accounting Software Solution in a Spreadsheet or a Database for programming exercises or commercial use.

Orientation

Pre-Reqs and Technical Orientation

This is a great place to learn about spreadsheet programming, data management (database management), google script and javascript, and much more within the context of a Google Sheet Template Designed Specifically for Bookkeeping for Tax/Financial Accounting. (expand for more)

Pre-requisites / Other Resources

bk-master-guide - Master Guide for The Bookkeeping Template (this website)

bk-download-guide - Download Files and Connect Scripting Project

bk-demodata-guide - Explore the Demo Data in the File

bk-configuration-guide - Delete Demo Data and Configure for your use

bk-user-guide - Populate with your data and use for Bookkeeping


Bookkeeping Template Specific

  1. All the core accounting in the Bookkeeping Template is done with spreadsheet equations.

  2. The Scripting, where included, enhances non-math related functionality only and is not required.

  3. This will not be "taught". This is technical documentation. You stand to learn a lot more that way a lot faster if you can get the gist of learning from this type of thing. (expand for more)


Foundational Data Management Understanding

Forms >> Data Tables >> Reports
Forms >> {scripts} >> Data Tables >> {scripts} >> Reports
Forms >> {scripts to add, update, delete records } >> Data Tables >> {scripts to retrieve records} >> Reports

Spreadsheets are traditional used by novices and advanced intermediates for non-data-table style data storage to achieve a data-storage and reporting twist.
Learning to separate data storage from reporting, learning to store data in data tables and then learning how to generate reports from data tables is all that separates a Spreadsheet thinker from a Database Thinker

Video Style

These videos were made with no scripts and no outlines. There is a gazillion vocabulary words to use and I think in images and mental process maps, so getting that close to comprehendible via words is nearly impossible for me. Often times I'll make a video, review it and provide verbal clarity as needed in the detailed video outline and/or in a second video.

Have fun with this Technology. It's other worldly.

Bookkeeping and Accounting Orientation (VERY IMPORTANT for understanding order of video presentation too!)

Bookkeeping and Accounting is a self-managed scorekeeping system for the game of commerce. The Income Statement declares the score one way. Half of the Balance Sheet declares the score another, and the other half of the balance sheet was intended to "provide a level of assurance" that no egregious cheating transpired by the commercial player in their own score keeping. (expand for more)

Truthfully, by itself the Balance Sheet provides nothing. It can be fabricated at all times. However, with a collection of other cross checks in play as well, it plays a very important role in a unique way.

In order for the score keeping system to be complete and serve a comparable purpose to that provided in the manual system, we need

  1. Asset and Liability Journals

  2. A General Journal

  3. An Income Statement

  4. A Balance Sheet

    As you will come to find out, we do NOT "need" a General Ledger.
    As you will come to find out, we do "need" a statement reconciliation tool, but it's not for cash flow management as most use it for today.

What you will come to find out is that taking the extra step of creating a "General Ledger" in it's very traditional format, with multiple lines per transaction, the score keeping system is far easier to use, manage, modify and audit .

  • Furthermore, that combined with an aggregated statement reconciliation tool goes a long ways to provided confidence in the self scoring system.

  • Finally, a change in the Balance Sheet from a tradition sense is required to provide full transparency which has always been missing in the digital age.

These videos are designed to show you how one might try to progress towards a functional accounting systems WITHOUT a General Ledger first.

  • Then we'll give up momentarily and try to do this with a General Ledger and come to the conclusion that that is in fact the "TRICK" to digital bookkeeping design too.

Video Introduction
Videos on this home page are organized in the procedural manner that you might pursue if you wanted to build an accounting system . In fact this is the order this system was pursued. These videos could have been organized in the order in which the worksheets appear in the Bookkeeping Template. The order below provides for a far better learning experience than an allopathic analysis of the final product only. Only a small portion of what we learned could be conveyed that way. With this approach, (expand for more)

you will learn how to think in the manner needed to try to rebuild our template for your own exercise.

This should also enable you to learning how to think about building any database application system similar to those found in our templates. This Bookkeeping Template is unique in complexity due to the nature of the Financial Reporting content. That creates additional content specific stressors that are not found in others. If you can master this most others should be easier to understand.

Foundational Videos

Introductory Video

THE FIRST TWO MINUTES OF THE VIDEO ARE A BIT BUMPY. Then we start at a technology level some might squirm or cringe. That lasts 2 minutes and then it ramps up very fast. This is designed to help technology folks realize the rudimentary level needed to bring everyone along who has interest. Understanding the application design flow near the end of this Introduction video is critical for the rest of the videos on this website so don't skip this...

Introduction - ( 2/25/2021 )

Video Outline with Content Documented by Minute in a Google Sheet

Google Sheet used in the video

Five broad groups of people will be viewing this:

  1. Folks with little to no spreadsheet experience who may or may not have pursued higher education. If you got a Liberal Arts degree without a healthy minor in spreadsheets, you got robbed. You should ask for your money back .

  2. Basic/Intermediate Spreadsheet savvy folks who know spreadsheet basics and think in terms of blending data storage with desired reporting

  3. Advanced/Expert Spreadsheet folks who can think in data lists with the ability to write queries and filter data but have never attempted a financial application (or maybe they have).

  4. Web Based Programmers who have varying levels of add/update/delete functionality and report presentation from a front end design and middle ware perspective. ( in context of the {form - script - data table - script - report} model ).

  5. Database Developers who understand data structure, queries and data optimization with varying levels of other understanding.

Learning to use a spreadsheet platform as the foundation for a small relational database application is easy with the right example.

  • It's a skill and an applied use that has NOT been covered properly in schools at any level since the mid 1980's for various nefarious reasons. It most certainly isn't omitted because it's of no value as will be understood by all far more easily with the ZAP Accounting Software Templates.

      • Teaching Spreadsheets is not profitable nor beneficial to Big Corporations in numerous ways, and given they are one major influencer over curriculum, it makes sense most are clue-less to their full uses to date.

      • Teaching Spreadsheets decreases wealth re-distribution in a capitalist society. Doers don't need as many service providers. Part of the FED's job in addition to managing currency was managing employment directly and indirectly. Most didn't realize that. Once you add that into the equation, things may (or may not) make more sense.

      • Learning spreadsheets empowers individuals to manage their own data in ways that is not desirable to those that like to control others (Big corporations, Government Bureaucrats, Military types, Bankers, etc)

Hopefully these videos will help introduce a simple model for understanding database basics in a spreadsheet while providing each party with a glimpse at the way others might think.

The goal is to create an easy bridge from basic spreadsheet use to database use with a simple example that in fact, serves as a proof of concept for the Income Statement. This was very similar to the proof of concept done in about 20 minutes when this project to compete with Intuit head on got started.

The Asset/Liability Journals, Txn Aggregator , Income Statement, General Journal and Balance Sheet
(a Case Study on a Digital Accounting system w/o a General Ledger as part of the System)

These videos document an attempt at a double entry bookkeeping system in a data efficient manner that stops short of the use of a General Ledger as an intermediate step between the txnAggregator and required reports.

Asset/Liability Journals + General Journal >> txnAggregator >> Income Statement + Balance Sheet


Asset/Liability Journals - (2/26/2021)

Video Outline with Content Documented by Minute in a Google Sheet

The one line journal worksheets are universal for Asset and Liability Accounts. They contain an optional section for custom needs.

The first video covers all relevant programming items on the one-line Journal worksheets except the Row Number was accidentally omitted.

That and a few other minor details were covered in video 2 as a supplement.

Transaction Aggregator - (2/26/2021)

Video Outline with Content Documented by Minute in a Google Sheet

Full Review of the txnAggregator Worksheet.

See notes for a few vocabulary misspeaks near the end. I said "General Journal" when I meant "General Ledger" several times and I said "composite key" several times when "composite flag" is accurate.

Income Statement, General Journal & Balance Sheet - (2/27/2021)

Video Outline with Content Documented by Minute in a Google Sheet

Link to GDrive Folder with Historical Versions pre General Ledger

In this video we are going to step back in time a few years! I get to share with you ZAP Bookkeeping Templates from the 2017 and 2019 time frame. That was a time before NAV1, before the General Ledger existed and before the Automated Balance Sheet had been born! I hope you enjoy watching this as much as I'm enjoying sharing it!

20171212 - I/S from Txn Aggregator (sumifs, no array formula)
20190221 - I/S from Txn Aggregator (sumif, arrayformuals)
20190311a - I/S from Txn Aggregator (sumif, arrayformulas + ??? )

NOTE: In the video intro I thought this was going to be three different videos. It all ran together!!

This turns out to be "doable" with only the Transaction Aggregator in place of and/or without a General Ledger. The data model is very efficient with minimal data enhancement beyond that entered in the Journals. The General Journal would have a few user rules not found in traditional General Journals. The final working Balance Sheet is very difficult to configure and manage. Without a General Ledger, the system as a whole also doesn't have a built in data cross check system which that can offer if debits and credits are assigned to signed values there. Trying to teach intricacies related to the Balance Sheet would have been difficult, and finding programming errors during customization would require immense skill.

Now. Let's add a General Ledger into this mix as a supplement to the txnAggregator and as and additional intermediary between the the Journals and the reports and see what comes if it...

The General Ledger

The General Ledger is a unique data table that takes data that most would think best to store as single line records and it stores them as two line records (or multiline records). While this seems illogical from a data management perspective, it turns out to be a perfect view of the data for then preparing reporting and monitoring data integrity. In the Pre Digital age, it served a somewhat similar purpose for data integrity tracking.

General Ledger - (2/27/2021)

Video Outline with Content Documented by Minute in a Google Sheet

Everything you need to know about the relationship between the txnAggregator and the General Journal.

Everything you need to know about the General Ledger. A summary for the data(base) schema and realization that the accounting application development process is really very unique in it's requirements and a one line and two line view of the data with pos and neg values as well as debits and credits on back end makes everything easy. Try to short cut that and it's a far harder application to manage.


General Journal, Income Statement and Balance Sheet in final form

( these work in conjunction with a General Ledger not without it, as attempted above )

A General Journal is a peer to other Journals, and it is not a peer to a General Ledger. It feeds a General Ledger like the other Journals do although it feeds it directly where as the others feed it through the txnAggregator first. The General Ledger then feeds the Income Statement and Balance Sheet

(Asset/Liability Journals via txnAggregator) + General Journal >> General Ledger >> Income Statement + Balance Sheet

General Journal - (2/28/2021)

Video Outline with Content Documented by Minute in a Google Sheet

This is a review of the General Journal in final form as of 2/28/2021.

What I thought was going to be a very fast review turned into more of a conversation as I remember in real time the breadth of differences between the final version and the one used for the txnAggregator. New ideas came in for consideration for several portions.

Income Statement - (2/28/2021)

Video Outline with Content Documented by Minute in a Google Sheet

This is a review of the Income Statement in final form as of 2/28/2021.

A good conversation about the Income Statement as of this date. I was a bit sloppy with the words. Some of that is noted in the Google Sheet outline.

Balance Sheet - (2/28/2021)

Video Outline with Content Documented by Minute in a Google Sheet

Video 1

This video discusses the "ZAP Special" Account Type. This account type was created to replace the use of "suspended expense accounts". This makes the tough accounting adjustments transparent on the Balance Sheet and it helps force the conversation about "how does money enter into a monetary system from the ether".


Statements Worksheet AND Chart of Accounts

Statements Worksheet - xxx

xxxx

view

Chart of Accounts - xxx

xxxx

view

More Videos...

On the more videos menu you will find videos for every worksheet in the workbook...