Modeling

2.1: Revenue, customers and churn

What you build. Revenue from customers and what they pay. Churn is the share of customers who leave in a year.

Formulas (plain text)

• customers_lost[t] = ROUND(churn_rate * customers_end[t-1])
• customers_end[t] = customers_end[t-1] - customers_lost[t] + new_customers
• rev_per_customer[t] = ROUND(rev_per_customer[t-1] * (1 + price_growth))
• revenue[t] = customers_end[t] * rev_per_customer[t] - customer_loss[t]
• customer_loss[t] = loss_amount if t >= loss_start_year, otherwise 0

The loss switch is off at 0. It lets you test what happens if revenue disappears.

Worked example, Year 1. Lost = ROUND(0.08 * 130) = ROUND(10.4) = 10. End = 130 - 10 + 15 = 135. Revenue per customer = ROUND(32,000 * 1.03) = 32,960. Revenue = 135 * 32,960 = 4,449,600.

Try it: Hit the target. Using the defaults, read the Year 5 revenue off the model. Then change churn to 15 and watch it fall. The page asks you to say in one line why customer count alone can mislead.

Your observation (saved in this browser)

Reference for the defaults: Year 5 customers 150, Year 5 revenue 5,564,400.

2.2: Gross margin, operating costs and EBITDA

What you build. What is left after serving customers and running the office.

Formulas

• gross_profit[t] = ROUND(revenue[t] * gross_margin)
• cost_of_revenue[t] = revenue[t] - gross_profit[t]
• opex_before_ai[0] = start_opex
• opex_before_ai[t] = ROUND(opex_before_ai[t-1] * (1 + opex_growth))
• ebitda_before_ai[t] = gross_profit[t] - opex_before_ai[t]

Cost of revenue is the direct cost of doing the work: field labor, parts and materials. Operating costs (opex) are everything else: the office, rent, insurance, vehicles and the owner or manager.

Worked example, Year 1. Gross profit = ROUND(4,449,600 * 0.42) = 1,868,832. Operating costs = ROUND(967,200 * 1.05) = 1,015,560. EBITDA before AI = 1,868,832 - 1,015,560 = 853,272.

Try it: Margin squeeze. Drag the operating cost growth slider above the price growth. Watch EBITDA margin shrink. Say in one line which of the two the owner controls more easily.

Your observation (saved in this browser)

Reference for the defaults: Year 5 EBITDA before AI is 1,102,628 (gross profit 2,337,048 less operating costs 1,234,420).

2.3: AI automation savings

What you build. Savings from automating office work, phased in over three years, with a one-time cost to set it up.

Formulas

• ai_phase[1], ai_phase[2], ai_phase[3] are inputs. ai_phase[4] = ai_phase[5] = ai_phase[3].
• ai_savings[t] = ROUND(opex_before_ai[t] * ai_runrate * ai_phase[t])
• opex_after_ai[t] = opex_before_ai[t] - ai_savings[t]
• ebitda[t] = gross_profit[t] - opex_after_ai[t]
• ebitda_margin[t] = ebitda[t] / revenue[t]
• The one-time cost (ai_onetime) is paid in Year 1. It is not part of ebitda[t]. It is subtracted from cash in Lesson 2.4.

The 8.0 percent is a placeholder. It is your job, later, to argue for your own number.

Worked example, Year 1. Savings = ROUND(1,015,560 * 0.08 * 0.30) = 24,373. Operating costs after AI = 1,015,560 - 24,373 = 991,187. EBITDA = 1,868,832 - 991,187 = 877,645.

Try it: Ramp the helpers. Slide the run-rate from 0 to 20 and the phase-in from slow to fast. A bar compares total savings over five years with the one-time cost. Find the point where the bar flips.

Your observation (saved in this browser)

Reference for the defaults: Year 5 EBITDA is 1,201,382 (Year 5 savings 98,754).

2.4: Debt, the seller note and coverage

What you build. How the purchase is paid for, and whether the business can carry the payments.

Sources and uses. Uses are what you pay: purchase_price + transaction_costs. Sources are how you pay: senior_amount + seller_amount + equity_invested. Equity is the plug (the number that makes the two sides match):

• uses = purchase_price + transaction_costs
• equity_invested = uses - senior_amount - seller_amount
• entry_multiple = purchase_price / EBITDA in Year 0

Senior term loan. Amortisation means paying the loan down in steady steps. This model pays equal principal each year.

• senior_interest[t] = ROUND(senior_begin[t] * senior_rate)
• senior_principal[t] = MIN(senior_begin[t], ROUND(senior_amount / senior_amort_years))
• senior_end[t] = senior_begin[t] - senior_principal[t]

Seller note. A standby period is a stretch where the seller agrees not to be paid in cash. Interest builds up and is added to the note. After standby, interest is paid in cash. The principal is repaid in one payment at the sale.

• seller_interest[t] = ROUND(seller_begin[t] * seller_rate)
• seller_cash_interest[t] = seller_interest[t] if t > seller_standby_years, otherwise 0
• seller_end[t] = seller_begin[t] + seller_interest[t] - seller_cash_interest[t]

Cash and coverage. Capex is spending on equipment and vehicles. This model sets depreciation equal to capex.

• capex[t] = ROUND(revenue[t] * capex_pct)
• taxable_income[t] = ebitda[t] - one_time_cost[t] - capex[t] - senior_interest[t] - seller_interest[t]
• tax[t] = ROUND(tax_rate * MAX(0, taxable_income[t]))
• cash_available[t] = ebitda[t] - one_time_cost[t] - capex[t] - tax[t]
• debt_service[t] = senior_interest[t] + senior_principal[t] + seller_cash_interest[t]
• dscr[t] = cash_available[t] / debt_service[t]
• cash_after_debt[t] = cash_available[t] - debt_service[t]
• cash_balance[t] = cash_balance[t-1] + cash_after_debt[t], starting at 0 (nothing is paid out to owners before the sale)

DSCR means debt service coverage ratio. one_time_cost[t] is ai_onetime in Year 1 and 0 after.

Equity cannot be negative. If the loans add up to more than the uses, the page shows a warning.

Worked example, Year 1. Uses = 4,680,000 + 120,000 = 4,800,000. Equity = 4,800,000 - 2,400,000 - 600,000 = 1,800,000. Senior interest = 216,000; principal = 240,000. Seller interest = 36,000, added to the note, so the note is 636,000. Capex = ROUND(4,449,600 * 0.015) = 66,744. Taxable income = 877,645 - 120,000 - 66,744 - 216,000 - 36,000 = 438,901. Tax = 109,725. Cash available = 877,645 - 120,000 - 66,744 - 109,725 = 581,176. Debt service = 216,000 + 240,000 = 456,000. DSCR = 581,176 / 456,000 = 1.27x.

Try it: Stress the structure. Drag the senior loan up and the seller note down. Watch the Year 1 DSCR cross the flag level. Find the largest senior loan that keeps every year at or above the flag level.

Your observation (saved in this browser)

Reference for the defaults: DSCR by year is 1.27, 1.68, 1.76, 1.96, 2.15.

2.5: Exit, MOIC and IRR

What you build. What equity gets when the business is sold at the end of Year 5.

Formulas

• exit_value = exit_multiple * ebitda[5]
• equity_at_exit = exit_value - senior_end[5] - seller_end[5] + cash_balance[5]
• moic = equity_at_exit / equity_invested
• irr = moic ^ (1/5) - 1 (valid because the only cash in is at the start and the only cash out is at the end; if moic is 0 or less, show -100%)
• min_dscr = the lowest dscr[t] across Years 1 to 5

The default exit multiple is lower than the entry multiple on purpose: do not assume a better price on the way out than on the way in.

Worked example, defaults. Exit value = 5.0 * 1,201,382 = 6,006,910. Equity at exit = 6,006,910 - 1,200,000 - 674,160 + 1,651,387 = 5,784,137. MOIC = 5,784,137 / 1,800,000 = 3.21x. IRR = 3.2134 ^ 0.2 - 1 = 26.3%.

Reference table for the defaults (use to check your model)

Try it: Flex three, rank them. Change churn, exit multiple and price growth one at a time, by the same relative step. The page draws a small bar chart of the change in IRR. Rank the three from most to least sensitive.

Your observation (saved in this browser)

"Your model is built. One thing it cannot tell you is whether the inputs are true. That is Module 3."