Excel의 긴 기능 목록은 Microsoft 스프레드시트 응용 프로그램의 가장 매력적인 기능 중 하나이지만 이러한 기능을 향상시키는 몇 가지 활용도가 낮은 보석이 있습니다. 자주 간과되는 도구 중 하나는 What-If 분석입니다.
Excel의 What-If 분석(What-If Analysis) 도구는 세 가지 주요 구성 요소로 나뉩니다. 여기에서 설명하는 부분은 함수에서 역방향으로 작업하고 셀의 수식에서 원하는 출력을 얻는 데 필요한 입력을 결정할 수 있는 강력한 목표 찾기(Goal Seek) 기능입니다. Excel의 What-If 분석 목표 찾기(What-If Analysis Goal Seek) 도구를 사용하는 방법을 알아보려면 계속 읽으십시오.
Excel의 목표 찾기 도구 예
주택 구입을 위해 모기지론을 받고 대출 금리가 연간 지불액에 어떤 영향을 미칠지 걱정한다고 가정해 보겠습니다. 모기지 금액은 $100,000이며 30년 동안 대출금을 상환하게 됩니다.
엑셀의 PMT 기능을 이용하면 이자율이 0%일 때 연간 납입금이 얼마인지 쉽게 알 수 있습니다. 스프레드시트는 다음과 같을 것입니다.
A2의 셀은 연간 이자율을 나타내고, B2의 셀은 대출 기간(년), C2의 셀은 모기지 대출 금액을 나타냅니다. D2의 공식은 다음과 같습니다.
=PMT(A2,B2,C2)
그리고 30년 만기 $100,000 모기지의 연간 지불액을 0%의 이자로 나타냅니다. Excel(Notice) 에서는 지불이 재무 상태에서 발생하는 음의 현금 흐름이라고 가정 하므로(Excel) D2의 수치는 음수 입니다.
불행히도, 어떤 모기지 대출 기관도 0%의 이자로 $100,000를 빌려주지 않을 것입니다. 계산을 하고 모기지 상환금으로 연간 6,000달러를 상환할 여유가 있다는 것을 알게 되었다고 가정해 보겠습니다. (Suppose)이제 연간 $6,000 이상을 지불하지 않도록 하기 위해 대출에 대해 취할 수 있는 가장 높은 이자율이 얼마인지 궁금합니다.
이 상황에 있는 많은 사람들은 D2의 수치가 약 $6,000에 도달할 때까지 단순히 A2 셀에 숫자를 입력하기 시작합니다. 그러나 What-If 분석 목표 찾기(Analysis Goal Seek) 도구 를 사용하여 Excel 에서 작업을 수행하도록 할 수 있습니다. 기본적으로 Excel 은 최대 지불금 $6,000를 충족하는 이자율에 도달할 때까지 D4의 결과에서 거꾸로 작동합니다.
리본 에서 (Ribbon)데이터(Data) 탭을 클릭 하고 데이터 도구(Data Tools) 섹션 에서 가상 분석(What-If Analysis) 버튼을 찾아 시작합니다 . What-If 분석(What-If Analysis) 버튼을 클릭하고 메뉴 에서 목표 찾기(Goal Seek) 를 선택합니다.
Excel은 작은 창을 열고 세 개의 변수만 입력하도록 요청합니다. 셀 설정(Set Cell) 변수는 수식 이 포함된 셀이어야 합니다. 이 예에서는 D2 입니다. To Value 변수 는 D2 에 있는 셀이 분석 끝에 있기 를 원하는 양 입니다.
우리에게는 -6,000 입니다. Excel 은 지불을 음수 현금 흐름으로 간주합니다 . (Excel)셀 변경(By Changing Cell) 에 따른 변수는 Excel에서 찾기를 원하는 이자율로, $100,000 모기지가 연간 $6,000만 소요되도록 합니다. 따라서 A2(A2) 셀을 사용 하십시오.
확인(OK) 버튼을 클릭 하면 반복이 최종적으로 최종 숫자로 수렴될 때까지 Excel 이 각 셀에서 많은 숫자를 깜박이는 것을 알 수 있습니다. 우리의 경우 A2의 셀은 이제 약 4.31%를 읽어야 합니다.
이 분석은 30년, $100,000 모기지에 연간 $6,000 이상을 쓰지 않으려면 4.31% 이하로 대출을 확보해야 함을 알려줍니다. 가정 분석을 계속하려면 다양한 숫자와 변수 조합을 시도하여 모기지론에 대한 좋은 이자율을 확보하려고 할 때 선택할 수 있는 옵션을 탐색할 수 있습니다.
Excel의 What-If 분석 목표 찾기(What-If Analysis Goal Seek) 도구는 일반적인 스프레드시트에서 볼 수 있는 다양한 기능과 공식을 강력하게 보완합니다. 셀의 수식 결과에서 거꾸로 작업하면 계산의 다양한 변수를 보다 명확하게 탐색할 수 있습니다.
Using Excel’s What-If Analysis Goal Seek Tool
Although Excel’s long liѕt оf functions is one of the mоst enticing featυres of Microsoft’s spreadsheet аpplicatіon, there a few underutilized gems thаt enhance these funсtions. 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.
