Excel File: Your own model Your group has the choice between a full model and a basic model. Full Model The considered case is the same for all groups, described in the feasibility study on the Korle...

1 answer below ยป

Excel File: Your own model


Your group has the choice between a full model and a basic model.


Full Model

The considered case is the same for all groups, described in the feasibility study on the Korle Bu diagnostic centre in Accra, Ghana. Imagine yourself preparing a model for a bank considering providing debt to the project. Your main output is the DSCR for each period, and the minimum DSCR over all periods. Your model will further need a balance sheet and a cash flow statement. It should be flexible regarding main inputs. For a top grade, it needs a scenario manager covering main risk factors.


A full model is to be constructed on the basis of the provided template model. The grading criteria is the model qualities of transparency, flexibility and reliability described during lectures. Following the detailed rules presented generally will be regarded as best practice.


The template used for the gas power plant example provides a rich and extremely useful infrastructure. During lectures you get acquainted to the sheets scenarios, timing, L, N, C, Comments. It is highly recommended to use all of this infrastructure as much and as strictly as possible. Nevertheless, You should not copy those elements from the templates, but rather code and format them yourself. Any deviance will be captured in the grading. If You use differently structured elements, it is absolute fine. They will be judged by the same standards.


All information sources should be exactly documented in the model.


You will not find all data necessary for your calculations in the feasibility study. When you find some data to be missing, you should first try to find out with the help of thefeasibility study or external sources. If you need to infer indirectly, e.g. performing backwards calculations from results presented there, report your approximation method and your results to me. If you deem it impossible to derive the missing information, ask me for a sensible assumption.


I will publish both mentioned kinds of results to all groups, but the first group asking will be credited with being frontrunner regarding the concerned question. The same applies even stronger, if you publish information from any external sources, which you are generally expected to do. For anycreativeinput or model feature, publishing it as the first group will improve your grading compared to only documenting it in the model. If you are not the first group to publish it, you will not be credited with the idea, but with any successful use of it.


Basic Model

A basic model derives the DSCR for the Korle Bu Diagnostic Centre during a typical period, much in the same way as we practiced it in the one-period "back of an envelope" calculation for the gas power plant example. It is not necessary to use the model template. The remaining framework of the full model delivery applies to the basic model delivery as well.

kb-health-ppptsr20140310-3-4iylxlni.pdf template-example-r3qdkgqz.xlsm key-sheets-jxalpr2i-lbl0o5z1.docx fmvalfmzafarmanoharan-armoogumyemane2020-09-15-rilipnhm-um5i5jvg.xlsm
Answered 23 days AfterAug 19, 2021

Answer To: Excel File: Your own model Your group has the choice between a full model and a basic model. Full...

Ishmeet Singh answered on Sep 05 2021
154 Votes
Index Sheet_Hyperlinks
Inputs
Gantt Chart
Cash Flows
Assumptions
P&L
YoY Growth Rates
Projections for Lease period
Expected Volumes
Inflation Adjusted Cost/Test
Cost Build Up
Revenue Build Up
Cash Flow from Investing
Cash Flow from Financing
Scenario Linkages
NPV
IRR
Payback Period
WACC of Project
Project Outline
Due Dilligence
Scenarios
Project Cost & Finance
Test Volumes and Revenues
ONE PLATFORM DASHBOARD
/xl/drawings/drawing1.xml#P_Dashboard!A1
Structure
INPUT
Model Assumptions
Gantt Chart
Calculations
Cash Flow_Investing
Cash Flow_Financing
Cash Flow_Operations
Cash Flows
P&L
Revenue Drivers
Cost Built up Drivers
Outputs
P_Dashboard (XNPV,XIRR & Payback)
Summary (Balance Sheet, P&L & Cash Flows aggregated)
Ratio Analysis
KPI Dashboard
Inputs
    Name of the Project:
    Private Sector Participation Project
    Korle-Bu Diagnostic Centre PPP Project
    PROJECT OUTLINE
    First Party :    Government of Ghana (MOH)
    Second Party:    IFC (Mott Macdonald (UK) as technical Consultant
and AB & David (Ghana) andGides (France) as legal counsel)
    Service required:    Diagnostic Centre providing both laboratory and imaging services
    OUTCOMES (TECHNICAL DUE DILLIGENCE)
    Laboratory Services    KBTH and the referral centres; Haematology, Chemical
Pathology, Microbiology and Immunology
    Growth Expected     400,000 in 2017; 750,000 in 2021 and 800,000 in 2024.
    Currently (250,000 per year)
    Imaging Services    KBTH; Mammography, Intravenous Urogram,
Ultrasonography, Hysterosalpingography, MRI, CT, GIT/Barium studies, General x-ray and Dexa Scanning
    Growth Expected    65,000 in 2017; 100,000 in 2021 and 106,000 in 2024.
    Currently (36,000 per year)
    OUTCOMES (LEGAL DUE DILLIGENCE)
    MoH and Korle Bu Teaching Hospital Board should be parties to the PPP arrangement. In the
    event any direct agreement with financiers or guarantees is required,
    such agreements should be entered into by the Ministry of Finance
    and Economic Planning on behalf of the Government of Ghana
    SCENARIOS BASED ON OPTIONS
    Option 1    New Laboratory and Imaging Services on a greenfield site
    Option 2    New Laboratory and Imaging Services utilising
existing Advanced Imaging Centre building
    Option 3    Lab only PPP on a greenfield site
    TOTAL PROJECT COST AND MEANS OF FINANCE
    Project Cost    Option1    Option2    Option3
    Building    $ 7.00    $ 5.50    $ 2.90
    Equipment    $ 16.00    $ 9.80    $ 2.80
    Other Capex (Profit, Insurance etc.)    $ 5.50    $ 3.80    $ 1.70
    Subtotal    $ 28.50    $ 19.10    $ 7.40
    Development Cost    $ 2.40    $ 2.40    $ 2.40
    Cash to DSRA    $ 2.10    $ 1.50    $ 0.70
    IDC    $ 1.40    $ 1.10    $ 0.60
    Other(eg. Tax during constr'c)    $ 2.30    $ 2.30    $ 1.00
    Total Project Cost    $ 36.70    $ 26.40    $ 12.10
    Share Capital    $ 6.90    $ 4.90    $ 2.30
    Shareholders Loan    $ 2.30    $ 1.60    $ 0.80
    Senior Debt    $ 27.50    $ 19.80    $ 9.10
    Tenure of Debt    9    (in yrs.)
    Interest Rates:
    Senior Debt    9.15%
    Shareholders Loan    10%
    ANNUAL VOLUMES OF LAB TEST
    Specialty     2007    2008    2009    2010    2011
    Haematology     95962    122336    143170    111933    123655
    Chemical Pathology     39143    43377    79857    52827    56964
    Microbiology     44537    60085    70678    64128    60365
    Immunology     14923    21024    22249    13742    9995
    Total     194565    246822    315954    242630    250979
    REVENUES OF LAB TEST    2011
    Specialty     Private    NHIS    Total
    Haematology     $ 598,295.00    $ 220,506.00    $ 818,801.00
    Chemical Pathology     $ 709,105.00    $ 22,173.00    $ 731,278.00
    Microbiology     $ 372,089.00    $ 107,401.00    $ 479,490.00
    Immunology     $ 75,091.00    $ 11,534.00    $ 86,625.00
    Total    $ 1,754,580.00    $ 361,614.00    $ 2,116,194.00
    ANNUAL VOLUMES OF RADIOLOGICAL ACTIVITY
    Types of Procedures    2006    2007    2008    2009    2010    2011    2012
    Mammography    626    940    716    768    954    1075    838
    Intravenous Urogram    494    690    492    720    435    408    505
    Ultrasonography    4092    4464    6232    5672    4063    3285    4745
    Hysterosalpinggography    1471    1575    1892    1437    1409    911    1104
    Specials    17    216    279    480    29    16    13
    Chest    13190    11307    12951    11490    5674
    MRI    1546    3092    1435    1660    1428    1430    904
    CT Scan    1597        2240    7150    4610    2966    5548
    GIT        575    558    1166    418    218    259
    X-Rays    13119    15107    13312    16962    10105    38902    22332
    Total    16,262    18,774    17,545    26,938    16,561    43,516    29,043
    REVENUES OF RADIOLOGICAL ACTIVITY
    Types of Procedures    2006    2007    2008    2009    2010    2011    2012
    Mammography    $ 31,300.00    $ 47,000.00    $ 35,800.00    $ 38,400.00    $ 47,700.00    $ 53,750.00    $ 41,900.00
    Intravenous Urogram    $ 34,580.00    $ 48,300.00    $ 34,440.00    $ 50,400.00    $ 30,450.00    $ 28,560.00    $ 35,350.00
    Ultrasonography    $ 65,472.00    $ 71,424.00    $ 99,712.00    $ 90,752.00    $ 65,008.00    $ 52,560.00    $ 75,920.00
    Hysterosalpinggography    $ 80,905.00    $ 86,625.00    $ 104,060.00    $ 79,035.00    $ 77,495.00    $ 50,105.00    $ 60,720.00
    Specials    $ 1,360.00    $ 17,280.00    $ 22,320.00    $ 38,400.00    $ 2,320.00    $ 1,280.00    $ 1,040.00
    Chest    $ 211,040.00    $ 180,912.00    $ 207,216.00    $ 183,840.00    $ 90,784.00    $ - 0    $ - 0
    MRI    $ 742,080.00    $ - 0    $ 688,800.00    $ 796,800.00    $ 685,440.00    $ 686,400.00    $ 433,920.00
    CT Scan    $ 223,580.00    $ 432,880.00    $ 313,600.00    $ 1,001,000.00    $ 645,400.00    $ 415,240.00    $ 776,720.00
    GIT    $ - 0    $ 31,625.00    $ 30,690.00    $ 64,130.00    $ 22,990.00    $ 11,990.00    $ 14,245.00
    X-Rays    $ 223,023.00    $ 256,819.00    $ 226,304.00    $ 288,354.00    $ 171,785.00    $ 661,334.00    $ 379,644.00
    Total    $ 1,613,340.00    $ 1,172,865.00    $ 1,762,942.00    $ 2,631,111.00    $ 1,839,372.00    $ 1,961,219.00    $ 1,819,459.00
    Equipments Available
    Fluoroscopy    1
    MRI    1
    CT Scanner    1
    Mammography    2
    Ultrasound    2
    X-Ray    2
    Total    9
    New Equipments received under Belstar
    General Radiography System    4
    General Radiography System(Safire)    2
    Fluoroscope    1
    Mammogram    1
    Medical Dry Laser    3
    CT Scanner    1
    MRI    1
    Total    13
Assumptions
    ASSUMED INFLATION RATES
    1    0.10%    (Assumed Inflation Rate)
    Y-O-Y OF LAB TEST                    FORECASTED TAKEN FROM CASE STUDY (DUE DILLIGENCE REPORTS)
    Specialty     2008    2009    2010    2011    2012    2013    2014    2015    2016    2017    2018    2019    2020    2021    2022    2023    2024
    Haematology     27.5%    17.0%    -21.8%    10.5%    6.50%    6.50%    6.50%    6.50%    6.50%    15.00%    22.00%    18.00%    15.00%    13.00%    2.00%    2.00%    2.00%
    Chemical Pathology     10.8%    84.1%    -33.8%    7.8%    6.50%    6.50%    6.50%    6.50%    6.50%    15.00%    22.00%    18.00%    15.00%    13.00%    2.00%    2.00%    2.00%
    Microbiology     34.9%    17.6%    -9.3%    -5.9%    6.50%    6.50%    6.50%    6.50%    6.50%    15.00%    22.00%    18.00%    15.00%    13.00%    2.00%    2.00%    2.00%
    Immunology     40.9%    5.8%    -38.2%    -27.3%    6.50%    6.50%    6.50%    6.50%    6.50%    15.00%    22.00%    18.00%    15.00%    13.00%    2.00%    2.00%    2.00%
    ANNUAL VOLUMES OF LAB TEST
    Specialty     2008    2009    2010    2011    2012    2013    2014    2015    2016    2017    2018    2019    2020    2021    2022    2023    2024    2025    2026
    Haematology     122336    143170    111933    123655    131692    140251    149367    159075    169414    194826    237687    280470    322540    364470    371759    379194    386777    386777    386777
    Chemical Pathology     43377    79857    52827    56964    60666    64609    68808    73280    78043    89749    109493    129201    148581    167896    171253    174678    178171    178171    178171
    Microbiology     60085    70678    64128    60365    64288    68466    72916    77655    82702    95107    116030    136915    157452    177920    181478    185107    188809    188809    188809
    Immunology     21024    22249    13742    9995    10644    11335    12071    12855    13690    15743    19206    22663    26062    29450    30039    30639    31251    31251    31251
    REVENUES PER TEST OF LAB TEST        FORECASTED (WILL BE TAKEN AT PAR OF INFLATION RATES)
    Specialty     2011    2012    2013    2014    2015    2016    2017    2018    2019    2020    2021    2022    2023    2024    2025    2026
    Haematology     $ 6.62    $ 6.63    $ 6.63    $ 6.64    $ 6.65    $ 6.65    $ 6.66    $ 6.67    $ 6.67    $ 6.68    $ 6.69    $ 6.69    $ 6.70    $ 6.71    $ 6.71    $ 6.72
    Chemical Pathology     $ 12.84    $ 12.85    $ 12.86    $ 12.88    $ 12.89    $ 12.90    $ 12.91    $ 12.93    $ 12.94    $ 12.95    $ 12.97    $ 12.98    $ 12.99    $ 13.01    $ 13.02    $ 13.03
    Microbiology     $ 7.94    $ 7.95    $ 7.96    $ 7.97    $ 7.97    $ 7.98    $ 7.99    $ 8.00    $ 8.01    $ 8.01    $ 8.02    $ 8.03    $ 8.04    $ 8.05    $ 8.06    $ 8.06
    Immunology     $ 8.67    $ 8.68    $ 8.68    $ 8.69    $ 8.70    $ 8.71    $ 8.72    $ 8.73    $ 8.74    $ 8.75    $ 8.75    $ 8.76    $ 8.77    $ 8.78    $ 8.79    $ 8.80
    Volumes    2017    2021    2024
    Mammography    990    1,429    1,516
    Intravenous Urogram    724    1,046    1,110
    Ultrasonography    15,773    24,641    27,750
    Hysterosalpinggography    2,107    3,041    3,227
    Specials    19    27    28
    Chest    3,975    6,457    7,488
    MRI    7,419    12,051    13,975
    CT Scan    985    1,421    1,508
    GIT    32,035    46,234    49,064
    Sub-Total    64,027    96,347    105,666
    X-Rays    563    609    647
    Total    64,590    96,956    106,313
    Y-O-Y OF RADIOLOGICAL TEST
    Types of Procedures        2012    2013    2014    2015    2016    2017    2018    2019    2020    2021    2022    2023    2024
    Mammography        6.50%    6.50%    6.50%    6.50%    6.50%    29.00%    29.00%    11.00%    10.00%    9.00%    2.50%    2.50%    2.50%
    Intravenous Urogram        6.50%    6.50%    6.50%    6.50%    6.50%    29.00%    29.00%    11.00%    10.00%    9.00%    2.50%    2.50%    2.50%
    Ultrasonography        6.50%    6.50%    6.50%    6.50%    6.50%    29.00%    29.00%    11.00%    10.00%    9.00%    2.50%    2.50%    2.50%
    Hysterosalpinggography        6.50%    6.50%    6.50%    6.50%    6.50%    29.00%    29.00%    11.00%    10.00%    9.00%    2.50%    2.50%    2.50%
    Specials        6.50%    6.50%    6.50%    6.50%    6.50%    29.00%    29.00%    11.00%    10.00%    9.00%    2.50%    2.50%    2.50%
    Chest        6.50%    6.50%    6.50%    6.50%    6.50%    29.00%    29.00%    11.00%    10.00%    9.00%    2.50%    2.50%    2.50%
    MRI        6.50%    6.50%    6.50%    6.50%    6.50%    29.00%    29.00%    11.00%    10.00%    9.00%    2.50%    2.50%    2.50%
    CT Scan        6.50%    6.50%    6.50%    6.50%    6.50%    29.00%    29.00%    11.00%    10.00%    9.00%    2.50%    2.50%    2.50%
    GIT        6.50%    6.50%    6.50%    6.50%    6.50%    29.00%    29.00%    11.00%    10.00%    9.00%    2.50%    2.50%    2.50%
    X-Rays        6.50%    6.50%    6.50%    6.50%    6.50%    29.00%    29.00%    11.00%    10.00%    9.00%    2.50%    2.50%    2.50%
    ANNUAL VOLUMES OF RADIOLOGICAL TEST
                                                                Extrapolated (Assumed)
    Types of Procedures    2011    2012    2013    2014    2015    2016    2017    2018    2019    2020    2021    2022    2023    2024    2025    2026
    Mammography    1075    1,145    1,219    1,299    1,383    1,473    1,900    2,451    2,721    2,993    3,262    3,344    3,427    3,513    3,513    3,513
    Intravenous Urogram    408    435    463    493    525    559    721    930    1,033    1,136    1,238    1,269    1,301    1,333    1,333    1,333
    Ultrasonography    3285    3,499    3,726    3,968    4,226    4,501    5,806    7,490    8,314    9,145    9,968    10,217    10,473    10,734    10,734    10,734
    Hysterosalpinggography    911    970    1,033    1,100    1,172    1,248    1,610    2,077    2,306    2,536    2,764    2,833    2,904    2,977    2,977    2,977
    Specials    16    17    18    19    21    22    28    36    40    45    49    50    51    52    52    52
    Chest    10922.4    11,632    12,388    13,194    14,051    14,965    19,304    24,903    27,642    30,406    33,143    33,971    34,821    35,691    35,691    35,691
    MRI    1430    1,523    1,622    1,727    1,840    1,959    2,527    3,260    3,619    3,981    4,339    4,448    4,559    4,673    4,673    4,673    (Base year has been taken as interpolated average)
    CT Scan    2966    3,159    3,364    3,583    3,816    4,064    5,242    6,762    7,506    8,257    9,000    9,225    9,456    9,692    9,692    9,692
    GIT    218    232    247    263    280    299    385    497    552    607    661    678    695    712    712    712
    X-Rays    38902    41,431    44,124    46,992    50,046    53,299    68,756    88,695    98,452    108,297    118,043    120,994    124,019    127,120    127,120    127,120
    REVENUES PER RADIOLOGICAL ACTIVITY
                                    FORECASTED (WILL BE TAKEN AT PAR OF INFLATION RATES)
    Types of Procedures    2006    2007    2008    2009    2010    2011    2012    2013    2014    2015    2016    2017    2018    2019    2020    2021    2022    2023    2024    2025    2026
    Mammography    $ 50.00    $ 50.00    $ 50.00    $ 50.00    $ 50.00    $ 50.00    $ 50.00    $ 50.05    $ 50.10    $ 50.15    $ 50.20    $ 50.25    $ 50.30    $ 50.35    $ 50.40    $ 50.45    $ 50.50    $ 50.55    $ 50.60    $ 50.65    $ 50.70
    Intravenous Urogram    $ 70.00    $ 70.00    $ 70.00    $ 70.00    $ 70.00    $ 70.00    $ 70.00    $ 70.07    $ 70.14    $ 70.21    $ 70.28    $ 70.35    $ 70.42    $ 70.49    $ 70.56    $ 70.63    $ 70.70    $ 70.77    $ 70.84    $ 70.92    $ 70.99
    Ultrasonography    $ 16.00    $ 16.00    $ 16.00    $ 16.00    $ 16.00    $ 16.00    $ 16.00    $ 16.02    $ 16.03    $ 16.05    $ 16.06    $ 16.08    $ 16.10    $ 16.11    $ 16.13    $ 16.14    $ 16.16    $ 16.18    $ 16.19    $ 16.21    $ 16.23
    Hysterosalpinggography    $ 55.00    $ 55.00    $ 55.00    $ 55.00    $ 55.00    $ 55.00    $ 55.00    $ 55.05    $ 55.11    $ 55.17    $ 55.22    $ 55.28    $ 55.33    $ 55.39    $ 55.44    $ 55.50    $ 55.55    $ 55.61    $ 55.66    $ 55.72    $ 55.78
    Specials    $ 80.00    $ 80.00    $ 80.00    $ 80.00    $ 80.00    $ 80.00    $ 80.00    $ 80.08    $ 80.16    $ 80.24    $ 80.32    $ 80.40    $ 80.48    $ 80.56    $ 80.64    $ 80.72    $ 80.80    $ 80.88    $ 80.97    $ 81.05    $ 81.13
    Chest    $ 16.00    $ 16.00    $ 16.00    $ 16.00    $ 16.00    $ - 0    $ - 0    $ 16.02    $ 16.03    $ 16.05    $ 16.06    $ 16.08    $ 16.10    $ 16.11    $ 16.13    $ 16.14    $ 16.16    $ 16.18    $ 16.19    $ 16.21    $ 16.23
    MRI    $ 480.00    $ - 0    $ 480.00    $ 480.00    $ 480.00    $ 480.00    $ 480.00    $ 480.48    $ 480.96    $ 481.44    $ 481.92    $ 482.40    $ 482.89    $ 483.37    $ 483.85    $ 484.34    $ 484.82    $ 485.31    $ 485.79    $ 486.28    $ 486.76
    CT Scan    $ 140.00    $ - 0    $ 140.00    $ 140.00    $ 140.00    $ 140.00    $ 140.00    $ 140.14    $ 140.28    $ 140.42    $ 140.56    $ 140.70    $ 140.84    $ 140.98    $ 141.12    $ 141.27    $ 141.41    $ 141.55    $ 141.69    $ 141.83    $ 141.97
    GIT    $ - 0    $ 55.00    $ 55.00    $ 55.00    $ 55.00    $ 55.00    $ 55.00    $ 55.05    $ 55.11    $ 55.17    $ 55.22    $ 55.28    $ 55.33    $ 55.39    $ 55.44    $ 55.50    $ 55.55    $ 55.61    $ 55.66    $ 55.72    $ 55.78
    X-Rays    $ 17.00    $ 17.00    $ 17.00    $ 17.00    $ 17.00    $ 17.00    $ 17.00    $ 17.02    $ 17.03    $ 17.05    $ 17.07    $ 17.09    $ 17.10    $ 17.12    $ 17.14    $ 17.15    $ 17.17    $ 17.19    $ 17.21    $ 17.22    $ 17.24
Gantt Chart
    Korle-Bu Diagnostic Centre PPP Project
    Sl. No.    Activity    Start in Month    Time required in month(s)    Months
                    1    2    3    4    5    6    7    8    9    10    11    12    13    14    15    16    17    18    19    20    21    22    23    24    25    26    27    28    29    30    31    32    33    34    35    36    37    38    39    40    41    42    43    44    45    46    47    48    49    50    51    52    53    54    55    56    57    58    59    60    61    62    63    64    65    66    67    68    69    70    71    72    73    74    75    76    77    78    79    80    81    82    83    84    85    86    87    88    89    90    91    92    93    94    95    96    97    98    99    100    101    102    103    104    105    106    107    108    109    110    111    112    113    114    115    116    117    118    119    120    121    122    123    124    125    126    127    128    129    130    131    132    133    134    135    136    137    138    139    140    141    142    143    144
    1    Workshop with GoG to discuss
Feasibility Study (completes Phase 1)    0    1    8    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
    2    Approval of Feasibility Study    1    1    8    8    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
    3    Prequalification process from RFQ    2    1    0    8    8    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
    4    Bid Submission Date    3    3    0    0    8    8    8    8    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
    5    GoG/IFC evaluation of bids, prepare Evaluation Report    6    1    0    0    0    0    0    8    8    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
    6    Preferred Bidder approved by GoG O    7    1    0    0    0    0    0    0    8    8    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
    7    Contract Signature: approval of the
final PPP agreement    8    2    0    0    0    0    0    0    0    8    8    8    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
    8    Receiving of Capital Equipments under Belstar    10    2    0    0    0    0    0    0    0    0    0    8    8    8    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
    9     Construction Work    8    24    0    0    0    0    0    0    0    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
    10     Construction Completion and transfer of grant    32    1    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    8    8    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
    11    Operations & Revenue Cycle    33    111    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8    8
    12    Maintenance & Handling    48    12    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    8    8    8    8    8    8    8    8    8    8    8    8    8    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
Cash Flows
    Korle-Bu Diagnostic Centre PPP Project
    1    Project Details
    A    Project Name    Korle-Bu Diagnostic Centre
    B    Nature    Lab and Imaging Centre Revamp
& Expansion
    C    Location    Ghana, Affrica
    D    Revenue Driver    Lab Test Charges, Insurances commission
    E    Model    PPP
    F    Stage    Construction
    2    Key Dates
    A    Project Normal Start Date    1/1/15
    B    Operational date    1/1/17
    C    Exit date    12/31/26
    D    Concession period (months)    144
    3    Reporting unit
    A    Million (mn)    1,000,000
    4    Design Parameter
    A    Equipment Charges    Scenario Based (from Inputs)
    B    Building Charges    Scenario Based (from Inputs)
    C    Other Capex (Profit, Insurance etc.)    Scenario Based (from Inputs)
    D    Development Cost    Scenario Based (from Inputs)                Cost Build Up
    E    Cash to DSRA    Scenario Based (from Inputs)
    F    IDC    Scenario Based (from Inputs)
    G    Other(eg. Tax during constr'c)    Scenario Based (from Inputs)
    H    Private Funding     82.9%    Based on share contribution
    I    NHIS    17.1%    Based on share contribution
    J    Hours / day    24    Nos.
    K    Days / month    30    Nos.            Revenue Drivers
    L    Designed annual traffic handling capacity    Table based
    M    Max traffic    Table based
    5    Project Commercials
    A    Annual 1 test charge    Table based
    B    Annual growth rate in test charge    Table based (inflation adj.)
    C    Steady state test charges    Table based
    D    Government Grant    Table based
    E    Government Grant in year    May 1,2013
    F    Exit value    (To be Quoted)
    6    Project Scenarios
    (in millions USD)
    Project Cost    Option1    Option2    Option3
    Building    $ 7.00    $ 5.50    $ 2.90
    Equipment    $ 16.00    $ 9.80    $ 2.80
    Other Capex (Profit, Insurance etc.)    $ 5.50    $ 3.80    $ 1.70
    Subtotal    $ 28.50    $ 19.10    $ 7.40
    Development Cost    $ 2.40    $ 2.40    $ 2.40
    Cash to DSRA    $ 2.10    $ 1.50    $ 0.70
    IDC    $ 1.40    $ 1.10    $ 0.60
    Other(eg. Tax during constr'c)    $ 2.30    $ 2.30    $ 1.00
    Total Project Cost    $ 36.70    $ 26.40    $ 12.10
    7    Construction & Commissioning Charges
    CASH FLOW FROM INVESTING
    Month after approval    0    1    2    3    4    5    6    7    8    9    10    11    12    13    14    15    16    17    18    19    20    21    22    23
    Period    Jan-15    Feb-15    Mar-15    Apr-15    May-15    Jun-15    Jul-15    Aug-15    Sep-15    Oct-15    Nov-15    Dec-15    Jan-16    Feb-16    Mar-16    Apr-16    May-16    Jun-16    Jul-16    Aug-16    Sep-16    Oct-16    Nov-16    Dec-16
    Option 1 (Complete Greenfield)    $ 1.53    $ 1.53    $ 1.53    $ 1.53    $ 1.53    $ 1.53    $ 1.53    $ 1.53    $ 1.53    $ 1.53    $ 1.53    $ 1.53    $ 1.53    $ 1.53    $ 1.53    $ 1.53    $ 1.53    $ 1.53    $ 1.53    $ 1.53    $ 1.53    $ 1.53    $ 1.53    $ 1.53
    Option 2 (Building & Eqp Lease
Under Belstar)    $ 1.10    $ 1.10    $ 1.10    $ 1.10    $ 1.10    $ 1.10    $ 1.10    $ 1.10    $ 1.10    $ 1.10    $ 1.10    $ 1.10    $ 1.10    $ 1.10    $ 1.10    $ 1.10    $ 1.10    $ 1.10    $ 1.10    $ 1.10    $ 1.10    $ 1.10    $ 1.10    $ 1.10
    Option 3 (Only Lab)    $ 0.50    $ 0.50    $ 0.50    $ 0.50    $ 0.50    $ 0.50    $ 0.50    $ 0.50    $ 0.50    $ 0.50    $ 0.50    $ 0.50    $ 0.50    $ 0.50    $ 0.50    $ 0.50    $ 0.50    $ 0.50    $ 0.50    $ 0.50    $ 0.50    $ 0.50    $ 0.50    $ 0.50
    CASH FLOW FROM OPERATIONS
    8    Scenario specific expenditure and expenses
    Sl. No.    Execution & Operation Scenario    Start Date    Construc & Operations Period    Manpower Expenses    Maintenance Expenses    Billing & Other Expenses
                months    % of revenue    % of revenue    % of revenue
    1    Option 1 (Complete Greenfield)    Jan-15    144    15%    15%    15%    (Assumed)
    2    Option 2 (Building & Eqp Lease
Under Belstar)    Jan-15    144    12%    12%    12%    (Assumed)
    3    Option 3 (Only Lab)    Jan-15    144    10%    10%    10%    (Assumed)
    9    Scenario independent normalized expenses
    A    Power Expenses    $ mn / month    0.25
    B    Insurance Expenses    $ mn / month    0.10
    CASH FLOW FROM FINANCING
    10    Interest Expense (Taken from Debt Schedule)
    Month after approval
    Period    0    1    2    3    4    5    6    7    8    9    10    11    12    13    14    15    16    17    18    19    20    21    22    23    24    25    26    27    28    29    30    31    32    33    34    35    36    37    38    39    40    41    42    43    44    45    46    47    48    49    50    51    52    53    54    55    56    57    58    59    60    61    62    63    64    65    66    67    68    69    70    71    72    73    74    75    76    77    78    79    80    81    82    83    84    85    86    87    88    89    90    91    92    93    94    95    96    97    98    99    100    101    102    103    104    105    106    107
    Option 1 (Complete Greenfield)    Jan-15    Feb-15    Mar-15    Apr-15    May-15    Jun-15    Jul-15    Aug-15    Sep-15    Oct-15    Nov-15    Dec-15    Jan-16    Feb-16    Mar-16    Apr-16    May-16    Jun-16    Jul-16    Aug-16    Sep-16    Oct-16    Nov-16    Dec-16    Jan-17    Feb-17    Mar-17    Apr-17    May-17    Jun-17    Jul-17    Aug-17    Sep-17    Oct-17    Nov-17    Dec-17    Jan-18    Feb-18    Mar-18    Apr-18    May-18    Jun-18    Jul-18    Aug-18    Sep-18    Oct-18    Nov-18    Dec-18    Jan-19    Feb-19    Mar-19    Apr-19    May-19    Jun-19    Jul-19    Aug-19    Sep-19    Oct-19    Nov-19    Dec-19    Jan-20    Feb-20    Mar-20    Apr-20    May-20    Jun-20    Jul-20    Aug-20    Sep-20    Oct-20    Nov-20    Dec-20    Jan-21    Feb-21    Mar-21    Apr-21    May-21    Jun-21    Jul-21    Aug-21    Sep-21    Oct-21    Nov-21    Dec-21    Jan-22    Feb-22    Mar-22    Apr-22    May-22    Jun-22    Jul-22    Aug-22    Sep-22    Oct-22    Nov-22    Dec-22    Jan-23    Feb-23    Mar-23    Apr-23    May-23    Jun-23    Jul-23    Aug-23    Sep-23    Oct-23    Nov-23    Dec-23
    Shareholders Loan    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032    0.032
    Senior...
SOLUTION.PDF
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions ยป

Submit New Assignment

Copy and Paste Your Assignment Here
Have files?