What is What-If Analysis?
What-if analysis is a kind of analysis that lets you experiment with different variables, values, or scenarios in order to learn how any changes would influence the projected outcome of a situation. Also known as a sensitivity analysis, what-if analysis is one of Excel’s most powerful, although least understood, features. One of the main benefits of working with this tool is that it provides users with a way to see the ramifications of testing different scenarios and changing variables, but doesn’t require them to make changes to the actual data.
One example of applying what-if analysis would be if a shop owner wanted to know if they would be able to make a greater profit by increasing the price of items, or if they could increase revenue by running a sale to sell additional items. By applying what-if analysis to both of these sales scenarios, the shop owner could figure out how much either of these changes may contribute to the desired outcome of making more money.
Using What-If Analysis for Goal Seek in Excel
Microsoft Excel offers three built-in tools for what-if analysis: Scenarios, Data Tables, and Goal Seek. Each relies on “what-if” scenarios for calculating how altering input values will alter the outcome. Excel’s Goal Seek function provides users with a way to solve for a specific output by altering one of the driving assumptions. By applying a trial-and-error approach of sorts, this Excel function back-solves a problem by offering a series of guesses until it eventually hits on the answer.
Goal Seek offers a sensitivity analysis tool that is helpful in instances where you are aware of the one outcome you want to achieve. Excel can then mathematically adjust one of the equation’s variables to help you arrive at this outcome. However, Goal Seek software such as Excel only works when there’s just one input value. This function is especially helpful for performing financial calculations, such as if the Excel user needs to figure out the interest rate a borrower has to be approved for (input) if the borrower only knows how much he can afford to pay in a given month (output).
There are many benefits to applying Goal Seek to data. If professionals were to calculate all equations manually rather than applying this tool, it would require a significant amount of time and effort. Goal Seek can be a money-saving tool for businesses because it provides insights into how much of a specific item is required. If, for example, Goal Seek finds that you must sell 500 items in June to meet profit expectations, you can use this information to order 500 items rather than wasting money over-ordering.
Real-World Examples of Goal Seek in Excel
Excel’s Goal Seek tool is often used to perform sensitivity analysis in the business, management, or financial sectors. The following are a few examples of how the Goal Seek function in Excel can be applied to real-world scenarios:
- Calculating the number of votes required to win an election. If a candidate is running for a school board position in a local election and needs two-thirds of the votes to win, and currently has 256 out of 850 voters, Goal Seek can be applied to figure out how many votes they still need to win the election.
- Finding the passing score for an exam. In school, a student must take three exams and earn an average score of 75% to pass the course. If all exams are weighted equally, and the student has already completed two exams, what score must they earn on the remaining exam to pass the class? Goal Seek can find the answer.
- Calculating the necessary sales numbers to yield a desired annual profit. Goal Seek can help store owners calculate how much of a product they need to sell during a particular timeframe to reach a $50,000 annual net profit.
Tips for using Goal Seek
The following tips can help you get the most out of the Goal Seek function:
- Because Excel overwrites previous data, when performing Goal Seek, it’s a good idea to do so on a copy of your data. In addition, making a note on the copied data that it was found using Goal Seek can help to avoid confusing it with other data.
- Once you’ve confirmed a value change and pressed “OK,” you may decide that you’d also like to delete that cell’s Goal Seek value. To undo Goal Seek, you can either click on “Ctrl + Z” to undo the action or select the back arrow from the top of the window.
- If Goal Seek isn’t able to arrive at a solution after you’ve made changes to the parameters, it’s possible to troubleshoot. In order to do so, check that the “Set cell” text box has the cell with the formula contained in it.
- In instances when you may need to change a value to a percentage, you can do so by highlighting the cell that contains the Goal Seek value and choosing the percent sign from the “Home” tab.
- Goal Seek offers an approximate value. However, to arrive at a more precise solution, you can change the iteration setting so that value will carry out to multiple decimal places.
Uncertainty in the financial or business sector can have serious repercussions for stakeholders. With the help of what-if analysis and Excel’s Goal Seek function, you can now be in control of variables and better meet your professional goals by making more informed decisions.
Learn More about Microsoft Excel by Enrolling in Hands-On Classes
A great way to learn more about Microsoft Excel’s various tools and functions is to enroll in one of Noble Desktop’s Excel courses in NYC and live online. Courses are available for those who are new to Excel, as well as learners who regularly work with this spreadsheet application and wish to brush up on their skills. In addition, there are also in-person and live online Excel courses available through Noble Desktop or one of its affiliate schools.
Noble Desktop’s Excel Bootcamp provides a great option for those who want to master core Excel concepts, such as creating PivotTables or macros. Students who enroll in this rigorous, 21-hour course can elect to study in person in Manhattan or learn in the live online environment. This small class comes with the option of a free retake and covers a variety of Excel concepts applicable to the business world.
Those interested in studying Excel close to home can also browse over 280 in-person and virtual Excel classes to find nearby study options. Courses are available that vary in duration from three hours to 24 weeks, and range in price from $110-$8,749.