Intro > DIY in 5 minutes
What if I told you that you could build the basic foundations of this entire solution in under 5 minutes if you have a little bit of spread sheet savvy and a guide who knows the goal. Would you believe me?
No? Start the timer and take on the challenge...
Notes:
1) You do NOT have to build your own system. We have provided the files for you but come on... we all know we gain real knowledge through personal experience right?
2) The screen shots and this demo below was created with an initial version of the software in which the chart of Accounts data was stored on the Data Validation tab, so it's a little different, but the basic premise is all the same. We'll update this at some point, but for now, this gets the point across.
No? Start the timer and take on the challenge...
Notes:
1) You do NOT have to build your own system. We have provided the files for you but come on... we all know we gain real knowledge through personal experience right?
2) The screen shots and this demo below was created with an initial version of the software in which the chart of Accounts data was stored on the Data Validation tab, so it's a little different, but the basic premise is all the same. We'll update this at some point, but for now, this gets the point across.
Step 1 - Create the tabs
Create 8 Tabs with the following names:
- Transactions
- Income Statement
- Transaction Report
- Balance Sheet
(this is just a place holder, you won't get a real balance sheet from this solution). - Validation Lists
- IRS Schedule C
- IRS Schedule E
- IRS 1120
Step 2 -- Validation Lists Tab
Column A -- (this is a hard part... we are going to provide generalities here.. the actual items you would enter are covered in the book or in another section in more detail)
- A1 = 1120s
- A2 = "All"
- A3:end - {Make up a few entries for now to get going. We'll give you the real list later}
- B1 ="Bank Accounts"
- B2 = "All"
- B3 = "Checking Account"
- B4 = "Corporate Credit Card"
- B5 = "Cash or Other"
- C1 = Payor or Payee Pick List
- C2 = "= sort(unique(Transactions!B2:B),1,True)" (ignore the values you see there.. they are being created by this equation because there is already some sample data in this spreadsheet)
Step 3 -- Transactions Tab
Create the following column names...
Column B -- Payor or Payee
You are now ready to enter Transactions and categorize them... All that is left is the Income Statement reporting...
- Transaction Date, Payor or Payee, Amount, Tax Category, Account, Comments
- Optional -- I delete what I'm not using...
Column B -- Payor or Payee
- Apply Data Validation {Validation Lists!C2:C}
- This Validation list will be blank to start with and then it will build as you make entries.
- Apply Data Validation {Validation Lists!B2:B}
- Apply Data Validation {Validation Lists!A3:A}
You are now ready to enter Transactions and categorize them... All that is left is the Income Statement reporting...
Step 4 - Income Statement Tab
This is the bare bones. No control over date range. Everything entered on the transactions page goes on a report...
Income
Income
- A7 = "Income"
- B7 = "={'Validation Lists'!A3:A4}"
- A10 = "Expense"
- B10 = "={'Validation Lists'!A5:A}"
- C7 = "=sumifs(Transactions!C:C,Transactions!D:D,B7)"
- Then drag that equation down the page...
- Offset the Owner Draw Values and other 99_categories one row to right (for now)
Net Income Summary Table
|
Columns H-Z
|
Step 5 - Print It...
Your final printed report would look like this...
You have just created a tool that can be used to track all relevant expenses for your business this year...
With 10 more minutes or so, we could really build this out and make it pretty...
If Bob Ross (the Painter) was a computer programmer, this is what he would be doing in 30 minute segments in the Digital Age...
You have just created a tool that can be used to track all relevant expenses for your business this year...
With 10 more minutes or so, we could really build this out and make it pretty...
If Bob Ross (the Painter) was a computer programmer, this is what he would be doing in 30 minute segments in the Digital Age...