発注量の計算は、Excelでどこまでできるか

Excelで出せるのは平均と幅まで。3つの計算を役割で分ける

発注量をExcelで決めるときに使える計算は、大きく3つあります。同じ条件の日だけを集めた平均、前年の同じ時期との比較、そして移動平均です。この3つは、それぞれ答えられる問いが違います。

あすいくつ頼むかの目安になるのは、1つ目の平均です。同じ曜日、同じ営業形態の日だけを集め、平均と最小・最大を出します。2つ目の前年同期との比較は、季節によって数量が入れ替わる品目に使います。3つ目の移動平均は、売れ方が上向きか下向きかを確かめるために使います。

3つを1つの列にまとめようとすると、どれも中途半端になります。シートの列を分け、平均の列、前年との倍率の列、移動平均の列として並べておくと、どの数字を見て発注したかが後から追えます。

同じ曜日だけを集めて平均する。混ぜると曜日の差が数字から消える

手順は、日付と数量の2列から始まります。日付の隣に曜日の列を足し、同じ曜日の行だけを集めて、平均と最小・最大を出します。8週ぶんの記録があれば、同じ曜日が8回そろいます。

試算例として、ある品目の金曜の販売数が、8週前から順に9・12・10・15・11・13・12・16個だったとします。8週の平均は12.3個、最小は9個、最大は16個です。同じ品目を曜日で分けずに平均した1日8個と並べると、金曜だけで4個以上の開きがあります。この開きは、曜日を混ぜた平均からは読み取れません。

曜日をそろえるのは、暦そのものが数量を動かすからです。総務省統計局は季節的な動きを除去という解説で、季節変動の要因として、天候や気温などの自然条件、月による日数や休日の違いといった暦の要因、中元や歳暮のような制度・習慣からの影響を挙げています。曜日の差は、このうち暦の要因を1週間の単位で見たものにあたります。

平均を出す前に、記録から外す日を決めます。臨時に休んだ日、棚卸のために早く閉めた日、催しで普段と客層が違った日です。こうした日を極端な数字のまま平均に入れると、平均も最小・最大も動きます。外した日は消さずに、別の列へ理由を書いて残してください。同じ条件がまた来たときの材料になります。

移動平均は何をならすのか。翌週の数量はここから出ない

移動平均の定義は、統計局のその他のデータ分析手法という解説に書かれています。各月の移動平均値として、その月を含む一定期間の平均値を使う方法で、その月と前後の月を使う中央移動平均、その月とそれ以前の月を使う後方移動平均、その月とそれ以後の月を使う前方移動平均があると説明されています。同じ考え方を、週や日の単位にも当てはめられます。

発注で使えるのは後方移動平均です。中央移動平均はその後の週の数字が必要になるため、いちばん新しい週の値が出ません。先ほどの8週の例で4週の後方移動平均を出すと、直近の4週が13.0個、その前の4週が11.5個になります。売れ方は上向きです。

ここで気をつけたいのは、直近4週の移動平均13.0個が、いちばん新しい週の実績16個より低いことです。移動平均はその前の3週ぶんを一緒に平均するので、直近の動きに遅れて付いてきます。上向きの品目で移動平均をそのまま発注数にすると、足りない状態が続きます。

同じ解説では、移動平均は季節調整の手法にも使われています。季節調整は、季節による変動を取り除いた数字を出すための処理です。統計局は、企業では消費の需要に季節性がある場合、季節性を含んだデータをもとに商品の生産量を決めたりする、とも書いています。発注に要るのは、季節の波を含んだままの数量のほうです。

季節をまたぐときは、前年の同じ時期と比べる

直近8週の平均でまかなえるのは、8週のあいだ売れ方が大きく動かない品目です。夏物と冬物のように季節で入れ替わる品目や、年に数回しか出ない品目では、去年の同じ時期の記録を見ます。

統計局は、季節要因を考えなくて済む最も簡単な方法として、当月と前年同月を比較することを挙げています。あわせて、前年との比較であるため、前年に急激な変動があった場合は前年の動きに影響されること、動向をつかむのが遅れることを注意点として示しています。店の記録でも同じです。去年その時期に工事で休んでいた、去年は大きな催しがあった、といった年と比べると倍率が大きく狂います。

日付をそろえるか曜日をそろえるかも、先に決めておきます。給料日や月末のように日付で動く品目は日付をそろえ、週末に集まる品目は曜日をそろえます。去年の8月の第3土曜と今年の8月の第3土曜、という並べ方です。

前年と比べられない品目もあります。品揃えを入れ替えたり、価格を変えたりしたあとは、前年の数量が目安になりません。変化のあとで何週たまったかを数え、たまった範囲だけで平均を出し直します。

平均を出す前に、記録のほうを直す

平均の計算そのものは短い式で済みます。時間を取られるのは、その前の記録の整理です。中小企業基盤整備機構の経営ハンドブック過剰在庫を見直すは、過剰在庫が起きる原因の1つとして、返品された分が出荷済みとして新しく発注され、返品在庫と新たに発注した在庫が積み重なることを挙げています。返品と取消を引かずに平均を出すと、売れた数を多めに見積もることになります。

同じ資料は、ネット販売と実店舗で同じ商品を売っている場合に、販路が2つあるぶん余分に発注しがちで、適正な在庫を把握できないまま過剰在庫になりやすいことも挙げています。販路ごとに数字が分かれているなら、平均を出す単位も販路ごとにするか、合算する範囲を先に決めてください。

サイズや色の展開がある商品についても、同じ資料は、特定のサイズや色だけが売れ残りやすいこと、過去の実績から売れ残りやすいサイズや色は発注を少なくする調整が要ることを挙げています。合計の数量だけで平均を出しても、どのサイズを何個頼むかまでは決まりません。品目をどこまで分けて見込むかは、商品別の発注量の記事にまとめています。

当社が相談を受けるときに最初に確かめるのも、この整理がどこまで済んでいるかです。日付と数量が1行ずつ並んでいて、返品と休業日の扱いが決まっていれば、平均も移動平均もその日のうちに出せます。決まっていなければ、まずそこから一緒に決めます。

Excelで続けられる範囲と、仕組みにするときに決めること

紙と表計算ソフトで始められるのは、品目を絞った状態です。売上の大きい10〜20品目について、曜日の列、平均の列、直近4週の移動平均の列を作り、週に1回だけ数字を入れ替える。この形なら、毎週30分ほどの作業で回せます。費用もかかりません。

続かなくなる合図は、計算の難しさよりも集計の量に出ます。品目が増えて数字の貼り付けが月末にまとめて発生している、手順を知っている人が1人しかいない、先月から更新が止まっている。製造業からの在庫の相談に答えた中小機構のビジネスQ&Aは、製品のアイテム数が非常に多い場合には、市販の販売管理や在庫管理のソフトを活用することを勧めています。同機構の経営ハンドブックも、取扱商品量が多い場合は人手だけに頼らず、在庫管理システムの導入を検討すべきだとしています。

当社が開発中の手軽に使える需要予測AIは、日付と数量の2列があるCSVから28日先までの予測を幅つきで出します。曜日の列を足したり移動平均の式を入れたりする作業は要りません。リリース時期は未定です。CSVを1つお預かりして、予測に使える状態かを確認する相談は無料です。試験予測に費用がかかる場合は、始める前に金額をお伝えします。

発注表の画面まで作る場合、見積もりのために確認するのは3点です。対象の品目数と、レジや販売管理から日付と数量を書き出せるかどうか。そして、在庫の残数や発注の締切まで画面に載せるかどうかです。客数から日配品の数量を出す形は食料品店の発注表の開発プランに、品目ごとの発注点を毎晩の発注に使う形は飲食店の発注表アプリの開発プランに書いています。

まとめ

発注数の目安に使うのは、同じ曜日だけを集めた平均と、その最小・最大です。曜日を混ぜた平均からは、曜日による差が消えます。

移動平均は傾向を確かめるための計算です。直近の動きに遅れて付いてくるので、上向きの品目では移動平均そのままの数量だと足りなくなります。季節をまたいで動く品目は、前年の同じ時期と比べます。

まず、売上の大きい1品目について、同じ曜日の直近8週を並べてみてください。平均と最小・最大の開きが、その品目でどれだけ余裕を持つ必要があるかの目安になります。