予算と実績データの収集と統合の実現

トップページ＞財務数値の収集と統合の具体的な施策＞予算と実績データの収集と統合の実現

予算と実績データの収集と統合の実現

予算と実績の収集と統合の概要

本項では、私が、実際の経営管理の現場で、予測財務数値（＝予算）や実績の収集や統合、予算実績管理に用いていた方法を紹介します。

右の図は、この方法の概要を示したものです。「統合処理ファイル」というエクセルファイルに、全ての部署の予算値と実績値を集めた大きなデータ一覧（データベース）を作成し、そこから、用途に応じた複数のPivotテーブルを作成します。各種会議用資料などのアウトプットは、全てこのPivotテーブルから作成します。

このデータ一覧（データベース）を作成することによって、データの訂正があった場合も、データ一覧の当該値を訂正するだけで帳票ごとに修正する必要性がないため、訂正漏れによる資料間の齟齬といった問題は回避されます。

また、月次実績値などデータが追加された場合も、データベースに追加しPivotテーブルを更新するだけですので、毎月の予実管理の運用が省力化できます。

収集と統合作業の準備

まず、こちらのファイルを適当なフォルダにダウンロードしてください。
また、このファイルは圧縮されていますので、適当なフォルダに解凍してください。すると、解凍した先には、以下のファイルとフォルダが作成されていますので、ご確認ください。

統合処理ファイル
収集したデータを統合し、データ一覧を作成するマクロを搭載したエクセルファイル

予算実績データ
各部から収集した予算や経理システムから取り出した実績データ（例）を格納したフォルダ

統合作業用フォルダ
実際に統合作業を行う時に使用するフォルダ。解凍時は何も入っていない

①　予算データの収集

こちらのスケジュール例にもある通り、各部で記入した経営計画（案）を収集します。
ここでは、上記で準備した「予算実績データ」フォルダの中の「予算2月15日時点」フォルダにある4つのファイルを「統合作業用フォルダ」にコピーします。これは、実際の業務において、各部から収集したファイルを「統合作業用フォルダ」に保存するという作業を模擬しています。

②　データの統合

「統合処理ファイル」を開いてください。なお、このファイルではマクロが組み込まれていますので、それを実行できる形にしてください。

１）　メニュー⇒表示⇒マクロにて、「データ統合」を選択し実行します。

２）　「収集されたファイルが入っているフォルダを指定してください」というメッセージが出ますので、①でファイルをコピーした「統合作業用フォルダ」を指定します。

３）　「フォルダ内全ファイルを統合しますか」というメッセージが出ます。

このマクロは、「統合作業用フォルダ」に入っているファイルをすべてデータベースの形に変換して保存していくものですが、一度取り込んだファイルを覚えており、読み飛ばす機能を有しています。

一度取り込んだファイルを読み飛ばす場合は、「いいえ」を選択しますが、今回は最初ですので、「はい」（全てのファイルを読み込む）を選択します。この場合は覚えていた取り込みファイル名もリセットされます。

４）　「統合作業用フォルダ」に入っているファイルごとに、データベース化するための属性範囲を指定します。
今回の予算ファイルでは、以下の範囲を指定します。

勘定科目などを示すセル範囲：　A9：B62
年月などを示すセル範囲：　C1：N4

５）　「統合処理ファイル」の「TOTAL」というワークシートには、４）の処理により統合された各部の予算値がデータベース化されて入っています。
その一行目に、A列から順に、「コード」「科目名称」「事業部」「データ名称」「データ種別」「年月」「金額」「ファイル名」と記入します。また、F列（「年月」）は、日付フォーマットに変更してください。

③　データ属性の追加

「複数の利益体系の実現」や「組織統合の実現」の項でも示しましたが、②で作成したデータベースの勘定科目及び事業部にさらに属性を追加することにより、このデータベースから様々なアウトプット帳票を作成する準備をします。

「統合処理ファイル」には、「科目体系マスタ」と「組織統合マスタ」という２つのワークシートを搭載しています。これは、それぞれ、勘定科目と事業部を、いくつかの「グループ」に分類するためのものです。

「TOTAL」ワークシートの　I 列から　L 列にこれらのマスタと勘定科目コードや事業部名を結びつけます。
この結び付けには、エクセルVLOOKUP関数を用いますが、その方法は、「TOTAL完成表（ご参考）」の当該列をご参照ください。

なお、「売上原価」「売上総利益」「営業利益」「経常利益」に関しては、科目体系マスタと結びつかず、"#N/A”と表示されてしまいます。これらの勘定科目は、他の科目と異なり、計算値として導出されるため、Pivotテーブル内で「集計アイテム」という機能を用いて計算したほうがいろいろと便利です。

この場合は、「TOTAL」データベースからこれらの項目を削除し、ピボット（Pivot）テーブルに計算式を定義しますが、その方法はこちらに記載しています。

④　ピボットテーブルの作成

作成された「TOTAL」ワークシートから、ニーズに応じた複数の帳票を作成します。

「TOTAL」ワークシートを選択し、メニュー⇒挿入⇒ピボットテーブルと選択しますと、データ範囲が自動で設定されます。また、ピボットテーブルを配置する場所として、新規ワークシートを選択してOKを押します。
すると新しいシートに「ピボットテーブル2」という表示が出ますので、右側のピボットテーブルのフィールドの選択画面において、「フィルター」という場所にデータ名称、ファイル名、「行」という場所にコード、科目名称、「列」という場所に年月、「値」という場所に金額をドラッグします。

なお、「統合処理ファイル」では、「TOTAL完成表（ご参考）」をベースに、利益体系や組織統合の方法を変えたいくつかのピボットテーブルや、予実比較のためのピボットテーブルの例を示していますので、参考にしてください。

実際の経営の現場では、部門ごと、役員ごと、管理目的ごとに様々な管理帳票が使用されており、それ毎にExcelファイルが準備されている場合も多いと思います。このような状況では、元となるデータが変わるごとにこれらの帳票の一つ一つを修正せねばならず手間がかかってしまいますが、このように一つのデータベースから複数のピボットテーブルを作成しておけばデータベースを追加修正するだけで全部の帳票が更新されることになり大変省力化が図れます。
その点は、予算と実績データの収集と統合の実現（２）にて記します。