Part 2 - A Worksheet by Worksheet tour along with intro to Universal Database Theory
Part 2 - A Worksheet by Worksheet tour along with intro to Universal Database Theory
This contains sections for:
2.1) Statements Worksheet
2.2) Raw Transactions Worksheets
2.3) Journals (Working with Data Tables)
2.4) Chart of Accounts
2.5) Income Statement (Forms, Reports and Data Tables In general)
2.6) Balance Sheet (Two separate Reports that must equal each other)
Database systems in database platforms or spreadsheets can be thought of in very simple terms.
Forms >> Data Tables >> Reports
Forms >> {scripts} >> Data Tables >> {scripts} >> Reports
Forms >> {scripts to add, update, delete records } >> Data Tables >> {scripts to retrieve records} >> Reports
As we work through this section and get to the Income Statement, this over arching structure will become more relevant and it will get included in discussions more.
7:59
Video 2a) Statements Worksheet - Part 1
Familiarize yourself with the Statements Worksheet
Review basic functionality
Compare an Asset Data Table to a Liability Data Table (there are subtle differences)
6:42
Video 2b) Statements Worksheet - Part 2
Use the Statements Worksheet to review basic data trends...
Review Checking Data Table for Data Trends
Review the Credit Card Data Table for Data Trends
1:56
Video 2c) Statements Worksheet - Part 3
Learn about the handy selected data tool that can provide sum, average, max, min and count at a snapshot! Learn to use it now so you can use it later too!
7:48
Video 3a) Raw Transactions Worksheet - Part 1
Top Left - Download Data Set Table at the top
Bottom Left - Bank Data in raw form
Bottom Right - Mapped Bank Data
Upper Right (rarely) - will have a lookup table if it's a corporate credit card account
4:50
Video 3b) Raw Transactions Worksheet - Part 2
The Raw Transactions worksheet is "optional" if you are manually entering data from Paper Statements or just for a transaction list.
!! This is important for those of you who will be manually keying in transactions from paper statements or receipts instead of downloading bank data !!
22:40
Video 4a) Introduction to Journals
Introduction to 6 Journals
Qty 5, 1 line journals (2 Asset Journals, 3 Liability Journals)
Qty 1, multi line journal (the General Journal)
Delineate between Journals and the General Ledger
You really need to know what these are and how they work before we focus on the Demo Data. It helps a lot.
7:25
Video 4b) Sorting Journal Demo Data and Categorization Comments
Checking Journal
Credit Card Journal
Expenses Paid Via Journal
General Journal
A quick lesson in sorting columns and comments about categorization. It turned out light on discussing transaction categorization details.
12:03
Video 4c) Finding and Filtering Data with Google Sheets Filter...
Checking Journal
Credit Card Journal
Expenses Paid Via Journal
General Journal
23:17
Video 5a) Chart of Accounts Intro
An explanation as to why the Chart of Accounts is discussed after the Statements Worksheet and Journals
An introduction to the Chart of Accounts as a simple Two Column Lookup Table as opposed to the full view as a Dashboard, with configuration and Budgeting Functionality too.
Introduction to 5 standard account types, a sub account that we treated like an account and a 6th account type we created for transparency.
Introduction to the Naming Nomenclature we use and reasons for it.
Introduction to assigning lists as lookup values in a cell
Brief conversation about Categorization systems that only offerred income and expense tracking
11:50
Video 5b) ZAP's Chart of Accounts as a Dashboard +
Discussion about the Dashboard Details found in our Chart of Accounts.
Dashboards are typically one of the more difficult things to build for numerous reasons and some of that...
16:52
Video 6a) ZAP's Income Statement +
Database Theory 101 - Understanding Reports in the context of "Forms--Datatables-Reports" framework.
@ 10:45... Income Statement Basics for w2 Employees as it pertains to their Pay checks and hidden Taxes and Insurance that add an additional 20% loss to the sweat of your brow!!PLUS, I forgot to add the Health Insurance premiums they piggy back out of the employees back without credit to your brow too?!?
@ 13:10... Income Statement Basics for Small Business Owners
@14:10... The Alternate reports found on our Income Statement - Cashflow Report
14:48
Video 6b) Income Statement (Continued)
Drill Down Reporting
Pre-filtering the General Ledger for Automated Income Statement Reporting
Other Income Statement Reports - Income Statement by Month, Income Statement by Month Graph, I/S2** and I/S2** by month which are older less automated reports iwth more traditional spreadsheet reporting features and drawbacks.
@11:45 - Budgeting Vs Actual Report
These videos were shot in the reverse of the order they are being presented below. That's why b is before a.
8:51
Video 7b) Balance Sheet
What is a Balance Sheet - from 50,000 feet
Who needs one?
Do I need one for Personal Accounting? What would the advantages be for having one?
A Balance Sheet is mandatory for Businesses. It provides a data integrity check for the categorization process for any given time span. It could be used for personal accounting but you would need to be very disciplined with tracking like a business (not hard to do). It would provide some benefits in the event you mis-categorized things although not sure for most that would really be that relevant.
18:48
Video 7a) Balance Sheet
Development History
What is a Balance Sheet - 2 Reports that equal each other
Differences from Traditional Balance Sheets from vocabulary to functionality
Drill Down Reporting
Historical Report Data (covered but obsolete)
In Part 3 you will see the simulations where the Balance Sheet is forced out of Balance. The benefits of a Balance Sheet in general and our Balance Sheet will become easier to understand.
8:51