公司如何利用 Solver 來決定應該承擔哪些專案？
每年，像禮來這樣的公司都需要決定要開發哪些藥物;像 Microsoft 這樣的公司，負責開發哪些軟體程式;像寶 & 潔公司這樣的公司，開發哪些新的消費產品。 Excel 中的求解器功能可以幫助公司做出這些決策。
公司如何利用 Solver 來決定應該承擔哪些專案？
大多數企業希望承接能帶來最大淨現值 (淨現值) 的項目，且資源有限 (通常是資本和勞動) 。 假設一家軟體開發公司正在決定應該從20個軟體專案中接手哪一個。 NPV (每個專案) 投入數百萬美元，資本 (則以數百萬美元) 計，未來三年內所需的程式設計師人數則在檔案 Capbudget.xlsx 的 基礎模型 工作表中列出，該工作表見下一頁圖30-1。 例如，第二計畫的收益為9.08億美元。 第一年需1.51億美元，第二年需2.69億美元，第三年需2.48億美元。 專案二在第一年需要139名程式員，第二年需86名程式員，第三年需83名程式員。 E4：G4 顯示三年內每年 () 數百萬美元的資本，H4：J4 則表示可用的程式設計師數量。 例如，第一年可投入高達25億美元的資金及900名程式設計師。
公司必須決定是否承接每個專案。 假設我們無法承擔軟體專案的一小部分;舉例來說，如果我們分配了0.5的必要資源，那我們就會有一個無法運作的計畫，卻帶來0美元的收入！
在建模情境中，要麼做某件事，要麼不做，關鍵是使用 二元變換儲存格。 二元變化單元永遠等於 0 或 1。 當一個二進位變化的單元格對應到專案時，我們就執行專案。 如果一個二進位變化的儲存格對應到專案等於 0，我們就不會做專案。 你可以透過加入一個限制來設定 Solver 使用一系列二元變動的儲存格——選擇你想使用的變動儲存格，然後在「新增限制」對話框中選擇 Bin。
有了這樣的背景，我們準備好解決軟體專案的選擇問題。 一如既往地，解算器模型會先確定目標格、變動格子及約束條件。
- 目標細胞。 我們最大化選定專案所產生的淨淨值。
- 換細胞。 我們會為每個專案尋找一個 0 或 1 的二進位變化單元。 我已經將這些單元定位在 A6：A25 (，並命名為 range doit) 。 例如，A6 格中的 1 表示我們正在進行專案 1;C6 單元的 0 表示我們不執行專案 1。
- 限制。 我們需要確保每 (年t=1、2、3) ，年t資本使用量小於或等於可用年t資本，且年t勞動力使用量小於或等於可用年t勞動。
如你所見，我們的工作表必須計算任何專案的淨曝光值、每年使用的資本，以及每年使用的程式設計師。 在 B2 格中，我使用 SUMPRODUCT (doit，NPV) 來計算選定專案產生的總 NPV。 (範圍名稱 NPV 指的是範圍 C6：C25.) 對於每個欄位 A 為 1 的專案，此公式會擷取該專案的 NPV;對於每個欄位 A 為 0 的專案，此公式不會擷取該專案的 NPV。 因此，我們能計算所有專案的淨曝光值，目標單元是線性的，因為它是透過相隨 變化單元) * (常數) (的項相加計算的。 同樣地，我透過從 E2 複製到 F2：J2 的公式 SUMPRODUCT (doit，E6：E25) ，計算每年使用的資本和勞動力。
我現在填寫圖 30-2 所示的求解器參數對話框。
我們的目標是最大化選定專案 (B2細胞) 的淨值值。 我們 (名為 doit) 的變動單元是每個專案的二進位變動單元。 限制條件 E2：J2<=E4：J4 確保每年所使用的資本與勞動力均小於或等於可用資本與勞動力。 要新增使變更儲存格變成二元的限制，我會在求解器參數對話框中點選「新增」，然後從對話框中間的列表中選擇 Bin。 新增限制對話框應如圖30-3所示顯示。
我們的模型是線性的，因為目標儲存格是作為 變化儲存格) * (常數 () 的項的總和計算出來的，且資源使用限制是透過比較 (變化儲存格) * (常數) 常數 與常數的總和來計算。
填滿求解器參數對話框後，點擊求解，我們會看到圖 30-1 中先前顯示的結果。 公司可透過選擇專案2、3、6–10、14–16、19及20,000 (92.93億美元的最高淨現值（NPV）達到92.93億美元) 。
處理其他限制條件
有時專案選擇模型會有其他限制。 例如，假設我們選擇專案 3，也必須選擇專案 4。 因為我們目前的最佳解選擇了專案3，但沒有選擇專案4，我們知道目前的解決方案無法維持最佳狀態。 要解決此問題，只需加上一個限制：專案 3 的二元變換儲存格小於或等於專案 4 的二元轉換儲存格。
你可以在檔案 Capbudget.xlsx 的 「如果3再4 」工作表中找到這個範例，該工作表如圖30-4所示。 單元 L9 代表與專案 3 相關的二進位值，而單元 L12 代表與專案 4 相關的二進位值。 透過加入限制 條件 L9<=L12，若選擇專案 3，L9 等於 1，限制條件會使 L12 (專案 4 的二進位) 等於 1。 如果我們沒有選擇專案 3，限制也必須保持專案 4 變動格中的二進位值不受限制。 如果不選擇專案 3，L9 等於 0，而我們的約束允許專案 4 的二進位值等於 0 或 1，這正是我們想要的。 新的最優解如圖30-4所示。
若選擇專案3意味著我們也必須選擇專案4，則計算出一個新的最優解。 現在假設我們只能從專案1到10中做四個專案。 (請參考圖 30-5 所示的「 最多 4 of P1–P10 」工作表。) 在 L8 格中，我們用公式 SUM (A6：A15 計算專案 1 到 10 的二進位值之和) 。 接著我們加入限制 L8<=L10，確保最多只能選取前 10 個專案中的 4 個。 新的最優解如圖30-5所示。 淨曝光值降至90.14億美元。
解決二元與整數規劃問題
線性求解器模型中，若部分或全部變化格子必須是二元或整數，通常比所有變化格子皆可為分數的線性模型更難求解。 因此，我們通常對二元規劃或整數規劃問題的近似最佳解感到滿意。 如果你的求解器模型運行時間很長，建議你在求解器選項對話框中調整公差設定。 (見圖 30-6.) 例如，容忍設定為 0.5% 表示求解器在第一次找到可行解且距離理論最佳目標格值 0.5% 以內時會停止 (理論最佳目標格值是省略二元與整數限制後) 的最佳目標值。 我們常常面臨在10分鐘內找到最優解10%以內的答案，或是在兩週的電腦時間內找到最優解之間做出選擇！ 預設的容忍值為 0.05%，這表示當目標格子值在理論最佳目標格子值的 0.05% 以內時，求解器就會停止操作。
問題
- 一家公司目前有九個專案正在考慮中。 每個專案新增的淨值值及未來兩年所需資本額見下表。 (所有數字均以百萬為單位。) 舉例來說，專案1將增加1400萬美元的淨值，並在第一年支出1200萬美元，第二年則需300萬美元。 第一年可投入5,000萬美元資金用於專案，第二年則有2,000萬美元。
| NPV | 第一年支出 | 第二年支出 | |
|---|---|---|---|
| 計畫一 | 14 | 12 | 3 |
| 計畫二 | 17 | 54 | 7 |
| 第三計畫 | 17 | 6 | 6 |
| 第四計畫 | 15 | 6 | 2 |
| 第五計畫 | 40 | 30 | 35 |
| 第六計畫 | 12 | 6 | 6 |
| 第七計畫 | 14 | 48 | 4 |
| 第八計畫 | 10 | 36 | 3 |
| 第九計畫 | 12 | 18 | 3 |
- 如果我們無法承擔專案的一部分，卻必須全部或全不承擔，我們如何最大化NPV？
- 假設如果進行第四項計畫，則必須進行第五項計畫。 我們該如何最大化NPV？
一家出版社正在決定今年應該出版36本書中的哪一本。 檔案 Pressdata.xlsx 提供了關於每本書的以下資訊：
- 預計收入與開發成本 (數千美元)
- 每本書的頁面
- 本書是否面向軟體開發者， (以E欄中的1標示)
出版公司今年最多可出版8500頁的書籍，且必須出版至少四本針對軟體開發者的書籍。 公司如何最大化利潤？
關於本文
本文改編自 Wayne L. Winston 所著《Microsoft Office Excel 2007 資料分析與商業建模 》。
這本課堂式書籍由知名統計學家及商業教授 Wayne Winston 的一系列演講所開發，他專精於創意且實務性的 Excel 應用。