ビジネスソフト ヒント×テクニック
経営分析


実績に基づいて、適切な月次売上予算を立てるには?

浦和税理士法人
松波 竜太

2007/3/5

未来を予測することは難しい。しかし、だからといって予算を編成するときに勘と度胸だけに頼るのも考えものだ。予算額を合理的に導き出す方法はないだろうか?

- PR -

 予算編成を行う際、「前年度売上高の○%増で……」と適当に決めてしまうのは簡単です。しかし、この数値が現実とあまりに懸け離れたものとなってしまうと、従業員や株主・金融機関などからの信頼を失ってしまうことになりかねません。

 また、前期データを基に月次予算を作成することが多いと思いますが、前期特有の特殊な要素が含まれている可能性がありますので、前年月次データのみに基づいて考えるというのは好ましくありません。

 そこで今回は、過去データをベースに今期の売上の傾向を分析し、そこに季節指数を加味して、月次予算まで落とし込むという方法を説明したいと思います。具体的には月次推移の季節調整済みデータを基に回帰分析を行い、関数式に当てはめることにより売上を予測します。

分析準備

[ステップ1] 3期分(以上)の月次推移データを用意する

 まずは、会計ソフトごとに月次推移データの取り出し方を見てみましょう。

(1)弥生会計

  1. [集計表]−[残高推移表]−[年間推移]を選択
  2. メニューバーの[Excelへの書き出し]ボタンを押す
  3. 「新規Excelファイルに書き出す」の損益計算書(及び製造原価報告書)をチェックして[OK]ボタンを押す

(2)勘定奉行

  1. [分析処理]−[推移表]−[科目別推移表]を開く
  2. 条件設定画面から[印刷・転送]ボタンを押す
  3. 月範囲を指定(当期)して、[転送]ボタンを押す
  4. 転送条件は「他ソフト編集」「タブ区切り」を指定して[実行]を押す(保存場所は任意、ファイル名は例えばsuii.txt)

(3)PCA会計

  1. [分析処理]−[月次推移表]を選択
  2. [集計期間]を選択
  3. [印刷]ボタンを押す
  4. 「汎用データ」選び、損益計算書をチェックして、保存場所を任意に指定して、ファイル名を指定(保存場所、ファイル名は勘定奉行と同じ)し、[OK]ボタンを押す

[ステップ2] 季節調整値を求める

 ステップ1で会計データより集計したデータから売上データを例に取って、季節調整値を求めてみましょう。例として下記のような3期分のデータが集計できたとします。

売上
X1年4月 21,976,345
X1年5月 21,069,297
X1年6月 22,823,089
X1年7月 25,403,761
X1年8月 26,054,983
X1年9月 30,643,599
X1年10月 28,826,735
X1年11月 27,430,262
X1年12月 29,522,353
X2年1月 31,419,116
X2年2月 33,866,640
X2年3月 32,880,402
X2年4月 33,194,237
X2年5月 30,902,484
X2年6月 30,969,582
X2年7月 32,414,579
X2年8月 33,974,414
X2年9月 30,715,664
X2年10月 31,561,850
X2年11月 26,775,958
X2年12月 34,985,260
X3年1月 35,817,500
X3年2月 40,465,161
X3年3月 38,507,891
X3年4月 37,870,312
X3年5月 41,498,419
X3年6月 40,725,049
X3年7月 46,248,962
X3年8月 44,827,669
X3年9月 46,483,769
X3年10月 42,864,309
X3年11月 38,224,501
X3年12月 44,033,507
X4年1月 32,015,937
X4年2月 34,897,893
X4年3月 40,313,001
サンプルデータ1 3期分の売上データ

 このデータを季節調整し、季節調整済みデータに変換します。季節調整の方法については、「季節変動を取り除いて、月次の業績推移を分析するには?」をご覧ください。

 その結果、各月の「季節調整値」と、「季節指数」が得られます。

画面1 得られた季節調整済みデータ

分析手順

[ステップ3] 回帰分析により当期を予測する

 当てはめる関数式には一次関数(直線)のほかに、指数関数やゴンペルツ曲線などがあります。現データの変動の様子によって、適用すべき関数式は変わってきます。ここでは、短期の売上の変動に当てはまりの良い、一次関数(直線)Y= a X + bを用いて売上の予測をします。

 まず、B列とC列の間に1列挿入して、季節調整値の順序に対応するように1から36の数字を入力します。

画面2 C列に連続値を入力する

 続いて、回帰分析を行います。Excelには数字をドラッグするだけで直線回帰式を自動的に導き、さらに数値を代入してくれる便利な機能が付いていますので、これを使いたいと思います。

 方法は非常に簡単です。C2:D37のセルを選択して、D37セルのフィルハンドルにポインタを合わせて形が「+」型になったら、D49セルまでドラッグします。

画面3 D49セルまでドラッグする。マウスポインタ脇の「48」という数字は、ここでドロップすると選択範囲中で左側にあるC列一番下(C49セル)に48という値が入力されることを表している

 すると結果は、以下のとおりになります。

画面4 回帰分析による予測値が代入された

 ちなみに、分析ツールを使って傾きと切片を計算した結果、当てはめる関数は Y=534,426.7X+24,094,533になりました。グラフ化してみると、得られた値がどのようなものか、感覚的に分かるでしょう。

参考グラフ 

[ステップ4] 季節指数を加味して月ごとの予測値を求める

 最後に、ステップ3で得られた数値に季節指数を加味して月ごとの売上予算を計算します。D38→D49の合計が年間売上予算となりますので、これに4月から翌年3月までの季節指数を掛け合わせて毎月の予算を求めます。

 具体的には、

  1. D50セルにD38:D49の合計を入力
  2. E38セルに「+$D$50*G2/12」を入力(「$D$50」は上記D50セルのことで、絶対指定になっています)
  3. E38セルをアクティブにし、フィルハンドルをE49セルまでドラッグ

をすることにより、計算できます。

画面5 回帰分析の予測値に季節調整を行う

 結果、以下のような数値が求められます。

画面6 季節調整された値が得られた

 このようにして、過去のデータから月次の売上予算を求めることができます。

 ただし、過去データの範囲の取り方によって、回帰式は増加の式にも減少の式にもなり得ます。数期分の元データをグラフにして確認し、範囲を適切に調整して、現状の趨勢に見合った売上予測を行うとよいでしょう。

筆者プロフィール
松波 竜太(まつなみ りょうた)
税理士(関東信越税理士会所属)
神奈川大学経済学部卒。大手OA機器商社・会計事務所勤務を経て、現在 浦和税理士法人 代表社員(埼玉県さいたま市)。本業の決算、税務申告・相談を行う傍ら、会計データの統計解析法を研究する。帰納的アプローチにより企業の経営課題を分析し、成果をクライアントである中小企業にフィードバックしている。「多くの中小企業がデータもツールもそろっているのに、それを分析して経営に生かす方法を知らないのは残念。中小企業はもっと生産効率を高めていける」と考えている。「お役立ち会計事務所全国100選 2004年度版(三和書籍、実務経営サービス編)」に選出される。
ブログ:http://www.maznami.biz/

「ビジネスソフト ヒント×テクニック」 − INDEXページへ


この記事に対するご意見をお寄せください managemail@atmarkit.co.jp

キャリアアップ

- PR -
@IT Sepcial
@IT Sepcial

「ITmedia マーケティング」新着記事

新型コロナ接触確認アプリ プライバシーと感染抑止の間で揺れ動く各国の対応
欧米では新型コロナ「第2波」への危機感が高まっています。主要国における接触確認アプリ...

2020年米国ホリデーシーズンショッピング、オンライン売上高が過去最高に――Adobe予測
Adobeはホリデーシーズンの米国におけるオンライン売上高が1890億ドルになると予測してい...

自撮り写真の加工はメイク? 整形?――ByteDance「Ulikeユーザー白書」
ナチュラルに盛れると人気のカメラアプリ「Ulike」のユーザー調査。もっとも、カメラアプ...