美國大聯盟球場之旅 選擇優於市場的投資組合 決策支援系統補充範例 美國大聯盟球場之旅 選擇優於市場的投資組合

美國大聯盟球場之旅 貝琪剛完成了她在華盛頓大學商學院的MBA學位，想利用暑期開車到美國各地旅遊。 她想要到每個有美國聯盟球隊的城市去觀看大聯盟球賽。 然後回到家鄉西雅圖，以便於秋季時開始她的工作。 問題：何種旅遊的途徑可以使得旅行的距離為最短？

美國大聯盟球場之旅（續）

美國大聯盟球場之旅問題 尚未求解之試算表模式 Figure 8.28 A spreadsheet model for determining the minimum-distance route from Seattle to the other 13 American League cities and back to Seattle. The changing cells are Route (D22:P22) and the target cell is Total Miles Traveled (Q26).

alldifferent 限制式 進化Solver有一種類型的限制式，叫做alldifferent，它會強迫 n 個變動儲存格從1~n中選擇不重複的整數。 Figure 8.29 The Add Constraint dialogue box showing the alldifferent constraint.

美國大聯盟球場之旅問題 求解後之試算表模式 Figure 8.30 After adding the alldifferent constraint, Route (D22:P22) and C23:Q23 show the route from Seattle to the other 13 American League cities and back to Seattle that has been obtained by Evolutionary Solver.

進化 Solver 的優勢與劣勢 進化Solver在求解困難的非線性規劃問題中，與標準Solver相比有兩項優勢： 所找到的最好解答不一定是最佳解（雖然也許非常靠近）。

選擇優於市場的投資組合 投資經理人通常想要達成的目標是績效優 於市場。 若我們假設過去的績效對未來有一些指標 作用，那麼選擇一個可以在過去打敗市場 最多次的投資組合也許在未來將最有可能 打敗市場。 考慮五家在紐約證券交易所（New York Stock Exchange, NYSE）交易的大公司： 迪士尼 Disney (DIS) 波音 Boeing (BA) 奇異 General Electric (GE) 寶僑 Procter & Gamble (PG) 麥當勞 McDonald’s (MCD) 問題：應該選擇這五檔股票怎樣的組合有可能在未來優於市場績效？

試算表模式 Figure 8.22 A spreadsheet model (prior to using the Premium Solver dialogue box) for selecting a portfolio that beat the market most frequently in recent quarters. A starting solution has been entered in the changing cells Portfolio (D31:H31). The target cell is Number of Quarters Beating the Market (J36).

高等規劃求解（Premium Solver）對話方塊 Figure 8.23 The Premium Solver dialogue box that is used to complete the spreadsheet model introduced in Figure 8.22. Selecting Standard Evolutionary from the dropdown menu specifies that Evolutionary Solver will be used to solve the problem.

進化 Solver General Options 對話方塊 Figure 8.24 This Solver Options dialogue box for Premium Solver provides several parameters for Evolutionary Solver. The default values shown here are reasonable choices for most small applications.

Limit Options 對話方塊 Figure 8.25 This Limit Options dialogue box provides additional control over when the search conducted by Evolutionary Solver should be terminated.

進化 Solver 試算表解 Figure 8.26 After clicking the Solve button in the Premium Solver dialogue box, Evolutionary Solver found the solution shown in the changing cells Portolio (D31:H31) for the model formulated in Figures 8.22 and 8.23. The target cell Number of Quarters Beating the Market (J36) indicates that this portfolio beat the market in 18 of these 24 quarters. Clicking Solve again probably would lead to at least a slightly different solution for the portfolio.