3-in-1 Templates: Accounting System in Excel

Video Overview:

This is a tool for accountants and/or business owners to use in order to general financial statements that are GAAP compliant. The template also works if you just use cash-basis accounting. For all the GAAP logic, the accrual and matching principles are adhered to so that the income statement, balance sheet, and cash flow statement all flow together in the proper periods according to generally accepted accounting principles and IFRS.

The user simply selects a date, transaction description, transaction category, and amount. The Excel template will do the rest. I put in examples of how every possible event can be recorded as an easy reference until you get used to all the account types and logic tied to them.

The purchase includes 3 files. One that has an example of every accrual transaction entry / lifecycle of a given event, one for cash basis, and a blank version. There is also a Google Sheet version included with each file. The Google Sheets version has extra auditing features for checking data entry spelling errors relative to the possible validation selections and reporting by category auto-filtered by the most recent date.

Here is a list of all the possible transaction types that can be selected in the transaction dropdowns:

Revenue Transaction Triggers
-Sales
-Sales on Account
-Receipts from Sales on Account
-Contra Revenue
-Deferred Revenue
-Recognized Revenue
-Other Income
-Dividend Receivable
-Receipt of Dividends
-Bad Debt Expense
-Writing Off Doubtful Accounts
-Recovery of Doubtful Account
COGS / Inventory
-COGS Non Inventory
-COGS Non-Inventory on Account
-Payments for COGS Non-Inventory on Account
-Inventory Purchases
-Inventory Purchases on Account
-Payments for Inventory on Account
-COGS Inventory
Operating Expenses / Other Expenses
-General and Administrative Expenses
-General and Administrative Expenses on Account
-Payments for General and Administrative Expenses on Account
-Sales and Marketing Expenses
-Sales and Marketing Expenses on Account
-Payments for Sales and Marketing Expenses on Account
-Research and Development Expenses
-Research and Development Expenses on Account
-Payments for Research and Development Expenses on Account
-Prepaid Expense Account
-Prepaid Expense
-Other Expense
Capital Purchases / Depreciation
-Depreciation Expense
-Accumulated Depreciation
-Capital Asset Purchase
-Capital Asset Purchase on Account
-Payment of Capital Asset on Account
-Sale of Capital Assets
-Proceeds from Sale of Capital Asset
Taxes
-Income Tax Expenses
-Income Tax Payable
-Deferred Tax Liability
-Payment of Income Tax
-Sales Tax Payable
-Payment of Sales Tax
Loans / Debt Payments
-Loan Proceeds
-Principal Payment on Loan
-Interest Expense
-Payment of Interest Expense
Stock Issuance / Dividends
-Common Stock
-Additional Paid in Capital
-Declare Dividend
-Pay Dividend
Short and Long-term Security Holdings / Purchases / Sales
-Stock or Other Security Investment
-Unrealized Gain/Loss from Trading Securities
-Sale of Trading Securities
-Proceeds from Sale of Trading Securities
-Long-term Stock or Other Security Investment
-Unrealized Gain/Loss from Long-term Trading Securities
-Sale of Long-term Trading Securities
-Proceeds from Sale of Long-term Trading Securities

Gain/Loss on Sale – Used in a few of the transaction life cycles above.

This template is for reporting actuals and if you are a teacher and planning on distributing this to students, please contact me about licensing / bulk discount deals.