実地棚卸の集計表をExcelで作る方法【原価法・低価法と評価損】

決算の利益を最後に動かすのが期末棚卸です。実地棚卸のカウントミスや集計の転記ミスは売上原価に直結し、利益を狂わせます。さらに「売れ残った古い在庫を評価損で落としたい」という相談には、税務上の厳格な要件という落とし穴が待っています。

この記事では、実地棚卸の集計フローをExcelで整理する方法と、評価方法の選択・滞留在庫の評価損の考え方を解説します。

実地棚卸の基本フロー

• ①事前準備：棚卸原票（ロケーション・品名・数量の記入用紙またはリスト）の準備、締めルール（棚卸中の入出庫停止）の周知
• ②カウント：2人1組（数える人・記録する人）で実施。原票には連番を付け、全数回収を確認
• ③集計：原票をExcelに入力し、品目マスタと突合して金額化
• ④差異分析：理論在庫（帳簿）との差異を確認し、大きな差異は再カウント

Excel集計表の作り方

ステップ1：原票入力シート

原票1枚=1行で「原票No.・ロケーション・商品コード・数量・カウント者」を入力します。品名・単価はマスタからVLOOKUPで自動表示し、手入力を数量だけに絞るのが転記ミス防止の要点です。

品名：=IFERROR(VLOOKUP(商品コード,マスタ,2,FALSE),"コード不明！")
原票の回収漏れチェック：=IF(COUNT(原票No.列)<>MAX(原票No.列),"欠番あり","OK")
同一商品の重複カウント検知：=IF(COUNTIFS(商品コード列,商品コード,ロケ列,ロケ)>1,"重複？","")

ステップ2：金額化と評価方法

集計シートで商品コード別に数量をSUMIF集計し、評価単価を掛けて金額化します。評価方法は届出した方法によります（届出がない場合は最終仕入原価法）。中小企業の実務では最終仕入原価法が主流で、直近の仕入単価をマスタに反映しておけばExcelとの相性も良好です。

在庫金額：=SUMIF(入力!商品コード,集計の商品コード,入力!数量)*評価単価
棚卸資産合計：=SUM(在庫金額列)　→ 決算整理仕訳（期末商品棚卸高）へ

ステップ3：滞留在庫の見える化

最終入荷日（または最終出荷日）をマスタに持たせ、経過月数で滞留ランクを自動表示します。

滞留月数：=DATEDIF(最終出荷日,棚卸日,"m")
滞留ランク：=IFS(滞留月数>=24,"C：2年超",滞留月数>=12,"B：1年超",TRUE,"A：正常")

評価損はいつ計上できるか（重要）

「売れないから評価損」は税務では通りません。損金にできるのは、原則として次のような事実がある場合です。

• 物損等の事実：災害による損傷、品質変化（賞味期限切れ・破損・型崩れなど）
• 著しい陳腐化：季節商品の売れ残りで通常価額では今後販売できないことが実績で明らか、新製品の発売で型落ち品が通常の方法で販売できなくなった、など
• 低価法を選定している場合：期末時価が帳簿価額を下回れば評価損相当を計上できる（中小企業も届出により選択可）

単なる過剰在庫・物価下落は評価損の事由になりません。滞留ランクC=自動的に評価損、という運用は危険です。一方、廃棄処分すれば廃棄損として損金になります（廃棄の事実を写真・廃棄証明で残すこと）。評価損の要件は個別性が高いため、計上前に要件と証拠資料を必ず確認してください。

数値例：カウント差異のインパクト

ケース	期末在庫	売上原価	利益への影響
正しい棚卸	1,000万円	7,000万円	—
100万円の数え漏れ	900万円	7,100万円	利益△100万円（過少）
二重カウント100万円	1,100万円	6,900万円	利益＋100万円（過大→過大納税）

数え漏れは利益の過少計上（税務調査での指摘リスク）、二重カウントは過大納税。どちらに転んでも損です。原票の欠番チェックと重複検知という2つの数式が、この両方を防ぐ保険になります。

実務の注意点・つまずきポイント

• 期末日以外に棚卸する場合は入出庫調整を：営業の都合で決算日前後にカウントする場合、カウント日と期末日の間の入出庫を加減算する調整表が必要です。
• 預け在庫・預り在庫を忘れない：外注先への支給材、倉庫業者への預け品は自社在庫です。逆に預り品はカウントから除外します。ロケーション欄に「社外」を設けて管理しましょう。
• 未着品・積送品の計上漏れ：期末までに所有権が移転した仕入（未着品）は在庫計上が必要です。仕入計上基準と整合させてください。
• 評価方法の変更は届出が必要：最終仕入原価法から低価法への変更などは、原則として変更しようとする事業年度開始日の前日までの届出が必要です。
• 決算チェックリストと連動させる：棚卸集計・差異分析・評価損の要件確認を決算チェックリストの項目に組み込むと、毎期の品質が安定します。（関連記事：No.33 決算チェックリスト）

よくある質問

Q. 少額の貯蔵品（切手・収入印紙・消耗品）も棚卸が必要ですか？

原則は貯蔵品として計上が必要です。ただし毎期おおむね一定数量を取得し経常的に消費する消耗品は、継続適用を条件に取得時の損金処理が認められます。金額的に重要な未使用印紙・金券類は棚卸対象と考えてください。

Q. 理論在庫との差異はどこまで追うべきですか？

金額基準（例：1品目5万円以上）と率基準（例：±5%以上）を決めて、それを超えた品目だけ再カウント・原因調査するのが実務的です。差異原因（出荷記録漏れ・サンプル出庫・盗難等）の傾向は、翌期の在庫管理改善のヒントになります。滞留と併せてABC分析で管理の濃淡を付けましょう。（関連記事：No.85 ABC分析のやり方）

棚卸集計テンプレートのご案内

原票入力→欠番・重複チェック→金額集計→理論在庫との差異分析→滞留ランク表示までを1冊にまとめた実地棚卸集計Excelテンプレートをご用意しています。棚卸原票の印刷レイアウト付きで、準備からその日の集計まで完結します。

まとめ

• 棚卸の手入力は数量だけに絞り、品名・単価はマスタ参照にして転記ミスを防ぐ
• 原票の欠番チェックと重複検知で、数え漏れ・二重カウントの両方を防止する
• 評価は届出した方法で（中小は最終仕入原価法が主流。低価法も選択可）
• 評価損は物損・著しい陳腐化など要件が厳格。「売れないから」だけでは損金にならない
• 期末日とカウント日のズレ、預け在庫・未着品などの範囲論点を毎期チェックする