Question

After analysing the financial data of Q-Constructions, you notice that they are trending in the right direction. A new 12-month construction proposal has come to the company worth $1,000,000 and an important question is whether it will be financially viable. They want you to analyse the proposal, in particular, the recommended cash flow schedule and to understand the key financial points during the construction project. The following cash flow schedule is summarised below.

To ensure that all upfront and on-going outlay costs are covered in advance, Q-Constructionsincur an initial start-up cost of $200,000. The proposal states that they will receive a deposit from the client of 10% of the total project cost at the beginning. They then receive four equal instalment payments of 20% of the total project cost associated to project milestones from the client at the end of the 2^nd, 6^th, 8^thand 10^thmonth. Finally, they receive the last 10% project milestone on lock-up which occurs at the end of the 12^thmonth. Q-Constructionshas ongoing project costs of $20,000 to pay salaries and services at the end of each month. In additional, there are material costs of $100,000 associated for each of the project milestones at the end of the 2^nd, 6^th, 8^thand 10^thmonth. The current cost of capital for company is 8% per annum compounded monthly. You have been tasked with the important objective to determine whether this future project is financially viable. In addition, they want you to determine which milestone is needed to be completed in the project proposal such that it will be financially viable.  It’s time to show your Quants knowledge andexpertise with Excel to determine the financial viability of this project.

1. In a worst-case scenario where the project does not proceed, and the initial outlay is paid. Calculate the amount of interest that would have accrued on an amount of $200,000 at the end of 12 months with an interest rate of 8% p.a compounded monthly. Do not use EXCEL for this calculation.

b. Set up a cash inflow and outflow for the 12-month construction project proposal based on the information provided by the company above. By using the current 8% p.a compounded monthly cost of capital, calculate the Net Present Value of this proposal and whether it is financially viable project. Use EXCEL to calculate the net present value of the current situation.

- The full spreadsheet with all completed entries.  Show how you entered cash inflow and cash outflow amounts at the beginning, 1^st, 2^nd, 3^rdmonths.  You can type this in Word.

2. The NPV calculation (showing the calculation via the Excel function NPV and Excel cell references is OK).  You can show this either in the spreadsheet or type it in Word.    

EXCEL Instructions:  Set up your spreadsheet as below and add yourinitials to column names (i.e. unless your initials really are NFY!).  The coloured boxes below contain instructions.  We used the NPV function in Lectures (Week 2) – see lecture recordings for a demonstration.

3. The company wants to know at which milestone in the project proposal would be financially viable if the contract has terminated was early. Determine the milestone in the construction proposal for which the project would be financially viable.

d. Q-Constructionswould like you to create a visualisation of the completed NPV spreadsheet from part (b).  Include the graph here.

Answer 1

Time Period	Inflow	PV of Inflow	TOTAL Inflows
0	100,000	100,000	100,000
2	200,000	197,451	297,451
6	200,000	192,450	489,901
8	200,000	189,997	679,898
10	200,000	187,576	867,474
12	100,000	92,593	960,067

NOTE - PV is calculated at 8% p.a. intrest rate for each of the milestone. Total inflow is amount recieved till that milestone. Time period is No. of month divided by 12.

Time Period	Initial Outflow	Salaries	PV of Salaries	Material Cost	PV of Material Cost	TOTAL Cost Incured
0	200,000					200,000
1		20,000	19,872
2		20,000	19,745	100,000	98,726	338,343
3		20,000	19,619
4		20,000	19,493
5		20,000	19,369
6		20,000	19,245	100,000	96,225	512,294
7		20,000	19,122
8		20,000	19,000	100,000	94,999	645,414
9		20,000	18,878
10		20,000	18,758	100,000	93,788	776,838
11		20,000	18,638
12		20,000	18,519			813,994
TOTAL			230,257		383,737	813,994

NOTE - PV is calculated at 8% p.a. for salaries and material cost. Time period is No. of month divided by 12.

Time Period	TOTAL Inflows	TOTAL Cost Incured
0	100,000	200,000
2	297,451	338,343
6	489,901	512,294
8	679,898	645,414
10	867,474	776,838
12	960,067	813,994

As PV of total inflow is more than outflow the project is feasible.

Even if 8th milestone onwards if the project is not completed it will be financially fesiable.

Worst case senario

= 200000(1+8/12)^12*1

=216599.9

Intrest Amount = 216599.9 - 200000

= 16599.9