企業はどのようにして Solver を使用して、どのプロジェクトに取り組むべきかを決定できますか?
毎年、イーライリリーのような企業は、どの薬を開発するかを決定する必要があります。Microsoftのような会社、どのソフトウェアプログラムを開発するか。Proctor & Gambleのような会社は、新しい消費者向け製品を開発します。 Excel のソルバー機能は、企業がこれらの意思決定を行うのに役立ちます。
企業はどのようにして Solver を使用して、どのプロジェクトに取り組むべきかを決定できますか?
ほとんどの企業は、限られたリソース(通常は資本と労働力)を条件として、最大の正味現在価値(NPV)に貢献するプロジェクトを引き受けたいと考えています。 ソフトウェア開発会社が 20 のソフトウェア プロジェクトのうちどれを引き受けるべきかを決定しようとしているとします。 各プロジェクトによって貢献されたNPV(数百万ドル)、および今後3年間に必要な資本(数百万ドル)、およびプログラマの数は、次のページの図30-1に示すファイル Capbudget.xlsx の 基本モデル ワークシートに記載されています。 たとえば、プロジェクト 2 の収益は 9 億 800 万ドルです。 1年目に1億5,100万ドル、2年目に2億6,900万ドル、3年目に2億4,800万ドルが必要です。 プロジェクト 2 では、1 年目に 139 人のプログラマー、2 年目に 86 人のプログラマー、3 年目に 83 人のプログラマーが必要です。 セル E4:G4 は 3 年間に利用可能な資本金 (単位は百万ドル) を示し、セル H4:J4 は利用可能なプログラマーの数を示します。 たとえば、1年目には最大25億ドルの資本と900人のプログラマーが利用可能です。
会社は、各プロジェクトを引き受けるかどうかを決定する必要があります。 ソフトウェア プロジェクトのほんの一部も引き受けることができないと仮定しましょう。たとえば、必要なリソースの 0.5 を割り当てると、0 ドルの収益をもたらす機能していないプログラムが生成されます。
何かをする、またはしない状況をモデリングする際のコツは、 バイナリ変化セルを使用することです。 2 進数が変化するセルは、常に 0 または 1 に等しくなります。 プロジェクトに対応するバイナリ変更セルが 1 に等しい場合、プロジェクトを実行します。 プロジェクトに対応するバイナリ変更セルが 0 の場合、プロジェクトは実行されません。 制約を追加して、バイナリ変更セルの範囲を使用するようにソルバーを設定しました。使用する変化するセルを選択し、[制約の追加] ダイアログ ボックスのリストから [ビン] を選択します。
このような背景により、ソフトウェア プロジェクトの選択の問題を解決する準備ができています。 ソルバー モデルではいつものように、ターゲット セル、変化するセル、制約を識別することから始めます。
- ターゲット セル。 選択したプロジェクトによって生成された NPV を最大化します。
- 変化するセル。 プロジェクトごとに 0 または 1 のバイナリ変更セルを探します。 これらのセルを範囲 A6:A25 で見つけました (範囲に doit という名前を付けました)。 たとえば、セル A6 の 1 は、プロジェクト 1 に着手していることを示します。セル C6 の 0 は、プロジェクト 1 を実施しないことを示します。
- 制約。 年 t (t=1、2、3) ごとに、年 t の使用資本が Year t 以下であり、Year 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 により、各年に使用される資本と労働力が利用可能な資本と労働力以下になるようになります。 変化するセルをバイナリにする制約を追加するには、[ソルバー パラメーター] ダイアログ ボックスで [追加] をクリックし、ダイアログ ボックスの中央にある一覧から [ビン] を選択します。 「制約の追加」ダイアログ・ボックスは、図30-3に示すように表示されます。
このモデルは線形です。ターゲット セルは形式 (変化するセル)*(定数) を持つ項の合計として計算され、リソース使用制約は (変化するセル)*(定数) の合計を定数と比較することによって計算されるためです。
「ソルバ・パラメータ」ダイアログ・ボックスに入力した状態で「解決」をクリックすると、図30-1に示した結果が表示されます。 同社は、プロジェクト 2、3、6-10、14-16、19、および 20 を選択することで、最大 92 億 9,300 万ドル (92 億 9,300 万ドル) の NPV を得ることができます。
その他の制約の処理
プロジェクト選択モデルには他の制約がある場合があります。 たとえば、プロジェクト 3 を選択した場合は、プロジェクト 4 も選択する必要があるとします。 現在の最適ソリューションではプロジェクト 4 ではなくプロジェクト 3 が選択されるため、現在のソリューションでは最適のままではできないことがわかっています。 この問題を解決するには、単純に、プロジェクト 3 のバイナリ変更セルがプロジェクト 4 のバイナリ変更セル以下であるという制約を追加します。
この例は、図30-4に示すファイル Capbudget.xlsx の If 3 then 4 ワークシートにあります。 セル L9 は Project 3 に関連するバイナリ値を参照し、セル L12 は Project 4 に関連するバイナリ値を参照します。 制約 L9<=L12 を追加すると、プロジェクト 3 を選択すると、L9 は 1 に等しく、制約により L12 (プロジェクト 4 バイナリ) が 1 に等しくなります。 また、Project 3 を選択しない場合は、Project 4 の変更セルのバイナリ値を制限しないままにしておく必要があります。 プロジェクト 3 を選択しない場合、L9 は 0 に等しく、制約によりプロジェクト 4 バイナリは 0 または 1 になります。これは必要な値です。 新しい最適解を図30-4に示します。
プロジェクト 3 を選択することで、プロジェクト 4 も選択する必要がある場合、新しい最適解が計算されます。 ここでは、プロジェクト 1 から 10 の間で 4 つのプロジェクトしか実行できないとします。 (図 30-5 に示す P1 から P10 のワークシートの最大 4 つを参照してください。セル L8 で、プロジェクト 1 から 10 に関連付けられたバイナリ値の合計を、数式 SUM(A6:A15) で計算します。 次に、制約 L8<=L10 を追加します。これにより、最初の 10 個のプロジェクトのうち最大で 4 つが選択されます。 新しい最適解を図30-5に示します。 NPVは90億1,400万ドルに低下した。
二項および整数プログラミングの問題を解決する
一部またはすべての変化するセルが 2 進数または整数である必要がある線形ソルバー モデルは、通常、変化するすべてのセルが分数である線形モデルよりも解くのが困難です。 このため、私たちは多くの場合、2 進または整数プログラミングの問題に対するほぼ最適な解に満足します。 ソルバー モデルの実行時間が長い場合は、[ソルバー オプション] ダイアログ ボックスで [許容値] 設定の調整を検討することをお勧めします。 (図 30-6 を参照)。たとえば、[許容値] を 0.5% に設定すると、ソルバーは、理論上の最適ターゲット セル値の 0.5% 以内の実現可能な解を初めて見つけたときに停止します (理論上の最適ターゲット セル値は、2 進制約と整数制約を省略した場合に検出される最適ターゲット値です)。 多くの場合、私たちは 10 分で最適の 10% 以内の答えを見つけるか、2 週間のコンピューター時間で最適な解決策を見つけるかの選択に直面します。 既定の許容値は 0.05% です。つまり、ソルバーは、理論上の最適ターゲット セル値の 0.05% 以内にあるターゲット セル値が見つかると停止します。
問題
- ある会社には 9 つのプロジェクトが検討中です。 今後 2 年間に各プロジェクトによって追加された NPV と各プロジェクトが必要とする資本金を次の表に示します。 (すべての数字は百万単位です。たとえば、プロジェクト 1 では NPV が 1,400 万ドル追加され、1 年目に 1,200 万ドル、2 年目に 300 万ドルの支出が必要になります。 1年目はプロジェクトに5,000万ドル、2年目は2,000万ドルの資金を利用できます。
| NPV | 1 年目の支出 | 2 年目の支出 | |
|---|---|---|---|
| プロジェクト 1 | 14 | 12 | 3 |
| プロジェクト 2 | 17 | 54 | 7 |
| プロジェクト 3 | 17 | 6 | 6 |
| プロジェクト 4 | 15 | 6 | 2 |
| プロジェクト 5 | 40 | 30 | 35 |
| プロジェクト 6 | 12 | 6 | 6 |
| プロジェクト 7 | 14 | 48 | 4 |
| プロジェクト 8 | 10 | 36 | 3 |
| プロジェクト 9 | 12 | 18 | 3 |
- プロジェクトのほんの一部を引き受けることができず、プロジェクトのすべてまたはまったく引き受けなければならない場合、どうすれば NPV を最大化できますか?
- プロジェクト 4 が実施される場合、プロジェクト 5 も実施する必要があるとします。 NPVを最大化するにはどうすればよいでしょうか?
ある出版社は、今年 36 冊の本のうちどれを出版するかを決定しようとしています。 ファイル Pressdata.xlsx には、各ブックに関する次の情報が記載されています。
- 予想される収益と開発コスト (数千ドル)
- 各書籍のページ数
- 本がソフトウェア開発者を対象としているかどうか (列 E に 1 で示されます)
出版社は今年、合計8500ページの本を出版でき、ソフトウェア開発者向けの本を少なくとも4冊出版する必要があります。 どうすれば会社は利益を最大化できるでしょうか?
記事について
この記事は、Wayne L. Winston 著『 Microsoft Office Excel 2007 Data Analysis and Business Modeling 』から引用されたものです。
このクラスルーム スタイルの本は、Excel の創造的で実用的なアプリケーションを専門とする著名な統計学者およびビジネス教授である Wayne Winston による一連のプレゼンテーションから作成されました。