Financial Model, Marketplaces, Online & IT Businesses

5-year Financial Projections, Accounting, Break-Even, Business Valuation, Cash Flow Analysis, Cash Flow Projections, Cash on Cash Yield, Cash-on-Cash Multiple, CFO, Cost Projections, Dashboard, DCF Model, Debt Schedule, Debt Service Coverage, Discount Rate, Excel, Financial Feasibility, Financial Model, Financial Modeling, Financial Planning, Financial Projections, Forecast, Forecasting, Investment Multiple, Investor Cash Flows, Investors, IRR (Internal Rate of Return), NPV (Net Present Value), Pre Money Valuation, Private Equity, Profitability Analysis, Return on Investment (ROI), Revenue Projections, Scenario Analysis, Sensitivity Analysis, Staffing, Startup Financial Models, Supply, Terminal Value, Venture Capital

 

Video Tutorial:

An ad network facilitates the connection of advertisers and publishers in exchange for a fee, which is usually based on the total ad revenue.

Let’s start with how revenue is defined. It is based on assumptions related to 3 different types of publishers and a related fill rate for each. The following inputs are configurable for each type:
1. Start month
2. Start count
3. % Growth of count (configurable over 5 years)
4. Starting average monthly impressions per publisher
5. % Growth of impressions overtime per publisher (configurable over 5 years)
6. Average CPM (configurable over 5 years)
7. % of Impressions that are filled by advertisers (configurable over 5 years)
8. Fee rate (drives top-line revenue for the ad network) (configurable over 5 years)

This model did require some complex logic for the way that impressions are defined. This allows for the user to account for the fact that the impressions a publisher currently has are not likely to be the impressions that the publisher has in the future (it will likely go up at some rate). This model isolates the monthly new publishers into their own cohort and shows the impressions attained over the course of the 5-year model. Each month has its own row for how this can change over time depending on the amount of publishers of a given type that joined in a given month. That data is then aggregated to get a total impression count each month.

The best way I could think of to show the way advertisers are being connected to publishers and the resulting revenue is to define a percentage of the impressions that are purchased by advertisers and then total revenue is calculated based on a CPM. This is because if there is not enough demand for ads, then it is possible not all 100% of the impressions will be filled by advertisers. There is an input for the average amount of impressions purchased by an advertiser per month, which then backs into the potential count of advertisers on the network.

More inputs include:
1. Exit month if applicable
2. Exit value (based on a multiple of trailing 12-month revenue) if applicable
3. Required equity contributed by investors/owners/debt
4. % Ownership given to investors
5. Fixed monthly operating expenses (broken into G&A/S&M/R&D as well as COGS. For COGS (cost of goods sold) there are up to 4 ways to define this depending on your specific situation. They include a general % of revenue, a hard-coded dollar value per publisher type per month, a $ amount per impression, and a $ value cost per publisher per month. Some, all, or just one may be relevant. For fixed costs, the user can define the start month and monthly cost over each of the 5 years.
6. One-time startup costs
7. Future one-time CAPEX

After all the assumptions are entered, the resulting monthly P&L detail is populated. That rolls all the way down to EBITDA, Net Income before Taxes, and cash flow. The monthly summary rolls into an annual summary and that rolls into an Executive Summary that focuses on high-level financial line items.

A DCF analysis is done on a project level as well as an investor view and owner view depending on how the funding sources were filled out. This area includes IRR, ROI, equity multiple, and NPV.

A lot of visuals exist to demonstrate the assumptions in an easy to visualize way.