Experiential Learning Assignment #1

Dear Students,

Here are the details of the first (and only) experiential learning assignment for SAPM course. The due date for this assignment is 18, April 23, 9.00 PM. I expect you to complete this assignment by this date/ time. Hence you have 2 weeks to work on this assignment, which should give you plenty of time to do a quality job in a timely manner. This is a Capstone assignment that will help you synthesize your learnings across the course. This assignment will be for 15 marks.

This will be an individual assignment, meaning each and every student is expected to do independent and original work as per guidelines elaborated below.

Two ways to get the data:

1)     Prowess database

You may download the data from PROWESS database – details on how to access Prowess database were communicated earlier and training sessions were also held earlier. Please follow those instructions. PROWESS database is a reliable and robust database and BITS, Pilani, WILP Division has subscribed to the same so that our students can get access to the most reliable and up to date financial data for analysis and project work. Prowess can be accessed remotely by the students. Details have already been shared and students should follow those instructions.

2)     Bloomberg Terminal

Second option is to get the data from Bloomberg Terminal. Again, Bloomberg is a world renowned database used by top banks across the globe. BITS, Pilani, WILP Division has subscribed to the same so that our students can get access to the most reliable and up to date financial data for analysis and project work. However remote access to Bloomberg is not available as per the policy of Bloomberg world wide. You may come to campus during the “Lab Open Week” and get the data if you so desire. Details of the Lab Open Week have been communicated separately. Alternatively you may submit an email request to Ms. Shilpa Narlagiri (Email: narlagiri.shilpa@wilp.bits-pilani.ac.in) and she will send the data (in Excel format) via email to you from Bloomberg. But you have to clearly mention what data you need, i.e. company name, ticker symbol, and what data you want, i.e. closing stock price data for one year. She will reply back generally within 48 hours.

When you submit your assignment, (i.e. in your excel file) you must mention the source of your data, i.e. whether it is Prowess or Bloomberg and the date you downloaded the data. This is very important.

Detailed instructions are as follows:

1) Select two stocks/ companies of your choice for this assignment, and also one index (to which they belong like Nifty 50 or BSE Sensex)

First step is to select two stocks/ companies of your choice. The only condition is that they should be from different industries. For example, you may select Tata Motors and say Infosys (Manufacturing/ Automotive and IT), but do not select Wipro and Infosys (both IT industry). Ideally one of the companies should be the company you are working for or in the same industry as the one you are working in. But in any case, select the companies from different industries, otherwise you will not be able to see any diversification benefits.

2) Please obtain the daily closing stock price of these two companies (and also obtain the closing index) from Bloomberg or Prowess database for the past one year, i.e. from 1 April 2022 to 31 March 2023. You can get the data only from these two sources and no other data source. You need to mention the data source and the date of obtaining the data in your excel sheet.

3) From the price series you should compute the daily returns either simple returns or log returns. Please choose one method and be consistent, i.e. apply same method for both the stocks (and the index) for the one year entire period. Please include a brief explanation as to your choice of method (in your report), i.e. why you chose to use simple returns or log returns as the case may be.

4) First sheet should have identified information: Make sure you have one sheet which has your student ID, name, course code, course name, companies chosen and why (does it relate to the company you are working in? Means one of the companies should be the company you are working or from the same industry.) In this sheet the source of the data (Bloomberg or Prowess) should be mentioned along with the date on which the data was obtained.

5) Next sheet should have the closing stock price and index data that you have downloaded

6) First set of calculations (do this in separate tab in Excel):

You will need to calculate the following:

–       Average daily return, Variance, and Standard deviation of Stock #1 over the period of a year

–       Average daily return, Variance, and Standard deviation of Stock #2 over the period of a year

–       Covariance and Correlation across the two stocks (over the same time period)

7) Assume that you are being given Rs. 100,000 in your portfolio (as of the starting date) and you need to split that 60:40 between the two stocks. You may choose which stock should get 60% and which stock should get 40%.

8) In another sheet in excel do the following set of calculations:

–       Expected return, Variance, and Standard deviation of Overall portfolio (with 60:40 split)

9)  In this sheet you will also need to plot the efficient frontier and the CML. I will now outline some guidelines as to how to go about doing this. Compute the portfolio risk and portfolio return for various combinations of the two stocks that you have selected. Go from 0% in stock 1 and 100% in stock 2 all the way to 100% in stock 1 and 0% in stock 2 in increments of either 1% or 5% or 10%. You should now have two columns of data, one column which has the portfolio risk and the second column which has the portfolio return. Draw a scatter plot using this data. Excel requires that the first column in your dataset should represent the X axis, which in our case is portfolio risk and the second column should represent the Y axis which in our case is the portfolio return. The chart that you obtain would be the efficient frontier.

10) In this sheet you would also need to plot the CML. For this identify the risk-free rate from the internet and mark it as a point on the Y axis on the same scatter plot. You can do that by adding a new data series consisting of only one point and label it as risk free rate. Then insert a line into this chart that is anchored at the risk-free point and is tangential to the efficient frontier. This line represents the CML.

11) You may identify the point of tangency by using excel solver. The steps to do this are as outlined below. Remember the CML represent combinations of risk free asset and the market portfolio. Initially and quite arbitrarily assume that your market portfolio comprises of 50% in stock 1 and 50% in stock 2. Then calculate the risk and return of the market portfolio. Then compute the Sharpe ratio for this market portfolio in a cell in this sheet. Then use Excel solver to maximize the Sharpe ratio by changing the weight of the stocks in the portfolio. This will give you the point of tangency, which is the market portfolio.

12)    Finally, in another sheet, run a regression to calculate the Beta of each of the stocks with respect to the market, where the market is proxied by the index. Graph the regression on a chart. Using the calculated Beta and the risk-free rate calculate the required return for each of the stocks based on CAPM model.

13) Once you have completed all the above calculations in Excel, please write a short report in WORD (max two A4 size pages) outlining your key analysis, findings, learnings, as well any decisions you made such as methodology, etc. It should be a short report and to the point.

PLEASE NOTE THAT THIS IS AN INDIVIDUAL ASSIGNMENT AND EACH STUDENT SHOULD WORK INDEPENDENTLY AND PRESENT THEIR FINDINGS INDIVIDUALLY. PLEASE DO NOT COPY OR PLAGIARIZE IN ANY WAY EITHER FROM YOUR FRIENDS OR FROM THE INTERNET, ETC. IF YOU DO THEN YOU WILL BE AWARDED “0” MARKS WITHOUR ANY HESITATION. I CAN EASILY FIND OUT IF YOU HAVE COPIED.

You should upload a soft copy of your Excel Workbook and Word report to the course page in Taxila latest by April 18, 9.00 PM. 

Best Wishes!

Get expert help for SAPM Course Assignment Help Online and many more. 24X7 help, plag free solution. Order online now!