📌 Stage 5 · Scenarios and Variance · Plan vs Actual · variance review · 35–45 min

Actuals typed from the close, plan pulled live

Learning goals

  • Read the variance layout: actual, plan, variance per month plus a Q1 column
  • Retrace the variance identity as a formula
  • Interpret the Q1 story: revenue ahead, collections behind
  • Decide what action a variance justifies

Concepts

The layout

Plan vs Actual compares seven lines — total revenue, contractor cost, salaries, total operating expenses, net profit, collections, and closing cash — across January, February, and March, then rolls each into a Q1 column. For every month there are three cells: the actual typed from the bookkeeping close (white), the plan pulled live from the driver sheets (pale-green), and the variance.

Only the actual columns (C, F, I) are typed. The plan columns link straight to the model: D5 = 'Monthly P&L'!C8, J11 = 'Cash Roll-Forward'!E8, and so on — so the comparison can never drift out of sync with the plan.

The variance identity

Every variance cell is the same subtraction, and every Q1 column is either a sum of the three months (for flows like revenue and profit) or a repeat of March (for the closing-cash balance, which is a point in time). That is why the Q1 revenue variance of $1,200.00 equals the sum of the monthly variances: $1,525.00 − $2,050.00 + $1,725.00.

=C5-D5

Plan vs Actual E5 (January revenue variance): actual $50,400.00 − plan $48,875.00 = $1,525.00 favorable. A positive variance means ahead of plan; a negative one means behind. Q1 sums the three months for flows, and repeats March for the closing-cash balance, because a balance is a snapshot.

The Q1 story

Three Q1 variances tell the whole quarter:

  • Revenue +$1,200.00 ahead of plan ($162,200.00 actual vs $161,000.00 plan).

  • Collections −$1,000.00 behind ($163,037.50 vs $164,037.50) — the studio billed ahead of plan but banked less than the plan assumed.

  • Net profit +$995.00 ahead ($9,277.00 vs $8,282.00), with closing cash −$1,039.59 behind ($40,925.00 vs $41,964.59).

Revenue ahead, cash behind is the classic signature of slower payment: the earnings are real, but the bank balance is late. The response is not panic about the P&L — it is tightening NET 30 follow-up, because Lesson 7 showed receivables are already the plan's slowest-moving asset.

What a variance justifies

A variance is not an instruction; it is evidence. The discipline: explain every line above a threshold you set in advance (say $1,000.00 or 5%), then change a driver only when the explanation points to a durable cause. One slow month is noise; three months of collections behind plan is a reason to retest the collection curve on Assumptions — a change that ripples through the cash plan honestly, instead of quietly overwriting the formula.

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 E5 (January revenue variance) is blank: retype the variance formula.

  1. Open Plan vs Actual — The exercise opens on Plan vs Actual. Columns C-E are January, F-H February, I-K March, and L-N the Q1 roll-up. Actual columns are typed; plan and Q1 columns are formulas.
  2. Retype the variance formula in E5 — Cell E5 is empty. Type =C5-D5 and confirm: it should return $1,525.00 (actual $50,400.00 vs plan $48,875.00).
  3. Scan the monthly variances — H5 (February revenue variance) reads −$2,050.00 and K5 (March) $1,725.00 — revenue is choppy month to month, which is normal for a project business; the Q1 column is what smooths it out.
  4. Read the Q1 story — N5 (Q1 revenue variance) reads $1,200.00, N9 (Q1 net profit variance) $995.00, N10 (Q1 collections variance) −$1,000.00, and N11 (closing cash variance) −$1,039.59.
  5. Write the one-line explanation — In your own words: billings ran ahead of plan, but collections lagged, so profit is ahead and cash is behind. That sentence is what a monthly close is for.

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 =C5-D5 in E5 and it returned $1,525.00
  • ☐ Q1 variances read: revenue $1,200.00, net profit $995.00, collections −$1,000.00, closing cash −$1,039.59
  • ☐ Can explain why revenue ahead and collections behind points at payment timing, not demand
  • ☐ Can state the threshold rule for when a variance earns a driver change