18 Goal Seek Analysis

Ms.Vinodini Kapoor

1. Learning Outcome:

After completing this module the students will be able to:

• Develop a basic understanding of goal seek analysis.
• Understand the relevance of goal seek analysis in context of decision support systems.
• List the various steps involved in goal seek analysis.
• Understand the importance and benefits of goal seek analysis.
• Understanding the results of goal Seek Analysis.

2. Introduction

Processes in any organization are carried with an aim to achieve a target or a desired outcome. This target should be aligned to the corporate objectives as well as the mission statement of the organization. The mission statement refers to what the organization intends to do. The pre-defined outcome that one sets to achieve is termed as a ‘goal’. Mathematically, if we know the end result from a formula, we may use goal seek analysis to determine the input to get the required output. Taking an example of availing a home loan, the total amount to be borrowed is determined, the time period of pay back is decided, the goal seek function helps calculate the equal monthly installment to be paid. It shall also help to calculate the rate of interest the bank shall determine to fulfill the loan goal. Goal seek offers the functionality to back calculate to obtain an input corresponding to a desired output. This functionality is offered as part of many software packages such as MS Excel, MATLAB, and Office 365 for Mac etc.

Management information systems (MIS) integrate with decision support systems to capture data and analyze it for business use. Data analysis uses a number of pictorial aids like pie charts, graphs, and tables etc which facilitate decision making (Refer exhibit 2). Such analysis finds a number of applications in routine decisions related to business such as in accounting, sales, operations, production, marketing etc. Analysis pertaining to consumer behavior, purchasing patterns and transactional data can also be performed.

3. Goal Seek Analysis in context of Decision Support Systems

A decision support system (DSS) works together with the Management Information Systems (MIS) and facilitates decision making across the organizational hierarchy. The DSS supports the information system for operational, functional as well as tactical level decisions. It is able to analyze and process voluminous data to yield information which is relevant for business use. It enables to study how changes in a particular parameter helps to analyze where we are and what we intend to achieve. It is a helpful tool for strategic level decisions (exhibit 3). It is interesting to find out what lies inside a decision support system.

Main characteristics that define a decision support system are

• An interactive graphic user interface.
• Computation models and analytical techniques like what if analysis, goal seeking, sensitivity etc.
• Data is sourced from internal sources and those that are external to the organization. It helps in the essential capturing and acquisition of data.

The computation models in case of DSS are different. These are iterative in nature and built upon series of interactions with the end user. Decision makers can use these models in multiple ways to perform experiments. Among the three commonly used techniques are what-if analysis, sensitivity analysis, goal seeking.

The following case study shall provide insight as to how such modeling techniques are relevant.

Case of Flipbasket using a decision support system

Let’s refer to the case of Flipbasket.com which is an online shopping portal. Flipbasket has a huge warehouse of online products. When consumers make a purchase, like a home theater, the data from the individual’s purchase is stored and managers can analyse how certain brands are selling over others. In a situation where a Sony home theatre system was purchased, managers could take the data, compile it and pair the system with a new compatible high definition (HD) television. This helped the integrated system to sell faster. Also, since the complete system was rather expensive in comparison with traditional speakers, managers could offer discounted home theatres with every purchase of a player. This was the case when home theatres and HD-players first came out. Managers had to determine which of these two brands would triumph, and which machine should be dropped from the company’s website. Sales records and data from customer purchases were complied and managers took this information to determine what the best course of action would be. Eventually, it was the home theatre systems whose sale outnumbered the HD players. They became the standard for the ultimate movie experience. Thereafter, on account of declining sales, HD players and home theatres were no longer sold through Flipbasket.com. After studying the consumer’s purchase patterns the managers could decide what format would sell better.

Inside a decision support system lie the three different parts or methods that help to make the right decisions. These are explained in the section below.

3.1 The three parts that make up a Decision Support System

Sensitivity analysis – It is the technique to study the impact of a independent variable on the dependent variable when change occurs. Sticking with the example stated above, the case of Flipbasket, when home theatre players become cheaper due to a new technology the theatre sales are affected. Sales could stoop low due to the new technology or sales could skyrocket due to the falling price on home theatre players. Change does not just affect one element, it also affects complimentary or substitute products.

What-If analysis: It is used to determine the possible changes that can be applied on a theoretical solution. For example, say the target is to sell 500 home theatre players, within one month, over Flipbasket.com. In addition, say the aim is to lower the prices of the players by 25% so as to increase the sales and reach the quota. What-if analysis when exercised shall reveal what the possible outcomes could be. Home theater system could become more popular or less popular and sales could remain constant regardless of the given discount.

Goal-Seeking analysis: Goal seek analysis is the most critical part of a decision support system. It helps to set a target value and back calculates the input that is required to reach the target value. This can be easily implemented using the Microsoft Excel spreadsheet package (Refer Exhibit 4 & 5). An in-built functionality of the package, it is easier to feed multiple input values to a variable till the desired output is achieved (Refer Fig1.)

This analytical modeling technique has multiple applications that facilitate business decisions. It helps us answer a number of business related questions. For example, ‘how would one achieve the net profit after taxes’ by changing the prices or costs or quantities? The what-if analysis just looks at the possibilities and given scenarios. The purpose of the DSS is to compile unstructured data into useful information for business that managers can utilize and apply to organizational decision making.

Source: http://www.computerbusinessresearch.com/Home/decision-making/goal-seeking-analysis

4. Steps involved in goal seek analysis

Goal seek analysis helps to narrow down to the input values to achieve a specific output. This feature prompts the user to set the target value in the first step. Further, in the goal seek command; the spreadsheet package changes the value in one cell until the value in a second cell reaches a number that one intends to achieve. In goal seek there are three boxes to fill in. A basic example of this can be seen in Fig2.

• A dialog box that appears is shown in Fig 3. It seeks the input value where it displays “Set cell.” This is where we feed the value which we intend to set to a certain number. For example, the sale price or profit. This cell should contain a mathematical formula or function. If this is not specified, it cannot be linked to the cell you will be changing.

• The next cell specifies the ‘To value’ (Refer Fig 4). Here we enter the particular value we wish to see in that specified cell stated in the above case. (i.e. 0 if you want the loss to come out zero).

• The last cell mentions, ‘By changing cell.’ Here we specify the cell number that goal seek should change to obtain the target value. This cell should only have details of the cell id or a number and not a function. Then click ‘OK.’ At this stage, goal seek analysis yields the final answer. (Refer Fig 5)

Finally, in accordance with the answer revealed by goal seek; we obtain the required input value stated in cell B2.

4.1 Troubleshooting a goal seek analysis scenario.

There are certain key points that should be kept in mind to ensure goal seek finds the accurate solution. These can be summarized as under:

• ‘Set Cell’ should always be a mathematical formula. This should tell the goal seek method what function it has to operate upon.
• Ensure that you have set that cell to a reasonable number and not a function.
• Ensure the ‘by changing cell’ is a number or blank, and not a formula, function or cell reference.
• Be cautious that there is a link between formulas in the two cells you entered in the goal seek. The formula in the “Set Cell” cell should be entered correctly.
• During a goal seek analysis using a package like MS Excel, it uses multiple iterations to solve a problem.
• It may be possible, that the solution is not 100% correct but close to the solution.

5. The benefit and importance of goal seek analysis

The ability to back calculate is a very useful application of the goal-seek analysis. It refers to the technique of solving a problem in the opposite direction. This implies to fetch the input value that satisfies the desired output value. The value can be changed repeatedly until the designated outcome is achieved.

This tool creates two respective new columns in the data table. These help determine the possibility of a target outcome that can be achieved and the changes to be incorporated, if any. The analysis tool can consider test cases or execute the analysis over the complete data. Various benefits of the goal seek analysis are highlighted below.

• While the goal seek analysis is limited to the analysis of only one variable at a time, the solver plug-in in the software packages help analyze multiple variables. In other words, if goal seek enables to solve a problem of the type f(x) ≈ a, solver further helps you to solve equations in multiple unknowns, such as f(x1,x2,…xn) ≈ a. It also allows you to find the values which maximize or minimize the value of f(x1, x2…xn) subject to constraints.
• To achieve the target for highest sales or high customer satisfaction we use goal seek analysis. We can ascertain the max possible value of sales and eventually check the right quantity to be sold to achieve this figure. For customer satisfaction it can help determine if factors being considered are having a positive effect and are economically viable.
• Using the excel package one can use goal seek functionality to analyze a polynomial function by finding its roots. It can also be applied to problems that involve multiple iterations.
• For any function defined as ‘f(x) = a’ where ‘a’ refers to a constant value, to be able to find all possible solutions, multiple trials are needed. This is where goal seek analysis is useful.
• Goal Seek analysis can be used to determine breakeven point where an entity makes no profit and no loss.
• Hence, goal seek can be useful in a number of ways which help make the analyses less cumbersome. It can help in estimating future values and show the required changes.

Requirements of Goal Seek Analysis Function.

This technique uses the principle of logistic regression. This means that it can process either numeric or discrete values. In other words, it can process a binary or yes/no variable to other variables of interest. This statistical method helps in determining the contribution of multiple factors to a pair of outcomes. The effect of every input on the output is measured, and then various inputs are weighted in the finished model. The logistic regression algorithm has numerous industry vide applications. From economics to healthcare and diagnostics it helps to analyze situations that are dependent on variables that take binary values. You can achieve better results if you select columns for analysis that contain useful information. In other words, if the purpose of goal seeking is to determine factors that encourage iphone purchases, you should only include customers who have shown interest to purchase the iphone.

6.  Understanding the results of Goal Seek Analysis A goal seek analysis tool kit enables

• The first step is to create a structure in the data mining framework to store data sets.
• The next step is to run an algorithm based on binary coded data using the logistic regression mining technique.
• Lastly a prediction model for each of the values specified.

The results can be understood better with test scenarios in each case. The result gets highlighted when a successful solution is found. It further gives a recommendation to incorporate the required change.

Source: https://msdn.microsoft.com/en-in/library/dn282387.aspx

Limitations of Goal Seek Analysis

Apart from a number of applications, goal seek analysis has certain limitations that should be taken into consideration. Some of them can be summarized as:

• This technique is restricted to one variable. It directly cannot consider two input values changing at the same time.
• Even in cases of multiple solutions that could be possible, this technique gives one possible outcome.
• This tends to work under the condition when the ‘set cell’ value converges towards the ‘to value’ in each iteration
• The ‘set cell’ and ‘by changing cell’ values are required to be together on the similar sheet.

Example: Case of Attainable Score

Considering a situation of Mr A. enrolled in a class of advance mathematics. He has secured a score of 65, and needs at least a 70 to pass the class. Luckily, there is one final assignment that might be able to raise the average score. Goal Seek analysis can yield what grade you need on the final assignment to pass the class.

As per Fig8, the assessment of the previous four assignments is listed. Despite the fifth one being unknown, a function can be specified—that calculates the final grade. The function, AVERAGE (B2:B6) yields the average value. The goal-seek analysis reveals the value of B6 to show us the minimum grade we’ll need to make on that assignment.

• The first step is to choose the particular cell whose value shall undergo a change. Apart from this there should be a cell that already contains a function. As seen in Fig 8, the cell id, B7 contains the formula =AVERAGE (B2:B6).
• In the next step, we go to the data tab and choose the what-if analysis command. From here we select goal seek analysis.
• A dialog box will appear with three fields as shown in Fig 9.Set cell: It reflects the cell id of the designated cell.

To value: This refers to the targeted outcome. Here, we have entered 70 because that is required to clear the semester.

By changing cell: This again consists of the cell reference. In this example we have entered B6 to calculate the grade to be secured on the final assignment.

4. When you’re done, click OK.

5. A dialog box appears which suggests if goal seek was in a position to find a solution. Click OK.

6. The result will appear in the specified cell as exhibited in Fig 10. In this case the goal – seek functionality reveals that the marks to be secured are at least 90 on the final assignment to earn a passing grade.

7. Summary

In a goal seeking analysis we first estimate the intended outcome. The next step ahead of the decision maker is specifying the target values. The decision maker takes into consideration various constraints and limiting conditions while trying to achieve an optimum value. The decision maker can use this technique to work with decision variables and allocate resources. This helps to find ways to improve upon solutions to improve profitability of business. The goal seek is a useful concept in solving any mathematical computation which involves a single variable. It is one of the most efficient methods when it is difficult to derive a formula to calculate unknown value. Goals seek analysis finds applications in different fields such as loan payment, scientific experiments, statistics etc. The only drawback in case of goal seek analysis is that the study is limited to a single variable. In such situations, where there are complex problems dealing with more than one variable and other limiting conditions, the best solution is to use MS Excel’s solver plug-in instead of goal-seek. This helps to solve complex problems where optimization is needed. Solver uses the iterative mechanism to solve problems and derive solutions.