|Due By (Pacific Time)
||12/12/2016 12:00 pm
Problem 1: Analyzing real estate database using Table Use Excelâ€™s Table feature and the database provided for this problem to create a Table that shows only the data for single family houses that have not been sold yet. The Table should be organized by the names of the Agent. For each Agent, the Table should show the houses grouped by whether they have a pool or not, and then within each of those groups (i.e., houses with pools and no pool for each agent) sorted in order of List Price (highest to lowest). At bottom of the Table show for the unsold single family houses the total number of houses in this group , total dollar value (at List Price) and average List Price. These values should be properly labeled and the calculations should be done using the features of Excelâ€™s Table and not manually or outside the Table. Problem 2: Analyzing investment portfolio database using Table Using the mutual fund database provided for this problem create a Table and calculate using the Total feature of Tables how much money the portfolio currently has invested in Domestic Value mutual funds and the average market cap of the stocks that the portfolio owns through these funds (i.e., the average of the average market caps of the stocks in these funds). Exclude those funds whose Current Price is within 10% of the fundâ€™s 52 week high or 52-week low (including the boundaries) and return for 2004 equals or exceeds the fundâ€™s average return for the period 2003 to 2007. The printed Table you submit should show the records for the mutual funds that meet the specified criteria, but you can hide some non-essential Fields (columns) to be able to print the Table in one page with easily readable font size. Problem 3: Analyzing real estate database using PivotTable Using the same database as in Prob. 17 and PivotTable show the total number of unsold houses, total dollar value (at List Price) and average List Price for all single family houses that are not sold yet. Your PivotTable should show the data broken down by Agent and then by houses with and without pools for each agent. Problem 4: Analyzing investment portfolio database using PivotTable Using the mutual funds database provided for this problem create a PivotTable to calculate (show) how much money the portfolio currently has invested in Domestic Value mutual funds and the average market cap of the stocks that the portfolio owns through these funds (i.e., the average of the average market caps of the stocks in these funds). Exclude those funds whose Current Price is within 10% of the fundâ€™s 52 week high or 52-week low (including the boundaries) and return for 2004 equals or exceeds the fundâ€™s average return for the period 2003 to 2007. Problem 5: Testing an investment method For this problem use the SPY Adjusted Close data given in the data workbook for Prob 22. In this problem wherever I say close price it means the adjusted close price. (a) Calculate the nominal and real cumulative and annualized total returns (i.e., returns with dividend reinvested) for SPY for the period 12/31/07 to 12/31/12. To calculate the real returns use the Consumer Price Index for All Urban Consumers (CPI-U) data from the Bureau of Labor Statistics website. Note that there is an easy way to do this problem and many harder ways to do this. Try to think of and do it the easiest way, although you will get full credit even if you do it a hard way as long as you get the correct answer. (b) An investor wants to test the 200-day moving average (MA) crossover method for investing in SPY for the period 12/31/08 through 12/31/12 using an initial investment of $100. The 200-day moving average for any day is the average closing price for that day and the previous 199 days. The day counting is based on only trading days. Under this investment method, he will buy SPY at the dayâ€™s closing price on any day the closing price moves above (crosses to the upside) the 200-day MA of SPY and sell his SPY position at the dayâ€™s closing price on any day the closing price falls below (crosses to the downside) the 200-day MA of SPY. The former crossover is called the buy signal and the latter is called the sell signal. He will invest $100 in SPY on the first buy signal after 12/31/08, then sell his entire position at the next sell signal (i.e., go to cash), buy SPY with all the money he has at that point on the next buy signal and so on. The cash he has on his hand on 12/31/12 (if he is in cash at that point) or the value of his SPY position on 12/31/12 is the final value of his investment. (Note that he does not earn any interest on his money when the money is in cash.) Calculate the final value of his investment on 12/31/12 using this method and compare it to the amount of money he would have had if he just bought $100 of SPY on 12/31/08 and held this position to 12/31/12. (c) Redo part (b) assuming that on any day he is out of SPY, i.e., his money is in cash, he earns interest daily on the cash at a 3% annual rate. (Daily interest rate is 1/365th of this rate. For simplicity, ignore weekends and holidays to calculate interest credit. This means, for example, that if he is in cash from a Friday to Monday, he will earn interest for only one day although technically it should be for 3 days.) Note that if SPY is not on a buy signal on 12/31/08, he will hold his $100 in cash (and earn interest on it) until he gets the first buy signal and then proceed as described before.