Excel。事務職のデータ分析その13。回帰分析の結果を使って適正値を求めよう
＜回帰分析を使った表＞
次の表を使って、
分析ツールをアドインして、回帰分析を算出してみると、
y = -1.4641x + 184.76
ということがわかりました。
売上個数＝－1.4641×販売金額 ＋ 184.76を表している数式ですね。
ということを以前ご紹介させていただきました。
グラフでも確認しておきましょう。
そこで、折角、回帰分析を算出してみたので、
この数値を使ってシミュレーション（最適化）して、
価格をいくらにすると、売上個数はどのぐらいになるのか？確認してみましょう。
利益＝（販売金額－仕入金額）×売上個数
という計算式を使って、利益を求めていきましょう。
F4の粗利は、販売金額－仕入金額で算出できますよね。
なので、数式は、=F1-F2
F5の利益は、売上個数×粗利で算出できますので、
=F3*F4
当然、金額を倍にしたら、粗利があがるので、
利益もUPということに、数式上はなりますよね。
しかし、これはおかしいですよね。
販売金額をUPしたら、売りにくくなる可能性がありますよね。
販売金額が変わったら、売上個数が変化するということが、
この数式では反映されていません。
ここで登場するのが、回帰分析で登場した、数式。
y = -1.4641x + 184.76
なのです。
詳しく書きなおすと、
売上個数＝－1.4641×販売金額 ＋ 184.76
では、表を下記のように修正してみます。
G8には、係数である、-1.4641を入力します。
G9には、切片である、184.76を入力します。
G3の売上個数を修正しますので、
=G8*G1+G9
という数式を設定します。
今回は、個数なので、小数点はありえませんので、
INT関数を使って小数点を切り捨てるようにしますので、最終的な数式は、
=INT(G8*G1+G9)
すると、売上個数は38となりました。
F1とG1を200に変更してみましょう。
F列は、売上個数が販売金額で変化することを考慮していません。
なので、利益がUPするようになっていましたが、
G列は売上個数が販売金額で変化することを考慮する、
回帰分析の数値を使っていますが、
なんと、－109。つまり全く売れないという結果の数値が算出されました。
では、F1とG1を逆に90にしてみましょう。
回帰分析の数値を使わないと、利益は下がると算出されておりますが、
回帰分析の数値を使った数式にした事により、なんと、販売金額を下げたらば、
売上個数がUPして、最終的には、利益もUPしていますよね。
簡単な数式ではありますが、
回帰分析を今後加えた資料を誰もが作る必要性が高まってくるのではないでしょうか？
このあとは、90に下げたら利益がUP。
改善される予想が出ていますが、
89にしたら、85にしたら…と適正な販売金額【最適化】を探していくわけですね。
