Excel Financial Modeling and Business Analysis Masterclass

Learn to build a simple budget in Excel by calculating income, taxes, and expenses with cell references and the sum function, then format results to reveal net income and savings.

Identify the data you need for your financial model, starting with internal historical financials, then gather external data from BLS, Fed, and US Census using the Fred add-in.

Define the problem your financial model solves by constructing do nothing, do something, and comparison scenarios to guide decision making.

Design a multi-tab financial model that layers inputs, calculations, and outputs, using a back-to-front flow and continuous assumption documentation to enable scenario analysis and clear outputs.

Learn methods for documenting assumptions in financial models, including in-cell notes, threaded comments, data validation messages, and hyperlinks, plus dynamic text that updates with inputs.

Model a basic profit spreadsheet in Excel to explore how fixed costs, variable costs, and dual selling prices affect profit under uncertain demand and order quantity.

Create a from-scratch Excel model for a small business, detailing fixed and variable costs, full-price and reduced-price revenue, inventory decisions, and profit using min and max.

Master cell referencing in Excel for financial modeling, including relative, absolute, and mixed references, plus practical demos on using dollar signs, F4, and copying formulas for consistency.

Discover how named ranges simplify formulas by replacing cell references with meaningful names, and learn to create, use, and manage them in Excel with the name box and name manager.

Explore dynamic ranges and dynamic array formulas in Microsoft 365, using the unique function to generate a list of unique values and understand spill behavior.

Learn to create a multi-sheet financial model in Excel by linking cells to build a P&L, move assumptions to a separate sheet, and generate a clear summary.

Apply data validations and protections, including dropdown lists and sheet protection, to guide inputs and deter unauthorized edits.

Create a spreadsheet model in Excel to project bookshelf costs for Mellon Wood by varying wood and labor cost growth rates, using tables and charts to show cost changes.

Model a five-year cost projection for the Melon Wood Company in Excel, using cherry and oak materials, labor costs, and annual cost increases; build a table and chart.

Explore how to add scenarios and perform sensitivity and what-if analyses in financial models, linking inputs to outputs to test risks, make decisions, and understand interdependent effects.

Master goal seek in Excel to drive a formula to a target by varying a hard-coded input, using what-if analysis for cost control and break-even calculations.

Develop proficiency with goal seek within what-if analysis in Excel to determine break-even and target profit by adjusting the number of manuals in a cost model.

Learn to build drop-down scenarios in Excel by linking a scenario table to input cells, using data validation to drive a three-year call center cost forecast and sensitivity analysis.

Explore data tables as a tool for sensitivity analysis in a profitability model. Test profit margin and total profit against changes in sales price per student and cost per exam.

practice data tables for financial modeling by building single and dual variable data tables to explore fixed expenses and revenue what-if scenarios.

Explore how to use scenario manager within Excel's what-if analysis to model loan repayments with PMT, testing changes in interest rate and term.

Use scenario manager to compare current price, a $5 price decrease with 15% more sales, and a $5 price increase with 15% fewer sales to gauge projected profit.

Learn how to use Excel's Solver to optimize producing 12 manuals in 20 weeks, setting objectives, changing cells, and adding constraints, with Scenario Manager for multiple scenarios.

Master speed and accuracy in financial modeling with Excel shortcuts like F2 edit formula and F4 dollar toggle. Utilize pivot table shortcuts, advanced filter, and visual basics for applications.

Create a date table in Excel quickly, from January 1, 2019 to December 31, 2023. Use the hidden series menu and keyboard shortcuts to generate all dates without dragging.

Master the dates method in Excel to generate weekday sequences for a year, exploring weekend gaps, series across columns, and extending to months or years.

Master generating number patterns in Excel by dragging with the control key to build sequential lists, and use ctrl+shift+a to view formula components for date and Vlookup.

Learn to sum data in Excel using the alt equals shortcut, quickly applying auto sum across rows or columns, with caveats when data contains gaps.

Create charts quickly with shortcuts like alt F1 and build a reusable template. Clean visuals by adjusting series, grid lines, and data labels using quick right-click and control 1.

Learn to save a chart as a template, apply it to revenue and expense charts, size identically, colorize charts, and build a reusable dashboard in Excel.

Manipulate and reincorporate large Excel datasets using filter, paste special with skip blanks, and copy-paste techniques to safely update the original worksheet.

Convert numerical scores into words and visuals using icon sets and conditional formatting in Excel, mapping values with Vlookup and an approximate match to show poor, average, and good.

Master the camera tool in Excel to capture dynamic data snapshots for dashboards, customize the quick access toolbar, and link across sheets for live financial reports.

Learn how to use Excel pivot tables to summarize data by country and create per-country sheets quickly using the report filter pages shortcut from the Analyze menu.

Learn to use Excel's advanced filter to extract country-specific records by payment status, copy results to another location, generate unique country lists, and automate with macros.

Learn to distinguish formulas from functions in Excel and how to find the right function when you’re unsure. Master the most commonly used and critical functions for financial modeling.

Learn the difference between formulas and functions in Excel, where formulas use cell references and functions are predefined, with steps to find and insert the right function.

Master relative and absolute cell references in Excel, including locking with the dollar sign and using F4 to toggle, enabling reliable copying for dashboards and reports.

Master Excel's sum, max, min, and average functions, including non-contiguous ranges and autosum shortcuts, to analyze data and note that average ignores empty cells while including zero values.

Discover how count and counta work in Excel, counting numbers only versus non-empty cells (including text and errors), with examples for doctors and yearly sales and how blanks affect results.

Master rounding in Excel with round, roundup, and rounddown, and apply them to financial models using growth, inflation, escalation, and staffing forecasts.

Master the sumifs formula to sum values by multiple criteria using a sum range and criteria ranges, including dates, greater than or equal to, and wildcard filters.

Master vlookup, hlookup, and xlookup for building financial models in excel 365, focusing on exact and close matches and robust formulas.

Learn to build an index and match formulation to look up a state within a range and return a chosen column, using exact-match logic and flexible row and column indexing.

Use the offset function in Excel to define a starting point by offsetting rows and columns from a base cell, then sum a dynamic range defined by height and width.

Master custom number formatting in Excel using the format cells dialog box to create custom syntax for numbers, thousands, millions, currencies, and date and time, preserving data integrity.

Analyze financial data in excel using pmt, npr, pv, npv, and ex npv to compute loan payments, principal and interest, and investment present value.

Learn how simple and compound interest differ, how the principal and time value of money determine future value, and how to apply p(1+r)^n to investing and borrowing.

Calculate the monthly payment for a fully amortized loan using Excel's PMT function, with rate, periods per year, total payments, present value, and type to reflect end-of-period payments.

Calculate loan payments by separating principal and interest using a mortgage example, showing how PMT and IPMT reveal how principal grows over time while interest declines.

Apply the pv function to determine today's value of a future investment by using the discount rate, future value, and type, then compare to the asking price.

Compute net present value by comparing two 300,000 investments with different cash-flow timing, using a 4.5% discount rate to compare npvs of about 29,246 and 20,991.

Learn to calculate net present value for irregular cash flows using the XNPV function with exact dates and a discount rate, and see how lowering the rate raises NPV.

Learn to compute the future value of an investment in Excel using the FV function, with rate, periods, and present value inputs, including manual and inflation-based growth.

Explore calculating the internal rate of return in Excel using IRR, XIRR, MIRR, and RRI, compare timing of cash flows, reinvestment rates, and implied rates to evaluate investments.

Use rate function in Excel to find the rate of an annuity with a $90,000 upfront payment and $1,000 monthly payments over eight years, converting monthly to annual by 12.

Explore how straight-line depreciation spreads an asset’s cost over its economic life using Excel, including calculating annual depreciation with SLN and handling salvage value.

Master date functions like today, edate, and eomonth to build timing flags in financial models and tie operating costs to the project start date.

Master practical Excel techniques to prorate costs and compute depreciation using day, EOMONTH, and SLN functions for rent calculations and mid-month asset depreciation.

Learn how to use the if function to build logical comparisons, validate deals against costs, create spend schedules, anchor formulas, and add error checks in financial models.

Learn to compute the payback period in Excel by building a cumulative cash flow, using IF and XLOOKUP to identify the payback date, and calculating years to payback with YEARFRAC.

Learn to calculate compound annual growth rate (CAGR) from starting and ending values using the long formula or Excel functions RATE and RRI, with hands-on examples.

Create a debt schedule in Excel that splits PMT repayments into principal and interest with IPMT and PMT, rolling opening balances into closing balances for cash flow and balance sheet.

Apply SLN and IF to compute monthly depreciation in a financial model, practice mixed referencing, and validate depreciation starts after the asset spend date using headers and freeze panes.

Learn to build a depreciation schedule in excel using sumifs to capture fixed assets and capex, convert dates with eomonth to the first of the month, and perform error checks.

Learn how to calculate the weighted average cost of capital (wacc) using capm with beta, risk-free rate, and market risk premium, and apply it to debt and equity in Excel.

Learn to value a company using DCF by calculating free cash flow to the firm, applying WACC, estimating perpetual growth, and terminal value in Excel.

Explore essential Excel functions for financial modeling, comparing sumproduct and sumifs, applying sum and product in commissions and taxation, and mastering lookup, indirect, and address formulas to build dynamic reports.

Explore two-criteria summing in Excel by building sumifs and sumproduct formulas on a data set, using region and market segment as criteria.

Explore when to use sumifs versus sumproduct in Excel for tabular data, and learn how sumproduct handles horizontal and vertical criteria with a practical Northeast corporate order quantity example.

Explore advanced Excel techniques by combining sumifs, sumproduct, index and match to handle wildcards and multi-criteria summing across regions and segments.

Master the Excel lookup formula to retrieve values for codes like 100, 101, and 102, and compare it with vlookup and hlookup.

Use indirect and address formulas to create dynamic, sheet-aware references that pull data from different sheets (such as Northeast and West) and adapt as you drag across rows and columns.

This lecture introduces charting for analysis, building three chart types (trend column, bullet, four-segment line) to compare data like actual vs plan and year-over-year, while avoiding 3d or radar charts.

Master a trending column chart by combining actual, budget, and trend data, adjusting axes, formatting series, and labeling to create a clear financial infographic for modeling.

Learn to build and refine a stacked column chart of 2019–2022 performance targets, including data selection, switching rows/columns, axis formatting, and using a slicer for interactive exploration.

Create a clear four-segment line chart in Excel by organizing monthly data into four series, overlaying vertical lines with a scatter line, and refining axes, colors, and smoothing for readability.

Learn to create an Excel fan chart to visualize forecast uncertainty with base, best, and worst-case scenarios, using shading, data layout, and CAGR annotations.

Master the meco (Marimekko) chart in Excel to visualize market share and compare segments across categories, using a helper table, cumulative percentages, and formatted stacked bars.