Reference no: EM132569310 , Length: word count:1000
FIN5016 Financial Modelling Assignment - Birmingham City University, UK
Question 0 - Put in an honour pledge and put Getformula into the spreadsheet. Use Getformula in your answers!
Question 1 - The Question1 template sheet in exam template file gives the basic pro forma model discussed in class. Make the following changes to this model:
The firm is planning a regular usage of cash every year from next year, so its cashes and marketable securities should become: Casht = Max(Casht-1 - 300,0)/
The firm is not going to pay an interest rate on debt which is as high as 12%, but pay 10% instead.
Long-term free cash flow growth rate will be at a higher level which is 10% instead of 6%.
The firm will dilute its number of shares to200 shares when other things hold constant.
Dividend pay-out ratio is reduced to 50%.
The firm's tax rate is 40% when its profit before tax is above (or equal to) 480, or 30% when its profit before tax is below 480.
Question 2 - Company XYZ is contemplating a product development program encompassing 6 major projects. The company is constrained from embarking on all of the projects at once by the number of available (budgeted) developers (50) and the budget allocated for project expenses (£300,000). The following table shows the resource requirements and the estimated profit for each project. Use EXCEL's Solver to answer questions (1), (2) and (3) in the EXCEL spreadsheets.
|
Project
|
Expense (£000)
|
Developers required
|
Profit (£000)
|
|
1
|
60
|
5
|
35
|
|
2
|
100
|
8
|
80
|
|
3
|
55
|
9
|
30
|
|
4
|
50
|
5
|
20
|
|
5
|
100
|
6
|
60
|
|
6
|
85
|
5
|
55
|
1. What is the maximum profit, and which projects should this company select?
2. Suppose that CEO of company XYZ decides that project 1 and project 3 are mutually exclusive when all other projects remain independent. What is the revised project portfolio and the revised maximum profit?
3. In addition to conditions in question (2), suppose that project 4 and project 5 are "must-take" projects. What is the revised project portfolio and the revised maximum profit?
Question 3 - Assume that you need to find out a portfolio to invest but you are only given a short time to do your research and find a suitable portfolio. Therefore, you need to do the following:
(1) Pick up 10 firms from FTSE100 and download these firms' daily share prices over the most recent 3-month.
(2) Use these prices to calculate (by using EXCEL) these firms' mean log returns and log returns' variances.
(3) After that, use the information of returns and variances to choose 5 firms that you believe to be "good choices" in forming your own investment portfolio. Discuss why you choose these 5 firms.
(4) Use EXCEL to calculate covariance and correlations of these 5 firms (assume that they are equally weighted). Explain your results.
(5) Use EXCEL's MMULT function (plus TRANSPOSE function) to calculate your portfolio's mean log return and standard deviation of log returns.
(6) Now select any two stocks out of the five, and demonstrate the selection of the minimum variance portfolio in Excel. Plot the efficient frontier in Excel graph.
Attachment:- Financial Modelling Assignment Files.rar