「ゴールシーク」はExcelなどの表計算ソフトで用いられる機能で、あるセルの値を特定の目標値に設定し、その目標値を達成するために別のセルの値をどのように変化させればよいかを計算します。
主な利用例
以下に、ゴールシークがよく使われている作業をいくつかリストアップします。
- 利益目標の達成: 企業はある期間に達成したい利益目標を設定することがよくあります。ゴールシークは、達成するために販売価格や販売量をどの程度変えればよいかを求めるのに役立ちます。
- 返済計画の設定: ローンや債務の返済計画を立てる際に、特定の返済期間または月々の返済額を目標に設定し、それを達成するための利息率や返済回数を求めるのにゴールシークを使用します。
- 投資目標の達成: 投資家は特定の収益目標を設定することがあります。ゴールシークは、その収益目標を達成するために必要な投資金額を求めるのに使用します。
- ブレークイーブン分析: ブレークイーブン分析(損益分岐点分析)では、費用と収益が等しくなる点を見つけるためにゴールシークが使用されます。つまり、損益分岐点（利益がゼロになる点）を計算します。
例：利益目標の達成
利益目標の達成に向けたゴールシークの利用例を考えてみましょう。例えば、ある企業が1年間で達成したい利益目標が$1,000,000であるとします。その企業は1つの製品を販売しており、販売価格、製造コスト、販売量がそれぞれ既知であるとします。
以下にそのデータを示します。
| 項目 | 値 |
|---|---|
| 販売価格 | 100 |
| 製造コスト | 50 |
| 販売量 | 5000 |
| 利益 | 250000 |
ここで利益は「(販売価格- 製造コスト) * 販売量」「=(B2-B3)*B4」という式で計算されます。この場合、目標の利益1,000,000を達成するためには販売量をどの程度増やす必要があるのかを求めたいと思います。
この時、Excelのゴールシークを使います。以下に設定の手順を示します。
- データタブの「予測・What-IF分析」グループの「ゴールシーク」を選択します。
- 「数式入力セル」には利益のセルを選択します。
- 「目標値」には目標の利益である1,000,000を入力します。
- 「変更させるセル」には販売量のセルを選択します。
これにより、達成したい利益を得るために必要な販売量が計算されます。
例：返済計画の設定
ローンや債務の返済計画におけるゴールシークの利用例を作成します。例えば、500万円の借金を持ち、毎月の返済額が15万円の返済計画を立てたとします。返済期間をどれくらいにすればよいか、または必要な利息率がどれくらいかを求めるためにゴールシークを使用します。
以下にそのデータを示します。
| 借金額（円） | 年利（%） | 返済期間（月） | 月々の返済額（円） |
|---|---|---|---|
| 5000000 | 4% | 未定 | 150000(ただし計算式で表記予定) |
この例では、500万円の借金を年利4%の利息で返済する場合を考えます。まだ返済期間と月々の返済額は未定としています。複利計算のある返済金額の算出にはPMT関数が適しています。
次に、「月々の返済額」のセルに次の式を入力します。そのままだと「#VALUE」のエラーが出ます(気になる場合は返済期間に「30」などの適当な数値を入れておく)。
= -PMT(B2/12, C2, A2)
ここでB2は年利、C2は返済期間、A2は借金額を表します。また、マイナス符号はPMT関数が返す値がマイナスであるため、正の値にするために追加しました。
ある目標の月々の返済額（例えば15万円）を達成するために必要な返済期間を求めるために、ゴールシーク機能を使用します。
- データタブの「予測」グループにある「What-If分析」ボタンをクリックし、ドロップダウンメニューから「ゴールシーク」を選択します。
- 「数式入力セル」には「月々の返済額」のセル（上記の例ではD2）を入力します。
- 「目標値」には目指す月々の返済額（例えば150000）を入力します。
- 「変更させるセル」には「返済期間」のセル（上記の例ではC2）を入力します。
これにより、「月々の返済額が15万円となる返済期間」がゴールシークによって求められます。
以上の手順により、特定の月々の返済額を達成するための返済期間をゴールシークを使用して計算することができます。
例：投資目標の達成
以下に、Excelゴールシーク機能を使用して投資目標を達成するための投資金額を計算する例を示します。
| 投資先 | 予想リターン率（%） | 投資額（円） | 収益（円） |
|---|---|---|---|
| 投資先A | 9.5 | 未定 | 未定 |
この例では、投資先Aから10%の予想リターンを得られるとします。投資金額と収益はまだ未定としています。
次に、「収益」のセルに次の式を入力します。
= B2 * C2 / 100
ここでB2は予想リターン率、C2は投資金額を表します。わかりやすくC2に「7500000」などの適当な値を入れておいても構いません。
ある目標の収益（例えば100万円）を達成するために必要な投資金額を求めるために、ゴールシーク機能を使用します。
- データタブの「予測」グループにある「What-If分析」ボタンをクリックし、ドロップダウンメニューから「ゴールシーク」を選択します。
- 「数式入力セル」には「収益」のセル（上記の例ではD2）を入力します。
- 「目標値」には目指す収益（例えば1000000）を入力します。
- 「変更させるセル」には「投資額」のセル（上記の例ではC2）を入力します。
これにより、「収益が100万円となる投資金額」がゴールシークによって求められます。
以上の手順により、特定の収益目標を達成するための投資金額をゴールシークを使用して計算することができます。
なおリターン率が「7.5」だったらどうなのか、「11.5」だったらどうなのか、を記憶させておくには「What-If分析」の「シナリオの登録と管理」を使うとよいでしょう。
例：ブレークイーブン分析(損益分岐点分析)
以下の表はある商品のブレークイーブン分析(損益分岐点分析)の例です。
| 項目 | 値 |
|---|---|
| 単価 | 300 |
| 固定費 | 100,000 |
| 変動費（単位あたり） | 150 |
| 販売量 | 未定 |
| 総費用 | =B3 + (B4 * B5) |
| 総収益 | =B2 * B5 |
| 総収益と総費用の差 | =B7 – B6 |
- まず、総費用の計算式として「固定費 + (変動費/単位 * 販売量)」を設定します。表ではB6セルに「=B3 + (B4 * B5)」を入力します。B5が「未定」ではエラーが出ますが、気になるようでしたら「200」などの適当な値を入れておいてください。
- 続いて、総収益の計算式として「単価 * 販売量」を設定します。表ではB7セルに「=B2 * B5」を入力します。
- 「総収益と総費用の差」のセル（B8セル）には「総収益 – 総費用」の計算式を入力します。「=B7 – B6」を入力します。
以上のように計算式を設定した後、ゴールシークを実行します。
- Excelの「データ」タブの「予測」グループの「What-If分析」ボタンをクリックし、「ゴールシーク」を選択します。
- 「ゴールシーク」のダイアログボックスが開くので、「数式入力セル」には「総収益と総費用の差」（B8セル）、「目標値」には0、「変更させるセル」には「販売量」（B5セル）を入力します。
以上の設定で「OK」をクリックすれば、ゴールシークが実行され、「販売量」が「総収益」と「総費用」が一致する（つまり、損益分岐点）値に更新されます(666.67個)。
コメント