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 spreadsheet shortcuts for editing cells in financial modeling, inserting and deleting rows or columns, adding comments, and viewing formulas with the grave accent.

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.

Master entering formulas in Excel by using cell references to sum scores, understand the difference between formulas and functions, and apply practical, corporate-ready examples.

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.

Count text occurrences with Excel's count and count if functions, handling exact matches, case-insensitive counting of 'green,' and pattern matching with ? and * to count specific text variants.

explore essential excel formulas with the offset function and count function to analyze regional sales data; learn how relative, absolute, and mixed references shape dynamic results in spreadsheets.

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.

Learn and apply Excel logical functions like if, and, nested ifs, and switch, with practical demos and a project to master boolean tests and conditional results.

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.

Learn to use weekday and text functions to display day names, and use the networkdays function to count working days between dates, excluding weekends and holidays.

Explore the Excel work day function to calculate a future date after a set number of days, accounting for weekends and holidays with holiday lists and date formatting.

Explore Excel string and text functions, including concatenation with ampersand, left, right, mid, length, substitute, and find, plus date-time calculations and rounding to determine quarters.

Explore Excel string functions to count words and occurrences in a sentence by trimming spaces, measuring length, and substituting characters, with practical formula examples.

Learn to use search, substitute, and replace to manipulate text, and master concatenation with ampersand, concatenate, and xjoin to build full names while handling blanks.

Learn how to use vertical lookups in Excel, create a two-column lookup table, and apply VLOOKUP with exact and approximate match, while introducing MATCH, INDEX, and the lookup tools.

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.

Learn how to use left lookups with vlookup, and master index and match for exact match lookups from non-first columns.

Explore Excel lookups and references, using the address, max, match, and index functions, with indirect referencing and named ranges, and learn offset-based retrieval through practical examples.

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.

Explore essential Excel financial functions, including ppmt and ipmt, to model loans, calculate payments, interest, and principal, and build an amortization table.

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.

Analyze how to use Excel statistical functions to compute percentiles and quartiles, and apply max and min on practical datasets.

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.

Analyze the sales productivity data database, detailing zones, departments, levels, and gender, with monthly targets and achievements, and learn the backend formulas and charts that drive the analysis.

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.

Learn to use Excel's screenshot tool to capture images and paste them into a new screenshots sheet for dashboards, with onscreen clipping and remove background options.

Learn how to build charts with a backend database, calculate percentage achievement month over month, and create dashboards using concatenate formulas and drag and fill techniques.

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 how horizontal bar charts compare mean or percentages across eight or more mutually exclusive categories, using candy types as examples.

Learn to position labels in pie charts for clarity, choosing centered, inside, or outside placements, and apply best fit, legend options, and data labels for readability.

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 donor charts as layered alternatives to pie charts, showing outer-layer distributions and enabling comparisons of male and female data across years.

Explore how area charts visualize magnitude of change across data points and multiple variables, using trend lines, stacked areas, zone distributions, and target versus achieved comparisons in Excel.

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 how pyramid charts visualize population by age and gender, using central axes and income brackets to compare male and female distributions and reveal demographic trends.

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 how smart art in excel uses basic block lists to organize non-sequential information with diagrams, lists, and pictures to visually communicate data.

Explore how picture list and other smart charts organize information, showing a circle process, step sequences, and labeled visuals to convey main topics, subtopics, and data.

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.

Create pyramid-style visuals and mark charts to organize data, captions, and pictures. Design and format options, dashboards, pivot charts, and macro charts are demonstrated.

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 absolute valuation methods: dividend discount model, asset-based valuation, liquidation vs going concern values, and intrinsic value via discounted cash flow, plus relative valuation.

Explore relative valuation through comps and comparable transactions, guided by the law of one price, to value a subject company using multiples and enterprise value benchmarks.

Explain how projecting future cash flows and discounting them to present value uses a suitable cost of capital, like the weighted average cost of capital.

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.

Compare dcf and comps to understand intrinsic value, theoretical soundness, and practical investor communication. Examine the impacts of assumptions, terminal value, and data quality on valuation.

Learn the steps of discounted cash flow analysis to estimate a company's intrinsic value by forecasting cash flows, growth, and terminal value, then discounting to determine enterprise and equity value.

Explore how rising cash flows boost company value and how higher discount rates lower value, using present value calculations and terminal value concepts in an Excel example.

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.

Learn to compute cash flow from operations using the indirect method, adjust for non-cash expenses and working capital, and derive CapEx and asset sale proceeds for FCFE or FCFF.

Explore how to estimate the cost of debt for a firm within the WACC framework, including after-tax cost, tax shields, marginal tax rate, yield to maturity, and rating-based approaches.

Use industry beta, remove debt effects to obtain an unlabeled beta, then re-lever with company-specific debt to estimate cost of equity and debt using market-value weights.

Finalize the case study by calculating enterprise value via perpetuity and multiples, then derive the equity value per share from net debt and diluted shares.

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 core financial modeling and valuation concepts by linking basic accounting equations and beta to selecting appropriate valuation methods, with case studies and balance sheet insights.

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.

Learn how to select the right valuation multiples, balance enterprise value and equity multiples, and ensure apples-to-apples comparisons across industries and life cycles.

Explore equity multiples, especially price-earnings, and the role of book value in valuation. Compare trailing and forward-looking earnings and note data timing in valuation.

Explore industry specific multiples for valuation, using last twelve months data and P/E and EBITDA multiples across sectors like hotels, oil and gas, and healthcare to estimate fair value.

Gather data inputs for financial modeling by collecting share price, earnings per share, and enterprise value components, and organize sources like tickers and annual reports.

Link data from multiple sheets into a single comps output sheet for a clear valuation, using indirect references, consistent company names, local currency pricing, 52-week highs, and DCF.

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.

Apply descriptive statistics to company data, using mean, min, max, and range to assess valuation. Compare P/E ratios with peers and conclude that Siemens is undervalued relative to its peers.

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.

Master Excel formatting for a case study: create euro currency formats, set date formats, and use format painter, paste special, year and eomonth for date handling in euro million.

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.

Populate historical financials in Excel by assembling income statements, balance sheets, and cash flows, and calculate depreciation and amortization, stock-based compensation, and basic versus diluted shares for EPS.

Forecast revenue by breaking income statement revenue into drivers, compare straight-line and average growth methods, and apply management guidance to project multi-year revenue.

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.

Compute the basic shares outstanding by linking the equity schedule with issued and repurchased shares and the average share price to finish the model.

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.

Analyze sensitivity tables to see how revenue growth rate and gross profit margin affect outputs using data tables and what-if analysis in Excel, with hardcoded values.

Explore sensitivity and scenario analysis in financial modeling using Excel data tables to assess how input drivers like revenue growth rate and margin influence EBITDA and exit analysis.

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.

Explore intrinsic valuation through discounted cash flow analysis, valuing equity and enterprise value using future cash flows, discount rates, and sensitivity analysis.

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.