General recommendations
Read these carefully as they impact your final grade.
1. You must use the template Excel file available on Black Board. The data and the different outputs of your
work must be on the worksheet ‘portfolio analysis’.
2. The requirements above are a minimum. Some points will be allocated for improvements, if they make the
application better. As a consequence, an application meeting only the basic requirements cannot expect the
maximum grade. Original improvements will be specially rewarded.
3. Enhancements can include but are not limited to:
Implementation of alternative risk-adjusted performance measure (implying the creation of another
UDF). For instance, Jensen’s alpha is given by the outperformance (or underperformance) of the
portfolio with respect to the CAPM-implied expected return. Once a CAPM regression is run for the
portfolio under analysis, the Jensen’s alpha is just represented by the estimate of the constant :
The signature of this function could be:
Creative userform design;
Users can navigate through several userforms easily, one userform per task (efficiency frontier,
building the portfolios table, etc.), and only one userform is visible at the same time;
Create a new chart from the GUI for the efficiency frontier (instead of filling a pre-created one);
Allow stock prices to be automatically imported from a text file;
Harder: Allow users to give ticker symbol of stocks, a start date and an end date (and possibly the
choice between weekly, monthly, annual data…) and then use a macro to import data from a website
(only try this if all the basics work well);
Let your creative juices flow for further enhancements… but make enhancements pertinent to the
tasks at hand!
4. The result is not all right or all wrong: some point will be allocated for the quality of the code and the quality
of the user experience! Make a user-friendly application. Respect the ‘best practices’ seen in class.
5. Explain everything the users need to know to use your application, with a special focus on the improvements,
in the worksheet ‘user manual’ (but not in a separate file, as it won’t be read).
6. Make sure that your application works on a PC running Windows / Excel 2007, 2010 or 2013. Do not use
ActiveX controls or other non-standard extensions, as you cannot be sure that these controls will be available
on the computer used to grade your application.
7. You can work in pairs of two for this exam. No groups of more than two are accepted. If you choose to work
in pairs, only upload the exam file ONCE.
8. Submitting your work:
You will see 2 assignments on Blackboard – one for each teacher: use the right one, depending on the
teacher you had in class!
Upload a single Excel file containing all the work, with the names and groups of both partners
in the file name. Example: “Teller-Pierre-A1_Lespagnol-Vivien-B2.xlsm”.
You must also upload the consent form with both names and signatures indicating that you are
aware of penalties for non-adherence to the Academic Honesty policy. Any pieces of work judged
to be too similar will be investigated and heavily sanctioned.
9. DEADLINE: All files must be uploaded before midnight on Sunday March 19, 2017. NO EXCEPTIONS
will be made, late submissions will be penalized: -2 points each day, starting the first minute after the
deadline.
Jensen_alpha(asset1 As Range, asset2 As Range, proportionAsset1 As Double, risk_free As Range, benchmark As Range) As Variant