Excel でコスト管理をするケースは非常に多いと思います。コスト管理を上手くこなすにはExcelの知識だけでは無く、コスト管理の基礎知識も必要です。
この記事では、Excelでコストの予実管理をするために必要なコスト管理の基礎知識からExcel活用のテクニックまでを紹介します。
コスト管理を任されたけど、どこから手を付けたら良いのか分からないという方や、自分で色々工夫してコスト管理をしているけれど、手間が多くて困っているという方は、是非一度この記事を読んでヒントを得て頂きたいと思います。
また実際に処理したサンプルファイルも掲載していますので、参考にして下さい。
では早速本題に入っていきましょう。
予実管理とは
予実管理とは「予算」と「実績」を比較して行う管理のことです。現実は必ずしも計画通りにいきませんから、「予算」と「実績」はズレていきます。このズレを正確に把握して対策を打つことで予算超過を回避することが可能になります。
例えば、ある部署が予算を超えてしまいそうだということを早い段階で把握することができれば、その部署と相談してコスト節約の対策を打つことで予算超過を未然に防ぐことが可能です。
予実管理の基本
どんな仕事にも「予算」の制限があると思います。最終的にはこの「予算」に収まるように仕事が完了できれば良い訳ですが、それを管理するためには色々と考えることが出てきます。
例えばある仕事を予算１億円で行うことを考えてみましょう。この１億円という予算以外には何も考えられていない場合は、コスト計上の実績が上がってくると、「今は予算のXX%までコストが計上された」というようなことは分かりますが、それが問題無いのか、大問題なのか全く分かりません。
これでは管理が出来ているとは言いがたいでしょう。では、最初に何をすべきでしょうか？
WBS（=「仕事の区分」・「部署」）ごとに予算を配分
最初にやるべきは仕事をWBSに分解して予算を配分するということです。
WBSとは Work Breakdown Structure の略で、仕事を細かい単位に分解したものです。プロジェクト管理をやられている方なら馴染みのある言葉だと思います。
例えば家を造る仕事のWBSとしては、まず最初に「設計」、続いて「資材調達」、最後に「施工」と大きく分けることができます。
更にそれぞれの項目を細分化していくことも可能で、「施工」なら、「基礎工事」「躯体工事」「外装工事」「内装工事」などと細分化できます。
こうすることで仕事の内容をより具体的にできて「予算」もより具体的に立てることが出来るようになります。
WBSは１つに対して担当する部署が１つ割り当てられるように作成します。このWBSとその担当部署ごとに予算を配分することで、「予算」に対する責任者を明確にすることができます。
予算を配分された各部署の管理者は予算を守ろうとするでしょうし、もし予算を守れそうにないのであれば全体コストの管理者に相談に来るでしょう。
こうすることで、「１億円」という ひとかたまり だった予算は、WBSごと、それを担当する部署ごとに分割することができました。
分割することでそれだけ細かい単位でコストを管理できるようになるということです。「１億円」より「１千万円」の単位で管理した方がより精密な管理ができそうだということは直感的に分かると思います。
次は「費目」で予算を分解する
WBSごとに分解した予算もそのままではWBS単位で見れば金額が ひとかたまり のままなので、更に分解して管理が必要です。
そこで、次は「費目」ごとに分けて予算を配分します。「費目」とはお金の使い道で、「人件費」や「物品費」などです。その他の経費としては「出張旅費」、「外注費」や「輸送費」などがあります。
会社によってお金の使い道の特徴があるとは思いますが、「費目」の区分設定としては「人件費」「物品費」「その他経費」の３つぐらいに分解しておけば良いでしょう。
更に「時間」で予算を分割していく
次に重要な要素は「時間」です。仕事のスケジュールをもとに何時どれだけのお金を使うのか、各部署で計画を立ててもらいます。
すると未来のある時点で、そのWBSのコスト計上が計画と比べて多いのか、少ないのかが分かるようになります。
仕事が進んでいてコスト計上が少ないのは Good News です。一方で、仕事が進んでいないのにコスト計上が多いのは Bad News です。このように、コストと同時に仕事の進捗も管理することができるようになるのです。
予実管理の基本まとめ
以上のように、「予算」は「WBS」「費目」「時間」という3つの軸で分解することでより明確にイメージして管理することができることが分かったと思います。
「予算」を図のような3つの軸で分割するイメージです。
続いてはこの予実管理をExcelで実現する方法を紹介していきます。
Excel での管理の実際
この章ではExcelで予実管理を実現するための方法やノウハウを紹介してきます。
管理作業のフロー
Excelでの管理作業のフローは次のようになります。
- 「実績」データの確認と取得（Power Query）
- 「予算」データの作成と取得（Power Query）
- 「分類」データの作成と取得（Power Query）
- データの結合と分析（Power Pivot）
では、１ステップずつ作業の内容を見ていきましょう。
まず最初にやるべき事は組織で使われている会計システムからどのような「実績」データが取得できるのか確認することです。
「実績」データの区分粒度を確認する
あなたの会社ではどのような会計システムが使われているでしょうか？会社の会計システムには全てのお金の動きが管理されているはずです。あなたが担当する仕事のコストの実績もこの会計システムから取得することができると思います。
会計システムとして有名なものとして SAP や Oracle Financials などが挙げられます。
この会計システムから前に挙げた「WBS」「費目」「時間」に関する情報を含めてデータをダウンロードします。
ここで重要なのは 「実績」データがどのような区分粒度で取得できるか ということです。
「WBS」ならどの程度細かく仕事を分解して識別できているか、「費目」であれば、先に説明したように「人件費」「物品費」「その他経費」に分類するために必要な情報が得られるか、など確認が必要です。
また、「時間」はおそらく問題無いと思われますが、最低限「年」「月」が識別できる情報を得る必要があります。
以上を確認した上で、「予算」データは「実績」データの粒度に合わせて作成する必要があります。何故なら「実績」データと整合する形で「予算」データが作成できていなければ 条件を揃えて比較することが出来ない からです。
この点は「予算」と「実績」を比較するという予実管理において最も重要な部分です。
Power Query で「実績」データを取得して加工する
ここではシステムからダウンロードしたファイルのデータをPower Query で読み込みます。
Power Query は様々なデータの加工が可能なので最終的に「予算」データと重ね合わせできるようデータを整えたり、列名を変更するなどの処理をしておきます。
以下に具体的なデータ加工の例を示します。
コード番号を日本語名に変換
WBSなどは会計システム上はコード番号で管理されていることが多いと思います。しかし、そのコード番号だけでは何を意味しているか理解できないので、これを日本語名に変換します。
Power Query のテーブルのマージ機能を使って、会計システムのデータとコード変換テーブルを結合します。結合した変換テーブルの日本語名を WBS として置き換えて使います。
細かい「費目」データを分類
会計システム上では「費目」の分類が非常に細かくなっているかと思います。これについても変換テーブルを使って、「人件費」「物品費」「その他経費」の３つに分類します。
これも Power Query のテーブルのマージ機能を使って、変換テーブルを結合します。これにより、細分化された「費目」を集約して3つの区分にすることができます。
「年」「月」データから「日付」データを作成
「年」「月」もしくは「年度」「期間月」の情報から「日付」のデータを作成します。
「年」「月」からは以下の Power Query の式で「日付」データを作成可能です。
=#date([年],[月],1)「年度」と、４月が１で順に大きくなり、３月が１２となる「期間月」から「日付」を作成する場合は、以下の Power Query の式になります。
=Date.AddMonths(#date([年度],[期間月],1),3)予算データの作成と取得
冒頭にも紹介しました通り、予実管理は「予算」と「実績」を比較評価してコストを管理する手法です。ですから、 「予算」は「実績」データと同じ形にするのが鉄則 です。
予算データ作成時の考慮すべきポイントは次の通りです。
- 「WBS」は「実績」で変換した日本語名を使います。
- 「費目」は実績データを分類分けした「人件費」「物品費」「その他経費」ごとの「予算」を作成します。
- 「時間」は月単位で管理するのであれば、「YYYYMM」形式が何かと便利です
- 時間軸を横に展開した表で作成（⇒ Power Queryでピボット解除する）
実際の予算データは図のようになります。
時間軸の分類
時間軸は「年」「月」「四半期」「年度」などに分類します。これは Power Query の機能で作成します。
M関数でそれぞれ以下のようにすると作成できます。
年
=Date.Year([日付])年度
=Date.Year(Date.AddMonths([日付],-3))月
=Date.Month([日付])四半期
=Date.QuarterOfYear([日付])四半期（年度単位）
=Date.QuarterOfYear(Date.AddMonths([日付],-3))予算データと実績データを束ねる
以上のように予算データを実績データは同じ形のデータになるようそれぞれを加工します。
最後に、それぞれのデータに予算、実績の識別ができるように、「区分」列を追加してそれぞれ「予算」「実績」という内容でデータを作ります。
この状態で予算データと実績データを束ねます。Power Query の「追加」機能で２つのデータを１つにします。
最後に Power Pivot で扱うためにデータモデル化する
Power Query の処理結果を Power Pivot で集計するために、最後のクエリはデータモデルにします。これにより Power Query で DAX関数などを使えるようになり、様々な便利な集計機能などを活用することができます。
Power Query 処理フローまとめ
以上のように Power Query でデータを加工して最後はデータモデルにするまでのフローを図にすると次のようになります。
元データから Power Query でデータを加工して、グラフ表示までを行ったサンプルファイルは以下のリンクからダウンロードできますので、良かったら参考にしてみて下さい。
まとめ
この記事では予実管理の基本事項として、「WBS」や「費目」「時間軸」に分解して、「予算」と「実績」を比較することで問題点をいち早く把握できることを紹介しました。
また、Excel の Power Query の機能を使ってデータ処理をして予実管理を実現する方法について解説しました。
解説した内容は一例ではありますが、多くの事例に共通する内容だと思いますので、皆さんの実務にもきっと使えるテクニックだと思います。
是非、Excel の Power Query、Power Pivot の強力な機能を活用してコスト管理を効率的にこなしてみて下さい。
Power Query や Power Pivot の DAX関数 は別記事で解説していますので、こちらも是非参考にしてみて下さい。
