there are two files one word with every thing you need the other is excel to do calculation and depends on the
output result write a report
________________________________________Major Case
1. Background
1.1. Introduction
In the early years, investment in Samz’s water supply and sanitation sector centered on expanding the system
capacity to meet the growing demand. However, since the facilities were constructed, maintenance was
neither fully performed nor properly programmed. As a result, the water supply and sanitation systems were
functioning under serious constraints.
1.2. Problem to be Addressed
At the present, the water supply systems in some regions of Samz, like Ayumba, Chumba, Comba and Samz
City are in a state of collapse due to insufficient maintenance of facilities, ineffectual coordination to protect
water resources and receiving bodies, ineffective metering and low account collection. The water services are
characterized by a low coverage of household connections and high rates of water of water losses (technical
24/01/2021 62915 – there are two files one word with every thing you need 3/8
and commercial).
To remedy these shortcomings, the Samzian government, with the support of the Development Bank (DB), is
proposing to finance and privatize the Agency of Water Supply and Sanitation Services (AWSS), the public
utility responsible for providing water supply and sanitation services. The project aims to undertake an
intervention in order to provide sustainable and efficient water supply services in Samz, as well as to meet the
current and future demand for water.
You have been instructed to carry out the required analysis of this project to assess the impact of the proposed
DEAL of the Water Supply Project. Lessons need to be drawn from this case to determine if this is a good
project to be implemented.
2. Project Description
2.1. Project Objective
The Samzian government, with the support of the Development Bank (DB), launched the Public Enterprise
Reform Program for the water supply and sewerage sector.
The program’s main aims are to
• Support the restructuring of AWSS and involve the private sector in managing and in funding future
• Rehabilitate and optimize the water supply systems,
• Provide technical cooperation.
To achieve these objectives, the program has been divided into three subprograms:
I. Subprogram 1: Restructuring of Public Utility.
This subprogram includes restructuring and privatization of AWSS. Establishment of Samz Metropolitan
Corporation (SMC), fully owned by private investors. Reduction of AWSS’s excess staffing, provision of
adequate training to the remaining staff and finally, to help those made redundant with worker outplacement
services, and bring in a strategic operator from the private sector.
II. Subprogram 2: Rehabilitation Works.
This subprogram consists of rehabilitation works on the systems supplying water to Ayumba, Chumba,
Comba, and Samz City. On the technical and operational side, it entails upgrading the distribution networks
and developing geographic information systems, technical records, system metering, and operating and
control units for the entire Metropolitan Samz area. On the commercial side, it involves upgrading or
developing customer records, end-user metering, flow measurement, and detecting and reducing water losses
in each of the four targeted systems (Samz City and Others ).
III. Additional Activities.
This subprogram includes improving inter-institutional coordination and the authority responsible for
protecting water resources and the water quality of receiving bodies, and developing feasibility studies and
updating the master plan for Samz City’s sewerage system.
3. Project Assumptions
3.1. Project Implementation Timeline
The project commences at the beginning of Year 1, with the construction phase of the project. The
construction period is four years and ceases at the end of Year 4. Operations commence at the beginning of
Year 5 and cease in Year 24, making up a total operating period of 20 years. Project liquidation occurs in Year
3.2. Capital Expenditure
Equipment: All equipment are imported. Imports are subject to a 15% import duty on the CIF price. The
prices of all imports, including equipment, are expected to rise at the rate of foreign inflation. Total capital
expenditure on equipment will be incurred in the period from Year 1 to Year 4.
Establishment and Support Services: The total capital expenditure on establishment and support services
amounts to USD 3.53 million. The cost of establishment and support services includes the cost of drilling,
digging and other earthworks, installation of pipes, repair of asphalt surfaces, construction works and water
use evaluations.
The table below shows the breakdown of the total Capital Expenditure (CAPEX)
In Millions of USD (MIL’$)
Year 1 2 3 4
CIF Cost of Equipment 4.34 8.22 10.80 3.19
24/01/2021 62915 – there are two files one word with every thing you need 4/8
Establishment & Support Services 1.05 1.32 1.05 0.11
3.3. Project Financing
The Development Bank (DB) will provide loan financing for 70 percent of the nominal capital expenditure in
each year, while the remaining financing (30%) will come from investor equity contributions.
The term of DB’s loan facility are as follows:
• A real rate of interest of 4.9%
• A grace period for principal payments last until the end of Year 4
• The loan principal is paid in eleven equal annual installments, starting from year 5.
• The interest expense that is accrued in Year 1 will be paid in Year 2 and interest expense that is accrued in
Year 2 will be paid in Year 3 etc.
3.4. Technical & Revenue Assumptions
i. Number of connections, Access Rates and Consumer Groups
The project aims at increasing the percentage of residential connections with meters from the current level of
54 percent of all households to 91 percent in Year 5 onward. These metered households will now enjoy a 24-
hour water supply service.
Table 2 shows the projected total annual water connections (including both metered and unmetered) in Samz
of different categories expressed in thousands of connections.
(Note: From Year 8 till the end of the project the number of water connections remain constant).
Table 2: Projected Total Annual Water Connections (000’Connections)
YEAR 1 2 3 4 5 6 7 8
Residential Connections
Samz City & Others
Other Connections
Commercial &Industrial
Public Institution 0.51 0.52 0.53 0.54 0.55 0.56 0.57 0.58
ii. Water Demand and Consumption
The annual water consumption per connection in ‘with’ and ‘without’ the project scenario is expressed in the
table below (Table 3) with their respective own-price elasticities of demand for water.
Table 3: Annual Water Consumption/Connection
Category Without the Project
(m3/000’connections) With the Project
Residential metered
Residential unmetered
24/01/2021 62915 – there are two files one word with every thing you need 5/8
Commercial & Industrial
Public Institution
iii. Water Tariffs
Without the Project
Unmetered residential customers pay, on average, a flat-fee of LC 5.00 per month. Metered residential
customers pay a weighted average volumetric tariff LC 0.85 per m3. Commercial & Industrial customers pay
LC 1.16 per m3, and Public Institution customers pay LC 1.05 per m3. The real average rate charged to nonpaying users before the project is LC 0.78 per m3. These are all in real values.
With the Project
With the project, the real water tariffs are proposed to increase by 10% starting in Year 5.
In both the with and without the project case, it is assumed that the water tariffs will be adjusted annually
with the domestic inflation rate.
iv. Bill Collections
Without the project
80% of the total number of water bills issued to all types of consumers per year only are paid.
With the project
With the project starting in Year 5, 90 percent of the bills sent to all water customers will be paid.
v. Water Losses and Non-Revenue Water
The current distribution network in Metropolitan Samz experiences a substantial water leakage.
Without the project
Non – revenue water in without the project case is comprised of 10% physical losses and 20% commercial
losses (water bills not paid).
With the project
With the project the quantity of non-revenue water is expected to reduce physical losses to 5% 10% for
commercial losses.
3.5. Operating and Maintenance Costs
The system incurs personnel, electricity, chemical, material and market and administration costs for daily
Without the project
• Personnel and materials expense amount to 66,000 and 19,000 LC per ‘000 connections per year.
• Electricity and chemicals require 0.32 and 0.23 LC per m3 produced.
• Marketing and administration costs are 4,950,000 LC per year.
Electricity, Chemical and Materials are subject to a VAT of 18%.
Personnel, marketing and administration (labor cost) expenses are expected to increase by 1% per annum real
terms for the foreseeable future.
With the project
Once the upgrades are implemented (with project), these costs are expected to reduce for personnel by 10
percent, marketing and administration by 15 percent.
Chemical and material costs remain unchanged.
Note: Electricity cost is per m3 of water produced
3.6. Working Capital
Accounts receivable are expected to amount to approximately 20% and 10% (both from metered and
unmetered customers) in the without and with project scenario respectively.
Accounts payable are expected to amount 16% of operating expenses, excluding the labor expenses.
The desired level of cash reserve equals to 8% total operating expenses, including labor expenses.
3.7. Taxation
The utility pays zero income tax in the “without project” scenario. It is assumed that the project will be
subject to corporate income tax. The tax law allows a full deduction of the operating expenditures, interest
expense and depreciation allowances. The rate of corporate income tax is 25% of taxable income.
3.8. Residual Values
24/01/2021 62915 – there are two files one word with every thing you need 6/8
The straight-line depreciation method is used in determining the economic depreciation of the project’s assets
as well as their residual values. The economic life of the project’s equipment (which includes establishment
and support services) is assumed to be 25 years, assuming no major capital replacements for the duration of
the project.
3.9. Tax Depreciation
All of the project’s equipment is to be depreciated for tax purposes using the straight-line method. For tax
purposes, the project’s equipment (which includes establishment and support services) is assumed to have a
useful life of 20 years. The depreciation allowances are calculated on the basis of the sum of the historical
cost of the assets for the years during which the capital expenditure was incurred.
Interest during contrition (IDC) is assumed to have a useful life of 4 years.
3.10. Macroeconomics Parameters
i. Inflation and Exchange Rates
The domestic inflation rate (Samz) is projected at 3% and foreign inflation (USA) at 2%.
The real exchange rate of 1.5 LC per USD (in Year 1 prices) is assumed to remain constant during the life of
the project. The projected nominal exchange rates in the following years is the real exchange rate adjusted by
the relative price index between the domestic and foreign currency.
ii. Discount Rate
The real financial discount rate for equity is 12%.
3.11. Model Mechanics
Use of Flag
Flags are time-dependent variables that can hold a value of 1 or 0. The model uses flags inside a conditional
formula (such as an “if” statement) to allow for conditional calculations. If you want a result delivered (e.g.
because revenues are being generated or expenses are being incurred in that year) the flag will be “1”. If no
result should be delivered, the flag value will be “0”. Flags are used to provide flexibility in the model, so
you can easily multiply any element of the financial model by the flag to either deliver a result or not.
4. Assignment I: Financial Analysis
4.1. Objective
The financial analysis is the first component of the integrated analysis of this project. The principal focus of
this analysis is to see whether the deal is financially feasible and what the benefits would be for private
investors. (The “deal” consists of both taking over AWSS and implementing the project.)
4.2. Approach
i. Incremental Analysis
The model uses the incremental analysis approach, where the incremental impact is measured of the project
that is over and above what would have occurred in the absence of the project. This is achieved by comparing
the “with” and “without” project scenarios. The difference would then measure the incremental contribution
of the project.
ii. Cash flow Statement from Total Investment Perspective “without project.”
This cash flow statement is prepared from the “without project situation,” where it is assumed that the AWSS
will continue to fully operate the water supply and distribution network, no rehabilitation of water supply
i. Cash flow Statement from Total Investment Perspective “with project.”
This cash flow statement is prepared from the perspectives of the Project “DEAL” (Private Operator). It
looks at the revenues and costs of the rehabilitation and expansion project (“with the project” scenario) and
addresses the question of whether the cash flows generated by the project are large enough to recover the
capital and operating costs of the project, as well as generate an acceptable financial rate of return.
It also examines the Private Operator’s ability to pay back the “potential” loan.
This statement forms the basis for the computation of the Debt Service Coverage Ratios for the potential
ii. Cash flow Statement from Total Investment Perspective “Incremental”
Finally, the incremental cash flow statement for Utility is derived by subtracting the without project scenario
from with project scenario. The incremental cash flow statement provides the basis for calculating financial
returns (FNPV and FIRR) for the Utility and determines whether restructuring the water and sewer utility in
Samz is financially justified for the Public Utility.
4.3. Questions
24/01/2021 62915 – there are two files one word with every thing you need 7/8
i. Fill the table of parameters in the “Inputs” sheet.
ii. Complete the calculation tables in “Calculations” sheet.
iii. Complete the following cash flow statements and calculations in “Fin” sheet:
– Cash Flow Statement from Total Investment Point of View – Without Project (Nominal)
– Cash Flow Statement from Total Investment Point of View – With Project (Nominal).
– Incremental Cash Flow Statement from Total Investment Point of View (Nominal)
iv. Estimate Annual Debt Service Coverage Ratios (ADSCR) for each year of scheduled debt repayment in
“Financial Analysis”
v. Estimate Loan Life Coverage Ratio (LLCR) for each year of scheduled debt repayment in “Financial
vi. Calculate NPV, IRR and MIRR of the proposed “DEAL” water supply project for the Private Operator,
using 12% real discount rate.
vii. Based on the financial results of the project, would the Private Operator be interested to participate in the
financing and operations of the project? Explain your answer.
viii. Calculate the NPV, IRR and MIRR of the incremental cash flows for the Utility, using 12% real discount
ix. Based on the financial results to the Utility, explain whether this is a good investment for the Utility.
Should the government go ahead in privatizing AWSS rather than letting the Public Utility continue to
manage the water supply operations in Samz city?
x. Based on the ratios, ADSCR and LLCR, does the project generate enough cash flows to service its debt
obligations? Should the DBA be willing to lend money to the project? If privatized or not.



15% off for this assignment.

Our Prices Start at $11.99. As Our First Client, Use Coupon Code GET15 to claim 15% Discount This Month!!

Why US?

100% Confidentiality

Information about customers is confidential and never disclosed to third parties.

Timely Delivery

No missed deadlines – 97% of assignments are completed in time.

Original Writing

We complete all papers from scratch. You can get a plagiarism report.

Money Back

If you are convinced that our writer has not followed your requirements, feel free to ask for a refund.