Clarify the analysis objective. Ask what financial question the user is trying to answer: "Is this business profitable?" "How much runway do we have?" "Should we invest in this opportunity?" "What does our budget variance tell us?" The objective determines the model structure.
Gather financial data. Request historical financial statements, budget figures, unit-level data, or transaction records. Identify what data is available, what is missing, and what can be estimated. Document data gaps explicitly.
Decision point: If critical data is unavailable, recommend proxy data sources (industry benchmarks, comparable companies) and flag the increased uncertainty in the analysis.
Identify key assumptions and drivers. List the 5-10 assumptions that most influence the outcome. For each assumption, document: the assumed value, the source or rationale, the sensitivity (what happens if it is 20% higher or lower), and the confidence level (high, medium, low).
Build the model structure. Create a clear three-section model: Inputs (all editable assumptions in one place), Calculations (formulas that reference inputs), and Outputs (summary results, charts, decision metrics). Structure the model so that changing any input automatically flows through to outputs.
Run scenario analysis. Define three scenarios by varying the most sensitive assumptions:
Base case: Most likely values based on available evidence
Upside case: Optimistic but plausible values with identified tailwinds
Downside case: Conservative values reflecting identified risks
Decision point: If the downside case produces an unacceptable outcome (e.g., negative runway, breakeven pushed beyond 18 months), flag this as a risk requiring mitigation planning.
Calculate key metrics. Compute the relevant financial metrics for the analysis type:
Profitability: gross margin, operating margin, EBITDA margin, contribution margin
Unit economics: CAC, LTV, LTV:CAC ratio, payback period
Cash: monthly burn rate, runway in months, cash flow breakeven date
Investment: NPV, IRR, payback period, risk-adjusted return
Benchmark against comparables. Compare calculated metrics against industry benchmarks or comparable companies. Identify where the business is above, at, or below peer performance. Note any structural reasons for deviation.
Identify insights and recommendations. Translate analysis into 3-5 actionable insights. Each insight should identify: the finding, its implication for the business, and the recommended action.
Decision point: If the analysis reveals conflicting signals (strong growth but deteriorating margins), present both findings and let the stakeholder decide which to prioritize.
Document limitations and caveats. List all known limitations of the analysis: data gaps, assumption weaknesses, external factors not modeled, and the shelf life of the projections.
Present findings. Structure the output with the conclusion first (what should the reader know?), followed by supporting evidence (how do we know?), then methodology (how was this calculated?).