Financial Analysis & Modeling in MS Excel using Gen AI

Master Excel's if function and nested logic to build conditional models, including tiered commissions and multi-criteria project status assessments with and conditions.

Learn how and functions require all conditions to be true and how or functions require any condition to be true. See loan approvals and premium discounts as practical examples.

Master how to handle Excel errors gracefully using the IFERROR function to display custom messages, improve robustness, and keep financial models and dashboards clean when data is missing or zero.

Explore how vlookup performs vertical lookups to retrieve data from a table using a common identifier, covering exact and approximate matches, the left-to-right limitation, and practical tax and employee examples.

Master HLOOKUP, a horizontal lookup similar to VLOOKUP, that searches the first row and returns a value from a specified row. Apply it to time-based financial data like quarterly statements.

Xlookup modern, the versatile successor to vlookup and hlookup, enables left-right or up-down lookups, with built-in error handling and flexible match and search modes for multi-value retrieval.

Study average, median, and mode to understand central tendency in financial data, using transaction values and salaries to identify outliers and compare measures.

Explore standard deviation as a risk measure, comparing stdev.s (sample) and stdev.p (population) with stock returns and quarterly growth, including annualized volatility using 252 trading days.

Apply the correlation function to quantify the strength and direction of linear relationships between data sets, illustrated by stock returns and advertising spend to guide portfolio diversification and marketing decisions.

Learn to use sumif and sumifs to sum sales by product, sales rep, region, and date, using multiple criteria for targeted financial reporting.

Master countif and countifs to count data by single or multiple criteria, such as transactions over 1000, regional sales by rep, and Q2 2024 date ranges.

Learn to control precision in financial modeling with round, round up, and round down for currency, percentages, interest expense, and per share dividend, formatting to two decimals or whole number.

Leverage today and now date functions in Excel to create dynamic timestamps, and use date arithmetic with serial numbers to compute days until deadlines and age in days.

Master date manipulation with edate and eomonth to add or subtract months, find month ends, and align schedules for loan amortization, reporting periods, and forecasts.

Master advanced excel financial functions—present value, future value, loan payments, internal rate of return, depreciation, and multi-cell operations—to support investment analysis, capital budgeting, and debt management.

Explore how the future value function projects the growth of regular savings with monthly contributions and compound interest, with examples for retirement planning and long-term investments.

Calculate the future value of a retirement portfolio with an initial 25,000, monthly 300 for ten years, then 500 for 15 years, at 7% annual interest to 25 years.

Calculate the present value with the PV function using rate, nper, pmt, fv, and type to determine investment for a future sum, noting cash outflows are negative and inflows positive.

Use the PV function to find the present value of a $100,000 tuition and $500 monthly withdrawals, converting to monthly rates and discounting to today to estimate the upfront investment.

Use the pmt function to calculate a loan payment with constant payments and rate, illustrated by a mortgage and retirement contribution planning with negative cash outflows.

Use the nper function to determine how many months to reach a target loan balance after an upfront lump sum and fixed monthly payments, with rate, pv, fv, and type.

Use the rate function to determine the interest rate per period, convert monthly rates to the effective annual rate, and account for compounding in loans and investments.

Understand net present value as a tool that discounts future cash inflows at a discount rate to compare them with initial investment. Use the Excel NPV function to assess profitability.

Learn how to compute the internal rate of return (IRR) from cash flows, compare it to the hurdle rate, and use Excel IRR to assess project profitability.

Excel's XNPV computes NPV with irregular cash flows by discounting each flow using its exact date. This precise method, using a rate and dates, guides project viability decisions.

Learn to compute extended internal rate of return using xirr for irregular cash flows with exact dates in Excel, including syntax, dates, and practical venture capital examples.

Calculate straight-line depreciation to allocate asset cost over its useful life using the SLN method in Excel, including salvage value and partial-year adjustments for accurate financial reporting and tax savings.

Calculate accelerated depreciation with declining balance methods using the db function, showing higher early-year charges and a fixed-rate applied to remaining book value.

Learn how double-declining balance depreciation accelerates asset expense, automatically shifts to straight-line toward salvage value, and how to build an Excel depreciation schedule using cost, life, salvage, and period.

Explore Excel's data analysis tools, from data validation and conditional formatting to sorting, filtering, and text to column cleaning plus remove duplicates, then master pivot tables, charts, and what-if analysis.

Master data validation in Excel to enforce drop-down lists and rules, including input messages, error alerts, and dependent dropdowns with named ranges and indirect for region and sales reps.

Learn how conditional formatting automatically highlights overdue invoices in a spreadsheet by applying an and rule that uses today to compare due dates.

Apply multiple conditional formatting rules to a stock data dashboard, highlighting daily swings, high and low price-earnings ratios, and strong dividend yields for quick insights.

Identify stocks near 52-week highs and lows with conditional formatting rules, compare current price to 95% of these levels, and flag unusual volume for quick visual analysis.

Learn how to sort sales data by order date and apply successive filters to isolate laptop sales in the North region, enabling focused, actionable financial analysis in Excel.

Develop expertise in multi-level sorting and layered filtering of a financial expense dataset, organizing by department, category, date, and amount to identify pending, approved expenses within the current quarter.

Learn to clean and split messy data in Excel using text to columns and removing duplicates to create product category, type, and details columns for granular analysis.

Learn to convert fixed-width legacy financial data into clean columns using text to columns, preserving leading zeros, formatting dates and amounts, and enabling pivot tables and financial modeling.

Remove duplicates in Excel using multi-column criteria, keeping the first unique record by employee ID and email. Learn to select headers and understand how to clean data for accurate analysis.

Build pivot tables and pivot charts in Excel to transform raw transaction data into revenue and expense summaries by department, enabling income statements and dynamic reports with visual trend analyses.

Build a dynamic pivot table and pivot chart dashboard to analyze profitability by country and product line, calculating gross profit, net profit, net profit margin, using slicers for interactive filtering.

Use goal seek, a reverse-engineering, what-if analysis tool, to adjust inputs and hit target outputs, verify feasibility, and compare scenarios for loan, pricing, and profit targets.

Learn to use the Scenario Manager to create and compare base, best, and worst cases, project revenue, COGS, expenses, and net profit with ROI insights.

Utilize data tables to perform sensitivity analysis on NPV and mortgage payments, linking the main NPV calculation to one variable and two variable tables for rapid scenario comparison.

Lay the foundation for professional financial modeling by applying six principles—transparency, flexibility, robustness, consistency, integrity, and clarity—to create models easy to audit and that forecast cash flow and debt.

Create a common language with a glossary, then build a live three-statement financial model (income statement, balance sheet, cash flow) linked to show practical insights for management and investors.

Learn best practices for professional financial modeling with a single input sheet, color-coded drivers, and transparent links that drive revenue, expenses, and balance sheet projections.

Identify income statement drivers and input assumptions, including 2024 revenue, 8% growth, COGS 55%, 20% gross margin, fixed R&D, depreciation, capex, working capital, debt, tax, and dividend payout.

Build income statement first, driven by revenue growth and expense assumptions. Use net income to drive cash flow and inform the balance sheet, avoiding circular references in the three-statement model.

Build a dynamic balance sheet and model by linking cash, receivables, inventory, PPE, and debt to a 2025 income statement forecast with 8% revenue growth, using formulas and balance checks.

Build a 2025 balance sheet by linking assets, liabilities, and equity to cash flow and income statements, using days sales outstanding, days inventory outstanding, capital expenditure, depreciation, and debt schedules.

Understand how the cash flow statement links net income to cash by adjusting for depreciation, working capital changes, and capex, revealing operating and investing cash movements.

Trace how change in cash reflects operating, investing, and financing activities, including a -50k debt repayment and 30% dividend payout, linking ending and beginning cash to reconcile the three-statement model.

Read a company's numbers as a story by linking the income statement, balance sheet, and cash flow statement to reveal CapEx, depreciation, working capital, and the three statement narrative.

Master financial modeling fundamentals, learn glossary terms, and build a single source of truth with an inputs sheet that links income statement, balance sheet, and cash flow statements for investors.

Explore sensitivity analysis in Excel for financial modeling, using tornado charts to gauge how changes in revenue, costs, and expenses affect net profit in base-case scenarios.

Examine how percentage changes in sales price and costs affect net profit, build negative and total impact tables using absolute values, and sort data for a chart-ready sensitivity analysis.

Perform a sensitivity analysis using tornado and butterfly charts in Excel to identify key drivers of net profit and npv, and compare projects alpha and beta.

Assess year zero as initial investment with upfront costs. Include negative cash flow, apply the discount factor to future cash flows for NPV, and note COGS as revenue percentage.

Calculate yearly revenue growth, apply present value calculations, and evaluate base NPVs for projects A and B under different discount rates to assess viability.

Conduct a one-variable-at-a-time sensitivity analysis for scenarios a and b, computing npv changes from the base case to reveal key drivers and robustness.

Perform a sensitivity analysis on two projects by varying growth rate, cost of goods sold, initial investment, and discount rate by ±2%, computing NPV and impact on the base NPV.

Consolidate positive and negative impacts from both scenarios into a single table, compute total and absolute impacts, and sort to generate the butterfly tornado chart.

Create a 2D stacked bar tornado chart for sensitivity analysis in Excel, using helper columns to form a butterfly effect, reverse category order, and apply labels in red and green.

Explore NPV sensitivity through a butterfly chart to compare cost of goods sold as a percent of revenue in project A and initial investment in project B, guiding investment choices.

Learn to translate sensitivity analysis into actionable insights by visualizing financial data with line charts, revealing trends, patterns, and anomalies across five regions' quarterly data.

Explore how to visualize financial data with line charts and secondary axes to identify top regions like Asia Pacific, assess year-over-year growth and seasonality, and communicate insights.

Visualize quarterly financial data for Innovate Tech Solutions, Global Dynamics, and Quantum Enterprises using a grouped bar chart to compare revenue, gross profit, net profit, and margins.

Learn how to combine line and bar charts into a combo chart with a secondary axis to analyze revenue growth alongside gross profit margin, revealing profitable growth.

Create a comprehensive combo chart that visualizes divisional revenue as clustered columns and overall operating margin percentage on a secondary axis, using a pivot chart for quarterly data.

Learn to analyze revenue and margin trends through chart-driven insights, identifying cost drivers across software and cloud versus consulting services, and using combo charts to drive profit improvements.

Audit your financial model with Excel's built-in error checking, trace precedents, and trace dependents to identify and correct errors, building trust.

Audit financial models by showing formulas to reveal inputs versus calculations, using the formulas tab to toggle formulas. Evaluate complex formulas step by step with the evaluate formula tool.

Use error checking and trace error to locate and fix formula errors in a financial model, showing how to evaluate steps, inspect precedents, and correct issues like dividing by zero.

Explore automating repetitive finance tasks with macros (VBA) in Excel, formatting data, cleaning data, and generating reports to save time and enhance analysis.