excel
予算管理をエクセルでやると決めたときに最初に悩むのは、どこまでをこの表で扱うかという範囲の話です。全社の費目だけを見るのか、案件ごとの原価まで追うのか、工数を金額に換算するのか。範囲を決めずに作り始めた表は、必要な列が増え続けて、半年後に誰も更新しなくなります。この記事では、エクセルが担える範囲の見極め方、表の組み方と関数、案件が増えたときに行き詰まる場所、そして別の仕組みに移る判断の基準までを順に整理します。
予算管理という言葉は、範囲の広い言葉です。会社全体の費目を月ごとに見ることも予算管理であり、案件1件の原価が見積のなかに収まっているかを見ることも予算管理です。この2つは、必要な表の形がまったく違います。
エクセルが得意なのは前者です。費目が固定されていて、行の数が月ごとに決まっていて、入力する人が少ない。この条件が揃っていると、エクセルの表は長く使えます。全社の費目が30行程度で、入力するのが経理の1人か2人なら、他の仕組みに移る理由は特にありません。
苦手なのは後者です。案件ごとの原価管理は、案件が増えるたびに行が増え、案件ごとに費目の並びが違い、入力する人が案件の担当者の数だけいます。この条件が揃うと、エクセルの表は毎月形が変わり、集計が合わなくなります。
企業経営にぜひ役立てたいプロセスの一つに予実管理があげられます。予実管理とは、経営管理のための手法の一つで、あらかじめ設定した予算と実際の業績を比べて原因を分析・改善していく予算管理手法の一つです。予実管理を活用することで組織の課題と改善ポイントを特定することができます。 出典: biz.moneyforward.com
課題と改善ポイントを特定する、という目的に照らすと、範囲の決め方も見えてきます。改善のために動かせるものだけを表に載せる、という基準です。家賃や固定の通信費は、表に載せても改善の判断には使えません。動かせない費目は合計の1行にまとめ、動かせる費目だけを細かく持つ。この切り分けをすると、表の行数は半分近くまで減ります。
範囲を決めたら、それを表の先頭に1行で書いておきます。「この表は動かせる費用の月次の予算と実績を扱う。案件別の原価は別の場所で管理する」といった具合です。この1行があるだけで、あとから列を増やしたくなったときの判断が速くなります。
作り始める前に決めておくべきことが3つあります。ここを飛ばした表は、3か月目に作り直しになります。
1つ目は、誰が入力するかです。経理が1人でまとめて入れるのか、部門の担当者がそれぞれ入れるのか。前者なら表の形は自由に決められます。後者なら、入力する人が迷わない形にする制約が入ります。この違いは列の数にそのまま出ます。
2つ目は、どの単位で締めるかです。月次か四半期か。また、締めたあとに遡って直すことを認めるかどうかも決めます。認めるなら、いつまで遡れるかを決めます。ここが曖昧だと、先月の合計が今日と昨日で違うという状態が起きます。
3つ目は、何を見て何を決めるかです。予算を超えたときに、発注を止めるのか、他の費目から回すのか、来月に回すのか。決める内容が先に決まっていないと、表を見ても次の動きが出ません。判断の種類が3つあるなら、表にはその3つを見分けるための情報が必要になります。
この3つを決めると、表の列は自然に絞られます。逆に、決めずに「あったほうがよさそう」な列を並べると、埋まらない列が並んだ表になります。埋まらない列があると、入力する人はその表を軽く扱うようになります。
手順は5つです。
最初にやるのは、明細のシートを1枚作ることです。列は「年月、部門、費目、予算、実績、備考」の6列です。この形で縦に積みます。横に月が並ぶ形は、あとで作る集計のシートで実現します。明細を横に広げると、月をまたいだ比較ができなくなります。
次に、費目と部門の一覧を別シートに作ります。この一覧は、入力規則の参照先として使います。一覧の順番も決めておくと、集計のシートの並びが毎月同じになります。並びが毎月変わる表は、前の月の資料と見比べられません。
三番目に、集計のシートを作ります。行に費目、列に月を並べ、中身はSUMIFSで明細から拾います。この形なら、明細に行を追加しても集計は自動で更新されます。手で貼り付ける工程が1つもない状態を目指します。
四番目に、差異の欄を作ります。金額の差と、率の差の2つを持ちます。率だけだと、金額の小さい費目が上位に並んで判断を誤ります。金額だけだと、規模の違う部門を横に並べられません。
五番目に、色を付ける条件を決めます。差異率が基準を超えた行だけを塗ります。基準は費目ごとに変えても構いません。広告費の20%超過と、外注費の20%超過では、意味の重さが違うためです。
予算管理表を組むのに必要な関数は限られています。
SUMIFSが中心です。年月と部門と費目の3つを条件にして、明細から金額を合計します。条件の並び順を毎回同じにしておくと、式を横や下にコピーしたときの間違いが減ります。
COUNTIFSも役に立ちます。ある費目の実績が入っている行の数を数え、0件なら未入力と分かります。実績が0円なのか、まだ入っていないのかを区別できる表は、締めの確認が速く済みます。
IFERRORは、実績がまだ入っていない月の表示を整えるために使います。エラーが並んだ表は、見た人が壊れていると判断します。
テーブルとしての定義は、関数ではありませんが必須です。明細の範囲をテーブルにしておくと、行を追加したときに集計側の参照範囲が伸びます。これをやっていない表は、行数が想定を超えた時点から静かに欠け始めます。
ピボットテーブルは、定例の集計には向きません。更新のボタンを押し忘れると古い値が残るためです。定例の表はSUMIFSで作り、その場限りの分析にピボットテーブルを使う、という使い分けが安全です。
ここまで、予実管理表の作成目的についてみてきました。ここからは、エクセルで予実管理表を作成する際に予算の入力・集約に利用できる関数について具体的に見ていきましょう。 出典: diggle.jp
関数を覚える順番としては、SUMIFSだけを先に身につければ充分です。残りは必要になったときに調べれば済みます。逆に、関数を凝り始めると作った人以外が触れなくなり、その人が休んだ月に更新が止まります。
全社の費目だけを見る表は長く持ちます。行き詰まるのは、案件ごとの予算を追い始めたときです。行き詰まる場所は4つに集まります。
1つ目は、案件の行が増え続けることです。案件が終わっても行は残ります。終わった案件を別シートに移す運用を決めていないと、明細が数千行になり、開くのに時間がかかるようになります。案件が月に40件を超えるあたりから、この問題が体感できる速さで進みます。
2つ目は、案件ごとに費目の並びが違うことです。制作の案件と保守の案件では、追いたい費目が違います。共通の列で持つと空欄が並び、案件ごとに列を変えると集計ができません。
3つ目は、入力する人が増えることです。案件の担当者が自分の案件の実績を入れる形にすると、入力する人が案件の数に近づきます。同時に開けないファイルでは、この形は成立しません。クラウド上での共同編集にしても、同じ行を2人が直したときの解決は人が判断します。
4つ目は、発注の承認と表が別物になることです。発注の承認はメールやチャットで行われ、表には金額だけが後から入ります。承認したのに表に入っていない発注が積み上がり、月末に実績が急に増えます。この状態を「表が遅れている」と表現する人もいますが、正確には表と承認の経路がつながっていないという話です。
案件の予算管理で判断が難しいのは、社内の人の工数をどう扱うかです。
金額に換算して載せる方法と、時間だけを別に持つ方法があります。換算して載せると、案件ごとの利益が1つの表で見えます。ただし、社内の単価をいくらに置くかという議論が毎年発生し、その議論が終わるまで表が止まります。
時間だけを別に持つ方法なら、単価の議論を避けられます。案件ごとの外注費と経費は金額の表で、人の稼働は時間の表で持ちます。判断のときに2つを見比べることになりますが、どちらの表も止まりません。
チームが数十人までなら、後者のほうが続きます。前者は、社内の単価を決める仕組みと、稼働を毎日入れる習慣の両方が要るためです。稼働の記録は、入力する人の負担がそのまま精度に出ます。週に1回まとめて入れる形であれば、多くの現場で続きます。日ごとに細かく入れる形は、繁忙期に真っ先に止まります。
いずれにしても、工数の記録を予算管理の表と同じシートに混ぜるのは避けたほうがよいでしょう。列の性質が違いすぎて、どちらの用途でも読みにくくなります。案件番号でつなぎ、別のシートか別の場所に置きます。
予算管理の表が使われなくなる理由のうち、見落とされやすいものがあります。超えたときに何が起きるのかが決まっていない、という点です。
超過が見えたときの選択肢は、実務では4つしかありません。発注を止める、他の費目から回す、来月に繰り越す、そのまま超過を認める。この4つのどれを選ぶのかを、金額の幅ごとに先に決めておきます。
たとえば、月の予算に対する超過が5%以内なら担当者の判断でそのまま進める。5%から20%なら部門の責任者が他の費目からの振り替えを判断する。20%を超えたら発注を止めて相談する。この3段階を決めておくだけで、表を見た人がその場で動けます。
決めていない表は、見た人が「誰に相談すればよいのか」から考え始めます。考えている間に発注が進み、翌月にまた同じ超過が出ます。何度か繰り返すと、表を見ても何も変わらないという認識が広がり、入力そのものが止まります。
この決まりは、表と同じファイルの先頭のシートに書いておきます。別の文書に書くと、半年後には誰も場所を覚えていません。表を開いた人の目に入る場所に置くのが、いちばん確実です。
費目の名前だけを一覧に並べたシートは、あとで揃わなくなります。名前の横に、その費目に何を入れるのかを1行で書いておきます。
たとえば「外注費」なら、制作会社や業務委託の相手への支払いを入れる、社内の人の稼働は入れない、と書きます。「広告費」なら、媒体への出稿費用を入れる、制作の費用は外注費に入れる、と書きます。この1行があるだけで、入力する人が迷う場面が大きく減ります。
定義を書いておく効果は、入力のときよりも、差異を見るときに出ます。差異が出た費目の定義を読めば、何が含まれているかが分かるので、原因の当たりを付ける範囲が狭まります。定義がない表では、まず「この費目には何が入っているのか」を担当者に聞くところから始まります。
境界が曖昧な支出が出てきたときの扱いも、この一覧に書き足していきます。どちらに入れるか迷った支出は、迷ったこと自体が記録になります。同じ迷いが2回起きたら、その時点で定義に1行を加えます。
費目の数そのものは、部門の担当者が迷わず選べる範囲に抑えます。数十個の費目に分けた表は、毎月違う費目に同じ支出が入り、結果として集計の意味が薄れます。細かく分けたい欲求は、費目ではなく備考の欄で受け止めます。
表の中身と同じくらい効くのが、どこに置いて誰がどう触るかです。ここを決めていない表は、作りが良くても数字が合わなくなります。
置き場所は1か所にします。クラウドの共有フォルダの決まった場所に1つだけ置き、そこ以外にファイルを作らない決まりにします。作業用の複製を作りたくなったときは、複製ではなく別のシートを増やす形にします。
編集できる人を絞ります。明細に書き込めるのは部門の担当者と経理だけ、集計のシートは読むだけ、という分け方にします。集計のシートを触れる状態にしておくと、式が数字で上書きされる事故が起きます。上書きされた式は、見ただけでは気づけません。
ファイル名に日付を入れないことも決めておきます。日付が入った名前は、必ず複製を誘います。履歴は、クラウド側のバージョンの機能に任せます。
最後に、更新の順番を決めます。明細に入れる、集計のシートを開いて数字を確認する、差異の理由を書く。この順番を文字で書いて表の先頭に置きます。順番が決まっていないと、差異の理由を書く工程が毎月最後に回り、そのまま省かれます。
道具を変える話の前に、エクセルのままで続けたほうがよい条件を挙げます。
入力する人が3人以下であること。同時に触る場面がほとんど起きず、複製されたファイルが増える心配もありません。
費目が固定されていること。年に1回の見直し以外では費目が変わらないなら、表の形も変わりません。
締めのあとに遡って直す必要が少ないこと。変更の跡が残らない弱点が、問題になりにくくなります。
判断が月に1回で足りること。日々の動きを追う必要がないなら、更新の頻度が低くて済みます。
部門をまたいだ集計が不要であること。1つの部門で完結するなら、提出の遅れで全体が止まる問題が起きません。
この5つが揃っているなら、別の仕組みに移る必要はありません。むしろ、慣れた表を捨てる手間のほうが大きくなります。
現場でしばしば出る失敗は、次の4つの型に整理できます。
1つ目は、予算を作った人と実績を入れる人が違い、費目の理解が揃っていない型です。予算を立てた人は「外注費」に制作会社への支払いを想定していたのに、実績を入れる人が業務委託の報酬もそこに入れる。合計は合っていても、差異の意味が読めません。費目の定義を一覧のシートに1行ずつ書いておくことで防げます。
2つ目は、予算の修正を上書きしてしまう型です。期中に組み替えたときに元の値が消えると、達成率が良くなった理由を説明できません。当初と修正を2列に分けます。
3つ目は、報告のための表を毎月手で作る型です。コピーと貼り付けを挟むたびに転記の誤りが入り、会議の場で数字が違うという指摘が出ます。集計のシートは、明細を差し替えれば自動で更新される形にしておきます。
4つ目は、しきい値を決めずに全部の差異を追う型です。金額の小さい差異にまで理由を求めると、入力の負担が増えて提出が遅れます。しきい値を1つ決めるだけで、毎月の作業は目に見えて軽くなります。
見やすさは、続くかどうかに直結します。効くポイントは4つです。
数字の単位を列名に書きます。「外注費（千円・税抜）」のように書きます。表の上に1回だけ書いた単位は、印刷して配ったときに読み違えられます。
色は2種類までにします。基準を超えた行と、未入力の行。この2つだけです。色が増えると、どれが重いのか分からなくなります。
会議で見る表は、スクロールせずに全体が入る行数に絞ります。明細は別のシートに置き、聞かれたときに開きます。
列の並びは更新の頻度で決めます。左からめったに変わらないもの、右に行くほどよく変わるものです。年月、部門、費目が左で、実績と備考が右になります。触る列が右側にまとまるので、横スクロールの回数が減ります。
行の並びは、金額の大きい費目を上に置きます。費目の名前の五十音順に並べた表は、見る人が毎回上から下まで目で追うことになります。判断に関わる費目が上に集まっていれば、会議で見る範囲が上の数行に収まります。
数字の桁を揃えることも効きます。千円単位で見せると決めたら、すべての費目を千円単位にします。売上だけ円単位で、費用は千円単位という表は、合計を見たときに桁の違いに気づけません。
予算は、年度の初めに決めた数字のまま1年を終えることがほとんどありません。事業の状況が変われば組み替えます。この組み替えを表の上でどう扱うかで、期末の説明の手間が大きく変わります。
まず、予算の列は「当初」と「修正」の2列で持ちます。修正が入っても当初の数字が残るので、達成率が良くなった理由を説明できます。1列で持って上書きすると、期末に誰も理由を答えられません。
次に、組み替えた日と理由を記録します。明細とは別に、組み替えの履歴だけを積むシートを1枚作ります。列は「日付、部門、費目、変更前、変更後、理由」の6列で足ります。年に数回しか書かないシートですが、期末の説明のときにこのシートが一番読まれます。
三番目に、どの単位までの組み替えを認めるかを決めます。部門の合計を変えずに費目の間で振り替えるのは部門の責任者が判断してよい、部門の合計を変えるのは上の判断が必要、といった線です。この線を決めていないと、組み替えが増えたときに全社の合計が静かに変わっていきます。
四番目に、組み替えの回数に上限を置きます。四半期に1回までとするチームが多いようです。毎月組み替えられる表は、予算の意味が薄れます。差異が出るたびに予算を実績に寄せていくと、最後は予算と実績が常に一致する表になり、判断の材料が残りません。
予算管理表のテンプレートは、会計ソフトの会社や表計算ソフトの提供元から無料で配布されています。使うかどうかの判断は、次の点で決められます。
明細と集計が分かれているかどうか。1枚のシートに横に月が並ぶ形は、月が増えたときに壊れます。
費目の一覧が別シートにあり、入力規則が仕込まれているかどうか。仕込まれていないテンプレートは、配った先で表記が揺れます。
参照範囲がテーブルになっているかどうか。固定の行数で書かれたものは、行が増えたときに欠けます。
自社の費目に合わせて直せるかどうか。シートに保護がかかって列を変えられないものは、長く使えません。
予実管理表のアウトプット形式はテンプレートを参考に、実際に関数を入力したり数字を入力したりしながら、エクセルの操作を覚えていきましょう。 出典: diggle.jp
出来合いのテンプレートをそのまま運用に乗せるより、形を参考にして自分で組み直すほうが結果的に早く済むことが多くあります。明細の6列から作れば、最初の表は30分程度で用意できます。
表ができたら、毎月の段取りを決めます。ここが決まっていない表は、3か月で止まります。
期日を2つ置きます。部門が実績を出す期日と、まとめた表を配る期日です。月初の第3営業日と第5営業日が、多くのチームで現実的な線になります。
出してもらう形も揃えます。明細と同じ6列のシートを配り、その形で返してもらいます。形が違うものが集まると、貼り付ける前の整形に時間がかかります。
承認の経路も表につなげます。発注の承認をチャットやメールで行っているなら、承認した時点で表に1行入れる決まりにします。承認と入力が同じ日に起きる形にしておくと、月末に実績が急に増える現象が消えます。
会議で何を話すかも決めます。差異の大きい上位5行だけを議題にする形が回りやすいでしょう。すべての行を追う会議は長くなり、翌月から参加率が落ちます。
別の仕組みに移るかどうかは、次のサインで判断できます。入力する人が5人を超えた。案件の行が月に40件を超えた。承認した発注が表に入るまでに数日かかっている。同じ数字を2人が違う値で持っている場面が直近3か月で起きた。このうち2つが当てはまるなら、表の手直しでは戻りません。
移るとして、選ぶときの軸は3つです。
1つ目は、入力する人の負担です。とりまとめる人が楽になるかではなく、案件の担当者が続けられるかで決まります。入力の画面が重い道具は、2週間で誰も更新しなくなると言われています。検討の段階で、実際に入れる人に触ってもらうのが確実です。どの見せ方や項目が最初から使えるかはできることのページで確かめられます。
2つ目は、料金の増え方です。必要な1つの機能のために上のプランへ上がる形だと、比較の段階で機能の表を読み比べる手間がかかります。機能で絞らず、区切るのは人数とボードの数だけという形なら、判断が人数の話に収まります。金額と区切り方は料金のページで確かめてください。
3つ目は、いまの表と板をどう持っていくかです。エクセルの明細はCSVで書き出せますが、いま使っているタスクの板を作り直す手間は見落とされがちです。Trelloからの移行のように既存の板を取り込める経路があるかどうかは、先に確かめておきます。
予算管理を軽くする道は、表を良くすることだけではありません。実績が表に入るまでの経路を短くするほうが効きます。
実績が遅れて入る原因を辿ると、ほとんどが承認の場所と入力の場所が離れていることに行き着きます。発注の相談がチャットで進み、承認もチャットで出て、そのあと誰かが思い出して表に入力する。この最後の1手が抜けるだけで、月末に実績が積み上がります。
これを直す方法は2つあります。1つは、入力を承認の条件にすることです。表に1行入れてから承認する決まりにします。手間は増えますが、遅れは消えます。もう1つは、承認そのものを板の上でやることです。発注のカードを作り、金額と発注先を書き、承認の列に動かした時点で記録が残る形にします。後者のほうが、入力する人の負担が軽く済みます。
道具ごとに、カードに金額のような独自の項目を持たせられるかどうかが違います。持たせられる場合でも、上のプランが必要になることがあります。検討している道具を横に並べて見るなら比較の一覧が早く、いま使っている道具が決まっているならTrelloとの比較やBacklogとの比較のように1対1で見るほうが判断しやすいでしょう。
予算の数字を扱う場所を移すときは、誰がどこまで見られるかを先に確かめます。部門ごとの費用は、全員が見てよい情報とは限りません。権限の分け方とデータの扱いの考え方は安全性の考え方にまとめられています。人数の数え方など細かい点はよくある質問にあります。
全部をいきなり移す必要はありません。まずは発注の件数がいちばん多い1つの部門だけを板に移し、翌月の実績が締めの日までに揃ったかどうかを見る。1か月の試しで、移す価値があるかどうかは判断できます。
費目が固定されていて、入力する人が3人以下で、月に1回の判断で足りるなら、エクセルのままで長く続きます。行き詰まるのは案件ごとの原価を追い始めたときで、案件が月に40件を超えるあたりから明細が膨らみ、費目の並びが案件ごとに違う問題が出てきます。全社の費目と案件別の原価は、別の場所で持つのが実務的です。
年月、部門、費目、予算、実績、備考の6列です。この形で縦に積み、横に月が並ぶ表は集計のシート側で作ります。差異の欄は金額と率の2つを持ちます。率だけだと金額の小さい費目が上位に並び、金額だけだと規模の違う部門を横に並べられません。
承認の場所と入力の場所が離れていることが原因の大半です。発注の承認をチャットやメールで出したあと、誰かが思い出して表に入力する形になっていると、その1手が抜けます。表に1行入れてから承認する決まりにするか、承認そのものをカードの移動で行う形にすると、遅れは消えます。
チームが数十人までなら、金額に換算せず時間だけを別に持つほうが続きます。換算するには社内の単価を決める必要があり、その議論が毎年発生します。案件ごとの外注費と経費は金額の表、人の稼働は時間の表に分け、案件番号でつなぐ形が軽く済みます。稼働は週に1回まとめて入れる形にすると止まりにくくなります。