Page 220 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

Introduction to What-If Analysis
By using What-If Analysis tools in Excel, you can use several different sets of values in one or more formulas to
explore all the various results.
For example, you can do what-If Analysis to build two budgets that each assumes a certain level of revenue. Or,
you can specify a result that you want a formula to produce, and then determine what sets of values will produce
that result. Excel provides several different tools to help you perform the type of analysis that fits your needs.
How to use Goal Seek in Excel for What-If analysis
What-If Analysis is one of the most powerful Excel features and one of the least understood. In most general
terms, What-If Analysis allows you to test out various scenarios and determine a range of possible outcomes. In
other words, it enables you to see the impact of making a certain change without changing the real data. In this
particular tutorial, we will focus on one of Excel's What-If Analysis tools - Goal Seek.
What is Goal Seek in Excel?
Goal Seek is Excel's built-in What-If Analysis tool that shows how one value in a formula impacts another. More
precisely, it determines what value you should enter in an input cell to get the desired result in a formula cell.
The best thing about Excel Goal Seek is that it performs all calculations behind the scenes, and you are only
asked to specify these three parameters:
• Formula cell
• Target/desired value
• The cell to change in order to achieve the target
The Goal Seek tool is especially useful for doing sensitivity analysis in financial modelling and is widely used by
management majors and business owner. But there are many other uses that may prove helpful to you.
For instance, Goal Seek can tell you how much sales you have to make in a certain period to reach $100,000
annual net profit (example 1). Or, what score you must achieve for your last exam to receive an overall passing
score of 70% (example 2). Or, how many votes you need to get in order to win the election (example 3).
On the whole, whenever you want a formula to return a specific result but are not sure what input value within the
formula to adjust to get that result, stop guessing and use the Excel Goal Seek function!
Note. Goal Seek can process only one input value at a time. If you are working on an advanced business model
with multiple input values, use the Solver add-in to find the optimal solution.

How to use Goal Seek in Excel
The purpose of this section is to walk you through how to use the Goal Seek function. So, we'll be working with
a very simple data set:
Data set

The above table indicates that if you sell 100 items at $5 each, minus the 10% commission, you will make $450.
The question is: How many items do you have to sell to make $1,000?
Let's see how to find the answer with Goal Seek
1 Set up your data so that you have a formula cell and a changing cell dependent on the formula cell.

2 Go to the Data tab > Forecast group, click the what if Analysis button, and select Goal Seek…

207

CITS : IT&ITES - Computer Software Application - Lesson 63 - 77