Toy Problem-Basic Financial Model_0 by huangyuarong

VIEWS: 4 PAGES: 20

									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.

								
To top