Mastering Financial Modeling & Valuation with Excel

Explore how Excel supports data analysis, statistical analysis, reporting, charts, and macros, with practical examples and essential shortcuts to boost efficiency.

Master essential Excel shortcuts demonstrated with Ctrl sequences to insert symbols, format numbers and dates, copy formatting, hide rows, create tables, and insert hyperlinks for faster financial modeling.

Explore excel's count functions, including count, countif, and countifs. Learn to count numbers and count cells by single or multiple criteria across a data table.

Learn how to reference cells in Excel with relative, mixed, and absolute references to create dynamic formulas, adjust results when dragging, and anchor values in financial models.

Learn to use absolute, relative, and mixed references in Excel to lock constants, copy formulas across rows and columns, and perform efficient calculations.

Demonstrate how the and and or functions in Excel evaluate multiple conditions to return true or false, using passing marks as examples and comparing students' scores.

Master essential Excel date and time functions, including day, month, year, now, today, and date-time arithmetic, with hands-on formatting techniques to support financial modeling.

Explore horizontal lookups and the match function to retrieve values from a lookup table, understand exact versus approximate matches, and apply minus one to identify qualifying values.

Use Excel financial functions like PMT to compute loan EMI for different terms and rates, then compare monthly and annual payments and the total cost for investment or annuity planning.

Compute monthly loan payments and split principal and interest using PPMT and IPMT with a $100,000 amount at 4% annual rate over 20 years, using monthly periods and Excel-style formulas.

Explore Excel statistical functions, focusing on average and average if, then learn mean, median, mode, standard deviation, and maximum and minimum with practical data sets.

Explore excel's rand and randbetween to generate random numbers, apply max with zero to handle negatives, and implement the rank function with various arguments within core statistics functions.

Master how the Excel round function handles rounding to different digit places, including decimals, integers, and negative arguments, with clear examples and results.

Explore how to build and present data with Excel charts and SmartArt graphics, from data flow and illustrations to dashboards, icons, screenshots, and pivot tables and charts.

Learn how to insert pictures in Excel, manage file formats, and edit images with brightness, contrast, color, artistic effects, and picture styles for dashboards.

Build pivot tables and pivot charts, and use slicers to filter by state and department to reveal employee counts.

Master groupings in a database by building a lookup-based model in Excel, using VLOOKUP with exact and approximate matches to calculate percentage achieved and track yearly targets.

Explore Excel chart formulas, data selection, and labeling to build column charts for financial modeling and valuation.

Explore the layout of a column chart, including chart title placement, axis labels, borders and shadows, and data label options as you customize legends and axis details.

Explore design options for line charts, including axis titles, data labels and legend positioning, plus trend lines—linear, exponential, forecast, and moving average—with drop lines and error bars.

Explore building and interpreting a surface area chart to compare CPC and ratings, using averages and chart options such as normal surface, wireframe 3D, and corridor views.

Explore Instagram-like pie charts and one-dimensional distributions, using discrete intervals and continuous ranges to visualize weekly item sales and sample distributions.

Learn to build a thermometer chart in Excel to visualize achievement against targets across years, using data validation, index/match calculations, and color gradients.

Learn to refine your graphs by dimming or removing grid lines, placing legends, and applying clean, descriptive titles and dynamic data links for clear visuals.

Learn how to decide which chart to use by purpose: compare values with column, bar, line, scatter, or bullet charts; show composition, distribution, outliers, trends, and relationships.

Explore hierarchy and organizational charts, revealing top-to-bottom structures and layered groups of information. Analyze relationships, SWOT analysis, Venn diagrams, and matrices to map core ideas to outer layers.

learn the discounted cash flow method for valuing a company, including cash flow projections, cost of capital, terminal value, and sensitivity analysis in excel.

Explore how higher cash flows raise company value while higher discount rates or risk lower it, using DCF fundamentals, terminal value, and a perpetual growth example in Excel.

Explain valuing a company via enterprise value and equity value, considering cash. Demonstrate deriving free cash flow from net income by adding back non-cash expenses, after-tax interest, CapEx, and taxes.

Apply discounted cash flow with multiple valuation methods, including football field analysis, to assess enterprise value, considering preferred stock and changing capital structure; conduct sensitivity analysis to reveal ranges.

Explore basic valuation concepts through house price analogies, discounting future cash flows to present value with comparables, and introduce enterprise value and equity value within the accounting framework.

Explain how transactions affect assets, liabilities, and equity, illustrating the balanced accounting equation and the impact of cash, inventory, debt, and revenue on the balance sheet.

Understand how assets equal liabilities plus equity and how enterprise value connects operating assets and operating liabilities with equity, debt, and cash in a simple case using cash and inventory.

Explore relative valuation through comparable companies, applying the law of one price to estimate a subject firm’s value from peers, and discuss limitations like timing and availability of similar transactions.

Draw the comps sheet by deriving 2015–2016 sales from data sources or annual reports, adjust for exchange rates, and estimate equity value using ratios and net debt.

Explore commonly used valuation methodologies, including precedent transactions, comparable company analysis, and discounted cash flow, and learn how enterprise value, minority interest, and cash affect valuation in investment banking interviews.

Learn to build a Siemens case study financial model in Excel, using color-coded inputs to generate income statement, balance sheet, cash flow, schedules, and sensitivity analyses in one navigable model.

Populate the historical data for the income statement and balance sheet from annual reports, align year figures, and enter revenue and cost of sales as negative values.

Build a dynamic working capital schedule forecasting accounts receivable, inventory, and accounts payable as a percentage of cost of goods sold using drivers and averages, linking to the balance sheet.

Exclude land from depreciation in the depreciation schedule. Allocate land and buildings, compute net book value, accumulated depreciation, and depreciation base using a 14-year asset life with PPA and capex.

Learn to project long term debt in a financial model using beginning balance, additional borrowing, and end balance, while keeping other schedules constant when data is unavailable.

Explore the equity schedule to complete the balance sheet, predict common and treasury stock, and derive retained earnings from net income and dividends, leading to the cash flow statement.

Develop a working capital model by linking accounts receivable and inventory drivers to sales and cost of sales, and compare outputs with an equity research forecast.

Learn to model interest income from cash balances using the cash flow statement and mixed ratios. Average beginning and end cash to estimate the cash interest rate.

Finish the income statement, balance sheet, and cash flow by incorporating basic shares outstanding and completed schedules, including debt and equity, while validating Excel’s iterative calculation to avoid circular references.

Learn to troubleshoot balance sheet balancing in financial models using a change column and a check column, reconcile with cash flow, and spot common mistakes.

Identify and reconcile gross and net property, plant and equipment with depreciation, intangibles, and cash flow effects, using balance checks and circular references to spot model errors.

Analyze profitability and efficiency ratios, including gross, operating, and net profit margins, and return on assets and equity. Explore DuPont decomposition of return on equity and debt to equity ratio.

Explore scenario analysis to assess outcomes under best case, base case, and worst case, using index/match/offset lookups to reflect inputs like growth rate, interest rate, tax rate, and drivers.

Explore scenario analysis in a financial model, comparing base, best, and worst cases, linking hardcoded drivers to the model, and using match, offset, and conditional formatting to visualize outcomes.

Create an index at the start of your model to navigate quickly between income statement, balance sheet, and cash flow statements using internal hyperlinks and defined names.

Apply financial modeling best practices to build a model that is easily understandable and navigable, with well-documented assumptions, concise executive summaries when needed, and tightly linked, minimal sheets.