Pro workbook: Ledger, Monthly, P&L, Setup, Formulas. Sample data, real formulas.
| Income and expenses | ||||||||||
| One row per transaction. Pick a category and the row knows whether it's income or an expense; the totals follow. | ||||||||||
| Year | 2026 | This year | ||||||||
| Income | 31,300.00 | |||||||||
| Date | Description | Category | Type | Amount | Account | Income | Expense | Expenses | 3,053.00 | |
| 2026-01-05 | Invoice 1041 — Harbor Ltd | Services | Income | 2,400.00 | Business account | 2,400.00 | Net | 28,247.00 | ||
| 2026-01-08 | Figma | Software | Expense | 45.00 | Card | 45.00 | Margin | 90.2% | ||
| 2026-01-15 | Template sales, January | Sales | Income | 1,180.00 | PayPal | 1,180.00 | ||||
| 2026-01-20 | Coworking desk | Office | Expense | 220.00 | Card | 220.00 | ||||
| 2026-02-03 | Invoice 1042 — Grove Studio | Services | Income | 3,100.00 | Business account | 3,100.00 | ||||
| 2026-02-10 | Illustrator contract | Contractors | Expense | 600.00 | Business account | 600.00 | ||||
| 2026-02-14 | Template sales, February | Sales | Income | 1,420.00 | PayPal | 1,420.00 | ||||
| 2026-02-27 | Payment fees | Fees | Expense | 41.00 | PayPal | 41.00 | ||||
| 2026-03-02 | Cloudflare, domains | Software | Expense | 38.00 | Card | 38.00 | ||||
| 2026-03-12 | Invoice 1043 — Harbor Ltd | Services | Income | 2,400.00 | Business account | 2,400.00 | ||||
| 2026-03-16 | Template sales, March | Sales | Income | 1,650.00 | PayPal | 1,650.00 | ||||
| 2026-03-21 | Train to client | Travel | Expense | 96.00 | Cash | 96.00 | ||||
| 2026-04-01 | Coworking desk | Office | Expense | 220.00 | Card | 220.00 | ||||
| 2026-04-09 | Sponsored newsletter | Marketing | Expense | 250.00 | Card | 250.00 | ||||
| 2026-04-15 | Template sales, April | Sales | Income | 1,710.00 | PayPal | 1,710.00 | ||||
| 2026-04-22 | Invoice 1044 — Larkfield School | Services | Income | 1,800.00 | Business account | 1,800.00 | ||||
| 2026-05-06 | Monitor | Equipment | Expense | 480.00 | Card | 480.00 | ||||
| 2026-05-15 | Template sales, May | Sales | Income | 1,990.00 | PayPal | 1,990.00 | ||||
| 2026-05-28 | Payment fees | Fees | Expense | 58.00 | PayPal | 58.00 | ||||
| 2026-06-04 | Invoice 1045 — Grove Studio | Services | Income | 2,600.00 | Business account | 2,600.00 | ||||
| 2026-06-15 | Template sales, June | Sales | Income | 2,050.00 | PayPal | 2,050.00 | ||||
| 2026-06-18 | Copywriter | Contractors | Expense | 350.00 | Business account | 350.00 | ||||
| 2026-07-07 | Figma | Software | Expense | 45.00 | Card | 45.00 | ||||
| 2026-07-15 | Template sales, July | Sales | Income | 1,870.00 | PayPal | 1,870.00 | ||||
| 2026-08-03 | Affiliate payout received | Other income | Income | 140.00 | PayPal | 140.00 | ||||
| 2026-08-15 | Template sales, August | Sales | Income | 2,210.00 | PayPal | 2,210.00 | ||||
| 2026-08-20 | Conference ticket | Travel | Expense | 390.00 | Card | 390.00 | ||||
| 2026-09-01 | Coworking desk | Office | Expense | 220.00 | Card | 220.00 | ||||
| 2026-09-10 | Invoice 1046 — Harbor Ltd | Services | Income | 2,400.00 | Business account | 2,400.00 | ||||
| 2026-09-15 | Template sales, September | Sales | Income | 2,380.00 | PayPal | 2,380.00 | ||||
| Monthly | ||||||||
| Income, expenses and net per month for the year on Setup, cumulative net, and quarters. | ||||||||
| Month | Income | Expenses | Net | Cumulative | Margin | Best month | Sep 2026 | |
| Jan 2026 | 3,580.00 | 265.00 | 3,315.00 | 3,315.00 | 92.6% | Average income | 3,477.78 | |
| Feb 2026 | 4,520.00 | 641.00 | 3,879.00 | 7,194.00 | 85.8% | Average expenses | 339.22 | |
| Mar 2026 | 4,050.00 | 134.00 | 3,916.00 | 11,110.00 | 96.7% | Months with activity | 9 | |
| Apr 2026 | 3,510.00 | 470.00 | 3,040.00 | 14,150.00 | 86.6% | |||
| May 2026 | 1,990.00 | 538.00 | 1,452.00 | 15,602.00 | 73.0% | |||
| Jun 2026 | 4,650.00 | 350.00 | 4,300.00 | 19,902.00 | 92.5% | |||
| Jul 2026 | 1,870.00 | 45.00 | 1,825.00 | 21,727.00 | 97.6% | |||
| Aug 2026 | 2,350.00 | 390.00 | 1,960.00 | 23,687.00 | 83.4% | |||
| Sep 2026 | 4,780.00 | 220.00 | 4,560.00 | 28,247.00 | 95.4% | |||
| Oct 2026 | 28,247.00 | |||||||
| Nov 2026 | 28,247.00 | |||||||
| Dec 2026 | 28,247.00 | |||||||
| Year | 31,300.00 | 3,053.00 | 28,247.00 | 90.2% | ||||
| Quarter | Income | Expenses | Net | |||||
| Q1 | 12,150.00 | 1,040.00 | 11,110.00 | |||||
| Q2 | 10,150.00 | 1,358.00 | 8,792.00 | |||||
| Q3 | 9,000.00 | 655.00 | 8,345.00 | |||||
| Q4 | ||||||||
| Averages are over months with income, so the year-to-date figure isn't diluted by the months still to come. |
| Profit and loss | ||
| By category for the year on Setup. Share is of total income or of total expenses. | ||
| Income | Amount | Share |
| Sales | 16,460.00 | 52.6% |
| Services | 14,700.00 | 47.0% |
| Other income | 140.00 | 0.4% |
| Total income | 31,300.00 | |
| Expenses | Amount | Share |
| Software | 128.00 | 4.2% |
| Contractors | 950.00 | 31.1% |
| Office | 660.00 | 21.6% |
| Travel | 486.00 | 15.9% |
| Marketing | 250.00 | 8.2% |
| Fees | 99.00 | 3.2% |
| Equipment | 480.00 | 15.7% |
| Other expense | ||
| Total expenses | 3,053.00 | |
| Net profit | 28,247.00 | |
| Net margin | 90.2% | |
| Uncategorised (not in Setup) |
| Setup | |||||
| The year, the categories with their type, and the accounts. | |||||
| Year | 2026 | ||||
| Category | Type | inc # | exp # | Accounts | |
| Sales | Income | 1 | Business account | ||
| Services | Income | 2 | Card | ||
| Other income | Income | 3 | Cash | ||
| Software | Expense | 1 | PayPal | ||
| Contractors | Expense | 2 | |||
| Office | Expense | 3 | |||
| Travel | Expense | 4 | |||
| Marketing | Expense | 5 | |||
| Fees | Expense | 6 | |||
| Equipment | Expense | 7 | |||
| Other expense | Expense | 8 | |||
| Twenty categories (up to eight income, twelve expense on the P&L), eight accounts. 'inc #' and 'exp #' number the categories of each type — that's how the P&L lists them in order. Leave them alone. |
| How the formulas work | ||
| Every formula in this workbook, in plain words. Nothing is hidden and nothing needs a macro. Formulas are shown without the leading = sign. | ||
| Where | Formula | What it does |
| Ledger · Type | IF(C6="","",IFERROR(INDEX(cat_types,MATCH(C6,cats,0)),"Unknown")) | Looks the category up on Setup and returns Income or Expense. A category that isn't there gives 'Unknown' — red, and its amount lands in 'Uncategorised' on the P&L rather than disappearing. |
| Ledger · Income / Expense columns | IF(D6="Income",E6,0) | Amounts are always typed positive; these two columns split them by type so every total is a plain SUMIFS. Zero in the other column, hidden by the number format. |
| Ledger · This year | SUMIFS($G$6:$G$405,$A$6:$A$405,">="&DATE($B$3,1,1),$A$6:$A$405,"<="&DATE($B$3,12,31)) | Income for the year in B3 — date bounds built with DATE(), so the log can hold several years. |
| Monthly · Income | SUMIFS(Ledger!$G$6:$G$405,Ledger!$A$6:$A$405,">="&A5,Ledger!$A$6:$A$405,"<="&EOMONTH(A5,0)) | From the first of the month to its last day (EOMONTH). |
| Monthly · Cumulative | SUM($D$5:D5) | An expanding range: the first reference is anchored, the second moves. |
| Monthly · Average income | B17/COUNTIF(B5:B16,">0") | Divides by months with income, not by 12, so September's year-to-date isn't dragged down by an empty October. |
| Setup · inc # | IF(B8="Income",COUNTIF($B$8:B8,"Income"),0) | Numbers the income categories 1, 2, 3 in list order. P&L then looks up number k — the way to list a filtered subset without FILTER or array formulas. |
| P&L · category row | IFERROR(INDEX(Setup!$A$8:$A$27,MATCH(1,Setup!$C$8:$C$27,0)),"") | The category whose income number is 1, then 2, then 3 … blank when there are no more. |
| P&L · Share | B5/$B$13 | Of total income for income rows, of total expenses for expense rows. |
| P&L · Net margin | B27/B13 | Net profit over total income. |