For example, to automatically calculate the data in B2 and C2 and produce the final result in D2, click the D2 cell and enter: =B2*C2 and press Enter. So we entered =SUM(D2:D5) in cell D6.Īdditionally, you’ll want to automate the values received in cells D2-D5. In D6, we are using the =SUM() function to calculate the sum of cells D2, D3, D4, and D5. Here’s what ours looks like in this example.īefore we go further, there are a few basic Excel functions that we are using in this example. Open the Excel sheet that contains your data. So, we know the desired outcome, and we have all of the data except one value: Q4.īased on the data we currently have, we can use the Goal Seek function to determine what the net profit of Q4 needs to be in order to achieve the set goal. To repaint the scenario, the company set a net profit goal of $2,000,000 USD and Q3 just concluded.
#USE GOAL SEEK EXCEL 2013 HOW TO#
In this how-to guide, we’ll show you how to use the Goal Seek function in Microsoft Excel with our previously-mentioned business scenario. How to use Goal Seek in Excel: A step-by-step tutorial You can use Goal Seek to determine the interest rate you need to secure in order to stay within your monthly budget. You know the amount you need, you know how many months you want to pay it back, and you know the amount you can afford to pay back each month. The most widely used example of a personal use case of Goal Seek is the loan example. You can use Goal Seek to determine what score you need to make on the final exam to receive the desired final score. Or, perhaps you have a B and want to raise it to an A. Perhaps you have a failing grade, and you want to determine what score you need on your final exam to reach that passing threshold. We’ve all had that one subject in school that, no matter how hard we studied, we just weren’t very savvy in. You can use Goal Seek to determine what net profit you need to close Q4 with in order to reach your annual goal of $2,000,000. Your current net profit for the year is $1,518,750. Q3 just closed, so you have the net profit data for Q1, Q2, and Q3. Let’s say your company has set a goal of $2,000,000 USD net profit for the current year. Here are few hypothetical situations in which the Goal Seek function would come in handy. There are any number of situations in which you may want to consider using Goal Seek. However, if you want to determine what the interest rate you need is and the amount of time required to pay the loan back in order to keep the monthly payment at your desired amount, you would need to use Solver. If there are multiple input values you want to define, you can use the Solver add-in instead.įor example, if you want to take out a loan and you know the output value, which is how much you can afford to pay each month, and you know the input values of (1) how much money you need to borrow and (2) how many months you require to pay the loan back, you can use the Goal Seek function to determine (3) the interest rate needed to keep the monthly payment within your budget. One caveat that you need to be aware of-the Goal Seek function only works if there is one input value you want to define. The Goal Seek function is used to understand what input value is needed in order to realistically achieve your set goal. All three tools are used in “what-if” scenarios to understand how changing certain input values will affect the output value, or the desired outcome.
Microsoft Excel comes with three built-in What-If Analysis tools: Data Tables, Scenarios, and Goal Seek. What is the Goal Seek function in Microsoft Excel?
0 kommentar(er)
