Get documents about "Toy Problem-Basic Financial Model_0
Document Sample
Inputs
General Project Details
Name of the Project Manufacturing Unit
Location of the Project
Size of the Project Unit/per annum capacity
Life of the Project years
Construction Timeline
Date
Const. Period months
Capacity Utilization
Project Capital Cost & Phasing
Civil Construction & Plant & Machinery
Land
Interest During Construction
Total Cost of the Plant
Project Cost Phasing Months-> 1
Land % age spent 100.0%
Civil Construction & Plant & Machinery %age spent 20.0%
Expenses
Operating Expenses 5% per year of hard cost
Revenue
Sales Price
Finished Product Sales Price US$/unit
Financial Assumptions
Financial Structure
Debt %
Equity %
Debt Component
Equity Component
Expected Return on Equity % p.a.
Discount Rate (equiv. to WACC)
Other Assumptions
Fiscal Assumptions
Income Tax %
Depreciation
Depreciation Rate (Civil Works, Plant & Machinery) %
End of Sheet
Manufacturing Unit Legends
Yet to decide Input data that can be changed
100,000 xxx Calculated Data
10 xxx data linked to other sheets & should not be deleted
Const. Start CoD xxx Units
1-Jan-12 31-Dec-12 xxx Sum/check
12
90%
100 US$ Mn
10 US$ Mn
4 US$ Mn
114 US$ Mn
2 3 4 5 6 7
5.0% 5.0% 5.0% 10.0% 10.0% 10.0%
5.5 US$ Mn
US$ 300
70% 80 US$ Mn
30% 34 US$ Mn
Interest Rate Moratorium Repayment Period
8.00% 1 yrs 6 yrs
15%
10.10%
30.00%
10.00%
Legends
d to other sheets & should not be deleted
8 9 10 11 12
5.0% 5.0% 5.0% 5.0% 15.0%
Project Capital Expense Phasing
Months
Month Count
Capital expense
Land US$ Mn
Civil Construction & Plant & Machinery US$ Mn
Interest During Construction US$ Mn
Total US$ Mn
Interest During Construction (IDC)
Opening Debt US$ Mn
Addition US$ Mn
Interest During Construction US$ Mn
Closing Debt US$ Mn
Debt US$ Mn
Equity US$ Mn
Investment breakup including IDC (for depreciation purpose)
Land US$ Mn
Plant & machinery US$ Mn
Total US$ Mn
Annual capex Financial Year Ending->
Annual Breakup of Capex including IDC
Plant & machinery US$ Mn
Land US$ Mn
Total US$ Mn
Annual Breakup of Capex including IDC
Equity US$ Mn
Debt US$ Mn
Total US$ Mn
Interest payment US$ Mn
End of Sheet
Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12
1 2 3 4 5 6 7 8
10.0 10.0 - - - - - - -
100.0 20.0 5.0 5.0 5.0 10.0 10.0 10.0 5.0
3.6 0.1 0.2 0.2 0.2 0.2 0.3 0.3 0.4
113.6 30.1 5.2 5.2 5.2 10.2 10.3 10.3 5.4
- 21 25 28 32 39 46 54
21 4 4 4 7 7 7 4
0.1 0.2 0.2 0.2 0.2 0.3 0.3 0.4
21 25 28 32 39 46 54 57
21 4 4 4 7 7 7 4
9.02 1.55 1.55 1.56 3.07 3.09 3.10 1.61
10.3 10 0 0 0 0 0 0 0
103.3 20.1 5.1 5.2 5.2 10.2 10.3 10.3 5.3
113.6 30.1 5.2 5.2 5.2 10.2 10.3 10.3 5.4
Financial Year Ending-> Dec-13 Dec-14 Dec-15 Dec-16 Dec-17
Year Count-> 0 1 2 3 4
103.3
10.3
113.6
34.1
79.5
113.6
3.6
Sep-12 Oct-12 Nov-12 Dec-12
9 10 11 12
- - - -
5.0 5.0 5.0 15.0
0.4 0.4 0.4 0.5
5.4 5.4 5.4 15.5
57 61 65 69
4 4 4 11
0.4 0.4 0.4 0.5
61 65 69 80
4 4 4 11
1.62 1.63 1.63 4.65
0 0 0 0
5.4 5.4 5.4 15.4
5.4 5.4 5.4 15.5
Debt Schedule
Financial Year Ending -> Dec-12 Dec-13 Dec-14 Dec-15 Dec-16
Year Count-> 1 2 3 4
Interest Calculations
Term Loan
Loan Amount 79.50
Moratorium Period 1
Repayment Period 6
Opening Balance US$ Mn 80 80 66 53
Interest payment 8.00% US$ Mn 6.36 5.83 4.77 3.71
Principal Payment US$ Mn - 13 13 13
Closing Balance US$ Mn 79.50 80 66 53 40
DSCR
Cash Profit US$ Mn 14 14 15 16
Interest US$ Mn 6 6 5 4
Total (Cash available for debt service) US$ Mn 20 20 20 19
Interest US$ Mn 6 6 5 4
Term Loan Installment US$ Mn - 13 13 13
Total Debt Service requirement US$ Mn 6 19 18 17
DSCR NA 3.15 1.04 1.09 1.14
Minimum DSCR 1.04
Average DSCR 1.46
End of Sheet
Dec-17 Dec-18 Dec-19 Dec-20 Dec-21 Dec-22
5 6 7 8 9 10
40 27 13 - - -
2.65 1.59 0.53 - - -
13 13 13
27 13 - - - -
16 17 18 18 18 18
3 2 1 - - -
19 19 18 18 18 18
3 2 1 - - -
13 13 13 - - -
16 15 14 - - -
1.19 1.25 1.33 NA NA NA
Depreciation Schedule
Financial Year Ending Dec-12 Dec-13 Dec-14 Dec-15 Dec-16
Year Count 0 1 2 3 4
Civil Construction & Plant & Machinery
Op Balance US$ Mn - 103 93 83 72
Additions during the year US$ Mn 103 - - - -
Total of above US$ Mn 103 103 93 83 72
Depreciation US$ Mn 10 10 10 10
Closing Balance US$ Mn 103 93 83 72 62
Land US$ Mn 10 10 10 10 10
Total Depreciated Assets US$ Mn 113.6 103.3 92.9 82.6 72.3
Total Yearly Depreciation US$ Mn 10.3 10.3 10.3 10.3
Cumulative Depreciation US$ Mn 10 21 31 41
End of Sheet
Dec-17 Dec-18 Dec-19 Dec-20 Dec-21 Dec-22
5 6 7 8 9 10
62 52 41 31 21 10
- - - - - -
62 52 41 31 21 10
10 10 10 10 10 10
52 41 31 21 10 - 0
10 10 10 10 10 10
62.0 51.6 41.3 31.0 20.7 10.3
10.3 10.3 10.3 10.3 10.3 10.3
52 62 72 83 93 103
Profit & Loss Statement
Dec-12
Financial Year ending -> Dec-13 Dec-14 Dec-15
Year Count -> 0 1 2 3
Operations -->
Revenue
Capacity units 100,000 100,000 100,000
Capacity Utilization % 90% 90% 90%
Production units 90,000 90,000 90,000
Sales rate US$/units 300 300 300
Revenue from sales Mn US$/unit 27 27 27
Total Revenue Mn US$ 27 27 27
Expenses
O&M Expenses Mn US$ 5.50 5.50 5.50
Total Expenses Mn US$ 6 6 6
PBDIT Mn US$ 21.5 21.5 21.5
Interest on term loan Mn US$ 6.36 5.83 4.77
PBDT Mn US$ 15.1 15.7 16.7
Deprecation Mn US$ 10 10 10
Profit Before tax Mn US$ 4.8 5.3 6.4
Taxes Mn US$ 1 2 2
PAT Mn US$ 3.4 3.7 4.5
End of Sheet
Dec-16 Dec-17 Dec-18 Dec-19 Dec-20 Dec-21 Dec-22
4 5 6 7 8 9 10
100,000 100,000 100,000 100,000 100,000 100,000 100,000
90% 90% 90% 90% 90% 90% 90%
90,000 90,000 90,000 90,000 90,000 90,000 90,000
300 300 300 300 300 300 300
27 27 27 27 27 27 27
27 27 27 27 27 27 27
5.50 5.50 5.50 5.50 5.50 5.50 5.50
6 6 6 6 6 6 6
21.5 21.5 21.5 21.5 21.5 21.5 21.5
3.71 2.65 1.59 0.53 - - -
17.8 18.8 19.9 21.0 21.5 21.5 21.5
10 10 10 10 10 10 10
7.5 8.5 9.6 10.6 11.2 11.2 11.2
2 3 3 3 3 3 3
5.2 6.0 6.7 7.5 7.8 7.8 7.8
Income Tax
Financial Year Ending -> Dec-12 Dec-13 Dec-14 Dec-15 Dec-16
Year Count -> 0 1 2 3 4
Tax Calculation
Profit before tax and depreciation US$ Mn 15 16 17 18
Depreciation US$ Mn - 10 10 10 10
Carry Forward Loss US$ Mn - - - -
Net Taxable Profit US$ Mn 5 5 6 7
Tax Payable US$ Mn 1.4 1.6 1.9 2.2
End of Sheet
Dec-17 Dec-18 Dec-19 Dec-20 Dec-21 Dec-22
5 6 7 8 9 10
19 20 21 22 22 22
10 10 10 10 10 10
- - - - - -
9 10 11 11 11 11
2.6 2.9 3.2 3.4 3.4 3.4
Balance Sheet
Financial Year Ending -> Dec-12 Dec-13 Dec-14 Dec-15 Dec-16
Year Count -> 0 1 2 3 4
Sources of Funds (Liabilities)
Share Holders Funds
Share Capital (Equity) Mn US$ 34 34 34 34 34
Reserves & Surplus Mn US$ - 3 7 12 17
Loan Funds
Term Loan Mn US$ 80 80 66 53 40
Total Liabilities Mn US$ 114 117 107 99 91
Application of Funds (Assets)
Fixed Assets
Land Mn US$ 10 10 10 10 10
Other Fixed assets Mn US$ 103 93 83 72 62
Current Assets
Cash & Bank Balance Mn US$ - 14 15 16 18
Total Assets Mn US$ 114 117 107 99 91
Check(0) - - - - -
End of Sheet
Dec-17 Dec-18 Dec-19 Dec-20 Dec-21 Dec-22
5 6 7 8 9 10
34 34 34 34 34 34
23 29 37 45 53 60
27 13 - - - -
83 77 71 79 87 94
10 10 10 10 10 10
52 41 31 21 10 - 0
21 25 30 48 66 84
83 77 71 79 87 94
- - - - - -
Cash flow & Returns Calculations
Financial Year Ending Dec-12 Dec-13
Year Count 1
Cash Flows (in $ mns)
Profit After Tax US$ Mn - 3
Add: Tax Depreciation US$ Mn - 10
Cash From operating activities US$ Mn - 14
Add interest US$ Mn - 6
Less Capex US$ Mn 114 -
Free Cash Flow to Firm US$ Mn - 114 20
Debt
Term Loan
Drawn US$ Mn 80 -
Repayment US$ Mn - -
Interest US$ Mn - 6
Free Cash Flow to Equity US$ Mn - 34 14
Equity US$ Mn 34 -
Surplus during the year US$ Mn - 14
Closing balance US$ Mn - 14
Project Returns
Discounting factor 1.00 0.91
Present value of future cash flow Discount rate 10% - 114 22
Net Present value US$ Mn 217
Internal Rate of Return % 11%
Equity Returns
Discounting factor 1.00 0.87
Present value of future cash flow Discount rate 15% - 34 12
Net Present value US$ Mn 1
Internal Rate of Return % 16%
End of Sheet
Dec-14 Dec-15 Dec-16 Dec-17 Dec-18 Dec-19 Dec-20 Dec-21 Dec-22
2 3 4 5 6 7 8 9 10
4 4 5 6 7 7 8 8 8
10 10 10 10 10 10 10 10 10
14 15 16 16 17 18 18 18 18
6 5 4 3 2 1 - - -
- - - - - - - - -
20 20 19 19 19 18 18 18 18
- - - - - - - - -
13 13 13 13 13 13 - - -
6 5 4 3 2 1 - - -
1 2 2 3 4 5 18 18 18
- - - - - - - - -
1 2 2 3 4 5 18 18 18
15 16 18 21 25 30 48 66 84
0.82 0.75 0.68 0.62 0.56 0.51 0.46 0.42 0.38
24 26 28 31 33 36 39 43 47
0.76 0.66 0.57 0.50 0.43 0.38 0.33 0.28 0.25
1 1 1 2 2 2 6 5 4
www.finfizz.
This model is prepared by finfizz.org. A
w.finfizz.org
by finfizz.org. All rights are reserved.
