発注点をExcelで計算する方法｜無料テンプレートと在庫表への設定例

発注点は、補充を確認する目安となる在庫数です。1日あたりの使用量×調達日数＋安全在庫で計算します。たとえば1日8個を使い、届くまで5日、安全在庫を12個と決めた商品なら、発注点は52個です。

発注点を計算する無料Excelをダウンロード。登録不要で、記事と同じ1商品分の記入例と数式が入っています。「発注点」シートの黄色い欄へ入力し、C4の結果を確認してください。

発注点52個は「在庫が52個になったら補充を確認する」という基準です。注文する数量は別に決めます。この記事では、計算した基準を在庫表へ手入力するところまで説明します。

まずC14の調達日数を5から6へ変えてみてください。C4の発注点が52から60へ変われば、最初の操作は完了です。確認後はC14を5へ戻します。C16の現在庫が52なら、C5は「要発注」と表示します。

日々の入出庫から現在庫を計算し、発注の状態もまとめて見たい方には、在庫管理 Excelテンプレートがあります。販売中v1.2は、商品マスタH列へ発注点を入力し、P列の現在庫とQ列の状態を確認する形式です。

購入するとExcelと説明テキストをまとめたZIPが届きます。商品登録300件・入出庫2,000行の集計式、棚卸表、ダッシュボードを用意済みです。初期設定と発注点の転記は販売版へ設定する手順で確認できます。

入出庫の記録と発注点の確認をまとめる

在庫管理 Excelテンプレート

¥2,200 税込買い切り

お買い物カゴに追加 画面と機能を見る

無料Excelの入力欄を自分の商品へ置き換える

元ファイルを保管し、コピーを開いてください。「発注点」シートのC9〜C17が入力欄です。1商品ごとにファイルまたはシートをコピーし、商品名と設定日が分かる名前で保存します。C4・C5・C20〜C22の数式は残してください。

数値の欄には「8個」のような文字を付けず、8だけを入力します。無料作例では使用量・日数・安全在庫に0以上の数値、現在庫に0以上の整数を使います。1個または1箱単位で数える商品が対象です。

使用量と調達日数の基準をそろえる

1日あたり使用量は、同じ期間の数量と日数で求める

たとえば直近30暦日に240個使ったなら、240÷30＝8個／日です。休業日も含む30日で割っています。調達期間も、発注してから使える状態で受け取るまでの暦日数へそろえてください。

同じ240個を20営業日で割ると12個／営業日になります。この場合は、納品までに使用する営業日数を数えます。仕入先の「3営業日」と自社で使用する3日が一致するとは限らないため、双方の休日と実際の到着予定日を照合してください。

この2例は前提が異なります。営業日を選ぶだけでは日数も使用量も換算されません。C11とC12が違う場合は計算を止めますが、同じ選択肢でも数値の基準まで自動確認する機能はありません。実際の期間を確認してから入力し直してください。

調達日数には、発注締切後の待ち時間や受入検品など、使えるようになるまでの時間も含めます。当日届いても数時間の使用があるなら、日数を0にせず、0.5日など実態に合わせます。毎週決まった日だけ発注する運用では、次の発注日までの待ち期間も別途考慮する必要があります。

個と箱は、使用量・安全在庫・現在庫をまとめて換算する

1箱10個の商品を「個」で管理するなら、2箱は20個です。C10を「箱」に変えるだけでは他の数字は変換されません。すべての数量を箱へ直して入力してください。

作例を箱へ換算すると、使用量0.8箱／日、安全在庫1.2箱です。5日分を足すと5.2箱なので、箱単位の発注点は6箱へ切り上げます。個単位の52個と、箱単位の6箱は、切上げ幅が違う結果です。端数の箱を開けて使うなら、現在庫も数えやすい「個」で管理する方法があります。

安全在庫は理由を残して決める

安全在庫は、使用量の増加や納品の遅れに備える数量です。この作例の12個は、8個／日の1.5日分を確保するという説明用の仮定です。12個で欠品を何％防げるかを計算した値ではありません。

まず過去の使用量と納品日を見て、平均より多く使った日や、予定より遅れた日を確認します。その数量を参考に、保管場所、使用期限、欠品したときの影響も含めて仮の数量を決めてください。C17へ「繁忙期は追加で2日分」など、判断した理由を残します。

履歴が少ない商品は、仕入先に確認した納期と使用予定から仮置きし、入荷のたびに実績を確認します。季節商品や一度に大量に使う資材は、過去の全期間の平均だけで決めると、次の使用予定を捉えにくくなります。需要や納期が変わったら、使用量・調達日数・安全在庫を一緒に見直してください。

標準偏差やサービスレベルを使う方法もありますが、その計算には需要と納期の変動に関する前提が必要です。無料作例では、安全在庫を利用者が決める入力値にしています。発注点の基本式は、物流支援サービスのLOCAL LOGITEXによる定量発注方式の解説でも確認できます。

白紙のExcelで発注点の式を設定する

無料ファイルを使う方は、この章の設定は不要です。自作する場合は、シート名を「発注点」にし、上の表と同じC9〜C17へ入力してください。B4は「発注点の目安」、B5は「現在庫との比較」、B20〜B22は順に「計算に使う入力の確認」「調達中の使用量」「切上げ前の発注点」とします。

最初にC10を「個,箱」、C11とC12を「暦日,営業日」の選択式にします。PC版Excelでは対象セルを選び、［データ］→［データの入力規則］でリストを選択し、元の値へ入力します。C13〜C15は0以上の小数、C16は0以上の整数を設定してください。貼り付けでは規則をすり抜けることがあるため、数式でも入力を確認します。

C20で単位と入力を確認する

C20へ次の式を入れます。初期例では「計算可」です。数値が空欄・文字・負数のときや日数基準が違うときは、確認する内容を表示します。0は入力済みの数値として扱います。

=IF(OR(C10="",C11="",C12=""),"単位・日数基準を入力",IF(OR(AND(C10<>"個",C10<>"箱"),AND(C11<>"暦日",C11<>"営業日"),AND(C12<>"暦日",C12<>"営業日")),"選択肢を確認",IF(C11<>C12,"日数基準を揃える",IF(COUNT(C13:C15)<3,"使用量・日数・安全在庫を入力",IF(MIN(C13:C15)<0,"負数を直す","計算可")))))

C21・C22で調達中の使用量と安全在庫を足す

C21に次の式を入れると、8×5＝40になります。C20を先に設定してください。

=IF(C20<>"計算可","",C13*C14)

C22に安全在庫を足す式を入れます。結果は40＋12＝52です。

=IF(C20<>"計算可","",C21+C15)

C4で整数へ切り上げる

=IF(C20<>"計算可","",ROUNDUP(C22,0))

C4の結果は52です。1個単位で管理するため、3.2×4＋7.5＝20.3のような端数は21へ切り上げます。表示形式を整数にしただけでは内部の端数は残るため、ROUNDUP関数を使います。桁数0の指定はMicrosoftのROUNDUP関数の説明でも確認できます。

C5で現在庫と比べる

=IF(C20<>"計算可","入力を確認",IF(COUNT(C16)<1,"現在庫を入力",IF(OR(C16<0,MOD(C16,1)<>0),"現在庫を確認",IF(C16=0,"欠品",IF(C16<=C4,"要発注","発注点超")))))

C16が52なら「要発注」、53なら「発注点超」、0なら「欠品」です。「発注点超」は今回の基準を上回る意味で、在庫量が適切かどうかの総合判定ではありません。

空欄・0・発注点と同じ数量を確認する

「入力を確認」と出たら、C20の表示を先に読みます。計算に使う3つの数値はC13〜C15です。「8個」などの文字を外し、負数や空欄を直してください。C20が「計算可」でも現在庫を入れていなければ、C5は「現在庫を入力」のままです。

試した後は、C10＝個、C11・C12＝暦日、C13＝8、C14＝5、C15＝12、C16＝52へ戻すと、この記事の作例になります。自分の商品へ置き換えるときは、試験した数値を残さず、C17の根拠も更新してください。

計算した発注点を販売版の在庫表へ設定する

在庫管理 Excelテンプレートでは、日々の入出庫から現在庫を集計します。無料作例で計算した発注点は、商品の単位を確認して手入力します。無料ファイルと販売版は自動連携されません。

購入後のコピーへ、商品と発注点を登録する

ZIPを展開して元のExcelを保管し、コピーの「はじめに」を読んでください。「設定」で会社・店舗名、分類、保管場所を整えます。新しく始める場合は、「商品マスタ」A7〜L306と「入出庫」A7〜G2006のサンプル入力値を消します。棚卸表の入力値はF7〜F306とI7〜I306です。行やシートを削除せず、自動計算欄を残してください。

作例の1商品を登録するなら、商品マスタA7へC21-01、B7へ梱包クリップ、E7へ個を入力します。H7に発注点52、I7に発注ロット10、J7に上限在庫100、K7に仕入先、L7に開始時点の実在庫52を入れてください。分類・場所・単価なども自社の値へ置き換えます。

L7の期首在庫52個を、入出庫にも「入庫52」と重ねて記録しないでください。管理開始後に動いた分だけを、入出庫のA列へ日付、B列へ区分、C列へ商品コード、D列へ数量として記録します。現在庫P列は数式のため上書きしません。

発注点52個と推奨発注数50個を区別する

今回の作例は現在庫52個で発注点に達しています。上限在庫100個との差は48個ですが、発注ロット10個単位へ切り上げるので、U7の推奨発注数は50個になります。入荷までに使用も追加の入荷もなければ、入荷後は102個です。ロットによって上限在庫を超えることがあります。

販売版v1.2は、現在庫が発注点以下のときに推奨発注数を計算します。上限在庫に正の数があればその数を、空欄・0なら発注点の2倍を補充の基準に使い、現在庫との差をロット単位で切り上げます。最低1個分を計算し、ロットが空欄・0なら1個単位になります。

発注点と同じ正の現在庫は「要発注」、0以下は「欠品」が先に表示されます。負の現在庫を見つけた場合は、まず記録漏れや数量の誤りを調べてください。発注点H列の空欄は0として扱われるため、無料作例で計算した値の入力を忘れないようにします。

推奨発注数には、注文済みでまだ届いていない発注残が含まれません。同じ「要発注」が出続けても、そのたびに注文せず、発注書や注文履歴で数量と納期を照合してください。安全在庫の統計計算、発注残の相殺、EC・POS連携、自動発注は販売版の機能に含まれません。

無料作例と販売版で行う作業の違い

無料作例は、1商品の補充基準を決めるときに使う

使用量・調達日数・安全在庫から発注点を計算し、手入力した現在庫と比べます。発注点の根拠を整理する用途です。日々の入出庫履歴や、注文する数量の計算は別に管理します。

販売版は、日々の在庫と発注の状態を続けて管理する

300商品の入力枠、入出庫2,000行の集計式、状態判定、ロットを使う推奨発注数、棚卸表を使えます。自作する場合に必要な、商品コードごとの集計や状態表示、棚卸用の表を設定する作業を省けます。発注点やロットの決定、入力、仕入先への注文は利用者が行います。

対応環境の案内はPC版Excel・Web版Excel・Googleスプレッドシートです。PCでの利用をおすすめします。無料作例と販売商品は別ファイルで、販売商品には無料教材を追加したわけではありません。

現在庫の集計・棚卸・発注の確認を同じExcelで続ける

在庫管理 Excelテンプレート

¥2,200 税込買い切り

お買い物カゴに追加 画面と機能を見る

買い切り・テンプレートの月額料金なし
お支払い完了後、Excel＋説明テキストのZIPをダウンロード

カートの商品で購入手続きへ →

在庫表そのものを自作する場合は、入出庫から現在庫を計算する在庫管理表の作り方をご覧ください。記録と現物が合わない場合は、無料棚卸表の記入と差異の調整手順で数え直しと記録を先に確認できます。

運用を始めたら、入出庫を記録するたびに現在庫と状態を確認し、発注前に注文済み数量と納期を照合してください。週に1回など決まった間隔だけで判断する場合は、その待ち期間中にも使用が進みます。この記事の単純な発注点をそのまま流用せず、発注間隔を含めて補充基準を見直します。