予実差異分析をExcelで自動化する方法｜数量・単価・為替の影響を分解する
毎月の予実管理で、売上高の増減を集計するだけで終わっていませんか？
売上が予算を上回った場合でも、販売数量が増えたのか、単価が上がったのか、為替レートが変動したのかによって、評価は異なります。
今回は、数量差異・単価差異・為替差異を自動計算できるExcelテンプレートを無料配布します。
テンプレートにはA001・A002・A003の3製品のサンプルデータを収録しています。この記事では、実際のExcelファイルと同じ数値を使い、入力方法、計算結果、実務での注意点を説明します。
予実差異分析Excelテンプレートを無料配布
今回のExcelテンプレートでは、次の2種類の分析ができます。
- 数量・単価分析：販売数量と販売単価の変化による売上差異を計算
- 数量・単価・為替分析：海外売上の円換算差異を3つの要因に分解
いずれのシートにも3製品分のサンプルデータが入力されており、計算式と合計行を設定しています。
Excelテンプレートの構成と使い方
ファイルには次の3シートがあります。
| シート名 | 内容 |
|---|---|
| 00_使い方 | 操作方法と注意事項 |
| 01_数量単価分析 | 3製品の数量差異・単価差異 |
| 02_数量単価為替分析 | 3製品の数量・単価・為替差異 |
基本的な使い方は次のとおりです。
- 分析したいシートを開く
- 青色のセルに予算数量・単価・実績数量などを入力する
- 薄緑色のセルで自動計算された差異を確認する
- 最終行の合計値を確認する
- 照合差額が0であることを確認する
注意：計算セルには数式が設定されています。誤って数式を上書きしないようにしてください。
数量・単価分析シートのサンプルデータ
「01_数量単価分析」シートには、次の3製品が登録されています。
| 製品 | 予算数量 | 予算単価 | 実績数量 | 実績単価 |
|---|---|---|---|---|
| A001 | 1,000 | 10,000円 | 900 | 11,000円 |
| A002 | 500 | 20,000円 | 550 | 21,000円 |
| A003 | 800 | 7,000円 | 750 | 6,800円 |
数量差異と単価差異は次の式で計算します。
数量差異＝（実績数量－予算数量）×予算単価
単価差異＝（実績単価－予算単価）×実績数量
3製品の計算結果
実際のExcelに設定されている計算式から求めた結果は次のとおりです。
| 製品 | 予算売上 | 実績売上 | 売上差異 |
|---|---|---|---|
| A001 | 1,000万円 | 990万円 | ▲10万円 |
| A002 | 1,000万円 | 1,155万円 | ＋155万円 |
| A003 | 560万円 | 510万円 | ▲50万円 |
| 合計 | 2,560万円 | 2,655万円 | ＋95万円 |
数量差異・単価差異の内訳
| 製品 | 数量差異 | 単価差異 | 売上差異 |
|---|---|---|---|
| A001 | ▲100万円 | ＋90万円 | ▲10万円 |
| A002 | ＋100万円 | ＋55万円 | ＋155万円 |
| A003 | ▲35万円 | ▲15万円 | ▲50万円 |
| 合計 | ▲35万円 | ＋130万円 | ＋95万円 |
3製品合計では、数量差異が▲35万円、単価差異が＋130万円となり、売上差異は＋95万円です。
A001とA003では数量減少が発生していますが、A002の数量増加がその一部を補っています。また、A001とA002の単価上昇が全体の売上増加に貢献しています。
このように、製品別に差異を分解すると、単純な売上合計だけでは分からない変化を把握できます。
数量・単価・為替分析シートのサンプルデータ
次に「02_数量単価為替分析」シートを確認します。
こちらもA001・A002・A003の3製品が登録されています。販売単価は米ドル、為替レートは円/米ドルで設定しています。
| 製品 | 予算数量 | 予算単価 | 実績数量 | 実績単価 |
|---|---|---|---|---|
| A001 | 1,000 | 100ドル | 900 | 110ドル |
| A002 | 500 | 200ドル | 550 | 210ドル |
| A003 | 800 | 75ドル | 760 | 73ドル |
為替レートは、3製品とも次の設定です。
- 予算レート：140円/ドル
- 実績レート：150円/ドル
円換算売上高は、次の式で求めます。
円換算売上高＝販売数量×外貨単価×為替レート
3製品の円換算売上高
| 製品 | 予算売上 | 実績売上 | 売上差異 |
|---|---|---|---|
| A001 | 1,400万円 | 1,485万円 | ＋85万円 |
| A002 | 1,400万円 | 1,732.5万円 | ＋332.5万円 |
| A003 | 840万円 | 832.2万円 | ▲7.8万円 |
| 合計 | 3,640万円 | 4,049.7万円 | ＋409.7万円 |
3製品合計では、予算売上3,640万円に対して実績売上4,049.7万円となり、409.7万円のプラス差異です。
数量・単価・為替差異の内訳
このテンプレートでは、数量、単価、為替の順に実績値へ置き換えて差異を計算します。
- 数量差異＝（実績数量－予算数量）×予算外貨単価×予算レート
- 単価差異＝（実績外貨単価－予算外貨単価）×実績数量×予算レート
- 為替差異＝（実績レート－予算レート）×実績数量×実績外貨単価
| 製品 | 数量差異 | 単価差異 | 為替差異 | 売上差異 |
|---|---|---|---|---|
| A001 | ▲140万円 | ＋126万円 | ＋99万円 | ＋85万円 |
| A002 | ＋140万円 | ＋77万円 | ＋115.5万円 | ＋332.5万円 |
| A003 | ▲42万円 | ▲21.28万円 | ＋55.48万円 | ▲7.8万円 |
| 合計 | ▲42万円 | ＋181.72万円 | ＋269.98万円 | ＋409.7万円 |
合計の409.7万円の増加は、数量差異▲42万円、単価差異＋181.72万円、為替差異＋269.98万円に分解できます。
今回の例では、為替変動によるプラス影響が最も大きいことが分かります。
一方、A003では数量減少と単価低下が発生しており、円安によるプラス影響があっても、売上高は予算を下回っています。
このように、全体では売上が増加していても、製品別に見ると改善が必要な項目が見つかることがあります。
Excelの数式と照合チェック
今回のテンプレートでは、2～4行目に3製品のデータがあり、5行目に合計が設定されています。
数量・単価分析シート
1 2 3 4 5 6 | F2 =B2*C2 G2 =D2*E2 H2 =(D2-B2)*C2 I2 =(E2-C2)*D2 J2 =G2-F2 K2 =J2-H2-I2 |
これらの式が3行目、4行目にも設定されています。
5行目ではSUM関数を使って、3製品の差異を集計します。
数量・単価・為替分析シート
1 2 3 4 5 6 7 | H2 =B2*C2*D2 I2 =E2*F2*G2 J2 =(E2-B2)*C2*D2 K2 =(F2-C2)*E2*D2 L2 =(G2-D2)*E2*F2 M2 =I2-H2 N2 =M2-SUM(J2:L2) |
こちらも3行目、4行目に同じ考え方の数式が設定されています。
照合差額が0になっていることを確認してください。
ただし、照合差額が0でも、入力データそのものが正しいとは限りません。実務では元データとの照合も必要です。
Excelテンプレートを使う際の注意点
サンプルは3製品分で、5行目が合計
今回のExcelでは、A001・A002・A003の3製品が2～4行目に入力されています。
5行目は合計行です。新しい製品を追加する場合は、合計行を移動し、数式とSUM関数の範囲を修正してください。
単に5行目へ新しい製品を入力すると、既存の合計数式を消してしまうため注意が必要です。
入力セルと計算セルを区別する
青色のセルは入力用、薄緑色のセルは計算用です。
計算セルの数式を上書きすると、差異分析が正しく行われなくなる可能性があります。
数量が0の場合は別途処理する
実績売上を実績数量で割って実績単価を計算する場合、数量が0だとゼロ除算エラーが発生します。
例えば、実績数量がD2、実績売上がE2の場合は、次のように処理できます。
1 | =IF(D2=0,"",E2/D2) |
ただし、数量0で売上金額が発生している場合は、返品や調整伝票などを確認する必要があります。
新製品・販売終了品は自動判定しない
今回のテンプレートには、新製品や販売終了品を自動判定する機能はありません。
予算単価が存在しない新製品などは、別途処理ルールを設けてください。
製品構成差異は独立して計算していない
複数製品の数量差異には、総販売数量の増減だけでなく、製品構成の変化による影響も含まれる場合があります。
必要に応じて、数量差異を総数量差異と製品構成差異に分ける分析を検討してください。
為替レートの定義を統一する
今回の為替分析シートでは、予算レート140円/ドル、実績レート150円/ドルを使用しています。
実務では、予算レート、月中平均レート、取引日レートなど、どのレートを使用するかを統一する必要があります。
また、今回の為替差異は円換算売上高の差異であり、会計上の為替差損益とは別のものです。
SAPなどのERPデータとの照合が必要
SAPなどからデータを取得して分析する場合は、次の項目を確認してください。
- 抽出対象期間が一致しているか
- 製品コードや通貨コードが正しいか
- 返品・値引き・調整伝票を適切に扱っているか
- データの重複や欠落がないか
- ERPの売上高とExcelの集計結果が一致しているか
無料テンプレートでできること・できないこと
| 機能 | 対応状況 |
|---|---|
| 数量差異・単価差異 | 対応 |
| 為替差異 | 対応 |
| 3製品のサンプル分析 | 対応 |
| 差異合計の照合 | 対応 |
| 製品数の追加 | 数式・合計範囲の修正が必要 |
| CSVの自動取り込み | 未対応 |
| Power Queryによる自動更新 | 未対応 |
| SAPデータとの自動連携 | 未対応 |
| 新製品・返品などの例外処理 | 未対応 |
今回のテンプレートは、差異分析の基本的な計算方法を学び、自社での活用を検討するためのサンプルです。
実務ではPower Queryによる自動化もおすすめ
今回の無料テンプレートは、Excelに予算・実績データを入力する方式です。
製品数が数百、数千になる場合や、毎月SAPなどからCSVを出力する場合には、Power Queryによる自動化が有効です。
例えば、次のような作業を効率化できます。
- 予算データの取り込み
- 実績データの取り込み
- 製品コード・年月などによる結合
- 為替レートの結合
- 数量・単価・為替差異の計算
- 製品別・工場別の集計
毎月のデータ加工を減らすことで、差異の原因分析や改善策の検討に時間を使えるようになります。
Excel・Power Queryによる業務改善のご相談
今回のテンプレートは基本的な差異分析のサンプルですが、実際の業務では複数拠点のデータ統合、製品コードの不一致、SAP出力データの加工など、個別の対応が必要になることがあります。
業務改善ラボでは、Excel・Power Queryを活用した予実管理や月次集計の効率化について、個別のご相談も受け付けています。
自社データに合わせた集計ツールの作成や既存Excelファイルの改善をご検討の方は、お問い合わせページからご相談ください。
よくある質問
Q. Excelのマクロを有効にする必要はありますか？
いいえ。今回のファイルは通常のExcel数式を使用しており、VBAマクロは使用していません。
Q. 3製品より多く分析できますか？
はい。明細行を追加し、数式と合計範囲を修正すれば対応できます。ただし、行追加に自動対応する仕組みは設定していないため、計算結果を必ず確認してください。
Q. 数量・単価分析と為替分析で製品の数値が違うのはなぜですか？
2つのシートは別々の計算例です。数量・単価分析シートでは円単価を、為替分析シートでは米ドル単価と為替レートを使用しています。
同じ製品コードでも、両シートの売上金額が一致する設計ではありません。
Q. 差異チェックが0にならない場合は？
入力した数量・単価・為替レート、計算式、追加した行の参照範囲を確認してください。
まとめ：3製品のサンプルで差異分析を試してみよう
今回のExcelテンプレートでは、A001・A002・A003の3製品を使って予実差異分析を試せます。
- 数量・単価分析では、3製品合計の売上差異が＋95万円
- 数量・単価・為替分析では、3製品合計の売上差異が＋409.7万円
- 各製品の数量・単価・為替の影響を確認できる
- 照合差額で計算結果の整合性を確認できる
- 実務利用時はデータ構造や管理会計ルールに合わせた調整が必要
まずはサンプルデータの数量、単価、為替レートを変更し、それぞれの差異がどのように変化するか確認してみてください。
予実差異分析を自動化し、毎月の集計作業から原因分析へ時間を振り向けていきましょう。
※本テンプレートのデータは架空の数値です。実務で使用する場合は、社内の管理会計ルールや対象取引の内容に合わせて計算方法を確認してください。
