ExcelでWhat-If分析を使って利益シミュレーションを行う方法

What-If分析とは？利益シミュレーションに使えるExcel機能の基本

「この商品の価格を少し上げたら、利益はどれくらい増えるのか？」「原価が10％上がった場合、目標利益を維持するには何個売ればいいのか？」こうしたビジネス上の“もしも”を数値で確認できるのが、ExcelのWhat-If分析です。

What-If分析とは、直訳すると「もし〜だったら分析」という意味です。Excel上で売上、販売数、原価、利益率などの条件を変えながら、結果がどのように変化するかをシミュレーションできます。感覚や勘に頼るのではなく、数字をもとに判断できるため、営業、企画、マーケティング、経理など幅広い仕事で役立つ機能です。

たとえば利益は、基本的に以下のような式で考えられます。

• 売上 ＝ 販売単価 × 販売数量
• 利益 ＝ 売上 − 原価
• 利益率 ＝ 利益 ÷ 売上

このとき、販売単価や販売数量、原価のどれかが変わると、当然ながら利益も変わります。ExcelのWhat-If分析を使えば、こうした変化を手作業で何度も計算し直す必要がありません。条件を変えたときの結果を効率よく確認できるため、短時間で複数パターンの利益シミュレーションができます。

Excelでよく使われるWhat-If分析の代表的な機能には、ゴールシークとデータテーブルがあります。

ゴールシークは、「最終的に達成したい数値」から逆算する機能です。たとえば「利益を100万円にするには、売上はいくら必要か？」といったケースで使えます。目標から必要な条件を導き出せるため、売上目標や販売計画を立てるときに便利です。

一方、データテーブルは、複数の条件を一覧で比較したいときに役立ちます。たとえば、販売価格を3,000円、3,500円、4,000円に変えた場合や、販売数が100個、200個、300個になった場合の利益をまとめて確認できます。1つずつ数値を入力し直すよりも、圧倒的に効率よく比較できます。

20代のビジネスパーソンにとって、Excelで利益シミュレーションができることは大きな武器になります。会議で「なんとなく良さそうです」と話すよりも、「価格を5％上げると利益は約○万円増えます」と数字で説明できるほうが、説得力は一気に高まります。

What-If分析は、難しいプログラミングや専門的な会計知識がなくても使えるExcelの実用的な機能です。まずは基本の考え方を押さえ、売上や原価、利益の関係をExcel上で見える化することから始めましょう。

まずは利益計算表を作成しよう：売上・原価・利益率の整理方法

What-If分析を正しく使うためには、最初に利益計算表を作っておくことが重要です。Excel上で売上や原価、利益率の関係が整理されていないと、あとから条件を変えても「どの数字が何に影響しているのか」が分かりにくくなってしまいます。

まずは、以下のような項目をExcelに用意しましょう。

項目	内容	例
販売単価	商品1個あたりの販売価格	5,000円
販売数量	販売する個数	300個
売上	販売単価 × 販売数量	1,500,000円
1個あたり原価	商品1個にかかるコスト	3,000円
総原価	1個あたり原価 × 販売数量	900,000円
利益	売上 − 総原価	600,000円
利益率	利益 ÷ 売上	40％

Excelでは、入力するセルと計算するセルを分けるのがポイントです。たとえば、販売単価、販売数量、1個あたり原価は手入力する項目にします。一方で、売上、総原価、利益、利益率は数式で自動計算されるようにしておきます。

具体的には、以下のような数式を設定します。

• 売上：=販売単価のセル*販売数量のセル
• 総原価：=1個あたり原価のセル*販売数量のセル
• 利益：=売上のセル-総原価のセル
• 利益率：=利益のセル/売上のセル

利益率のセルは、Excelの表示形式を「パーセンテージ」にしておくと見やすくなります。また、金額のセルには桁区切りを設定しておくと、会議資料や上司への報告でもそのまま使いやすくなります。

ここで大切なのは、変更する可能性がある数値を分かりやすくしておくことです。たとえば販売単価や販売数量、原価のセルに色を付けておくと、あとからシミュレーションするときに操作しやすくなります。

利益計算表がきちんと作れていれば、「価格を上げたら利益はどう変わるか」「販売数が減ったら利益率はどれくらい下がるか」といった確認がスムーズになります。What-If分析は便利な機能ですが、その土台になるのは正確な計算表です。まずはシンプルで見やすい利益計算表を作成し、数字の動きが一目で分かる状態にしておきましょう。

ゴールシークで「目標利益を達成する売上」を逆算する方法

利益計算表が作成できたら、次にゴールシークを使って「目標利益を達成するには、どれくらいの売上が必要か」を逆算してみましょう。ゴールシークは、ExcelのWhat-If分析の中でも特に使いやすい機能で、目標値から必要な入力値を自動で求めてくれます。

たとえば、現在の利益が60万円だとして、「利益を100万円にするには、販売数量を何個にすればよいか？」を知りたいケースを考えます。通常であれば、販売数量を少しずつ変えながら利益が100万円になるまで試す必要があります。しかし、ゴールシークを使えばExcelが逆算してくれるため、手作業で何度も計算する必要がありません。

ゴールシークを使う前に、以下のような状態になっていることを確認しましょう。

• 利益のセルに数式が入っている
• 販売数量のセルを変更すると、売上・総原価・利益が自動で変わる
• 目標にしたい利益額が決まっている

準備ができたら、Excelで次の手順を実行します。

1. Excel上部の「データ」タブをクリックする
2. 「What-If分析」をクリックする
3. 「ゴールシーク」を選択する
4. 「数式入力セル」に、利益が計算されているセルを指定する
5. 「目標値」に、達成したい利益額を入力する
6. 「変化させるセル」に、販売数量や売上など変更したいセルを指定する
7. 「OK」をクリックする

たとえば、利益セルがB7、販売数量セルがB3、目標利益が100万円の場合は、以下のように設定します。

設定項目	入力内容
数式入力セル	B7
目標値	1000000
変化させるセル	B3

これでExcelが自動的に販売数量を調整し、利益が100万円に近づく値を算出してくれます。結果を見れば、「目標利益を達成するには、何個売る必要があるのか」がすぐに分かります。

また、販売数量ではなく販売単価を変化させるセルに指定すれば、「販売数はそのままで、いくらで売れば目標利益に届くか」を確認できます。営業目標の設定や価格改定の検討をするときに、とても便利な使い方です。

ただし、ゴールシークで変更できるセルは基本的に1つだけです。「販売単価も販売数量も同時に変えたい」といった複数条件のシミュレーションには向いていません。その場合は、次章で紹介するデータテーブルを使うと効率的です。

ゴールシークは、目標から逆算して必要な数字を出せるため、仕事の場面で非常に実用的です。「この利益を出すには、どれだけ売ればいいのか？」をすぐに数字で示せるようになると、提案資料や会議での説明にも説得力が増します。

データテーブルで「価格・販売数・原価」の変化を一括シミュレーションする方法

ゴールシークが「目標から逆算する機能」だとすると、データテーブルは「条件を変えたときの結果を一覧で比較する機能」です。利益シミュレーションでは、販売単価、販売数量、1個あたり原価を変えた場合に、利益がどのように変化するかをまとめて確認できます。

たとえば、以下のような利益計算表があるとします。

• 販売単価：B2
• 販売数量：B3
• 1個あたり原価：B5
• 利益：B7

まずは、販売単価と販売数量を変えた場合の利益を一覧にしてみましょう。Excelの空いている場所に、横方向へ販売単価、縦方向へ販売数量を入力します。

	4,500円	5,000円	5,500円
200個
300個
400個

表の左上の空白セルには、利益セルを参照する数式を入力します。たとえば利益がB7にある場合は、=B7と入力します。

次に、作成した表全体を選択し、Excel上部の「データ」タブから「What-If分析」、続いて「データテーブル」をクリックします。

設定画面では、以下のように指定します。

設定項目	指定するセル
行の代入セル	販売単価のセル（例：B2）
列の代入セル	販売数量のセル（例：B3）

これで、販売単価と販売数量の組み合わせごとの利益が一括で表示されます。「単価を上げても販売数が減ると利益はどうなるか」「数量が増えたときにどの価格帯が一番利益を出しやすいか」といった比較が、一目で分かるようになります。

原価の変化も確認したい場合は、別のデータテーブルを作成し、販売単価 × 1個あたり原価、または販売数量 × 1個あたり原価の組み合わせでシミュレーションします。たとえば、横方向に販売単価、縦方向に原価を並べれば、「仕入れ価格が上がったとき、どの販売価格なら利益を維持できるか」を確認できます。

注意点として、Excelのデータテーブルで同時に変更できる条件は基本的に最大2つです。価格、販売数、原価の3つをすべて同時に動かしたい場合は、データテーブルを複数作成するか、原価パターンごとに表を分けると分かりやすくなります。

データテーブルを使えば、複数パターンの利益を手入力で何度も計算する必要がありません。価格改定、販売計画、原価上昇への対応など、ビジネスでよくある判断をスピーディーに数字で確認できるようになります。

利益シミュレーションを仕事で活かすコツと注意点

ExcelのWhat-If分析は、操作方法を覚えるだけでも便利ですが、仕事で本当に役立てるには「数字をどう見せるか」「どこまで現実的に考えるか」が重要です。シミュレーション結果はあくまで判断材料であり、そのまま正解になるわけではありません。

まず意識したいのは、前提条件を明確にすることです。たとえば「販売単価を5,000円から5,500円に上げた場合」といっても、価格を上げても販売数量が変わらない前提なのか、販売数量が減る前提なのかで結果は大きく変わります。資料にする場合は、表の近くに次のような前提を書いておくと、上司や関係者にも伝わりやすくなります。

• 販売数量は月間300個を想定
• 1個あたり原価は3,000円で固定
• 広告費や人件費などの固定費は含めない
• 価格変更による需要の増減は別途検討

次に、現実的な範囲でシミュレーションすることも大切です。販売単価を2倍にしたり、販売数量を急に10倍にしたりすれば、Excel上では大きな利益が出るかもしれません。しかし、実際の市場や顧客の反応を考えると、非現実的な数字になってしまうことがあります。仕事で使う場合は、「少し悪い場合」「通常の場合」「うまくいった場合」のように、3パターン程度に分けると検討しやすくなります。

シナリオ	考え方
悲観シナリオ	販売数が想定より少ない、原価が上がるなど
標準シナリオ	現在の実績や平均値をもとにする
楽観シナリオ	販売数が伸びる、原価を抑えられるなど

また、利益を見るときは変動費と固定費の違いにも注意しましょう。商品の仕入れ原価のように販売数量に応じて増える費用は変動費です。一方、家賃、人件費、システム利用料などは、販売数に関係なく発生する固定費です。簡易的なシミュレーションでは原価だけを使うこともありますが、実務で利益を判断するなら、固定費を含めた営業利益ベースで見るとより正確です。

さらに、Excelファイルを共有する場合は、入力セルと計算セルを分けて管理することをおすすめします。入力するセルに色を付け、数式が入っているセルは保護しておくと、誤って計算式を消してしまうリスクを減らせます。ファイル名に日付やバージョンを入れておくことも、会議後の修正や比較に役立ちます。

最後に、シミュレーション結果はグラフや色分けで見やすくすると効果的です。たとえば利益が高いセルを緑、赤字になるセルを赤で表示すれば、どの条件が危険で、どの条件が狙い目なのかが一目で分かります。数字の羅列だけでなく、視覚的に伝える工夫をすることで、提案資料としての説得力も高まります。

What-If分析は、未来を正確に当てるための機能ではなく、複数の可能性を比較し、より良い判断をするための道具です。前提条件を整理し、現実的な範囲でシミュレーションし、分かりやすく共有する。この3つを意識すれば、Excelの利益シミュレーションは日々の業務で強力な武器になります。