FINANCIAL MODELING PREMIUM BUS SERVICE

FINANCIAL MODELING PREMIUM BUS SERVICE

BY NAVEEN KUMAR DUTTAMBA (FINANCE),FINANCIAL MODELING

ASSUMPTIONSAssumptions - Bus

Cost of Bus 7,500,000 Maintenance / year 10% Escalation Rate - Maintenance 15% Life of Bus (years) 4 Deprecation Rate 25%

ASSUMPTIONAssumptions - Operating Revenues Seating Capacity 35 Load Factor 50% Tariff (Rs./Ticket) 300.00 Escalation Rate - Tariff 10%

ASSUMPTIONAssumptions - Operating Costs Fuel Efficiency ( Km / L ) 5 Driver Cost (Rs./ Trip) 500 Miscellaneous (Rs. / Trip) 500Toll Amount (Rs. / Trip) 500

ASSUMPTIONAssumptions - General Tax Rate 33% Debt is to be repaid in 4 equal annual installments at the end of every year Interest Income 5% Diesel rate (Rs./Lit) 42 Diesel Price escalation 15% Inflation 10%

ASSUMPTIONAssumptions - Operating

General Route Mumbai - Pune Distance (Kms) 155 Estimated Time Hours/Trip 3 Trips / Day 4

ASSUMPTIONAssumptions - Financing Debt 75% Equity 25% Interest on Debt 15% Tenure of loan (years) 4 Cost of Equity 20%

Income Statement 20162017201820192020Flag01234Revenue 7,665,000 8,431,500 9,274,650 10,202,115 Other IncomeFuel Costs 1,900,920 2,186,058 2,513,967 2,891,062 Maintenance Expenses 750,000 862,500 991,875 1,140,656 Driver Costs 730,000 803,000 883,300 971,630 Miscellaneous Costs 730,000 803,000 883,300 971,630 Toll Amount 730,000 730,000 730,000 730,000 Cost of Revenues 4,840,920 5,384,558 6,002,442 6,704,978

INCOME STATEMENT20162017201820192020Gross Pofit / (EBITDA) 2,824,080 3,046,942 3,272,208 3,497,137 Depreciation 1,875,000 1,875,000 1,875,000 1,875,000 Interest Expense 843,750 674,776 480,456 256,988 Profit Before Tax 105,330 497,166 916,752 1,365,149 Income Taxes 34,759 164,065 302,528 450,499 Profit After Tax 70,571 333,101 614,224 914,650

DEBT SCHEDULE20162017201820192020 BOP - Debt 5,625,000 5,625,000 4,498,507 3,203,041 1,713,254 Int Expense 843,750 674,776 480,456 256,988 EAI 1,970,243 1,970,243 1,970,243 1,970,243 EOP - Debt 4,498,507 3,203,041 1,713,254 (0) Principle Repaid 1,126,493 1,295,466 1,489,786 1,713,254 LINKED TO BALANCE SHEET IN EXCEL

BALANCE SHEET20162017201820192020AssetsCash 819,078 1,731,713 2,731,151 3,807,546 PPE, Gross 7,500,000 7,500,000 7,500,000 7,500,000 7,500,000 Acc Depreciation - 1,875,000 3,750,000 5,625,000 7,500,000 PPE, Net 7,500,000 5,625,000 3,750,000 1,875,000 - Total Assets 7,500,000 6,444,078 5,481,713 4,606,151 3,807,546

BALANCE SHEETLiabilitiesDebt 5,625,000 4,498,507 3,203,041 1,713,254 (0)Equity 1,875,000 1,875,000 1,875,000 1,875,000 1,875,000 Retained Profit 70,571 403,672 1,017,896 1,932,546 Total Liabilities 7,500,000 6,444,078 5,481,713 4,606,151 3,807,546 LINKED TO DEBT SCHEDULE

Cash Flow Statement 20162017201820192020Net Income (PAT) 70,571 333,101 614,224 914,650 Depreciation 1,875,000 1,875,000 1,875,000 1,875,000 Cash Flow From Operations - 1,945,571 2,208,101 2,489,224 2,789,650 Capex (7,500,000)Cash Flow from Investing (7,500,000) - - - - Debt (Repayment)/Issuance 5,625,000 (1,126,493) (1,295,466) (1,489,786) (1,713,254)Equity Addition 1,875,000 Cash Flow from Financing 7,500,000 (1,126,493) (1,295,466) (1,489,786) (1,713,254)Net change in Cash - 819,078 912,635 999,437 1,076,395 Cash - BOP 819,078 1,731,713 2,731,151 Cash - EOP 819,078 1,731,713 2,731,151 3,807,546

Free Cash Flow - Equity 20162017201820192020Net Income - 70,571 333,101 614,224 914,650 + Depreciation - 1,875,000 1,875,000 1,875,000 1,875,000 - Capital Expenses (7,500,000) - - - - - Debt Repayment/+ raised 5,625,000 (1,126,493) (1,295,466) (1,489,786) (1,713,254)Cash Flow to Equity (1,875,000) 819,078 912,635 999,437 1,076,395

Returns Analysis - Equity 20162017201820192020FCFE (1,875,000) 819,078 912,635 999,437 1,076,395 Cost of Equity20.0% NPV 538,813 IRR33.80%

SCENARIO ANALYSISFinancing Debt (%) - For Neutral CaseInterest Rate (%)34%10%25%40%55%95%12%17.4%19.2%21.8%26.0%129.0%13%17.3%19.0%21.4%25.3%121.5%14%17.3%18.8%21.0%24.6%113.8%15%17.2%18.6%20.6%23.9%105.9%16%17.1%18.4%20.2%23.1%97.9%17%17.1%18.2%19.8%22.4%89.7%