Case study: Financial modelling for TechStars Inc.

Background:

TechStars Inc. is a SaaS company offering software services through both subscription and usage$-$based models. The company is seeking an FP&A Analyst to develop a comprehensive five$-$year financial model covering the income statement, balance sheet, and cash flow statement.

Assumptions:

$1.$ Subscription revenue stream:

$-$ TechStars offers monthly and annual subscription plans. Monthly subscribers pay $$50$ per month, while annual subscribers pay the full amount upfront with $15\%$ discount. Assume that all annual contracts are up for renewal on Jan$-1$st of each year.

$2.$ Usage$-$based revenue stream:

$-$ This is estimated to be $$5/$month per active subscriber during high demand seasons, which happens at the last month of each quarter. And for it to be $$3$ the rest of the year.

$3.$ User growth and retention:

$-$ The company anticipates a user growth of $15\%$ annually for both monthly and annual subscribers and the churn rate is expected to be $10\%$ every year.

$4.$ Costs and expenses:

$-$ Cost of sales is expected to be around $30\%$ of subscription revenue. The company is working on a cost optimization project which be lunched in year $3$ and is expected to reduce the cost of sales by $20\%$

$-$ General and administrative costs are expected to grow by $12\%$ every.

$-$ Sales and marketing costs are expected to increase proportionally with revenue growth.

$5.$ Capital expenditure and investments:

$-$ Capex is projected to be $5\%$ of the previous year revenue and depreciation is $25\%$ of previous year fixed assets ending balance.

$-$ R&D is expected to be $30\%$ of revenue and amortization is estimated to be $25\%$ of the previous year's R&D ending balance.

$6.$ Working capital:

$-$ Accounts receivable and accounts payable are assumed to grow proportionally with revenue.

$-$ Working capital days are expected to remain stable over the forecast period.

$7.$ Interest and taxes:

$-$ Interest expense is based on a fixed rate of $4\%$ on outstanding debt.

$-$ The tax rate is estimated to be $20\%$ of taxable income.

Base year numbers $($End of period$)$:

$-$ Number of monthly subscribers: $5,000$

$-$ Number of annual subscribers: $1,000$

$-$ Total revenue: $$3,000,000$

$-$ Gross margin: $70\%$

$-$ General and administrative: $$1,000,000$

$-$ Sales and marketing: $$500,000$

$-$ Depreciation: $$100,000$

$-$ R&D amortization: $$100,000$

$-$ PP&E balance: $$500,000$

$-$ R&D balance: $$1,100,000.$

$-$ Accounts receivables: $$300,000$

$-$ Accounts payable: $$250,000$

$-$ Cash: $$2,000,000$

$-$ Share capital: $$100,000.$

$-$ Long$-$term debt: $$2,000,000($to be paid by end of year $5).$

Requirements:

a$-$ Building the financial model

$-$ Develop a detailed income statement projecting revenue, COGS, gross profit, operating expenses, EBITDA, depreciation, interest, taxes, and net income for each year.

$-$ Create a comprehensive balance sheet displaying assets, liabilities, and equity for each year, considering Capex, depreciation, debt, and equity financing.

$-$ Construct a cash flow statement $($operating$,$ investing, financing$)$ for each year, reconciling with changes in the balance sheet accounts.

$-$ Perform sensitivity analysis on user growth rates and subscription churn rates to assess their impact on financial statements.

$-$ Summarize key findings and insights derived from the financial model, presented in a clear and organized manner.

Show only table of projection of year $1,2,3,4,5$ and explain why $($Dont forget churn rate, growth rate, G&A$,$ S&M$,$ R&D$,$ Dep, Capex, and tax rate$)$