Financial Modeling with AI: Excel + Copilot in Practice
Updated: 2 days ago
UIB University 365 Institute of Business
Series Business AI Series | Level Basic (Free)
Duration 15 to 20 minutes | Access Free
Business Management, Digital Entrepreneurship, Innovation, Finance, Leadership
UNOP Sound (University 365 Neuroscience Oriented Pedagogy)
Take five minutes to prepare your brain. Play the isochronous tone track (40Hz gamma frequency) with your eyes closed. Gamma-frequency tones before a learning session raise attention and make the material easier to absorb.
[Audio player: UNOP Pre-Lecture Isochrone (40Hz, 5 minutes)]
Table of Contents
The Hook: Your Question, Answered
Your investor meeting is in 30 minutes. They want to see a 3-year revenue projection with three scenarios: base case, optimistic, and pessimistic. You have historical data in a spreadsheet and a blank presentation. How do you build a credible financial model fast?
Five years ago, this task took a financial analyst a full day. Today, with AI tools like Excel Copilot, ChatGPT Advanced Data Analysis, and Claude, you can build a functional financial model in 15 minutes. The AI writes the formulas, generates the scenarios, and creates the charts. You review the logic, adjust the assumptions, and present with confidence.
In this lecture, you will learn how to use AI to build financial models that are fast, accurate, and defensible. The AI handles the mechanical work. You handle the judgment.
What AI Financial Modeling Actually Does
AI does not replace financial expertise. It replaces the mechanical work of building spreadsheets: writing formulas, formatting cells, generating charts, and running scenarios. The financial logic must come from you.
Three Things AI Does Well in Financial Modeling
Formula generation: Describe what you want in plain English and AI writes the Excel formula. "Calculate the compound annual growth rate for revenue from 2024 to 2027" produces =RRI(3, B2, E2).
Scenario automation: AI can build best-case, base-case, and worst-case scenarios in parallel by applying different growth rate assumptions to the same model structure.
Data visualization: AI can generate charts and graphs from your financial data in seconds. Revenue trend lines, cash flow waterfalls, and scenario comparison bars.
What AI Cannot Do
AI cannot determine whether your assumptions are realistic. If you tell AI to assume 50% annual revenue growth, it will build a model with 50% growth. The model will look professional and the math will be correct. But the assumption is your responsibility. Garbage in, garbage out applies even when AI is doing the input.
The CI-First principle is critical here. You define the assumptions. AI builds the model. You validate the results. The formula is CI = HI + (AI x HI): your human intelligence provides the assumptions and validation, AI amplifies your speed in between.
Step 1: Define Your Model Structure
Before opening Excel or asking AI for anything, you need to know what model you are building. A financial model is a structured representation of how your business makes and spends money over time.
The Basic Model Components
Every financial model has five core components:
Revenue drivers: What generates income? (units sold, price per unit, subscription count, contract value)
Cost structure: Fixed costs (rent, salaries) and variable costs (materials, commissions, shipping)
Timing assumptions: When does revenue hit? When are bills paid? Monthly, quarterly, annually?
Growth assumptions: How fast does each revenue driver grow? What drives that growth?
Scenarios: What happens if growth is higher or lower than expected?
The AI Prompt for Model Structure
"I need to build a 3-year financial model for a SaaS company with $2M ARR, 85% gross margin, 120% net revenue retention, and 50% YoY growth. The model should include: revenue projection (by cohort if possible), cost of goods sold, operating expenses (sales and marketing, R&D, G&A), cash flow statement, and three scenarios (base, optimistic, pessimistic). Structure the model with monthly granularity for year 1 and quarterly for years 2-3."
This prompt gives AI enough context to generate a model structure. It specifies the business type, current metrics, growth rate, and desired output format. AI will generate the row labels, column headers, and formula structure.
Why Structure Matters Before AI
If you skip the structure step and just ask AI to "build a financial model," you get a generic template that may not fit your business. A SaaS model needs subscription metrics. A retail model needs inventory turnover. A manufacturing model needs production capacity. The structure determines what data you need and what formulas AI should write.
Step 2: Build Revenue Projections with AI
Revenue projection is the heart of any financial model. AI can accelerate this by generating formulas, extrapolating trends, and building cohort models.
The Revenue Projection Process
Historical data: Provide AI with 12-36 months of historical revenue data.
Growth model: Tell AI what growth assumption to use (percentage, absolute, or driver-based).
Seasonality: If your business has seasonal patterns, tell AI to incorporate them.
Formula generation: AI writes the Excel formulas or Python code to project revenue forward.
Using Excel Copilot
Excel Copilot (available with Microsoft 365 Copilot licenses) lets you describe what you want in natural language and it generates formulas, creates PivotTables, and builds charts. Key commands:
"Project revenue for the next 12 months using 15% YoY growth"
"Create a scenario analysis showing revenue at 10%, 15%, and 20% growth"
"Build a chart showing cumulative revenue by quarter"
Using ChatGPT or Claude for Financial Modeling
If you do not have Excel Copilot, you can use ChatGPT Advanced Data Analysis or Claude to build financial models. Upload your historical data as a CSV file and ask AI to:
Analyze historical trends and identify growth patterns
Project revenue forward based on your stated assumptions
Generate the Excel formulas you need to replicate the model
Create visualizations of the projections
The Key Insight
AI can calculate growth rates, identify trends, and project forward. But the growth assumption itself comes from you. Is 15% growth realistic given market conditions? Is 50% growth achievable with your current sales team? These are business judgment questions that AI cannot answer.
At UIB, we teach that financial modeling in the AI age is about speed plus judgment. AI gives you speed. You bring judgment. Together, they produce better models faster.
Step 3: Automate Scenario Analysis
Scenario analysis is where AI saves the most time in financial modeling. Instead of manually building three separate models, AI can generate all scenarios from a single base model.
The Three-Scenario Framework
Base case: Your most likely outcome. Use realistic, defensible assumptions.
Optimistic case: Everything goes right. Higher growth, lower costs, faster sales cycles.
Pessimistic case: Things go wrong. Lower growth, higher costs, longer sales cycles.
How AI Automates Scenarios
AI can take your base case model and automatically generate optimistic and pessimistic variants by adjusting key assumptions:
Revenue growth: base 15%, optimistic 25%, pessimistic 5%
Gross margin: base 85%, optimistic 88%, pessimistic 80%
Sales cycle: base 45 days, optimistic 30 days, pessimistic 60 days
Churn rate: base 5%, optimistic 3%, pessimistic 8%
The AI applies these adjustments to the model and generates three complete financial projections. You can then compare them side by side to understand the range of possible outcomes.
Sensitivity Analysis with AI
Beyond the three scenarios, AI can run sensitivity analysis: changing one variable at a time to see its impact on the bottom line. "What happens to cash flow if churn increases from 5% to 7% while everything else stays constant?" AI can answer this in seconds by adjusting the formula and recalculating.
This is where AI amplifies your capability. Running 20 sensitivity scenarios manually would take hours. AI does it in minutes. You spend your time interpreting the results, not calculating them.
Step 4: Generate Cash Flow Statements
Cash flow is the lifeblood of any business. Revenue on paper means nothing if cash is not in the bank. AI can help build cash flow statements that connect your revenue and cost projections to actual cash timing.
The Cash Flow Statement Structure
A cash flow statement has three sections:
Operating activities: Cash from revenue minus cash paid for expenses. Adjust for timing differences (accounts receivable, accounts payable, inventory).
Investing activities: Cash spent on capital expenditures, acquisitions, or investments.
Financing activities: Cash from debt, equity, or dividend payments.
How AI Helps with Cash Flow
AI can generate the formulas that connect your income statement to your cash flow statement:
"Build a cash flow statement that converts accrual-based revenue to cash revenue using a 45-day collection period"
"Calculate the cash burn rate for the pessimistic scenario and identify when we run out of cash"
"Create a cash runway chart showing months of cash remaining under each scenario"
The Critical Output: Cash Runway
For startups and growth companies, the most important output of a financial model is the cash runway: how many months until cash runs out. AI can calculate this across all three scenarios and flag the scenario where runway falls below a safe threshold (typically 12-18 months).
This is where the 5M2S principle applies directly. In 5 minutes, AI can calculate cash runway across three scenarios. In the pre-AI era, this required a financial analyst working for half a day. The speed gain is not a convenience. It is a strategic advantage. You can iterate on assumptions and see cash impact in real time during a board meeting.
Step 5: Validate and Stress-Test with AI
A financial model is only as good as its assumptions. AI can help you validate and stress-test those assumptions before you present them to investors or stakeholders.
AI Validation Techniques
Assumption checking: Ask AI to review your assumptions and flag any that seem unrealistic or unsupported. "Review this model and tell me which assumptions are most risky."
Benchmark comparison: Ask AI to compare your assumptions against industry benchmarks. "Is 85% gross margin realistic for a B2B SaaS company at $2M ARR?"
Break-even analysis: Ask AI to calculate when each scenario reaches break-even and what assumptions drive the timing.
Monte Carlo simulation: For advanced models, AI can run thousands of random variations to produce a probability distribution of outcomes.
The Human Validation Layer
AI validation is useful but insufficient. You must also:
Sanity-check the outputs: Does the projected revenue for year 3 make sense given market size?
Check for circular references: AI-generated formulas sometimes create circular logic that produces wrong results.
Verify the formula logic: Spot-check key formulas to ensure they calculate what you intended.
Stress-test the downside: What happens if the pessimistic case is worse than you modeled? Can the business survive?
The CI-First Validation Loop
The validation step is where CI-First matters most. AI builds the model and runs the scenarios. You validate the assumptions, check the logic, and make the final call on whether the model is defensible. The formula is CI = HI + (AI x HI). Your intelligence is the bookend: it starts the process (defining assumptions) and ends it (validating results). AI multiplies your capability in the middle.
Feynman Summary: Explain It Like You Are 12
Imagine you have a piggy bank and you want to know how much money you will have in 3 years. You know you get $10 a week now, but you might get more later if you do more chores.
You could sit with a calculator and figure it out: $10 times 52 weeks is $520 per year. If you get a 15% raise each year, year 2 is $598 and year 3 is $688. Total: $1,806.
But what if you also spend some of that money on candy? And what if your allowance goes up by 10% instead of 15%? Or 20%? You would have to redo all the math.
An AI helper can do all that math in 5 seconds. You tell it: "I get $10 a week, I spend $2 a week on candy, my allowance might grow 10%, 15%, or 20% per year. Show me how much I will have saved after 3 years in each case."
The AI calculates three answers at once. You pick the one that seems most realistic. The AI did the math. You made the decision about what is realistic.
That is AI financial modeling. The AI does the calculating. You do the thinking about what numbers make sense.
Mindmap: The Complete Picture
The mindmap shows the full workflow: defining model structure (revenue, costs, timing, growth, scenarios) leads to building revenue projections with AI formula generation, which feeds into scenario analysis (base, optimistic, pessimistic), which connects to cash flow statements (operating, investing, financing) with cash runway calculation, and ends with validation (assumption checking, benchmarking, stress-testing). Human judgment wraps the entire process: you define assumptions at the start and validate results at the end.
UNOP Sound (University 365 Neuroscience Oriented Pedagogy)
Take five minutes to consolidate your memory. Play the isochronous tone track (10Hz alpha frequency) with your eyes closed. Alpha-frequency tones after a learning session support consolidation, helping move what you just learned from short-term to long-term memory.
[Audio player: UNOP Post-Lecture Isochrone (10Hz, 5 minutes)]
Practical Exercise: Build a Mini Financial Model
Exercise: 15-Minute Financial Model Sprint
Choose a business: Pick a simple business you understand (coffee shop, online store, consulting practice).
Define your structure: Write down your revenue drivers (what you sell, at what price, to how many customers) and cost structure (fixed and variable costs).
Set assumptions: Choose a growth rate, gross margin, and one key variable to test.
Use AI to build: Open ChatGPT, Claude, or Excel Copilot and ask it to build a 3-year projection with your assumptions. Ask for three scenarios.
Validate: Review the AI output. Are the formulas correct? Are the assumptions realistic? What happens in the pessimistic case?
Present: Create a one-page summary with the key numbers: revenue projection, cash flow, and break-even point for each scenario.
What to Look For
Did AI use the right formulas? Common errors: using simple growth instead of compound growth, mixing monthly and annual figures, forgetting to account for seasonality.
Do the pessimistic case results make sense? If the pessimistic case still shows strong growth, your downside assumptions may not be pessimistic enough.
What is the cash runway in each scenario? This is the number investors care about most.
The model is a tool, not a crystal ball. Its value is in helping you think through scenarios, not in predicting the future.
Applied AI Connection
This exercise demonstrates the CI-First workflow in financial modeling. You defined the structure and assumptions (human intelligence). AI built the model and ran scenarios (AI amplification). You validated the results and made judgments (human intelligence). The speed gain from AI lets you iterate faster: you can test 10 assumption sets in the time it used to take to build one model. That means you explore more possibilities and make better-informed decisions.
Glossary
Term | Definition |
**Financial Model** | A structured spreadsheet or program that projects a company's financial performance based on assumptions about revenue, costs, and growth. |
**Revenue Driver** | A variable that directly generates revenue: units sold, price per unit, subscription count, or contract value. |
**Scenario Analysis** | Building multiple versions of a financial model with different assumptions to understand the range of possible outcomes. |
**Cash Flow Statement** | A financial statement that tracks cash entering and leaving a business across operating, investing, and financing activities. |
**Cash Runway** | The number of months a company can operate before running out of cash, based on current cash balance and burn rate. |
**Sensitivity Analysis** | Testing the impact of changing one variable at a time in a financial model to identify which assumptions have the most impact. |
**Break-Even Point** | The point at which revenue equals costs and the business stops losing money. |
**Gross Margin** | Revenue minus cost of goods sold, expressed as a percentage of revenue. |
**Net Revenue Retention** | The percentage of recurring revenue retained from existing customers over a period, including expansion, contraction, and churn. |
**CI-First** | Co-Intelligence First: the U365 principle that human intelligence orchestrates and AI amplifies. CI = HI + (AI x HI). |
**5M2S** | 5 Minutes to Success: the U365 principle of using AI to compress time-intensive tasks into minutes. |
**Monte Carlo Simulation** | A technique that runs thousands of random variations of a model to produce a probability distribution of outcomes. |
Quiz: TEST YOUR UNDERSTANDING
1. What is the most important thing AI cannot do in financial modeling?
A) Write Excel formulas
B) Generate charts and visualizations
C) Determine whether your assumptions are realistic
D) Run multiple scenarios simultaneously
2. What are the five core components of a financial model?
A) Assets, liabilities, equity, revenue, expenses
B) Revenue drivers, cost structure, timing, growth assumptions, scenarios
C) Market size, competition, pricing, distribution, marketing
D) Cash, inventory, receivables, payables, debt
3. Why is cash runway the most important output for startups?
A) It determines the company's valuation
B) It shows how many months until cash runs out, which is critical for survival
C) It measures customer satisfaction
D) It calculates the tax liability
4. What is the purpose of sensitivity analysis?
A) To test how changing one variable impacts the model outcome
B) To determine the company's market share
C) To calculate employee bonuses
D) To measure brand awareness
5. In the CI-First validation loop, what are the two roles of human intelligence?
A) Building formulas and creating charts
B) Defining assumptions at the start and validating results at the end
C) Uploading data and formatting spreadsheets
D) Writing code and debugging errors
Answers: 1-C, 2-B, 3-B, 4-A, 5-B
Related Resources
U365 INSIDE Publications
Book Essential: Co-Intelligence by Ethan Mollick: The Centaur model and human-AI collaboration
Lecture 1: AI for Market Research: The first lecture in the Business AI Series
External Resources
Microsoft Copilot for Excel: Official documentation and tutorials: microsoft.com/copilot
ChatGPT Advanced Data Analysis: OpenAI's guide to data analysis with AI: openai.com
Wall Street Prep: Financial Modeling: Industry-standard modeling training: wallstreetprep.com
Aswath Damodaran's Valuation Course: Free NYU valuation lectures: pages.stern.nyu.edu
Related U365 Lectures (Coming Soon)
Lecture 3: AI-Driven Customer Segmentation (UIB, Business AI Series)
Lecture 6: AI for Sales Forecasting (UIB, Business AI Series)
Lecture 9: AI for Investment Analysis (UIB, Finance Series)
U.Copilot for This Lecture
Discuss this lecture with U.Copilot, your AI chat companion trained on this content.
Copy and paste the following prompt into the U.Copilot chat on university-365.com:
You are U.Copilot for Lectures, an AI chat companion specially trained on University 365 lecture content. You are helping a Fellow who just completed the lecture "Financial Modeling with AI: Excel + Copilot in Practice" from the Business AI series at the U365 Institute of Business (UIB). Your role is to help the Fellow deepen their understanding of AI-powered financial modeling. You can: - Clarify any concept from the lecture (model structure, revenue projections, scenario analysis, cash flow, validation) - Provide additional examples of AI prompts for financial modeling tasks - Explain how to use Excel Copilot or ChatGPT for specific modeling scenarios - Discuss how to validate AI-generated financial models - Help the Fellow apply the CI-First approach to their own financial modeling project - Suggest follow-up learning based on the Fellow's industry and interests Always maintain U365's CI-First approach: encourage the Fellow to think critically, verify AI outputs, and maintain human judgment as the orchestrator of AI tools. Use the UP-Context Method: provide context-rich, role-aware responses that account for the Fellow's learning level and goals.
Next Steps
Now that you understand how to use AI for financial modeling, here is what to do next:
Try the practical exercise above to build a 3-year financial model for a business you know
Experiment with different AI tools to see which produces the best financial models for your use case
Take Lecture 3 in this series: "AI-Driven Customer Segmentation" to learn how AI helps you understand your customer base
Take Lecture 6: "AI for Sales Forecasting" to go deeper into revenue prediction techniques
Join a UIB program if you want structured learning in business management and digital entrepreneurship: visit university-365.com/tuition
Financial modeling with AI is not about replacing your financial expertise. It is about compressing the mechanical work so you can spend more time on what matters: thinking through assumptions, evaluating risks, and making strategic decisions. The best financial modelers in the AI age are not the ones who build the fastest spreadsheets. They are the ones who ask the best questions and apply the best judgment to the answers.
IMPORTANT NOTICE
This lecture is published by University 365 as part of its INSIDE Publications Hub. The content is free to read for all visitors. Lectures in this series may be part of a structured academic program leading to a Micro-Credential for your Career (MCC). To enroll in an academic program, visit university-365.com/tuition.
This content is for educational purposes. While we strive for accuracy, AI is a fast-moving field. Verify current tool capabilities and financial formulas against primary sources for professional applications.
Copyright University 365, Inc. All rights reserved. This content is protected under University 365's copyright policies. For permissions or inquiries, contact uda@university-365.com.
Published by the Department of Academics, University 365.
Lecture delivered by the University 365 Institute of Business (UIB).
Denise Cromwell, Dean of Business, UIB
Signed for the academic year 2026.
Comments