公司如何使用 Solver 來確定它應該承擔哪些項目？
每年，像禮來這樣的公司都需要確定要開發哪些藥物;像 Microsoft 這樣的公司，開發哪些軟件程序;像寶潔 & 賭博這樣的公司，開發哪些新的消費品。 Excel 中的求解器功能可協助公司做出這些決策。
公司如何使用 Solver 來確定它應該承擔哪些項目？
大多數公司都希望開展對淨現值 (NPV) 貢獻最大淨現值的項目，但資源有限 (通常是資本和勞動力) 。 假設一家軟體開發公司正試圖確定它應該承擔 20 個軟體專案中的哪一個。 每個專案) 貢獻的 NPV (，以及未來三年每年) 以百萬美元為單位的資本 (和所需的程式設計師數量，均在檔案 Capbudget.xlsx 的基本 模型 工作表中給出，如下一頁的圖 30-1 所示。 例如，專案 2 的收益為 908,000,000 美元。 第一年需要一億五千一百萬美元，第二年需要二億六千九百萬美元，第三年需要二億四千八百萬美元。 項目 2 在第一年需要 139 名程序員，在第二年需要 86 名程序員，在第三年需要 83 名程序員。 儲存格 E4：G4 顯示三年中每年可用的資本 (以百萬) 美元為單位，儲存格 H4：J4 表示有多少程式設計師可用。 例如，在第一年，將有高達 2.5 億美元的資本和 900 名程序員可用。
公司必須決定是否應該承擔每個項目。 讓我們假設我們無法承擔軟體專案的一小部分;例如，如果我們分配 0.5 個所需資源，我們將有一個不起作用的計劃，這將為我們帶來 0 美元的收入！
在建模您要么做某事或不做某事的情況下，訣竅是使用 二進制更改單元格。 二進位變更儲存格永遠等於 0 或 1。 當對應至專案的二進位變更儲存格等於 1 時，我們執行專案。 如果對應至專案的二進位變更儲存格等於 0，我們就不執行該專案。 您可以透過新增限制式將 [規劃求解] 設定為使用一系列二進位變更儲存格—選取您想要使用的變更儲存格，然後從 [新增限制式] 對話方塊的清單中選擇 [BIN]。
有了這樣的背景，我們已經準備好解決軟體專案選擇問題了。 與求解器模型一樣，我們首先識別目標儲存格、變更儲存格和約束。
- 目標儲存格。 我們將選定項目產生的 NPV 最大化。
- 變更儲存格。 我們會為每個專案尋找 0 或 1 二進位變更儲存格。 我已將範圍 A6：A25 (中找到這些儲存格，並將範圍命名為 doit) 。 例如，儲存格 A6 中的 1 表示我們正在進行專案 1;儲存格 C6 中的 0 表示我們不承接專案 1。
- 限制。 我們需要確保對於每個 t 年 (t=1、2、3) ，使用的 t 年資本小於或等於 t 年可用資本，以及 t 年使用的勞動力小於或等於 t 年可用勞動力。
如您所見，我們的工作表必須計算任何項目選擇的 NPV、每年使用的資本以及程序員每年使用的 NPV。 在儲存格 B2 中，我使用公式 SUMPRODUCT (doit，NPV) 來計算所選專案產生的總 NPV。 (範圍名稱 NPV 是指範圍 C6：C25。) 對於 A 欄中具有 1 的每個專案，此公式會提取專案的 NPV，而對於 A 欄中具有 0 的每個專案，此公式不會提取專案的 NPV。 因此，我們能夠計算所有專案的 NPV，並且我們的目標儲存格是線性的，因為它是透過將遵循形式 (變化儲存格) * (常數) 的項求和來計算的。 以類似的方式，我通過將公式 SUMPRODUCT (doit，E6：E25) 複製到 F2：J2 來計算每年使用的資本和每年使用的勞動力。
現在，我填寫求解參數對話框，如圖 30-2 所示。
我們的目標是在儲存格 B2) (最大化所選專案的 NPV。 名為 doit) 的範圍 (變更儲存格是每個專案的二進位變更儲存格。 限制式 E2：J2<=E4：J4 可確保每年使用的資本和勞動力小於或等於可用的資本和勞動力。 若要新增使變更儲存格成為二進位的限制式，請按一下 [規劃求解參數] 對話方塊中的 [新增]，然後從對話方塊中間的清單中選取 [Bin]。 [新增限制式] 對話方塊應顯示，如圖 30-3 所示。
我們的模型是線性的，因為目標儲存格是計算為具有 變更儲存格) * (常數) 形式的項 (總和，而且因為資源使用限制是透過比較 (變更儲存格) * () 常數 與常數的總和來計算的。
填入「求解器參數」對話方塊後，按一下「求解」，結果如圖 30-1 所示。 該公司可以通過選擇項目 2、3、6-10、14-16、19 和 20) 獲得最高 9,293,000,000 美元 (92.93 億美元的淨現值。
處理其他限制
有時專案選擇模型有其他限制。 例如，假設如果我們選取 [專案 3]，則還必須選取 [專案 4]。 由於我們目前的最佳解決方案會選取 Project 3，而不是 Project 4，因此我們知道目前的解決方案無法保持最佳狀態。 若要解決這個問題，只要新增 Project 3 的二進位變更儲存格小於或等於 Project 4 的二進位變更儲存格的限制即可。
您可以在檔案 Capbudget.xlsx 的 If 3 Then 4 工作表中找到此範例，如圖 30-4 所示。 儲存格 L9 是指與專案 3 相關的二進位值，儲存格 L12 是指與專案 4 相關的二進位值。 藉由新增限制式 L9<=L12，如果我們選擇專案 3，則 L9 等於 1，而我們的限制式會強制 L12 (專案 4 的二進位) 等於 1。 如果不選取 Project 3，限制式也必須讓變更 Project 4 儲存格中的二進位值不受限制。 如果不選取 Project 3，則 L9 等於 0，而我們的限制式允許 Project 4 二進位文件等於 0 或 1，這正是我們想要的。 新最優解如圖 30-4 所示。
如果選取專案 3 表示我們也必須選取專案 4，則會計算新的最佳解。 現在假設我們只能從專案 1 到 10 中執行四個專案。 (參閱 [最多 4 個 P1–P10 ] 工作表，如圖 30-5 所示。) 在儲存格 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 將增加 14,000,000,000 美元的 NPV，並要求在第 1 年支出 12,000,000,000 美元，在第 2 年支出 3,000,000 美元。 在第一年，項目有五千萬美元的資本可用，第二年有兩千萬美元的資金可用。
| NPV | 第 1 年支出 | 第 2 年支出 | |
|---|---|---|---|
| 專案 1 | 14 | 12 | 3 |
| 專案 2 | 17 | 54 | 7 |
| Project 3 | 17 | 6 | 6 |
| 專案 4 | 15 | 6 | 2 |
| Project 5 | 40 | 30 | 35 |
| Project 6 | 12 | 6 | 6 |
| Project 7 | 14 | 48 | 4 |
| Project 8 | 10 | 36 | 3 |
| Project 9 | 12 | 18 | 3 |
- 如果我們不能承擔一個項目的一小部分，但必須承擔全部項目或不承擔項目，我們如何才能最大化 NPV？
- 假設如果開展項目 4，則必須開展項目 5。 我們如何才能最大化 NPV？
一家出版公司正試圖確定今年應該出版 36 本書中的哪本。 檔案 Pressdata.xlsx 提供每本書的以下資訊：
- 預計收入和開發成本 (數千美元)
- 每本書的頁數
- 本書是否面向軟體開發人員， (以 E 欄中的 1 表示)
出版公司今年可以出版總計8500頁的書籍，並且必須出版至少四本面向軟體開發人員的書籍。 公司如何實現利潤最大化？
關於本文
本文改編自 Wayne L. Winston 所著的《 Microsoft Office Excel 2007 Data Analysis and Business Modeling 》。
這本教室式書籍是根據著名的統計學家和商學教授 Wayne Winston 的一系列演講發展而成的，他專門研究 Excel 的創意和實際應用。