This note was written as a supporting document for a complete project submission for the Entrepreneurial finance course at Columbia Business School in Dec 2000. While the exhibits and EXCEL files are dated, the process followed to build the online education model is still very relevant. If you would like to see an example with detailed step by step numerical calculations please see the following free cases:
- Master Case: AMD: Valuation and Projections: Case Guide
- Master Case: Credit Process: Baldwin Piano
- Master Case: Electronic Arts: Corporate Finance
- Master Case: Office Depot: Ratio Analysis
- Finance Case: Ratio Analysis: ODP and Staples
Valuation model – Core steps
Step One – size of market, customer penetration, income and expense heads
- Identified Key Markets for online education users. Users included business school students, business school applicants, actuaries, small business owners and online investors.
- Estimated Market size and growth rates for each of the above target segments (business school students, business school applicants, actuaries, small business owners and online investors)
- Estimated product penetration rates for next five years for each of the target segments (business school students, business school applicants, actuaries, small business owners and online investors)
- Estimated income and expense items for next five years
Step Two – projected cash flows and valuation
- Modeled projected income statement and balance sheet for five years
- Projected Free Cash flows for the next five years
- Estimated three different discount rates for diversified investors (VC), traditional shareholders (less diversified than VC’s) and Entrepreneurs. Used for getting a range of valuation for the firm across these segments.
- Performed three different valuations using three different discount rates
Step Three – sensitivity and stress testing
Performed sensitivity analysis on the following parameters
- Discount Rate
- Terminal Growth Rate
- Average annual revenues per customer
- Base Product Penetration rates
Additional analysis was also performed to get a feel for the value of the firm under worst case scenarios and the level of second round funding required.
The four factors were chosen because valuation is very sensitive to changes in any one of these. Attached exhibits include a list of key assumptions behind the model, projected financial statements, valuation & results of sensitivity analysis.
Key Assumptions for the valuation model
Valuation
- Penetration rates have been estimated as the number of people who are classified as early adapters wrt to new products launched by the online education business
Financials
- On a net basis, Current Assets would cancel out Current Liabilities. Since the business is subscription based the only significant item affected would be deferred liability.
Market Size
Target Segment |
Size | Growth rate |
Number of students per program |
300 |
Stable |
Top 50 Business School Students (Past, Present & Future) |
285,000 |
Stable |
Number of applicants to the top 50 Business Schools |
60,000 |
Stable |
Actuaries & Other certified professionals |
233,000 |
Stable |
Small Business Managers (Actual Size = 2 M) |
250,000 |
Stable |
Online Investors (Actual Size 2-10 Million) |
500,000 |
20-30% |
Stable = zero growth or growing at GDP growth rate
Base Penetration Rates
Target Segment |
2000 |
20001 |
2002 |
2003 |
Top 50 Business School Students (Past, Present & Future) |
3% |
4% |
4% |
5% |
Number of applicants to the top 50 Business Schools |
2% |
2% |
2% |
3% |
Actuaries & Other certified professionals |
5% |
6% |
7% |
9% |
Small Business Managers (Actual Size = 2 M) |
0% |
2% |
2% |
2% |
Online Investors (Actual Size 2-10 Million) |
0% |
2% |
2% |
2% |
Discount Rates
Investor | Beta | Risk Premium | Risk free rate | Discount Rate |
Diversified Investor | 1.0 | 7% | 6.1% | 13.1% |
Typical investor | 2.0 | 7% | 6.1% | 20.1% |
Entrepreneur | 4.0 | 7% | 6.1% | 34.1% |
Average Revenues Per customer
The average revenue per customer is a weighted average of the number of customers in a segment time (*) the average revenues for that segment
Market |
Average Revenue Per customer |
Business school Student Market |
50 |
Business school Applicant market |
30 |
Actuarial Market |
175 |
Small Business Managers |
200 |
Online Investors |
250 |
Average Revenue per customer |
175 |
Expenses
Expenses |
2000 |
2001 |
2002 |
2003 |
Base |
Marketing Expenses |
15% |
20% |
25% |
30% |
% of Net Revenues |
Growth in (G & A) Expenses |
200% |
60% |
40% |
Increase over last year | |
Tax Rate |
33% |
35% |
36% |
36% |
|
Depreciation Rate |
30% |
20% |
15% |
15% |
Total Fixed Assets |
Valuation Results
Investor Type |
Typical Investor |
Diversified Investor |
Management |
Terminal Growth Rate |
6% |
6% |
6% |
Discount Rate |
20% |
13% |
34% |
Terminal Cash Flow |
2,660,359 |
2,660,359 |
2,660,359 |
Terminal Value |
18,856,552 |
37,469,842 |
9,467,469 |
PV of Terminal Value |
7,543,889 |
20,247,380 |
2,183,182 |
Total Value |
13,285,832 |
26,908,452 |
6,609,062 |
No of Shares |
1,000,000 |
1,000,000 |
1,000,000 |
Price Per share |
13 |
27 |
7 |
Current Offering |
50,000 |
50,000 |
50,000 |
Total Expected Proceeds |
664,292 |
1,345,423 |
330,453 |
Funding Requirements
Round | Amount | % of equity | Timing |
First round | 250,000 –300,000 | 2.5% | Apr – May 2000 |
First round | 250,000 – 300,000 | 2.5% | Dec – Jan 2001 |
Second round | 1,500,000 – 2,000,000 | * | Jun – Jul 2001 |
* Decision for second round financing would be taken in Jan 2001. % of equity would also be set at that stage
Next stage financing would only occur if milestone for earlier stages have been met. Primary reason for using staged financing were
- Reduce uncertainty in a systematic fashion at each stage
- “Fume dates” to ensure that the team has the right incentives to hit milestones
- Retaining management control over the firm
The level of second round funding was determined by analyzing the maximum second year deficit across a range of possible scenarios.
Exhibits for the online education business valuation model
- Projected Income Statement
- Projected Balance Sheet
- Free Cash Flows and Valuation
- Scenarios & Results
- Sensitivity to Discount rate and Terminal Growth Rate
- Sensitivity to Product Penetration and Average Annual Revenues per customer
- Sensitivity of Second round funding requirements to Penetration & Average annual revenues per customer
- Valuation on Elm Street (worst case scenarios)