Recent Question/Assignment

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 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 investments,
• 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 25.
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
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
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 non-paying 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 operations.
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
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 systems.
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 loan.
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
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 Analysis”.
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 rate.
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.

Looking for answers ?

Recent Questions

Assignment 2BFA504 Semester 1, 2021Data Analytics and Data VisualizationAssignment Type: IndividualWeighting: 20%Due Date: 10th of May (Monday: 11:59 PM)Submission: Upload files on to MyLO. Click here...• Determine the level of materiality to be used for the audit of the group accounts for the year ending in 2020. Your answer should include a discussion of the nature of materiality, and a description...Assessment Details and Submission GuidelinesAssessment Type: Individual Assignment % of total: 25%Assessment Date: End of Week 07 Learning Outcomes: 1,2,3 & 4Summative: Yes Duration/Word Length: 1500...Assessment 4 Marking Rubric: Ethical Concepts Part 2 MC Quiz - Value 5%Criteria Fail Pass Credit Distinction High Distinction Weighting% (0 - 49%) (50 - 64%) (65 - 74%) (75 - 84%) (85-100%)Number of correct...EXERCISE 4To be completed by the TraineeYou must include this page with your Exercise 4 submissionAlternatively you can retype the details as a front page to your ExerciseTrainee name _________________________________________________________________________Trainee...EXERCISE 3To be completed by the TraineeYou must include this page with your Exercise 3 submissionAlternatively you can retype the details as a front page to your ExerciseTrainee name _________________________________________________________________________Trainee...EXERCISE 2To be completed by the TraineeYou must include this page with your Exercise 2 submissionAlternatively you can retype the details as a front page to your ExerciseTrainee name _________________________________________________________________________Trainee...Show All Questions