【事業計画】全自動集計のフォーマットを作ろう

経営企画

さて前回、事業計画・予算策定の下準備までについて解説しました。

まだご覧になれていない方はこちらをどうぞ。

【事業計画】エクセル不要!スプレッドシートで全自動集計
経営企画や予算担当者の方でエクセルと毎回格闘されていませんか?スプレッドシートを使えば、全自動集計が出来て、予実管理も出来ます!是非、業務の効率化を目指して、早く退社できるよう頑張りましょう!まずはその概要を説明します!

では、今回は実際のフォーマットの策定について触れてたいと思います。

フォーマット作成にあたっての下準備

実際の下準備がどのような感じになっているのか、まずは確認しましょう!

イメージはこんな感じです。過去1年間(実際は9ヶ月分くらいになりますかね、次年度の予算策定のタイミングによると思います)の営業部の部門コストを抽出します。

以下の表をシート名「過去実績」で作成していきます。

部門・勘定科目・取引先・品目にそれぞれCDを振っておきます。

使用している会計ソフトによっては、デフォルトで入っていますので、そのままエクスポートしてください)

また、マスタCDの一覧表も作成しておく必要があります。以下の表をシート名「マスタ一覧」に作成していきます。

これは全社共通で、予算作成している時点までに発生した過去取引をベースに作成します。

また一覧では、#(ナンバリング)も振っておきます。

このナンバリングを使用して実際に作業をしていきます。これは「合算#」を作るために使用します。

合算#は「&関数」を使用して作成します。品目#・勘定科目#・取引先#・部門#を繋げていきます。

こちらの作業については、後ほど紹介します。

集計作業は「sumif関数」を使用しますので、全ての合算#が一致している取引だけが合計されていきます。

また、最後に「取引先名」の列の最後に必ず「未定」を加えて、取引先CD及び取引先#には適当な数字を振ってください(他のCD・#と被らないように)

上記理由は、後ほど解説します。

下準備が済んだら実際に関数を活用して作業

シート「過去実績」にて、「摘要」列の横にまずは「text関数」で、「日付」の列に入っている日付を以下のように変えてください。1行目は「日付関数」とでも入力してください。

例:4/18/2018(又は2018/4/18)→2018年4月

Text関数例:=IF(B2=””,””,TEXT(B2,”yyyy年m月”))

上記の表では一番左に「日付」の列がありますが、A列は空けてB列から日付を入力してください(A列は別の用途で使用します)

その次に「日付関数」を入力して列の横に、順に「部門#」・「勘定科目#」・「品目#」・「取引先#」と入力してください。

入力が終えたら、各列の2行目から「vlookup関数」で各#をシート「マスタ一覧」から参照してください。

部門#例:=IFERROR(VLOOKUP(C2,’マスタ一覧’!$Q$1:$R$1000,2,FALSE),””)

上の例では、シート「過去実績」のC列に部門CDが入力されており、シート「マスタ一覧」ではQ列に部門CDが入力されております。そしてR列に部門#が入力していますのでそこから参照しています。

またセルの範囲は、今回は1000行としています。これは皆さんにお任せしますが、過去実績にてどの程度会社が取引があるのかによって変わってきます。

範囲自体は少し多く取っておく必要もあり、その理由は予算策定にあたって新規取引先や品目が増えるからです。

予実でも本ファイルを使用しますので、新たに追加される事も考慮する必要もあり、範囲はある程度広めに取っておいた方が良いのです。

また、シート「マスタ一覧」で取引先に「未定」を加えて頂いたかと思いますが、これは予算を作る際に「この勘定科目と品目で、これだけの予算を確保する必要があるが、取引先が決まっていない」という事に対応するためです。

例えば、今取引先を選定中とか十分どこの会社でもあるかと思います。

また、品目名の「未定」はなるべく避けましょう!(だって、品目も決まっていないのに予算を取るとかふざけていますよねwそこは、ちゃんと考えてね!と上手く担当者と交渉しましょう)

歴史ある会社さんで、かつ取引先も多いとなると、かなりの数になるかと思います。シート「マスタ一覧」を作ったあとに、どの程度必要になるかはきちんと把握しておく必要があります。

上記と同じように、勘定科目#・品目#・取引先#を入力していってください!

そして、入力を終えますと、取引先#の列の横に「合算#」列を作ってください。

「合算#」列では、以下の関数(&関数)を入力してください。

セルR2=IFERROR(N2&O2&P2&Q2,””) 

上記作業を終えると、シート「過去実績」は以下のようになります。

金額(税抜)より左は省略しています

次回は、少しテクニカルな話になりますが、これまでピボットを使ってきた方にも参考になる話になると思います。

経営企画
Adminをフォローする
経営管理ラボ

コメント