Excel What-If分析の分析手法とは？

 

Excelにおける"What-If"分析は、特定の変数や条件を変更して、それがデータや計算結果にどのように影響するかを評価するための手法です。これは、仮説を検証し、予測を行うために非常に便利なツールです。

〇主な分析手法

主な"What-If"分析の手法には、以下のようなものがあります。

 

データテーブル (Data Table): データテーブルは、特定のセル内の数式や関数を、異なる変数や条件の組み合わせに対して自動的に計算するための機能です。たとえば、異なる利率や期間におけるローンの支払い額を調査する場合、データテーブルを使用してそれらの変数を簡単に変更し、結果を比較できます。

 

シナリオマネージャ (Scenario Manager): シナリオマネージャは、ワークシート内の異なるセルの値や数式を、異なるシナリオに基づいて一括で変更することができるツールです。これにより、異なる条件下での結果を簡単に比較することができます。

 

ゴールシーク (Goal Seek): ゴールシークは、目標値を達成するために必要な入力値を見つけるためのツールです。特定のセルの値を目標値に設定し、別のセルの値を変更して目標を達成するための入力値を求めることができます。

 

シナリオ分析 (Scenario Analysis): シナリオ分析は、特定の変数や条件に対するモデルの感応性を評価する手法です。異なるシナリオや仮定の下で、モデルの挙動がどのように変わるかを調査します。

 

これらの手法を使用することで、ビジネス計画、財務分析、プロジェクト管理など、さまざまな分野で異なる条件下での結果を簡単に比較し、意思決定をサポートすることができます。

〇データテーブルを詳しく

データテーブルの例として、ローンの支払い計算を考えてみましょう。以下は、ローンの元本、利率、期間に基づいて毎月の支払い額を計算するための単純なExcelデータテーブルの例です。

 

元本 (Loan Amount): A1セルにローンの元本を入力します。例えば、$100,000とします。

 

利率 (Interest Rate): B1セルに利率を入力します。例えば、5%とします。

 

期間 (Loan Term): C1セルにローンの期間（年数）を入力します。例えば、30年とします。

 

支払い計算 (Payment Calculation): D1セルに以下の数式を入力します。これは、PMT関数を使用してローンの毎月の支払い額を計算するものです。

 

=PMT(B1/12, C1*12, -A1)

データテーブルの作成:

 

E2セルを選択します（データテーブルの左上のセル）。

リボンの "データ" タブから "テーブル" を選択します。

テーブルのダイアログボックスが表示されたら、"行の入力セル" にA1セル（元本）を指定します。

"列の入力セル" にB1セル（利率）を指定します。

OKボタンをクリックします。

これにより、データテーブルが作成され、元本と利率の組み合わせに対して、異なる期間の支払い額が自動的に計算されます。このデータテーブルを通じて、利率と期間の変化に伴う支払い額の違いを一目で確認することができます。

〇シナリオマネージャを詳しく

シナリオマネージャは、ワークシート内の異なるセルの値や数式を、異なるシナリオに基づいて一括で変更するための機能です。以下は、シナリオマネージャを使用して異なる利率に基づいてローン支払いを計算する例です。

 

元本 (Loan Amount): A1セルにローンの元本を入力します。例えば、$100,000とします。

 

期間 (Loan Term): A2セルにローンの期間（年数）を入力します。例えば、30年とします。

 

支払い計算 (Payment Calculation): B1セルに以下の数式を入力します。これは、PMT関数を使用してローンの毎月の支払い額を計算するものです。

 

=PMT(C1/12, A2*12, -A1)

シナリオの設定:

 

別のセルに利率のシナリオを設定します。例えば、D1セルに4%、E1セルに5%、F1セルに6%などの利率を設定します。

シナリオの管理:

 

リボンの "データ" タブから "シナリオマネージャ" を選択します。

シナリオマネージャのダイアログボックスが表示されたら、"追加" ボタンをクリックします。

シナリオ名を入力します（例: 4%、5%、6%など）。

変更したいセルの範囲を選択し、OKボタンをクリックします。

シナリオの切り替え:

 

シナリオマネージャのダイアログボックスで、表示したいシナリオを選択してOKボタンをクリックします。

これにより、シナリオに基づいて異なる利率の下でのローン支払いが自動的に計算され、切り替えることができます。シナリオマネージャを使用することで、異なる条件での結果を簡単に比較し、意思決定をサポートすることができます。

〇ゴールシークを詳しく

What-If分析の中で、ゴールシークは特定の目標値を達成するために必要な入力値を計算する手法です。具体的なケースとして、以下のステップでゴールシークを詳しく説明します。

 

例: 利益を最大化するための販売数量の計算

 

データの準備:

 

Excelのワークシートに、販売数量と利益が関連する数式が含まれているセルを用意します。例えば、A1セルに販売数量、B1セルに利益の数式があるとします。

 

A1: 販売数量

B1: =A1 * 単価 - 固定費 - 変動費 * A1

ゴールの設定:

 

目標となる利益を決定します。例えば、利益を最大化するためにはどれだけの販売数量が必要かを見積もります。

ゴールシークの設定:

 

Excelのリボンから "データ" タブを選択し、"ゴールシーク" オプションをクリックします。

ゴールシークのダイアログボックスが表示されたら、以下のように入力します。

セル参照: 利益を計算するセル（例: B1）を指定します。

値を変更するセル: 販売数量を入力するセル（例: A1）を指定します。

値: 目標の利益を入力します。

ゴールシークの実行:

 

OKボタンをクリックしてゴールシークを実行します。

結果の確認: 

ゴールシークが成功すると、指定した利益に最も近い販売数量が計算され、それがセルA1にセットされます。結果を確認して、目標の利益が達成されたかどうかを確認します。

この例では、ゴールシークを使用して、特定の利益を達成するために必要な販売数量を見つけるプロセスを示しています。これにより、手動で何度も計算する手間を省き、迅速かつ正確に目標を達成するための入力値を見つけることができます。

〇シナリオ分析について詳しく

What-If分析の一環としてのシナリオ分析は、異なる条件や仮定に基づいてビジネスモデルやプロジェクトの結果を評価し、意思決定をサポートする手法です。Excelを使用してシナリオ分析を行うと、異なるシナリオにおける結果を比較しやすくなります。以下に、詳細な手順と具体例を示します。 

シナリオ分析の手順:

データの準備: 

Excelにビジネスモデルやプロジェクトに関連するデータを入力します。これには、数式や関数が含まれ、シナリオ分析の対象となる変数が含まれます。

変数の識別: 

影響を与える可能性のある主要な変数や要因を識別します。これには、売上、コスト、利益率、販売数量などが含まれます。

シナリオの定義: 

考えられる異なるシナリオや仮定を定義します。これは、変数や要因が異なる条件下でどのように変化するかを仮定するものです。例えば、景気の好況シナリオ、不況シナリオ、中立的なシナリオなどが考えられます。

データテーブルやシナリオマネージャの利用: 

データテーブルやシナリオマネージャを使用して、異なるシナリオに基づいて結果を一度に表示したり、管理したりします。これにより、手動で数値を変更する手間を省きながら、異なる条件での結果を比較できます。

結果の分析: 

各シナリオにおける結果を分析し、特定の条件下でのリスクや機会を理解します。どのシナリオが最も影響を与えるかを把握し、戦略や対策を検討します。

具体例: 製品の収益シナリオ分析

データの準備: 

製品の収益モデルをExcelに作成し、関連するデータを入力します。これには、販売価格、費用、販売数量などが含まれます。

変数の識別: 

影響を与える可能性のある主要な変数として、広告費、販促活動、競合他社の動向などを特定します。

シナリオの定義: 

3つのシナリオを仮定します。

楽観的なシナリオ: 広告効果が高く、販促活動も成功すると仮定。

悲観的なシナリオ: 広告費が予算オーバーし、競合他社の影響が大きいと仮定。

ベースケース: 標準的な広告効果と販促活動を仮定。

データテーブルやシナリオマネージャの利用: 

データテーブルやシナリオマネージャを使用して、各シナリオにおける収益の変動を簡単に比較します。

結果の分析: 

各シナリオの結果を分析し、特定の条件下でのリスクや機会を理解します。異なるシナリオに基づいて、製品の収益に影響を与える要因を特定し、戦略の調整やリスク管理に活かします。

シナリオ分析は、ビジネスの複雑さと不確実性に対処するための強力なツールであり、What-If分析の中でも重要なアプローチの一つです。