数値の探索
Googleスプレッドシートで、計算式や条件を考慮して最適な数値を探索して求めてくれるGoal Seekアドオンを試してみます。
Excelのソルバーのような機能です。
3行まとめ
- GoalSeekアドオンを導入することで、スプレッドシート内で簡易的なExcelのソルバーのような機能を実現可能
- 変更するセル・計算結果のセル・目標値を設定してボタン押すだけ
- ただし「目標の値にするためには数値をどうすればよいか？」に特化しているため、最大化や最小化へ利用する場合は注意が必要
- その場合はExcelのソルバーなどを使いましょう
Goal Seek
Goal Seekや最適化がどういうものかは一旦おいておいて、利用するための設定だけしてしまいましょう。
スプレッドシートの「拡張機能」→「アドオン」→「アドオンを取得」で、アドオンの検索画面を開き、
Goal Seekを検索するとGoogle製のアドオンが出てくるのでインストールします。
インストール時にエラーが出た場合はこちらのマーケットプレイスから検索してインストールもできます。
例題
実例がないとわかりにくいので、こんな場面を考えてみます。
あなたはアイスクリーム屋さんです。
材料を入荷してアイスを販売するときの入荷計画を立てるとします。
アイスは気温によって売上が大きく変わり、去年1年の気温別売上実績をまとめると表のようになっていました。
これをもとに今年の売上予測モデルを作って入荷・販売計画を立てます。
※いろいろツッコミどころありそうですが目をつぶってください
| 気温 t | 販売個数(過去実績) |
|---|---|
| 0 | 5 |
| 5 | 6 |
| 10 | 10 |
| 15 | 19 |
| 20 | 33 |
| 30 | 64 |
| 35 | 110 |
予測モデルを作る
見るからに気温が高ければ急激に売れるようになっているので、気温を使った2次関数で販売個数を予測してみます。
販売個数 = a(t-p)+q
a = 0.1
p = 2
q = 5
としてみると、グラフのようにいい感じに実績と近い値を再現できています。
また、仮の設定として売価と原価（材料費）を定めます。
売価 = 100
入荷の原価 = 20
ではこれをスタートに以下の問題を考えて見ます。
原価の最適化
原価をどのぐらいに設定するのがよいかを考えます。
思ったより売れなかったときのリスクを考えて、「去年よりも売れる数が20%減になっても赤字にならないようするには原価をどの程度に抑えておけばよいか」考えます。
20%減少の状態を表とグラフで表すと
こんな感じで上の章で作った予測モデル分だけ材料を入荷したけど、売れ残りがいっぱい出てしまった状態になります。
この状態で損がギリギリ出ない原価設定（つまり損益分岐点）を見つけるには、粗利の合計が0になる部分を見つけられるとOKです。
※本来はもっと気温を細かくしたり35度の日が何日あるかみたいな重みを考えたりが必要ですが、今回は無視！
ここでGoal Seekの登場です。
今回の損益分岐を求めるには、「原価」を色々変更して「粗利」が０になる点を見つけられればよいわけです。
ということで
| 設定項目 | 指定 |
|---|---|
| Set Cell | 粗利合計のセル |
| To Value | 0 |
| By Changing Cell | 原価のセル |
に設定してSolveボタンを押します。
すると、数値がグネグネ動いて数秒後に計算が完了します。
原価が70.92となり、これ以下に抑えておけば粗利が確保できそうです。
粗利の最大化
次のテーマとして、予測モデルを良いものにしていくことを考えます。
最初はパラメータa,p,qを適当に雰囲気で決めていましたが、このパラメータを最適化します。
では今度は売値・原価は固定しておいて、パラメータaを動かして粗利が最大化するaを探してみましょう。
多く入荷すると売れ残りがマイナスになり、少なく入荷すると機会損失が発生するのでそのバランスする点を見つけたい、というものです。
| 設定項目 | 指定 |
|---|---|
| Set Cell | 粗利合計のセル |
| To Value | 100000 |
| By Changing Cell | aのセル |
ここで、To Valueの設定ですが、「最大にする」「最小にする」という設定は見当たらないのでとりあえず大きそうな数字を入れておきます。
この状態で実行してみると、一応それっぽい数字が出てきています。
グラフを見ても、販売個数よりも少しだけ多く入荷しておく（機会損失をなくす）ような数値になっており、一般的な感覚と合ってそう。
ただ、Errorが出ていることからも本来は想定されていない使い方と思われるので、Excelのソルバーなどの最大化・最小化に対応したツールを使うのが一番です。
簡単な問題設定で、ある程度答えの数値の妥当性がわかるときは一旦Goal Seekで試してみるのでもいいかなと。
まとめとソルバー利用バージョン
GoogleスプレッドシートでもGoal Seekを使うと簡単な最適化は実現できそうです。
このツールをうまく動かすためには数式の作り方に慣れないといけませんが、一度慣れてしまうと応用範囲が広いのでぜひどうぞ。
そしてLibraOfficeのCalcをみると、メニューの「ツール」に「ゴールシーク」と「ソルバー」がありました。
このソルバーを使うと、モデルパラメータa,p,qすべてを選択して粗利の最大化なども計算できます。
すごーい。