VCE Summer Internship Program 2020
Smart Task Submission Format
Intern’s Details
Name Gaurav Parmar
Email-ID gauravkumarparmar999@[Link]
Smart Task No. 2
Project Topic Project Finance : Modelling and Analysis
Smart Task (Solution)
Task Q1: While preparing a financial model what are the assumptions we need to take.
Please list down the list of assumptions with the values, assuming the project will be set up
in India.
Task Q1 Solution :
While preparing finance model we have to make some rational assumptions
Some ground rules for assumption:
Rely on past trends
Be conservative
Looking for guidance numbers
Looking for majors changes / disruptions / developments
The main categories that need to be covered in the assumptions are as follows:
1. Project assumptions-
Completion time- 25 years
Land size in Sq. ft.- 3000
Deposit- 25% of annual rent
Debt repayment- 10 years
Monthly Rent- 250,000
2. Financing Assumption
Debt and Equity Ratio ;Debt - 70% Equity - 30%
Debt service reserve- 0.25 years
Payment periods- 40 (Quarters)
Repayment periods- 10 years
Debt rate-10%
ST Solution Page 1 [Link]
VCE Summer Internship Program 2020
Smart Task Submission Format
3. Economic factors –
Inflation- 4%
Exchange rate : Currency rates – especially if company is involved into international
business (USD/INR)- 70
4. Cost assumptions-
CAPEX (Capital Expenditure) or project cost related assumptions- 10,170,000
OPEX (Operating Expenditure) or operating cost related assumptions. 966,000
5. Possible changes in the applicable taxes in every market of company are serving.
Tax rate- 25%
Corporate tax- 25%
Tax holiday- 0 years
Minimum alternate tax - 18%
Dividend distribution tax- 0
500 Words (Max.)
Task Q2: Explain the function of revenue, cost and debt sheet of the financial model.
Task Q2 Solution :
FUNCTION OF REVENUE SHEET IN FINANCIAL MODEL
With the forecast assumptions we made accordingly we can calculate revenue, From Revenue
sheet we know
Total revenue of company
Estimating all revenue items
Define Revenue parameters or different sources of revenue
Interest earned on deposits and different investments
To know the revenue growth rates
FUNCTION OF COST SHEET IN FINANCIAL MODEL –
From Cost sheet we know
Estimation of Total cost of company
Estimation of CAPEX (Capital expenditure) or project cost of project
Estimation of OPEX (Operating expenditure) of project
ST Solution Page 2 [Link]
VCE Summer Internship Program 2020
Smart Task Submission Format
Individual cost unit as a percentage to total cost of project
FUNCTION OF DEBT SHEET:
From Debt sheet we know
Interest expense.
Calculation of interest payment.
Projection of principal amount of debt
500 Words (Max.)
Task Q3: Explain in detail the various steps involved (with the importance) in the fin flows
sheet. Why and what the bank needs to check before financing the project.
Task Q3 Solution :
Steps involved in preparation of fin flow sheet are as follows-
1. Project Details -
The first step involved in preparation of fin flow sheet is to project the all details related to project in
fin flow sheet. This will help to show the percentage of debt and equity used to finance the project
and the debt service coverage ratio. It includes the all details regarding project like size in square
foots, Debt-equity ratio and DSCR etc.
2. Making assumptions -
There are various assumptions that need to be kept in mind while preparing the financial model.
The next step is to make list assumption on the fin flow sheet. These assumptions are in rational.
This is important step in financial modelling because this helps to forecast the future fin flow. This
will help us in doing various calculations in EBIT, Net income and Total fin Flow.
3. Estimation of Net Income for different periods-
Next step is the estimation of net income generated by calculating the total revenue or expenses
generated during the period and then deducting all the operating or non-operating expense from
the revenue and then tax to find net income.
4. Finding total project cash flow from the project’s equity financing, DSCR and then
final project cash flow -
The next step is to find the total cash inflow in the project from equity financing. Next is to find
DSCR (debt service coverage ratio) and final project cash by deducting principal and interest
amount from final project cash flow from equity.
ST Solution Page 3 [Link]
VCE Summer Internship Program 2020
Smart Task Submission Format
5. Finding results
The last step is to find the result that is been in form of calculation of IRR (internal rate of return)
from equity financing, DSCR minimum or average (debt service coverage ratio) and IRR (Internal
rate of return) from project.
Bank is one of the sources of Project financing and it always make sure the capabilities of
company to repay the debt and also check out the background of company regarding to pay
the debt in time or not. Bank need to check following before financing the project of the
company:
Company and management profile.
Last years audited financial results.
Details of collateral security.
Projected cash flow for next five years.
Bank loan of the company and its repayment structure.
All commercial agreements related to project.
Memorandum and articles of association of company with certification of incorporation.
Last one year bank statement from all the bankers.
Personal guarantee of the directors and their net worth statement.
Any other document which banker may feel necessary to approve financing.
500 Words (Max.)
ST Solution Page 4 [Link]