Excelでシミュレーションをするなら知っておきたい3つの機能

スクリーンショット 2015-08-15 19.39.42

税理士の仕事でExcelを使うのはどんな時か?

私があえてExcelを使う理由として多いのは、シミュレーションをする場合です。

例えば、新規事業を行う場合に、どれくらいの売上高の時にどれくらいの利益が見込めるかなどを計算します。

そこで、今回は、Excelでシミュレーションをする場合に、これは絶対に使えるようになっておいたほうが良いという3つの機能について話をしてみようと思います。

スポンサードリンク

いくつかのケースで比較をするならchoose関数

シミュレーションをするというのは、いくつかのケースによって求めたい結果がどう変わるのかを確認したり、比較をしたいということが多いはず。

例えば、新規出店を検討している際には、客数や客単価などを一つに限定するのではなく、楽観的、普通、悲観的などいくつかのケースを想定して、どれだけ利益が出るかを予想するはずです。

しかし、それぞれケースごとに表を作成するのは面倒。

その場合には、数字が変化する組み合わせを前提条件としていくつかのケースにまとめておき、それを切り替えて表示するとよいでしょう。

その場合に用いるのがchooseという関数です。

例えば、楽観的、普通、悲観的の3つのケースでそれぞれの月別の客数が想定される場合、次の様に設定をします。

・choose関数:fx/choose

スクリーンショット 2015-08-15 18.12.39

スクリーンショット 2015-08-15 18.13.10

なお、この場合、インデックスというのがシミュレーションの切り替えボタンの役割は果たします。

そこに入力した数字の値を返すのです。

このケースで言えば、1=悲観ケース 2=普通ケース 3=楽観ケースが返されます。

後は、インデックスだけを絶対参照にした上で、切り替えたいデータすべてにこの関数をコピーをすれば良い。(オレンジの欄)

そして、客単価にも同じような設定をしましょう。

スクリーンショット 2015-08-15 18.26.23

後は、切り替えボタンに数字を入れれば、それぞれのケースごとの収益シミュレーションが切り替わって表示されることになります。

スクリーンショット 2015-08-15 19.38.29

スクリーンショット 2015-08-15 19.38.46

スクリーンショット 2015-08-15 19.39.42

なお、同様のことは「sumif関数」を用いたり、What-if分析の「シナリオ」と言う機能で行うこともできますので、そちらも試してみてください。

ピンポイントの目標値を求めるならゴールシーク

目標となる売上高や損益分岐点売上高などピンポイントの数字を満たすためにはどうなればよいのかという答えを導きたい場合にはゴールシークという機能を用います。

・ゴールシーク:データ/What-if分析/ゴールシーク

例えば、この場合の普通ケースで赤字にならないために最低限必要な客数を求めたいのであれば、利益=0を「目標値」と定め、それに必要な客数を「変化させるセル」とすれば良いのです。

スクリーンショット 2015-08-15 19.49.56

スクリーンショット 2015-08-15 19.50.22

制約条件の中で最適値を求めるのであればソルバー

このソルバーを使うといくつかの制約条件の中で求める結果を最大、最小あるいは指定の値にするための組み合わせなどを導き出すことができます。

これぞスプレッドシートというようなすごく便利な機能なのですがなぜかこのソルバーは標準ではインストールされていません。

まずは、アドインを追加してください。

・ファイル/オプション/アドイン/ソルバーアドイン/OK

具体的な使い方としては、今回の例で言うと、次のようなメニュー構成の時に実際に客単価が3000円になるには、それぞれどれだけのオーダーがあればよいのか、

さらに、一月で提供できるAコースの上限が100、Bコースが200だというような制約条件がある中ではどんな組み合わせが必要かなども求めることができるのです。

スクリーンショット 2015-08-15 21.29.53

スクリーンショット 2015-08-15 21.30.34

ちなみにソルバーは以前のGoogleドキュメントにも実装されていた機能なのですが新しいバージョン
になった時に削除されてしまいました。

最近では、スプレッドシートといえば、共有もしやすいので、まずはGoogleDriveを使う事を考えますが、シミュレーションのモデルを作るときは、まだExcelのほうが良さそうですね。

セミナー音源No.12:はじめてのExcel財務モデル

インフィードモバイル

9割の人が間違えている「会社のお金」無料講座公開中

「減価償却で節税しながら資産形成」
「生命保険なら積金より負担なく退職金の準備が可能」
「借金するより自己資金で投資をするほうが安全」
「人件費は売上高に関係なく発生する固定費」
「税務調査で何も指摘されないのが良い税理士」

すべて間違い。それじゃお金は残らない。
これ以上損をしたくないなら、正しい「お金の鉄則」を