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

浦和税理士法人
松波 竜太

2007/3/5

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

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

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

分析準備

［ステップ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　季節調整された値が得られた

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

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

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