Equity Capital Markets Mastery: IPO Modeling and Analysis

Explore how venture capital and private equity funding drives startup growth, the mezzanine exit via IPO, and the role of capital gains reinvestment in startups.

Explore how venture capital funds invest in startups in private partnerships, pursue exits through IPOs, manage risk across stages, and understand cyclical IPO dynamics from demand and supply sides.

Understand why IPO activity cycles into hot issue periods, and compare supply- and demand-driven drivers, while examining access to capital, primary vs secondary markets, dilution, and follow-on offerings.

Initial public offerings improve liquidity for shareholders by enabling trading in the secondary market, including gdrs and adrs, with regulatory oversight ensuring low transaction costs, cheap information, and market efficiency.

Explore the disadvantages of going public, including high costs, regulatory burdens, and loss of control, as external capital markets disclosure exposes information to competitors via adr and grs.

Explore the IPO process from issuer and lead underwriter to syndicate, bookbuilding, marketing, pricing, and post-market activity, including registration and underwriting spread.

Explore how the book building process, led by bookrunners like JP Morgan, captures institutional demand, informs price discovery, and allocates shares off market with confidential bids and the greenshoe option.

The bookbuilding process for an IPO sets a price range. The bookrunner opens a draft prospectus, gauges five-day demand, and allocates shares at the determined price.

Describe how the lead underwriter (bookrunner) earns the largest share of the underwriting spread, allocates fees to syndicate members and brokers, and uses the greenshoe option to support pricing.

Understand how the Securities Act of 1933 requires a registration statement with the SEC and a prospectus. See due diligence, public information, and S-1 filing for listing.

Explore how the registration statement becomes the preliminary red herring prospectus and how the roadshow drives investor interest, pricing, and use of proceeds for an initial public offering.

Explore how issuer roadshows generate investor interest, and how book building and offer price, guided by retail and institutional orders, determine IPO demand and allocation.

Learn how lead underwriters price IPOs through fixed price or bookbuilding with confidential demand data, balancing underpricing risks and capital needs.

Analyze IPO pricing concepts, including underpricing, the underwriter's role, and flipping or stag profits. Refer to Alibaba, Visa, General Motors, and Facebook as key examples.

The lecture explains how underpricing shields uninformed investors from the winner’s curse, how underwriters support post-IPO trading, and the quiet period rules that limit forecasts until after 25 days.

Explore the other divestiture method, spin-offs, where a parent creates an independent subsidiary and tax-free distributes 80% of its stock to shareholders, unlocking value amid volatility.

Explore how an underwriter uses a preliminary prospectus, price ranges, and indication of interest to set the final offer price, and how greenshoe and reverse greenshoe options stabilize ipo prices.

Explain how underwriting agreements ensure responsibilities, compare greenshoe and reverse greenshoe options for market stabilization, and contrast firm commitment with best efforts, including Q min, Q max, escrow, and under-subscription.

Examine best efforts agreements in IPOs, where underwriters, as agents, try to sell the issue without guaranteeing full sale and face limited upside with flat fees, including all-or-nothing provisions.

Learn UK IPO methods, including placing and forms commitment, fixed price offer for sale, and book building with price bands, reservations for qibs and small investors, and auction options.

Analyze the Dutch auction IPO, where bids are ranked and winners pay a uniform price, ensuring equal access, while examining IPO cost components.

Explore ipo underpricing, the gap between first-day close and offer price, with 15% for firm commitments and 48% for best-effort, covering agency costs, information-based explanations, signaling, and the winner's curse.

Analyze ipo underwriter stabilization around the issue price and compare qip and fpo as domestic capital-raising routes with private placement to ships under sebi guidelines.

Explore follow-on public offers and rights issues, including dilutive and non-dilutive offerings, impact on share float and eps, with atm options and gdr/ifrs considerations.

Understand how rights issues let cash-strapped companies raise capital by offering existing shareholders discounted new shares, with renounceable rights traded among investors to fund growth or debt relief.

Explore how a rights issue works, including underwritten scenarios, a 3-for-10 offer at $3, and options to take up, ignore, or sell rights, plus dilution effects.

Explore how a rights issue causes dilution and three cases—take up, ignore, or sell rights—while estimating nil paid rights and evaluating IPO pricing using EPS and forward multiple.

Compare forward p/e to industry p/e to spot underpriced ipos. Note closely held firms and float; compute eva, nopat, cost of capital, and market value added.

Compute cost of equity via CAPM, after-tax cost of debt, and cost of preference shares; derive WACC and EVA, enterprise value, and EBITDA, P/S, P/E, and P/B multiples.

Explore common IPO valuation metrics, including forward price-earnings and EV/EBITDA, price-to-book, and sector-specific methods for autos, real estate, IT, oil and gas, banks, and retail.

Explore the ipo raw model as a baseline to analyze floor and cap price ranges, primary versus secondary issuance, greenshoe, underwriting discounts, ipo discounts, and expense assumptions.

Analyze ipo fees and expenses, including sec and finra fees, nasdaq listing costs, transfer agent and registrar charges, legal and accounting fees, underwriting discounts, and class a voting rights.

Learn how class a shares confer dividend priority, liquidation preferences, and higher voting rights, while class b shares may have lower rights and dividends, and avoid agency problems.

Explain the underwriting discount and gross spread in IPOs, defining the outflow and inflow to issuers and underwriters, and illustrate how issue size and risk affect spreads.

Build a treasury-method IPO model to compute fully diluted shares, including option strike price, options outstanding, and FDSO metrics across potential price ranges.

Determine fully diluted shares by calculating option outstanding and in-the-money status, then apply the dilution to IPO projections, including offering price range and primary and secondary shares.

Learn to compute overallotment shares by linking the base deal, primary allocation, greenshoe option, and overall allotment into total issued shares across primary and secondary markets.

Learn to compute pro forma issuer shares by adding existing shares to primary and overallotment, yielding fully diluted shares outstanding, with Vlookup or Hlookup on the matrix sheet.

Calculate offering size by adding the base deal and overallotment, including secondary shares, at offering price, then apply initial public offering discount and 6% underwriting discount to determine proceeds.

Compute the net ipo price per share after underwriting discount and outline the fees, then assess gross and net primary proceeds and the fully distributed valuation.

Explore how to compute the IPO discount using fully distributed valuation, derive implied 2013 and 2014 price-to-earnings multiples, and analyze earnings-driven shifts in market capitalization.

Assess an IPO using the potential price range and forward p/e multiple, contrasting offering-price and forward-multiple methods to determine capital to raise and valuation.

Explore forward PE multiples to evaluate IPOs, detailing a two-model approach (forward and offering price) with assumptions on currencies, discounts, greenshoe, allocations, and forward revenues, EBITDA, and net income.

Model one-year forward PE multiples to derive implied post-money equity value, apply IPO pricing discount, and compute the implied offering price per share from forward net income and shares outstanding.

Learn how to compute the implied offering price in IPOs by linking post-money equity value, pricing adjustments, and raised funds to per-share value using forward P/E multiples.

Assess how a company determines the capital to raise for expansion, using comparable multiples and existing shareholder perspectives to set the offering size and price.

Learn to compute primary and secondary IPO shares using implied post-money value, apply greenshoe and over-allotment adjustments, and determine post-IPO pro forma shares outstanding.

Learn to determine the primary and secondary share splits, understand the greenshoe over-allotment provision, and calculate base and overallotment shares and gross proceeds in IPO modeling.

Learn to compute net IPO proceeds by subtracting underwriting discounts and deal expenses from gross proceeds, then derive implied pre-money and post-money equity values and the IPO stake.

Learn to calculate IPO net proceeds from primary and secondary offerings, subtract fees, and apply the results to implied post-money enterprise value and valuation multiples.

Compute implied post-money equity value pricing and related multiples, including forward p/e, ev/ebitda, and ev/revenue, using pricing and trading data.

Model IPO value using the offering price per share as an alternative to the PE multiple, mapping shares issued and IPO proceeds based on price range, percentage sold, and greenshoe.

Compute the primary and secondary shares issued and the total shares sold in the base deal. Apply the green shoe provision to determine the over-allotment and finalize the IPO sizing.

Compute post-money equity value by blending existing and primary shares (including overallotment) to derive post-IPO shares, and determine implied value at pricing versus trading using a 15% IPO discount.

Calculate total offering size by summing base deal (primary plus secondary plus allotment) and over allotment; derive IPO proceeds after underwriting discount and expenses, and infer implied pre-money equity value.

Calculate the percentage of the company sold and derive the valuation multiple by linking gross primary IPO proceeds to the implied post-money equity value, considering deal size and net proceeds.

Apply noncontrolling interest to derive the implied post-money enterprise value, then translate it to equity value and analyze pricing multiples such as forward P/E and EV/EBITDA.

Calculate enterprise value by EBITDA using pricing and trading multiples for IPO valuation. Explore IPO proceeds, valuation methods, and ownership bifurcation, including case insights from Jet Airways.

Evaluate an ipo case study by modeling from historical financials, projecting growth, and applying dcf, forward p/e, and offer price methods to determine fair ipo valuation.

Explore a case study of Cafe Coffee Day's IPO, focusing on growth assumptions and forecasting based on 2014-2015 numbers, and analyze its vertical integrated business model and divisions.

Divide revenue across Cafe Coffee Day's divisions—fresh, square, beverages, exports, and B2C plant—and map outlets from cafes to express outlets for IPO modeling and valuation.

Summarize the Cafe Coffee Day group, including CD beverages with Celesta vending, fresh and ground coffee, hotels and resorts, exports, plus the four outlet formats: cafe, lounge, square, and express.

Forecast p&l and balance sheet to 2030, build cash flow projections, and value an ipo using dcf, pe multiples, and the offering price method.

Analyze the market share of cafe brands at the IPO date and model growth from a 2015 base year in Excel, detailing IPO proceeds allocation and per outlet revenue projections.

Model revenue per outlet by applying percentages of revenue to manufacturing cost and general and administrative expenses, using the 2015 balance sheet and P&L data.

Model general and administrative expenses at 41% of revenue and allocate them per outlet for Cafe Coffee Day, then compute EBITDA using manufacturing, selling, and machinery costs with 8.5% inflation.

Analyze market shares of leading cafe chains by outlet count and regional distribution, noting Cafe Coffee Day's dominance and Starbucks and Dunkin Donuts expanding, with IPO valuation context.

Examine the north region's dominant market shares and the top eight indian cities, and explore ipos, mergers, and private funding as strategies to expand.

Learn to set up IPO modeling assumptions in Excel, manage circular references with iterations, and apply 2015 base year growth, capex, and financing notes.

Apply Pareto analysis to focus on the 20% of outlets that generate 80% of profits, guiding expansion and prioritization across cafe formats.

Calculate raw material and manufacturing costs as a percentage of revenue and allocate them per outlet, then analyze general and administrative expenses and other income to understand overall profitability.

Leverage ebitda-based analysis to estimate per-outlet profitability and revenue percentages. Forecast expansion, machinery and furniture investments, and inflation-driven changes to support ipo modeling.

Learn how IPO funds are allocated to debt reduction and future growth, with a 12% term loan framework and revenue growth projected from 10% to 4% amid competition.

Analyze current liabilities and net working capital from balance sheet data, focusing on trade payables, and compare depreciation methods and tax implications including mat and dta/dtl.

Forecast the income statement for an IPO model by deriving revenue from outlets, aligning balance sheet figures like non-current assets and minority interest, and using the assumption sheet.

Apply spreadsheet techniques to model other operating income, using 3% of total revenue and 20% growth, leveraging the assumption sheet and expanding formulas to the P&L and balance sheet forecasts.

Model expenses from revenue by applying percentage-of-revenue assumptions to manufacturing, raw materials, and general and administrative costs, then spread and subtotal across the income sheet to show variable costs.

Explore selling and administrative expenses and build an expense sheet that scales with revenue. Prepare the balance sheet and working capital by forecasting current assets like accounts receivable and inventory.

Learn to build a balance sheet and profit and loss from forecasted income and expenses, align March 16th assumptions, and prepare operating income within a working capital framework.

Fix PNL and balance sheet models by anchoring D7 and D81, adjusting references, and using ctrl-alt-v to spread formulas across sheet to derive accounts payable, current liabilities, net working capital.

Demonstrates the application of funds in IPO modeling by detailing short term loan repayment, net working capital management, and capex allocation for machinery, furniture, and expansion outlets.

Calculate sources of funds by tying balance sheet cash and cash equivalents to capital expenditure, determining debt and equity portions, and preparing a cash flow framework for IPO modeling.

Model loan repayments in Excel by configuring existing and fresh loans, applying if statements to compute ten-year installments, and linking debt and assumptions across sheets with insolvency reform notes.

Calculate depreciation using the straight-line method in Excel, incorporating additions and capital expenditure, updating gross block, accumulated depreciation, and net block across years with minimum life logic.

apply furniture and fixtures depreciation and accumulated depreciation in an IPO modeling cross-block, updating the balance sheet with additions and existing assets using a 10-year useful life.

Learn to calculate gross block, depreciation, accumulated depreciation, and net block for machinery and furniture, apply tax depreciation rules, and compile a balance sheet in Excel.

Walk through building the p&l and balance sheet, inputting manufacturing costs and expenses, calculating ebitda and depreciation and amortization, and projecting profit before tax from cash flow statement and dcf.

Learn to build a balance sheet and apply tax considerations by outlining shareholder funds (share capital, reserves and surplus), PNL inputs, and both long-term and current liabilities, with formatting steps.

Compute goodwill on consolidation and finalize non-current and current asset schedules, including tangible assets, intangible assets, and capital work in progress, then prepare the tax sheet.

Explore the concept of PBT for MAT by contrasting book vs income tax depreciation, explaining deferred tax assets and liabilities, and the purpose of minimum alternate tax (18%).

Explore how minimum alternative tax (MAT) credit affects tax outcomes, comparing MAT and income tax, calculating effective tax rates, and applying depreciation rules under income tax and Companies Act guidelines.

Explore how to calculate carried forward loss, tax at the regular rate, tax under MAT, actual tax paid, and MAT credit utilization with carry-forward mechanics.

Master MAT credit calculations and tax planning in the equity capital markets masterclass, evaluating MAT versus regular tax, carry forward, expiry, and deferred tax assets and liabilities.

Learn how depreciation differences between the books and the income tax act generate deferred tax assets and liabilities, model timing differences from origination to reversal, and track cumulative effects.

Explore the cash flow statement and balance sheet, detailing cash flows from operations and investing activities, and compare direct versus indirect methods with depreciation and amortization.

Learn to build a cash flow statement with cash flow from operations, investing, and financing, plus opening and closing cash balances, including deferred tax and IFRS versus US GAAP differences.

Master cash flow from operations by adjusting depreciation, amortization, and working capital on balance sheet and cash flow statement. Analyze investing and financing to determine opening and closing cash balance.

Transform cash flow from the statement into the balance sheet to update debt, equity, and interest, then value the company using dcf and free cash flow with present value concepts.

Perform a dcf analysis by deriving revenue, ebitda margins, depreciation as a percentage of sales, tax liability, and free cash flow from equity through changes in working capital and capex.

Calculate depreciation as a percentage of sales, model EBITDA, interest. Compute debt, working capital, capex, taxes, and free cash flow from equity to estimate equity value and share price.

Compute the cost of equity using CAPM, risk-free rate, beta, and equity risk premium, and apply discounting, terminal value, and perpetuity growth to value IPO equity.

Learn to assemble balance sheet data and compute cash flow and debt ratios for IPO modeling. The lecture defines formula inputs such as long-term debt, cash, net debt, and depreciation.

Learn to calculate interest and repayments for long-term debt, including first charge holders, secured and other loans, to determine total repayment and debt servicing within EBITDA.

Explore balance sheet metrics: outside liabilities to net worth, debt ratios, EBITDA coverage, net debt to EBITDA, and current ratios with and without next-year repayments and cash, using iferror calculations.

Evaluate the EV/EBITDA valuation using peak EBITDA and seven- to eight-times multiples, compute enterprise value in rupees crores, and adjust for debt, cash, and market cap.

Master long term debt calculations from the ratio sheet, adjust for March 17, 2017 values, and compute total debt, market cap, and expected market value per share.

Calculate the average WACC by determining total debt and net worth, computing weights as debt and equity over total capital, and applying the sum product with the costs.

Calculate average EBITDA using a WACC-based discount factor, then value equity via DCF and EBITDA, including P multiples and 7x/10x scenarios.

Perform a two-scenario pe multiple valuation at 25x and 30x, using average net profit, eps, and shares, with cost of equity and discounting factor to determine the expected market price.

Calculate the average net profit, select a peer p/e multiple, and derive eps and market price using ebitda and p/e valuations to determine a final target price.