Recent Question/Assignment

Project finance model
INSTRUCTIONS
Scenario: You are acting as financial advisor to an Asian diversified conglomerate building a cement plant in Angola. The greenfield project will take 12 months to build at a cost of $100m plus a 10% contingency, and will generate $28m p.a. in first year revenues with opex of $10m p.a. We assume conservatively a plant life of 8 years. Sponsors would like to achieve 75% gearing. You are tasked in building a simplified model in order to discuss the key financial issues. USCPI is forecasted at 2% over the medium term. LIBOR is currently at 3% and you expect a net margin of 4.5%.
1/ Go to the calculations page - Note: The calculations sheet is a guide for a simplified model and may be amended to answer some of the questions below.
2/ Calculate inflation and interest rates
a/ Calculate the inflation index - begin the inflation in year 1 (the number in year zero is 1.0)
3/ Calculate the Sources and Uses statement in year zero - the assumed construction period
a/ Link the capital expenditures to the input
b/ Calculate the debt drawdown
c/ Calculate the equity drawn (Contributed as Ordinary Equity)
4/ Calculate the debt balance and interest expense
a/ Beginning debt balance in year zero is zero
b/ Debt drawn in year zero are from the sources and uses
c/ Debt repayment begins in year 1, assume constant principal repayments over 7 years
d/ Calculate interest costs
5/ Calculate the Asset balance and depreciation schedule
6/ Complete the Income Statement
a/ Assume revenues are constant in real terms, but subject to inflation in nominal terms
b/ Similarly assume opex is constant in real terms, but subject to inflation in nominal terms
c/ Complete the P&L down to Net Income
7/ Complete the Cash Flow Statement
a/ Assume dividends are restricted by cash available and net income
b/Given the Net Income restriction above, discuss how Equity contributions can be structured differently to ensure distributions to Sponsors are made when cash is available to equity but there is an accounting restriction (Net Income) on the maximum possible dividend.
8/ Calculate the Equity Cash Flow and Free Cash Flow, and resulting Equity and Project IRRs
9/ a/Calculate the Debt Service Cover Ratio (-DSCR-), and resulting minimum and weighted average DSCRs
a/Calculate the Loan Life Cover Ratio (-LLCR-)
b/Calculate the Average life of the Debt
10/ Complete the Balance Sheet
a/ Test that the Balance Sheet balances
b/List some model checks that would be required to ensure the accuracy of the outputs of a more complex model and hence the confidence of users in it
11/ a/What are the key drivers of the IRRs and DSCRs?
b/Present key sensitivities on the IRRs and DSCR that Sponsors may want to see
12/ What would the maximum Debt Capacity be if the average DSCR is expected to be 1.50x?
13/ What other information would be helpful to refine the financial model?
14/ What additional risks might the conglomerate face over the life of the project? How could this be mitigated?
15/ Please send your model solution and present the key results in one or two slides with your analysis and justify any assumptions you make.
Note: Excel model and PowerPoint to be sent within 48 hours. Documents to be labeled as follows: [Name - Financial Model], [Name - Presentation]