Question

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.

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

Answer 1

Particular	Balance on 09/18/19	Future Desired Allocation(in%)	Future Desired Allocation(in$)	Expected interest and dividend Rate(in %)	Expected interest and dividend Rate(in$)	Return
Cash	4385000	4%	2890000	0.4%	2913.75	0.10%
Fixed Income	28685000	40%	28900000	4.3%	313228.49	1.08%
Large Cap Volume	13051000	18%	13005000	1.8%	59003.51	0.45%
Large Cap Growth	11226000	20%	14450000	1.2%	43706.30	0.30%
Mid/Small Cap Value	4395000	5%	3612500	0.8%	7284.38	0.20%
Mid/Small Cap Growth	3525000	5%	3612500	0.2%	1821.10	0.05%
International Equity	6983000	6%	4335000	2.1%	22945.81	0.53%
Emerging Market Equity	-	2%	1445000	0.7%	2549.53	0.18%
Total	72250000	Total	72250000		Average	0.36%