MTH016
2nd SEMESTER 2024/25 COURWEWORK 2
Introduction to Financial Modelling
SUBMISSION DEADLINE: 5:00 PM on 16 May 2025
INSTRUCTIONS TO CANDIDATES
1. This coursework must be completed individually and independently.
2. Total marks available are 100.
3. This coursework consists of 9 questions, and each question is worth 11.11 marks.
4. Answer all questions. There is NO penalty for providing a wrong answer.
5. The answers to all questions should be presented in a single workbook.
6. Submit the EXCEL workbook of your answers to Learning Mall Core.
Open “Coursework 2.xlsx” and save as “YourGivenName YourFamilyName.xlsx”. You must use your name (e.g. “Youngmin Ha.xlsx”).
Switch to the S&P 100 worksheet if needed.
1. Select cell D2, and enter a formula to get the price of symbol in the cell A2 on 2015-04-01 from the Price worksheet.
Select cell E2, and enter a formula to get the price of symbol in the cell A2 on 2025-04-01 from the Price worksheet.
Format D2:E100 with Accounting format (dollar signs), two decimal places.
2. Select cell F2, and enter a formula to calculate annual return from D2 and E2 (see pp. 207-
208 of the textbook titled “Financial Analysis with Microsoft Excel”). Use the GUESS argument of the RATE function if you get #NUM error for NVDA.
Format F2:F100 with Percent format, no decimal places.
Check that Excel automatically fill in the range D3:F100.
3. Click in the table area, and insert a PivotTable as a new worksheet. Name the worksheet SectorPT.
Switch to the SectorPT worksheet.
4. Create a PivotTable to show both 1) the Average of Annual Return by Sector and 2) the Count of Symbol by Sector. Format the values of Average of Annual Return in Percent format, zero decimal places. Also, apply the data bars. You should make the PivotTable below:
Switch to the S&P 100 worksheet.
5. Select cell G2, and enter a formula to calculate outperformance compared to the Average of Annual Return of the corresponding Sector from SectorPT worksheet. For example, the outperformance of AAPL is 1% from the following calculation: 23% (Annual Return of AAPL) - 22% (Average of Annual Return of Information Technology Sector).
Format G2:G100 with Percent format, no decimal places.
Check that Excel automatically fill in the range G3:G100.
Make a copy of the ‘S&P 100’ worksheet, and rename it to ‘S&P 100 Subtotal’. Switch to the new worksheet.
6. Subtotal the data by Sector, 1) averaging the Annual Return column, and 2) counting the Annual Return column. You should follow the four steps (a, b, c, d) of Figure 5.60 Subtotal Rules in the textbook.
a. Sort the data first.
b. Convert to a range.
c. Select the control field.
d. Subtotal within Subtotal.
Switch to the ‘Advanced Filter’ worksheet.
7. Fill the range A4:?? to form the criteria of Advanced Filter to filter the stocks that satisfy both the conditions:
a. Annual Return is less than or equal to 13%,
b. and Outperformance is greater than or equal to 0%, in the three sectors: Financials, Health Care, and Industrials.
8. Do an Advanced Filter on the SP_100 table in the ‘S&P 100’ worksheet based on the criteria in the range A4:??. Place the results in A15.
9. Turn the Advanced Filter results into a table. Do a two-level sort by Sector (A to Z) and Annual Return (Largest to Smallest). Autofit the column widths to make names and sectors are visible.