krotrt.blogg.se - What if analysis excel 2010 goal seek

WHAT IF ANALYSIS EXCEL 2010 GOAL SEEK HOW TO

In this case we see that we need to sell 172 copies to get $5,000 in revenue.Īctually the number in the cell is 172.41, but it is rounded in the screenshot above. Press OK, and we are done! Excel did the math for us, and automatically updated the spreadsheet with the correct answer. By changing: the input we want to change, that's the "quantity sold" in C3.Set cell: the cell we want to set, that's the "revenue" in C4.There select the "goal seek" option.Ī popup will appear that you need to fill like this: Select the cell containing the revenue, and in the "data" tab click on the "what if analysis" button.

WHAT IF ANALYSIS EXCEL 2010 GOAL SEEK HOW TO

Since this is a tutorial about goal seek, let's see how to do the last option -) Goal Seek Example

Use goal seek to get the result almost instantaneously.

Try different values of "quantity sold" manually in Excel until you find the correct answer.

Do some math and figure out the answer by yourself.

How many books should we sell to get that result? There are 3 ways to answer that question: Let's say we want to get exactly $5,000 in revenue from the book sales. If we sell 100 books at $29 each, we will make $2,900. Here's a table with 2 inputs (book price and quantity sold), and a formula (revenue = price * quantity). To use the goal seek feature we will need some data first. When you don't know how to change the formula's input to get that number, you should use goal seek. Sometimes you'll have a formula with a specific goal in mind, like "I want the result to be exactly 42". And the "goal seek analysis" is one of them. There are some Microsoft Excel features that are awesome but somewhat hidden. And the Solver add-in will help you optimize the model if you give it the right rules to do so.the help you optimize you model if you give it the right rules to do so.Clear Excel Tutorials home about Excel: The What If Analysis With Goal Seek The scenario manager can help you view the results of many cells that you have changed and compare them to other scenarios. For example, a Data Table can give you results by changing a maximum of two variables.

Excel offers other options to change many cells to complete the analysis you seek. Another limitation for this command is that you can change the content of a single cell. There may be some limitations that would prohibit Excel from reaching the goal you seek. It always depends on the way you created the model and on the conditions you decided to use. But that could not be the case, the next time, you use this option. It's the cell you allow Excel to change so that you can reach your goal.įor this example, goal seek was able to reach the desired goal. In the By changing cell box, select the B21 cell. It's the cell with the value you want to reach. In the Set cell box, select the D19 cell. In other word, what would have to be the growth rate (B21) to reach a quarterly profit (D19) of 50 000 $? For this example, the cell that we will allow Excel to change is the growth rate witch is cell B21. But this option can only let you change the content of one cell. You want to know what growth rate is needed to reach 50 000 $ for the same period.

The content of the D19 cell is 40 031, 25 $. This model, such as it's, shows a profit for the trimester. The values for the months of February and March are increased according to that rate. The only variable in this model is the growth rate located in the B21 cell. This is the model that will be used for most of the Excel exercise’s on this website. For example, you tax rate may not change for many years and suddenly increase (rarely decrease). You want to try simulating possibilities by changing some of these values. Variables are the values that can change at any time. In a model, there are two types of values: variables and constant. To follow this exercise, you can use the file excel2007-2010-exercises.xlsx on the demonstrations files web page. You can use this option to tell it your goal and let it change one cell in your model to acheive it. That's where Excel's Goal Seek comes into play. Specificly, you want to know what you have to do to reach your goal. You've completed your business model and want to try out some "What if" possibilities.