n Table 3 below, see current data obtained from Morningstar Direct on 658 U.S. actively managed fixed-income mutual funds.
finance
Description
Finance 380 – Investment
Valuation and Analysis
Spreadsheet
Assignment 1
Due date: Friday, March 6, 2020 Prof.
D. M. Smith
In this assignment, you will use VLOOKUP and
pivot tables to evaluate the recent success of active bond mutual fund managers,
use various Text functions to manipulate cell contents, and develop a model that
accommodates prepayment on an amortizing loan.
GENERAL INSTRUCTIONS:
Answer each of the following questions in a file,
sheets, and cells exactly as instructed. Unless the question states otherwise,
wherever possible use Excel functions. It is important to leave the function in
the solution cell (i.e., don’t change “=NPV...” to a number).
Each student must start with a brand new, blank
file. Name the file lastname.firstname.Fin380 #1.xlsx. This file will contain
all your work for this assignment. Submit a hard (printed) copy of your work
and email the file to ds693@yahoo.com. This
assignment must be completed as stated in the course syllabus (no collaboration
with other students on any step of
this project).
QUESTIONS:
1. In Table 3 below, see current data obtained
from Morningstar Direct on 658 U.S. actively managed fixed-income mutual funds.
Each fund in Table 3 is trying to outperform one of five benchmark indexes
listed in Table 1.
In your blank
Excel file, paste Table 1 into a sheet that you name Indexes. Paste Table 3 into an adjacent sheet that you name Active Fund Performance.
Using
Excel’s vlookup function, in your
Active Fund Performance sheet, calculate the benchmark-adjusted return
for each of the mutual funds for 1, 3, and 5 years. For each fund, benchmark-adjusted
return is calculated as:
Benchmark-adjusted
return = Individual mutual fund return - Return for index.
a) Do this in a new sheet called Pivot Tables: Create a pivot table showing the proportion of
funds associated with each benchmark that beat the index in the past 1 year. Then
do the same for 3 and 5-year returns. You should have three pivot tables (one
for each return period), and they should appear in the form of the following
(the example below is for a different type of funds).
b) In a text box in the Pivot
Tables sheet, describe the
results of the analysis reflected in the tables. Did active management work? Address
the following:
i) Identify which types
of managers underperformed and which outperformed.
ii) Describe
whether your conclusions differ based on time period.
iii) State what your results indicate about
the validity of active management.
2. Copy Tables
4, 5, and 6 into a sheet that you name Text Functions. Do the following entirely using Excel functions, and
leave the functions intact.
a) First, in a new
column to the right of the data in Table 4, use the TRIM function to remove the
spaces from the left and right of each cell.
b) Next, using whichever
of the “Text Functions” you need from the list in Exhibit 15.3 below, split the Table 4 data so that tickers
all appear in one column and the company names all appear in the adjacent
column. Leave the Excel function names in all the cells.
c) Next, using whichever
text functions you need, combine the
two columns of data from Table 5 into a single column. The format of each cell in
the column should be first name, then a space, then last name.
d) Using
Excel’s manipulation functions, switch
the order of the first and last names for each person in Table 6, and insert a middle initial “X.” for each.
(e.g., David X. Smith)
Leave the
Excel function names in all the cells.
