Sales volume in relation to a given target can be shown by a goal achievement chart. There is another way to do this, and that is to display real sales values instead of moving the intersections of the x and y axes, using a Combo chart in which the target would be a horizontal line based on the data in the table. Also, by adding the Spin Button control, we will be able to change a goal easily, which leads to the dynamic chart.
How to show on the chart whether the goal has been achieved? If we use the Column Chart for this purpose, the simplest way is to add a line that intersects the y axis at the point that corresponds to the value of the given target. We can do this manually, as well as add a series of data that has only one value – the goal, which could be shown in the report using the Combo chart. And we can also create a goal achievement chart by moving the y axis, which will be discussed here …
The Goal Seek option allows us to easily find a value that meets the set goal. However, this is an option of modest possibilities. To find the optimal calculated solution, by setting the target value and additional parameters, use the Excel Add-in named Solver. It is a complex tool, with many possibilities, which comes up to the target by applying one of the methods of Operational Research.
Goal Seek is one of the What-If tools used to calculate how much to change an argument of a formula, or argument that indirectly affects to it, in order to get a certain result. This is a simple option that can be used, for example, to calculate the sales price of the item that is needed to reach the desired sales level. Learn about the Goal Seek option in the text that follows …