📌 Stage 2 · People and Revenue · Revenue Plan · drivers and seasonal index · 35–45 min

Clients × fee × season, one row at a time

Learning goals

  • Split revenue into a retainer engine and a project engine
  • Apply the seasonal index to both engines with cell formulas
  • Read the FY totals and confirm the revenue plan ties to the P&L
  • Explain why the index row must average 100%

Concepts

Two revenue engines

The studio sells two things. Retainers are monthly service agreements: a small number of clients billed every month at a fixed fee — predictable revenue. Projects are one-off brand and web engagements: a count per month at a blended average fee — seasonal revenue.

Under Base, three retainer clients pay $6,500.00 each per month ($234,000.00 for the year) and four projects a month at $9,500.00 average ($456,000.00), for $690,000.00 of FY2026 billings. The Revenue Plan builds each engine as its own row so the P&L can report the mix.

The seasonal index

Row 6 of the Revenue Plan is a typed index where 100% is an average month: January 0.85, February 0.90, March through May 1.05–1.10, summer 0.80–0.95, September through December back to 1.05–1.15. The twelve values average exactly 100% — check it yourself:

=IF(COUNT(C6:N6)=0,0,AVERAGE(C6:N6))

Revenue Plan O6: the FY column averages the twelve monthly indices and returns 100%. If the row is empty the guard returns 0 instead of #DIV/0!. The average must stay 100%, or the year is silently re-based.

💡 Tips:

  • Type the index from your own history: last year's billings divided by the average month is a defensible starting point.

Retainer and project billings

Each billing row multiplies its drivers by the month's index — and pulls every driver from the Assumptions Active column, so the scenario switch moves both engines:

=Assumptions!$F$14*Assumptions!$F$15*C6

Revenue Plan C8 (January retainer billings): 3 clients × $6,500.00 × 0.85 = $16,575.00. The FY total in O8 is $234,000.00.

=Assumptions!$F$16*Assumptions!$F$17*C6

Revenue Plan C9 (January project billings): 4 engagements × $9,500.00 × 0.85 = $32,300.00. The FY total in O9 is $456,000.00.

Total billings and the tie-out

Row 10 adds the two engines, and column O sums the twelve months:

=C8+C9

Revenue Plan C10: January total billings $16,575.00 + $32,300.00 = $48,875.00. O10 sums to the FY figure $690,000.00, which Monthly P&L row 8 links to as total revenue.

💡 Tips:

  • Retainers are 34% of the year (O8/O10). The higher that share, the calmer the cash flow — a useful fact when the studio weighs a retainer push against project work.

Practice

Fixed case: Harbor Creative Studio LLC, a four-person design studio in Austin, Texas, planning calendar year 2026. Cached numbers are the Base scenario. Pale-green cells are formulas; white cells are typed inputs. The exercise opens on the sheet this lesson teaches. Cell C8 (January retainer billings) is blank: retype the driver multiplication.

  1. Open Revenue Plan — The exercise opens on Revenue Plan. Row 6 is the seasonal index (typed); row 8 is retainer billings; row 9 is project billings; row 10 is total billings.
  2. Retype the retainer formula in C8 — Cell C8 is empty. Type =Assumptions!$F$14Assumptions!$F$15C6 and confirm: it should return $16,575.00 (3 × $6,500.00 × 0.85).
  3. Read the two engines — O8 (FY retainers) should read $234,000.00 and O9 (FY projects) $456,000.00. Scan the peak months: the March-to-May and September-to-December blocks each bill above $60,000.00; July and August are the trough near $46,000.00.
  4. Check the index average — O6 should read 100%. If it does not, the index row was mistyped — that is the built-in check for the whole revenue plan.
  5. Confirm the FY tie — O10 should read $690,000.00. Open Monthly P&L: C8 (January revenue) links to this sheet's C10 and reads $48,875.00, and O8 reads the same $690,000.00.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ Retyped the retainer formula in C8 and it returned $16,575.00
  • ☐ O8 reads $234,000.00 and O9 reads $456,000.00; O10 reads the FY total $690,000.00
  • ☐ O6 confirms the seasonal index averages 100%
  • ☐ Can state the retainer share of revenue and why it matters for cash flow