【予実管理のヒントを探す】エクセルで予実差異分析をしてみよう

Excel

営業活動をしている企業では、定期的に予算と実績をまとめています。
さらに予算と実績の差異を分析し、課題を見つけ、予算達成に向けて軌道修正を行っていきます。
そのような活動を予実管理といいます。

 

そして、「予実管理」という言葉があるということは「予実管理」業務の担当者が存在します。
予算管理システムが導入されている企業では、システム側でマクロな状況は把握できる事が多いと思います。
しかしながら、予算管理システムが導入されていてもミクロな分析をする場合には、エクセルの方が使い勝手が良い事が多いです。
また、予算管理システムが導入されていない企業では、現状はエクセルで予実管理を行っているところが多いと思います。

そこで、主に予算管理システムが導入されていない企業で「予実管理」をされている方に向けて、
エクセルによる予実差異分析方法をご紹介します。

 

スポンサーリンク

今回紹介する差異分析方法

売上/利益の変化を捉える方法に、マクロな捉え方とミクロな捉え方があります。

マクロな捉え方

マクロな捉え方は、売上/売上総利益/営業利益の予算と実績の変化を捉え、
その変化原因を売上増減と費用(変動費+固定費)の増減で概要を説明します。
この捉え方のメリットは、部門別(営業部門/工場部門/管理部門など)の費用増減と利益の関係を把握しやすくする事です。

ミクロな捉え方

ミクロな捉え方は、商品別/製品別の売上/原価/利益の明細データから予算と実績の変化を捉え、
その変化要因を数量変化や価格変化で説明します。
この捉え方のメリットは、商品別の戦略と利益の関係を把握しやすくする事です。

 

今回紹介するのはミクロな変化の予実差異分析方法

マクロな捉え方は、部門別の費用を入手する事が出来れば比較的簡単に分析する事ができます。
しかしながら、売上変化の変化の要因分析は別途行う必要があります。
つまり、利益の変化を説明するためには、売上についてはミクロに捉え方が必要になります。言い換えれば、ミクロな捉え方は分析の基礎となります。

そこで、今回はミクロな変化の差異分析方法を詳細に説明します。

予実差異分析の概要

分析を行うためには、商品別/製品別の数量(予算&実績)と売価(予算&実績)と原価(予算&実績)が必要です。
※自社生産工場を抱える企業ではPLと直結する商品別売上原価を”正確に”捉える事は困難ですが、
何かしらの形で商品別原価を把握していると思います。

 

売上を例にした分析イメージは下記の図の通りです。
薄い黄色の四角形は、予算の売上額を示していて【予算売上=予算数量 × 予算売価】で計算できます。

薄い黄色の四角形と濃い黄色のL字部分の合計は、実績の売上額を示していて【実績売上=実績数量 × 実績売価】で計算できます。

つまり、濃い黄色のL字部分が予算と実績の差異となります。
さらにL字部分は2つに分解する事が出来ます。
1つは数量変化による売上影響として【数量差異= (実績数量 ー 予算数量)× 予算売価】に分解します。
もう1つは価格変化による売上影響として【価格差異= (実績売価 ー 予算売価) × 実績数量】に分解します。

上のような計算式ですべての商品について、売上と原価の変化を分解していきます。

スポンサーリンク

 

エクセルでの差異分析方法について

それでは、具体的にエクセルでどのように分析していくかを説明します。

1. データの準備

先ほど説明したように、予実差異分析には
商品別/製品別の数量(予算&実績)と売価(予算&実績)と原価(予算&実績)
が必要です。
※売価と原価があれば、利益も計算できます。

実際のデータの形は各企業によって異なるとは思いますが、
例としてある家電メーカーの状況をイメージしたものを作ってみました。
予算データと実績データをそれぞれ別のシートに格納しました。
※データ形式は予算データと実績データは合わせておく必要があります。

予算データ

 

実績データ

 

2. 分析シートの作成

予算データと実績データを分析するために分析用のシートを作ります。

分析フォーマットの例を示します。
商品コードをキーにして、予算と実績データからVLOOKUP関数で情報を引用して作成します。

 

商品コードの設定

予算と実績のデータに含まれる商品コードをすべて記入します。
この例では、実績データだけに存在する商品コード0006も含めてB列に記入しています。
※商品コードが重複しないように気を付けてください。

 

商品情報データの引用

この例でのC列からE列は、「予算データと実績データの双方から情報取得」をします。
引用する関数の例としては、C4セルにおいては

=IFERROR(VLOOKUP($B4,予算!$B:$E,予実差異分析!C$1,FALSE),VLOOKUP($B4,実績!$B:$E,予実差異分析!C$1,FALSE))

となります。この式をC列からE列のセルにコピペします。
IFERROR関数を用いて、予算シートに存在しない商品コードがある場合には、実績シートの情報を引用するようにしています。

 

予算データの引用

この例でのF列からL列は、「予算データから情報取得」をします。
引用する関数の例としては、F4セルにおいては

=IFERROR(VLOOKUP($B4,予算!$B:$L,予実差異分析!F$1,FALSE),0)

となります。この式をF列からL列のセルにコピペします。
IFERROR関数を用いて、予算シートに存在しない商品コードがある場合には値を0にしています。

 

実績データの引用

この例でのM列からS列は、「実績データから情報取得」をします。
引用する関数の例としては、M4セルにおいては

=IFERROR(VLOOKUP($B4,実績!$B:$L,予実差異分析!M$1,FALSE),0)

となります。この式をM列からS列のセルにコピペします。
IFERROR関数を用いて、実績シートに存在しない商品コードがある場合には値を0にしています。

 

差異の要因を算出

売上と原価と利益の変化を、それぞれ数量差異と価格差異に分解します。

売上の数量差異

この例でのT列は、「数量差異による売上変化を算出」をします。
計算のための関数の例としては、T4セルにおいては

=IF(OR($F4=0,$M4=0),(N4-G4),($M4-$F4)*J4)

となります。この式をT列のセルにコピペします。
数式の意味は、
予算と実績のどちらかの数量が0の場合は、売上変化金額自体を数量差異として計上し、
予算と実績の双方の数量が0でない場合は、数量変化×予算売価を数量差異として計上する
という事になります。

スポンサーリンク

 

売上の価格差異

この例でのU列は、「価格差異による売上変化を算出」をします。
計算のための関数の例としては、U4セルにおいては

=IF(OR($F4=0,$M4=0),0,(Q4-J4)*$M4)

となります。この式をU列のセルにコピペします。
数式の意味は、
予算と実績のどちらかの数量が0の場合は、価格差異を0とし、
予算と実績の双方の数量が0でない場合は、売価変化×実績数量を価格差異として計上する
という事になります。

 

原価の数量差異

基本的には売上の場合の考え方と同じです。
計算のための関数の例としては、V4セルにおいては

=IF(OR($F4=0,$M4=0),(O4-H4),($M4-$F4)*K4)

となります。この式をV列のセルにコピペします。

原価の価格差異

基本的には売上の場合の考え方と同じです。
計算のための関数の例としては、W4セルにおいては

=IF(OR($F4=0,$M4=0),0,(R4-K4)*$M4)

となります。この式をW列のセルにコピペします。

利益の数量差異

こちらも同様に、X4セルにおいては

=IF(OR($F4=0,$M4=0),(P4-I4),($M4-$F4)*L4)

となります。この式をX列のセルにコピペします。

利益の価格差異

こちらも同様に、Y4セルにおいては

=IF(OR($F4=0,$M4=0),0,(S4-L4)*$M4)

となります。この式をY列のセルにコピペします。

 

3. 分析まとめシートの作成

分析シートのデータ範囲をピボットテーブルで集計します。

 

分析は売上だけの分析をしても良いのですが、
利益の分析を行うと、売上と原価の変化の結果としての利益変化を明らかにすることが出来ます。
先ほど作成したピボットテーブルで
分類 / 商品名
をフィールドの行にドラッグします。

さらに、
予算_利益 / 実績_利益
利益_数量差異 / 利益_価格差異
売上_数量差異 / 売上_価格差異
原価_数量差異 / 原価_価格差異
をフィールドのΣ値にドラッグします。

 

分析結果のまとめ

今回の分析例では、
予算に対して、実績は+34万円の利益増加 しています。
内訳としては、数量差異によって利益は13万円減少、価格差異によって利益は47万円増加となります。
価格差異の内訳としては、洗濯機の売価アップによる貢献が+7万円、エアコン(製品B)のコストダウンによる貢献が+40万円となりました。

つまり、エアコン(製品B)のコストダウンによる貢献が最も大きく、仮にそれが無ければ増益を達成できなかった、ということになります。

 

最後に

以上、エクセルによる予実差異分析方法をご説明しました。

今回の分析テンプレートをサンプルとしてダウンロードできるようにしました。
ご希望の方はご活用ください。

 

売上/原価/利益の予実差異分析が出来るようになってくると、販売部門の活動成果と、生産部門の活動成果を利益の貢献度で測る事が出来るようになります。
また、商品別の利益貢献度を見える化する事で、商品戦略の課題を見える化し、軌道修正をするきっかけを見つけることが出来ます。

是非、予実差異分析を活用して予実管理の精度をアップし、自社の利益向上に役立ててください。

 

さらに詳しく分析したい方へ

実は、数量差異は「総販売数量差異」と「セールスミックス差異(プロダクトミックス差異)」の2つへとさらに細かく分解することが出来ます。

その方法については、有料ではありますが別の分析ファイルを作成予定です。

コメント

タイトルとURLをコピーしました