Sometimes, a Financial Analyst encounters situations in which she needs to back-solve a formula for a specific input value that drives a predefined output. Perhaps as part of what-if analysis, she needs to find what Direct Cost value is desired to reach a specific Profit Margin.
In simple formulas, such as if Y=3X, what is the value of X for Y=12, it would take no time to find X value. But in complex formulas when the function involves several independent variables (X’s), and for multiple scenarios sought, back-solving the equation would be a tedious task.
Luckily, Excel provides the Goal Seek wizard as part of the What-If Analysis group to solve for a desired output by changing an input that drives it.
In the example presented in the movie, Cost of Sales is a component that drives the Profit% at the bottom. Using Goal Seek, we can let Excel run iterations to reach a desired Profit% value by changing the Cost of Sales in the formula.
