Excelの機能の長いリストは、Microsoftのスプレッドシートアプリケーションの最も魅力的な機能の1つですが、これらの機能を強化する十分に活用されていない宝石がいくつかあります。見過ごされがちなツールの1つは、What-If分析です。
ExcelのWhat-If分析(What-If Analysis)ツールは、3つの主要コンポーネントに分かれています。ここで説明する部分は、関数から逆方向に作業し、セル内の数式から目的の出力を取得するために必要な入力を決定できる強力なゴールシーク機能です。(Goal Seek)ExcelのWhat-If分析ゴールシーク(What-If Analysis Goal Seek)ツールの使用方法を学ぶために読んでください。
Excelのゴールシークツールの例
住宅を購入するために住宅ローンを借りたいと考えており、ローンの利率が年間の支払いにどのように影響するかを心配しているとします。住宅ローンの金額は$100,000で、30年間でローンを返済します。
ExcelのPMT関数を使用すると、利率が0％の場合の年間支払い額を簡単に把握できます。スプレッドシートは次のようになります。
A2のセルは年利を表し、B2のセルは年単位のローンの長さであり、C2のセルは住宅ローンの金額です。D2の式は次のとおりです。
=PMT(A2,B2,C2)
そして、0％の利子で30年の$100,000の住宅ローンの年間支払いを表します。Excelは支払いがあなたの財政状態からの負のキャッシュフローであると想定しているため、D2の数値は負であることに注意してください。(Notice)
残念ながら、住宅ローンの貸し手は、0％の利子で$100,000を貸し出すことはありません。いくつ(Suppose)かの計算を行って、住宅ローンの支払いで年間6,000ドルを返済する余裕があることがわかったとします。あなたは今、あなたが年間6,000ドル以上を支払うことにならないようにするためにあなたがローンのために取ることができる最高の金利は何であるか疑問に思っています。
この状況の多くの人々は、D2の数字が約$ 6,000に達するまで、セルA2に数字を入力し始めるだけです。ただし、What-If Analysisゴールシーク(Analysis Goal Seek)ツールを使用すると、 Excelに作業を任せることができます。(Excel)基本的に、ExcelをD4の結果から、最大支払い額である6,000ドルを満たす金利に到達するまで逆方向に機能させます。
リボンの[(Ribbon)データ(Data)]タブをクリックし、[データツール]セクションの[仮定の(Data Tools)分析(What-If Analysis)]ボタンを見つけることから始めます。[ What-If Analysis ]ボタンをクリックして、メニューから[GoalSeek]を選択し(Goal Seek)ます。
Excelは小さなウィンドウを開き、3つの変数のみを入力するように求めます。Set Cell変数は、数式を含むセルである必要があります。ここでの例では、D2です。To Value変数は、分析の最後にD2のセルを配置する量です。
私たちにとっては-6,000です。Excelは支払いをマイナスのキャッシュフローと見なしていることを忘れないでください。[セルを変更する](By Changing Cell)変数は、Excelで検索する利率であり、100,000ドルの住宅ローンの費用は年間6,000ドルになります。したがって、セルA2を使用します。
[ OK ]ボタンをクリックすると、反復が最終的に最終的な数値に収束するまで、 (OK)Excelがそれぞれのセルの数値の束を点滅させることに気付く場合があります。この場合、A2のセルは約4.31％になるはずです。
この分析によると、30年間の$ 100,000の住宅ローンに年間6,000ドルを超えて支出しないためには、4.31％以下でローンを確保する必要があります。what-if分析を継続したい場合は、数値と変数のさまざまな組み合わせを試して、住宅ローンの良好な金利を確保しようとするときに利用できるオプションを調べることができます。
ExcelのWhat-If分析ゴールシーク(What-If Analysis Goal Seek)ツールは、一般的なスプレッドシートにあるさまざまな関数や数式を強力に補完します。セル内の数式の結果から逆方向に作業することで、計算のさまざまな変数をより明確に調べることができます。
Using Excel’s What-If Analysis Goal Seek Tool
Although Excel’s long list of functions is one of the most enticing features of Miсrosoft’s spreadsheet application, there a few underυtіlizеd gems that enhance these functions. One often-overlooked tool is the What-If Analysis.
Excel’s What-If Analysis tool is broken down into three main components. The part discussed here is the powerful Goal Seek feature that lets you work backwards from a function and determine the inputs necessary to get the desired output from a formula in a cell. Read on to learn how to use Excel’s What-If Analysis Goal Seek tool.
Excel’s Goal Seek Tool Example
Suppose that you want to take out a mortgage loan to buy a house and you are concerned about how the interest rate on the loan will affect the yearly payments. The amount of the mortgage is $100,000 and you will pay back the loan over the course of 30 years.
Using Excel’s PMT function, you can easily figure out what the yearly payments would be if the interest rate were 0%. The spreadsheet would likely look something like this:
The cell at A2 represents the yearly interest rate, the cell at B2 is the length of the loan in years, and the cell at C2 is the amount of the mortgage loan. The formula in D2 is:
=PMT(A2,B2,C2)
and represents the yearly payments of a 30-year, $100,000 mortgage at 0% interest. Notice that the figure in D2 is negative since Excel assumes that the payments are a negative cash flow from your financial position.
Unfortunately, no mortgage lender is going to lend you $100,000 at 0% interest. Suppose you do some figuring and find out that you can afford to pay back $6,000 per year in mortgage payments. You are now wondering what is the highest interest rate you can take on for the loan to make sure you don’t end up paying more than $6,000 per year.
Many people in this situation would simply start typing numbers in cell A2 until the figure in D2 reached approximately $6,000. However, you can make Excel do the work for you by using the What-If Analysis Goal Seek tool. Essentially, you will make Excel work backwards from the result in D4 until it arrives at an interest rate that satisfies your maximum payout of $6,000.
Begin by clicking on the Data tab on the Ribbon and locating the What-If Analysis button in the Data Tools section. Click on the What-If Analysis button and choose Goal Seek from the menu.
Excel opens up a small window and asks you to input only three variables. The Set Cell variable must be a cell that contains a formula. In our example here, it is D2. The To Value variable is the amount you want the cell at D2 to be at the end of the analysis.
For us, it is -6,000. Remember that Excel sees payments as a negative cash flow. The By Changing Cell variable is the interest rate you want Excel to find for you so that the $100,000 mortgage will cost you only $6,000 per year. So, use cell A2.
Click the OK button and you may notice that Excel flashes a bunch of numbers in the respective cells until the iterations finally converge on a final number. In our case, the cell at A2 should now read about 4.31%.
This analysis tells us that in order not to spend more than $6,000 per year on a 30-year, $100,000 mortgage, you need to secure the loan at no more than 4.31%. If you want to continue doing what-if analyses, you can try different combinations of numbers and variables to explore the options you have when trying to secure a good interest rate on a mortgage.
Excel’s What-If Analysis Goal Seek tool is a powerful complement to the various functions and formulas found in the typical spreadsheet. By working backwards from the results of a formula in a cell, you can explore the different variables in your calculations more clearly.
