今回は、Excelで予実管理を行うときに、差分を確認しやすくする表の作り方を紹介します。
Excelの予実管理は差分の見せ方が重要
予実管理では、予算や計画に対して実績がどうなっているかを確認します。Excelで表を作るとき、予算と実績を並べるだけでは、どこに差があり、何を確認すべきかが分かりにくいことがあります。
大切なのは、差分をすぐ確認できる形にすることです。予算、実績、差分、差分理由を同じ流れで確認できる表にすると、会議や報告で使いやすくなります。
予算と実績は横並びで配置する
予実管理表では、予算と実績を離れた場所に置かないことが基本です。同じ項目の予算と実績を横並びにすると、差を確認しやすくなります。
たとえば、左から「項目」「予算」「実績」「差分」「差分率」「コメント」の順に並べます。月別で管理する場合は、各月の中で予算、実績、差分をセットにする方法もあります。
項目数が多い場合は、表が横に長くなりすぎないように注意します。詳細は別シートに置き、確認用シートでは主要項目だけを表示すると見やすくなります。
差分は数式で計算する
差分は手入力せず、数式で計算します。一般的には、実績から予算を引いて差分を出します。費用管理のように、実績が予算を超えると注意が必要な場合は、符号の見方をあらかじめ決めておきます。
差分率も必要に応じて計算します。ただし、予算がゼロの場合は割り算エラーが出るため、IFERROR関数などで表示を整えると見やすくなります。
差分列で確認したいこと
- 予算を上回っているか
- 予算を下回っているか
- 確認が必要な差なのか
- コメントが必要な項目なのか
差分の計算ルールを表の上部や設定シートに書いておくと、見る人が数字の意味を理解しやすくなります。
条件付き書式で確認箇所を示す
予実管理表では、条件付き書式を使うと確認が必要な差分を見つけやすくなります。たとえば、差分が一定以上の場合に背景色を付ける、マイナスの場合に文字色を変えるなどの方法があります。
色を使うときは、意味をそろえます。注意が必要な項目は赤系、確認中は黄系、問題なしは色なしなど、表内でルールを統一します。
条件付き書式は、差分列だけに使うと見やすくなります。表全体に色が付きすぎると、どこを見ればよいか分かりにくくなります。
差分理由を書く欄を用意する
予実管理では、差分そのものだけでなく、その理由が重要です。差分が出た項目には、原因や対応方針を書けるコメント欄を用意します。
コメント欄には、単に「増加」「減少」と書くのではなく、何が影響したのかを短く書きます。たとえば「広告出稿時期を前倒し」「一部案件の納品が翌月へ移動」のように、背景が分かる表現にします。
コメント欄に入れたい内容
- 差分が発生した理由
- 一時的な差なのか継続する差なのか
- 対応が必要か
- 次月以降の見込み
- 確認中の相手や期限
コメント欄が長くなる場合は、要点だけを表に書き、詳細は別シートや議事メモに分けると読みやすくなります。
月別と累計を分けて確認する
予実管理では、単月の差分と累計の差分を分けて確認すると状況を理解しやすくなります。単月では差が出ていても、累計では計画内に収まっている場合があります。逆に、単月の差は小さくても、累計で差が広がっている場合もあります。
Excelでは、月別表と累計表を分けるか、同じ表の中で列を分けて表示します。確認する人が多い場合は、上部に累計、下部に月別明細を置くと、全体から詳細へ進みやすくなります。
グラフを使う場合は、予算と実績の推移を折れ線や棒グラフで並べると、差の流れを確認しやすくなります。
集計元データを整える
予実管理表を安定して使うには、集計元データの形も重要です。部署名や項目名が月によって違うと、集計がずれやすくなります。
項目名はマスタとして管理し、入力時に選択できるようにすると表記ゆれを減らせます。Excelの入力規則を使ってプルダウンにする方法があります。
また、予算データと実績データの項目コードをそろえておくと、XLOOKUP関数やピボットテーブルで集計しやすくなります。見た目の表を整える前に、元データの列名や分類をそろえることが大切です。
報告用シートは要点を絞る
予実管理のExcelファイルには、入力用、集計用、報告用のシートを分けると便利です。報告用シートでは、すべての明細を見せるのではなく、確認が必要な項目に絞ります。
上部に全体の予実、中央に主な差分、下部にコメントや対応状況を置くと、会議で使いやすくなります。詳細を聞かれたときは、集計用や元データのシートに戻って確認します。
報告用シートは、説明しやすさを優先して情報量を調整することがポイントです。
差分の確認基準を決める
予実管理表では、どの程度の差を確認対象にするかを決めておくと運用しやすくなります。小さな差まで毎回確認すると、重要な差分が埋もれることがあります。
確認基準は、金額、割合、項目の重要度などで決めます。たとえば、差分が一定額を超えた項目だけコメントを必須にする方法があります。基準を表の上部や設定シートに書いておくと、担当者が同じ判断でコメントを入れやすくなります。
担当者別の確認欄を用意する
差分が出たときに誰へ確認するかが分からないと、対応が止まりやすくなります。予実管理表には、担当者、確認状況、回答期限の列を用意しておくと便利です。
確認状況は「未確認」「確認中」「対応済み」のように選択式にすると、表記ゆれを減らせます。期限を入れておけば、会議前に未回答の項目を探しやすくなります。
予算変更の扱いを決めておく
予実管理では、途中で予算や計画を見直すことがあります。そのとき、元の予算を上書きするのか、修正後予算として別列に残すのかを決めておくことが大切です。
元の予算を消してしまうと、当初計画との差が分からなくなる場合があります。必要に応じて、当初予算、修正予算、実績、差分の列を分けると、変更の経緯を追いやすくなります。変更日や変更理由をメモ欄に残しておけば、後から確認するときにも役立ちます。
グラフで差分の傾向を見る
表だけでは差分の流れが分かりにくい場合は、グラフを使います。月別の予算と実績を並べた棒グラフや、累計差分を示す折れ線グラフを作ると、どの時期から差が広がったかを確認しやすくなります。
グラフは報告用シートに置き、詳細な明細は別シートに分けると見やすくなります。
まとめ
Excelで予実管理を行うときは、予算と実績を横並びにし、差分と差分率を数式で計算すると確認しやすくなります。条件付き書式を使って注意箇所を示し、コメント欄で差分理由や対応状況を残すと、報告や会議で使いやすい表になります。
単月と累計を分けて確認し、元データの項目名や分類をそろえることも大切です。入力用、集計用、報告用を分けて設計すると、差分確認と説明の両方に使いやすい予実管理表を作れます。