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 the F6 shortcut to navigate the same column in a split data set and view data in equal parts, while using F7 for spellcheck and F9 for recalculation.

Learn to use f10 to activate the menu bar, f11 to create charts, and f12 to save as, plus control plus shortcuts for dates, formulas, and text.

Master formatting text and numbers in Excel using shortcuts, including Ctrl+1 to open the format dialog, adjust font, size, color, and apply bold or underline.

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.

Learn how to enter and edit Excel functions and formulas, use the sum function across a data range, and understand predefined functions and their categories, with practical examples.

Learn to create and manage named ranges, named constants, and use the name manager in Excel formulas, plus dynamic ranges with the offset function for reliable sums.

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 how to count text occurrences and logical values in Excel using countif, wildcards, and range logic, including true, false, and blank cells.

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.

Explore nested if and the switch function in Excel, demonstrating how multiple conditional checks in logical functions yield results through practical demos and a basic project.

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 essential Excel text functions, including upper, lower, and proper, and learn to split delimited text into columns using left, right, and find, plus the text to columns feature.

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

Explore essential Excel text functions by applying trim and clean to remove non printable characters, compare exact, find, and search, and master replace, substitute, and string concatenation.

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.

Explore the match function and vlookup for exact and approximate lookups in Excel. Use the index function to retrieve values by row and column and combine it with lookup.

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

Learn how to perform a two-way lookup in Excel using index and match to fetch sales by month and product from a lookup table.

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 present value and future value calculations to compare loans and investments under different terms, using annual and monthly contributions, 6 percent annual rate, and annuities.

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.

Understand random numbers with rand and rand between, apply round to decimals, and use the rank function to rank values within a specified bottom to top range.

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

Learn to apply Excel's round, round up, and round down functions, plus the even and odd and mod functions, using if statements to identify even and odd numbers.

Master trendline and trend analysis in Excel, using forecast and historical statistical functions, and learn to create and tailor charts with data labels and axis titles.

Explore building and interpreting trend lines and trend analysis, choosing linear or exponential trends, displaying equation, r-squared, and using forecast and trend functions to predict future sales data.

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

Examine how the int and truncate functions trim decimals, compare round up, round, and round down, and illustrate how truncation and rounding determine integers and decimal values.

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.

Create shapes in Excel to build diagrams, from lines and arrows to rectangles and basic shapes. Use callouts, banners, and scrolls to craft illustrations; adjust fill, outline, and effects.

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.

Learn how to build and customize a data table and a column chart, including legend, axis options, tick marks, and switching rows and columns to compare calendar years.

Explore various column chart types, from normal and 2d to 3d, including 100 percent stacked, cylindrical, cone, pyramid, and cluster variations for data visualization.

Explore how horizontal bar charts compare mean or percentages across eight or more mutually exclusive categories, using candy types as examples.

Demonstrate how pie charts reveal a one-dimensional breakdown of biotech data, showing percentage distributions across age groups and time points while colors denote categories and totals sum to 100%.

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 surface area charts, three-dimensional visuals that map three data points—product name, notes, and value sold—into a topographic view for multi-variable analysis, including disease outbreak patterns.

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.

Learn how mosaic or mekko charts compare multiple categories and variables at once, using axes, size, and color to reveal market potential and demographic and salary insights.

Learn how the radar chart, also known as a star chart, displays multiple quantitative variables as a polygon from a central point. Use scaling and percentages to compare data.

Explore creating a sunburst chart to visualize a regional and head office hierarchy, adjusting segment sizes and colors to reflect percentages like Washington, D.C. 19% and Northeast 19%.

Explore scatter plots to visualize relationships between two variables, using x and y axes, color-coded data points for a third variable, and trend lines to reveal correlations.

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

Create and visualize data with combo charts by linking a database to a chart, using a trend line and a secondary axis to compare states, including Washington, D.C.

Create dashboard charts in financial modeling, including area, combo, and donut pie visuals. Use formulas to pull 2016 and 2017 target and achieved data, forming a readout chart with alignment.

Create a dashboard chart by dividing into 50 percent halves, coloring completion and non-completion, and showing data labels; link sub department choices to target cash flow chart with data validation.

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 build and customize a map chart in Excel, plotting data points on a map, adjusting axes and scale, and highlighting regions based on thresholds.

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 visual representations of task processes and workflows, using picture-containing rectangles and alternating step sequences to illustrate progression, timing, and interconnections.

Explore converging text visualizations, including level-based text groups, circular layouts, and timelines, to illustrate workflows, progressions, and multi-step processes in an organized diagrammatic format.

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.

Master the basics of the discounted cash flow method, calculating free cash flow to value a company. Compare market value with book value and relate share price to valuation.

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.

Learn how terminal value converts infinite cash flows into a finite company value by assuming stable growth and discounting to the present using the discounted cash flow method.

Learn how discounted cash flow yields intrinsic value using two- or three-stage growth models, and distinguish firm value from equity value via the weighted average cost of capital.

Understand enterprise value and equity value alignment, and when to discount free cash flow to firm with WACC versus free cash flow to equity with cost of equity.

Explore the essential accounting equations, balancing assets with liabilities and equity, and learn how transactions—inventory, cash, revenue, and retained earnings—change the balance sheet.

Compute intrinsic value through discounted cash flows, basing value on cash flows and fundamentals; model changes in synergies and costs, customize assumptions, and use DCF to complement other valuation methods.

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.

Compare two methods for calculating free cash flow and show how a bottom-up, segment-based forecast of revenue and net income yields more accurate cash flow projections in a case study.

Learn to build a DCF valuation model in Excel by linking share data, using color coded inputs, and calculating cash flows with depreciation and working capital changes.

Explore how to estimate terminal value under going concern, using the stable growth model, multiples, and liquidation value, and assess sensitivity to growth rate and discount rate across growth stages.

Learn to compute net debt from the latest 10-k, use market or book values, adjust for convertible debt, and combine cash, debt, and quarterly data.

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.

Explore methods to estimate cost of debt, including bank loan rates and synthetic ratings. Use interest coverage and risk-free rate to derive default spreads and the after-tax cost of debt.

Explore how the cost of equity is estimated using the build-up method, CAPM, and models like arbitrage pricing and multi-factor approaches, detailing premiums for market risk, size, and company-specific risk.

Understand beta as a measure of a stock’s systematic risk and market volatility, calculated by regression against the market, influenced by business cycle sensitivity, operating leverage, and financial leverage.

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.

Explore sensitivity analysis by varying the long-term growth rate and WACC to see how stock value changes across scenarios, using a data table and conditional formatting.

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.

Learn to gather data, select financial modeling and valuation multiples, perform balance sheet analysis, and synthesize a football field valuation using DCF and relative methods to judge undervaluation or overvaluation.

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.

Discover how to compute enterprise value from the operator’s perspective by valuing net operating assets, excluding cash, and adjusting for debt, pension liabilities, minority interest, and market capitalization.

Learn how to compare enterprise value and equity value by adding minority interest to enterprise value, and by using net debt—debt minus cash—from market capitalization, debt, and cash.

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.

Compare comparable comps with dollar-based valuation and other methods to estimate a company's value, using data from annual reports and public sources.

Apply comps in valuation by using peer financials, public data, and annual reports to corroborate value estimates and explain growth and cost of capital assumptions.

Learn to value a company using absolute and relative valuation, sourcing data from annual and quarterly reports and press releases, and assess regional and segment dynamics.

Outline three steps for comparable comps: select peers, adjust for differences, and gather data for the analysis.

Select the right peers for valuation by matching industry, geography, and revenue model. Apply qualitative and quantitative filters—market cap, revenue, geography, and customers—to avoid mispricing.

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 how enterprise value equals market cap plus debt minus cash and liquid assets, and why enterprise value multiples reflect capital structure and liquidity considerations.

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.

Analyze how employee and investor stock options affect a company's valuation, focusing on in the money versus out of the money scenarios, dilution, and buybacks.

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.

Learn to construct a combined property plant and equipment and intangible assets schedule, apply depreciation and amortization, and model CapEx using balance sheet and cash flow notes.

Analyze intangible assets within a cash flow framework, linking revenue, depreciation and amortization, and capital expenditures to build a practical depreciation schedule in financial modeling.

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.

Learn to project long term debt, compute interest expense from notes, bonds, and loans, and reflect cash versus in-kind payments in the balance sheet and cash flow statement.

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.

Correct misclassification of short-term debt and reconcile it with cash balances. Apply minimum cash rules, decide debt paydown or borrowing, and finalize financial model including balance sheet and flow statement.

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.

Learn ratio analysis to interpret financial models, focusing on liquidity ratios (current, quick, cash), the cash conversion cycle, and profitability margins.

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.

Learn stock and bond valuation techniques—dcf, comparable, binomial models—and compare equity and debt financing, including accretion/dilution analysis, interview prep, and real-world deal insights.