An investor’s portfolio consists of $680,000 held in 17 mutual funds and exchange-traded funds (ETFs). Each fund spans one or more asset classes. For example, the holdings of fund 11 [Pershares Zacks MicroCap ETF (PZI)] are 0.7% domestic mid cap, 16.3% domestic small cap, 76.0% domestic micro cap, 0.1% international mid cap, 6.7% international small/micro-cap, and 0.3% cash. The spreadsheet “Exam 2 Problem 4 Data.xlsx” shows the asset classes in which the investor wishes to diversify the portfolio (see row 4). The 17 funds are shown in column B, and the current holdings of $40,000 per fund in column C. Cells D5:Q21 indicate the percentages of each fund in each asset class. This allows easy computation of row 22, the total dollar holdings in each asset class. Thus, row 23 shows the actual percentage of the portfolio in each asset class. These percentages deviate from the investor’s desired percentages in each asset class, which are summarized in the table below.

Authors such as William Bernstein, Larry Swedroe, and Burton Malkiel argue that one should periodically rebalance the portfolio, namely, sell and buy funds to bring the portfolio back to the desired allocation depicted in the right-hand column above. It has been shown that rebalancing improves the overall performance of a portfolio because it forces the investor to sell the funds that have grown faster than average (sell high) and buy the funds that have grown slower than the average (buy low). The investor would like to build a Goal Programming model to rebalance the portfolio. How many dollars should be held in each fund so that each of the 14 asset classes come as close to the desired proportion of the portfolio as possible? You should model this problem using 14 two- 4 sided goals with equal penalties for each asset class being above or below each desired proportion. Please formulate and solve your model in excel spreadsheet (You do not need to show the mathematic formulation)

  

Domestic International Asset Class Large Cap (>$10B) Mid Cap ($2B-10B) Small Cap ($200M-2B) Micro Cap ( $10B) Mid Cap ($2B-10B) Small & Micro Cap (