患者中心の保健医療を支える
処方情報分析のリーディングカンパニー

 03-3239-6840

棒グラフと近似曲線で売上予測

time 2018/05/15

※解説記事内の画像はExcel 2010のものです。

今回はEXCELの棒グラフと近似曲線を用いた簡単な売上予測をご紹介します。

これまでのデータを分析し、将来の大まかな予測を行うための機能がEXCELにはあります。
近似曲線を用いて売上予測をしてみましょう。

売上データの準備

自社製品の過去の売上実績表(サンプル)を作成しました。
後ほど登場する数式をわかりやすくするため、売上(円)は小さい数字を用います。
サンプル
この過去データから2018年1月の売上予測をしてみたいと思います。

グラフから近似曲線を設定する

EXCELの棒グラフから近似曲線を設定する方法をご説明します。
まずはいつも通りB列とC列を対象とした棒グラフを作成します。
B列とC列を選択して、キーボードのファンクションキー「F11」を押下します。
サンプルデータ
すると簡単に棒グラフを作成できます。
棒グラフ
グラフの見た目は残念ですが、今回の目的は売上予測なのでほうっておきますね。

次にこのグラフに線形近似曲線を追加していきます。
ちなみに、この線形近似曲線は回帰曲線とも呼ばれます。

グラフのデータ系列を選択し、右クリックでメニューを表示します。
するとその中に「近似曲線の追加」メニューがあるので、これを選択してください。
棒グラフポップアップ
「近似曲線の書式設定」ウィンドウが開くので、「線形近似」のラジオボタンを選択し、「グラフに数式を表示する」「グラフにR-2乗を表示する」のチェックボックス両方にチェックを入れてください。
近似曲線の書式設定
するとグラフに直線と、y = 1629.4x + 409.09とR2 =0.8788数式が表示されます。
これで売上予測をする準備は完了です。
数式あり
このy = 1629.4x + 409.09が線形近似曲線の数式です。

このように、予測を目的とする分析を回帰分析といいます。
そのうち、上記のように目的変数(y)を説明変数(x)ひとつだけで予測するものを「単回帰分析」といい
その2変量の間の関係性をY=aX+bという一次方程式の形で表します。

a(傾き)とb(Y切片)がわかれば、X(時系列)からY(売上)を予測することができます。

R-2乗値(決定係数)

このR-2乗値は決定係数と呼ばれ回帰分析の結果を読み取る指標のひとつです。

決定係数(けっていけいすう、: coefficient of determination)は、独立変数(説明変数)が従属変数(被説明変数)のどれくらいを説明できるかを表す値である。寄与率と呼ばれることもある。標本値から求めた回帰方程式のあてはまりの良さの尺度として利用される。
引用:ウィキペディア
https://ja.wikipedia.org/wiki/%E6%B1%BA%E5%AE%9A%E4%BF%82%E6%95%B0

なかなか難しいですね。。。

簡単にお伝えすると1に近いほど、回帰式が実際のデータに当てはまっていることを表していて、
説明変数(X)が目的変数(Y)をよく説明していると言えます。
この決定係数ですが、実際にはいくつ以上が適切という基準はなく、
「R2 = 0.4」でも当てはまるということもあるし、「R2 = 0.7」当てはまらないという事もあります。

長くなりそうなので、この辺はまた別の機会にでもご紹介しようかと思います。
今回は近似曲線のR-2乗値が0.87なので、「ほどほどに当てはまる」ということで進めます。

売上予測

ではEXCELの設定してくれた数式を用いて2018年1月の売上を推計してみます。
売上を求める場合は
     売上 = 1629.4 × 年月+  409.09
で算出できます。
過去データの期間が12ヶ月あり、2018年1月は13ヶ月目に当てはまるため、X = 13となり下記の結果が得られます。
        1629.4× 13 +  409.09 = 21591.29

つまり2018年1月の売上予測値は21591.29円ということになります。
売上予測完了です。

まとめ

いかがでしたか?
EXCELと過去データを用いて売上予測をする方法をご紹介しました。

ただし今回のような予測値には限界があります。
あくまで過去の推移から推計しているので、今まで起きていない外部環境の影響が含まれていないのです。
たとえば、競合品の発売・ガイドラインの変化等々、実際には多くの要素を考慮して推計する必要があります。

状況に応じて適切な分析手法を用いて頂ければと思います。
統計学の難しいところですね。。。

最後まで読んで頂きありがとうございました。

タグ

このブログについて

このブログ(JMIRI Office部)は、製薬企業のマーケティング部門の方やアナリティクス/レポーティングを担当している方に向け、Excel / PowerPointなどのOfficeソフトの使い方、分析の考え方、グラフの使い方、美しい資料の作り方、効率的な作業の進め方など、仕事に役立つ様々な情報を少しゆるめに発信していきます。

Author:医療情報総合研究所(JMIRI)について

医療情報総合研究所 JMIRIは、まだビックデータという言葉が無いころに日本で初めて処方情報データベースの運用・分析を開始した、処方情報分析のリーディングカンパニー。これまでに累計数十万ページ規模の分析レポート/グラフを製薬企業に向けて作成/提供してきました。

[詳細]


お問い合わせ

arrowup

mail