Tabellab

Client List: Monthly Fees, Payroll Add-On and Recurring Revenue

Lesson 1 of 20Lab

All lessons
  1. Client List: Monthly Fees, Payroll Add-On and Recurring RevenueLabFree
  2. Time Tracking: Minutes Logged into Hours per ClientLab Locked
  3. Bank Feed: Categorising Transactions with a Rules TableLab Locked
  4. Missing Receipts: a Chase List per Client with COUNTIFSLab Locked
  5. Monthly P&L per Client from One Ledger with SUMIFSLab Locked
  6. Sales Tax / VAT Summary: Rate Lookup and the Amount PayableLab Locked
  7. Month-End Checklist: Completion % per ClientLab Locked
  8. Invoice Batch: Fees, Extra Hours, Tax and the Batch TotalLab Locked
  9. Your Own Receivables: Due Dates, Days Overdue and Follow-Up ActionsLab Locked
  10. Client Profitability: Fee vs Hours × Target RateLab Locked
  11. Practice Dashboard: Revenue Mix, Revenue per Hour and a ChartLab Locked
  12. Clean-Up Quote: Backlog Transactions, Minutes per Line & a Fixed PriceLab Locked
  13. Client Visits: Mileage Log, Allowance per km & Rebillable TravelLab Locked
  14. Duplicate Bills: Same Invoice Number, Same Amount Within 7 Days & the Amount at RiskLab Locked
  15. Staff Expense Claims: Limits per Category, Receipts & the Approved AmountLab Locked
  16. Prepayments & Accruals: Spreading Annual Bills Over the Months They CoverLab Locked
  17. Budget vs Actual: Variances, Favourable or Unfavourable & a Two-Limit Review RuleLab Locked
  18. Annual Fee Review: Rises by Package, Rounding Up & the Effect on Recurring RevenueLab Locked
  19. Unbilled Time: Extra Hours Not Yet Invoiced, Value per Client & Billing SharesLab Locked
  20. Final Project: Practice Year Review – Revenue per Hour, Recurring Share, Receivable Days & a Combo ChartLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Read first

In Excel

  • Turn the list into a table (Ctrl+T): new clients added at the bottom are picked up by the formulas automatically.
Loading the spreadsheet…

Why a client list is the first spreadsheet every freelance bookkeeper needs

You have left an accounting firm, or you have built a side business doing the books for a handful of local companies, and the work is going well. Clients send their receipts, you reconcile their bank accounts, you run payroll for some of them. Then a simple question stops you: how much do I actually earn every month, before any extra work comes in? Many self-employed bookkeepers cannot answer it without opening their invoicing software and adding things up by hand.

This free first lesson solves that problem. In the lab above you set up the client list of Brooks Bookkeeping, the fictional one-person practice of Nadia Brooks. She looks after six small businesses, from a café to a dental practice, and charges each one a fixed monthly fee based on a package. Three of them also pay a flat add-on because she runs their payroll. Your job is to turn that list into a small model that calculates each client’s monthly total, its annual value and the practice’s monthly recurring revenue.

It sounds modest, and that is the point. A clean client list is the base of every other workbook a freelance bookkeeper builds: time tracking, invoicing, profitability, pricing reviews and a practice dashboard all look up the client name and the fee from this one sheet. If you get it right now, with formulas instead of typed totals, every later step is easier. The guide below explains the idea, works through Nadia’s numbers, shows every formula and walks you through the Excel steps, so you can repeat it with your own clients tonight.

The core idea: fixed fees, add-ons and recurring revenue

Most freelance bookkeepers move away from pure hourly billing sooner or later. Clients like to know what they will pay, and you like to know what you will earn. The common answer is a fixed monthly fee, often sold as packages such as Basic, Standard and Plus. A Basic package might cover bank reconciliations and a quarterly report, while Plus might add monthly management accounts and a call. The names and contents are up to you, but the logic is always the same: one agreed amount per client per month.

Services that only some clients need are priced as add-ons. Payroll is the classic example. Running payroll means extra deadlines and extra responsibility, so it makes sense to charge it separately rather than bake it into every package. In Nadia’s practice the payroll add-on is $60 per month, used here as an example price. A client’s monthly total is therefore its package fee plus $60 if it has payroll, and just the fee if it does not.

Monthly recurring revenue, often shortened to MRR, is the sum of all those monthly totals. It is the income the practice can count on every month before any one-off work such as a year-end catch-up, a tax return or a software migration. The term comes from subscription businesses, and a bookkeeping practice on fixed fees is exactly that: a small subscription business. Knowing your MRR tells you whether you cover your own costs, how much room you have to take on new clients and how much a lost client would hurt.

Two Excel ideas make the model work. The first is the IF function, which adds the add-on only when the payroll column says Yes. The second is the absolute reference. The add-on price lives in one cell, B9, and every formula points to it as $B$9. The dollar signs stop the reference from moving when you copy the formula down, and keeping the price in one cell means that a price rise next January is a single edit, not six.

How the client list calculates itselfFee (C2)$350Payroll? (D2)Yes → +$B$9Monthly total (E2)=C2+IF(D2="Yes",$B$9,0)Annual value (F2)=E2*12MRR (B11)=SUM(E2:E7)Willow Café: 350 + 60 = 410 per month · 410 × 12 = 4,920 per yearChange B9 once and every total, annual value and the MRR update together.
Figure 3. The calculation chain of the lab. Only C, D and B9 are typed; everything else is a formula.

Worked example: Brooks Bookkeeping’s client list

Here are the six clients from the lab with the results you will calculate. Columns A to D are typed in; columns E and F are formulas. The fees are realistic examples for a small practice, not market rates, so use your own prices when you copy the model.

ClientPackageMonthly fee (C)Payroll (D)Monthly total (E)Annual value (F)
Willow CaféStandard$350Yes$410$4,920
Harbor DentalPlus$620Yes$680$8,160
Pine JoineryStandard$380No$380$4,560
Luma StudioBasic$190No$190$2,280
Fern YogaBasic$210Yes$270$3,240
Atlas MoversPlus$540No$540$6,480
Total$2,2903 × Yes$2,470$29,640

Walk through one row to see the logic. Willow Café is on the Standard package at $350 and Nadia runs its payroll, so the monthly total is 350 + 60 = $410 and the annual value is 410 × 12 = $4,920. Pine Joinery pays a similar Standard fee of $380 but has no payroll, so its monthly total stays at $380. The IF formula makes this decision for every row, so you never have to remember who pays what.

Add up column E and you get the practice’s MRR: $2,470. There is a neat way to check it. The fees alone add up to $2,290, and three clients pay the $60 add-on, which is $180. And 2,290 + 180 = 2,470. That is exactly the formula the lab uses to check your answer in B11: =SUM(C2:C7)+COUNTIF(D2:D7,"Yes")*B9. You can simply type =SUM(E2:E7), but it is good practice to know a second route to the same number, because when the two disagree, something in the list is wrong.

Finally, =COUNTIF(B2:B7,"Plus") counts the clients on the Plus package: 2 (Harbor Dental and Atlas Movers). These are the formulas behind the finished sheet:

CellFormulaResultWhat it does
E2=C2+IF(D2="Yes",$B$9,0)$410Fee plus the add-on, only where payroll is Yes
F2=E2*12$4,920Annual value of the client
B11=SUM(E2:E7)$2,470Monthly recurring revenue (MRR)
B12=COUNTIF(B2:B7,"Plus")2Number of clients on the Plus package
(extra)=SUM(F2:F7)$29,640Annual recurring revenue, same as B11 × 12
(extra)=SUMIF(B2:B7,"Plus",E2:E7)$1,220MRR from Plus clients only
(extra)=AVERAGE(E2:E7)$411.67Average monthly revenue per client

Monthly total per client = fee + payroll add-onHarbor Dental$680Atlas Movers$540Willow Café$410Pine Joinery$380Fern Yoga$270Luma Studio$1900200400600Monthly fee (C)Payroll add-on $60 (only where D = Yes)
Figure 1. Brooks Bookkeeping’s six clients by monthly total. Blue is the package fee, peach is the $60 payroll add-on that the IF formula adds only for Willow Café, Harbor Dental and Fern Yoga.

The chart makes a pattern visible that the table hides. Harbor Dental alone brings in $680 a month, more than three times Luma Studio’s $190. That is not a problem in itself, but it is worth knowing: if Harbor Dental left, MRR would fall by more than a quarter. Concentration like this is common in small practices, and the client list is where you first notice it.

How to build it in Excel, step by step

  1. Open a new workbook and type the headers in row 1: Client, Package, Monthly fee, Payroll, Monthly total, Annual value in A1:F1. Make them bold with Ctrl+B.
  2. Enter one client per row from row 2 down. Type the fee as a plain number (350, not “$350”) so Excel can calculate with it, and format the column later with Home > Number > Currency.
  3. In the Payroll column type exactly Yes or No. To prevent typos, select D2:D7 and use Data > Data Validation > Allow: List, Source: Yes,No. A drop-down arrow now appears in each cell.
  4. Put the add-on price in its own labelled cell, away from the client rows: Payroll add-on / month in A9 and 60 in B9.
  5. Click E2 and type =C2+IF(D2="Yes",, then click B9 and press F4 once. Excel turns B9 into $B$9. Finish with ,0) and press Enter. The result for Willow Café is 410.
  6. In F2 type =E2*12 and press Enter.
  7. Select E2:F7 and press Ctrl+D to fill both formulas down. Alternatively, select E2:F2 and double-click the small square at the bottom right of the selection (the fill handle).
  8. Press Ctrl+` to show formulas. Every row in column E should point to its own C and D cells but to the same $B$9. Press Ctrl+` again to go back to values.
  9. In A11 type Monthly recurring revenue and in B11 =SUM(E2:E7). With the cell below a column selected, Alt+= (Windows) or Cmd+Shift+T (Mac) inserts the SUM for you.
  10. In A12 type Clients on Plus and in B12 =COUNTIF(B2:B7,"Plus").
  11. Optional but recommended: click any cell in A1:F7 and press Ctrl+T to turn the list into an Excel Table. When you add a seventh client in row 8, the table grows and the formulas in E and F fill in by themselves. If you then write the totals as =SUM(Table1[Monthly total]), they include the new client too.
Name the add-on cell. Select B9, click the Name Box left of the formula bar, type PayrollAddOn and press Enter. The formula becomes =C2+IF(D2="Yes",PayrollAddOn,0), which reads like a sentence and needs no dollar signs at all.

Going one step further: revenue per package

Once the monthly totals exist, every summary is one formula away. To see how much each package contributes, list the package names in a small block, for example in H2:H4, and next to them use =SUMIF($B$2:$B$7,H2,$E$2:$E$7). SUMIF looks down the package column, keeps the rows that match the name in H2 and adds their monthly totals. For Nadia the result is Basic $460, Standard $790 and Plus $1,220.

Monthly recurring revenue by package (total $2,470)04008001200$460Basic$790Standard$1,220PlusShare of MRR: Basic 19% · Standard 32% · Plus 49%
Figure 2. The same monthly totals grouped by package with SUMIF. The two Plus clients bring in almost half of the practice’s recurring revenue.

Divide each package’s revenue by the MRR, =I2/$B$11, and format as a percentage to get the mix: roughly 19% Basic, 32% Standard and 49% Plus. A view like this helps with pricing decisions. If almost half of the revenue comes from two Plus clients, a modest increase on the Plus package matters more to the practice than the same percentage on Basic.

What-if: why one input cell pays off

Because the add-on price, the fees and the payroll flags are the only typed values, you can test decisions by changing a single cell and watching MRR react. The table below shows a few examples on Nadia’s list. Each one is a single edit in the lab’s sheet; the formulas do the rest.

Scenario (for example)ChangeNew MRRDifference per year
TodayAdd-on $60$2,470–
Raise the payroll add-onB9 = 75$2,515+$540
Pine Joinery starts payrollD4 = Yes$2,530+$720
Luma Studio moves Basic → Standard at $350C5 = 350$2,630+$1,920
Atlas Movers leavesdelete row 7$1,930−$6,480

This is the practical payoff of building the list with formulas. When a client asks whether you can take on their payroll, you can see at once what it adds to your year: $60 × 12 = $720. When you consider raising the add-on, you see the effect on the whole practice, not just on one invoice. And when a client gives notice, you know immediately how much new work you need to replace it.

All prices in this lesson are fictional examples chosen to make the arithmetic easy to follow. They are not recommendations for what you should charge. Set your own fees from your costs, the time each client takes and your local market.

Excel for Freelance Bookkeepers course: client list, time tracking, bank feed rules and practice dashboard
The full course follows Brooks Bookkeeping from this client list to time tracking, client P&Ls, invoicing and a practice dashboard.

Common mistakes and how to spot them

  • Typing the add-on into the formula. =C2+IF(D2="Yes",60,0) gives the right answer today, but the price is now hidden in six formulas. Next year you will update some and forget others. Keep it in B9 and refer to it.
  • Forgetting the dollar signs. Without them, =C2+IF(D2="Yes",B9,0) becomes B10, B11 and so on as you copy down, and lower rows add the wrong number or nothing at all. Spot it with Ctrl+`: the add-on reference must be identical in every row.
  • Adding the add-on to everyone. =C2+$B$9 charges payroll to clients who do not use it. Check the result: Pine Joinery must stay at $380.
  • Inconsistent text in the Payroll column. “yes” and “Yes” are treated the same by IF, but “Yes ” with a trailing space or “Y” is not. A data validation list prevents this. To find such cells, use =COUNTIF(D2:D7,"Yes")+COUNTIF(D2:D7,"No"): if the result is less than the number of clients, some entries are off.
  • Fees typed as text. A fee entered as “$350” or with a leading apostrophe is text, and SUM ignores it silently. Left-aligned numbers and a green triangle in the corner of the cell are the warning signs.
  • Totals that miss new clients. If you add a client below row 7, =SUM(E2:E7) will not include it. Use an Excel Table, or insert new rows inside the range so Excel extends it.
  • Annual value without the add-on. =C2*12 ignores payroll. Base the annual value on the monthly total in column E, so both always agree.

Quick reference: functions and shortcuts used

Function / shortcutSyntax or keysUse in the client list
IFIF(test, value_if_true, value_if_false)Add the payroll add-on only for clients with payroll
SUMSUM(range)Monthly and annual recurring revenue
COUNTIFCOUNTIF(range, criteria)Count clients per package or with payroll
SUMIFSUMIF(range, criteria, sum_range)Revenue per package
Absolute reference$B$9, press F4 to toggleKeep the add-on price fixed when copying down
Fill downCtrl+D (select the range first) or double-click the fill handleCopy E2 and F2 to the last client
Format as tableCtrl+TNew clients are included automatically
Show formulasCtrl+` (grave accent)Check every row uses the same formula
AutoSumAlt+= (Windows), Cmd+Shift+T (Mac)Insert SUM below a column

Real-world uses

  • Annual price review. Before you send new-year fee letters, raise the add-on or package prices in the input cells and see the effect on MRR before you commit to anything.
  • Capacity planning. If you know your MRR and your monthly costs, you know how many more Standard clients you need to reach an income target, and whether you have time for them.
  • Client concentration check. Add a column with =E2/$B$11 to see each client’s share of revenue. A single client above a quarter of your income is a risk worth knowing about.
  • Selling or merging a practice. When bookkeeping practices change hands, recurring fee income is usually one of the first figures discussed. A tidy list with annual values per client is the obvious starting document.
  • Quoting a new client. Add the prospect as a row with their likely package and payroll flag, and you see what they would add per month and per year before you send the proposal.

What you will build in the rest of this course

This first lab is free. The other nineteen lessons are part of the paid course ($9.90, one-time) and follow Nadia’s practice through a full month of real work, using the same clients you met here.

Time Tracking: Minutes Logged into Hours per Client. You turn a raw October time log in minutes into hours per client and each client’s share of Nadia’s working time. This is the first time you see which clients really take her time, compared with what they pay.

Bank Feed: Categorising Transactions with a Rules Table. You categorise Luma Studio’s bank feed with a payee rules table and a lookup, and flag the lines that still need a human decision. It is the spreadsheet version of the bank rules in accounting software.

Missing Receipts: a Chase List per Client with COUNTIFS. You build the list every bookkeeper needs before closing a month: how many receipts each client still owes, for how much and how old the oldest one is.

Monthly P&L per Client from One Ledger with SUMIFS. You keep the posted totals of three clients in one ledger and pull out a profit and loss statement for any client with SUMIFS, so one sheet serves the whole practice.

Sales Tax / VAT Summary: Rate Lookup and the Amount Payable. You prepare Atlas Movers’ quarterly sales tax summary: tax per invoice from a rate lookup, net sales and tax per rate, and the amount payable after input tax.

Month-End Checklist: Completion % per Client. You track the October close of four clients on one checklist and measure how far each one has got, so nothing is forgotten at the busiest time of the month.

Invoice Batch: Fees, Extra Hours, Tax and the Batch Total. On the first working day of November you prepare the invoice run for all six clients, combining the fees from this lesson with extra hours and tax.

Your Own Receivables: Due Dates, Days Overdue and Follow-Up Actions. You check which of your own invoices are unpaid, how late they are and whether each client needs a friendly reminder or a phone call.

Client Profitability: Fee vs Hours × Target Rate. You compare each client’s fee with the hours spent and a target hourly rate, and flag the clients that need a new price. This is where the client list and the time log come together.

Practice Dashboard: Revenue Mix, Revenue per Hour and a Chart. You finish with a one-screen dashboard of six months: revenue by type, revenue per hour, a chart and a colour scale you can reuse for your own practice.

Every lesson is a graded lab like the one above, with hints and feedback on common mistakes, and the course ends with an exam and a certificate.

Summary

A freelance bookkeeper’s client list is more than a contact list. With one IF formula, one absolute reference and a SUM, it tells you each client’s monthly and annual value and the practice’s monthly recurring revenue. Keep prices in input cells, keep the payroll flag consistent, use an Excel Table so new clients are counted, and check MRR a second way with SUM plus COUNTIF.

Now scroll back up and try it in the free lab. Fill in the monthly totals and annual values for Nadia’s six clients, calculate the MRR in B11 and count the Plus clients in B12. Then change the add-on in B9 and watch the whole sheet update.

Frequently asked questions

How do I calculate monthly recurring revenue in Excel?

Calculate each client’s monthly total in its own column, for example fee plus any add-ons, and add the column with =SUM(E2:E7). Use an Excel Table so new clients are included automatically.

How do I add a fee only for some clients in Excel?

Use IF with a flag column: =C2+IF(D2="Yes",$B$9,0) adds the price in B9 only where column D says Yes. Keep the price in one cell so you can change it once.

What does $B$9 mean in an Excel formula?

It is an absolute reference. The dollar signs keep the formula pointing at B9 when you copy it to other rows or columns. Press F4 after clicking a cell reference to add them.

How do I count clients by package in Excel?

Use COUNTIF on the package column, for example =COUNTIF(B2:B7,"Plus"). To add up their revenue instead, use =SUMIF(B2:B7,"Plus",E2:E7).

Should a freelance bookkeeper charge fixed monthly fees or hourly?

Both are common, and many bookkeepers use fixed packages with add-ons for extra services. Whatever you choose, track your hours per client so you can check that each fee still covers the time it takes.

Is this Excel lesson free?

Yes. The first lesson with its interactive lab is free. The remaining nineteen lessons, the exam and the certificate are part of the paid course.

Keep going: Excel for Freelance Bookkeepers: Clients, Bank Feeds, Monthly P&L & Practice Profit

This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.

  1. Time Tracking: Minutes Logged into Hours per Client
  2. Bank Feed: Categorising Transactions with a Rules Table
  3. Missing Receipts: a Chase List per Client with COUNTIFS
  4. Monthly P&L per Client from One Ledger with SUMIFS
  5. Sales Tax / VAT Summary: Rate Lookup and the Amount Payable
  6. Month-End Checklist: Completion % per Client
  7. Invoice Batch: Fees, Extra Hours, Tax and the Batch Total
  8. Your Own Receivables: Due Dates, Days Overdue and Follow-Up Actions
  9. Client Profitability: Fee vs Hours × Target Rate
  10. Practice Dashboard: Revenue Mix, Revenue per Hour and a Chart
  11. Clean-Up Quote: Backlog Transactions, Minutes per Line & a Fixed Price
  12. Client Visits: Mileage Log, Allowance per km & Rebillable Travel
  13. Duplicate Bills: Same Invoice Number, Same Amount Within 7 Days & the Amount at Risk
  14. Staff Expense Claims: Limits per Category, Receipts & the Approved Amount
  15. Prepayments & Accruals: Spreading Annual Bills Over the Months They Cover
  16. Budget vs Actual: Variances, Favourable or Unfavourable & a Two-Limit Review Rule
  17. Annual Fee Review: Rises by Package, Rounding Up & the Effect on Recurring Revenue
  18. Unbilled Time: Extra Hours Not Yet Invoiced, Value per Client & Billing Shares
  19. Final Project: Practice Year Review – Revenue per Hour, Recurring Share, Receivable Days & a Combo Chart

Buy course — $9.90 See the full course