Case study for three statement model and DCF and LBO, how to best approach the model?

To tackle this effectively, here's how you can approach the three-statement model, DCF, and LBO modeling based on the most helpful WSO content:

1. Structuring the Three-Statement Model

• Income Statement (IS): Start by projecting the IS line items (Revenue → EBITDA → Net Income). This will serve as the foundation for both DCF and LBO models.
• Balance Sheet (BS): Build the BS by linking it to the IS and Cash Flow Statement (CFS). For example:
  • Assets: Use beginning minus ending for changes in assets.
  • Liabilities: Use ending minus beginning for changes in liabilities.
  • Ensure every CFS item flows into the BS to guarantee it balances.
• Cash Flow Statement (CFS): Create the CFS by deriving it from the IS and BS changes. The CFS should reconcile the change in cash across operating, investing, and financing activities.

2. Should the Three-Statement Model Be Independent or Integrated?

• Integrated Approach: It’s more efficient to build a single three-statement model that serves as the base for both DCF and LBO. This avoids duplicating efforts and ensures consistency across your projections.
  • For the DCF, you can derive UFCF directly from the IS and BS without needing a separate tab.
  • For the LBO, you can layer on the transaction adjustments (e.g., debt, equity, fees) and integrate the debt schedule into the BS and CFS.

3. Handling Transaction Adjustments (LBO-Specific)

• In the LBO model, you’ll need to adjust the BS for the transaction (TXN):
  • Add a Pro Forma (PF) BS tab where you reflect the transaction adjustments (e.g., new debt, equity injection, goodwill).
  • Include a Debt Schedule tab to track debt paydown, interest expense, and cash sweeps. This will tie into the CFS and BS.
• The CFS in the LBO model doesn’t need to be fully detailed (CFO, CFI, CFF) if you’re already capturing the debt paydown and cash flow mechanics in the debt schedule.

4. Tab Structure Recommendation

• Tab 1: Three-Statement Model (IS, BS, CFS): Build a fully integrated model that serves as the base for both DCF and LBO.
• Tab 2: DCF Analysis: Use the IS and BS from the three-statement model to calculate UFCF. No need for a separate BS or CFS here.
• Tab 3: LBO Model: Add a PF BS and debt schedule to reflect transaction adjustments. Tie these adjustments back to the three-statement model.

5. Key Tips for Execution

• Logical Flow: Maintain a logical flow in your model (e.g., IS → CFS → BS) to ensure everything ties together and balances.
• Simplify Where Possible: If the case study doesn’t require a detailed CFS, focus on the key cash flow drivers (e.g., debt paydown, capex, working capital changes).
• Flexibility: Build your model to be flexible and easy to audit. For example, use clear labels and avoid hardcoding numbers directly into formulas.

By following this approach, you’ll create a streamlined and efficient model that integrates seamlessly across the three-statement model, DCF, and LBO.

Sources: Modeling question: creating your own CFS, Structuring and Flow in an M&A Model, REPE Entity level investment, DCF Modeling Course ~ Pre-training text.pdf