過去数週間にわたって、私達は見ました Excelの用法と人気の上昇そして私は、Excelがアナリティクスに進出したことを指摘しました。今日は、シナリオベースの計画と最適化という1種類の分析について説明します。
Excelアドインを使用すると、モンテカルロシミュレーションを使用して、仮説分析およびシナリオベースの計画を作成できます。しかし、基本的なExcelにはそのような組み込み機能がないため、この種の分析を作成するのは面倒です。また、ディスプレイはグラフィックディスプレイではなくスプレッドシート形式です。入力をグラフィカルに表示したり、出力への影響を相互作用とともに表示できることは、視聴者にとって非常に貴重です。
数年前、私たちは独自のExcelアドインを使ってJMP分析をExcelスプレッドシートに追加することにしました。これは、JMPグラフィックスとモンテカルロシミュレーションを組み込んでExcelスプレッドシートの価値を高めます。
いつ引退できますか - 例
2人のアプローチの違いを、私たち全員に近いものであり、親愛なるトピックを使用して見てみましょう。いつ引退するのに十分なお金がありますか？以下は、潜在的な結果を示すために作成したスプレッドシートです。
それは今までの私の貯蓄（期首残高）、私が毎年貯蓄することを計画しているもの、私が私の投資からどれだけの利益を得ることができるかそして私が仕事をする予定の期間から始まる。次に、開始残高の将来価値と一連の貯蓄の将来価値を計算します。入出力セルのみを表示していますが、いくつかの列が非表示になっていることに気付くでしょう。
数値がどのように計算されたかを示すために、列を非表示にしました（ところで、スプレッドシート内のものを隠すことができるのは良い考えですか？）。これで、モデルの作成方法がわかりました。
退職までの年数に基づいて計算する必要がありました。その数が10であれば、私は始値と節約の流れの両方の将来の価値公式を10年先に設定したいと思います。私は30個のセルを作成することによってそれを達成しました（私が働くために30年以下があると仮定すると）それぞれ以下の公式で
列Dのセルには、= + IF（$ C $ 9> D5、D5 + 1,0）が含まれています。
E列のセルには= IF（D6 <> 0、E5,0）が含まれています。
どちらも必要に応じて相対的または絶対的な用語です。これにより、計算に必要な年数の配列を作成できました。
セルF10には= FVSCHEDULE（F6、$ E $ 5：$ E $ 54）と
セルF11には= FV（C8、C9、F7）* - 1が含まれます。
これはこのモデルを作成するための最善の方法ではないかもしれませんが、それは結果を正しく計算するように思えます - それでより良い方法がある場合それはどうしますか。動作するようになったね。（同意しない場合、あなたは典型的なExcelユーザーではありません。）
入力を調整して、出力が変わるのを見ることができます。綺麗な。それでも、スプレッドシート内には明らかではない潜在的な洞察があります。モデルの理解を深めるためのグラフィック表現があります。
ここでは、Excelがシナリオベースの分析を実行する方法の詳細については説明しません。 あなたはExcelからそれを学ぶのは自由です。 しかし、私はどのように見せたいのですか JMP Excelができないことを新鮮な洞察を提供することができます。
JMPアドインを呼び出してスプレッドシートを開き、JMPタブをクリックして、Create / Edit Modelを選択します。
次に、作成/編集ダイアログボックスを使用してモデルを定義します。
定義段階では、選択ボタンをクリックして、入力ラベルと入力金額を含むセルを選択します。各入力セルに最小値、初期値、最大値を設定してから、同じポイントアンドクリック方式で出力セルを識別します。
モデルを定義したら、JMPアドインタブから[モデルの実行]を選択します。JMPは以下を表示します。
JMPは結果（将来の節約）に寄与する変数を図で示します。これらのグラフはセルよりもはるかに多くの情報を伝えます。JMPは各変数の最小値/最大値をグラフ化しているため、最も急な勾配を持つものが、ターゲット変数に最も影響を与えるもの（将来の節約）です。この場合、貯蓄を増やすために私が働くことができる年数です。
しかしプロファイラまた、各変数が他の変数にどのように影響を与える可能性があるかも示します。マウスを使って各グラフの真ん中にある赤い縦線を選択して左右にドラッグすると、実際にはその変数の値を上下させることができます。退職までの年数を増やすと、年次ROIグラフはどうなるのでしょうか。
その線の傾きは劇的に増加し、この変数が結果に大きな影響を与えていることを示しています。これは理にかなっています - あなたがあなたのお金が興味を得て成長するのを許す時間が多いほど、より良いです。
しかし、分析はそれだけにとどまりません。各変数を現実的な値に設定した後、それらの値に達する確率をテストすることができます。つまり、年間平均10,000ドルを節約するか、または7％の収益を得る可能性がどれだけあるのでしょうか。投資？これがシミュレーションが効くところです。
JMPを使用すると、モンテカルロシミュレーションを実行するための変数を定義できます。
上の画像は、年間の節約額、収益率、および退職までの年数をランダムに変更することによって、将来の節約で100万ドルの目標を達成する可能性を示しています。5％の確率で私の目標を達成しても、それが実数であれば気分が悪くなることはありません。
しかし、このブログシリーズの目的は、既存のスプレッドシートを活用する方法を示すことではありません。実際には、スプレッドシートにエラーがあった場合（そして何も見つけられなかった場合）、JMP分析はほとんど価値がなく、おそらく誤った結論や判断につながる可能性があります。
来週は、JMPで同じモデルを作成する方法を見ていきます。
注意： このブログ記事は、スプレッドシートのエラーに関するシリーズ 。このシリーズの新しい記事は毎週水曜日に表示されます。
チャールズピレロ書きました：
ありがとうブルース、
ユーザーが捕らえたトラップの種類に関する洞察が提供されてうれしいです。来週の結論の後、読者はこれらのタイプの分析のためのより洗練された、よりリスクの少ない解決策があることに同意することを願っています。
マイク・クレイトン書きました：
普通のIRAタイプの貯蓄システムを考えると、貯蓄の例は常に私が非常に楽観的なROIであると思うことを特徴としています。1994年から2014年までの20年間の大幅なボラティリティからの節約に対するROIは、AFTER ROIとBEFORE TAXESの約4パーセントを示しています。しかしそのお金を出すために、私は所得税を支払わなければなりません...私は私がその20年間の着実な貯蓄の間に避けたことに同意します。
だから何が本当ですか？IRAの一般的な使用を考えて、貯蓄プラン分析に税の問題をどのように含めますか。これは、ROTHまたはREGULAR IRAのどちらかにサインアップする前に、モンテカルロの取り組みを追加するための興味深いモデルかもしれません。素敵なウェビナーになるでしょう。
ブルースジャンダ書きました：
これはExcelに関するこれまでの記事を基にした素晴らしいビルドです。私はあなたが以前に説明した罠に陥った。
次回の投稿で、すべてのJMP環境でこの問題が発生するのを楽しみにしています。
