Subject Code & Title : ACFI7016 Financial Modeling Practical Skills
Learning outcomes assessed :
1.Identify and extract large volumes of relevant data, and process them into concise analysis
2.Apply appropriate financial models to complex problems in practical decision making and financial management
3.Demonstrate comprehensive understanding of various principles that underlie financial modelling and analysis
4.Evaluate critically the assumptions underlying financial models and demonstrate understanding of the consequences of inappropriate assumptions.
5.Communicate complex ideas effectively through verbal and written means
ACFI7016 Financial Modeling Practical Skills Assessment – UK.
Objectives :-
The assignment tests understanding of the modelling techniques introduced in the course, requiring you to use Excel effectively and to be able to explain your assumptions and approaches.
Required :-
There are two tasks to complete.
Task 1 Corporate valuation.
Your task is to value the shares of the company Glaxo Smith Kline plc using the data provided in the above Excel file. You are required to:
1.Forecast the financials of the company for six years
2.Build a free cash flow forecast
3.Using the WACC and long term growth rate given, perform the DCF analysis of the company and calculate the implied price per share.
4.Add a data table varying WACC and the long term growth rate
5.Add a scenario analysis on sales growth, using the current forecast as base case. Vary upside case by +0.5%, downside case by -0.5%
6.Insert a text box into the Assumptions tab of your spreadsheet and identify four items where the assumptions made for 2021-26 are only weakly justified by the figures for 2019-20, and based on your reading of the 2020 Annual Report
identify any improvements that could be made.
Task 2 Portfolio building :-
The task is to create a portfolio of five stocks that uses the concepts of Modern Portfolio Theory to deliver minimum risk and maximum return.
ACFI7016 Financial Modeling Practical Skills Assessment – UK.
Follow these steps to select your stocks:
1. Download the London Stock Exchange’s Company List, available from
2. Filter the list to show companies that satisfy the criteria :
o Market = AIM
o ICB Industry ≠ [does not equal] Financials
3.Select five stocks from a variety of industries for which the company name starts with the same letter as your own family name. If there are not enough company names that start with this letter, use one letter up or one letter down.
Next using Yahoo Finance or otherwise, download weekly closing prices for your five stocks for the period
Use this data to:
a) Calculate the mean weekly return for each stock
b) Calculate the variance-covariance matrix of weekly return for the five stocks
c) With short-selling allowed, produce a graph of the efficient portfolio frontier, preferably also showing the performance of the individual stocks.
d) Convert a risk-free annual rate of 1% into a weekly rate.
e) Add the Capital Market Line (CML) to your graph.
f) Find the weightings of the five stocks in the portfolio which lies on the CML.
g) In no more than 200 words, discuss the limitations of this analysis.
Mark allocations :-
1. Models correctly and efficiently applied to the data
2.Assumptions clearly stated and reasonable
3.Calculations clearly laid out with explanations to aid the reader
4. Work well-presented
5.Commentary shows understanding of the limitations of the techniques
ACFI7016 Financial Modeling Practical Skills Assessment – UK.
Assessment criteria :-
For calculations:
1. Techniques correctly applied to the data
2. Work well-presented
For discussion:
1. Demonstration of understanding of concepts
2. Clarity of writing
Presenting coursework for assessment
Submit two Excel files
There is no overall maximum length for this assignment but observe the maximum words given for the discussion tasks above.
ORDER This ACFI7016 Financial Modeling Practical Skills Assessment NOW And Get Instant Discount
Read More :-
ACFI7012 Financial Markets & Institutions Assignment-Oxford Brookes University UK.
