Financial Modeling & Valuation in Excel - Complete Course

Discover how to access Microsoft Excel on Windows using the Start menu, desktop shortcuts, pinned tiles, and the search bar for quick, clutter-free access.

Explore the Excel startup screen, including the home, new, and open tabs, with a blank workbook option, templates, and a search bar for recent files.

Explore how the Excel ribbon arranges tools by file, home, insert, and data. Discover how the home tab groups clipboard, font, alignment, editing features, and includes sorting, filtering, and tables.

Navigate Excel’s grid of cells, named by column and row like B2 or N17, and use the name box and formula bar to view and edit formulas.

Master worksheet basics: tabs and sheets, naming with underscores or hyphens, and renaming by double-click or right-click; add sheets, and recognize that a workbook contains them.

Click the save button, browse to the desktop, create an Excel folder, and name your workbook 'My first workbook' before saving as a best practice to protect your data.

Explore how to format a grocery list in Excel by applying bold and italic titles, adjusting font size, indenting sublists, and applying borders, including all borders and grid lines.

learn how to apply custom cell styles in Excel to color code lists with fill colors, emphasize essential items, and format fonts and headings for clear financial models.

Learn to create custom number formats in Excel, using the format cells dialog, including thousand separators, decimals, negative numbers in brackets or colors, and using hash vs zero placeholders.

Master custom number formatting in Excel by defining positive, negative, and zero formats, using colors, brackets, and decimals, and applying currencies, percentages, and fractions.

Learn to apply Excel's custom formats to display dates, times, and numbers with US/UK date orders, month names, weekday names, and units like years or currency, while preserving underlying values.

Discover how the Bedmas order of mathematical operations guides Excel calculations, solving brackets, exponentials, division, multiplication, and addition/subtraction with examples like 3.5 and 9.5.

Learn to perform simple arithmetic in Excel without functions, sum scores by linking cells, and compare with greater than or equal to the passing score 50, yielding true or false.

Explore performing mathematical operations in Excel without functions, using relative referencing to copy formulas across cells and calculate totals and score differences.

Learn how to use the if function in Excel to convert logical tests into pass or fail outputs by comparing scores to a passing score of 50 and nesting ifs.

Explore how nested if functions evaluate multiple conditions to produce pass or fail outcomes, with Excel handling true/false logic, bracket nesting, and relative referencing.

Learn to import employee data into Excel, format dates, apply borders and number formats, and calculate annual salaries for departmental analysis.

Explore how to determine total headcount and department-level breakdowns in Excel using count function, count A, and countifs, including payroll and contract filters with absolute references.

Learn to use count if function in Excel to count by payroll and department, lock references with dollar signs as you copy formulas across engagement types, and apply multiple criteria.

Learn to calculate total salary outflow using the sum function and split it by payroll and contract across departments with sumif and sumifs in Excel.

Learn to compute overall, department-wise, and engagement-type averages in Excel using average and averageif functions, with payroll criteria and locking references for copying formulas.

Use averageifs for multi-criteria salary averages (department and engagement type). Lock references when copying the formula across rows and columns, and handle div zero errors.

Discover how to find the overall maximum salary with the max function and use maxifs with department and payroll criteria to compute department level maxima.

Discover how to find the minimum salary by department and overall with min and minifs in Excel, using locked ranges and payroll and department criteria.

Determine each employee's share of the overall annual salary by adding a percentage column, using autosum to total the salaries, and copying a fixed denominator formula in excel.

Apply conditional formatting in excel to visualize salary data with color scales, highlight high and low outflows, and use thresholds such as 100000 and 70000 with custom rules.

Explore dynamic conditional formatting in Excel by testing rules with salary data, adjusting colors and percentages, and managing, editing, and deleting rules, including data bars and heat maps.

Learn to improve Excel tables by formatting as a table, applying borders, headers, conditional formatting, filters, and wrapping text while resizing columns.

Enforce unique column headings and disallow merging within the table to keep data structured and ready for analysis, and convert to a range to regain flexibility.

Learn to clean and organize data in Excel using filters and multi-level sorting. Practice sorting by department, engagement type, and salary with expand selection and proper copy-paste workflows.

Learn to filter, sort, and color-code data in Excel, including filter by color and font color, and master freezing panes to keep headers and key columns visible.

Learn how to print Excel data effectively by mastering page layout, orientation, scaling, and margins. Use headers, footers, and print preview to ensure clean, one-page or multi-page outputs.

Explore pivot tables in excel to summarize five-year regional sales by product category (LEDs, CFL, FL), showing year, region, and total sales using rows, columns, values, and filters.

Filter data for the last three years in Excel, compute sums and averages in pivot tables, adjust value field settings, and generate detailed breakouts in new worksheets.

Organize pivot tables in Excel by creating new tables, arranging year and region as rows, summing sales by category, and refining layouts, subtotals, and styles.

Create column and bar charts from pivot table data in Excel, customize axis titles and chart title, show year-wise and region-wise sales, place the legend, and use filters for updates.

Learn to craft bar charts in Excel with design options, color palettes, and data labels, then highlight key bars and switch chart types for clearer financial visuals.

Learn how to create and customize line charts from pivot tables in Excel, add data labels, adjust legends, chart titles, and colors to build a concise sales dashboard.

Create region wise pie charts in Excel by organizing two-column data (labels and values), use a pivot table to filter by year (2020), and choose pie or donut chart styles.

Use map charts in Excel 2019 to visualize country revenue with color gradient, darker colors indicating higher contributions, switching between world and United States maps, data accuracy, and internet connection.

Explore common Excel errors, including hash div zero, href error, hash ref error, and hash value error, and learn to fix them with the iferror function.

Explains common Excel errors—hashref and hashvalue—caused by deleting cells, sheets, or mismatched ranges; covers cross-sheet references, regional number formats (lakhs vs millions), and using the iferror function to mitigate.

Learn to use excel's logical functions from the formulas menu, including the if function and the and function, to test multiple criteria and return pass or fail.

Explore the and, or, and xor functions in Excel and how they feed true or false into the if function, reducing nested conditions and clarifying pass/fail logic.

Learn how to map scores to grades using the IFS function in Excel, replacing nested ifs with multiple criteria. Understand how first-true evaluation and absolute references affect results.

Explore using the IFS function to determine pass or fail across multiple subjects by testing scores against 50, handling hash errors with iferror, and exploring alternatives.

Learn the switch function in Excel, introduced in Office 2019 and available in Microsoft 365, covering syntax, defaults, and true for range comparisons with IFS.

Explore Excel's remaining logical functions, including false, true, not, if error and if any, and examples of using not to negate conditions and handling division by zero.

Explore information functions in Excel, including isblank, iserr, iserror, iseven, isodd, and istext, for data validation and error detection.

Explore date and time functions in Excel, including today and now, and learn how dynamic dates differ from static dates using the control semicolon shortcut.

Learn how to extract month and year, determine weekday and week numbers, and compute date differences using Excel's date functions like month, year, weekday, weeknum, days, and date.

Master how to use edate and eomonth in Excel to add or subtract months, handle leap years, and return the last day of a month for financial modeling.

Demonstrates how the yearfrac function measures the difference between dates as a decimal or fraction, using eomonth for end dates and today for updated tenure, with examples like 22.5 years.

Learn to join data across cells in Excel using concat and textjoin, with the ampersand and delimiters, to create full names, handling empty cells and checking string length.

Apply len, find, left, right, trim, and text join to extract and validate text in Excel, from credit card length checks to first-name extraction and case conversions (lower, upper, proper).

Master Excel's unique and transpose functions to extract distinct values, remove duplicates, and reshape results across rows or columns, while handling array behavior and hash spill errors.

Learn how to use the text function in Excel to format numbers, dates, times, and strings with custom formats, currency, fractions, leading zeros, and phone styles.

Explore how the choose function uses an index number to return items from a list in Excel, enabling scenario analysis and dynamic lookups.

Explore using the choose function in Excel to model pessimistic, expected, and optimistic scenarios by selecting sales and expenses, then compute profit before tax, tax, and profit after tax.

Master the vlookup function for vertical lookups, covering exact and approximate matches, table array selection, column index, first-column search limitation, and copying formulas with locked references in Excel.

Master the hlookup function to retrieve region and product totals from horizontal data, compare with vlookup, handle exact matches, and interpret common hashref errors.

Discover how to replace vlookup with index and match, using exact and approximate matches to locate positions and retrieve values from a range via row and column numbers.

Master index and match in Excel to locate row and column positions with dynamic matches. See grades, west region sales, and closest totals using less than or greater than.

Master xlookup and xmatch in Excel to perform flexible lookups for financial modeling, with exact matches or next smaller/larger options, wildcard support, and configurable search and match modes.

Learn how the offset function in Excel returns a reference to a range by moving rows and columns from a starting point, with optional height and width.

Explore how the offset function dynamically selects ranges for total and average scores in Excel, including the current cell; learn how height and width control the selection.

Learn to use the offset function with max and min to locate highest and lowest scores, then apply width and height, using the month function to compute year-to-month cumulative sales.

Explore applying the offset function with sum and gcd to compute quarterly and half-year sales from monthly data, using current month numbers to sum the correct three- or six-month periods.

Learn to use the offset function to dynamically sum monthly sales into quarterly, bi-monthly, and half-year totals, guided by the greatest common divisor for period selection and robust, locked references.

name cells and ranges in excel using the name box to create readable formulas that refer to named items across sheets, improving clarity, navigation, and maintenance.

Learn how to create and manage named ranges in Excel using the name manager, edit or delete names, and apply them in formulas and vlookup for ranges like countries.

Use the text to columns tool to split names into last, first, and middle name using delimited options. Choose comma or space delimiters, set a destination to avoid overwriting data.

Explore how to use the subtotal tool in the data outline to calculate region totals in Excel, and learn grouping and ungrouping rows and columns for a collapsible view.

Explore how to use hyperlinks in Excel to link to other worksheets within the workbook, insert and edit links to websites or emails, and manage masking and security concerns.

Configure data validation in excel to ensure clean student records, using dropdowns for states, 3–15 character names, a bright.edu email, date of birth, year of passing, and cgpa.

Master data validation in excel: enforce text length, date ranges with today/date functions, and dynamic year limits. Create dropdown lists from state or other lists and allow decimal cgpa values.

Learn to build Excel custom data validation that enforces bright.edu emails via a formula checking the last 11 characters, applied across tables with clear error messages.

Generate random numbers in Excel with rand and rand between, observe dynamic updates as data changes, and learn to paste values to freeze results for stable modeling.

Learn to use Excel's custom views to save and switch between filtered data displays, USA data, Africa data, Asia and Central America, with hidden month column and zoom for presentations.

Protect worksheets and workbooks in Excel by applying password protection, unlocking specific ranges, and controlling edits to formulas and data, while distinguishing between sheet and workbook security.

Master advanced pivot table techniques in Excel, including date grouping by months and quarters, zero handling for empty cells, and multi-level region and country analysis.

Learn to create and customize pivot tables with product category filters, show report filter pages for each category, switch to tabular layout, and manage pivot data references and column behavior.

Explore pivot table tools: slicers and timelines, using visual filters to drill down by region, country, and transaction date, with dynamic cascading filters and date-based timelines.

Master goal seek and Solver in Excel to hit a target profit, adjusting sales volume, price, and costs with constraints, scenarios, and the scenario manager.

Discover data tables, a what-if analysis tool in Excel that links input cells to a final profit and tests multiple pricing and cost scenarios with single and two-dimensional formats.

Explore data tables in Excel to analyze how changes in variable cost and selling price impact profits, breakeven, and final results using row and column inputs.

Explore Excel's goalseek tool within what-if analysis to determine required sales volume for target revenue and to compute break-even using fixed and variable costs.

Master combo charts by combining column and line visuals to compare sales estimates and actuals, while learning data organization, axis labels, and chart type changes.

Learn to build and customize combo charts in Excel, combining sales, expenses, and profits with primary and secondary axes, formatting, and clear labeling for financial insights.

Explore sparklines in Excel to visualize year-on-year growth for five regions, using line and column sparklines for compact trend and region-wise contribution.

Learn how waterfall charts visualize changes from a starting point to an end point, with blue bars for increases, orange for decreases, and gray for totals.

Apply box plots and the five-number summary to real data in Excel, computing min, quartile one, median, quartile three, and max with examples from two teams.

Create and interpret box and whisker charts in Excel to compare age distributions by team and salary distributions by engagement type, highlighting mean, quartiles, whiskers.

Explore tree map and sunburst charts to visualize hierarchical data, revealing how regions, product categories, and sublevels contribute to totals with labeled values.

Explore radar charts, or spider charts, comparing three or more variables against a central point. See how higher distances from the center reflect stronger ratings and benchmark performance.

Explore creating interactive loan applications in Excel using form controls, including a scroll bar to adjust loan amount, period, and rate, with developer tab setup and cell linkages.

Demonstrate using spin button and radio button form controls in Excel to set loan period, interest rate, and payment options, linking to cells and data ranges across sheets.

Apply form controls in Excel to link dropdowns to cells, display position numbers, and drive loan parameters, using index, vlookup, and other lookups to calculate payments.

learn to use excel form controls and the pmt function to compute fixed loan installments, convert annual rates to per-installment rates, determine first bill amounts with abs, and test scenarios.

Enable a data entry form in Excel without macros by adding it to the quick access toolbar, then use it to add, edit, and delete records.

Explore the basics of accounting, the purpose of financial statements, the business cycle, and the key financial statements through a detailed case study, and compare with CBA.

Explore the purpose of financial statements in accounting, including income statements, balance sheets, and cash flows, to report the financial position and performance of an entity over an accounting period.

Illustrates a logistics business cycle from initial investment and asset purchases to revenues, expenses, and profits, explaining retained earnings, dividends, and how accounting tracks cash flow and statements.

Learn how revenues, costs, gross profit, and operating income appear on the income statement under accrual accounting, with case studies illustrating revenue recognition and the matching principle.

Master the balance sheet's structure, with assets equal to liabilities plus shareholders' equity, and learn current versus long-term assets and liabilities, plus the double-entry debit and credit system.

Examine prepaid expenses like prepaid insurance on the balance sheet. Show how inventory is valued under cost and conservatism, and explain unearned revenues and debt entries.

Learn how income statements tie to the balance sheet, tracing net income to retained earnings and exploring how depreciation, taxes, and unearned revenue affect asset and liability balances.

Identify the sources and uses of cash in a three-part cash flow statement—operations, investing, and financing—and see how revenue earned but not paid and loan receipts illustrate cash movements.

Celebrate completing the accounting course from CPA, reflect on the informative and fun learning, practice the lessons to master accounting, and invite friends to learn and keep learning.

Explore the difference between calendar year and fiscal year for income statements and annual reports, highlighting seasonality and 12-month periods with global examples like Google, P&G, and the US government.

Explore the income statement format with a $1,000 sales example, deriving net sales, cogs, gross profit, sg&a, ebitda, depreciation and amortization, ebit, net interest expense, taxes, and profit after tax.

Learn to analyze a company’s income statement by calculating margins such as gross profit, ebitda, ebit, and profit after taxes (pat) to evaluate financial performance.

Explore how non-recurring items such as unusual or infrequent events, discontinued operations, and extraordinary items are identified and integrated into the income statement above and below the line.

Analyze how changes in accounting principles and estimates alter the income statement and pat, including inventory changes and useful lives, with effects shown net of taxes below the line.

Explain gaap restatement for discontinued operations, separating the discontinued segment from segments and restating past income, and describe cumulative effect treatment for changes in accounting principles with three segments forward.

Learn how revenue is earned and recognized under accrual accounting, guided by FASB and SEC criteria, with a focus on percentage of completion and completed contract methods for long-term contracts.

Learn how installment sales recognize revenue over time and when to apply the cost recovery method, illustrated with a cash-collection schedule and practical examples.

Explore depreciation expense by comparing straight-line and accelerated methods, including double-declining balance, using cost, salvage value, and useful life to compute annual depreciation.

Download Colgate's 10-K, examine the income statement, and convert it into an analyst-friendly format with gross profit, EBITDA, EBIT, and net income. Distinguish recurring vs non-recurring items to ease analysis.

Identify the analyst-friendly income statement by defining gross profit, EBITDA, EBIT, and net income, and extract depreciation and amortization from the cash flow statement to compute EBITDA.

Format the income statement to analyst standards by color-coding hard coded numbers blue and formulas black, and separate recurring from non-recurring items with 10-K review.

Identify non-recurring items in the 10-K by comparing GAAP and non-GAAP measures, and use the non-GAAP reconciliation to separate recurring costs like restructuring from one-time charges.

Learn to separate recurring and non-recurring items in an Excel reconciliation, applying non-GAAP adjustments to derive recurring EBIT and EBITDA from gross profit, cost of sales, and SG&A.

Compare before and after adjustment margins, calculating gross profit, EBITDA, EBIT, and PAT margins, and distinguish recurring from non-recurring items to guide reliable financial modeling.

Understand the balance sheet as a snapshot on a specific date, showing assets equals liabilities plus shareholders' equity, with current and long-term assets and liabilities.

Explore current assets on the asset side, including cash and cash equivalents, accounts receivable, inventories, prepaid expenses, and other current assets within one year or operating cycle for Excel-based modeling.

Explain cash and cash equivalents as current assets used for day-to-day operations, with examples including cash, checking deposits, undeposited receipts, and liquid instruments like commercial papers and treasury securities.

Analyze how cash and cash equivalents and short term investments compare to total assets for Colgate Palmolive, Procter and Gamble, and Microsoft using Yahoo Finance balance sheets.

Explore accounts receivable in financial modeling: money owed by customers for delivered goods or services, shown net of allowances for bad debt.

Analyze accounts receivables in a two-year accrual accounting case study, modeling bad debt allowances, net receivables, and effects on the income statement, balance sheet, and cash flows.

Classify inventories as raw material, work in progress, or finished goods, and evaluate how ownership and fob terms affect whether in-transit items stay inventory or become cost of goods sold.

Examine how to classify inventories by stage and ownership, including raw materials, work in progress, and finished goods, with FOB destination and FOB shipping point implications and risk in transit.

Explore the lower of cost or market rule for inventory, compare cost and replacement cost scenarios, and show how write-downs reduce assets and equity while leaving cash flow unchanged.

Analyze Colgate-Palmolive's 2012 10-k inventories: 80% fifo, 20% leaf, lower of cost or market, with no material earnings impact and finished goods as a sizable class.

Explore inventory accounting on the balance sheet and the income statement, linking beginning inventory, additions, and ending inventory to cost of goods sold under LIFO, FIFO, and weighted average.

Compare cost of goods sold and ending inventory under fifo, lifo, and weighted average, noting how price trends affect valuation; under us gaap, all are allowed, while ifrs excludes lifo.

Prepaid expenses involve paying for services in advance and recognizing the expense over the accounting period as the benefit is consumed, illustrated by a $1,000 two-year insurance example.

Examine Colgate’s other current assets, totaling $639 million, including foreign currency contracts, interest rate swap contracts, and commodities contracts, with marketable securities and the current portion of deferred taxes.

Explain the need for standardized financial reporting for comparability, and how FASB, IASB set standards while SEC and FSA enforce filings like 10-K, 10-Q, and 8-K.

Explore current liabilities due within a year, including accounts payable, other accrued liabilities, short-term debt, current maturities of long-term debt, and unearned revenues, with supplier payments and magazine subscriptions.

Colgate-Palmolive's current liabilities include notes and loans payable, current portion of long-term debt, accounts payable, and other accruals; cash of $884m covers $304m short-term obligations, signaling liquidity.

Explain long term assets, including tangible assets, natural resources, and intangible assets like patents and goodwill, with depreciation and amortization concepts. Discuss property, plant and equipment and construction in progress.

Goodwill is an intangible asset arising from acquisitions; under the purchase method it forms on the balance sheet, whereas the pooling method yields no goodwill.

Explore the pooling method of consolidation by adding current assets, long-term assets, and liabilities to build the balance sheet, noting no goodwill is created.

Apply the purchase method of accounting to revalue assets and liabilities at fair value, compute new book values, determine goodwill premium, and consolidate the balance sheets.

Identify how goodwill is not amortized and undergoes annual impairment tests by auditors. See how impairment reduces goodwill on the balance sheet and reduces net income and retained earnings.

explore long term investments held for over one year, including stocks, bonds, real estate, and affiliates or subsidiaries, and report them at the lower of book value or market value.

Explore classifications of long term investments, held to maturity, trading securities, and available for sale, and their balance sheet and income statement impacts under fair value, cost, or present value.

An example analyzes a 1 million 5% bond and shows its effects under held-to-maturity, trading, and available-for-sale classifications on the balance sheet and income statement, including unrealized gains or losses.

Examine long term liabilities, including known, estimated, and contingent obligations, and how covenants affect bonds. Learn about secured, unsecured, callable, and convertible bonds, plus senior versus subordinate claims.

Assess the risk profile of long term liabilities across bonds and equity. Explore investment grade versus non-investment grade ratings and how risk relates to return with GE and J.Crew examples.

Explain how shareholders equity completes the balance sheet, contrast debt and equity funding, and define common and preferred shares, par value, and additional paid-in capital.

Split the $400 into $100 par value and $300 APIC for 100 shares. Note par value is a legal value, not fair value; clarify authorized, issued, and unissued shares.

Explore how to model treasury stock and its impact on shareholders' equity by showing authorized, issued, and outstanding shares, par value, APIC, and buyback calculations.

Learn how net income splits into dividends and retained earnings, shaping shareholders' equity, and how dividends per share and accumulated other comprehensive income fit into the picture.

Learn how accumulated other comprehensive income directly adjusts shareholders' equity when inventory write down or declines in available for sale marketable securities avoid impacts on the income statement.

Compare preference shares with common shares, noting dividend priority, no voting rights, liquidation order with debt holders first, plus convertible and cumulative vs non cumulative features.

Analyze McDonald's 2007 and 2006 balance sheets to identify stock types, par value, authorized and issued shares, and treasury stock. Include paid-in capital, retained earnings, and accumulated other comprehensive income.

Explore cash, property, and stock dividends: cash from retained earnings, property as assets, stock dividends preserving ownership. Ensure treasury stock receives no dividends; track declaration, record, and payment dates.

explains how cash and property dividends affect accounting entries across declaration, record date, and payment date, including dividends payable, cash outflow, and effects on shareholders’ equity, bonds, and retained earnings.

Stock dividends issue additional shares, not cash, keeping assets and liabilities unchanged; a split lowers price per share, while small or large dividends affect retained earnings, capital stock, and APIC.

Explains 20% small and 40% large stock dividends, showing how common stock, par value, and additional paid in capital adjust while retained earnings act as a plug to keep equity.

Stock splits differ from stock dividends by changing par value. A 1:1 split raises shares to 20,000 and lowers par value from $1 to $0.50, with no equity change.

Explore cash flow statements and why they matter beyond the income statement and balance sheet. Compare how cash receipts and expenditures relate to accounting principles used in financial statements.

Explore cash flows categorized as CFO, CFI, and CF. Compare direct and indirect methods, and explain accrual vs cash flows with balance sheet implications.

Learn to compute cash flow from operations using the direct method by reconciling the income statement with the balance sheet and analyzing accounts receivable, payables, and accrued liabilities.

Calculate cash flow from operations using the direct method by analyzing sales, COGS, SG&A, taxes, and working capital changes in the ABC Corporation case, yielding a CFFO of 188,500.

The indirect method starts with net income, adds back non-cash items like depreciation and amortization, and adjusts for non-operating gains to reveal cash flow from operations.

Explore the indirect method to calculate cash flow from operations by starting with net income, adding back depreciation, and adjusting for changes in accounts receivable, inventory, and accounts payable.

Learn how cash flow from investments accounts for fixed asset purchases and disposals, using net block changes, depreciation, and gains or losses to calculate cash inflows and outflows.

Analyze cash flow from financing by examining debt and equity activities, including debt issuances and repayments, equity issuances and buybacks, and dividends paid, and how these affect financing cash flow.

Solve a cash flow example using the direct method to derive CFO from the income statement and balance sheet, noting accounts receivable, inventory, accounts payable, wages payable, and interest payable.

The lecture demonstrates computing cash flow from operations using the direct method, incorporating taxes payable and deferred taxes, showing a net cash inflow of about 85,000.

Using the indirect method, start with net income of 75,000, add depreciation of 14,000, subtract a 20,000 gain, and adjust balance sheet items to derive cash flow from operations.

Calculate cash flow from investments by analyzing purchases and sales of fixed assets and land, using gross and net block changes, depreciation, and gains from sale of land.

Analyze dividends payable, declared dividends, and bond issuance to compute cash flow from financing. Determine the net impact of a common stock buyback (-20,000) and a bond increase (+10,000), totaling -17,000.

The lecture demonstrates reconciling the 2007 ending cash balance by combining CFO, CFI, and CFF with 2006 cash, confirming 2007 ending cash of 66,000 via balance sheet reconciliation.

Master corporate valuations with dividend discount model, discounted cash flows, and relative valuation (P/E, price-to-book, ROE) in Excel, CFA approved for 2.5 credits.

Use the dividend discount model to compute intrinsic value from dividends and a selling price, discounting cash flows with 15% return and using NPV formulas.

Apply the dividend discount model to infer the implied return when the market price equals intrinsic value, using D1=3, P=100, and g=9% to get 12%.

Compare intrinsic value to market price using the dividend discount model, with next-year dividends, growth, and cost of equity, and explore how expected returns alter buy or sell signals.

Explore valuing growth companies with a non-constant dividend growth model by separating explicit cash flows (2008–2011) from terminal value after 2011, using the dividend discount model with 8% long-term growth.

Demonstrates valuing dividends via dividend discount model by calculating present values of explicit cash flows and terminal values with NPV, correcting zero-cash-flow issues, and testing sensitivity to cost of equity.

Master the basics of discounted cash flow by building forecasted free cash flow to the firm, calculating cost of capital, and analyzing interlinked projection sheets to derive intrinsic value.

Forecast revenue using a 7% long-run growth assumption and drive costs with a percentage of sales, then derive EBITDA as revenue minus COGS minus G&A.

Project working capital in cash flow to the firm by linking current assets and liabilities to sales and cogs, including accounts receivable, inventories, prepaid expenses, accounts payable, and accrued expenses.

Forecast the abc income statement using 13% depreciation of sales, derive EBIT from EBITDA minus depreciation, and estimate tax at 33% with 13.3% sales capex.

Link the free cash flow to the firm by tying EBIT to taxes, depreciation, capex, and changes in working capital, illustrating cash outflows when working capital rises.

Calculate the present value of explicit period cash flows with an assumed 8% cost of capital and compare traditional NPV with XNPV, including dates and terminal values.

Calculate terminal values using perpetuity growth and exit multiple methods, apply discounting with an 8% cost of capital, and reconcile results using ex npv and npv approaches.

Compute DCF valuation summary by summing NPV of explicit period and terminal value to obtain enterprise value, then derive equity value and share price using capital structure and exit multiples.

Apply sensitivity analysis to discounted cash flow by testing perpetuity growth and EBITDA multiple methods across varying growth rates and wacc with two-dimensional data tables.

Analyze a company's capital structure to calculate weighted average cost of capital by classifying debt and equity, including short-term borrowings, revolvers, bonds, convertible bonds, and convertible preferred stock.

Explain how employee stock options are valued under the treasury stock method, comparing in-the-money and out-of-the-money options, and showing how dilution lowers EPS and affects valuation.

Clarify the treasury stock method for dilution by distinguishing options outstanding from exercisable, and apply strike price and market price amid a lock-in period.

Compute in the money convertibles by evaluating convertible preferred and convertible debt, determining issue prices, shares issued, and conditional conversions based on market price versus conversion price.

Identify in-the-money stock options by comparing strike price to market price, compute option proceeds with an if logic, and assess net dilution from buyback.

Compute the fully diluted market value of equity and debt, including convertibles and options, then calculate the debt to equity ratio from total debt and equity in the capital structure.

Explore cost of debt calculations using Damodaran's synthetic rating method, linking EBIT, interest expense, interest coverage, risk-free rate, and market capitalization-based spreads to arrive at the after-tax cost of debt.

Apply CAPM to estimate cost of equity for a post-IPO firm, using comparable analysis to derive unlevered beta and re-lever with debt at 5% risk-free rate and 5% market premium.

Link the cost of capital to the discounted cash flow model by updating debt, cash, and diluted shares. Use exit multiple to finalize terminal value and reveal enterprise value.

explore relative valuation using multiples such as price-to-earnings and ev/ebitda, distinguishing equity vs enterprise value multiples, and build a comparable sheet to value firms.

Compare enterprise value and equity value multiples, selecting industry-relevant metrics such as EV/EBITDA, EV/sales, and price-to-book, while understanding why EPS-based multiples misalign with enterprise value.

Explore enterprise value and equity value multiples in an oil and gas upstream comparable sheet, using price to earnings, EV to EBITDA, and price to cash flow.

Explore equity value multiples, especially price to earnings, and how relative valuations compare a company to its sector using trailing and forward P/E.

compare forward and trailing p/e ratios, where forward p/e uses expected eps and trailing p/e uses historical eps to guide investors.

Analyze why negative EPS makes price to earnings non meaningful, and assess how accounting policy, balance sheet risk, and capital structure influence PE comparisons and undervaluation judgments.

Understand price to book value, defined as price per share divided by book value per share from shareholder's equity on the balance sheet, used in banking sector valuations.

Explore why banks rely on price to book value. Compare bank assets and liabilities with manufacturing firms, and explain mark-to-market dynamics that align book value with market value.

Explain price to book value as price to earnings times roe, and how low roe with p/b signals overvaluation, while high roe with low p/b signals undervaluation in energy sectors.

Compare price to cash flows with price to earnings to gauge valuation. Highlight cash flows per share and non cash items in cash flow statements.

Cash flow based valuation suits hard-asset sectors like oil, gas, gold, and real estate, where reserves and production enable cash flow visibility. Low price to cash flow signals takeover appeal.

Build a financial model using Apple data from Edgar and the company's investor pages, incorporating 8-K, 10-K, and 10-Q insights to forecast earnings and revenue.

Build the income statement and core financial statements from annual reports; forecast revenue via a buildup and link depreciation, working capital, and cash flows to a discounted cash flow valuation.

Forecast revenue using prior year times (1 plus growth rate), noting cascading effects from initial estimates. Build an integrated horizontal Excel model linking revenue to the income statement.

Link revenue to cost of sales and expenses on a cost sheet for products and services. Use vertical analysis and percentages of revenue to forecast expenses and future years.

Link cost sheet data to the income statement, cascading 65% of product revenue and 30% of service revenue to cost of sales, plus 6% to operating expenses (sg&a and r&d).

Learn how to compute earnings per share (EPS) using basic and diluted share counts, including the impact of ESOPs and convertible securities on net income and EPS.

Understand weighted average number of shares for EPS and trace the income statement from gross margin to net income, including EBITDA, EBIT, depreciation and amortization, and taxes.

Learn balance sheet structure, distinguishing current and non-current assets and liabilities, including property, plant and equipment and depreciation, and forecast and link totals across working capital, equity, and debt schedule.

Explore how net working capital affects cash flow by linking current assets and liabilities, inventories, receivables, and payables. Learn how to present this story in financial modeling.

Analyze how receivables, payables, and inventory drive the cash conversion cycle and cash recovery cycle, using day-by-day timelines to optimize working capital.

Learn to calculate payables turnover and days of payables, and model accounts receivable, inventories, and current assets by correlating them to sales for accurate forecasting.

Develop assumptions for receivables, inventory, and payables using backward calculations from sales and costs. Define percentage-of-sales for current assets and liabilities to estimate working capital.

Link working capital figures to the balance sheet and validate the cash flow statement by correlating accounts receivables, payables, and deferred revenue across years.

Forecast capital expenditures by linking CapEx to sales growth and allocate investments across land and building, machinery, and leasehold improvements, reflecting CapEx as a cash outflow in Excel forecasts.

Apply straight-line depreciation and a waterfall approach to forecast asset depreciation, handling existing assets and new capex, mid-year and half-year conventions, with Excel techniques like transpose and cell locking.

Calculate ending net PPE by totaling depreciation for land and building, machinery, and leasehold improvements, then link to the balance sheet and depreciation schedule for a complete model.

Model shareholder's equity in Excel by forecasting common stock, retained earnings, and related components on the balance sheet, while integrating depreciation, capital expenditure, and dividends.

Forecast dividend and dividend equivalents using the per-share rate, multiply by diluted shares, convert thousands to millions, and compute the total cash outflow for the balance sheet ending balances.

explore calculating changes in working capital from current and non-current assets and liabilities, interpret positive or negative cash flow effects, and link these changes to debt and financing schedules.

Forecast long-term debt by linking cash flows from operating and investing activities to debt repayments, while applying a minimum cash balance to model principal payments and amortization.

Link interest expense from income statement to debt schedule, using the average of current and prior year debt balances to derive the rate, and forecast conservatively with reinvested cash.

Link debt in financial modeling by examining net interest expense, average interest rate, and debt repayment, and analyze their impact on ebitda margins, cash balances, and earnings per share.

Understand circular references in financial models where formulas link across balance sheet and cash flow, causing interdependent values like net income to loop. Learn to fix with iterative calculations.

Develop a startup's DCF-based valuation by building the income statement from net sales of 200,000, COGS, gross margin, EBITDA, depreciation and amortization, EBIT, and interest expense.

Explore how debt costs affect cash flow and profitability by modeling interest, taxes, and depreciation, comparing debt versus equity to determine cost of debt in valuation.

Compute the cost of debt as interest rate times one minus tax rate, highlighting the tax shield and its impact on the weighted average cost of capital in financial modeling.

Explore how to compute the weighted average interest rate across multiple loans by assigning weights to each loan and calculating the overall rate using proportional contributions.

Compute the weighted average cost of capital by combining cost of equity, cost of debt, and cost of preference equity with their capital weights as the discount rate.

Explore how the discount rate relates to cost of debt, cost of equity, and WACC, and compute present value and future value using rate, PV, FV, and opportunity cost.

Explore how opportunity cost serves as the discount rate in investment models, using cagr, irr, and Excel's rate, pv, and fv functions.

Learn to determine the risk-free rate using the US ten-year Treasury and understand how currency and country risk premiums influence valuation and WACC in discounted cash flow models.

Compute Apple’s beta at about 1.25 from five-year monthly data and apply the capital asset pricing model to estimate cost of equity as risk-free rate plus equity risk premium.

Explore market value of equity from stock price and diluted shares, and assess debt using short and long term debt to compute debt-to-equity in capital structure.

Compute the weighted average cost of capital by linking the risk-free rate, market risk premium, and levered beta to cost of equity, plus debt costs.

Compute fcff by starting from net income, adding back depreciation, and adjusting for changes in working capital, capex, and the tax shield on interest to obtain firm’s free cash flow.

Explore how to compute free cash flow from EBIT or EBITDA, reconcile with nopat, tax shields, depreciation, and changes in working capital, CapEx, and derive FCFF and FCFE.

Compute free cash flow to the firm to derive present value, terminal value, enterprise value, equity value, and share price, then perform a sensitivity analysis on the valuation.

Explore fcff growth rate considerations and terminal and perpetuity growth rate concepts, balancing bottom-up forecasts with perpetual growth to value free cash flows beyond a five-year horizon.

Learn to calculate NPV of explicit periods and terminal value using the Gordon growth model, leveraging free cash flows, perpetuity growth rate, and cost of capital (WACC) in valuation.

Calculate enterprise value by summing the net present value of explicit periods and terminal value, then adjust for cash and total debt to derive equity value.

Explore sensitivity analysis of weighted average cost of capital and growth rate to assess impact on share price, using Excel data tables and what-if analysis.

Explore how growth rate and terminal growth rate influence valuation, cost of equity and WACC, using sensitivity analysis in Excel and cautious data table usage.

demonstrate the cost of debt as the pre-tax obligation, and the cost of equity as an optional, higher risk, equity risk premium; discuss dividends, stock price impact, and exit options.

Discover how exit prices depend on buyers, relate valuation to last equity transactions, and use price-to-earnings ratios, earnings per share, and market cap to gauge value and investor returns.