DoDDo you make sales throughout the month but still struggle to see how much your business has actually collected, what it has paid, and which bills or customer payments remain outstanding?
A small business income and expense spreadsheet can help you organize those transactions in one place. This free Excel workbook tracks Paid and Pending amounts, summarizes recorded monthly receipts and payments, and helps you review spending trends throughout the year.
In this guide, you will learn how to use the workbook, interpret its monthly Net Balance, and avoid confusing transaction tracking with accounting profit. You can also download the free Excel template, including instructions and three months of sample data.
Quick answer: In this workbook, Net Balance = Income (Paid) − Expenses (Paid) for the selected month. Uncollected income and unpaid expenses appear separately as Pending. This net figure is not automatically your accounting profit or bank account balance.
Table of Contents
- What You Can Learn From This Spreadsheet
- What Is an Income and Expense Spreadsheet?
- What Is Included in the Free Excel Template?
- How to Use the Spreadsheet
- How to Fill Out the Transactions Sheet
- Paid vs. Pending
- How to Read the Monthly Summary
- A 10-Minute Month-End Review
- Use the Yearly Review to Identify Trends
- What This Spreadsheet Does Not Tell You
- Income and Expense Tracking vs. Cash Flow
- Download the Free Excel Template
- Frequently Asked Questions
- Conclusion
- References and Further Reading
What You Can Learn From This Spreadsheet in 10 Minutes
Keeping a consistent transaction log makes it easier to answer four practical questions: What was collected? What was paid? What is still pending? Where did the money go?
For example, suppose a small business collects $12,000 in customer payments during a month and records $8,500 in paid expenses. The spreadsheet’s monthly Net Balance is $3,500. If another $2,000 in vendor bills is Pending, those unpaid bills are displayed separately rather than deducted from the Paid-only calculation.
The $3,500 is the difference between recorded collections and payments—not proof of $3,500 in net profit and not a calculation of the bank balance. This distinction helps prevent decisions based on one incomplete number.
What Is an Income and Expense Spreadsheet?
An income and expense spreadsheet is a structured record of money received, amounts paid, and transactions awaiting settlement. For a small business, it can replace scattered notes with a consistent way to classify sales receipts, service income, supplier payments, payroll, rent, software, marketing, and other transactions.
This particular workbook is a practical internal tracking tool, not a complete bookkeeping system. It lets you assign a status to recorded transactions and review Paid amounts separately from Pending amounts. As OpenStax explains in its discussion of cash and accrual accounting, the timing of a cash payment can differ from the period in which revenue is earned or an expense is incurred.
What Is Included in the Free Excel Template?
The workbook contains six worksheets and example transactions covering January through March 2026. You can review the examples before entering your own information.
| Worksheet | What it does |
|---|---|
| Instructions | Explains the input fields and the daily, weekly, and monthly workflow. |
| Glossary | Defines the financial terms used in the workbook. |
| Categories | Provides editable classifications for income and expenses. |
| Transactions | Records each amount, date, category, payment method, and Paid/Pending status. |
| Summary | Displays Paid income, Paid expenses, Net Balance, Pending totals, and expense-category information for a selected month. |
| Yearly Review | Compares monthly results over the year and includes a trend chart. |
The template also provides dropdown selections for key transaction fields and highlights major expense categories. It is intended for users comfortable with basic Excel data entry; no macros are required for the core calculations.
How to Use the Small Business Income and Expense Spreadsheet
1. Review the Examples and Instructions
Start with the Instructions, Transactions, Summary, and Yearly Review sheets. The sample data helps you understand how a transaction becomes part of the monthly and yearly totals. Save a separate copy of the original file before replacing examples with your own data.
2. Customize the Categories
Choose categories that match your operation. An ecommerce store may track sales, inventory purchases, shipping, marketplace fees, and advertising. A service business may prioritize service income, subcontractors, software, rent, and travel. Keep categories consistent enough to compare them over time.
3. Enter Transactions Regularly
Add each new income or expense transaction to the Transactions sheet. Review the record regularly instead of waiting until month-end. Include a descriptive label and select the correct transaction type, category, amount, and status.
4. Review the Summary and Yearly Review
Choose the month in the Summary sheet. Examine what was collected, what was paid, what remains Pending, and which expense categories account for most payments. Use Yearly Review to compare the same measures across months.
How to Fill Out the Transactions Sheet
The Transactions worksheet has these actual input columns:
| Column | What to enter |
|---|---|
| Date (YYYY-MM-DD) | The transaction or expected settlement date; update it if the actual payment occurs in a different month. |
| Type (Income/Expense) | Whether money is coming in or going out. |
| Category | The relevant income or expense classification. |
| Description | A short explanation, such as “Website design invoice” or “Monthly rent.” |
| Payment method | The applicable method, such as cash or bank transfer. |
| Customer / Vendor | The party associated with the transaction, when useful. |
| Status (Paid/Pending) | Whether the money has actually been received or paid. |
| Amount | The transaction value in your chosen currency. |
| Notes (optional) | Any detail useful for later follow-up. |
For example, enter an unpaid $1,200 customer invoice as Income / Pending. When payment arrives, change its status to Paid and ensure the date reflects when the cash was collected. Record a completed $450 advertising payment as Expense / Paid.
Data-entry tip: the spreadsheet stores one date and one status per row. If a customer pays in installments, separate transaction rows may be needed to represent the amounts and actual payment dates accurately; this is not an automated installment-tracking system.
Paid vs. Pending: Why the Difference Matters
A sale is not necessarily a cash receipt, and a supplier bill is not necessarily a cash payment. The workbook uses two statuses to keep those situations separate:
- Paid: the amount has actually been received (Income) or paid (Expense).
- Pending: the expected receipt or payment remains outstanding and is shown separately from Paid transactions.
Example: an invoice is entered as Pending in March and the customer pays in April. According to the workbook’s operating instructions, update both the status to Paid and the date to the actual collection date. This places the amount in April’s Paid results instead of March’s. The workbook does not retain a separate historical invoice date and settlement date in the same row.
If you need to preserve invoice dates, due dates, collection history, and partial payments independently, use dedicated receivables/payables records in addition to this tracker. The distinction between transaction timing and accounting recognition is explained in Harvard Business School Online’s discussion of finance and accounting.
How to Read the Monthly Summary
Select a month in the Summary sheet. The main measures are based on the transactions recorded with that month’s dates and statuses:
| Metric | How to interpret it |
|---|---|
| Income (Paid) | Income marked Paid for the selected month. |
| Expenses (Paid) | Expense transactions marked Paid for that month. |
| Net Balance | Income (Paid) minus Expenses (Paid). |
| Pending Income | Recorded income still awaiting collection. |
| Pending Expenses | Recorded expenses still awaiting payment. |
| Expense Categories | Distribution of Paid expenses by category, including major expense categories. |
Net Balance = Income (Paid) − Expenses (Paid). If Paid income is $12,000 and Paid expenses are $8,500, Net Balance is +$3,500. Unpaid vendor bills of $2,000 appear separately as Pending Expenses.
Important: this Net Balance is not the same as accounting net profit or your actual bank balance. The workbook does not calculate accrual adjustments, include an opening bank balance in this measure, or automatically reconcile accounts. As OpenStax explains, cash receipts and payments can occur in different periods from the underlying revenue and expenses.
The Summary also includes a Pending-expense alert based on the relationship between Pending Expenses and Paid Expenses. Treat this as an internal prompt to review outstanding obligations, not as a formal liquidity or solvency assessment. If there are no Paid expenses, do not interpret a blank alert as proof that no obligations exist.
A 10-Minute Month-End Review
Use the workbook as a decision-support routine, not just a place to store transactions:
- Check completeness. Confirm the month’s key customer collections and supplier payments have been recorded.
- Review Pending items. Identify expected customer receipts and bills requiring follow-up.
- Compare Paid amounts. Examine the monthly Net Balance and whether receipts or payments changed.
- Inspect expense categories. Look for increases in supplier, payroll, delivery, or marketing payments.
- Plan the next action. Follow up on a collection, review pricing, adjust spending, or forecast upcoming payments.
Keep supporting invoices and receipts where required; the spreadsheet does not itself replace the documentation that may be needed for accounting or taxes. The IRS explains why business records matter for identifying income sources and substantiating expenses.
Use the Yearly Review to Identify Trends
The Yearly Review makes it easier to compare monthly collections, payments, and net results. Use its month-by-month view and chart to spot fluctuations, recurring spending patterns, or periods that deserve deeper investigation.
For instance, if Paid expenses climb for three months while collections remain flat, inspect the expense categories instead of assuming that every increase reflects normal growth. Where necessary, review the difference between fixed and variable costs to understand which expenses change with sales activity.
The yearly trend remains a view of recorded transactions. It should not be described as an audited income statement or an accounting profitability report.
What This Spreadsheet Does Not Tell You
The workbook provides a useful starting point for financial organization, but it has clear boundaries:
- It does not calculate accrual-basis net profit, depreciation, or complete financial statements.
- It does not reconcile bank accounts or establish the business’s actual opening and ending cash balance.
- It does not preserve a separate invoice date, due date, and payment history for every transaction.
- It does not automate partial payments, tax calculations, or comprehensive accounts receivable/payable aging.
- It does not predict future cash shortages or calculate the sales volume needed to break even.
For example, Iowa State University Extension and University of Minnesota Extension explain why cash-based records and an accrual-adjusted measure of profitability are not interchangeable.
If your question is “How much do I need to sell to cover my costs?”, use our Break-Even Analysis for Small Business guide.
Income and Expense Tracking vs. Cash Flow Forecasting
Transaction tracking shows what was recorded as Paid or Pending. Forecasting estimates when future receipts and payments are expected to occur and whether cash will be sufficient for upcoming obligations.
| Tool | Main question | Primary use |
|---|---|---|
| Income and expense spreadsheet | What have we collected, paid, and left Pending? | Recordkeeping and monthly review. |
| Cash flow forecast | Will enough cash be available when bills come due? | Planning future cash inflows, outflows, and balances. |
When you are ready to estimate future cash needs, continue with our Cash Flow Forecast Template for Small Business. For additional guides on pricing, costs, and liquidity, visit the Business Finance & Profitability hub.
Download the Free Small Business Income and Expense Excel Template
Download the free .xlsx workbook to organize business receipts and payments, distinguish Paid from Pending transactions, review expense categories, and compare monthly results. It includes the Instructions and Glossary sheets, editable categories, a transaction log, a monthly summary, a yearly review, and three months of sample data.
Free Excel .xlsx file. Review the sample transactions before entering your own data. This workbook is intended for internal tracking and does not replace a full accounting or tax reporting system.
Frequently Asked Questions
What Is a Small Business Income and Expense Spreadsheet?
It is a transaction record used to organize business income, expenses, and outstanding amounts. This template also summarizes Paid transactions and Pending amounts by month.
Is the Excel Template Really Free?
Yes. You can download the workbook as an Excel .xlsx file. It includes sample transactions, instructions, editable categories, monthly summaries, and a yearly review.
Should I Include Unpaid Invoices and Bills?
Yes. Mark outstanding customer receipts or bills as Pending. When the money moves, update the transaction to Paid and use the actual settlement date as explained in the workbook instructions.
Does Net Balance Mean Net Profit?
No. In this workbook, Net Balance equals recorded Paid income minus recorded Paid expenses for the selected month. It does not include all accounting adjustments required to determine net profit.
Does the Spreadsheet Show My Bank Balance?
No. The monthly Net Balance is a difference between recorded transactions, not an account reconciliation or an ending bank balance. Check your actual bank and cash records separately.
How Often Should I Update It?
Update it as transactions occur, or at least several times per week when activity is frequent. Perform a short month-end review to identify missing records, outstanding payments, and major spending changes.
Can I Use It to Prepare My Business Taxes?
It can help organize your transactions, but it is not a complete tax accounting system. Keep supporting records and follow the accounting and tax rules applicable to your business.
Conclusion: Turn Transaction Records Into Better Decisions
A simple income and expense spreadsheet can make routine financial reviews more consistent. Record transactions, keep Paid and Pending amounts separate, identify spending patterns, and use the monthly Net Balance as one input—not the only input—for business decisions.
Begin with the free Excel template. Once your transaction records are organized, combine them with a cash flow forecast to plan for future payments and cash requirements.
References and Further Reading
Internal Revenue Service — Why Should I Keep Records?. Official U.S. guidance on documenting business income and expenses.
OpenStax (Rice University) — Cash versus Accrual Accounting. Explains the difference between cash movement and accrual recognition (2026).
Harvard Business School Online — Finance vs. Accounting. Explains how accounting records differ from financial management analysis (updated September 2026).
Penn State Extension — Farm Accrual Adjustments to a Cash-Basis Income Statement. Explains why cash records and accrual adjustments differ.
Iowa State University Extension — Cash Flow and Profitability Are Not the Same. Discusses liquidity versus profitability.
University of Minnesota Extension — Income Statement. Explains why cash-based records alone do not measure full accounting profit.
Discover more from
Subscribe to get the latest posts sent to your email.
