E-Commerce & Web Shops, Financial Model, Online & IT Businesses, Retail, SaaS

5-year Financial Projections, Annual Recurring Revenue (ARR), Budgeting, Business Valuation, Cap Table, Cash Flow Analysis, Cash Flow Projections, CFO, Churn, Controlling, Customer Acquisition Costs (CAC), Customer Lifetime Value, DCF Model, Debt Service Coverage, Excel, Financial Analysis, Financial Feasibility, Financial Model, Financial Modeling, Financial Planning, Financial Projections, Financial Statements, Forecast, Forecasting, Income Statement, Investment Multiple, Investor Cash Flows, Investors, IRR (Internal Rate of Return), Know-How, KPIs (Key Performance Indicators), Monthly Recurring Revenue (MRR), NPV (Net Present Value), Payback Period, Private Equity, Private Equity Waterfall Distribution Models, Pro-Forma, Profitability Analysis, Return on Invested Capital (ROIC), Return on Investment (ROI), Revenue Projections, Scenario Analysis, Sensitivity Analysis, Startup Financial Models, Subscriptions, Terminal Value, Three Statement Model | Three Financial Statements, Valuation, Venture Capital

Video Overview:

I have a fun financial model here. It is for businesses that ship customers products in a box each month for a monthly fee. It includes a bunch of features based on client feedback I’ve gotten from all sorts of subscription models in the past and has assumptions that are specific to the subscription box industry.

Customer Acquisition and Growth:

• Define up to six advertisement channels and the month each one launches, each with a monthly ad spend (adjustable by year) and an average cost per customer acquisition input.
• Define monthly organic traffic, the start month of that, the monthly growth rate (adjustable by year), and the conversion rate of organic traffic to subscription signup.

Pricing and Retention / Churn

• Define up to six subscription pricing levels, each with a monthly price, the average contract length (in months), the percentage of all signups allocated to each pricing tier, and the average retention rate of each subscription level over time (you can enter exponential decay curves, linear curves, or whatever best fits your data and research.
• You can also account for promotions by tier, for example, if customers get their first month free, or the first month half off, or what have you. The model lets the user define the percentage of the full price paid in the first 7 months of the life of a customer, and this is uniquely configured for each of the six pricing levels.

Variable / Fixed Costs and CAPEX

• Configuration for a percentage of total revenue subject to costs that can be defined as a percentage of revenue (such as credit card fees).
• Define the average monthly cost per subscription tier (this has the ability to be offset to account for inventory purchasing frequency and will affect cash flow accordingly).
• The other general cost of goods sold can be defined as a fixed monthly amount that is adjustable each year.
• There is a full schedule for other fixed costs such as general and administrative, research and development, and sales and marketing. Each of those sections lets the user define the start month, monthly cost, and whether it is subjective to payroll taxes or not.
• There is a separate section to account for any other one-time costs that are non-depreciable and happen in period 1.
• There is a separate CAPEX schedule so the user can define the purchase of any depreciable items, the month of purchase, and the useful life.

Exit Value

• The model runs for up to six full years, and you can select any end month you want. At the end month, there is an option (yes/no selector) to opt for an exit value to show or not. If ‘yes’ then any remaining debt will be paid back, and there will be sales proceeds that flow through cash flow based on a trailing 12-month revenue multiple.

Debt and Equity Financing

• This model takes all assumptions and timing of expenses into consideration and outputs the minimum equity (investment) required to keep the business afloat. That can come from inside and outside investors, each with their own percentage share and investment amount.
• The amount of debt proceeds will affect the minimum equity requirement (lowering it).

Output Reports

• Monthly and annual financial statements (3 statement model)
• Monthly and annual pro forma detail (shows customers added / lost / ending count per month and MRR per subscription tier as well as all expenses and items affecting cash flow)
• Annual executive summary
• Lots of visualizations
• DCF Analysis (for the project and for the inside / outside investments)
• Monthly average LTV, CaC, LTV to CaC Ratio, and average months to pay back CaC

This model is also included in the SaaS Financial Models Bundle.