Assume that George Mason Medical Center had investments in a Building Replacement Fund with the following balances as of 9/18/19 listed by category. The Investment Committee of the Board has approved a recommendation as to how to allocate these funds. Assume that the portfolio was rebalanced on October 1, 2019, to equal the Board's target allocation, and that the investment balances in total on October 1st equaled the $72,250,000 noted on the schedule. Also, assume that the following interest and dividend annual percentage rates that are expected to be earned from October 1 to December 31 for these investment categories (see graph). In this exercise, prepare an Excel spreadsheet with two (2) tabs. The first tab will rebalance the investments and then estimate the amount of interest and dividends that this fund might be expected to earn from October 1 to December 31 (Don’t worry about the compounding of interest or dividends; make it simple). The second tab will be used to summarize your information in a highly readable format that will be presented to the GMMC CFO. Use the first tab of the spreadsheet to compute the budgeted “weighted average” annual investment return for dividends and interest for the fourth quarter of 2019. Prepare this spreadsheet so your calculations clearly show the reader your assumptions and how your calculations were made. When preparing the second tab, which summarizes and communicates the final results in a highly readable format, be sure to use the suggestions from the various resources presented in class.

 

Expected Interest and Description Balance as of Future Desired 9/18/19 $ 4,385,000 28,685,000 13,051,000 11,226,000 4,395,000 3,525,000 6,983,000 Allocation 4.0% 40.0% 18.0% 20.0% Dividend Rate 0.4% 4.3% Cash Fixed Income Large Cap Value Large Cap Growth Mid/Small Cap Value Mid/Small Cap Growth International Equity Emerging Markets Equity Total 1.8% 1.2% 0.8% 0.2% 5.0% 5.0% 6.0% 2.0% 2.1% 0.7% $72,250,000 100.0% Average ?