Excelで感度分析を行って重要変数を見つける方法

感度分析とは？Excelで「重要変数」を見つけるメリット

感度分析とは、売上や利益、コスト、投資回収期間などの結果に対して、どの変数がどれくらい影響を与えるのかを確認する分析方法です。簡単に言うと、「この数字が少し変わったら、最終結果はどれだけ変わるのか？」を調べる作業です。

たとえば、あなたが新規サービスの収益シミュレーションをExcelで作っているとします。売上は「顧客数 × 単価」で計算され、利益はそこから広告費や人件費などを差し引いて求めます。このとき、利益を大きく左右するのは顧客数なのか、単価なのか、広告費なのか。感覚だけでは判断しにくいですよね。そこで役立つのが感度分析です。

感度分析を行うと、数ある変数の中から結果に大きな影響を与える「重要変数」を見つけることができます。重要変数がわかれば、仕事の優先順位を決めやすくなります。たとえば、利益に最も効くのが「単価」なら価格戦略を見直すべきですし、「顧客数」が大きく影響するなら集客施策に力を入れるべきです。

これはビジネスの現場で非常に重要です。なぜなら、すべての数字を同じ熱量で改善しようとすると、時間も労力も足りなくなるからです。特に20代のうちは、限られた時間の中で成果を出すことが求められます。感度分析を使えば、「どこを変えれば成果につながりやすいか」を数字で説明できるようになります。

また、Excelで感度分析を行うメリットは、特別なツールを使わずに始められる点です。多くの会社ではExcelが標準的に使われており、関数や表計算に少し慣れていれば十分に実践できます。高価な分析ソフトやプログラミングの知識がなくても、データテーブルやグラフを使うことで、変数の影響度を見える化できます。

さらに、感度分析は上司や関係者への説明にも役立ちます。「なんとなくこの施策が大事だと思います」では説得力が弱いですが、「単価が5％上がると利益は15％改善します」と言えれば、意思決定の材料として一気に強くなります。数字に基づいて話せる人は、会議や資料作成でも信頼されやすくなります。

つまり感度分析は、単なるExcelテクニックではありません。成果につながるポイントを見極め、限られたリソースを効果的に使うためのビジネススキルです。次章からは、実際にExcelで感度分析を行うために、まずどのように数値モデルと変数を整理すればよいのかを見ていきましょう。

まずは準備：分析したい数値モデルと変数を整理する

感度分析をExcelで行う前に、まず必要なのが「何を分析したいのか」と「どの数字を変化させるのか」を整理することです。ここが曖昧なまま進めると、Excelの表は作れても、結局どの変数が重要なのか判断しにくくなってしまいます。

最初に決めるべきなのは、分析したい最終結果です。たとえば、新規サービスのシミュレーションであれば「月間利益」、営業施策であれば「売上高」、投資判断であれば「投資回収期間」などが対象になります。感度分析では、この最終結果をアウトプットとして設定します。

次に、そのアウトプットを計算するための数値モデルを作ります。難しく考える必要はありません。たとえば利益を分析するなら、以下のようなシンプルな式で十分です。

• 売上 = 顧客数 × 単価
• 変動費 = 顧客数 × 1人あたり原価
• 利益 = 売上 − 変動費 − 固定費 − 広告費

このように、最終結果がどの数字から計算されているのかを分解しておくことが大切です。数式の構造が見えていれば、どの変数を動かせば結果が変わるのかを確認しやすくなります。

続いて、感度分析の対象にする変数を選びます。変数とは、結果に影響を与える可能性がある入力値のことです。たとえば「顧客数」「単価」「原価」「固定費」「広告費」などが該当します。ただし、最初からすべての数字を対象にすると表が複雑になります。まずは、結果に影響が大きそうなものや、実務上コントロールしやすいものを優先して選びましょう。

Excel上では、入力値・計算式・結果を分けて配置すると管理しやすくなります。おすすめは、上部に前提条件となる入力値、中央に計算式、下部に最終結果を置くレイアウトです。

また、各変数には基準値を設定しておきます。基準値とは、通常ケースとして考える値のことです。たとえば顧客数1,000人、単価3,000円、広告費50万円といった前提です。感度分析では、この基準値をもとに「10％増えたらどうなるか」「20％減ったらどうなるか」を確認していきます。

このとき注意したいのが、単位をそろえることです。円なのか万円なのか、月次なのか年次なのかが混在すると、計算ミスの原因になります。Excelのセルには「円」「人」「％」などの単位を明記し、誰が見ても前提がわかる状態にしておきましょう。

ここまで準備できれば、感度分析の土台は完成です。次の章では、整理した数値モデルを使って、Excelのデータテーブルで変数ごとの影響度を確認する方法を見ていきます。

Excelのデータテーブルを使って変数ごとの影響度を確認する

数値モデルと変数を整理できたら、次はExcelのデータテーブルを使って、各変数が結果にどれくらい影響するかを確認していきます。データテーブルは、指定した入力値を自動で入れ替えながら、結果がどう変わるかを一覧表示できる機能です。

たとえば「顧客数が変わったときに月間利益がどう変化するか」を見たい場合、まず基準となる利益計算モデルをExcel上に作っておきます。ここでは、顧客数の入力セルをB2、最終結果である月間利益のセルをB10として考えます。

次に、別の場所に感度分析用の表を作ります。左側の列に、顧客数のパターンを並べます。たとえば以下のように、基準値の前後で複数のケースを用意します。

そして、表の右上にあたる「月間利益」の最初のセルに、最終結果のセルを参照する数式を入力します。たとえば月間利益がB10にあるなら、=B10と入力します。ここがポイントです。直接計算式を書くのではなく、元のモデルの結果セルを参照します。

その状態で、顧客数の一覧と利益の列を含む表全体を選択します。次にExcelのメニューから、「データ」→「What-If分析」→「データテーブル」を選びます。縦方向に顧客数のパターンを並べている場合は、「列の代入セル」に元の顧客数入力セル、つまりB2を指定します。

するとExcelが、800人、900人、1,000人……という値を順番にB2へ代入し、そのたびに月間利益を再計算して表に表示してくれます。これにより、顧客数が増減したときの利益への影響を一目で確認できます。

同じ手順で、単価、原価、広告費など他の変数についても個別にデータテーブルを作成します。たとえば単価を分析する場合は、単価のパターンを左側に並べ、「列の代入セル」に単価の入力セルを指定します。広告費を分析する場合も同様に、広告費の入力セルを指定すればOKです。

重要なのは、一度に多くの変数を動かしすぎないことです。最初は「顧客数だけ」「単価だけ」のように、1つの変数だけを変化させて確認しましょう。そうすることで、その変数単体が結果に与える影響を把握しやすくなります。

各変数のデータテーブルができたら、基準値からどれくらい結果が変化したかを比較します。たとえば、顧客数が10％増えたときの利益増加額と、単価が10％増えたときの利益増加額を比べれば、どちらがより利益に効く変数なのか判断できます。

このようにデータテーブルを使うと、感覚ではなく数値で「効く変数」を見つけられます。次章では、この結果をさらにわかりやすくするために、グラフ化して重要変数を見分ける方法を解説します。

グラフ化して一目でわかる「効く変数・効かない変数」の見分け方

データテーブルで変数ごとの結果を確認できたら、次はグラフ化して視覚的に比較しましょう。表の数字だけでも分析はできますが、変数が複数になると「結局どれが一番重要なのか」が見えにくくなります。グラフにすると、結果への影響が大きい変数ほど変化が目立つため、一目で判断しやすくなります。

まずおすすめなのが、各変数の変化率と結果の変化率を折れ線グラフにする方法です。たとえば、顧客数・単価・広告費をそれぞれ「-20％、-10％、0％、+10％、+20％」で変化させ、そのときの利益が何％変わったかを並べます。

この表を選択し、Excelの「挿入」→「折れ線グラフ」を選ぶと、変数ごとの影響度を線の傾きで比較できます。ここで見るべきポイントは、線の角度です。傾きが急な線ほど、結果に大きく効く変数です。上の例であれば、単価の線が最も急なので、利益への影響が大きいと判断できます。

一方で、線がほぼ横ばいの変数は、多少変化しても結果にあまり影響しない「効かない変数」です。もちろん完全に無視してよいわけではありませんが、改善施策の優先順位は下げてもよい可能性があります。

また、上司やチームに説明する資料では、棒グラフも有効です。特に「各変数が10％変化したとき、利益が何％変わるか」を横棒グラフにすると、どの変数が重要かをランキング形式で見せられます。

• 単価10％アップ：利益+22％
• 顧客数10％アップ：利益+18％
• 広告費10％アップ：利益-8％

このように並べると、「まず単価改善を検討すべき」「次に顧客数を増やす施策を考えるべき」といった示唆が出しやすくなります。資料にする場合は、重要な変数だけ色を変えると、さらに伝わりやすくなります。

グラフ化で注意したいのは、単位をそろえて比較することです。顧客数は「人」、単価は「円」、広告費は「円」のまま比べると、公平な比較になりません。できるだけ「変数を何％動かしたか」「結果が何％変わったか」という変化率でそろえると、重要変数を正しく見分けられます。

つまり、感度分析では数字を出して終わりではなく、グラフで見える化することで実務に使える判断材料になります。線の傾きが急な変数、棒グラフで大きく伸びる変数が、優先的に管理すべき重要変数です。

実務で使える感度分析の活用例とミスを防ぐポイント

感度分析は、Excel上の練習で終わらせるのではなく、実務の意思決定に活かしてこそ価値があります。特にビジネス現場では、限られた予算や時間の中で「どこに注力すべきか」を判断する場面が多くあります。そのとき、感度分析を使えば、感覚ではなく数字をもとに優先順位を決められます。

たとえば営業部門では、売上目標を達成するために「商談数を増やすべきか」「成約率を上げるべきか」「客単価を上げるべきか」を検討する場面があります。感度分析でそれぞれを10％改善した場合の売上インパクトを比較すれば、最も効果が大きい施策を選びやすくなります。

• 商談数を10％増やした場合の売上変化
• 成約率を10％改善した場合の売上変化
• 客単価を10％上げた場合の売上変化

また、マーケティングでは広告予算の検討にも使えます。広告費を増やせば顧客数が増える一方で、利益は広告費の分だけ圧迫されます。そこで、広告費を増やした場合に「どの程度の顧客増加が必要か」を確認すれば、無理のある施策かどうかを事前に判断できます。

新規事業や投資判断でも感度分析は有効です。初期費用、月間売上、解約率、人件費などを変化させ、投資回収期間がどう変わるかを見れば、事業計画のリスクを把握できます。特に上司に提案する場合は、楽観ケースだけでなく、標準ケース・悲観ケースも用意しておくと説得力が高まります。

一方で、感度分析には注意点もあります。まず避けたいのが、現実的でない範囲まで変数を動かしてしまうことです。たとえば単価をいきなり50％上げるシミュレーションをしても、実際に市場が受け入れないなら意味がありません。変化幅は、過去実績や市場環境をもとに設定しましょう。

次に、変数同士の関係を無視しないことも重要です。単価を上げると利益率は改善するかもしれませんが、同時に顧客数が減る可能性があります。広告費を削ればコストは下がりますが、新規顧客の獲得数も減るかもしれません。1つの変数だけを見て結論を出すのではなく、実際に起こりそうな連動も考える必要があります。

さらに、Excelの数式ミスにも注意しましょう。感度分析では同じモデルを何度も参照するため、元の計算式が間違っていると、すべての結果がズレてしまいます。分析前には、入力セル・計算式・結果セルが正しくつながっているかを必ず確認してください。

最後に大切なのは、分析結果をそのまま結論にしないことです。感度分析はあくまで意思決定を助ける材料です。数字で重要変数を見つけたうえで、実行可能性、顧客への影響、社内リソースも合わせて判断しましょう。Excelで重要変数を把握できれば、提案や改善施策の精度は大きく高まります。