Two Microsoft Excel tools

Sponsored Links

we will discuss two Microsoft Excel tools: 

  • GOAL SEEK
  • SOLVER

At the end we will then demonstrate the power of these function.

Sponsored Links

GOAL SEEK

Allow you to find a specific result for a cell by adjusting the value of one other cell

    1 – On the “Tools” menu, click “Goal Seek”

2 – In the “Set cell” box, enter the reference for the cell that

    contains the formula you want to resolve.

3 – In the “To value” box, type the result you want.

    4 – In the “By changing cell” box, enter the reference for the
          cell that contains the value you want to adjust.

SOLVER


Sponsored Links

Solver is similar to Goal Seek in that you can define a specific result, but unlike Goal Seek you can adjust up to 200 other cells.

1 – On the Tools menu, click Solver.
If the Solver command is not on the Tools menu,
you need to install the Solver add-in.

2 – In the Set Target Cell box, enter a cell reference or name

for the target cell. The target cell must contain a formula.
3 – To have the value of the target cell be as large as possible,
click Max.
To have the value of the target cell be as small as possible,
click Min.
To have the target cell be a certain value, click Value of,
and then type the value in the box.
4 – In the By Changing Cells box, enter a name or reference for
each adjustable cell, separating nonadjacent references with
commas. The adjustable cells must be related directly or
indirectly to the target cell. You can specify up to 200
adjustable cells.
To have Solver automatically propose the adjustable cells based
on the target cell, click Guess.
5 – In the Subject to the Constraints box, enter any constraints you
want to apply.
6 – Click Solve.
7 – To keep the solution values on the worksheet, click Keep Solver
Solution in the Solver Results dialog box.
To restore the original data, click Restore Original Values.

Sponsored Links

Tips:
You can interrupt the solution process by pressing ESC. Microsoft Excel recalculates the worksheet with the last values found for the adjustable cells.

BRINGING IT ALL TOGETHER


Below is a link to a simple marketing campaign model for ABC Widget Company. To better understand this example you should download a copy of this spreadsheet by clicking here: Nov 1999 Sample

In the marketing campaign model, rows 5 through 17 contain the assumptions and rows 26 through 33 contain the results.

The formulas to generate the results are relative simple and can be analyzed by click on the various cells.

Now suppose you want to know what is break-even response rate to the marketing campaign assuming the following:

      Number of Solicitations 500,000 (in cell D5)
      Solicitation Unit Cost $0.32 (in cell D7)
      Average Sale $49.99 (in cell D11)
      Cost of Goods Sold% 50% (in cell D13)
      Average shipping per sale $4.99 (in cell D15)
    Fixed Costs $10,000 (in cell D17)

From the tool bar, click “Tools”, then “Goal Seek”, click cell D33 (contribution margin), enter zero in the “To Value:” box, then enter D9 (response rate) in the “By Changing Cell” box. Finally click “OK”. The results show that a response rate of 1.7% to the marketing campaign will result in a break even!!!!

You can play around with different variation of this. Fixing other assumptions and varying other cells such as “Average Sales”, or “Solicitation Unit Cost”.

The same results can be achieved using “Solver”. For this example we will change the value in D7(solicitation unit cost) to $0.30.

From the tool bar, click “Tools”, then “Solver”, enter D33 (contribution margin) in the “Set Target Box”, check the box “Value of” and enter zero in the box, then enter D9 (response rate) in the “By Changing Cell” box. Finally click “Solve”. The results again show that a response rate of 1.6% to the marketing campaign will result in a break even!!!!

This of course is a simply example, if you have marketing models you can use solver and goal seek to set criteria and set goals.