Accounting System in Excel: Accrual or Cash Basis Versions

 I have been building financial models for a long time, but I've never built an entire accounting system. The usefulness of this spreadsheet is simply allowing any business to enter transactions into a database style tab and having financial statements automatically produced that are GAAP / IFRS compliant. I tried to hit all the possible types of transactions a business may need to account for. 



$45.00 USD

After purchase, the template will be immediately available to download. This is also included in the Accounting Trackers and Tools spreadsheet bundle. Last Version Update: 10/5/2022

General Features of this Spreadsheet and Notes:

  • Generate GAAP compliant monthly, quarterly, and annual financial statements (Income Statement, Balance Sheet, Cash Flow Statement)
  • Create reports for up to 20 years
  • Supports the use of Accrual and Cash Basis Accounting
  • MS Excel and Google Sheet versions
  • Comes with 3 Excel/G Sheet files (blank, one with examples of every possible accrual-basis transaction and one with examples of every possible cash-basis transaction)
  • The G Sheet version has auditing features to display all transactions sorted by most recent date for a selected category and automatically tells you if you have entered any transactions with a transaction name that is not found in the selection of possible transactions (spelling errors).
  • The G Sheet version has searchable dropdown (i.e. you start typing in a transaction type and it will narrow down options to select). Excel is supposed to be rolling out this feature, but has not for everyone yet.
  • With the one-time purchase, you can always come back to see if new updates have been made and gain access at no extra charge. If you have a good suggestion for new account types, share them and if it is good for the general user, I'll add the new logic in.
  • If you are purchasing this template and distributing it to students, please contact me about licenses. We can work out a bulk discount deal depending on the student count.
Possible Transactions (specific logic and formulas are tied to every single transaction type and that is how the financial statements populate) The sheet comes with examples of using each transaction type as well:

  • Revenue Transaction Triggers
    • Sales - Used to record sales in a given period. This is not 'on account' but sales collected in the period earned. If cash basis, this is used whenever cash is collected in a period from customers.
    • Sales on Account - For accrual basis and this is recorded when a sale is made and invoice is sent to customer. It will go down when 'receipts from sales on account' is recorded.
    • Receipts from Sales on Account - To record money received from sales made on account.
    • Contra Revenue - Transaction item to reduce revenue. This is often things like refunds, discounts, returns.
    • Deferred Revenue - When cash is collected for goods or services that are not provided until a later date.
    • Recognized Revenue - Used to recognize when unearned revenue becomes earned. No cash effect.
    • Other Income - Used to show cash received from activity that is not part of normal operations i.e. insurance claims, rent income, or what have you.
    • Dividend Receivable - Recorded when a dividend is declared for companies you own stock in.
    • Receipt of Dividends - Recorded when you received dividends that have already been declared.
    • Bad Debt Expense - Recorded when sales are made on account if you regularly don't collect a portion of receivables. This is an estimate you have to make yourself based on any available information.
    • Writing Off Doubtful Accounts - Used when you decide a given account is not recoverable.
    • Recovery of Doubtful Account - Used to add back funds that were received from accounts that had been written off previously.
  • Cost of Goods Sold / Inventory
    • COGS Non Inventory - Used to record when an expense is incurred in the same month it is paid out of the bank for cost of goods sold items (not including inventory)
    • COGS Non Inventory on Account - Used to record when a cost of goods sold expense is incurred on account and paid at some future date. (non inventory)
    • Payments for COGS Non Inventory on Account - Used to record when a cost of goods sold expense is paid that was previously incurred on account. (non inventory)
    • Inventory Purchases - Used to record inventory purchased (not on account)
    • Inventory Purchases on Account - Used to record inventory purchased on account.
    • Payments for Inventory on Account - Used to record when inventory that was purchased on account is paid for.
    • COGS Inventory - Used to record when inventory is sold
  • Operating Expenses / Other Expense
    • General and Administrative Expense - Record any expense that is considered in the general and administrative expense category that happened in the same period it was paid.
    • General and Administrative Expense on Account - Record G&A that was accrued on account and will be paid for at some future date.
    • Payments for General and Administrative Expense on Account - Record the payment of G&A expenses that were accrued on account.
    • Sales and Marketing Expense - Record any expense that is considered in the Sales and marketing expense category that happened in the same period it was paid.
    • Sales and Marketing Expense on Account - Record S&M that was accrued on account and will be paid for at some future date.
    • Payments for Sales and Marketing Expense on Account - Record the payment of S&M expenses that were accrued on account.
    • Research and Development Expense - Record any expense that is considered in the research and development expense category that happened in the same period it was paid.
    • Research and Development Expense on Account - Record R&D that was accrued on account and will be paid for at some future date.
    • Payments for Research and Development Expense on Account - Record the payment of R&D expenses that were accrued on account.
    • Prepaid Expense Account - Record any expense that is paid for in advance and used up later.
    • Prepaid Expense - This will be used to record when a given amount of prepaid expenses are used and in turn hit the income statement.
    • Other Expense - Record an expense that is not part of regular operating activity of the business
  • Capital Purchases / Depreciation
    • Depreciation Expense - Record depreciation expense of capital assets
    • Accumulated Depreciation - Only used when you sell a capital asset and you would put a number in for this entry equal to the total accumulated depreciation for the asset that was sold.
    • Capital Asset Purchase - Record money going out for a capital asset purchase
    • Capital Asset Purchase on Account - Buying a capital asset on account
    • Payment of Capital Asset on Account - Payment(s) for capital assets bought on account
    • Sale of Capital Assets - This is used to zero out the book value of the capital asset when it is sold and will always equal the initial purchase cost of the capital asset.
    • Proceeds from Sale of Capital Asset - Records the actual proceeds received from selling a capital asset and this will be used along with the gain/loss on sale. Gain/loss is based on net book value against what was received for the asset.
  • Taxes
    • Income Tax Expense - Records income tax to income statement
    • Income Tax Payable - Records amount owed to IRS
    • Deferred Tax Liability - Record different in tax expense and IRS liability over time
    • Payment of Income Tax - Record when taxes are paid
    • Sales Tax Payable - Record sales tax collected from customers
    • Payment of Sales Tax - Record payment of sales tax collected from customers
  • Loans / Debt Payments
    • Loan Proceeds - Borrowing money from a bank or lender.
    • Principal Payment on Loan - Repaying principal payments on loan
    • Interest Expense - Recording interest expense
    • Payment of Interest Expense - Record payment of interest on loan
  • Stock Issuance / Dividends
    • Common Stock - Sell stock to investors for cash
    • Additional Paid in Capital - Amount paid above par stock value
    • Declare Dividend - Declare a dividend that you will pay out in the future
    • Pay Dividend - Payout of dividend previously declared
  • Short and Long-term Security Holdings / Purchases / Sales
    • Stock or Other Security Investment - purchase of stock / securities / holdings
    • Unrealized Gain/Loss from Trading Securities - mark current value of stock / holdings to market
    • Sale of Trading Securities - sell stock / securities / holdings
    • Proceeds from Sale of Trading Securities - cash received from selling stock/holdings
    • Long-term Stock or Other Security Investment - purchase of stock / securities / holdings
    • Unrealized Gain/Loss from Long-term Trading Securities - mark current value of stock / holdings to market
    • Sale of Long-term Trading Securities - sell stock / securities /holdings
    • Proceeds from Sale of Long-term Trading Securities - cash received from selling stock/holdings
  • Gain/Loss on Sale - Used when selling capital assets as well as selling stock/securities/holdings
Note, I show a scenario / example situation for how each types of transaction above is used to properly record the financial activity on financial statements.

More Accounting Tools: