使用規劃求解進行大寫預算

大部分的公司會想要利用在 NPV) (提供最大凈現值的專案，但資源 (通常是大寫和人力) 。 假設軟體開發公司正嘗試判斷它應該在20個軟體專案中哪一個承擔。 NPV (由每個專案) 貢獻的數百萬美元，以及以數百萬美元) 的大寫 (，以及未來三年所需的程式設計人員數目，都會放在檔案 Capbudget.xlsx 中的基本模型 工作表上，並顯示在下一頁的圖 30-1 中。 例如，Project 2 的收益為 $9.08 億美元。 第 1 年需要 1.51 億美元、第 2 年需要 2.69 億美元，第 3 年需要 2.48 億美元。 Project 2 在第 1 年需要 139 名程式設計人員、第 2 年的 86 名程式設計師，以及第 3 年的 83 名程式設計師。 單元格 E4：G4 會以數百萬美元) 在三年內提供大寫 (，而單元格 H4：J4 則表示有多少程序設計師可以使用。 例如，在第 1 年最多可使用 25 億美元大寫和 900 名程式設計人員。

公司必須決定是否要讓每個項目成為備份專案。 讓我們假設我們無法假設軟體專案的一小部分;舉例來說，如果我們配置 0.5 個所需的資源，我們就會有一個會給我們帶來 $0 營收的非工作計劃！

在模型情況下，您執行或不執行某項動作的訣竅是使用 二進位變更單元格。 二進位變更單元格一律等於 0 或 1。 當二進位變更單元格對應至專案等於 1 時，我們會執行專案。 如果二進位變更單元格對應至專案等於 0，我們不會執行專案。 您設定 [規劃求解] 使用範圍的二進位變更單元格，方法是新增限制式—選取您要使用的變更單元格，然後從 [新增限制式] 對話框的清單中選擇 [Bin]。

在這個背景中，我們已準備好解決軟體項目選取問題。 一如往常使用規劃求解模型，我們會從識別目標單元格、變更的單元格和限制式開始。

• 目標儲存格。 我們將選取的專案所產生的 NPV 最大化。

• 變更儲存格。 我們為每個項目尋找一個0或1個二進位變更單元格。 我已經在範圍 A6：A25 (中找到這些儲存格，並將範圍命名為 doit) 。 例如，單元格 A6 中的 1 表示我們正在合併 Project 1;單元格 C6 中的 0 表示我們不承擔 Project 1。

• 約束。 我們需要確保每年 都 (t=1、2、3) ，使用的年 份用量 小於或等於 Year t 大寫可用，而使用年 份 t 的人力小於或等於可用的 Year t 人力。

如您所見，我們的工作表必須針對 NPV、每年使用之大寫以及每年使用的程式設計人員等任何選取專案進行計算。 在儲存格 B2 中，我使用公式 SUMPRODUCT (doit，NPV) 來計算所選項目產生的 NPV 總數。 (範圍名稱 NPV 是指範圍 C6：C25.) 針對欄 A 中有 1 個的專案，此公式會挑選專案的 NPV，而針對欄 A 中有 0 的每個專案，此公式不會挑選專案的 NPV。 因此，我們可以計算所有專案的 NPV，而且目標儲存格是線性的，因為它是透過加總表單後面的字詞 來計算， (變更單元格) * (常數) 。 以類似的方式，我計算了每年所用的金額，以及將 E2 複製到 F2：J2 公式 SUMPRODUCT (doit，E6：E25) 所用的工時。

我現在填寫 [規劃求解參數] 對話框，如圖 30-2 中所示。

我們的目標是將所選專案的 NPV 最大化 (儲存格 B2) 。 變更儲存格 (名為 doit) 的範圍是每個專案的二進位變更儲存格。 限制 式 E2：J2<=E4：J4 可確保每年使用之大寫和工時小於或等於可用的大寫和工時。 若要新增使儲存格變成二進位的限制式，請按兩下 [規劃求解參數] 對話框中的 [新增]，然後從對話框中間的清單中選取 [Bin]。 [新增限制式] 對話框應該會顯示在圖 30-3 中。

我們的模型是線性的，因為目標單元格會計算為具有窗體的字詞總和 ， (變更單元格) * (常數) ，而且資源使用限制式是透過比較 (單元格 ) * (常數) 至常數的總和來計算。

填入 [規劃求解參數] 對話框后，按兩下 [求解]，我們便會看到先前顯示於圖 30-1 中的結果。 公司選擇 [專案 2]、[3]、[6-10]、[14-16]、[19] 和 [20]，) 最多可獲得 92.93 億 ($92930 億美元 NPV。

有時候項目選擇模型會有其他限制式。 例如，假設我們選取 Project 3，也必須選取 Project 4。 因為我們目前的最佳解決方案選取的是 Project 3，而非 Project 4，因此我們知道目前的解決方案無法維持最佳狀態。 若要解決此問題，只要新增限制式，即二進位變更 Project 3 的儲存格小於或等於 Project 4 的二進位變更單元格。

您可以在檔案 Capbudget.xlsx 的 If 3 然後 4 工作表上找到此範例，此範例顯示在圖 30-4 中。 單元格 L9 是指與 Project 3 相關的二進位值，而單元格 L12 是指與 Project 4 相關的二進位值。 新增限制式 L9<=L12，如果我們選擇 Project 3，L9 等於 1，而我們的限制式強制 L12 (Project 4 二進位) 等於 1。 如果我們未選取 Project 3，我們的限制式也必須將二進位值保留在變更 Project 4 單元格中不受限制。 如果我們沒有選取 Project 3，L9 等於 0，而我們的限制式允許 Project 4 二進位等於 0 或 1，這是我們想要的。 新的最佳解決方案會顯示在圖 30-4 中。

如果選取 Project 3，則會計算新的最佳解決方案，表示我們也必須選取 Project 4。 現在假設我們只能從專案 1 到 10 之間執行四個專案。 (查看 P1-P10 中最多 4 個工作表，顯示在圖 30-5.) 在單元格 L8 中，我們會使用公式 SUM (A6：A15 ) ，計算與 Project 1 到 10 關聯的二進位值總和。 然後我們新增限制式 L8<=L10，這可確保在前 10 個專案中最多選取 4 個專案。 新的最佳解決方案會顯示在圖 30-5 中。 NPV 已降至 $90.14 億美元。

線性規劃求解模型，其中部分或所有變更的單元格都必須是二進位或整數，通常比允許所有變更單元格為分數的線性模型更難求解。 因此，我們通常會滿意二進位或整數程式設計問題的接近最佳解決方案。 如果您的規劃求解模型執行很長一段時間，建議您考慮在 [規劃求解選項] 對話框中調整 [容差] 設定。 (請參閱圖 30-6.) 例如，0.5% 的容差設定表示「規劃求解」會在第一次找到符合優化目標單元格值 0.5% 的可行解決方案時停止， (在省略二進位和整數限制式) 時找到的最佳目標單元格值。 我們通常會遇到一個選擇：在10分鐘內找到最佳答案的10%以內，或是在電腦使用時間的兩周內找到最佳解決方案！ 默認的容差值為 0.05%，這表示「規劃求解」會在找到符合優化目標儲存格值的 0.05% 以內的目標儲存格值時停止。

1. 一家公司有九個項目在考慮之中。 下表顯示每個專案所新增的 NPV，以及未來兩年每個專案所需的大寫。 (所有數位皆為百萬 ) 例如，Project 1 會在 NPV 中增加 $1400 萬美元，而在第 1 年需要支出 1200 萬美元，而在第 2 年則需要 300 萬美元。 在第 1 年，專案可使用 5 千萬美元大寫，而第 2 年則提供 2 千萬美元。

	Npv	第1年支出	第 2 年支出
Project 1	14	12	3
Project 2	17	54	7
Project 3	17	6	6
Project 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？

• 假設執行 Project 4，則必須執行 Project 5。 如何最大化 NPV？

• 一家發佈公司正嘗試判斷今年應該發佈 36 種書籍中的哪一種。 檔案 Pressdata.xlsx 提供下列每本書籍的相關信息：

  • 預計營收及開發成本 (以數千美元計)

  • 每本書籍中的頁面

  • 書籍是否面向軟體開發人員 (E 欄中的 1 欄)

    一家發佈公司今年最多可以發佈8500頁的書籍，而且必須發佈至少四本適用於軟體開發人員的書籍。 公司如何將利潤最大化？

本文是從 Microsoft Office Excel 2007 Data Analysis and Business Modeling by Google L. Winston 所修改。

此教室樣式書籍是從一系列簡報中所開發而成，由範例中的知名統計學和商務教授 Winston 所開發，他專為 Excel 的創意與實用應用程式專長。