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]

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]

Assessment 4: Individual Essay (35%)Due Date: Week 11Word Count: 1800-2000 wordsStudents are required to choose one client group out of the following:Young PeoplePeople experiencing HomelessnessIndigenous...Do NOT utilise sources such as www.tutor2u. references for the purpose of your assignments. If to have met the requirements of the assessment.com and other such web materials as these in no way constitute...Global LogisticsJindal School of Banking and FinanceNovember 2023The assignment comprises of two questions with various sub-parts. You are required to submit your answers for every question. The submission...Portfolio:Written portfolio item 3 - Personal Practice Framework 2000 wordsPurposeTo demonstrate your ability to apply a comprehensive and unified framework to a professional situation.Steps1. Profile...Lab 8 Imaging InformaticsThe grade for this lab is 20 points instead of 10 points.Please follow these instructions to acquire the necessary dataset and metadata for your work:Visit the International Skin...Case Study 1Zainab is a 24-year-old law student. She grew up in Lakemba in New South Wales and moved to Glebe when she was 18. Zainab began a relationship with a fellow university student, Sam, when she...Q1 ML for Malware Analysis 25 PointsIn the week 7 lecture, we show example code of using 3 machine learning models to train and measure the performances on data in the form of feature vectors for 200 binaries...**Show All Questions**