
An Initial Public Offering (IPO) is the process through which a privately held company offers its shares to public investors for the first time and becomes publicly traded. An IPO can raise new capital for the company through the issuance of primary shares, provide liquidity to existing shareholders through the sale of secondary shares, or combine both. The transaction typically involves determining the company’s valuation, setting an offering price and share structure, assessing investor ownership and dilution, and evaluating the use of the proceeds raised.
This Initial Public Offering (IPO) Financial Model provides a comprehensive framework for evaluating the valuation, structure and financial impact of a proposed public offering. The model combines historical financial information and operating forecasts with IPO-specific assumptions to calculate the implied valuation, offering price, number of shares issued and sold, gross and net proceeds, transaction expenses, post-IPO capitalization and shareholder ownership.
The model supports both a Funds Raised approach and a Fixed Shares & Price approach to structuring the offering. It incorporates primary and secondary share sales, a greenshoe over-allotment option, underwriting and other transaction expenses, debt repayment and use of proceeds, as well as the resulting impact on cash, debt, ownership and dilution. The model also includes comparable company analysis, DCF valuation, a valuation football field, post-IPO EPS projections, sensitivity analyses and comprehensive model integrity checks.
The structure of the template follows Financial Modeling Best Practices principles and is fully customizable.
Model Structure
• General Setup
Central assumptions section containing the principal company, valuation and IPO transaction inputs. Key assumptions include the IPO pricing date, operating scenario, offering structure methodology, pre-IPO capitalization, cash and debt balances, target EV / NTM EBITDA multiple, IPO discount, offering price, primary and secondary proceeds, greenshoe assumptions, selling shareholder allocations, underwriting fees, transaction expenses, use of proceeds, DCF assumptions and forecast operating drivers.
• Projections
Integrated historical and forecast financial model presenting the company’s projected financial performance before and following the IPO. The section includes revenue, gross profit, operating expenses, EBITDA, EBIT, interest, taxes, earnings, EPS, cash flow, capital expenditure, working capital, cash balances and debt development over the forecast period.
• Comparable Companies Analysis
Trading comparables analysis used to benchmark the company’s valuation against a selected peer group. The section calculates relevant market and valuation multiples, summarizes peer statistics and compares the company’s implied valuation at the IPO offering price and fully distributed value against the comparable company range.
• Valuation
Comprehensive valuation analysis incorporating a Discounted Cash Flow methodology and a valuation football field. The DCF calculates enterprise value, equity value and implied value per share based on unlevered free cash flow, WACC and terminal growth assumptions, while the football field compares valuation ranges derived from trading multiples, DCF and the indicative IPO price range.
• IPO Mechanics
Detailed IPO transaction analysis covering valuation, pricing, offering structure and proceeds. The section calculates fully distributed value per share using the Treasury Stock Method, the IPO discount, indicative offering price range, primary and secondary shares, greenshoe shares, gross and net proceeds, underwriting fees, offering expenses and Sources & Uses of primary proceeds. It also presents the resulting Pro Forma cash, debt, net debt and fully diluted share count following the offering.
• Ownership & Dilution
Pre- and post-IPO capitalization analysis showing the ownership of founders, financial sponsors, management and employees, equity award holders and new public investors. The section incorporates secondary share sales and primary share issuance to calculate post-IPO ownership percentages, dilution to existing shareholders, retained ownership value and cash realized by selling shareholders.
• Sensitivity Analysis
Dynamic sensitivity tables evaluating the impact of key valuation and transaction assumptions. The model includes analysis of offering price across different EV / NTM EBITDA multiples and IPO discounts, public float sensitivity to offering price and primary proceeds, and DCF enterprise value sensitivity to WACC and terminal growth assumptions.
• Executive Summary
High-level dashboard summarising the principal IPO transaction assumptions and outputs. The section presents key offering terms, valuation metrics, offering size and proceeds, pre- and post-IPO capitalization, ownership and dilution, operating performance, valuation outputs and other key statistics required to evaluate the proposed IPO transaction.
Detailed instructions for the use of the model are included in the Excel file.
Help & Support
Committed to high quality and customer satisfaction, all our templates follow best-practice financial modeling principles and are thoughtfully and carefully designed, keeping the user’s needs and comfort in mind.
No matter if you have no experience or are well versed in finance, accounting, and the use of Microsoft Excel, our professional financial models are the right tools to boost your business operations!
If you, however, experience any difficulty while using this template and you are not able to find the appropriate guidance in the provided instructions, please feel free to contact us for assistance.
If you need a template customized for your business requirements, please e-mail us and provide a brief explanation of your specific needs.
Cap Table and VC Returns Excel template covering funding rounds, dilution, ownership, preferred shar... Read more
Highly-sophisticated and user-friendly financial model for Startup Companies providing a 5-Year adva... Read more
Dynamic 10-Year Financial Model, suitable for any type of business, supporting strategic planning, i... Read more
M&A financial model providing transaction analysis, valuation, deal financing, synergies, and ac... Read more
Leveraged Buyout (LBO) financial model providing acquisition financing, debt analysis, exit valuatio... Read more
A collection of templates suitable for investment decisions in various types of businesses/industrie... Read more
Budgeting & Forecasting Template suitable for either a Trade or Service company in any type of i... Read more
Management Accounts Template, providing full reporting cycle and mapping between accounting and fina... Read more
A collection of templates suitable for various types of businesses/industries, covering major accoun... Read more
User friendly template enabling user to prepare an operating budget to project company’s performan... Read more
You must log in to submit a review.