Sales and Purchases on Account - Financial Modeling in Excel

This template is helpful to those that are trying to put together a 3-statement model in Excel and are struggling with the correct formulas and logic to properly balance the balance sheet with the income statement and cash flow statement for when sales and purchases are made on account (meaning the use of accounts receivable, accounts payable, and more).

$45.00 USD

After purchase, the template will be immediately available to download. It is fully unlocked and editable as needed. It is also included in the Accounting templates bundle and The Super Smart Bundle. You can easily upload it to Google Sheets as well.

accounting tools

Template Features:

  • Calculates all line item references for income statement, balance sheet, and cash flow statement.
  • 10-year capability with monthly and annual views.
  • All formulas and logic contained on a single tab.
  • 3 Visualizations.
  • Fully unlocked and editable.
  • Dynamically apply cash received / paid patterns and recognition patterns to future sales/costs.
3-statement modeling in Excel can be tough for all skill levels of accountants. When you layer in the complexities of cash flow patterns from customers that are offset from the initial purchase month, that gets even more difficult. This financial model shows how the formulas and logic fit together for the following accounts as you go through time on a monthly and annual basis:

1. Balance Sheet
  • Assets:
    • Cash
    • Accounts Receivable
    • Inventory
    • Prepaid Expenses
  • Liabilities:
    • Accounts Payable
    • Unearned Revenue (deferred revenue)
  • Owners Equity:
    • Retained Earnings
2. Income Statement
  • Recognized Revenue
  • Recognized Expenses (COGS)
  • Recognized Expense
  • Net Income
3. Cash Flow Statement
  • Net Income
  • Less Recognized Revenue
  • Add Cash Collected
  • Add Recognized Expense
  • Reduce Expense Paid
  • Add COGS
  • Reduce Inventory Payments
Example of Sales on Account and This Template Handles the Following Scenario Beautifully:

Example: Accounting for Sales on Account with Upfront Cash Collection
Scenario

Imagine a company that offers a one-year software subscription for $1,200, paid entirely upfront by the customer at the beginning of the subscription period.

Accounting Entries
  • Initial Cash Receipt
    • Date: January 1, 2024
    • Transaction: Customer pays $1,200 for a one-year subscription.
    • Accounting Entry:
      • Debit: Cash $1,200 (to increase the asset account)
      • Credit: Deferred Revenue $1,200 (to increase the liability account)
Explanation: The company receives cash upfront, but since the service is to be provided over the next year, it is recorded as deferred revenue, which is a liability because it represents an obligation to deliver services in the future.

  • Monthly Revenue Recognition
    • Period: Monthly throughout 2024 (e.g., end of each month)
    • Transaction: Recognize revenue as the service is provided.
    • Accounting Entry for Each Month:
      • Debit: Deferred Revenue $100 (to decrease the liability)
      • Credit: Revenue $100 (to increase the income)
Explanation: Each month, as part of the service is provided, the company recognizes $100 of the $1,200 as revenue. This reduces the deferred revenue liability and records the income earned from providing the service.

Summary
  • January 1, 2024: Cash is received and deferred revenue is recorded.
  • End of Each Month in 2024: Revenue is recognized proportionately as the service is delivered over the year.
This approach aligns the revenue recognition with the delivery of service, adhering to the accrual basis of accounting and the matching principle, ensuring that revenues and expenses are matched in the period they occur.

Example of Purchases on Account for Inventory (template handles this perfectly as well)

Example: Paying for Inventory on Account and Recognizing COGS Later
Scenario

Suppose a retail company orders $5,000 worth of merchandise from a supplier on July 1, 2024, agreeing to pay within 30 days. The company sells the merchandise later in the month.

Accounting Entries
  • Purchasing Inventory on Account
    • Date: July 1, 2024
    • Transaction: Company orders $5,000 worth of merchandise.
    • Accounting Entry:
      • Debit: Inventory $5,000 (to increase the asset account)
      • Credit: Accounts Payable $5,000 (to increase the liability account)
Explanation: Inventory is debited because it is an asset that the company now controls and expects to sell for a profit. Accounts payable is credited as the company has incurred a liability to pay the supplier.

Effect on Financial Statements:
  • Balance Sheet: Increase in Inventory (+$5,000) under current assets and increase in Accounts Payable (+$5,000) under current liabilities.
  • Paying the Supplier
    • Date: Within 30 days of purchase
    • Transaction: Company pays off the $5,000 accounts payable.
    • Accounting Entry:
      • Debit: Accounts Payable $5,000 (to decrease the liability)
      • Credit: Cash $5,000 (to decrease the asset)

Explanation: This entry settles the liability with cash, decreasing both accounts payable and cash.

Effect on Financial Statements:
  • Balance Sheet: Decrease in Accounts Payable (−$5,000) and decrease in Cash (−$5,000).
  • Selling the Inventory
    • Date: Later in July 2024
    • Transaction: Company sells the merchandise for $7,000.
    • Accounting Entry:
      • Debit: Cash (or Accounts Receivable) $7,000 (to increase the asset)
      • Credit: Sales Revenue $7,000 (to increase the equity)
      • Debit: Cost of Goods Sold (COGS) $5,000 (to increase the expense)
      • Credit: Inventory $5,000 (to decrease the asset)
Explanation: This entry recognizes the revenue from the sale and the expense associated with the sold goods. The inventory is reduced by the cost associated with the sold items.

Effect on Financial Statements:
  • Income Statement: Increase in Sales Revenue (+$7,000) and increase in COGS (−$5,000), resulting in a Gross Profit of $2,000.
  • Balance Sheet: Increase in Cash or Accounts Receivable (+$7,000) depending on if the sale was cash or credit, decrease in Inventory (−$5,000).
  • Statement of Cash Flows: Cash inflows from operating activities (if sold for cash).
Summary

The purchasing and selling of inventory affect several areas of the financial statements:
  • Balance Sheet: Changes in inventory, accounts payable, and cash or accounts receivable reflect the flow of goods and payments.
  • Income Statement: Recognition of sales and COGS affects profitability.
  • Statement of Cash Flows: Reflects the cash outflows for inventory payments and inflows from sales.
This example underscores the accrual concept, where transactions are recorded when they occur, regardless of when the cash transactions happen, ensuring the financial statements accurately reflect the economic activities of the company during the period.

More Accounting Spreadsheet Tools: