ひとりマーケティングのためのデータ分析

手順解説 | Excel(エクセル)でおこなうビジネスデータの分析

定量発注方式
How-to

経済的発注量と発注点

ここでは統計的在庫管理手法のひとつ、定量発注方式を運用するのに必要な経済的発注量(EOQ)と発注点(ROP)を求めます。

一部必要な手続きに関して Excel で追っていますが、Excel での処理はかかるテーマにおいては従としています。そのため、EOQ 公式を使って Excel でてっとり早く求めてしまいたいという場合には、以下は冗長な内容となりますのでご注意ください(出てきません)。

状況を整理する

晴花

1

リサーチサービス社で使用する用紙(業務用コピー用紙)の枚数です。

経理課でこれを調べたところ、1 年あたり 1980000 枚消費していることがわかりました。したがって、単純にこれを営業日数で除してやると、1 ヵ月では 165000 枚、1 日では 7500 枚消費していると考えられます。

[1年あたり消費量:1980000枚][1月あたり消費量:165000枚][1日あたり消費量:7500枚]

晴花

2

用紙の発注費用および保管費用です。

ここでは、前者を発注および管理事務にかかる諸費用(人件費・通信費など)および配送費用(重量には依らないとします)、後者を物的なスペースを占有することにかかる諸費用(倉庫・什器の賃借料、関連維持費など)として定義しています。とりあえず、ここではひらたくして、前者は発注回数に、後者は発注量およびそれを消費することにしたがって変化する諸費用と考えておいてください。

具体的な値としては、発注費用が 2000 円、保管費用が 1 枚あたり 3.216 円と求められています。

[発注費用:\2000/回][保管費用:\3.216/枚]

晴花

3

とりあえず、これらのデータを使って、まずは年間にかかる総費用をアバウトにシミュレーションしてみたいと思います。具体的には、

  • 1 年に 1 回だけ発注
  • 1 年に 12 回発注(つまり毎月 1 回)
  • 1 年に 264 回発注(これは年間営業日数に等しいとします。つまり毎日発注)

の 3 つのパターンを考えてみます。

晴花

4

あ!

話がややこしくなるとタイヘンですので、このシミュレーションの世界は、発注したら品物が瞬時に納品される世界だと、とりあえず都合のいい仮定をおかせてもらいますNe。

晴花

5

では、まず…1 年の最初に 1 回だけ発注するパターンを見てみたいと思います。この場合、在庫量の変化をグラフにあらわすと…こんなカンジになります(縦軸:在庫量, 横軸: t 日目)。在庫量は日々減っていきますが、当然ながら増えることはありません。

晴花

6

このパターンの場合、年間の発注回数は最初の 1 回のみですから、発注費用は 1 回 × 2000 円 で 2000 円 を要します。

晴花

7

年間の消費量は 198 万枚でしたので…この年 1 回いちばん最初に 198 万枚すべてを仕入れてしまいます。

この場合、年間の保管費用は 最初に仕入れた 198 万枚に単位あたり保管費用を乗じた(1980000 枚 × 3.216 円)金額(6367680 円)かなーとも思いましたが…

晴花

8

先に保管費用は発注量およびそれを消費することにしたがって変化する諸費用と定義したように、よくよく考えてみれば、たとえば保管スペースなんかは常々ピーク量(最初の 198 万枚)と同一のキャパシティを確保しておく必要があるわけではないですね。


ピーク量を保管可能なキャパシティを維持するために必要な費用を、下のグラフでうすい紫色であらわすと、保管スペースのキャパシティを可変的に運用できる場合には、年間の保管費用はこい紫色の領域であらわすことができます。これは保管費用が、キャパシティをピーク量に固定とした場合の半分となることを意味します。

晴花

9

上のことをふまえ、先の保管費用を再度計算すると(1980000 × 3.216 ÷ 2) 3183840 円 となります。

晴花

10

発注費用と保管費用をひとつにして総費用を求めます(2000 + 3183840)。1 年に 1 回だけ発注するパターンでは、在庫関連費用は 3185840 円必要となるようです。

[年次一括購入の場合 在庫関連費用:\3185840]

晴花

11

つづいて…1 年に 12 回発注するパターンです。先に示したとおり、ひと月あたりの用紙の消費量は 165000 枚でしたので、毎月この枚数を月初日に 1 度だけ仕入れていきます。

晴花

12

在庫量の変化をグラフにすると、こちらは…こんなカンジですね(下図)。先とは異なりグラフは櫛型となりました。

晴花

13

月ごとに発注をかけますので、山の数は 12 です。

また先と同様、年間の保管費用はうすい紫色の部分の 1/2 となることがわかります。

晴花

14

この点をふまえ、次の式で具体的に発注費用と保管費用を計算してみます。

晴花

15

えーと、1 年に 12 回発注するパターンでは、在庫関連費用は 289320 円必要となるようです。

[月次一括購入の場合 在庫関連費用:\289320]

晴花

16

今度は…これまた極端ですが 1 年に 264 回発注するパターンです。毎日の業務に必要な 7500 枚を日々仕入れていきます。

晴花

17

在庫量の変化のグラフはこんなふうになりました(下図)。レイアウトの制約上 系列線で潰れていますが…拡大するとこちらも櫛型の山があるんです…。

晴花

18

毎日発注をかけますから、山の数は 264(=年間営業日数)です。

またこれも以前の 2 つのパターンと同様、年間の保管費用はうすい紫色の部分の 1/2 となります。

晴花

19

この点をふまえ、同じように次式で具体的に発注費用と保管費用を計算してみると…

晴花

20

在庫関連費用は 540060 円必要となることが示されました。

[日次購入の場合 在庫関連費用:\540060]

晴花

21

以上の 3 パターンの結果を、下にあらためて提示します。

最初に掲げたリサーチサービス社のデータ(設定)において、在庫に関する総費用を考えるとき、こんなことが言えそうです。

  • 1 年分を一括で仕入れるより、1 ヵ月分を一括で仕入れた方が おトク
  • 1 ヵ月分一括仕入から、毎日の仕入れに切り替えると かえって損

経済的発注量を求める

晴花

22

そういったコトが明らかになってくると、少し欲がでてきました。

…というのも、上に見た 3 つのパターンはあくまで便宜上の区分にすぎないですね。ひと月 1 回、1 ヵ月の必要量である 165000 枚を発注していけば年間の在庫関連費用を最も小さくできるという最適解を示すものではありません。以後、この点を考えていきたいと思います。


その前に…定量発注方式では、その名のとおり最適な発注量を定数(≒変化させない)として考えます。ここで、その定数 すなわち 1 回あたりの発注量x と定義しておきます。

1回あたりの発注量:x

晴花

23

ではまず…

先の 3 つのパターンから、年間に要する発注費用の計算式を一般化してみると

  • 年間発注費用 = 年間の発注回数 × 1 回あたりの発注費用

であらわすことができます。

年間発注費用=年間の発注回数×1回あたりの発注費用

晴花

24

ここで、(1 回あたりの発注量を x としたので)年間の発注回数は

  • 年間消費量 ÷ x

であらわすことができます。「年間消費量」については具体的な値がわかっていますので(設定)、これを代入し

  • 1980000 ÷ x

とあらわせます。

年間消費量÷x→1980000÷x

晴花

25

この式、および 1 回あたりの発注費用(設定)を

  • 年間発注費用 = 年間の発注回数 × 1 回あたりの発注費用

式に代入すると、

  • 年間発注費用 = 3960000000 ÷ x

とすることができます。

年間発注費用=3960000000÷x

晴花

26

年間発注費用 = 3960000000 ÷ x 式をグラフに描画してみると、下図のようになります(縦軸:年間発注費, 横軸:x)。

晴花

27

つづいて同じように…

先の 3 つのパターンから、年間に要する保管費用の計算式を一般化してみると

  • 年間保管費用 = 1 回あたりの発注費用 × 1 単位あたりの年間保管費用 ÷ 2

であらわすことができます。

年間保管費用=1回あたりの発注費用×1単位あたりの年間保管費用÷2

晴花

28

「1 単位あたりの年間保管費用」については具体的な値がわかっていますので(設定)、これを代入し

  • x × 3.216 ÷ 2

とあらわせます。

x×3.216÷2

晴花

29

すなわち年間保管費用は

  • 年間保管費用 = 1.608x

となります。

年間保管費用=1.608x

晴花

30

年間保管費用 = 1.608x 式をグラフに描画してみると、下図のようになります(縦軸:年間保管費用, 横軸:x)。

晴花

31

これを先ほどの年間発注費用のグラフと重ねてみると…下のようなグラフとなりました。

晴花

32

ここで、在庫関連費用は総費用(発注費用+保管費用)ですから、

  • 年間在庫関連費用 = 3960000000 ÷ x + 1.608x

とあらわすことができます。

年間在庫関連費用=3960000000÷x+1.608x

晴花

33

この 年間在庫関連費用 = 3960000000 ÷ x + 1.608x 式を先のグラフに重ねると、下図オレンジ色の線で示すことができます。

在庫関連費用(総費用)曲線

晴花

34

年間在庫関連費用が最も小さくなるのは、オレンジ色の線上に置いた青色の点の位置となることがわかります。つまり、このときの横軸上の赤色の点が、年間在庫関連費用を最も小さくできる x(1 回あたりの発注量)となるはずです。

晴花

35

この x の具体的な値を知るために、下図緑色の点に着目します。

「発注費用」線と「保管費用」線の交点で、年間在庫関連費用が最も小さくなっている” ということを利用すると…

晴花

36

発注費用 = 保管費用 となる式

  • 3960000000 ÷ x = 1.608x

がみちびけます。これを任意の手段で x について解けば具体的な値が求められます。

3960000000÷x=1.608x

晴花

37

参考ですが…次のように式を整えて、便利な Web サービス「Keisan-高精度計算サイト」で求めてしまうのが手っ取り早いかもしれません。


いずれにしろ、結果は x=49625 となります(マイナスの発注量はありえないのでプラスの方のみ解とします)。

晴花

38

リサーチサービス社の環境のもとでは、都度 49625 枚発注していくと年間在庫関連費用を最も小さくできることがわかりました。なお、ここで求めた値は経済的発注量EOQ)と呼ばれます。

[セルA4]EOQ [セルB4]49625

発注点とリードタイム

晴花

39

最初の設定に返って… 1 日あたりの消費量が 7500 枚でしたから、この EOQ は 6.6 日分の消費量に相当することとなります。

晴花

40

と、いうことで下図の上段のパターン…すなわち 49625 枚発注・入荷して、6.6 日後に在庫がなくなるはずなのでそのときまた 49625 枚発注・入荷して…というパターンで運用のプランが立ちそうですが…実際の現場では、こんなに都合よくコトが運ぶはずもないですよね。


ここまで発注と入荷の時間差の存在しない世界を仮定してきましたが、第 1 の課題として、実際にはリードタイム(ここでは発注後入荷までの所要日数とします)が存在します。となれば、下段のように在庫が底をついてからあらたな用紙が入荷されるまで(濃い紫色で示した期間)、ウチの場合は業務が止まってしまうことになります。

晴花

41

したがって、リードタイムが存在することを鑑みて、発注のタイミングををいくらか先取りしていく必要が生じます。この「いくらか」を具体的に示すものとして、定量発注方式では 発注点(ROP : ReOrder Point)と呼ばれる物量的なタイミングを設定します。ここから後の説明は、この発注点を求めていく過程となります。

晴花

42

ということで、リードタイムが情報として必要です。

現在の業者さんとの取引において、このリードタイムは 3 日となっています。

[リードタイム:3日]

[セルA6]リードタイム [セルB6]3

晴花

43

用紙を発注して納入されるまでに 3 日を要しますから、この期間中消費される予定量 すなわち 22,500 枚をあらかじめ見込んで発注のタイミングをはかります。具体的に、在庫残数が 22,500 枚に到達した時点で発注をおこなえば業務を止めることなくスムーズにサイクルをまわせそうに思えます…

7500枚×3日分=22500枚

「安全在庫」も加味してみる

晴花

44

…が。やっぱり、何かが足りないですね。

ここまで何の気なしに「1 日あたり消費量」というワードを使ってきましたが、現実にはこれは定数ではありません。年間のスパンで考えた場合、リサーチサービス社では、1 日あたり 平均 7500 枚消費することに誤りはないのですが、日々の消費量には若干の多寡が生じています。

[1日あたり平均消費量:7500枚]

[セルA1]1日あたり平均消費量 [セルB1]7500

晴花

45

となれば…下図上段のように入荷・発注・在庫切れのサイクルが 6.6 日のスパンで規則正しく繰り返されることはまずありません。

場合によっては、下段のようなパターンになるかもしれません。たとえば最初は入荷から発注までが 3.6 日、そこから在庫切れ(つまり再入荷)に至るまでが 3 日(=リードタイム)という流れであったものが、次のフェーズではより速いペースで消費され(図の 緑の角度>オレンジの角度)、発注点までより早く到達する(2.6 日)…そんなケースです。


このパターンのように、定量発注方式では入荷から発注点までの日数は消費のペースにしたがって変化します。

晴花

46

何より問題なのは、発注点以降の消費量です。

すでにリードタイム中の消費量として 22,500 枚という値を導いていますが、先の話から、こちらも平均たることを考慮していかなくてはならなくなります。


後出しの情報になりますが、今回、毎日の消費量は正規分布にしたがっている ことをあらかじめ見越しています。これを前提として、下にリードタイム中の消費量の正規分布曲線を描いてみます。下図のうすい青色の領域で示されるように、リードタイム中(=3日間)の消費量は 50% の確率で 22,500 枚を超えてしまうことがわかります。

晴花

47

換言すると…

発注点後(=発注してから入荷されるまでの間)、今の計画のままでは在庫を切らしてしまう事態が 2 回に 1 回 は起こってしまうワケです。第 2 の課題として、これをなんとかする必要がありますね…。結局、このままでは業務への影響が大きいですし…。

晴花

48

と、いうことで…第 2 の課題を解決するために、乱暴な言い方ですが緑色の線をつまんでリードタイム中欠品が生じないような高さまでぐぐぐーっと引っ張り上げてやります(下図)。こうしていくらかのゲタを履かせてしまえば、リードタイム中に在庫が切れる確率を 50% より減らせます。この線を引っ張り上げたことによって現れた濃い紫色部分の高さ(=用紙の枚数)は 安全在庫 と呼ばれます(下図)。

晴花

49

こうして安全在庫を加味する場合、発注点にもそれを反映させてやる必要がありますね。したがって、リードタイム中の消費量(22,500 枚)に安全在庫を加えた値をあたらしい発注点として再定義したいと思います。

あたらしい発注点=リードタイム中の消費量+安全在庫

晴花

50

と、なれば… 業務が止まるのは私(わたし)的にもまずいですし、やっぱりこの安全在庫はすっごくたくさんの量を確保しといたほうがいいのかも…

晴花

51

と、一瞬考えてしまいましたが それも本末転倒…でしたね。

私はそもそも、在庫関連費用を可能な限り抑えようとこの問題に取り組んでいるはずでした。在庫切れを極度に恐れるあまり、在庫関連費用の問題を無視してしまうのでは意味をなしません。


ということで、ここに「安全在庫をどの程度に設定すべきか」というあらたな課題が生じます。

晴花

52

さて、先ほどリードタイム中の消費量に関して正規分布曲線を描画しました。と、いうことはこちらも後出しの情報となって申し訳ないのですが、1 日あたりの消費量についての標準偏差もあらかじめ見当をつけています(1650 枚)。

[1日の消費量の標準偏差:1650枚]

[セルA2]1日あたりの消費量の標準偏差 [セルB2]1650

晴花

53

リードタイムは 3 日ですから、先ほどの正規分布曲線は 3 日分の標準偏差で描いています。ここで説明の都合上、一時的に リードタイムを t 日とし、1 日の消費量の標準偏差を σ とします。

まとまった日々の観測値が存在するなら、t 日分の移動合計をとって標準偏差を求めるか、あるいは t 日分の標準偏差は おおむね

  • σ x √t

と近似することを利用して、リードタイム日数分の消費量の標準偏差を計算します。なお、このあたりのロジックについては私なんかより こちらの解説 に明るいです。


とまれ、後者の方法で求めると 2858 枚(1650 枚 × √3) となります。先ほどの正規分布曲線を描画するのに必要な標準偏差は、この値を用いています。

1650×√3

[セルA8]リードタイム期間中の標準偏差 [セルB8]=B2*SQRT(B6)

晴花

54

1 日の消費量が正規分布にしたがうという前提のもとでは、在庫切れの可能性を確率的に限りなく低くすることはできても、結局、完全に排除することはできません。したがって「安全在庫をどの程度に設定すべきか」については、緊急的な在庫切れに際しての対応策を用意した上で「どの程度の頻度までなら在庫切れを許容できるか」の方を考えた方がこころ持ち的にもよさ気です。

実際の運用経験のない私にとっては悩ましいところですが、ま…初めてですしここでは 20 回 に 1 回くらいの確率を許容することで様子を見てみようかなと思います(危険率:5/100 = 0.05)。

[セルA10]危険率α [セルB10]0.05

晴花

55

ということで…20 回 に 1 回の確率で在庫が切れるケースを考えてみます。

下図 リードタイム中の消費量に関する正規分布のグラフにおいて、右から 5% の領域を占める用紙の枚数は赤い点で示されます(上側 5% 点)。Excel でパーセント点を求める関数は累積(下側)確率を引数に求めてきますので、赤い点(パーセント点)の具体的な値は下図のうすい青色の領域の面積(95%)を使って計算することとします。

晴花

56

では、具体的に 上側 5% 点 を計算してみます。

下の図のようなシートの構成のとき、


ver.2010 or later=NORM.INV(1-B10,B7,B8)
ver.2007=NORMINV(1-B10,B7,B8)

という式になります。

[セルA11]上側αパーセント点 [セルB11]=NORM.INV(1-B10,B7,B8)

晴花

57

上側 5% 点…つまりあたらしい発注点は 27201枚 となりました(27201-22500=4701枚が安全在庫であることを意味します)。


結果、以上の手順でみちびいた定量発注方式による発注プランとして、


発注量49625 枚
発注点27201 枚

とする案が用意できました(完了)。

FINISHED

経済的発注量と発注点の計算・完了

  • 本頁で使用したデータはすべて架空のものです。また特定の会社等に実在する人物名、および同場所で実際に観測されたデータ群などを根拠にしたものでもありません。
.

LastUpdate

2016.7.12

.
このページの先頭へ