No.6 セールス ミックスのシミュレーション 1 - 線形計画法による最適化技法 - 1. セールス ミックス分析の意義 損益分岐点分析の限界 単純な損益分岐点分析には資源の希少性は考慮されていないことである 損益分岐点分析をつうじて将来の売上高や費用を合理的に予測できても 予測を目標として設定できるかどうかは その予測値が使用可能な資源の範囲内にあるかどうかによる よって計画段階における CVP 分析には 資源の適正配分を織り込むシミュレーションが不可欠となる 資源配分を考慮する必要性 有限資源の適正配分問題が顕著に現れるのは 多品種製品を生産する場合である 売価や変動費率が製品ごとに異なれば 獲得できる利益も製品ごとに異なる 仮に 種類の違う製品が同じ生産設備を用いて生産され 生産設備の稼働能力が有限だとすると その稼働能力を各製品にどのような割合で配分するかによって 全社利益は全く異なってしまう このことは 多額の利益を期待できない製品に生産設備能力を多く配分してしまうと その分 多額の利益を期待できる製品の生産量 ( 販売量 ) が減ってしまうため 全社的な最大利益を獲得できないという意味である 図表 6-1 販売量に制約がある場合の限界利益の相違 資 料 項 目 製品 A 製品 B 合計 販売単価 300 円 400 円 限界利益率 60 % 30 % 販売量 ( 生産量 ) 400 個 600 個 1,000 個 ケース1 売上高 12,000 円 24,000 円 36,000 円 限界利益 7,200 円 7,200 円 14,400 円 販売量 ( 生産量 ) 500 個 500 個 1,000 個 ケース2 売上高 15,000 円 20,000 円 35,000 円 限界利益 9,000 円 6,000 円 15,000 円 販売量 ( 生産量 ) 600 個 400 個 1,000 個 ケース3 売上高 18,000 円 16,000 円 34,000 円 限界利益 108,000 円 4,800 円 15,600 円 この会社の生産能力の制約から 2 つの製品の合計生産量 ( 合計販売量 ) は 1,000 個以下と決まっているものとする ケース1からケース3に向かって, 製品 Aの販売量は多くなり, 製品 Bの販売量は少なくなっている 販売単価が大きい製品 Bを多く販売したほうが全社売上高合計は大きくなるが ( ケース1>ケース2>ケース3) 逆に全社限界利益合計は小さくなる ( ケース1<ケース2<ケース3) つまり, 限界利益率の大きい製品 A を多く販売したほうが全社限界利益は大きくなり, 結果として営業利益も大きくなる 多品種製品を生産する状況では, 各製品の生産量の組み合わせは製品に配分する資源の大きさによって決まり, それに応じて獲得できる利益の大きさも異なる. よって, 利益計画局面においては, 共有資源を適正に配分することによって最大の利益を獲得できるような CVP 関係を追求することが必要になる. このように目標値としての利益を最大化するための最適な生産量を決定する課題を 最適セールス ミックスの決定問題 と呼んでいる. 生産量の組み合わせに影響を及ぼす諸条件を 制約条件 といい, 目標利益の最大化という定義を 目的関数 という. 上の例示では 2 製品の販売量合計を 1,000 個以下とする ことが制約条件で, 限界利益合計 ( または営業利益 ) の最大化 が目的関数になる. いま製品 A の生産量を x, 製品 B の生産量を y とすると, この例の制約条件は y+x 1000 という 1 次式で表現でき, また, 限界利益合計を z とすると, 目的関数は maxz=max(180x+120y) と表現できる. すべての制約条件と目的関数を 1 次式に表現すると, 求めるセールス ミックスはすべての 1 次不等式 ( または等式 ) を同時に満たす数値であり, 単純にいえば, 連立 1 次方程式の解となる. 数学上, 不等式または等式は線形表現されることから, このように, 一定の制約条件のもとで目標値 ( 目的関数 ) を満たすような 最適解 を探索する方法を線形計画法 (linier programming) と呼ぶのである. - 18 -
2. 線形計画法の基本 - 2 製品の場合 図表 6-2 設例モデルの資料 計画資料項目製品 X 製品 Y 1 販売価格 @ 80 円 @ 40 円 2 単位当たり変動費 @ 48 円 @ 28 円 3 年間共通固定費 480,000 円 4 製品 1 個の完成に必要な機械稼働時間 4 時間 1 時間 5 両製品の生産に共通的に使用する機械の年間稼働能力 60,000 時間 6 年間の最大需要量 ( 最大生産量 ) 12,000 個 30,000 個 7 需要を満足させるための最低販売量 ( 最低生産量 ) 10,000 個 5,000 個 設例モデルは, 製品 X と製品 Y を生産している会社が予算編成における利益計画を試みる際に, 図表 6-2 に要約する計画資料にもとづいて, 次期の営業利益を最大にする 2 製品の最適生産量を導き出そうというものである. ここで, 年間共通固定費は 2 製品の生産量が何個であろうと一定額発生するから, 営業利益を最大にするということは 2 製品の限界利益の合計を最大にするということになる. なお, ここでは, 期首および期末の在庫量は無く, 期中の生産量は販売量と等しいと仮定されている. [ 機械稼働能力に関する制約条件 ] 資料項目 4と5から 4x+y 60,000 (1 式 ) [ 販売量の下限 上限に関する制約条件 ] 資料項目 6( 製品 X) から x 12,000 (2 式 ) 資料項目 6( 製品 Y) から y 30,000 (3 式 ) 資料項目 7( 製品 X) から x 10,000 (4 式 ) 資料項目 7( 製品 Y) から y 5,000 (5 式 ) [ 非負条件 ] 製品 Xの生産量 x 0 製品 Yの生産量 y 0 [ 目的関数 ] 資料項目 1と2から max z = max( 32x + 12y ) 図表 6-3 制約条件式 1 図表 6-4 制約条件式 2 と目的関数式 y 4 式 2 式 y 60,000 z/12 最大 1 式 30,000 3 式 30,000 D z/12 最小 C 目的関数式 5,000 5 式 5,000 A B 10,000 12,000 x 10,000 12,000 x 販売量の上限 下限に関する制約条件の 2 式,3 式,4 式および 5 式をグラフ表現すると図表 6-3 になり, 製品 Ⅹ と製品 Y の最適生産量の組み合わせは網掛部分に存在する. 図表 6-3 に機械稼働能力の制約条件 (1 式 ) を重ねると, 図表 6-4 のように最適生産量の組み合わせは台形 ABCD の網掛けの範囲に絞り込まれる. このように 2 変数の線形計画モデルをグラフ表現するとき, 解を含む範囲は凸多角形になる - 19 -
ここで, 目的関数式 (z=32x+12y) での最大限界利益は現時点では未知であるが, この式を変換すると y=(-32x+z)/12 となり, グラフ上の点線に示すように, 傾き -8/3 の直線として描くことができる. この直線はグラフ上に無数に引けるが, 図表 6-4 では端点 A を通る直線と D を通る直線の 2 本を示している. 目標値を最大化する最適解はグラフの凸多角形の中を通る目的関数式のうち y 切片 (z/12) が最も大きくなる直線であるから, いまの設例モデルでは端点 D を通る直線が対象になる. 一般に, 凸多角形のうち原点から最も遠くに引ける直線 ( 目的関数式 ) と交わる端点が目標値を最大化する. 端点 D は 1 式と 4 式の交点であるから, 各製品の生産量は次の連立方程式を解くことによって算定される. 4x + y = 60,000 x = 10,000 この結果, 製品 X の生産量は 10,000 個, 製品 Y の生産量は 20,000 個となり, そのもとでの限界利益合計は 560,000 円 (@32 円 10,000 個 +@12 円 20,000 個 ) で最大となる. 3. Excel による最適生産量の算定 - ソルバーの適用 実習の目的 この実習では, 上の設例モデルを下に示す Excel シート上で解決する. この設例モデルのテーマは, 製品 X と製品 Y の生産に必要な機械稼働時間と生産量 ( 販売量 ) の上限 下限が製品ごとに与えられているとき, 全体の機械稼働能力の範囲内で限界利益を最大化するような各製品の最適生産量を決定することである. そのような最適生産量となる変数の組み合わせ問題は,Excel のソルバー機能によって容易に解決できる. 実習データの準備 Work ドライブの Excel ブック セールス ミックス基礎 を各自の記憶媒体にコピーする. 各自の記憶媒体から Excel ブック セールス ミックス基礎 を開く. 実習 6-(1) 計算式の定義 Excel ブックのシートには, いまの設例モデルに関する既知のデータと, 必要な関数 式の一部が入力されている ( 実際のシートでは式は隠されている ). ここでは,2 製品の 単位当たり限界利益 ( セル番地 D3 と D4) および最適生産量算定後の 年間売上高 ( セル番地 C17 と C18) を計算するための式を入力しよう. セル番地 B17 と B18 には, ソルバー実行後に最適解が自動表示されるので, ここでは何も入力する必要はない. まず, 限界利益は収益から変動費を差し引いて求められる. 製品の単位当たり限界利益を計算する際, 収益に相当する金額は各製品の販売単価である. セル番地 D3 に =B3-C3 と入力する.(32 が表示される ) セル番地 D4 に上と同じ考え方で適切な式を入力しなさい.(12 が表示される ) 次に, 年間売上高は製品の販売単価に年間生産量をかければ求まるが, この時点では各製品の年間生産量はまだ算定されていないので, 式を入力しても0が表示される. セル番地 C17 に =B3*B17 と入力する. セル番地 C18 に上と同じ考え方で適切な式を入力しなさい. 実習 9-(2) ソルバー : パラメータの設定と実行 必要なデータをシート上に定義した後, ソルバーの実行までに不可欠となる作業はパラメータの設定である. 図表 6-5 のように, いまの設例モデルに必要なパラメータの一部は定義済みであるが, ここでは残るパラメータを設定しよう. Excel の [ ソルバー : パラメータ設定 ] 画面で指定すべき項目は, 基本的には 目的セル, 変化させるセル, 制約条件 の3つである. ただしこの場合, 生産量はマイナスになりえないという非負条件をオプションとして指定する. - 20 -
1 ソルバー機能の起動 2 Excel 画面左上の<Ofiice>ボタンをクリック [Excel のオプション ] ボタンをクリック 左フレームの [ アドイン ] をクリック 右フレームの [ 設定 ] ボタンをクリック [ アドイン ] 画面の [ ソルバーアドイン ] をチェック [OK] ボタンをクリック.( ソルバーがメニューにないときは必ずこの操作をする ) 3 <データ>リボンの< 分析 >グループから [ ソルバー (V)] をクリック.( [ ソルバー : パラメータ設定 ] 画面がポップアップされる ) 図表 6-5 パラメータ設定画面 4 目的セルの設定 目的セル とは目的関数の対象となるセルのことである. 設例モデルの目的関数は 限界利益の最大化 である. 計算シートにおける限界利益の表示セルはセル番地 E19 である. これを最大化すべきことは [ 目標値 :] で設定済みである. [ ソルバー : パラメータ設定 ] 画面の [ 目的セル (E)] のテキストボックスに $E$19 を入力する. 5 変化させるセルの設定 変化させるセル とは最終的にソルバーが最適解を表示する Excel シート上のセル番地のことである. この設例モデルの 変化させるセル は, 製品 Ⅹと製品 Yの年間生産量であり, 計算シートのセル番地 B17~B18 の2 箇所である. [ ソルバー : パラメータ設定 ] 画面の [ 変化させるセル (B)] のテキストボックスに $B$17:$B$18 を入力する. 6 制約条件の設定 この設例モデルで必要となる制約条件は,2 製品を生産する機械の年間稼働時間の上限, および, 各製品の最大需要量 ( 最大生産量 ) と最低販売量 ( 最低生産量 ) である. 図表 6-5 で定義済みの制約条件のうち 1 行目の $B$13>=$B$17*$B$8+$B$18 *$B$9 は, 年間機械稼働時間の上限に関する設定である. すなわち 右辺の製品 Ⅹ の機械運転時間 ($B$17*$B$8) と製品 Y の機械運転時間 ($B$18* $B$9) の合計は, 左辺の稼働時間上限 ($B$13) 以下でなければならない という指定である. また 2 行目は製品 Ⅹ の年間生産量 ($B$17) が最大需要量 ($C$8) 以下であることを,3 行目は製品 Ⅹ の年間生産量 ($B$17) が最低販売量 ($D$8) 以上であることを, それぞれ指定している. そこには製品 Y の最大需要量と最低販売量に関する制約条件がまだ設定されていないので, ここで設定しなければならない. 図表 6-6 最大需要量の条件指定 図表 6-7 最低生産量の条件指定 図表 6-8 非負条件の指定 [ ソルバー : パラメータ設定 ] 画面の [ 制約条件 (U)] のテキストボックスをクリック [ 追加 (A)] ボタンをクリック. [ 制約条件の追加 ] 画面で図表 6-6 のようにセル番地と不等号を指定する [ 制約条件の追加 ] 画面の [ 追加 (A)] ボタンをクリック. [ 制約条件の追加 ] 画面で図表 6-7 のようにセル番地と不等号を指定する [ 制約条件の追加 ] 画面の [ 追加 (A)] ボタンをクリック 新たな [ 制約条件の追加 ] 画面の [ キャンセル ] ボタンをクリック. - 21 -
7 非負条件の設定 非負条件を上と同じように [ 制約条件 (U)] のテキストボックスで指定することも可能であるが, ソルバーのオプションで簡単に指定することができる. [ ソルバー : パラメータ設定 ] 画面の [ オプション (O)] ボタンをクリック. [ ソルバー : オプション設定 ] 画面 ( 図表 6-8) の [ 非負数を仮定する (G)] をチェック [OK] ボタンをクリック. 8 ソルバーの実行 [ ソルバー : パラメータ設定 ] 画面の [ 実行 (S)] ボタンをクリック.( 最適解がみつかった ) [ ソルバー : 探索結果 ] 画面の [OK] ボタンをクリック. この Excel ブックを上書き保存する. 図表 6-9 は, 以上の操作の結果である. セル番地 B17~B18 に年間限界利益を最大にする最適生産量が算定された. いまの制約条件のもとでは, 年間に製品 Ⅹ を 10,000 個, 製品 Y を 20,000 個生産すれば年間限界利益は 560,000 円で最大になると予測される. この算定結果は先の図表 9-4 における端点 D と一致し, 妥当である. 図表 6-9 ソルバーによる 2 製品のセールス ミックス算定結果 - 22 -