Excel What-If 分析の選挙シナリオ
エクセルWhat-If 分析シナリオ
12月14日の衆院選挙開票結果のシナリオをエクセルで作成してみました。今朝4日の日経新聞では、「自民、300議席うかがう」との記事や前日の「公示前の与党勢力は326(自民295、公明31)議席が全体の7割弱を占め、与党がどれだけ議席を確保できるのかで政局や政策の行方が大きく変わる。」として3つのシナリオと政局や政策の行方が出ていました。それを元に4つシナリオをエクセルで作成してみました。
エクセルの表と積み上げ縦棒グラフを作成してWhat-If 分析のシナリオを登録してみました。4つのシナリオを切り替えることにより、セルが変化してグラフも変わる。このようにすると、より実践的なExcel資料が作成できます。
操作方法
「データ」タブの「データツール」の「What-If 分析」▼「シナリオの登録と管理」をクリックします。「シナリオの追加」ダイアログボックスのシナリオ名に「シナリオ1 絶対安定多数」と入力して「OK」をクリックして、セルD3に「238」とD4に「28」入力して「OK」をクリックします。
次に、「シナリオの登録と管理」ダイアログボックスの「追加」をクリックして「シナリオの追加」ダイアログボックスのシナリオ名に「シナリオ2 公示前勢力」と入力して「OK」をクリックして、D3に「295」とD4に「31」入力して「OK」をクリックします。
同様に、「シナリオの登録と管理」ダイアログボックスの「追加」をクリックして「シナリオの追加」ダイアログボックスのシナリオ名に「シナリオ3 自民党単独過半数割れ」と入力して「OK」をクリックして、セルD3に「237」とD4に「25」入力して「OK」を設定します。同様に「シナリオの登録と管理」ダイアログボックスの「追加」をクリックして「シナリオの追加」ダイアログボックスのシナリオ名に「シナリオ4 与党大勝利」と入力して「OK」をクリックして、セルD3に「310」とD4に「35」入力して「OK」を設定します。
Excel What-If 分析シナリオ
エクセルWhat-If 分析シナリオ
OPEC減産合意が見送られたことから、原油価格が5年4ヶ月ぶりの安値を更新し、燃料安のメリットを受ける航空会社の株が前日比4%高になったようです。航空会社A社は営業費用の約22%の約3600億円が燃料費であり、原油価格は今年100ドル台から60ドル台まで下落しており10%下落すると約360億円費用が軽減される可能性があります。
そこで、現状と90ドル、80ドル、70ドル、60ドルのシナリオを作成して、シナリオごとに営業費用と営業利益をシュミレーションしてみます。原油が10ドル下落するごとに約360億円営業利益が増えていくシナリオになります。これは1年間の平均価格の試算でありますから、今3月期(残り4ヶ月)ではなく来期のかなり大まかなシナリオです。
エクセルのシナリオは、変化するセルの値を複数登録して、シミュレーションを行うことのできる機能です。登録したシナリオを選択して表示をクリックするだけで、セルの値が切り替わります。また、シナリオ情報レポートを作成するとシミュレーションを一覧表として分析できます。
操作方法
「データ」タブの「データツール」の「What-If 分析」▼「シナリオの登録と管理」をクリックします。「シナリオの追加」ダイアログボックスのシナリオ名に「現状」と入力して「OK」をクリックして、セルC5に「16150」と入力して「OK」をクリックします。
次に、「シナリオの登録と管理」ダイアログボックスの「追加」をクリックして「シナリオの追加」ダイアログボックスのシナリオ名に「90ドル」と入力して「OK」をクリックして、セルC5に「15785」と入力して「OK」をクリックします。
同様に、「シナリオの登録と管理」ダイアログボックスの「追加」をクリックして「シナリオの追加」ダイアログボックスのシナリオ名に「80ドル」と入力して「OK」をクリックして、セルC5に「15420」と入力して「OK」をクリックします。同様に「70ドル」は「15055」、「60ドル」は「14689」と設定します。
登録したシナリオを選択して「表示」をクリックすると、営業費用と営業利益のセルの値が変わります。
シナリオ情報レポートの作成
「シナリオの登録と管理」ダイアログボックスの「情報」をクリックして表示される「シナリオの情報」ダイアログボックスのシナリオの情報にチェックして「OK」をクリックすると、シナリオ情報レポートが作成されます。
エクセルデータ テーブル
What-If 分析でローン計算
クロス集計表のデータテーブル
昨日に続きデータテーブルで分析したいと思います。ローンの月次返済額をもう少し低減したいが、期間と金利を変更して10万円より少なくしたい。一覧表でわかりやすい資料で分析したい。What-If分析のデータ テーブルでローンの計算結果をシミュレーションしてみたいと思います。
計算結果が出たセルに対して、計算の2つの引数を変えてクロス集計の表形式を作成できるのがWhat-If 分析のデータ テーブルです。クロス集計の行に金利、列に返済年数を入れ計算結果を分析することができます。
操作方法
まずは行に金利、列に年数を入れます。セルD2の式を絶対参照にします。これを数式バーでコピーしてセルA5に貼り付けます。
次にセル範囲A5:H20を範囲指定して、「データ」タブ「のWhat-If 分析」の「データテーブル」をクリックします。「データテーブル」ダイアログボックスの「行の代入セル」にセルB2をクリックして「列の代入セル」にセル範囲C2をクリックし「OK」をクリックします。
計算結果が表示されます。
条件付き書式
これにより、条件付き書式を使い月次返済額が10万円より少ない金額を塗りつぶしを設定します。
セル範囲A5:H20を範囲選択して、「ホーム」タブの「条件付き書式」から「セルの強調表示のルール」の「指定の値より小さい」をクリックして、「100000」と入れ任意の書式を選び「OK」をクリックします。
金利と借入金額、借入金額と返済期間など、行と列のデータを入れ替え違うパターンの分析ができます。
ナレッジ・パソコンスクール 詳しくはExcel講座
エクセルWhat-If 分析
What-If 分析のデータ テーブル
異なる値の計算結果を一覧表に
What-If 分析のデータ テーブルは、セルの値を変更したときにワークシートの数式の結果にどのように影響するかを調べるプロセスです。What-If分析のゴールシークは計算結果の目標値を決め変化させる引数を1つ指定しました。データ テーブルは変化させる引数を2つ指定できます。
たとえば、現在価値を計算するPV関数の計算結果に、割引率と期間の2つの引数を変更した場合どのような結果となるのかシミュレーションすることができます。
東京スカイツリーの投資額1430億円、収入が82億円、割引率3%、25年で回収、と言う条件がありました。PV関数を使い1年先の82億円の現在価値を割引率3%で計算しました。
これを行に割引率、列に年数を入れ計算結果をシミュレーションすることができます。
操作方法
まずは行に割引率、列に年数を入れます。セルD2の式を絶対参照にします。数式を選択してF$キーを押します。$マークが付き絶対参照になります。これを数式バーでコピーしてセルA5に貼り付けます。
次にセル範囲A5:H30を範囲指定して、「データ」タブ「のWhat-If 分析」の「データテーブル」をクリックします。「データテーブル」ダイアログボックスの「行の代入セル」にセルB2をクリックして「列の代入セル」にセル範囲C2をクリックし「OK」をクリックします。
計算結果が表示されます。
31行目にSUM関数(B6:B30)を結果して、右方向にコピーします。小数点以下の書式設定をします。
これにより、25年間の現在価値が3%の場合は1428億円になり、4%の場合は1280億円、5%の場合1156億円、6%の場合は1048億円になります。この一覧表にすることにより割引率が高くなると現在価値が減少していく様子がよくわかります。
ナレッジ・パソコンスクール 詳しくは Excel講座