Excelを用いた予実管理の方法

トップページ＞財務数値の収集と統合の具体的な施策＞ Excelを用いた予実管理の方法

予算実績比較の方法

本ページでは、管理人が実際の企業で使っていたものと同じ予実管理ファイルを用いて、その具体的な方法を説明します。（もちろん、数値も、勘定科目も大きく変更しております。）

エクセル上で、簡易なデータベースを構築した上で、エクセルのピボットテーブルという機能を利用して表を作成していきますので、通常より少し手間がかかりますが、必ず、メリットがありますので、最後までお付き合いください。

エクセルデータベースを利用し複雑な予実対比も簡単に実現

予算と実績の比較は、最も基本的、且つ、頻繁に実施される経営管理業務であると考えられます。また、予算も当初予算だけでなく、様々なシミュレーションにより作成された複数の着地見込もあるでしょうし、実績も仮締、本締などにより多少のバージョンがあるかもしれません。

このような複数の予算と実績を、その組み合わせ毎に比較する表を作成することは大きな手間なのですが、ここでは、エクセルデータベースを用いることによりその手間を軽減する方法を紹介します。
（エクセルデータベースの基本的なやりかたはこちらをご覧ください。）

なお、実際の予実管理では、複数の部署の予算を統合することから始まりますが、本項では、単一部署の企業での予実比較を説明します。
（複数部署の統合は、別の項目にて説明しています）

具体的な実施方法

このファイルをご自身のＰＣの適当なフォルダにダウンロードしてください。ファイル名はご自由に変えていただいて構いません。

上記でダウンロードしたファイルを開きます。なお、このファイルにはマクロが含まれていますので、「マクロが無効にされました」といった警告が出る場合があります。その場合は、警告の横にある「コンテンツの有効化」ボタンを押下してください。

このファイルには「計画値１」「計画値２」というワークシートには異なった２つの時点での予測財務数値（＝予算）、「４月実績値」「４月実績値」というワークシートには経理システムなどから抽出した実績値を記入します。

なお、これらのワークシートの1行目には、データ名称を記入しています。計画値には予算の作成日付やバージョン名を、実績値には各月とも”実績”という名称とします。

まず、「計画値１」というワークシートのいずれかのセルを選択した後、エクセルのメニュー⇒表示⇒マクロにて「データベース作成」を実行します。
勘定科目の示すセル範囲として、セルアドレス：A7からB60を、年月などを示すセル範囲として、セルアドレス：C1からN2を選択します。

このマクロにより作成された「output」ワークシートのワークシート名を、「データ」に変更しておきます。

次に「計画値２」ワークシートのいずれかのセルを選択し、「データベース作成」マクロを実行します。
勘定科目の示すセル範囲として、セルアドレス：A7からB60を、年月などを示すセル範囲として、セルアドレス：C1からN2を選択します。
ここで、作成された「output」ワークシートのデータを先ほど名称を変更した「データ」の最後の行の下に追加します。

さらに「４月実績値」ワークシートのいずれかのセルを選択し、「データベース作成」マクロを実行します。
勘定科目の示すセル範囲として、セルアドレス：A7からB60を、年月などを示すセル範囲として、セルアドレス：C1からC2を選択します。
ここで、作成された「output」ワークシートのデータを先ほど名称を変更した「データ」の最後の行の下に追加します。
加えて「５月実績値」でも同じようにマクロを走らせ、「output」ワークシートのデータを「データ」ワークシートへ追加します。

次にこの「データ」ワークシートをピボットテーブル形式にします。
「データ」ワークシートのセルアドレスA1、B1、C1、D1、E1に「コード」、「科目名称」、「データ種別」、「年月」、「金額」と記入します。また、D列を選択し、表示形式を　20XX年XX月　としておきます。
次に、メニュー⇒挿入⇒ピボットテーブルと選択しますと、データ範囲が自動で設定されます。また、ピボットテーブルを配置する場所として、新規ワークシートを選択してOKを押します。
すると新しいシートに「ピボットテーブル１」という表示が出ますので、右側のピボットテーブルのフィールドの選択画面において、「フィルター」という場所に年月、「行」という場所にコード、科目名称、「列」という場所にデータ種別、「値」という場所に金額をドラッグします。

ピボットテーブルの「年月」を選択する箇所で、2017年4月を選択すれば、4月の予実管理の結果が表示されます。また、列ラベルをクリックすれば、比較に必要なデータを選択できます。

このように「データ名称」をデータに加えることにより、毎月の予実比較のための作業は、「データベース」に実績データを加えるだけとなります。

これは、予実比較表という「帳票」を、「データベース」部分と分離させたことによります。さらに、この方法では、「ベストシナリオ」、「ワーストシナリオ」等、複数の予測値と実績の比較など、これまではそれぞれに帳票を作成していたケースでも、ピボットテーブルにて、選択するデータ名を変えるだけで簡単に表記できます。