Lesson 1Profit and cash flow basics: simple monthly gross margin and operating profit lines; basic cash flow items (deferred revenue, collections lag)Construct straightforward monthly views of profits and cash flows, incorporating gross margins, operating profits, and elements like deferred revenues, collection delays, and expense payment schedules to support remote financial visibility.
Modeling revenue, COGS, and gross marginEstimating operating expenses and EBITDACollections lag and accounts receivable logicDeferred revenue and prepayment handlingCash timing for payroll and vendor paymentsLesson 2Scenario and sensitivity setup: setting up scenario toggles, data tables, and one-click switches for alternative assumptionsImplement effective methods for assumption testing via scenario switches, input drivers, and data tables, facilitating seamless comparisons of pessimistic, baseline, and optimistic scenarios while upholding model integrity for remote use.
Defining base, downside, and upside casesBuilding scenario selector and driver cellsUsing Excel and Sheets data tables safelyDesigning one-click assumption switchoversTracking and documenting scenario changesLesson 3Customer flow & cohort modeling: simple cohort setup, retention curves, cohort-based revenue vs aggregate methodsGrasp customer progression modeling through cohorts, from onboarding to attrition and growth, contrasting cohort-driven revenue projections with aggregated techniques suited to varied business models in remote forecasting.
Mapping customer lifecycle and key statesSetting up monthly acquisition cohortsDesigning and applying retention curvesModeling expansion, downgrades, and churnComparing cohort vs aggregate revenueLesson 4Choosing the spreadsheet platform and file conventions: Google Sheets vs Excel Online, naming and folder structureEvaluate Google Sheets against Excel Online for collaborative efficiency, speed, and connectivity, then formulate naming protocols, version controls, and organizational folders to maintain orderly, verifiable 12-month forecasts remotely.
Comparing Google Sheets and Excel OnlineCollaboration, permissions, and access controlPerformance limits and add-in integrationsFile naming and versioning standardsFolder structure for models and exportsLesson 5Model robustness and reusability: using named ranges, structured input table, separation of inputs/calcs/outputsAdopt proven practices for resilient, adaptable forecasts, utilizing named ranges, organized input frameworks, uniform calculations, and distinct zones for inputs, computations, and outputs to enhance remote model durability.
Using named ranges and structured referencesSeparating inputs, calculations, and outputsColor-coding and labeling modeling conventionsError checks, alerts, and reasonableness testsPreparing the model for reuse and handoffLesson 6Revenue calculations: link between new MRR, expansion, churn; monthly MRR roll-forward formulas and ARR conversionIntegrate fresh sales, expansions, and churns into a monthly MRR progression, transform to ARR, and align with customer metrics, ensuring transparent, verifiable formulas for accurate remote revenue tracking.
Defining MRR, ARR, and related metricsLinking new, expansion, and churned MRRBuilding monthly MRR roll-forward schedulesConverting MRR to ARR and revenue linesReconciling revenue with customer countsLesson 7Workbook architecture: recommended tabs (Assumptions, Input Dashboard, Monthly Revenue Schedule, Customer Flow/Cohorts, Cost Schedule, P&L Summary, Sensitivity Scenarios, Audit & Logs)Craft an intuitive workbook with specialized sections for assumptions, inputs, revenues, customers, costs, profit/loss overviews, scenarios, and audits, promoting logical progression and clear separation in remote collaborative environments.
Standard tab list and naming conventionsAssumptions and input dashboard structureMonthly revenue and customer flow schedulesCost schedules and P&L summary layoutScenario, audit, and log worksheet designLesson 8Core inputs and scenarios: defining and organizing key assumptions (starting MRR, new MRR growth, churn, ARPA, pricing tiers, seasonality)Establish and arrange pivotal drivers like initial MRR, sales increments, churn patterns, average revenue per account, pricing levels, and seasonal influences, structuring them for effortless modifications and scenario applications remotely.
Identifying essential revenue driver inputsCapturing starting MRR and opening balancesModeling new MRR growth and sales capacityRepresenting churn, downgrades, and reactivationHandling ARPA, pricing tiers, and seasonalityLesson 9Cost modeling: modeling salaries, hosting, marketing, and tools by month; mapping hires and ramp; variable costs tied to usage or revenueConvert staffing strategies, supplier agreements, and promotional initiatives into monthly cost projections, accounting for fixed/variable elements, recruitment timelines, and activity-linked expenditures that align with revenue growth in remote setups.
Classifying fixed vs variable operating costsModeling salary, benefits, and payroll taxesPlanning hiring dates, ramps, and backfillsScheduling marketing, software, and hosting spendLinking variable costs to usage or revenue
