在庫評価を先入先出法と移動平均法でエクセル計算する方法と限界

先入先出法と移動平均法をエクセルで正確に計算する仕組みと、規模が大きくなると崩れる理由を、具体的な計算例で解説します。

「棚卸資産の評価額が先月と微妙に合わない」。在庫を抱える中小企業の経理担当者からこうした相談を受けることが増えています。多くの現場が先入先出法か移動平均法のどちらかをエクセルで計算していますが、仕組みは理解していても数式の中で何が起きているかまでは追い切れていません。

この記事では、両方の計算方法を電卓でも検算できる数値例で解説し、エクセルという道具そのものが抱える限界と、限界を超える前にできる実務対応を整理します。

棚卸資産の評価は、同じ仕入・出荷データでも計算方法によって結果が変わる

棚卸資産の評価方法とは 先入先出法・移動平均法・総平均法の位置づけ

棚卸資産の評価方法は「どの仕入単価を原価として払い出すか」を決めるルールで、代表例は先入先出法・移動平均法・総平均法の3つです。

3つの評価方法は、それぞれ異なる順序・タイミングで仕入単価を払い出しに割り当てる

在庫を評価するとは、倉庫に眠る商品や材料に「いくらの値札を付けるか」を決める作業です。同じ数量でも、いつ・いくらで仕入れたものが残っているとみなすかで、貸借対照表の金額と売上原価が変わります。仕入価格が一定なら方法の違いは表面化しませんが、価格変動がある商品を扱う企業では選択が利益の見え方に直結します。

先入先出法は「先に仕入れたものから先に払い出す」前提で計算し、期末に残るのは直近の仕入分です。移動平均法は仕入のたびに平均単価を再計算し、常に1つの平均単価で払い出します。総平均法は一定期間の仕入総額を数量で割って単価を出し、期末にまとめて確定させます。会計基準上どの方法を選ぶかは自社の状況と顧問税理士の判断によりますが、この記事では相談の多い先入先出法と移動平均法を、実際の計算過程まで踏み込んで解説します。

先入先出法をエクセルで計算する仕組み 具体的な数値で検算する

先入先出法は「古い仕入から順に払い出す」ため、期末在庫には直近の仕入単価だけが残り、価格上昇局面では売上原価が低めに出ます。

先入先出法の計算例：期末在庫70個・84,000円、売上原価301,000円(280個分)

具体例で見ていきます。ある商品の期首在庫が100個(単価1,000円・100,000円)、6月5日に150個を単価1,100円(165,000円)で仕入れ、6月12日に200個を出荷、6月18日に100個を単価1,200円(120,000円)で仕入れ、6月25日に80個を出荷したとします。

日付	取引	数量	単価	金額
期首	在庫	100個	1,000円	100,000円
6/5	仕入	150個	1,100円	165,000円
6/12	出荷	200個	—	—
6/18	仕入	100個	1,200円	120,000円
6/25	出荷	80個	—	—

6月12日の出荷200個は「期首100個(1,000円)＋6月5日仕入100個(1,100円)」から払い出すため原価は210,000円、6月5日仕入分が50個(55,000円)残ります。6月18日の仕入で在庫は150個(175,000円)になり、6月25日の出荷80個は「残り50個(55,000円)＋6月18日仕入分30個(36,000円)」から払い出すため原価は91,000円です。

期末在庫は6月18日仕入分の残り70個・84,000円、2回の出荷にかかった売上原価の合計は301,000円になります。仕入総額385,000円から売上原価301,000円を引くと期末在庫84,000円と一致し、電卓でそのまま検算できます。エクセルでは仕入・出荷の履歴を1行ずつ積み上げ、出荷のたびに「上から順に何個・何円分を消し込むか」をSUMPRODUCTやIF文の組み合わせで判定する構成が一般的です。

移動平均法をエクセルで計算する仕組み 端数処理が原価を左右する

移動平均法は仕入のたびに平均単価を再計算するため、同じ取引データでも先入先出法とは異なる原価が算出され、端数処理次第で結果がさらにぶれます。

移動平均法の計算例：期末在庫70個・80,733円、売上原価304,267円(280個分)

同じ数値例を移動平均法で計算すると結果が変わります。6月5日の仕入で在庫は250個・265,000円になり、平均単価は265,000円÷250個＝1,060円に更新されます。6月12日の出荷200個はこの単価で払い出し原価212,000円、残りは50個・53,000円です。

6月18日の仕入で在庫は150個・173,000円になり、平均単価は173,000円÷150個＝1,153.33円です。ここで端数が生まれます。単価を1,153円に丸めてから80個分を計算すると92,240円になり、正確な計算値92,266.67円との差である約27円が消えてしまいます。実務では単価ではなく金額ベースで按分する(80個÷150個×173,000円＝92,267円)ことで、この差をほぼ解消できます。この方法では期末在庫は80,733円、売上原価は304,267円になり、仕入総額385,000円との差もぴったり一致します。

先入先出法の売上原価301,000円と比べると、移動平均法は304,267円で3,267円高く、期末在庫は逆に3,267円低くなります。当社が受ける相談でも、この差額そのものより「単価をどこで丸めるか」を決めずに運用しているシートで数百円〜数千円のズレが積み重なり、原因が分からず頭を抱えているケースを頻繁に見かけます。

エクセルで評価方法を運用する際に起きる限界

エクセルでの在庫評価は、再帰計算のセル参照崩壊・端数処理の累積誤差・同時入力の競合・変更履歴の欠如という構造的な限界を必ず抱えます。

エクセルの構造的な限界：セル参照崩壊・端数処理の累積誤差・同時入力の競合・変更履歴の欠如

1つ目は移動平均法特有のセル参照崩壊です。移動平均は「前回までの残高」を毎回参照する再帰計算になるため、行の挿入・削除で参照先が1行ズレただけで以降の月がすべて連鎖的に狂います。当社が実際にシートを見せてもらうと、前月分をコピーして新しい月の列を作った際に参照範囲がずれ、誰も気づかないまま数ヶ月分の評価額が積み上がっているケースを何度も見ています。2つ目は先述の端数処理の累積誤差で、丸めのルールを統一していないと差額が毎月少しずつ蓄積します。

3つ目は同時入力の競合です。複数拠点や複数担当者が同じブックを同時に更新すると、片方の変更が上書きされて消える事故が起きます。共有ブック機能はありますが動作が不安定で、安心して使い続けている現場をほとんど見たことがありません。4つ目は変更履歴の欠如です。誰がいつどのセルを書き換えたかを記録する仕組みがなく、数字が合わない原因を後から追跡できません。品目数や取引件数が増えるほど、これら4つの限界は同時に表面化しやすくなります。

限界を超えるための実務対応 今エクセルでできること

エクセルの限界を超える前にできる対応は、端数処理ルールの統一・変更履歴の記録・評価方法の継続運用・規模の閾値の見極めの4つです。

規模の閾値を見極める前に、まず今のエクセル運用でできる対応から着手する

いきなりシステムに置き換える前に、今の運用のままでも打てる対策があります。端数処理は「単価を丸めてから計算する」のではなく「金額ベースで按分してから最後に調整する」ルールに統一することです。これだけで移動平均法の累積誤差はかなり小さくできます。月次の評価額を確定させたら別シートかPDFで保存し、後から数式を追わなくても「いつ・いくらだったか」を追跡できる状態を作ることも、変更履歴の欠如をカバーする現実的な手段です。

評価方法そのものは、正当な理由なく毎期変更しないことも重要です。先入先出法と移動平均法を行き来すると期間比較ができなくなり、税務調査で説明を求められる可能性が出てきます。そのうえで、品目数が数百を超えた、拠点や担当者が複数に増えた、棚卸差異の原因調査に毎回半日以上かかる、といったサインが重なってきたら、構造的な限界に近づいているサインです。自社が今どの段階にいるのか判断に迷う場合は、初月無料の経営AI診断(通常30万円相当)で現状の在庫評価シートを一緒に確認し、優先度の高い対応から整理することもできます。

まとめ

先入先出法と移動平均法は、同じ仕入・出荷データを使っても算出される売上原価と期末在庫の金額が変わります。今回の数値例では両方とも3,267円の差になりました。どちらの方法を採用するにせよ、セル参照崩壊・端数処理の累積誤差・同時入力の競合・変更履歴の欠如という4つの限界は避けて通れません。まずは端数処理ルールの統一と変更履歴の記録から着手し、規模の閾値に近づいてきたと感じたら、初月無料の経営AI診断で自社の在庫評価の現状を可視化してみてください。

よくある質問

先入先出法と移動平均法、どちらを選べばいいですか

業種や在庫の性質によって向き不向きが分かれます。仕入価格の変動が大きく原価の実態を正確に追いたい製造業・卸売業では先入先出法が、日々の出入りが多く平均的な原価で管理したい小売業では移動平均法が扱いやすい傾向にあります。どちらを選んでも、一度決めた方法を継続して使うのが原則です。

エクセルで移動平均法を計算すると数値が微妙にずれるのはなぜですか

移動平均法は仕入のたびに単価を再計算するため、小数点以下の端数が必ず発生します。丸めた単価をそのまま次の計算に使うと誤差が入出庫のたびに積み重なり、月末の在庫金額が数百円から数千円単位でずれることがあります。丸めのルールを統一していないシートほどズレは大きくなりやすい傾向です。

評価方法は途中で変更してもいいですか

変更自体は可能ですが、正当な理由なく毎期変更すると期間比較ができなくなり、税務調査や監査で説明を求められる可能性があります。変更する場合は理由と影響額を記録に残しておくことが望ましく、実際の適用可否は顧問税理士に確認しながら進めるのが安全です。

エクセルでの在庫評価がどのくらいの規模で限界を迎えますか

明確な行数の境界線はありませんが、当社が受ける相談では「品目数が数百を超えた」「拠点や担当者が複数に増えた」「棚卸差異の原因調査に毎回半日以上かかる」の3つが重なったタイミングで限界を感じる企業が多い印象です。この段階に達したらシステム化の検討を始める企業が目立ちます。

関連記事

• 在庫管理システムの費用相場と選び方 失敗しない脱エクセルの判断基準 — 関連: システム化の費用判断
• 在庫管理をエクセルで自動化する方法 関数とマクロの実装と限界 — 関連: エクセル運用全般の自動化と限界
• 在庫管理エクセルのマクロ化はどこまで有効か VBAの限界と脱エクセルの分岐点 — 関連: マクロ運用の限界という共通テーマ
• 在庫回転率・滞留在庫をエクセルで分析する方法 発注点と欠品を防ぐ実務 — 関連: 在庫分析の別切り口