Microsoft アカウントでサインイン
サインインまたはアカウントを作成してください。
こんにちは、
別のアカウントを選択してください。
複数のアカウントがあります
サインインに使用するアカウントを選択してください。

会社がソルバーを使用して実施するプロジェクトを決定する方法

毎年、Eli Lilly のような会社は、開発する薬を決定する必要があります。開発するソフトウェア プログラムである Microsoft のような会社。Proctor のような会社&、新しいコンシューマー製品を開発する必要があります。 この機能のソルバー Excel、企業がこれらの決定を下すのに役立ちます。

ほとんどの企業は、限られたリソース (通常は資本と労働) に応じて、最大の正味現在価値 (NPV) を提供するプロジェクトを実施したいと考えます。 ソフトウェア開発会社が、実行する必要がある 20 のソフトウェア プロジェクトのどちらを決定しようとしているとします。 各プロジェクトによって提供される NPV (数百万ドル) と、資本 (数百万ドル) と、次の 3 年間に必要なプログラマの数は、次のページの図 30-1 に示されているファイル Capbudget.xlsx の基本モデル ワークシートに示されています。 たとえば、2 をProjectすると、9 億 8,000 万ドルが得られるとします。 1 年目には 1 億 5,100 万ドル、2 年目には 2 億 6,900 万ドル、3 年目には 2 億 4,800 万ドルが必要です。 Project 2 には、1 年目には 139 人のプログラマ、2 年目には 86 人のプログラマ、3 年目には 83 人のプログラマが必要です。 セル E4:G4 は、3 年間に利用できる資本 (数百万ドル) を示し、セル 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 は 1 を実行Projectします。

  • 制約。 各年 t (t=1, 2, 3) に対して、使用される年 t 資本が利用可能な年 t 資本以下であり、使用された年 t の労働が利用可能な年 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 に示すように、[ソルバー パラメーター] ダイアログ ボックスに入力します。

本の画像

目標は、選択したプロジェクト (セル B2) の NPV を最大化することです。 変更するセル ( doit という名前の範囲) は、各プロジェクトのバイナリ変更セルです。 制約 E2:J2<=E4:J4 では、毎年、使用される資本と労働が、利用可能な資本と労働量以下になります。 セルの変更をバイナリにする制約を追加するには、[ソルバー パラメーター] ダイアログ ボックスで [追加] をクリックし、ダイアログ ボックスの中央にある一覧から [Bin] を選択します。 図 30-3 に示すように、[制約の追加] ダイアログ ボックスが表示されます。

本の画像

このモデルは、ターゲット セルがフォーム (変更セル )*(定数) を持つ用語の合計として計算され、リソース使用量の制約が (変化するセル )*(定数) の合計を定数と比較して計算されるので線形です。

[ソルバー パラメーター] ダイアログ ボックスに入力した [解決] をクリックすると、図 30-1 で前に示した結果が表示されます。 プロジェクト 2、3、6~ 10、14 ~ 16、19、20 を選択すると、最大 NPV 9,293 百万ドル (92 億 9,300 万ドル) を取得できます。

プロジェクト選択モデルに他の制約がある場合があります。 たとえば、3 を選択した場合Project 4 を選択Projectします。 現在の最適なソリューションでは 3 Project選択されますが、Project 4 では選択されないので、現在のソリューションは最適な状態を維持できないとわかっています。 この問題を解決するには、Project 3 のバイナリ変更セルが 4 の 2 進変更セル以下の制約Projectします。

この例は、図 30-4 に示すように、ファイル Capbudget.xlsx の If 3 then 4 ワークシートで確認できます。 セル L9 は、セル 3 に関連する 2 進値Project、セル L12 を 4 の値に関連する 2 進値Projectします。 制約 L9<=L12 を追加すると、Project 3 を選択した場合、L9 は 1 に等しくなります。制約では、L12 (Project 4 バイナリ) に 1 が強制的に適用されます。 また、3 を選択しない場合、制約は Project 4 の変化するセルの 2 進数Project無制限のままにする必要があります。 Project 3 を選択しない場合、L9 は 0 に等しくなります。制約により、Project 4 バイナリは 0 または 1 に等しくなります。これは必要です。 新しい最適なソリューションを図 30-4 に示します。

本の画像

新しい最適なソリューションは、3 を選択Project、4 を選択する必要Projectされます。 次に、プロジェクト 1 から 10 のプロジェクトを 4 つしか実行できるとします。 (図 30-5 に 示すように、P1 ~ P10 の最大 4 ワークシートを参照してください)。 セル L8 では、SUM (A6:A15) という数式で Projects 1 ~ 10 に関連付けられているバイナリ値の合計を計算します。 次に、制約 L8<=L10 を追加します。これにより、最初の 10 件のプロジェクトの最大 4 つが選択されます。 新しい最適なソリューションを図 30-5 に示します。 NPV は 90 億 1,400 万ドルに減少しました。

本の画像

一部またはすべての変化するセルがバイナリまたは整数である必要がある線形ソルバー モデルは、通常、すべての変化するセルを分数にすることができる線形モデルよりも解決が困難です。 このため、多くの場合、バイナリまたは整数プログラミングの問題に対する最適なソリューションに満足しています。 ソルバー モデルの実行時間が長い場合は、[ソルバー のオプション] ダイアログ ボックスの [許容値] 設定の調整を検討してください。 (図 30-6 を参照)。 たとえば、許容値を 0.5% に設定すると、ソルバーは、理論上最適なターゲット セル値の 0.5% 以内の実行可能なソリューションを初めて見つけたときに停止します (理論上の最適なターゲット セル値は、バイナリ制約と整数の制約を省略した場合に見つかった最適なターゲット値です)。 多くの場合、10 分以内に最適な 10% 以内で回答を見つけるか、コンピューター時間の 2 週間で最適なソリューションを見つけるかの選択に直面します。 既定の許容値は 0.05% です。つまり、ソルバーは、理論上最適なターゲット セル値の 0.05% 以内にターゲット セル値が検出された場合に停止します。

本の画像

  1. 1 つの会社で 9 つのプロジェクトが検討されています。 各プロジェクトによって追加された NPV と、次の 2 年間に各プロジェクトに必要な資本を次の表に示します。 (すべての数値は数百万です)。 たとえば、Project 1 では NPV に 1,400 万ドルが追加され、1 年目には 1,200 万ドル、2 年目には 300 万ドルの支出が必要になります。 1 年目には、プロジェクトで 5,000 万ドルの資本を利用できます。また、2 年目には 2,000 万ドルを利用できます。

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 を最大化するにはどうすれば良いでしょうか。

  • たとえば、4 Project実行する場合は、Project 5 を実行する必要があります。 NPV を最大化する方法

  • 発行会社は、今年発行する必要がある 36 の書籍を決定しようとしている。 このファイルPressdata.xlsx各書籍に関する次の情報が表示されます。

    • 予測される収益と開発コスト (数千ドル)

    • 各書籍のページ

    • 本がソフトウェア開発者の対象ユーザーを対象とするかどうか (列 E の 1 で示されます)

      発行会社は、今年は最大 8,500 ページの書籍を発行できます。また、ソフトウェア開発者向けの書籍を少なくとも 4 つ発行する必要があります。 会社が利益を最大化するにはどうすれば良いでしょうか。

この記事は、Microsoft Office Excel 2007 Data Analysis and Business Modeling by Wayne L. Winston から適用されました。

このクラスルーム スタイルの本は、Excel のクリエイティブで実用的なアプリケーションを専門とする、よく知られている統計学者でビジネスの教授である Wayne Winston による一連のプレゼンテーションから開発されました。

ヘルプを表示

その他のオプションが必要ですか?

サブスクリプションの特典の参照、トレーニング コースの閲覧、デバイスのセキュリティ保護方法などについて説明します。

コミュニティは、質問をしたり質問の答えを得たり、フィードバックを提供したり、豊富な知識を持つ専門家の意見を聞いたりするのに役立ちます。

この情報は役に立ちましたか?

言語の品質にどの程度満足していますか?
どのような要因がお客様の操作性に影響しましたか?

フィードバックをいただき、ありがとうございます。

×