Class 12: Data
Analysis Tools: Solver and Goal Seek
Objective:
In this class, you
will learn how to use two important tools in Excel—Goal Seek and Solver—to
help you analyze data and find the best solutions to problems.
Topics:
1. Using Goal Seek
to Find Desired Results
Goal Seek is a feature in Excel that helps you find the
input value you need to achieve a specific result in a formula. It’s useful
when you know the result you want but not the value needed to get there.
How to Use Goal
Seek:
Step-by-Step
Instructions:
- Set Up Your Data:
- First, create a simple Excel spreadsheet.
- For example, in Cell A1, enter 100
(this represents your sales).
- In Cell B1, enter 20 (this
represents your costs).
- In Cell C1, enter the formula to
calculate profit: =A1-B1. This means profit equals sales minus costs.
|
A |
B |
C |
|
Sales |
Cost |
Profit |
|
100 |
20 |
=A1-B1 |
- Open Goal Seek:
- Go to the Data tab on the Excel
Ribbon.
- In the Forecast group, click on What-If
Analysis.
- From the dropdown, select Goal Seek.
- Set Up Goal Seek:
- In the Goal Seek dialog box, fill in the
following:
- Set cell: Click on the box and select Cell C1
(the profit cell).
- To value: Enter the desired profit amount (e.g.,
enter 50).
- By changing cell: Click on the box and select Cell A1
(the sales cell).
(Note: replace with
actual screenshot)
- Run Goal Seek:
- Click the OK button. Excel will
calculate the sales amount you need to achieve a profit of 50.
- A message box will appear telling you
whether it found a solution.
- Check the Results:
- Look at Cell A1. It should show
the sales value needed to achieve your target profit.
2. Solver Add-in:
Setting Up and Solving Optimization Problems
Solver is another powerful tool in Excel that helps
you find the best solution to problems with multiple variables. It’s useful for
maximizing profits, minimizing costs, or meeting certain constraints.
How to Use Solver:
Step-by-Step
Instructions:
- Enable the Solver Add-in:
- Open Excel and go to File > Options.
- Click on Add-ins.
- In the Manage box, select Excel
Add-ins and click Go.
- Check the box for Solver Add-in
and click OK.
- Set Up Your Model:
- Create a new spreadsheet where you can
define your problem.
- For example, let’s say you want to
maximize profit by adjusting production quantities of two products (A and
B).
- In Cell A1, enter the quantity of
Product A produced.
- In Cell A2, enter the quantity of
Product B produced.
- In Cell B1, enter the formula for
total profit: =A1*10 + A2*15 (where 10 and 15 are the profit per unit of
Products A and B, respectively).
|
A |
B |
|
Product A |
Profit |
|
0 |
=A110 + A215 |
|
Product B |
|
|
0 |
- Open Solver:
- Go to the Data tab, and in the Analysis
group, click on Solver.
- Set Up Solver Parameters:
- In the Solver Parameters dialog box, fill
in the following:
- Set Objective: Select Cell B1 (the profit
cell).
- To: Choose Max (to maximize profit).
- By Changing Variable Cells: Select Cells A1
(the quantities of
Products A and B).
- Add Constraints (if needed):
- Click the Add button to set any
limits.
- For example, if you can only produce a
maximum of 100 units of Product A and 150 units of Product B, set
constraints as:
- Cell A1 <= 100
- Cell A2 <= 150
- Run Solver:
- Click the Solve button. Excel will
find the optimal number of products to produce to maximize profit.
- A message will appear telling you the
solution found.
- Review the Results:
- Check the quantities in Cells A1
and A2. These values will show you how many of each product you
should produce for maximum profit.
Exercise:
- Use Goal Seek:
- Set up a profit calculation as shown
above.
- Use Goal Seek to find how much sales you
need to achieve a profit of $80.
- Use Solver:
- Set up the profit calculation model with
two products.
- Use Solver to find the optimal production
quantities of Products A and B to maximize profit, given their respective
limits.
