EOQ(経済的発注量)の計算をClaudeに任せ、現行ロットと総コストで比べる

EOQ(経済的発注量)は、年間需要・1回あたりの発注費用・在庫の保管費用の3つが決まれば、平方根1つで求まります。Claude for Excelには、その式をブックに組み込み、現行ロットとの総コストの差を表にする作業を任せられます。

ただし、発注費用と保管費率は、ブックの外にある数字です。Claudeに仮置きさせると、結論が静かにずれます。この記事では、3つの入力値を人が決めてブックに書き、式の組み立てと比較表づくりをClaudeに頼み、最後に手計算で照合する流れを示します。

EOQは何を最小にする発注量か

EOQは、発注にかかる費用と在庫を持つ費用の合計が、いちばん小さくなる1回あたりの発注量です。

1回の発注量を増やすと、発注回数が減るので、発注費用は下がります。一方で、平均在庫が増えるため、保管費用は上がります。両者の合計が底になる点が、EOQです。

EOQ(Q*) = √( 2 × 年間需要D × 1回の発注費用S ÷ 年間の単位あたり保管費用H )
年間総コスト(Q) = D ÷ Q × S + Q ÷ 2 × H

H(単位あたりの年間保管費用)は、単価に保管費率を掛けて出します。保管費率には、資金の機会費用、倉庫料、保険、陳腐化の見込みなどが入ります。

この式が成り立つ前提は4つあります。

• 需要が年間を通じてほぼ一定であること
• 発注した分が一度に入荷すること
• 欠品を許さず、数量割引や最低発注数量(MOQ)がないこと
• 発注費用が、発注量によらず1回あたり一定であること

前提が崩れる品目の扱いは、後半の「EOQが当てはまらない品目」で示します。

先に決める3つの入力値

Claudeに式を頼む前に、次の3つをブックの設定シートに書きます。ここを空欄にして任せると、一般的な値が仮置きされ、整った表のまま通ってしまいます。

発注費用Sには、発注書の作成と承認、入荷検品、入庫の入力、仕入先への支払処理にかかる人件費、1回ごとにかかる運賃を入れます。人件費は、1件あたりの作業時間に時間単価を掛けて出します。年間の事務コスト全体を発注回数で割る方法でも構いません。

保管費率は、会社ごとに根拠が違います。資本コスト、倉庫料、保険、陳腐化の見込みを積み上げて決めます。ここでは計算例として20%を使います。この数字はこの記事の計算例の前提であり、実在の企業の値ではありません。

設定シートは次のように作ります。

B1: 年間需要D       12000      (個)
B2: 発注費用S       5000       (円/回)
B3: 単価            800        (円/個)
B4: 保管費率        20%
B5: 保管費用H       =B3*B4     (円/個・年)   → 160
B6: 現行ロット      2000       (個/回)

年間需要の元になる出荷明細の集計は、安全在庫と発注点をClaudeにExcelで計算させる手順のステップ1と同じです。SUMIFSで月別に集め、12か月分を合計します。

使える環境と、作業前の準備

Claude for Excelは、Pro・Max・Team・Enterpriseの各プランで使えます。動作するのは、Excel on the web、Microsoft 365版のExcel on Windows(ビルド16.0.13127.20296以降)、Excel on Mac(バージョン16.46以降、ビルド21011600以降)です。

Excel 2016・2019の永続ライセンス版、iPad、Androidでは動きません。

作業前に、元のブックを複製して作業用のコピーを作ります。広く編集させる前に、信頼できるコピーで始めるのが基本です。仕入先から受け取ったファイルをそのまま開くと、隠れた指示文に操作を乗っ取られるおそれがあります。単価や発注費用は社内の数字を使い、外部のファイルに貼り付けて頼むことは避けます。

編集の途中で既存のデータを上書きしそうになると、Claudeは先に警告を出します。この警告は、設定シートのように入力値が入ったセルを守る役に立ちます。

ステップ1: EOQの式を組ませる

設定シートの値を参照して、EOQを出す式を入れてもらいます。依頼文には、どのセルが何かを書きます。

「設定」シートのB1が年間需要、B2が1回の発注費用、B5が単位あたりの
年間保管費用です。EOQを出す式を B8 に入れてください。
入力値は変更せず、空欄があれば式を入れる前に私に聞いてください。

返ってくる式は、例えば次のような形になります(例示であり、Claudeの出力そのものではありません)。

=SQRT(2*B1*B2/B5)

今回の入力値では、2×12,000×5,000÷160=750,000の平方根で、約866.03個になります。

発注は整数で行うので、そのまま使える数字ではありません。包装単位や入数がある品目は、その倍数に丸めます。丸め方は、切り上げ・切り下げの両方を試し、総コストが小さいほうを採ります。

切り上げ   =CEILING(B8, 100)
切り下げ   =FLOOR(B8, 100)

866個を100個単位に丸めると、切り下げは800個、切り上げは900個です。どちらが安いかは、次の総コスト表で比べます。

ステップ2: 総コストの比較表を作る

現行ロット、EOQ、丸めた候補を並べて、年間総コストを計算します。比較表の列は次のとおりです。

Claudeへの依頼は、列と式の意味を言葉で書きます。

「比較」シートのA列に候補名、B列に発注量を入れています。
C列に年間発注回数、D列に年間発注費用、E列に年間保管費用、
F列に合計を、設定シートの値を参照する式で入れてください。
保管費用は平均在庫(Q÷2)に単位あたりの年間保管費用を掛けます。

今回の入力値で計算すると、次の結果になります。

現行の2,000個とEOQの差は、年に約51,436円です。現行ロットの総コストを基準にすると、約27%の削減にあたります。

注目したいのは、EOQの近くでは曲線が平らな点です。900個と800個は、EOQとの差が数百円に収まります。1,000個でも、EOQより1.0%高いだけです。EOQちょうどでなくても、包装単位に合う数量を選べば、ほとんど損をしません。反対に、現行ロットのようにEOQの2倍を超えると、差が一気に開きます。

発注量が変わるたびに総コストがどう動くかを、ロットの列を変えて見る作業は、データテーブルに向いた用途です。ただしClaude for Excelはデータテーブルに対応していません。この記事の比較表のように、候補ごとに行を分けて通常の式で組む方法が現実的です。

ステップ3: 手計算で検算する

出てきた数字は、必ず1品目を手で確かめます。EOQには、検算しやすい性質があります。EOQの点では、年間発注費用と年間保管費用が等しくなります。

上の表でも、EOQの行は発注費用と保管費用がどちらも69,282円で揃っています。この2つが揃っていなければ、式のどこかが誤っています。

もう1つの検算は、EOQの2乗にHを掛けた値が、2×D×Sに等しいことです。

866.03^2 × 160 = 750,000 × 160 = 120,000,000
2 × 12,000 × 5,000            = 120,000,000

2つが一致すれば、平方根の式は正しく入っています。

Claudeの計算は、式として構造を見直せるぶん追いやすいのですが、結果の数字が正しい保証はありません。重要な計算は別の方法でも確かめるのが基本で、精度の考え方はClaude数式・計算の精度と検算が必要な理由で扱っています。

ずれたときに疑う箇所は、次の3つです。

• 保管費率が20でなく0.2になっているか(%書式の入力ミス)
• 保管費用が平均在庫(Q÷2)でなく、Qそのままになっていないか
• 年間需要が月次の値のまま入っていないか(単位の取り違え)

「総コストの列の式について、参照しているセルが何を指すかを一覧にして」と頼むと、Claudeが各参照の意味を説明します。誤った参照があれば、この説明の中に出てきます。

入力値が多少ずれても結論は動かないか

EOQの式は平方根なので、入力値の誤差が結果にそのまま乗りません。

発注費用Sを実際の2倍で見積もったとします。EOQは√2、約1.41倍になり、866個が1,225個になります。そこで総コストを計算すると、1,225個のときは約146,980円で、本来の最小値の138,564円より6%ほど高い程度です。

保管費率が15%や25%のときも、同じ見方ができます。入力値の精度よりも、現行ロットの桁が合っているかどうかのほうが、効いてくる場面が多くなります。

この性質は、発注費用の算出にどこまで手間をかけるかの目安になります。事務コストを精密に出せない品目でも、おおまかな見積もりでEOQの近傍までは寄せられます。

Claudeに頼むときは、入力値を3通り(下限・中央・上限)用意してEOQを並べる表を作らせると、感度が一目で分かります。

設定シートのS(発注費用)を 3,000 / 5,000 / 8,000 の3通りにして、
それぞれのEOQと総コストを「感度」シートに並べてください。
元の設定シートの値は書き換えないでください。

数量割引があるときは単価も比べる

仕入先が数量割引を出している品目では、EOQの式だけでは足りません。購入金額そのものが、発注量によって変わるためです。

比較の方法は、価格帯ごとに総コストを計算し、年間購入額を含めて比べることです。たとえば、1,000個以上の発注で単価が800円から780円になる品目を考えます。

780円の帯のEOQは約877個で、この帯の下限1,000個に届きません。このため、この帯で採れる最小のロットは1,000個です。購入額の差が大きいので、割引の帯が有利になります。

計算の中身は、1,000個の行が発注費用60,000円、保管費用78,000円、購入額9,360,000円で、合計9,498,000円という構成です。

このように、割引があるときの結論は、EOQよりも購入単価の差で決まります。ただし割引を取りに行くと、在庫の持ち越しが増え、期限や陳腐化のリスクも載ります。保管費率にそれが十分に反映されているかを確認します。

Claudeへの依頼は、「価格帯ごとに、その帯で採れる最小のロット(EOQが帯の範囲内ならEOQ、範囲より小さければ帯の下限)を求め、購入額を含む年間総コストを並べてください」と書きます。

EOQが当てはまらない品目

EOQは、需要が一定で補充が一括という単純なモデルです。次の品目では、計算結果をそのまま発注に使えません。

• 季節変動や販促で需要が大きく動く品目: 年間の平均需要でEOQを出すと、繁忙期に少なく、閑散期に多くなります。期間を区切るか、需要が日付で決まる場合は所要量から逆算するほうが合います。
• 賞味期限や使用期限のある品目: 保管できる期間に上限があるので、EOQより小さいロットで頭打ちになります。食品の廃棄を見ながら発注量を決める流れは、Claudeで廃棄ロスを日別に分析し、惣菜の発注量を見直す手順にあります。
• 運賃が車両単位やパレット単位で決まる品目: 発注費用が一定という前提が崩れます。積載単位の倍数を候補に入れて比べます。
• 最低発注数量(MOQ)が仕入先に決まっている品目: EOQがMOQを下回るときは、MOQが下限になります。
• 複数の品目を同じ仕入先からまとめて発注する場合: 発注費用を品目ごとに分けて計算すると、実態より高く見えます。

日付ごとに必要数量が決まる品目は、BOMから所要量を計算して発注日を逆算する方法が向きます。

どの品目から見直すかは、全品目に同じ手間をかけるより、年間の出荷金額が大きい品目を優先するほうが効果が出ます。出荷実績からその順位を付ける方法は、ABC分析で倉庫のロケーションを見直す手順の集計がそのまま使えます。

発注量を決めたあと、発注点とつなぐ

EOQが決めるのは、1回に発注する量です。いつ発注するかは、別の計算で決まります。発注点は、リードタイム中の需要に安全在庫を足した値で、EOQとは独立に求めます。

この2つを組み合わせると、在庫が発注点まで下がったらEOQの量を発注する、という定量発注の運用になります。発注点の出し方と、その検算の仕方は、安全在庫と発注点をClaudeにExcelで計算させる手順にまとめています。

使えない機能と、データの扱い

Claude for Excelは、データテーブルとマクロ・VBA操作に対応していません。EOQの感度分析をデータテーブルで組んでいる職場では、前述のように候補ごとに行を分けた表へ置き換えます。

推奨されない用途として、最終的な成果物を人の確認なしで渡すこと、監査で重要になる計算を検証なしで使うこと、機密性や規制の度合いが高いデータを統制なしで扱うことが挙がっています。発注ロットの見直しは社内の運用判断ですが、単価や取引条件を含むブックは、社内のデータ取り扱いルールと照らしてから使います。

会話の履歴は、ブラウザ内(IndexedDB)に保存されます。Anthropicのサーバーには保存されず、端末間でも同期されません。添付したファイルは、アップロードの28日後に失効します。入出力はバックエンドで、受領または生成から30日以内に削除されます。

毎回同じ前提で頼むためのInstructions

アドインのSettingsにあるInstructionsに前提を書いておくと、すべての会話に反映されます。この設定はExcelだけに効き、PowerPointやWordとは別です。

- 発注費用・保管費率・年間需要は設定シートの値を使い、勝手に変更しない
- 空欄の入力値があるときは、式を入れる前に質問する
- 発注量は整数で表示し、包装単位がある品目は倍数に丸めた候補も並べる
- 総コストの表には、発注費用と保管費用を別の列で示す

これらを書いておくと、仮置きの値が紛れ込む場面が減ります。

まとめ

EOQの計算は、式が短いぶん、入力値の置き方で結論が決まります。発注費用と保管費率を人が決めてブックに書き、Claudeには参照する式と比較表を組ませる分担にします。検算は、EOQの点で発注費用と保管費用が一致するかを見れば足ります。

現行ロットがEOQの2倍を超えている品目は、見直しの効果が表に出やすい対象です。EOQの近くは曲線が平らなので、丸めた数量でも十分に実用になります。