By Jonas Tyrone Lobaton, MSc Quantitative Finance, FMVA®
Founder, Frac CFO · Updated October 22, 2026
A reliable profit and loss (P&L) statement in Excel starts with accounting entries, not just bank transactions. This guide builds a 12-month income statement using account mappings, SUMIFS, an inventory-and-COGS schedule, and adjustments such as depreciation. It also shows why business profit is not the same as cash generated.
Our worked example reports $20,000 revenue, $8,000 COGS, $3,500 operating income and $3,300 net income for March, while its bank cash falls by $7,200. Both results can be correct because cash collections, inventory purchases, capital spending and loan repayments have different accounting effects.
The instructions use a fictional accrual-basis service-and-retail business. Tax treatment, capitalization thresholds, accounting periods and reporting obligations depend on your business and jurisdiction; consult your accountant on classification decisions. A cash-basis report requires a different, consistently applied set of recognition rules.
TL;DR: build a P&L that ties to accounting records
- Net revenue − COGS = gross profit. Gross profit − operating expenses = operating income. Add other income, subtract financing/non-operating expenses and applicable income tax to determine net income.
- Use dated accounting entries with unique IDs, account codes and source references. Do not enter the same invoice again when it is paid.
- For inventory businesses, purchases generally increase stock before sold units or the proper periodic calculation move costs to COGS. Loan principal and equipment purchases are not ordinary P&L expenses.
- Use
SUMIFSwith stable account IDs, dates and explicit P&L account types; check that all mapped source amounts reconcile to the report. - Keep bank reconciliation separate from the P&L reconciliation, and explain profit-to-cash changes with a cash bridge. A positive P&L does not imply an increasing bank balance.
Table of Contents
- P&L structure and worked example
- Accounting method and period decisions
- Step 1: Accounting source ledger
- Step 2: Set up the monthly report
- Step 3: Exact SUMIFS and account mappings
- Step 4: Profit and margin formulas
- Step 5: Accruals, inventory and depreciation
- Step 6: Accounting and cash reconciliation
- Step 7: Dashboard and review
- Errors and QA controls
- What makes a P&L usable
- Frac CFO templates and services
- Frequently asked questions
- Sources
What a profit and loss statement shows
An income statement reports revenue, costs and earnings over a specified period. It does not report all deposits and withdrawals, and it is not a statement of the bank balance. On an accrual basis it records revenue when earned and expenses when incurred according to applicable policy, including noncash depreciation where appropriate.
| Line | Amount |
|---|---|
| Net revenue | $20,000 |
| Cost of goods sold | ($8,000) |
| Gross profit | $12,000 |
| Payroll expense | ($6,000) |
| Rent | ($2,000) |
| Depreciation | ($500) |
| Operating income | $3,500 |
| Interest expense | ($200) |
| Entity-level income tax expense in this example | $0 |
| Net income | $3,300 |
Gross profit is not net income. Interest and other non-operating items, tax and entity-specific reporting requirements may affect net profit. Net margin here is $3,300 ÷ $20,000 = 16.5%. Gross margin is 60%. These figures are fictional and assume no other income, adjustments or applicable entity-level tax expense for this example.
Before building the workbook: choose period, basis and reporting policy
1. Define the reporting period and close date
Use the same accounting calendar and cutoff for revenue, COGS, payroll, accruals, depreciation and taxes. A 12-column monthly statement can also show year-to-date and full-year results. Designate a review date for draft versus closed periods; preserve prior closed copies when later adjustments are recorded.
2. Choose the accounting method actually applicable to the business
Under accrual accounting, a $5,000 service performed in March is recognized according to the applicable recognition policy even if its invoice is issued later or paid in May. The later bank receipt ordinarily reduces receivables; it must not be counted again as revenue. A customer advance may create a contract liability/deferred revenue instead of immediately creating earned income.
Cash-basis bookkeeping follows different recognition principles and tax rules; do not mix cash and accrual entry dates in the same report to make profit match the bank balance. Consult your accountant on permitted methods, special rules and taxes.
3. Separate operating, financial and balance-sheet accounts
Use stable Account IDs and an explicit P&L mapping for revenue, contra-revenue/refunds, COGS, operating expenses, depreciation, other income, interest and entity-level tax expense. Keep receivables, payables, inventory, owner contributions/draws, principal repayments, sales taxes collected and equipment additions in balance-sheet accounts. Payment-processing fees, inbound freight and outbound freight may have different treatment depending on accounting policy and materiality.
Do not classify all inventory purchases as COGS. Unsold inventory generally remains an asset until sold or otherwise adjusted under applicable inventory accounting rules. A bank transaction feed alone cannot replace the full adjusted accrual-basis accounting ledger.
Step 1: Build a controlled source ledger, not only a bank transaction list
For the tutorial, begin with accounting entries in a worksheet called Source Ledger. Columns should include Entry ID, accounting date, month-end date, Account ID, account name, P&L group, amount, source document reference, review status and entity/currency where relevant. Use a consistent accounting sign convention, this example uses positive revenue and positive expense amounts, with the P&L formulas subtracting expense totals in the reporting rows.
For full double-entry accounting, an adjusted general ledger/trial balance is the authoritative source. A simplified line-by-line P&L ledger like this tutorial’s example is an educational reporting extract, not a substitute for general-ledger debits, credits or a trial balance.
Assign one stable Account ID and exactly one approved P&L group per source item. Include a source key/record ID to flag duplicates, and map using the Account ID instead of a manually typed category. Log payments and receipt settlements in separate cash/accounting records so invoices are never recorded twice as earned revenue or incurred expense.
For each reporting date, bring in approved adjustments, including prepaid-expense recognition and depreciation, and a reviewed inventory/COGS schedule. Distinguish source entries from calculated totals to avoid duplicating amounts in the statement.
In a structured Excel Table named tblEntries, a convenient date-key formula is =EOMONTH([@PostingDate],0). For a multi-period service, the accounting recognition schedule, not just an invoice date, determines what is earned in each month.
Step 2: Set up a 12-month P&L and separate supporting schedules
The Monthly P&L tab has months in columns F:Q and an annual total in column R. Use line items in this order: Net revenue; cost of goods sold; gross profit; payroll; rent; depreciation; operating income; other income; interest and other non-operating expenses; pretax profit; any applicable entity-level tax expense; net income; gross and net margins.
Create accompanying Chart of Accounts, Source Ledger, Adjustments, Inventory & COGS, Cash Bridge, Dashboard and QA Checks worksheets. The income statement should show earnings; the cash bridge separately explains differences between earnings and cash movements.
Make each month header a real Excel month-end date, not a text label. Use the same date on source records, depreciation adjustments and inventory schedules. Do not hard-code totals in the P&L output; show direct links to the supporting schedules.
Step 3: Use SUMIFS with account mapping and monthly cutoff controls
Assume the normalized report extract is an Excel Table named tblEntries, with fields PLAmount, PLAccount, PostingMonth and IncludeInPL. A formula for one account/month is:
=SUMIFS(tblEntries[PLAmount],tblEntries[PLAccount],$E8,tblEntries[PostingMonth],F$6,tblEntries[IncludeInPL],TRUE)
This assumes all fields exist and PLAmount uses a documented sign convention. For the tutorial’s simplified reporting extract, revenue and expense lines are separately stored as positive values. Net income is then calculated by subtracting expense category totals, not by summing positive revenue and expense inputs into one undifferentiated number.
Inventory COGS is different: if using the supplied periodic inventory example, the P&L COGS line links to the reviewed Inventory & COGS schedule rather than summing this month’s inventory purchase payments as expenses.
Verify both that every source P&L amount belongs to an approved group and that each P&L group ties to its source ledger total. A global total that includes subtotals or balance-sheet-only cash entries is not a valid P&L reconciliation. Flag duplicates, missing categories, invalid periods and zero/missing source IDs rather than hiding them with IFERROR(...,0).
Microsoft’s SUMIFS reference explains the native multi-criteria aggregation. Excel Tables and lookup controls make the reporting range easier to expand without breaking formulas.
Step 4: Calculate gross, operating and net profit correctly
The reporting sequence is net revenue − COGS = gross profit; gross profit − operating expenses = operating income; and operating income + other income − non-operating expenses = pretax profit. Subtract any applicable entity-level income tax expense to arrive at net income.
For a compact example, if revenue is F8, COGS F10, operating expenses F15, interest expense F18 and tax expense F20, the formulas are:
GrossProfit = F8-F10
OperatingIncome = GrossProfit-F15
PretaxProfit = OperatingIncome-F18
NetIncome = PretaxProfit-F20
GrossMargin = IF(F8=0,NA(),GrossProfit/F8)
NetMargin = IF(F8=0,NA(),NetIncome/F8)
The variable-style expressions above illustrate the formula relationships; use the worksheet’s actual row references to implement them. Margin is profit divided by revenue; markup is profit divided by cost. An item costing $8 and selling for $10 has a 20% gross margin and 25% markup.
A 0%-margin result in a month with zero revenue can be misleading because the ratio is undefined. Prefer a blank or explicitly unavailable result rather than implying a valid 0% margin.
Step 5: Treat inventory, accruals, capital assets and debt appropriately
Inventory purchases are not automatically COGS
For a simplified periodic inventory system, COGS = opening inventory + net purchases + eligible inventoriable costs − closing inventory, subject to applicable returns, shrinkage and inventory accounting policies. A perpetual inventory system can instead record the cost of units actually sold as they sell.
| March inventory and COGS schedule | USD |
|---|---|
| Opening inventory | $15,000 |
| Net inventory purchases | $12,000 |
| Inventoriable freight-in | $1,000 |
| Closing inventory | ($20,000) |
| COGS | $8,000 |
Inventory purchases recorded or received in March are not necessarily cash paid to suppliers in March. The separate March cash example pays suppliers $10,000; explaining timing differences accurately may require opening and closing payables, purchasing records and actual bank entries.
Depreciation and prepaid expenses
The illustrative March P&L includes $500 depreciation. Its $3,000 equipment cash purchase is instead a capital spending item; the depreciation expense need not arise from that same newly purchased asset. Asset recognition and useful-life policies should be documented. Likewise, an annual insurance payment may create a prepaid asset whose expense is recognized over the relevant coverage period rather than expensing the entire payment immediately.
Loan principal, interest and owner transactions
A $1,000 loan principal repayment reduces debt and cash; it is not a P&L operating expense. The $200 illustrative interest payment is an income-statement interest expense. Owner capital contributions and sole-proprietor draws are not revenue and ordinary business expenses. Cash collected as sales tax on behalf of authorities is generally recorded as a liability rather than inflating sales revenue.
Service completion, invoicing, cash collection, supplier payments, taxes and accounting recognition may happen on different dates. An adjusted source ledger, inventory schedule, prepaid-expense schedule and fixed asset schedule are needed to produce a dependable accrual P&L.
Step 6: Reconcile the P&L, bank cash and cash bridge separately
Do not reconcile net income to the ending bank balance. A P&L measures profit over a period; a bank reconciliation substantiates cash and settlement transactions. Reconcile eligible revenue and expenses to the adjusted ledger and supporting schedules first, then reconcile opening and closing cash to actual bank records separately.
Worked March example: $3,300 profit but $7,200 cash decline
| Profit-to-cash bridge (USD) | Amount |
|---|---|
| Net income | $3,300 |
| Add back noncash depreciation | +$500 |
| Cash collections below earned revenue | −$5,000 |
| Supplier cash paid above COGS | −$2,000 |
| Equipment cash acquisition | −$3,000 |
| Loan principal cash repayment | −$1,000 |
| Cash movement | −$7,200 |
The simplified bridge assumes no other working-capital movements, cash flows or tax payments. It is educational, not a complete indirect cash-flow statement. Opening bank cash $12,000 less the $7,200 movement gives $4,800 closing cash.
Independently, March cash collected is $15,000. Cash outflows are $10,000 suppliers, $6,000 payroll, $2,000 rent, $200 interest, $3,000 equipment and $1,000 loan principal; a total of $22,200. Hence net cash is −$7,200. A more comprehensive model would reconcile customer AR, supplier AP, inventory, other working-capital balances and the cash ledger.
Monthly controls the workbook should run
- Source completeness: unique Entry IDs and source references, mapped account IDs, reviewed dates and no duplicate recognized revenue.
- Ledger-to-P&L: revenue and each expense group reconcile separately; totals exclude subtotals and balance-sheet-only cash movements.
- COGS: inventory movements, purchases, freight and closing balances reconcile to inventory accounting records.
- Adjustments: approved depreciation, prepaid costs and accruals reflect the right period.
- Cash: bank/cash movements are independently verified, then bridge profit to cash with documented differences.
- Period-close: errors, missing evidence and late entries are identified before the reporting period is marked closed.
IRS recordkeeping guidance emphasizes keeping adequate supporting records. An Excel P&L can support bookkeeping and reporting but does not replace statutory forms, required accounting schedules or professional review.
Step 7: Make the 12-month income statement decision-ready
Use separate monthly columns for revenue, gross profit, operating income, pretax profit, net income and key margins. Add a monthly trend and budget-versus-actual analysis where a reliable budget is available. Record explanations for unusual accruals and period adjustments rather than relying on visual changes alone.
March P&L: the profit and cash difference
$20,000
60.0%
$3,300
−$7,200
Fictional illustration. Positive operating profit and falling cash can coexist; they are separately reconciled.
Protect calculated rows, use documented account mappings and compare business trends with underlying contracts, invoices and inventory records. A visually clean income statement remains only as reliable as its accounting source and reconciliation process.
Common mistakes that distort a P&L
- Entering the same sales invoice when issued and again when collected, which double-counts revenue.
- Mixing accrual dates, bank settlement dates and invoice dates without a documented recognition policy.
- Recording the entire inventory purchase as COGS instead of reconciling actual costs of goods sold.
- Posting loan principal, owner withdrawals, equipment acquisitions or sales taxes collected as ordinary P&L revenue or expenses.
- Recording annual prepaid expenses immediately rather than using an appropriate expense schedule.
- Using unstable category labels instead of Account IDs, silently dropping unmapped amounts from
SUMIFS. - Treating the ending bank balance as a check of net income, even when credit terms, inventory and capital spending make them differ.
- Forcing a zero reconciliation by plugging a balancing amount, or overwriting totals rather than correcting the source.
Build a statement you can trace, not just a spreadsheet that looks right
Excel and Google Sheets can support useful management reporting when the source transactions, accounting method and reconciliation process are reliable. Separate editable source entries from calculation and reporting tabs; keep reviewable account mapping, period-close adjustments and a report checklist.
A small-business P&L need not become a complex accounting system, but it also cannot replace accrual accounting requirements merely by adding more formulas. Start with a reliable ledger or accounting export, then layer the income statement, working-capital and cash analysis on top. Every reported number should trace to its source or documented estimate.
Start with a template or get a custom P&L build
For a general small-business starting point, the Small Business Bookkeeping Excel Template (published at $22.99 when reviewed) covers bookkeeping, bills, invoicing, bank reconciliation and financial reports. A more specialized Shopify Profit and Loss Tracker Excel (published at $29.99 when reviewed) tracks Shopify orders, expenses, costs, advertising, fees and margins; it is not automatically an all-purpose accrual close-and-adjustment system.
For scope-limited margin work, see Profitability and Pricing Analysis. Businesses needing reconciled accounting extracts, transaction mappings or tailored monthly reporting may instead explore one-time finance projects, with exact deliverables and source cleanup agreed before work begins. The Launch Kit is published at $650 one-time for an initial finance system, subject to its defined scope.
For ongoing finance support, check the current STARTER Plan and other plan pages. Do not assume twelve monthly fees equal an actual annual-payment offer or that every product includes complex inventory, tax or accrual reporting features.
Request a scoped Excel or Google Sheets report with approved account mapping, COGS logic, period adjustments and independent checks.
Frequently asked questions
Can a beginner create a P&L statement in Excel?
Yes, for learning and simple internal reporting. Use consistent account categories and formulas, but confirm the accounting basis and classifications. For financial statements sent to lenders, regulators or tax authorities, the required evidence, adjustments and review depend on context.
Why doesn’t the P&L net income match my bank balance?
Because net income measures earned income less accounting expenses during a period, whereas the bank balance measures cash at a point in time. Accounts receivable, inventory, prepaid items, noncash depreciation, equipment spending and loan principal can all separate profit from cash. Reconcile the bank account independently and use a cash bridge to explain changes.
Are inventory purchases the same as cost of goods sold?
No. Unsold inventory is generally an asset. COGS reflects the cost of goods sold under the applicable inventory method; a periodic system may use opening inventory plus eligible net purchases and costs minus ending inventory, with needed adjustments.
Is a loan repayment an expense on my P&L?
The principal payment reduces a liability and cash, not profit. Interest ordinarily affects the income statement when recognized under the accounting method, subject to special capitalization and other applicable rules.
How do I record customer refunds or credit notes?
Typically as a negative adjustment to the related revenue or a clearly mapped contra-revenue account, rather than a random operating expense. Returns may also affect inventory and COGS. Follow the source document and accounting policy to avoid double counting.
Can I use an Excel P&L to prepare my taxes?
A well-maintained spreadsheet can support bookkeeping and tax preparation if it is accurate, complete and supported by the required records. It is not automatically a substitute for required returns, tax adjustments or prescribed financial statements. Rules depend on your jurisdiction and entity.
How often should I update and close the P&L?
Monthly management reporting is a practical starting point; review transactions, inventory, accruals and adjustments before treating a period as closed. If liquidity is tight, review cash and receivables more frequently than the monthly P&L.
About the author
Jonas Tyrone Lobaton is the founder of Frac CFO. He holds a Master’s degree in Quantitative Finance and the FMVA® certification, with work spanning financial modeling, forecasting, optimization, dashboards, business finance, and spreadsheet automation.
Sources and supporting guidance
- AccountingCoach – Income Statement Explained
- AccountingCoach – Inventory and COGS
- AccountingCoach – Gross Margin versus Markup
- IRS – Business Recordkeeping Requirements
- Microsoft Support – SUMIFS Function
All figures in this tutorial are fictional. Reporting formats, cost capitalization, tax requirements and accounting methods depend on your circumstances; seek qualified advice on your actual books.

2 Responses