Tools From Advanced Excel Courses: Goal Seek and Solver

Excel has a number of powerful tools for numerical analysis that can reduce complicated problems into fairly simple answers. Two of these that are covered in advanced Excel courses are Goal Seek and Solver.

Goal Seek: Looking For a Specific Answer

Goal Seek is the tool to use when you know the answer to an equation but don't know the input values to put out that answer.

Consider a simple example. You sell 100 units of a product per week for $10 each, giving you weekly revenue of $1,000. You want to increase your revenue to $1,200 per week while keeping the price constant. You would put this information into your spreadsheet then use Goal Seek to vary production in order to produce the desired revenue, revealing that you need to increase production to 120 units per week.

That's a deliberately trivial example that doesn't actually require this tool, but it illustrates how Goal Seek works. An advanced Excel course would use a more involved example such as air resistance.

A moving object's acceleration is affected by air resistance which in turn is affected by velocity which in turn is affect by acceleration. The problem of how much acceleration is required to accelerate a body to a specific speed in a certain time involves more than simple division. Goal Seek would iterate through the formula, trying different acceleration values until the desired time was reached.

Solver: Optimizing Your Objective

Sometimes the user doesn't need a specific answer, but rather the best answer. Solver is the tool introduced in advanced Excel courses that is able to minimize or maximize an objective.

Let's say your company produces a popular toy in three factories. Each factory has different production and distribution costs as well as production times and capacities. Christmas is approaching and you need to know how to produce 10,000 units within the next 30 days at the lowest cost.

Or perhaps your organization is considering several projects over the next year. Current staffing won't allow all projects to be completed, so which ones would provide the best return on the investment of employee time?

Problems for Solver have multiple inputs to vary and those inputs are constrained to some range of values. You can't expect your employees to put in 100 hours per week nor can you expect them to be satisfied putting in only 5 hours. The factories can only produce so many toys and no more.

Once you define the problem, Solver varies all inputs to find the optimal result. This result is going to either be the minimum, as in production costs, or the maximum, which would be the goal for return on investment.

Learning about these two tools in advanced Excel courses gives a new range of numeric analysis options to your employees, and that translates to better business decisions.