この記事では、what-if 分析に使用できる Microsoft Excel アドインプログラムであるソルバーの使用方法について説明し、最適な製品ミックスを決定します。
収益性を最大限に高める月間製品ミックスを決定する方法を教えてください。
多くの場合、会社は各製品の数量を月単位で作成する必要があります。 最も簡単な方法として、 製品の組み合わせ の問題には、1ヶ月に生産する各製品の利益を最大化する方法が含まれています。 製品ミックスは通常、次の制約に従う必要があります。
-
製品ミックスで利用可能なリソースより多くのリソースを使用することはできません。
-
各製品の需要は限られています。 余剰生産は無駄であるため (たとえば、perishable 薬品など)、1ヶ月間に製品を製造することはできません。
次の例では、製品の組み合わせの問題を解決していきます。 この問題の解決方法については、図27-1 に示す「ファイル Prodmix.xlsx を参照してください。
ここでは、工場で6つの異なる製品を生産する製薬会社について説明します。 各製品の生産には、労務と原材料が必要です。 図27-1 の行4には、各製品を1ポンド生産するのに必要な労働時間が表示され、行5には各製品のポンドを生産するために必要な原材料のポンドが表示されます。 たとえば、1ポンドの製品1を生成するには、6時間の労働力と3.2 ポンドの原材料が必要です。 各薬品について、1行5には1ポンドあたりの価格が示され、行9には1ポンドあたりの利益の寄与額が示されています。 たとえば、製品2はポンドあたり $11.00 に販売され、ポンドあたりの単価は $5.70 で、1ポンドあたりの $5.30 利益が発生します。 各薬品の月額需要は、行8に記載されています。 たとえば、製品3の需要は1041ポンドです。 今月、4500時間の労働力と1600ポンドの原材料が提供されます。 この会社が毎月の利益を最大化する方法を教えてください。
Excel ソルバーについて何もわかっていない場合、この問題は、製品ミックスに関連する利益とリソース使用状況を追跡するためのワークシートを構築することによって攻撃されます。 次に、試用版とエラーを使用して、利用可能な量を超える人件費または原材料を使用することなく、利益を最適化するために、製品ミックスを変更します。 このプロセスでは、ソルバーは試行錯誤ステージでのみ使用されます。 基本的に、ソルバーは最適化エンジンであり、試行錯誤した検索を完璧に実行します。
製品の組み合わせの問題を解決するための鍵は、特定の製品ミックスに関連するリソース使用量と利益を効率的に計算することです。 この計算を行うために使用できる重要なツールは、SUMPRODUCT 関数です。 SUMPRODUCT 関数は、セル範囲の対応する値を乗算し、それらの値の合計を返します。 SUMPRODUCT の評価で使用される各セル範囲には、同じディメンションが含まれている必要があります。これは、2つの行または2つの列で、1つの列と1つの行ではなく、SUMPRODUCT を使用できることを意味します。
製品ミックスの例で SUMPRODUCT 関数を使用する例として、リソース使用量を計算してみましょう。 労働力使用量は、によって計算されます。
(薬品1の1ポンドあたりの労務使用量) * (薬品1ポンド製造) + (薬品2の1ポンドあたりの労務使用量) * (薬品2ポンド生産) +... (薬品6の1ポンドあたりの労務使用量) * (薬品6ポンド生産)
さらに面倒な方法として、 D2 * D4 + E2 * E4 + F2 * + G2 * G4 + H2 * H4 +が含まれている I4 * I4 というように、労働力の使用状況を計算できます。 同様に、原材料の使用状況はD2 * D5 + E2*E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * I5として計算できます。 ただし、6つの製品のワークシートでこれらの数式を入力するには時間がかかります。 製造した会社 (工場で50製品など) を使用している場合は、どのくらいの時間がかかるか想像してみてください。 労働力と原材料の使用を簡単に計算する方法としては、D14 から D15 に数式 SUMPRODUCT ($D $2: $I $ 2, D4: I4)をコピーする方法があります。 この数式では、 D2 * D4 + E2 * E4 + F2 * H4 + H2 * I4 (これは、労働力) を計算しますが、より簡単に入力することができます。 セル範囲 D2: I2 では $ 記号を使用しているため、数式をコピーするときに、2行目から製品ミックスをキャプチャしていることに注意してください。 セル D15 の数式は、原材料の使用状況を計算します。
同じ方法で、利益は
(薬品1のポンドあたりの収益) * (薬品1の生産ポンド) + (薬品2の1ポンド当たりの収益) * (薬品2ポンド生産) +... (薬品6の1ポンドあたりの収益) * (薬品6ポンド製造)
セル D12 では、数式 SUMPRODUCT (D9: I9、$D $2: $I $2)を使用して、利益を簡単に計算できます。
これで、製品ミックスソルバーモデルの3つのコンポーネントを特定できるようになりました。
-
目的セル。 目標は、利益を最大化することです (セル D12 で計算されます)。
-
セルを変更します。 各製品に対して生成されたポンド数 (セル範囲 D2: I2)
-
式. 以下の制約があります。
-
利用可能な量を超える人件費または原材料を使用しないでください。 つまり、セル D14: D15 (使用されているリソース) の値は、セル F14: F15 (利用可能なリソース) の値以下である必要があります。
-
需要の高い薬品を生産しないでください。 つまり、セル D2: I2 (各薬品の生産量) の値は、各薬品の需要 (I8) 以下である必要があります (セル D8:)。
-
お客様にとっては、お金の残高はまったく発生しません。
-
目的セル、変化させるセル、制約をソルバーに入力する方法について説明します。 次に、[解決] ボタンをクリックして、利益を最大化した製品ミックスを見つけるだけです。
開始するには、[データ] タブをクリックし、[分析] グループの [ソルバー] をクリックします。
注: 「Excel ソルバーによる最適化の概要」で説明したように、Microsoft Office ボタン、[Excel のオプション]、[アドイン] の順にクリックして、ソルバーをインストールします。 [管理] ボックスの一覧の [Excel アドイン] をクリックし、[ソルバーアドイン] ボックスをオンにして、[OK] をクリックします。
図27-2 に示すように、[ソルバー: パラメーター] ダイアログボックスが表示されます。
[目的セルの設定] ボックスをクリックして、利益のセル (セル D12) を選択します。 [変化させるセル] ボックスをクリックして、各薬品の生産量が含まれている範囲 D2: I2 をポイントします。 ダイアログボックスが図27-3 のようになります。
これで、モデルに制約を追加する準備ができました。 [追加] ボタンをクリックします。 図27-4 に示す [制約の追加] ダイアログボックスが表示されます。
リソース使用量の制約を追加するには、[セル参照] ボックスをクリックし、[範囲 D14: D15] を選びます。 中央の一覧から [<=] を選びます。 [制約] ボックスをクリックし、セル範囲 F14: F15 を選択します。 [制約の追加] ダイアログボックスは、図27-5 のようになります。
ソルバーで変化させるセルの値が異なる場合、 D14<= F14 (労働力は利用可能な労働量以下) と D15<= F15 (使用されている原材料に等しい、または等しい) の両方が考慮されることを保証しました。 [追加] をクリックして需要の制約を入力します。 図27-6 に示されているように、[制約の追加] ダイアログボックスに入力します。
これらの制約を追加すると、変化させるセルの値に異なる組み合わせを使用しようとしたときに、次のパラメーターを満たす組み合わせのみが考慮されます。
-
D2<= D8 (薬品1の生産量が薬品1の需要以下である)
-
E2<= E8 (薬品2の生産量が薬品2の需要以下である)
-
F2<= F8 (製造された薬品3の生産量が薬品3の需要以下である)
-
G2<= G8 (製造された薬品4の生産量が薬品4の需要以下)
-
H2<= H8 (作成された薬品5の金額は、薬物5の需要以下)
-
I2<= I8 (製造された薬品6の量が薬品6の需要以下)
[制約の追加] ダイアログボックスで [OK] をクリックします。 ソルバーウィンドウは、図27-7 のようになります。
[ソルバー: オプション] ダイアログボックスで、セルを変更するときに負ではないという制約を入力します。 [ソルバー: パラメーター設定] ダイアログボックスの [オプション] ボタンをクリックします。 次のページの図27-8 に示されているように、[線形モデルで計算] ボックスと [負の値ではないものと仮定] ボックスをオンにします。 [OK] をクリックします。
[負の値ではないと仮定する] ボックスをオンにすると、変更されたセルの組み合わせのみが考慮され、変更されたセルはそれぞれ負の値ではないことが想定されます。 [線形モデルの仮定] ボックスをオンにしました。製品の組み合わせの問題は 線形モデルと呼ばれる特殊な種類のソルバーの問題であるためです。 基本的に、ソルバーモデルは次の条件に従って線形になります。
-
目的セルを計算するには、フォームの条件 (変化させるセル) * (定数)を加算します。
-
各制約は、"線形モデルの要件" を満たしています。 つまり、各制約が評価されることを意味します。フォームの用語 (変化させるセル) * (定数) を加算し、合計を定数と比較します。
このソルバーの問題が線形に発生するのはなぜですか? ターゲットセル (利益) は、
(薬品1のポンドあたりの収益) * (薬品1の生産ポンド) + (薬品2の1ポンド当たりの収益) * (薬品2ポンド生産) +... (薬品6の1ポンドあたりの収益) * (薬品6ポンド製造)
この計算は、ターゲットセルの値が、フォームの条件 (変化させるセル) * (定数)を加算することによって得られるパターンに従っています。
私の労働力制約は、 (薬品1の1ポンドあたりの使用量) + (薬品1ポンド生産 2) + (薬品2ポンド製造された労働力) +......(人件費1 ポンドあたりの薬品 6) * (薬品6ポンド生産) を利用可能。
そのため、労働力の制約条件は、フォームの用語 (変化させるセル) * (定数) を加算し、合計を定数と比較することで評価されます。 労働力制約と原材料制約の両方が、線形モデルの要件を満たしています。
需要の制約のフォーム
(薬品1の製造) <= (薬品1の需要) (薬品2製造日) <= (薬品2の需要) § (薬品6製造日) <= (薬品6の需要)
各需要の制約は、線形モデルの要件を満たしています。各条件は、フォームの用語 (変化させるセル) * (定数) を加算し、合計を定数に比較することで評価されるためです。
製品ミックスモデルが線形モデルであることを示しているのですが、なぜ必要なのでしょうか。
-
ソルバーモデルが線形で、[線形モデルを仮定する] を選択した場合、ソルバーはソルバーモデルに最適な解を見つけることが保証されます。 ソルバーモデルが線形ではない場合は、ソルバーが最適な解決策を見つけられないことがあります。
-
ソルバーモデルが線形で、[線形モデルを仮定する] を選択した場合、ソルバーは非常に効率的なアルゴリズム (シンプレックスメソッド) を使ってモデルの最適解を見つけます。 ソルバーモデルが線形で、[線形モデルを仮定する] が選択されていない場合、ソルバーは非常に非効率的なアルゴリズム (GRG2 メソッド) を使用し、モデルの最適解を見つけるのが困難な場合があります。
[ソルバー: オプション] ダイアログボックスで [OK] をクリックすると、図27-7 のように、[メインソルバー] ダイアログボックスに戻ります。 [解決] をクリックすると、製品の組み合わせモデルの最適解 (存在する場合) が計算されます。 第26章で説明したように、製品ミックスモデルに最適なソリューションは、すべての実現可能な解のセットに対して利益を最大化する、変化させるセル値 (各薬品の生産量) です。 これに対して、実現可能な解決策として、すべての制約条件を満たす変化させるセル値のセットがあります。 図27-9 で示されている変化させるセルの値は、すべての運用レベルが非負数であり、稼働レベルが需要を超えておらず、リソース使用量が利用可能なリソースを超えていないため、実現可能なソリューションです。
次のページの図27-10 に示す変更されたセルの値は、次の理由により、実現 不可能なソリューション を表します。
-
Skype では、その需要を超える薬品5を製造しています。
-
利用可能な機能よりも多くの労力を使用します。
-
利用可能な情報よりも多くの原材料を使用しています。
[計算] をクリックすると、図27-11 で示されている最適解がすぐに見つかります。 ワークシートの最適解の値を維持するには、[ソルバーソリューションを保持する] を選択する必要があります。
製薬会社では、$6625.20 ポンドの薬品4、1084ポンドの薬品5、およびその他の薬物の1つを596.67 製造することで、のレベルで月間利益を最大化することができます。 他の方法で $6625.20 の最大利益を達成できるかどうかを判断することはできません。 Skype では、限られたリソースと需要によって、今月の $6627.20 を超えることはできません。
各製品の需要が満たされている 必要が あるとします。 (ファイル Prodmix.xlsx の [ 実現不可能なソリューション ] ワークシートを参照してください)。 次に、要求の制約を d2: i2<= d8: I8 から D2: I2>= d8: I8に変更する必要があります。 これを行うには、[ソルバー] を開き、[D2: I2<= D8: I8 制約] を選択して、[変更] をクリックします。 図27-12 に示す [制約の変更] ダイアログボックスが表示されます。
[>=] を選択し、[OK] をクリックします。 すべての要求を満たすセル値のみを変更することが、ソルバーによって保証されました。 [実行] をクリックすると、"実行可能な解が見つかりませんでした" というメッセージが表示されます。 このメッセージは、モデルで間違いを犯したというわけではありませんが、限られたリソースで、すべての製品に対する需要を満たすことはできません。 ソルバーは、各製品の需要に対応する必要がある場合は、さらに多くの労働力、原材料、またはその両方を追加する必要があることを伝えます。
各製品に対して実質無制限の需要を認めていて、各薬品の生産に負の数量が許可されている場合はどうなりますか? (このソルバーの問題を確認するには、[ファイル Prodmix.xlsx の [値を次の ように収束しない ] ワークシートを参照してください)。 この状況に最適な解決策を見つけるには、ソルバーを開き、[オプション] ボタンをクリックして、[負の値ではないと仮定する] ボックスをオフにします。 [ソルバー: パラメーター] ダイアログボックスで、[需要制約 D2: I2<= D8: I8] を選び、[削除] をクリックして制約を削除します。 [計算] をクリックすると、"セルの値は収束していません" というメッセージが返されます。 このメッセージは、ターゲットセルが最大化されていることを意味します (この例では、任意の大きなターゲットセル値を持つ実現可能なソリューションがあります)。 (ターゲットセルが最小化されている場合は、"セル値を設定しても収束しません" というメッセージが表示され、任意の小さなターゲットセル値を持つ実現可能なソリューションがあります。) このような状況では、薬品の実質的な生産を可能にすることによって、"作成" リソースを利用できるようになりました。これは、任意の大量の薬品を生成するために使用できます。 無制限の需要により、実質無制限の利益が実現されます。 実際の状況では、無制限の金額を作成することはできません。 つまり、"設定値が収束しません" というメッセージが表示された場合は、モデルにエラーがあります。
-
製薬会社が、現在の人件費よりも1時間あたり500時間の労働力を $1 で購入できるとします。 利益を最大化するにはどうすればよいですか?
-
チップ製造工場では、4人の技術者 (A、B、C、D) で3つの製品 (製品1、2、3) を製造しています。 今月、チップメーカーは、80ユニットの製品1、製品2の50単位、および製品3の最大50単位を販売することができます。 技術者 A は、製品1と3のみを作成できます。 技術者 B は、製品1と2のみを作成できます。 技術者 C は製品3のみを作成できます。 技術者 D は、製品2のみを作成できます。 製造された各出荷単位の製品には、次の利益があります: 製品1、$6製品2、$7、製品3、$10 各技術者が製品を製造するために必要な時間 (時間) は、次のとおりです。
製品
技術者 A
技術者 B
技術者 C
技術者 D
1
2
2.5
できません
できません
2
できません
3
できません
3.5
3
3
できません
4
できません
-
各技術者は、1ヶ月あたり最大120時間稼働できます。 チップメーカーは、月間利益を最大化するにはどうすればよいですか? 単位の小数を生成できるとします。
-
コンピューター製造工場では、マウス、キーボード、およびビデオゲームのジョイスティックが作成されます。 単位あたりの1つの人件費、1単位あたりの労務使用量、月間需要、単位ごとの機械所要時間については、次の表をご覧ください。
ポインティング
キーボード
スティック
利益/単位
$8
$11
$9
労務使用量/単位
. 2 時間
. 3 時間
. 24 時間
マシンの時間/単位
04時間
. 055 時間
04時間
毎月の需要
15,000
27000
11000
-
毎月、13000の作業時間と3000時間の合計が利用可能になります。 メーカーは、プラントからの月間利益の貢献度を最大化するにはどうすればよいですか?
-
各薬品の最低200ユニットの需要が満たされている必要があることを前提として、薬物の例を解決してください。
-
Jason は、ダイヤモンドブレスレット、necklaces、earrings 行います。 1ヶ月あたり最大160時間の勤務を希望しています。 800オンスのダイヤを持っています。 各製品の製造に必要な利益、労働時間、およびダイヤモンドのオンスについては、以下に記載されています。 各製品の需要が無制限の場合、Jason は、利益を最大化する方法ですか?
製品
単位の利益
単位あたりの労働時間
単位あたりのダイヤのオンス
ブレスレット
¥30,000
35
1.2
Necklace
¥20,000
. 15
75
Earrings 利用
¥10,000
.05
切り捨て
