エクセルで見やすい予実管理表を作るには

初めて予実管理表を作成される方へ
【無料配布中】予実管理表テンプレート

予実管理表の作成に利用されるエクセル。しかし、どのようにエクセルを使えば良いのかわからないこともあるのではないでしょうか。

この記事では、エクセルで見やすい予実管理表を作る方法について解説していきます。

水原のひとこと：見やすい管理表を作成する理由

見やすくなくても、とりあえず作成できれば問題ではないのでは？と思う方もいらっしゃるかもしれません。確かに、立ち上がりたての会社でしたら「まずは、存在することが重要」ということもあるかもしれません。
しかし、「とりあえず」で作った予実管理表が長く使われ、集計不備や引き継ぎの煩雑さなどの様々な問題を引き起こすことも数多くあります。予実管理表を作成する際には、今後使われ続けること、引き継がれていくことを想定した作りで作成しましょう。

予実管理表とは

予実管理表の作成目的

予実管理表は、企業が予算と実績を管理するためのツールです。予実管理表を作成し運用することで、予算と実績の差異を把握し、予算の見直しやアクションプランの改善を行っていきます。「予実管理表の作成目的」を整理すると、以下になります。

予算の管理

予算と実績と比較することで、予算の達成状況を把握し、状況に応じて予算やアクションプランの変更を行います。

コストの削減

コストの増減を管理することで、コストを削減するための施策を考えることができます

売上の増加

予実管理表を作成し、売上の推移を把握することで、売上を増やすための施策を考えることができます。

経営戦略の策定

予実管理表の分析結果を根拠に、経営戦略を策定することができます。

意思決定の支援

実管理表の分析結果を根拠に、戦略の意思決定に役立てることができます。

予実管理表は、コスト削減や売上増加、意思決定に寄与し、経営改善に利用することができる便利なツールです。

予実管理表の例

予実管理表の具体例に関しては、こちらの記事を参考にしてください。予算策定や予算管理の全体像についてもかなり詳しく解説してい

予実管理とは。ツール選定より意義、進め方と運用ポイント解説。（井上伸也氏）

予算策定や予算管理の全体像についてもかなり詳しく解説している上、予実管理表のテンプレートも配布しています。

予算の入力・集約

ここまで、予実管理表の作成目的についてみてきました。ここからは、エクセルで予実管理表を作成する際に予算の入力・集約に利用できる関数について具体的に見ていきましょう。

予実管理表のアウトプット形式はテンプレートを参考に、実際に関数を入力したり数字を入力したりしながら、エクセルの操作を覚えていきましょう。

水原のひとこと：実践がスキルを向上させる

実務に近い操作をすることで、具体的に役立つ知見を得ることができます。

よく使用する関数

予実管理表を作成する際によく利用される関数は以下になります。基本的な関数なので、予実管理表の作成以外でも使われます。知っていることも多いかと思いますが、復習がてら確認していきましょう。

IF関数

条件分岐の関数です。
(例)
「=IF(B1>C1, B1-C1, 0)」
B1がC1よりも大きい場合はその差額を表示し、そうでない場合は0を表示することができます。

SUM関数

値を合計する関数です。予算や実績を合計するために利用されます。
(例)
「=SUM(A1:A10)」
A1からA10までの数値を合計することができます。

AVERAGE関数

値を合計する関数です。予算や実績を合計するために利用されます。
(例)
「=AVERAGE(A1:A10)」
A1からA10までの数値の平均値を表示することができます。

COUNT関数

セルの数をカウントするために利用されます。
(例)
「=COUNT(A1:A10)」
A1からA10までのセルの数を表示することができます。

関数を適切に利用することで、予実管理表を効率的に作成することができます。

予算の入力

次に、予算の入力に便利な関数について紹介します。テーブルやシートを跨いだり、数値の表示を操作したりする必要がある場合に用いる関数になります。

VLOOKUP関数

他のテーブルからデータを取得するために利用されます。
（例）「=VLOOKUP(A1, Table1, 2, FALSE)」
A1の値がTable1の1列目に存在する場合、その対応する2列目の値を表示することができます。

ROUND関数

小数点以下を丸めるために利用されます。
（例）「=ROUND(A1, 2)」
A1の値を小数点以下2桁に丸めることができます。複雑な数値を見やすく整えることができます。

VLOOKUP関数のように列を数値で直接指定する関数には、参照がずれやすい弱点があります。MATCH関数やINDIRECT関数などと組み合わせることで回避できる場合もありますが、計算の回数が増えるためエクセルが重くなったり、複雑化することで引継ぎコストが重くなったりという問題も伴います。参照先を「テーブル」として扱うのも一つの手です。

予算の集約

予実管理表で予算を集計するためには、以下のエクセル関数が便利です。条件ごとの和や積を表示させることができる関数になります。

SUMIF関数

条件に合致する範囲の、合計値を計算する関数です。ある部門の予算のみを合計したい場合などに利用します。
（例）「=SUMIF(条件範囲, 条件, 集計範囲)」

• 条件範囲：条件を判定するための範囲を指定します。
• 条件：条件範囲内で検索する値を指定します。
• 集計範囲：条件に合致する範囲を指定します。

SUMIFS関数

複数の条件に合致する範囲、合計値を計算する関数です。「ある期間内」「ある部門」の予算のみを合計したい場合などに利用します。
（例）「=SUMIFS(集計範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, …)」

• 集計範囲：合計する範囲を指定します。
• 条件範囲1：条件1を判定するための範囲を指定します。
• 条件1：条件範囲1内で検索する値を指定します。

同様に、追加の条件を範囲と条件の組み合わせで複数設定できます。

SUMPRODUCT関数

範囲の積を合計する関数です。例えば、数量と単価から総額を計算する場合などに利用します。
（例）=SUMPRODUCT(範囲1, 範囲2, …)
範囲1と範囲2には、合計する範囲を指定します。

以上の関数を組み合わせることで、複雑な条件に合致する予算の集計も簡単に行えます。

実績の入力・集約

次に、実績の入力と集約についてみていきましょう。

よく使用する関数

予算の入力・集約>よく使用する関数 に同じです。

実績の入力

基本的な関数は、「予算の入力・集約>予算の入力 」と同じです。ここでは、実績の手入力に役立つ、入力ミス防止のためのデータ検証機能を紹介します。

入力可能な値の範囲を事前に設定する方法
（例）予算が100万円である場合、その範囲内でしか実績が入力されないように、データ検証機能を設定する

A1セルに予算が入力されている場合に、B1セルに実績を入力するためのデータ検証機能の設定する場合の手順は下記のようになります。

1. B1セルを選択
2. リボンの「データ」タブをクリックします
3. 「データの検証」をクリックします。
4. 「設定」タブをクリックし、「整数」を選択します。
5. 「データ」タブをクリックし、「予算!A1」を入力します。
6. 「入力メッセージ」タブをクリックし、入力するデータについての説明を入力します。

これにより、B1セルに入力できる値が、予算の範囲内に制限されるようになります。

実績では、予算と異なり、結果として表れた数値を入力していきます。引用元になるデータの出力日時がが正しいものであるか、きちんと確認した上で入力していきましょう。

実績の集計

「予算の入力・集約>予算の入力」と同じです。今後の戦略を決める上で重要な指標になるため、引用ミスや入力ミスがないように、十分注意して作成していきましょう。

水原のひとこと：調べながら集計していく

ここでは基本的な関数を紹介しました。
実際に作業をする中で欲しい機能が出てきた時は、都度検索で関数を調べながら作成していくのが通常です。ただし、あまりに複雑な関数を利用すると、後任者への引継ぎ時に苦労することになります。

レポーティング

ここまで、予実管理表の作成目的や、予実管理表の入力や集計に便利なエクセル関数について解説してきました。ここまでの内容で予実管理表を作成し、入力・集計をしていくことができるかと思います。

予実管理表を作成したあとは、目的に応じて利用しやすいように、レポーティングをしていきましょう。ここでは集計したデータのレポーティングの方法について解説していきます。エクセルを利用すれば、簡単にグラフを作成することができます。

グラフの活用

数字の羅列だけでは重要な点がわかりにくいことがあります。グラフを活用し、視覚的にわかりやすいレポートを作成しましょう。基本的な内容ではありますが、グラフの種類について解説します。

折れ線グラフ

時間軸に対して数値の変化を表すことができます。
例えば、予算と実績を比較する場合、予算と実績の差異や傾向を視覚的に表現することができます。

棒グラフ

値の大小を比較することができます。
例えば、予算と実績を比較する場合、予算と実績の大小・変化を視覚的に表現することができます。

円グラフ

全体に対する割合を表すことができます。
例えば、予算の内訳や実績の内訳を比較する場合、円グラフを使用することで、各項目の割合や比率を視覚的に表現することができます

散布図

2つの変数の関係を表すことができます。
例えば、予算と実績の関係や、予算と売上の関係を表現する場合、散布図を使用することで、相関関係や傾向を視覚的に表現することができます

グラフは便利ですが、なんでもグラフにすればよいというものでもありません。グラフを活用する際には、目的に合わせたグラフを、適切な数作成するようにしましょう。

グラフ活用の注意点

• 目的に合わせたグラフを選択すること
• グラフのデザインやレイアウトを工夫すること
• ラベルやタイトルなど必要な情報を追加すること
• データの正確性を確認し、適切なスケールを設定すること

ピボットテーブルの活用

予実管理表を作成し、レポーティングする場合、ピポットテーブルを活用すると便利です。ピポットテーブルを利用すると、特定データの集計や集計結果の分析が容易になります。

ピポットテーブルの活用方法

データの集計

ピポットテーブルを使用することで、データを集計することができます。例えば、年度別、部門別、製品別などの集計が可能です。

集計の基準を変更することも簡単にできます。例えば、金額の合計から平均値、最大値、最小値などに変更することができます。

データの分析

ピポットテーブルを使用することで、データの傾向や関係を分析することができます。例えば、製品ごとの売上動向や、部門ごとの予算超過傾向などを分析することができます。

データのフィルタリング

ピポットテーブルを使用することで、必要なデータだけを抽出することができます。例えば、特定の期間のデータだけを表示することや、特定の部門のデータだけを表示することができます。

データの可視化

ピポットテーブルを使用することで、簡単に特定のカテゴリーのデータを集計し、グラフ化することができます。例えば、年度別の売上推移や、製品別の売上比較などをグラフ化することができます。

ピポットテーブルの注意点

ピボットテーブルを活用する際には、以下の点に注意しましょう。

• ピボットテーブルの設定方法を理解すること
• 集計する項目を選択すること
• 集計の基準を設定すること
• データのフィルタリング方法を理解すること
• 集計結果を分析するためのグラフ化方法を理解すること

ピポットテーブルを活用することで、予実管理表のデータを簡単に集計し、分析することができます。ピポットテーブルの分析結果をグラフ化することで、レポート作成の更なる効率化につながります。

見込管理を行うには

予実管理表のフォーマットができたら、見込管理も行いましょう。

見込管理とは、将来の売上見込みを事前に管理することです。見込数値を予実管理表に反映させることで、予算と実績の比較を行い、経営の意思決定に役立てることができます。

＜見込管理の方法＞

見込顧客リストの作成

まずは、販売する商品やサービスに興味を持ちそうな顧客のリストを作成します。過去の顧客リストや、関連するイベントの参加者リストなどを基に、リストを作成することができます。

見込顧客の評価

作成した見込顧客リストを、購買履歴やウェブサイトのアクセス履歴などから評価します。この評価によって、どの見込み顧客が最も商品やサービスを購入する可能性が高いかを判断します。

見込売上の予測

評価した見込み顧客から、どの程度の売上が見込めるかを予測します。これにより、将来の売上予測を立てることができます。

予実管理表への反映

予測した見込売上を予実管理表に反映させます。この際、実績と見込の区別を明確にして、比較しやすいようにします。

分析と改善

実績と見込を比較し、見込が外れた場合には、なぜ予測が外れたのかを分析し、改善策を検討します。また、見込が当たった場合には、その成功要因を分析し、今後の見込管理の参考とします。

以上が、見込管理の一例です。実際には、業種や企業の規模、商品やサービスの種類によって、見込管理の実行方法は異なる場合があります。

専用ツールによる予実管理

いざ予実管理表の作成・運用に取り掛かってみると、関数の参照範囲が間違っていたり、少し値を変更しただけで全体が再計算されて動作が重かったりといった壁に当たるかもしれません。そういった壁を乗り越えたとしても、事業が成長し、部門編成を変更するタイミングで、予実管理表を大きく変更することが必要になる場合もあります。

どれだけ先を見越して精緻に作りこんだとしても、エクセルでの予実管理に避けがたい問題があることは事実です。予実管理表は経営の意思決定にも利用される重要なツールですから、事業の成長を見込む場合、投資機会の損失を招かないように安定運用可能な専用ツールを導入してみても良いでしょう。

予実管理クラウド「DIGGLE」では、予算・見込の入力を直感的なUI上でかんたんに実現でき、実績の突合も数回のボタン操作で可能にします。エクセルではひと手間かかってしまう年度間の数値の比較も、レポート機能ですぐに実施できます。永年でカスタマーサクセス担当者もつくため、部門編成に変化が起きるなど、運用に変化が生じた際も無理なく対応していくことができるでしょう。

まとめ

この記事では、予実管理表の作成目的と、予実管理表の入力と集計に便利なエクセルの機能、見込管理の方法等を解説しました。

この記事を参考に、予実管理表を利用しながら、予実管理・見込管理を行ってみてください。

著者プロフィール

水原しおり

2018年より個人事業主としてリラクゼーション事業を経営する傍ら、研究開発系ベンチャー企業経営における事業企画を受託。

2021年にはマザーズ上場IT企業内にて経営企画部の立ち上げに参画し、属人化していた経営企画業務の標準化を推進。

2022年、グロース上場コンサルティング企業での事業企画に従事。

事業のフェーズに応じた戦略立案と確かな実行を見据えた施策表現を強みとして、中長期的なビジョンを踏まえた数多くの中期～短期施策立案を手掛ける。