Making 3 Statement Financial Model with the help of AI

-

 1. Introduction

What This Prompt Achieves:

  • Creates a fully linked 3 Statement financial model using a single AI prompt
  • This is not an industry-ready detailed model
  • Industry models require much more thought: detailed revenue builds, cost and SG&A assumptions, and scenario & sensitivity analysis
  • With current LLM context restrictions, it is not feasible to create detailed models with a single prompt
  • As context windows increase and computing costs decrease, we will be able to create more detailed models from single prompts very soon


​​Ideal Use Case For This Prompt: 

  • If you work with boutique companies, boutique investment banks, or early-stage investment firms with simpler financials, this prompt creates functional operating models in minutes

 

2. Three-Statement Models vs. DCF Models

Key Differences:

  • DCF Models: Can be built with single prompts to a reasonable level of detail because they require comparatively lesser linking and interdependencies
  • Three-Statement Operating Models: Require all three statements to be completely linked, with proper balance sheet balancing and cash flow reconciliation
  • Complexity Challenge: The AI must ensure multiple layers of reconciliation and interconnected balance checks, making even basic three-statement models a comparatively more difficult task

 

3. How AI Works with Spreadsheets

Here's a simplified explanation of how AI models create spreadsheets:

  • Understanding Structure: The AI reads the financial data and identifies patterns like revenue lines, expense categories, asset types, etc.
  • Building Logic: It then creates formulas to link statements together. Eg: calculating depreciation from PP&E
  • Writing Code: Since AI can't directly create Excel files from chat interfaces, it generates code that builds the spreadsheet structure at the back-end
  • Generating the File: That code creates the actual Excel file with tabs, formulas, and formatting, following the prompt instructions. 
  • Better prompt + Better model = Better Output

 

4. Step-by-Step Process

Data Gathering

  • Step 1: If the target company is private, input historicals that you get from the client. If not, go to Screener.in and search for your target company
  • Step 2: Take screenshots of the Profit & Loss and Balance Sheet sections
  • Step 3: Copy the below prompt, modify and paste

 

Prompt Customization

Yellow-highlighted sections in the prompt indicate areas you need to modify for your specific company:

  • Company Name
  • Industry
  • Forecast periods
  • Currency

Tip: Feel free to experiment with modeling rules to understand how much data the AI can handle in a single prompt.

 

5. Recommended AI Models & Settings

Model Ranking (Best to Good):

  • Claude Opus 4.5 (Best performance by a HUGE MARGIN for financial modeling)
  • ChatGPT 5.2
  • Perplexity Labs

Critical Settings When Using Claude:

  • Enable: Extended Thinking (for better model logic)
  • Disable: Web Search (to avoid incorrect data from random sources in your model)

 

6. The Complete Prompt


ROLE:
You are a Senior Investment Banking Analyst building a fully linked, institutional-quality three-statement financial model in Excel.

 

MODEL OVERVIEW

  • Company Name: RENEW
  • Industry: Renewable & Green Energy
  • Currency: INR Cr
  • Historical Period: FY23–FY25 (provided)
  • Forecast Period: FY26–FY30 (5 years)
  • Output: Fully downloadable Excel file

 

WORKBOOK STRUCTURE (STRICTLY FOLLOW)

Create 6 Tabs:

  1. Assumptions
  2. Income Statement
  3. Balance Sheet
  4. Cash Flow Statement
  5. Supporting Schedules
    • Working Capital Schedule
    • PP&E Schedule
    • Debt Schedule
    • Equity Roll Forward
  6. Graphs & Checks

 

MODELING REQUIREMENTS (VERY DETAILED)

1.     Revenue Forecast

  • Use driver-based forecasting
  • Derive historical CAGR (FY23–FY25)
  • Allow override growth assumptions in Assumptions tab
  • Show volume vs price if possible (optional enhancement)
  • Include YoY growth row

 

2.     Cost Structure

Break into:

  • COGS (% of revenue driver)
  • Employee Cost (% of revenue)
  • Other Operating Expenses (% of revenue)

Show:

  • Gross Profit
  • EBITDA
  • EBITDA Margin
  • EBIT
  • Net Income

Margins must be calculated dynamically.

 

3.     Working Capital Modeling

From historical:

  • Calculate DSO = AR / Revenue × 365
  • Calculate DIO = Inventory / COGS × 365
  • Calculate DPO = AP / COGS × 365

Forecast:

  • AR, Inventory, AP using forecast days
  • Build a separate Working Capital Schedule
  • Calculate Change in NWC for CFS

 

4.      PP&E Schedule

Build a roll-forward:

Opening PP&E

  • Capex (% of revenue assumption)
    – Depreciation (based on depreciation rate × Opening PP&E)
    = Closing PP&E

Depreciation flows into:

  • Income Statement
  • Cash Flow Statement

 

5.     Debt Schedule (MANDATORY)

Create a full debt roll-forward:

Opening Debt

  • New Borrowing
    – Principal Repayment
    = Closing Debt

Calculate:

  • Interest Expense = Average Debt × Interest Rate

Link interest to Income Statement
Link debt to Balance Sheet

Allow financing flexibility.

 

6.      Equity Roll Forward

Opening Equity

  • Net Income
    – Dividends (% payout assumption)
    = Closing Equity

Retained Earnings must reconcile.

 

7.     Tax Modeling

  • Derive historical effective tax rate
  • Apply to forecast
  • Tax = EBIT × Tax Rate

 

8.     Cash Flow Statement (Indirect Method)

Start with:
Net Income

  • Depreciation
    – Change in Working Capital
    – Capex
    = Free Cash Flow

Then:
+/- Financing flows
= Net Change in Cash

Cash must plug in Balance Sheet.

 

BALANCE SHEET LOGIC

Assets must equal Liabilities + Equity.

Include:

  • Cash (plug)
  • AR
  • Inventory
  • PP&E
  • Other Assets (if needed)

Liabilities:

  • AP
  • Debt

Equity:

  • Share Capital
  • Retained Earnings

 

ERROR CHECKS (CRITICAL)

Include:

✔ Balance Check = Assets – Liabilities – Equity (must be 0)
✔ Cash Flow Reconciliation Check
✔ Retained Earnings Check

Highlight errors in red.

 

FORMATTING REQUIREMENTS

• Font: Times New Roman
• Assumptions: Blue font
• Formulas: Black font
• Headings: White text, Green background
• No hardcoding inside calculation tabs
• Use consistent row structure
• Freeze panes
• Professional IB layout

 

GRAPHS TAB

Include:

  1. Revenue vs Total Expenses (line chart)
  2. EBITDA margin trend
  3. Debt vs Equity trend
  4. YoY Revenue Growth
  5. Free Cash Flow trend

Charts must auto-update.

 

MODEL QUALITY STANDARD

Model must:

  • Be fully linked
  • Avoid circular references
  • Be investment-bank ready
  • Be audit-friendly
  • Contain no broken links

 

END PRODUCT

Provide:

  • Downloadable Excel file
  • Clean structure
  • Fully functioning three-statement model

 

Comments

Post a Comment

Best Blogs

IB Interview Questions: M&A : Level 1 M&A Questions

-
Image
1. Why do companies merge? While are many reasons that companies merge the underlying driver of most M&A deals to expedite growth and/or generate cost savings to create value for the owners of the business. When people say that M&A is an ‘art’ and a ‘science’, what does that mean? The science refers to the mechanical valuation methods (Comps, DCF, etc.), but the art is in making a judgment call on valuation based on the outputs of the various valuation methodologies. 2. Who can pay more when buying a company: a Financial buyer or Strategic buyer? Why? Strategic (i.e. corporate) buyers can typically pay more than Financial (i.e. Private Equity) buyers because strategic buyers typically have overlapping operations that can be removed post-merger to generate cost savings (i.e. Synergies). 3. When are sponsors and corporate buyers on equal footing? When a Financial buyer already owns a competing asset, they can generate synergies (which justifies a higher price) and thus are on mor...
Read more

Getting into Investment Banking: A Comprehensive Guide

-
Image
Introduction Breaking into investment banking (IB) is no small feat, whether you're pre-MBA or post-MBA, from a target or non-target school. While high GPAs and test scores are standard, they are often not enough to stand out. Here’s a strategic approach to help you embark on a successful investment banking career. 1. Network Extensively When your application lands at an investment bank, it’s usually reviewed by a small team of bankers responsible for recruiting from specific schools. Given the competitive nature of this field, networking is crucial. Why Networking Matters: Decision-making bankers are more likely to advocate for you if they know you. Especially vital for non-target school students. Engage with alumni and other connections at your target firms. How to Network: Start reaching out to analysts and associates a few months before core recruiting starts. Look for non-target alumni if you're from a non-target school; they often help each other out. Demonstrate Interest...
Read more