Economic analysis of a residential project before and after the revolution final

AENG 345: Engineering Economy Dr. Ahmed Tolba

Economic analysis of a Residential Project Before and After the Revolution Fall ‘12

Ahmed Samir Amira Badran Layla Sabri Moaz Mohamed Ziyad Hamdy

Table of Contents Abstract……………………………………………………………………………………………………..2 Introduction……………………………………………………………………………….……………….3 Objective……………………………………..…………………………………….........................3 Economic situation before & after the revolution………………………………………........4 Project Information……………………………………………………………….........................7 Investment Cost List……………………………………………………………………………………...7 Cash Flow Diagram………………………………………………………………........................7, 10 Detailed...………………………………………………………………….……………….8, 11 Net…….…………………………………………………………………….........................9, 12 Economic worth analysis……………………………………………………………..…………………..13 Ranking Analysis………………………………………………………………..…………………14 Present worth…………………………………………………………….………………..15 Future Worth……………………………………………………………...……………….15 Annual Worth……………………………………………………………..........................15 Discounted Pay Back Period…………………………………………………………….15 Incremental Analysis…………………………………………………………….……………….18 External Rate of Return…………………………………………………………………..18 Internal Rate of Return…………………………………………………………………..19 Supplementary Analysis…………………………………………………………………………20 Breakeven…………………………………………………………………………………..21 Sensitivity………………………………………………………………………………….25 Risk Analysis………………………………………………………………........................28 Conclusions and Recommendations…………………………………………………………...30 Appendix…………………………………………………………………………………………...32 1

Abstract

Revolutions throughout history have proven to be one of the most important factors affecting financial standings of countries. The Arab spring revolutions are definitely not excluded, the Egyptian 25th of January Revolution specifically has led to a number of changes in the economic arena, from delaying to depreciating economic values of assets. Comparing between the financial standing before and after the revolution, this paper takes a residential project as a sample medium to analyze the current and previous economic situation of the country. The data collected include the cash inflows and outflows of the investments and through various types of economic worth analysis and comparisons between both sides, we reached a conclusion on the extent of the effect of the revolution on the Egyptian economic worth and what kind of measure could be taken early on by investors to avoid damage.

2

Objective

•

Analyze and measure the effect of the up rise on the economy

•

Compare between before and after the revolution profits and losses

•

Data for a real residential development collected as a sample for measurement

•

Measuring economic worth through several types of analysis; ranking, incremental, and supplementary

•

Suggesting a number of recommendations for investors to follow in order to minimize loss

3

Economy before & after the revolution: Through the economic analysis of an existing residential project, we aim to study and evaluate the economic consequences and effects of the political situation in Egypt. Thus, the 2010-11 revolution was the best case study that could help us measure the extent of the effect of politics on the economy of Egypt.

In fact, a study done by the Central Bank of Egypt shows that the revolution has affected many sectors in the Egyptian market: 1- GDP is the gross domestic product, and is defined by “the market value of all the goods and services produced by labor and property located in the region, usually a country�. As seen in the charts below, the GDP growth in 2011 post revolution in Egypt has dramatically decreased in the years 2010/2011, during and after the revolution, going from 5.1% to 1.4% annually. In fact, the main sectors contributing in the GDP growth (in this case the GDP decline) are the manufacturing and the tourism sectors (Fig.1 & Fig.3)

4

2- The unemployment rate was also dramatically affected by the revolution. As seen in the chart below, the unemployment rate among Egyptians reached 11.8% at the end of the fiscal year 2010/2011 up from 9% at the end of the previous year. This was mainly due to the fact that neighboring countries also experienced a revolution at a similar time, and workers returning from Libya for example joined the unemployed (270,000 settlers) greatly contributed in shooting the unemployed numbers to 3.11 million Egyptians up from 2.35 million in the previous year.

3- The effect on the currency exchange was also significant: the value of the Egyptian pound has continued to decrease in front of foreign currencies generally such as the Euro, US dollars, Arabian currencies, etc‌

5

4- The external debt in Egypt has also greatly increased during the fiscal year 2010/11 as seen in the chart below, reaching around 34.9 billion US dollars, witnessing an increase of 3.6% compared to the end of the last fiscal year (Fig. 10)

6

Project Information: A residential development built on 80 feddans (336,000 sqm) with a total allowable FAR of 35% and a maximum sellable land of 50%. The development boasts 300 upper middle class standalone villas with an average BUA of 400 sqm and land of 600 sqm per unit.

Investment Cost List: Pre-Revolution 1) Revenue Assumptions: a. Total sales of some EGP 1 billion sold in 3 years equally (some 313 million annually) b. Client pays the unit over 5 years (schedule in excel) 2) Costing Assumptions: a. Land: EGP 600 per sqm of gross land (paid in year 1) b. Construction and infra: EGP 301 million paid over 3 years (1850 per sqm of BUA for construction, 250 per sqm of land for infra) c. Design: 1.2% of infra and construction cost paid in year 1 d. Sales commission of 1% payable upon sales (over 3 years) e. Marketing: 1% of total sales payable upon sales (over 3 years)

7

Cash flow: Pre-Revolution Detailed Cash flow:

Net Cash Flow: 8

Post-Revolution 9

1) Revenue Assumptions: a. Total sales of some EGP 1 billion sold in year 1, year 2 and year 3 with no sales, year 4 and 5 same sales as year 1. (the revolution happened in year 2 and caused a sales delay of 2 years) b. Client pays the unit over 5 years (schedule in excel) 2) Costing Assumptions: a. Land: EGP 600 per sqm of gross land (paid in year 1) b. Construction and infra: EGP 301 million paid over 4 years (1850 per sqm of BUA for construction, 250 per sqm of land for infra) (delay of 1 year due to revolution) c. Design: 1.2% of infra and construction cost paid in year 1 d. Sales commission of 1% payable upon sales (year 1, year4, and year 5) e. Marketing: 1% of total sales payable upon sales (year 1, year4, and year 5)

Detailed Cash Flow: 10

Net Cash Flow:

11

Economic Worth Analysis To reach a solid conclusion on the effect of the revolution on the Egyptian economy, four types of economic worth analysis were conducted; 12

1. Ranking Analysis a. Pre-Revolution i. Present Worth At MAR=15% PW = -304,773,280 – 28,392,000 (P/F, 15%, n=1) +34,328,000 (P/F, 15%, n=2) + 197,568,000 (P/F, 15%, n=3) + 166,208,000 (P/F, 15%, n=4) + 150,528,000 (P/F, 15%, n=5) + 116,032,000 (P/F, 15%, n=6) + 68,992,000 (P/F, 15%, n=7) + 21,952,000 (P/F, 15%, n=8) PW = EGP 79,544,705.83 ii. Future Worth At MAR = 15 % FW= -304,773,280 (F/P, 15%, n=8) – 28,392,000 (F/P, 15%, n=7) + 34,328,000 (F/P, 15%, n=6) + 197,568,000 (F/P, 15%, n=5) + 166,208,000 (F/P, 15%, n=4) + 150,528,000 (F/P, 15%, n=3)+ 116,032,000 (F/P, 15%, n=2) + 68,992,000 (F/P, 15%, n=1) + 21,952,000 FW= EGP 425,584,071 iii. Annual Worth We already know the present worth value = EGP 79,544,706 AW= EGP 79,544,706 (A/P, 15 %, n=8) = EGP 17,726,537.73 iv. Discounted Pay Back Period: DPBP: 13

PW(0) = -EGP304,773280 PW(1) = -EGP304,773280 -28,392,000 (P/F,15%, N =1 )= -329,461,976 PW(2)= -329,461,976 + 34,328,000 (P/F,15%, N=2) = -303,505,076 PW(3)= -303,505,076 + 197,568,000 (P/F,15%, N=3) = -173,600,909 PW(4)= -173,600,909 + 166,208,000 (P/F,15%, N=4) = -78,570,945 PW(5)= -78,570,945 + 150,528,000 (P/F,15%, N=5) = -3,731,926 PW(6)= -3,731,926 +116,032,000 (P/F,15%, N=6) = 46,431,910 Therefore, without the revolution, the investment will be fully recovered in 6 years.

b. Post-revolution i. Present Worth At MAR=15% PW = -304,773,280 – 106,792,000 (P/F, 15%, n=1) -106,792,000 (P/F, 15%, n=2) + 78,400,000 (P/F, 15%, n=3) + 141,120,000 (P/F, 15%, n=4) + 197,568,000 (P/F, 15%, n=5) + 166,208,000 (P/F, 15%, n=6) + 150,528,000 (P/F, 15%, n=7) + 116,032,000 (P/F, 15%, n=8) + 68,992,000 (P/F, 15%, n=9) + 21,952,000 (P/F, 15%, n=10) PW = EGP -56,510,226

ii. Future Worth At MAR = 15 % FW= --304,773,280 – 106,792,000 (F/P, 15%, n=1) -106,792,000 (F/P, 15%, 14

n=2) + 78,400,000 (F/P, 15%, n=3) + 141,120,000 (F/P, 15%, n=4) + 197,568,000 (F/P, 15%, n=5) + 166,208,000 (F/P, 15%, n=6) + 150,528,000 (F/P, 15%, n=7) + 116,032,000 (F/P, 15%, n=8) + 68,992,000 (F/P, 15%, n=9) + 21,952,000 (F/P, 15%, n=10) FW= EGP -228,615,382 iii. Annual Worth We already know the present worth value = EGP -56,510,226 AW= EGP -56,510,226 (A/P, 15 %, n=10) AW= EGP -11,259,779 iv. Discounted Pay Back Period PW(0)= -EGP304,773280 PW(1)= -EGP304,773280 - 106,792,000 (P/F,15%, N =1 )= -397,635,889 PW(2)= -397,635,889 - 106,792,000 (P/F,15%, N=2) = -478,385,983 PW(3)=-478,385,983 + 78,400,000 (P/F,15%, N=3) = -426,836,711 PW(4)= -426,836,711+ 141,120,000 (P/F,15%, N=4) = -346,150,893 PW(5)= -346,150,893+ 197,568,000(P/F,15%, N=5) = -247,924,679 PW(6)= -247,924,679 +166,208,000 (P/F,15%, N=6) = -176,068,374 The sign will change between years 17 and 18, therefore the investment will be fully recovered in 18 years

Comparative Analysis

15

NPV EGP 100,000,000 EGP 80,000,000 EGP 60,000,000 EGP 40,000,000 EGP 20,000,000 EGP 0 ‐EGP 20,000,000

Pre‐ Revolution

Post‐Revolution

‐EGP 40,000,000 ‐EGP 60,000,000 ‐EGP 80,000,000

Future Worth

EGP 300,000,000 EGP 200,000,000 EGP 100,000,000 EGP 0

Pre‐Revolution

Post‐Revolution

‐EGP 100,000,000 ‐EGP 200,000,000 ‐EGP 300,000,000

Annual Worth EGP 20,000,000 EGP 15,000,000 EGP 10,000,000 EGP 5,000,000 EGP 0

Pre‐Revolution

Post‐Revolution

‐EGP 5,000,000 ‐EGP 10,000,000 ‐EGP 15,000,000

Discounted Pay Back Period 16

2. Incremental Analysis 17

a. Internal Rate of Return i. Pre-Revolution FW= -304,773,280 (FIP, i*%,8)-28,392,000(FIP, i*%,7)+34,328,000(FIP, i*%,6)+197,568,000(FIP, i*%,5)+166,208,000 (FIP, i*%,4)+150,528,000 (FIP, i*%,3)+116,032,000 (FIP, i*%,2)+68,992,000 (FIP, i*%,1)+21,952,000=0; For i*= 20%, FW= -304,773,280 (4.29982)28,392,000(3.58318)+34,328,000(2.98598)+197,568,000(2.48832)+166,208,000 (2.07360)+150,528,000 (1.72800)+116,032,000 (1.44000)+68,992,000 (1.2000)+21,952,000=EGP 58,501,008.63 For i*= 25%, FW= -304,773,280 (5.96046)28,392,000(4.76837)+34,328,000(3.81470)+197,568,000(3.05176)+166,208,000 (2.44141)+150,528,000 (1.95313)+116,032,000 (1.56250)+68,992,000 (1.2500)+21,952,000= EGP -228,816,738.4; There was a change in the sign of FW between i*=20% and i*=25%, thus we can conclude that i* lies between 20% and 25%.

By interpolation, (x- 0.2)/(0-58501008.63)=(0.25-0.2)/(-228816738.4-58501008.63) x= 0.210 x= 21.0%. i* > MARR (15%), therefore, the investment is recommended.

ii. Post-Revolution 18

FW= -304,773,280 (FIP, i*%, 10)-106,792,000(FIP, i*%, 9)-106,792,000(FIP, i*%, 8)+78,400,000 (FIP, i*%, 7)+141,120,000(FIP, i*%, 6)+197,568,000 (FIP, i*%, 5)+ 166,208,000(FIP, i*%, 4)+150,528,000(FIP, i*%, 3)+116,032,000(FIP, i8%, 2)+ 68,992,000(FIP, i*%, 1)+21,952,000=0; For i*=10%: FW= -304,773,280 (2.59374)-106,792,000(2.35795)106,792,000(2.14359)+78,400,000 (1.94872)+141,120,000(1.77156)+197,568,000 (1.61051)+ 166,208,000(1.4641)+150,528,000(1.331000)+116,032,000(1.21000)+ 68,992,000(1.1000)+21,952,000= EGP 131,676,148.7 For i*=15%: FW= -304,773,280 (4.04556)-106,792,000(3.51788)106,792,000(3.05902)+78,400,000 (2.66002)+141,120,000(2.31306)+197,568,000 (2.01136)+ 166,208,000(1.74901)+150,528,000(1.52088)+116,032,000(1.32250)+ 68,992,000(1.1500)+21,952,000= EGP 1,649,359,791 By interpolation, (x- 0.1)/(0-131,676,148.7)=(0.15-0.1)/(1649359791-131676148.7) x= 0.120 x= 12.0%. i* < MARR (15%), therefore, the investment is NOT recommended.

b. External Rate of Return 19

i. Pre-Revolution 34,328,000(FIP,15%,6)+197,568,000(FIP,15%,5)+166,208,000(FIP,15%,4)+150,528,00 0(FIP,15%,3)+116,032,000(FIP,15%,2)+68,992,000(FIP,15%,1)+21,952,000=304,773,2 80(FIP, i’%,8)+28,392,000(FIP, i’%,7)  1,251,173,319= 304,773,280(1+i’)^8+28,392,000(1+i’)^7 I’= 18%>MARR thus the project is recommended ii. Post-Revolution 78,400,000(FIP,15%,7)+141,120,000(FIP,15%,6)+197,568,000(FIP,15%,5)+150,528,00 0(FIP,15%,4)+116,032,000(FIP,15%,2)+68,992,000(FIP,15%,1)+21,952,000=304,773,2 80(FIP, i’%,10)+106,792,000(FIP, i’%,9)+ 106,792,000(FIP, i’%,8) 1,706,724,566= 304,773,280(1+i’)^10+106,792,000(1+i’)^9+106,792,000(1+i’)^8 I’= 13%<MARR thus the project is not recommended IRR & ERR Comparative Analysis

3. Supplementary Analysis a. Break-even i. Pre-Revolution: 20

Break even Value for Residential Sales

Break even using present worth PW = {-304,773,280} + {(P|F,i=15%,n=1) * ((25X/3) â&#x20AC;&#x201C; 106,792,000)} + {(P|F,i=15%,n=2) * ((15X) â&#x20AC;&#x201C; 106,792,000)} + {(P|F,i=15%,n=3) * (21X)} + {(P|F,i=15%,n=4) * (53X/3)} + {(P|F,i=15%,n=5) * (16X)} + {(P|F,i=15%,n=6) * (11.4X)} 21

+ {(P|F,i=15%,n=7) * (6.4X)} + {(P|F,i=15%,n=8) * (1.4X)} =0 Solving for X shows that the breakeven value = EGP 9,041,824.574 By plugging the ''X'' value back in the cash flow diagram we acquire the following cash flow diagram for the break even state in the post-revolution case.

Break even cash flow diagram for pre-revolution Thus, the break even values for residential sales show that the actual post revolution values for residential sales are more than the break even ones enlisted in the above cash flow diagram, which implies that the project is within the profit margin.

ii. Post-Revolution:

22

Break even Value for Residential Sales

Break even using present worth PW = {-304,773,280} + {(P|F,i=15%,n=1) * (â&#x20AC;&#x201C; 106,792,000)} + {(P|F,i=15%,n=2) * (â&#x20AC;&#x201C; 106,792,000)} + {(P|F,i=15%,n=3*) * (25X/3)} + {(P|F,i=15%,n=4*) * (15X)} + {(P|F,i=15%,n=5 * (21X)} + {(P|F,i=15%,n=6 * (53X/3)} + {(P|F,i=15%,n=7 * (16X)} 23

+ {(P|F,i=15%,n=8 * (11.4X)} + {(P|F,i=15%,n=9* (6.4X)} + {(P|F,i=15%,n=10* (1.4X)} =0 Solving for X shows that the breakeven value = EGP 10,862,238.11 By plugging the ''X'' value back in the cash flow diagram we acquire the following cash flow diagram for the break even state in the post-revolution case.

Break even cash flow diagram for pre-revolution Thus, the break even values for residential sales show that the actual post revolution values for residential sales are less than the break even ones in the above diagram, which implies that the project is within the loss margin.

b. Sensitivity 24

In our analysis of the construction development project we decided to perform sensitivity analysis to determine the impact on the measure of economic worth when values of different parameters are altered. We decided to test the installment system, the initial investment represented in the area of the land, and the cost of built up area. A comparison was made between the base case and several alternatives in a trial to come up with some conclusions on how to decrease the losses to a minimum after the revolution and the shift it caused in the cash flow.

1. Installment system: In the base case of the existing development, the installment system required the customer to pay 25% in his first year, 20% in the second, 18% in the third, 15% in the fourth, 15% in the fifth and 7% in the sixth year. This installment system resulted in a total NVP of - 56,510,226 LE after the revolution.

Different installment systems were experimented: Alternative 1: 50%,20%,10%,10%,10% Alternative 2: 30%,20%,20%,15%,15% Alternative 3: 50%,25%,25% As seen in the graph plotted, the value of the total NPV had increased when a bigger weight of the money was shifted earlier in time; the initial installments becoming higher. This proves that the present worth is sensitive to errors in estimating the installment system/investment distribution.

Changing the installment system has caused changes in the total NPV reaching 68,876,206 LE (Alternative 3) making the total NPV to become 12,365,980 LE instead of originally -56,510,226 LE.

Total NPV

Installment system 140000000 120000000 100000000 80000000 60000000 40000000 20000000 0 -20000000 -40000000 -60000000 -80000000

Base case

Alternative 1 Alternative 2 Alternative 3

Installment options

2. Initial investment (Area of land): 25

The original area of the project is 336,000 sq. (80 acres) and this resulted in the total NPV being 56,510,226 LE. The area or investment was altered by adding and subtracting 50% of the investment and observing its effect on the total NPV. The present worth was proved to be sensitive to errors in estimating the initial investment as observed in the graph.

Total NPV Change in project area 0.00 -10000000.00

336000 sq m (Base case)

504000 sq m (50% increase) 168000 sq m (50% decrease)

-20000000.00 Total NPV

-30000000.00 -40000000.00 -50000000.00 -60000000.00 -70000000.00 -80000000.00 -90000000.00

3. Price of Built up area: In the base case of this project, the cost of built up area was planned to be 3000 LE which resulted in the total VPN to be -56,510,226 LE. However when the values of the BUA cost was changed after the revolution to test sensitivity. It was observed that the values for VPN have altered vastly by raising 26

the price of the meter to 3500LE or 4000LE resulting in a total VPN of -31,172,268 LE and -5,834,312 LE which are differences of tens of millions.

Change in price of BUA Price of BUA 0 3000 LE (Base case)

3500 LE

4000 LE

-10000000

Total NPV

-20000000

-30000000

-40000000

-50000000

-60000000

c. Risk Analysis

27

Basically risk analysis calculations are employed when one or more of the parameters used in the cash flow diagram are used with some degree form of uncertainty regarding their values. This is usually the case in real occasions where values assigned to most future transactions can not be acquired with ultimate certainty.

In the case of this residential project, all contracts regarding different cash outflow operations where assigned before the revolution and their assigned values where with a 100% certainty. On the other hand, values assigned to residential sales in either the pre-revolution or the post revolution case where all made based on economical forecasting and expected demand.

For the pre-revolution, the deviation of sales from the mean assigned value is much less than their counterparts in the post revolution case.

The values of present in the attached spread sheet for both cases show that for the pre-revolution case the probability for different values of residential sales where usually higher in the near future than in the far one, this is basically due to the fact that any firm is more certain regarding what is going to occur in the next couple of years than in the case of after 10 years, for instance. However, for the post revolution the values of probabilities show an inverse situation where the probabilities assigned to the last couple of years where higher than the values of the three years following the revolution. This is merely due to the fact of political unrest in Egypt and its severe effect on the economy, and effect which is expected to be undermined after some time due to expected political stability after the formation of a new political system. This process needed for reaching political stability is expected to be within five years; after which, the political scene is expected to stabilize and so be the economy.

28

 For the Pre- revolution case Expected value for present worth was equal to $164,351,780 Standard deviation from this value was calculated to be equal to $12,392,106 ''Z'' factor = (0 – 164351780)/12392106 = -13.26 This gives a probability of achieving a positive cash flow with a confidence level approaching 1

 For the post revolution case Expected value for present worth was equal to $50,766,677 Standard deviation from this value was calculated to be equal to $42,355,957 ''Z'' factor = (0 – 50766677)/42355957 = -1.19 This gives a probability of achieving a positive cash flow with a confidence level approaching 0.88 which is below the standard acceptable confidence level of Z=0.95. So investment in this firm turned out to be risky in the post revolution model.

Conclusion & Recommendations The construction of residential area was planned before the break out of the revolution with some months. The business started and had its contracts signed for different transactions such as marketing, sales commissions, infrastructure, construction, land and other operations. Sales where expected toe be acquired from the first year following the start of the project in 2010; however, the 29

Egyptian revolt has had its effect on the sales and residential demand. This effected the expected residential sales planned before the revolution to be shifted for two years in the future.

This effect of shift of values for only one parameter has had dramatic effect on the business firm. Different engineering economy processes where used in order to asses the current project situation after the revolution in order to take any required action for enhancing the situation of the firm.

Different ranking methods have shown sign change from the positive profitable margin in the pre-revolution case to the negative loss margin. A Break even cash flow diagram was calculated for the pre and post cases. For the pre-revolution the planned cash flow diagram had higher cash flow arrows than the break even model which implies a profit margin. For the post-revolution the arrows of the residential sales where found to be less than the values in the break even model.

Supplementary analysis using Internal Rate of Return and External Rate of Return both showed interest values less than the assigned Marr of 15% which show that the post revolution investment model is unjustifiable or unprofitable. Risk analysis did not deviate from this trend and it reached a similar conclusion to different methods that the projects turned out from the safe unrisk side in the pre-revolution case to a risky one after the economical halt in the aftermath of the revolution. All this shift was merely due to a shift in one of the parameters which stresses on the time value of money and its effect on monetary transactions.

With the current risky situation of the project and expected loss, the board of the company carrying out the project has to react to save itself from the expected loss margin. First they would use new marketing strategies with proposed promotions that would lead to an increase in demand. This could be a in the form of accelerated construction and thus submission of apartments in less time.

Another marketing approach would be via modifying the instalments payment terms by adjusting them by lessening them in the near future and increasing them after the first two years in order to make up for the current financial difficulties in the first two years after the revolution. Other than marketing approaches the company would try to increase the Marr value to compensate for the effect 30

of shift of sales. All these approaches should be studied first using different economical methods as the ones used previously in order to reach the optimum recommendation for the post revolution situation of the project.

Appendix: 1. Spreadsheet for collected data, ranking, incremental & supplementary analysis 2. Risk Analysis Spreadsheet 31