Vce final report - Vardhan consulting engineers PDF

Preview

Summary

INTERNSHIP PROJECTON####### “PF 104 - Financial Modelling and Analysis of 50 Flats Housing####### Project in Gurgaon, Haryana IN”Company- Vardhan Consulting EngineersPOST GRADUATE DIPLOMA IN MANAGEMENT (Batch 2019-21)Under the Supervision of -Mr. Ashish Kumar (Mentor)Neha Kumari (HR)Submitted By -La...

Description

INTERNSHIP PROJECT ON “PF 104 - Financial Modelling and Analysis of 50 Flats Housing Project in Gurgaon, Haryana IN”

Company- Vardhan Consulting Engineers

POST GRADUATE DIPLOMA IN MANAGEMENT (Batch 2019-21)

Under the Supervision ofMr. Ashish Kumar (Mentor) Neha Kumari (HR)

Submitted ByLavish Dhingra

JAIPURIA INSTITUTE OF MANAGEMENT, NOIDA

ACKNOWLEDGEMENT

I am grateful to Vardhan Consulting Engineers for giving me this opportunity to work under them. I want to express my sincere thanks to Mr. Ashish Kumar , for his guidance and mentorship during the period of internship. He has helped in understanding the business model and encouraged me at every phase of submission of reports. Without his persistent aid and valuable guidance, completing the assigned project would have become very cumbersome.

The HR working with AIM India Pvt. Ltd. have also been a helping hand in completing this internship. They have been cooperative throughout my internship tenure.

Executive Summary Real Estate, one of the most recognised sectors around the world. It is divided into 4 sub-sectors which are retail, housing, commercial and hospitality. Residential segment contributes about 80% in this sector and its demanded is expected to grow sharply in the years to come due to urbanisation and rising household income. India is among the top10 price appreciating housing markets internationally. Around 1 crore people migrate to cities every year. The key growth drivers for this residential segment are rise in population, rise in number of nuclear families, rise of disposable income, easy availability of finance, repatriation of NRIs and HNIs.

On the other hand, in order to protect home-buyers and to boost investment in real-estate industry, the Real Estate (Regulation and Development) Act, 2016 was passed, which has made mandatory for all commercial and residential real estate projects to register with it where the land is over 500 sq. metres or 8 apartments. And it establishes a Real Estate Regulatory Authority (RERA) in each sector to regulate and act as an adjudicating body for speedy dispute resolution. There are some other key policies launched by government for this sector Benami Transactions Act  Change in arbitration norms

 Dividend Distribution Tax (DDT) exemption  PR for foreign investors  Boost to affordable housing construction  Service tax exemption  Goods and Services Tax  Demonetisation  Interest subsidy to home buyers The commercial space has few players with presence across India and they have shown rapid increase in IT and ITEs, BFSI, and Telecom sector. Their operating model has been shifted from sales to maintenance and lease and with time demand for office space in TierII cities could be seen.

The retail segment forms a small portion Indian Real Estate market. There are small number of organised retailers in India and are mostly developed by residential and office space developers. In case of hospitality segment, Delhi/NCR, Bengaluru, Mumbai, Chennai and Hyderabad forms the biggest hospitality market in India. They have services apartments and convention centres alongwith hotels. The key growth driver is rise in tourism and tax incentives for hotels.

With the changing time and with increase in the number of cities demand for housing is increasing. So, in order to boost real estate

sector, government is providing housing loans up to Rs 3.5 million in metro cities and they are included in priority sector lending by the RBI in June 2018. The government has allowed 100 % FDI for townships and settlements development projects. And in order to promote affordable housing in India, government in the Union Budget 2019-20 has extended benefits under Section 80 - IBA of the Income Tax Act.

The top players in real estate sector are- Godrej properties, Omaxe, DLF, Eldeco, Prestige Group, Kotle Patil developers, Ericsson India Global Services, Punj Lloyd Ltd., Signature Global India Pvt. Ltd.and many more.

Table of Content 1

Executive Summary

2

Introduction

3

Analysis

4

Results

5

Conclusion

Introduction Techvardhan Infra Pvt. Ltd is a pioneer organization involved in Real Estate activities. It is a non-government, incorporated in the year 2018 and have a registered office at VARDHAN HOUSE, Anand Bazar, Danapur Cantt, Patna 801503, Bihar. They do what a real estate developer needs to do, they have adopted chic and smart projects alongwith regular announcement of new feasible projects, adopted a consumer-friendly approach, which in turn leads to fewer or no complaints. They believe in having a good track record of events and provide affordable housing solutions to individuals.

The company has completed more than 30 projects and are expanding its services all over India. They firstly do the research and then plan accordingly keeping in mind the customers. It has developed several projects in Hyderabad, Gurgaon and Bengaluru region. They are planning to diversify its business into developing an industrial warehousing parks and IT parks for the next stage of growth.

Now, it is planning to develop a project near Gurgaon, Haryana and has acquired a land for the same wants to develop it as a residential building having 50 flats of 900 sq. ft each. And they are expecting to sell the flats at a rate of Rs. 4000 / sq. ft. They have expected a Capital expenditure on this project to be Rs. 8 Crore and Operating Expenses to be Rs. 50 Lacs / per annum for the whole project. And for this, they are seeking a non-recourse debt (project financing) with 70:30 as Debt to equity ratio from leading commercial banks in India as a 12 years term loan.

Analysis of the financial model The Financial Model is prepared in which all the information regarding the Flat construction and cost and revenue related to the project are given below: Details of the assumptions made for the Project are-

For Cost, Capex Capex is the total investment done for the project company. Investment breakdown is done in detail:

1) It is assumed that the total cost incurred on flat will be 74% of the total capital expenditure which is Rs. 58997050 , which means that the cost of constructing 1 flat is Rs. 1179941 2) For interior decoration it will cost 3.7% of the total capex which means for 1 flat it will cost up to Rs. 58997 3) For furnishing all the flats it will cost 7.4% of the total capex i.e., 29 lakhs of the total cost. 4) Fixtures will cost around 2 lakhs which is 0.3% of the total cost. 5) The fees and the duties which includes Stamp Duty, Broker Fee, Fund Raising Fee and Transfer fee will contribute 11.8% of the total capex. 6) Interest during Moratorium is 1.8%, which accounts to approx. Rs. 14 lakhs. 7) Loan and documentation fee incurred will be 0.7% of capex 8) CSR, HSE and Training will contribute to 0.3% of Capex.

Capex Loan and Interest CSR,ofHSE, Documentati During Tranfer Fund Raising Moratorium on Training Fee Fee FeeDeed 0% 1% 1%2% Stamp Duty 1% Building 7% Broker Fee Registration Fixtures 2% 0% 0% Furniture 7% Interior Decoration 4%

Flat 74%

OpEx The total operating and maintenance cost per year is 50 lakhs 1) For maintaining the overall building, it will cost 68% of the total opex which means that Rs.34 lakhs will be incurred every year per flat. 2) For providing the amenities like electricity, water and internet it will cost 7% of the total opex i.e., Rs. 7000 per year for every flat. 3) The salary that will be paid to maid and accountant will account to 13% of the operating expenses. 4) For plumber, electrician and other miscellaneous expenses it will account to 9% of the operating expenses every year. 5) And for insurance charges it will cost 3% of the total opex.

Opex O & M Cost (Monthly Breakdown) (OpEx) Utilities (Electric + Water + Internet) Plumber + Electrician + Misc etc 9%

Building Maintainence Salary (Maid + Acountant) Insurance

3%

13%

7% 68%

For Revenue, Revenue growth rate assumptions are one of the most important assumptions in a financial model. Small variances in top-line growth can mean big variances in earnings per share (EPS) and cash flows and therefore stock valuation. For this reason, analysts must pay a lot of attention to getting the top-line projection right.  Total expected revenue from flat sales will be Rs 18 crores.  The instalment amount is fixed that is Rs.1.2 crores per year that will be deposited in bank.  So every year the sum of sales receivable and interest is the revenue for the project.  The instalments will be received till 15 years.  The interest received form the bank is 5 percent of Principle of every year.

For Debts The Capital structure of the firm is formed in such a way that 30 percent is raise through equity and 70 percent is raised through Debts.

 The Debt is raise d by the firm for 12 years at rate of 8 percent per year.  The Moratorium is 0.5 years and the total number of periods is 24.  Than the debt is being estimated first the interest payment is calculated.  Than the principal payment.  Than final the total is taken of both payments and subtracted from year wise debts till it becomes zero.

FINANCIAL MODEL In this step the Cash flows will be calculated by taking some of the assumptions. ASSUMPTIONS Inflation DDT Tax Holiday Tax rate

4.50% 0.00% 0 yrs 20.00 %

Debt rate Moratorium Debt tenure

8% 0.5 years 10.0 yrs

Depreciation

7.00%

USD/INR Discount Construction MAT

70.00 7% 0.25 yrs 18.5%

These are some of the assumptions which had been taken for the estimation of Cash flows, IRR and DSCR.  Inflation rate is taken to be 4.5% by seeing the current economic situations.  Tax rate is taken to be 20%  Debt rate is 8%.  The depreciation is charged at the rate of 7%  The discount rate for the investments is 7%. So, after considering all the assumptions the estimation of Cash flow started. The steps involved were: 1. The revenue is being calculated and other incomes are also added in it. 2. The operation cost is being calculated.

3. The operating cost is subtracted from the Revenue and EBTIDA is being calculated. 4. After this non-operating expenses is been deducted from the EBTIDA. 5. Income before tax is generated. 6. Than tax is been deducted and finally income after tax is calculated. 7. The depreciation is added back to get the final cash flows.

Results: RESULTS Equity IRR

3.96%

Min DSCR Avg DSCR

-0.21 0.32

Project IRR

-5.30%

These were the findings from the final calculations. The Equity IRR turn out to be 3.96% which is lower than the discount rate so it is clear that the project is not giving the promising return so investors doesn’t need to take high risk for such a low return. The Project IRR is -5.30% which is negative and negative IRR are consider worst for any project so it is clear that this project is not making the good amount of returns also. The Min DSCR for the project is -0.21 The average DSCR is 0.32.

LEARNINGS These were 4 tasks provided to us. Each task has its own importance and each task gave its own leanings and knowledge. But before starting with the task, the main task was given to us was regarding the corporate skill development. In which we have to analyse our skills and measure them, and has to work on the skills which were not appropriate for us. Starting with the task1 – Task1- Under this we have to read and work on some basic concepts like the difference between the finance project and accounting project. The relevance of project finance with the corporate finance was made. Then I was able to learn or you can say I was able to recall various finance terminologies like Amortization, Angel Investor, Annuity, Bond, IRR, NPV, Sunk cost, Future Value and many more. Then though this task I was able to learn that infrastructure, mining and transportation, etc. comes under finance project. Learnings: I get to know about the finance project that it is used for particular longterm project through a Non- recourse source. This means that the investor has no resources to invest except the cash flow of the project and collateral of that project. The investor will only invest in that project which will give him profit. Risk and the returns of these types of projects is low. The sector having low market volatility and reasonably predictable market is suitable for these types of projects such as Infrastructure, mining and transportation.

Task2- A financial model was provided to us which was related to the selling of flats in Hyderabad which were 3000 sq ft in total. Through this we were able to learn how to prepare a financial model with cost sheet, revenue sheet and the debt flows. The coast sheet includes all the building, infrastructure and maintenance cost and also the operation cost. All this cost was calculated per sq ft. and then total cost was calculated. The revenue sheet consists of all the income earned to the project. This includes the rent income and other incomes. After that appreciation is applied and the rent is calculated for the coming 25 years (this can be done for any no. of coming years). All the revenue is sum up. 3rd sheet was a debt sheet which show the amount of loan taken from the band and the no of installments to be paid. The time period within which the loan will be repaid along with the interest amount. The 4th sheet was of the finflow, which tells about the FCFF with the revenue of the project. From EBITDA the interest and depreciation are deducted to get EBT (i.e.

earning before tax). From EBT, tax rate is deducted to get at PAT (i.e. Profit after tax). The method of calculating the FCFF includes adding back the depreciation amount, deduct the principal payment. But the CSR is calculated on total income. It is the duty of banks to check the financial position of the business To know that whether the company is able to pay back its loan or not. The bank must need to be sure that there is no amount of risk involved at all. This is way I have analyzed all the modelling of the project and calculate IRR & DSCR to arrive at the future cash flow. Learnings: Through this assignment I was able to learn how to analyze the financial model. Task3- I was able to learn the various terms like NPV, IRR, DSCR, Debt repayment, Inflation, depreciation, revenue and cost. I was able to learn and understand the basic concepts and the conditions of the project. Different types of the ventures were also studied like solar power, infrastructure, utilities and etc. various factors are taken into consideration wile approving the finance project. There factors are the assessment of the promoter history and background, evaluation of the company and project business model, legal obligations, and analysis of the financial statements and the structure. Then the revenue model for the solar power, real estate was studied in detail their revenue sources were analyzed. Learning: I was able to learn all the aspects of the finance project.

Task4A- We got various cases under this task. I choose the case of constructing expressway between the Hyderabad and Anantapur AP. The total capex was 200 crores and the revenue from the toll was 373750000Rs as the income with the inflation rate of 5%. With this the client was incurring loss for more than 5 year where ass the project was given to us was to estimate the income of the client for next 25 years. Learnings: with this I was able to prepare a financial model by my own. It requires various types of assumptions to be taken into mind regarding the revenue of the project.

Conclusion Project finance is the funding (financing) of long-term infrastructure, industrial projects, and public services using a non-recourse or limited recourse financial

structure. The debt and equity used to finance the project are paid back from the cash flow generated by the project. Project financing is a loan structure that relies primarily on the project's cash flow for repayment, with the project's assets, rights, and interests held as secondary collateral. Project finance is especially attractive to the private sector because companies can fund major projects off-balance sheet. Cash flows generated by the SPV must be sufficient to cover payments for operating costs and to service the debt in terms of capital repayment and interest. Because the priority use of cash flow is to fund operating costs and to service the debt, only residual funds after the latter are covered can be used to pay dividends to sponsors undertaking the project.

The cash flows generate in these projects are totally dependent upon the economy and project is going to react. Usually they are high investment project for the long period. It is not always true that the projects which giving good amount of revenue will lead to a successful project. There are many factors on which the cost and life of the project is dependent.

The negative IRR is really bad for the projects and will lead to make loss so the investors need to decide how much risk he/she is interested in taking in for the project.

References

1) https://www.cbre.co.in/en/research-reports/India-Real-Estate-MarketOutlook-2019

2) https://www.asiapropertyhq.com/india-property-developers/ 3) https://www.ibef.org/industry/indian-real-estate-industry-analysispresentation...