The instructions are in the attachments however in STEP 2 part 3, you are asked for the company assigned. The company that has been assigned to me is INCO Homes Corporation. Also, please see the the time frame from which you have to find the data.1. SP500 data
2. Gold data
3. Three Month Treasury Bill data
4. My company (B C B Bancorp Inc) data
GOAL OF PROJECT 2:
Develop your Excel and financial modeling skills.
SITUATION AND AUDIENCE:
You’re a portfolio manager at an asset management firm.
READINGS:
ECO 204 Excel videos on Youtube and the Costco, Dell, US T-Bills Portfolio Excel Model discussed in lectures.
DATA AND TOOLS:
Install the FRED Excel add-in from here.
Download the following data:
In worksheet “A1”: use FRED to download the S&P 500 Index (SP500) monthly series through December 2014. To do this, type “SP500” in cell A1 type “m” in cell A3 click the button “Get FRED data”. You’ll have to delete some rows to get a data set through December 2014. Don’t delete the “dates” column – you’ll need it later on. Note: FRED’s SP500 monthly series reports the S&P 500 index at the close of the last trading day of the previous month. For example, the February 1st 1957 value is the S&P 500 index on the last trading day of January 1957.
In worksheet “A2”: use FRED to download the US 3-month T-Bills (TB3MS) interest rates series through December 2014. To do this, type “TB3MS” in cell A1 type “m” in cell A3 click the button “Get FRED data”. You’ll have to delete some rows to get a data set through December 2014. Don’t delete the “dates” column – you’ll need it later on. Note: FRED’s TB3MS series reports the monthly interest rate (in percentage terms) on the first day of each month.
Please see Appendix A for the company assigned by the last 6 digits of your student ID number. In worksheet “A3”: use CRSP to download the following monthly data for your assigned company through December 2014: share price on the last trading day of each month, returns, and returns without dividends. Don’t delete the “dates” column – you’ll need it later on. Note: If data for your assigned company is missing, please e-mail Ajaz
University of Toronto, Department of Economics, ECO 204, 2015 – 2016
2
for what to do next. Note: Here’s how you access U of T “restricted” data bases from off-campus locations: http://guides.library.utoronto.ca/proxy.
In worksheet “A4”: use FRED to download monthly gold prices through December 2014. Don’t delete the “dates” column – you’ll need it later on. Note: FRED has several series on (daily) gold prices. You can choose any one of these daily series (can you “force” FRED to give you gold prices on a monthly basis )
In worksheet “A5”: copy and paste your company’s “date”, prices, returns, and returns without dividends series from worksheet “A3” into worksheet “A5”. Next, use the “vlookup” function to “pull” the S&P 500 monthly series, the US 3-month T-Bills monthly interest rate series, and the monthly gold price series into worksheet “A5”. Delete all rows with missing data. Note: The data set in worksheet “A5” will consist of data from the first month of your company’s data set (in worksheet “A3”) through December 2014.
In worksheet “A5”: create the following additional columns of data:
Monthly returns on the S&P 500 index.
Monthly returns on gold.
A “dummy variable” which takes the value 1 if your company’s returns were positive and 0 otherwise.
A “dummy variable” which takes the value 1 if the S&P 500 returns were positive and 0 otherwise.
A “dummy variable” which takes the value 1 if gold returns were positive and 0 otherwise.
In worksheet “A6”: compute the sample mean and standard deviation of your company’s returns, S&P 500 returns, “gold” returns, and the monthly interest rate on US 30-day T-Bills. Also, compute the sample covariance between your company’s returns, S&P 500 returns, and “gold” returns.
Comment on your results (within worksheet “A7”).
TASKS:
In worksheet “A7”: use “pivot tables” to make the following two tables:
(a) The first table showing the number of times that your company’s returns increased or decreased/stayed constant versus the number of times that the S&P 500 returns increased/decreased or remained constant.
(b) The second table showing the number of times that the returns on gold increased/decreased or remained constant versus the number of times that the S&P 500 returns increased or decreased/stayed constant.
Here is an example showing the number of times that American Express and S&P 500 returns went up or decreased/stayed the same (from December 1972 to December 2011):
University of Toronto, Department of Economics, ECO 204, 2015 – 2016
3
S&P 500 Monthly Returns
Increased
Decreased or
No Change
Total
American Express
Monthly Returns
Increased
183
81
264
Decreased or No Change
100
101
204
Total
283
185
468
In worksheet “A8”: copy and paste the S&P 500 index series (from worksheet “A1”). Next, use the “vlookup” function to pull monthly gold prices (from worksheet “A4”) into worksheet “A8”. Delete all rows with missing data. Compute the sample covariance between S&P 500 returns and Gold returns:
(a) for the entire data set (b) for data through December 1978 (c) for data after January 1979 through December 2014
Comment on your results (within worksheet “A8”).
In worksheet “A9”: Suppose it is January 1st, 2014 and your client wants to invest funds for a month in a portfolio consisting of a “risk free asset” and shares in the company assigned to you (on the basis of the last 5 digits of your ID number). Use the January 1st 2014 monthly interest rate in worksheet “A2” as the risk free rate in January 2014.
Assume your client has a mean-variance utility function over portfolio return and variance. Calculate the value of the utility parameter at which 100% of the portfolio is invested in the “risky” asset.
Use Solver to calculate the fraction of the portfolio invested in the risky asset and the risk free asset for the case when the mean-variance utility parameter is:
If your ID # ends with the numbers 0, 2, 4, 6, 8 then use: =1.02 ×Value of parameter in step If your ID # ends with the numbers 1, 3, 5, 7, 9 then use: =0.98 ×Value of parameter in step
In worksheet “A10”: Suppose it is January 1st, 2014. Use Solver to construct a synthetic asset formed by blending a fraction of your assigned company’s share, a fraction of a share in an ETF which tracks the S&P 500, and a fraction of a troy ounce of gold.
In worksheet “A11”: Suppose it is January 1st, 2014 and your client wants to invest funds for one month in a portfolio consisting of a risk free asset and a “synthetic” asset formed by blending a fraction of your assigned company’s share, a fraction of a share in an ETF which tracks the S&P 500, and a fraction of a troy ounce of gold. Use the January 1st 2014 monthly interest rate in worksheet “A2” as the risk free rate in January 2014.
Assume your client has a mean-variance utility function over portfolio return and variance. Calculate the value of the utility parameter at which 100% of the portfolio is invested in the “synthetic” risky asset.
Use Solver to calculate the fraction of the portfolio invested in the “synthetic” risky asset and the risk free asset for the case when the mean-variance utility parameter is:
If your ID # ends with the numbers 0, 2, 4, 6, 8 then use: =1.02 ×Value of parameter in step If your ID # ends with the numbers 1, 3, 5, 7, 9 then use: =0.98 ×Value of parameter in step
University of Toronto, Department of Economics, ECO 204, 2015 – 2016
4
Comment on your results (within worksheet “A11”).
In worksheet “A12”: Suppose it is January 1st, 2014 and your client wants to invest funds for one month in a portfolio consisting of a risk free asset and a “synthetic” asset formed by blending a fraction of your company’s share, a fraction of a share in an ETF which tracks the S&P 500, and a fraction of a troy ounce of gold. Use the January 1st 2014 monthly interest rate in worksheet “A2” as the risk free rate in January 2014.
Identify the least risky of the following risky assets: your assigned company, S&P 500, and gold.
Assume your client has a mean-variance utility function over portfolio returns and variance. Calculate the value of the utility parameter for which 100% of the portfolio is invested in the “synthetic risky” asset (this value of should be identical to the value of in task – )
Use the following value for the mean-variance utility parameter:
If your ID # ends with the numbers 0, 2, 4, 6, 8 then use: =1.02 ×Value of parameter in step If your ID # ends with the numbers 1, 3, 5, 7, 9 then use: =0.98 ×Value of parameter in step
Use Solver to calculate the fraction of the portfolio invested in the “synthetic” risky asset and the risk free asset subject to the constraint that the portfolio risk is less than or equal to the risk of the least risky asset identified in step . Use Solver to generate “limits” and “sensitivity” reports.
Comment on your results (within worksheet “A12”).
Use the “limits” and “sensitivity” reports in task to answer the following question: “what is the impact on the portfolio return in step when the risk of the least risky asset in step increases by 1% Explain your answer in the relevant report (i.e. answer or sensitivity report).
TECHNICAL INSTRUCTIONS FOR PROJECT 2:
Submission: Please submit this project through the “Project 2 Assignment” Tab in ECO 204 Blackboard by 11 pm, Wednesday, December 30th, 2015. The Excel file must be named Lastname_Firstname. Penalty for late submissions: 5% for each day after deadline. It is your responsibility to name the file as it appears in ROSI. Failure to do so will result in a 5% penalty per incident.
If Blackboard is officially down at the due time and date (see example1) then the deadline of this project is automatically extended by 24 hours; if after 24 hours Blackboard is still down then the deadline of this project is automatically extended by 24 hours, and so on. Do not e-mail the project to the course staff.
1 For example: the project is due on December 1st 11 pm and Blackboard is “down for maintenance on December 1st from 6 pm to midnight”.
Our Service Charter
-
Excellent Quality / 100% Plagiarism-Free
We employ a number of measures to ensure top quality essays. The papers go through a system of quality control prior to delivery. We run plagiarism checks on each paper to ensure that they will be 100% plagiarism-free. So, only clean copies hit customers’ emails. We also never resell the papers completed by our writers. So, once it is checked using a plagiarism checker, the paper will be unique. Speaking of the academic writing standards, we will stick to the assignment brief given by the customer and assign the perfect writer. By saying “the perfect writer” we mean the one having an academic degree in the customer’s study field and positive feedback from other customers. -
Free Revisions
We keep the quality bar of all papers high. But in case you need some extra brilliance to the paper, here’s what to do. First of all, you can choose a top writer. It means that we will assign an expert with a degree in your subject. And secondly, you can rely on our editing services. Our editors will revise your papers, checking whether or not they comply with high standards of academic writing. In addition, editing entails adjusting content if it’s off the topic, adding more sources, refining the language style, and making sure the referencing style is followed. -
Confidentiality / 100% No Disclosure
We make sure that clients’ personal data remains confidential and is not exploited for any purposes beyond those related to our services. We only ask you to provide us with the information that is required to produce the paper according to your writing needs. Please note that the payment info is protected as well. Feel free to refer to the support team for more information about our payment methods. The fact that you used our service is kept secret due to the advanced security standards. So, you can be sure that no one will find out that you got a paper from our writing service. -
Money Back Guarantee
If the writer doesn’t address all the questions on your assignment brief or the delivered paper appears to be off the topic, you can ask for a refund. Or, if it is applicable, you can opt in for free revision within 14-30 days, depending on your paper’s length. The revision or refund request should be sent within 14 days after delivery. The customer gets 100% money-back in case they haven't downloaded the paper. All approved refunds will be returned to the customer’s credit card or Bonus Balance in a form of store credit. Take a note that we will send an extra compensation if the customers goes with a store credit. -
24/7 Customer Support
We have a support team working 24/7 ready to give your issue concerning the order their immediate attention. If you have any questions about the ordering process, communication with the writer, payment options, feel free to join live chat. Be sure to get a fast response. They can also give you the exact price quote, taking into account the timing, desired academic level of the paper, and the number of pages.