Goal Seek: Reverse Engineering For Excel

# Goal Seek: Reverse Engineering For Excel

**AUTHOR: JASON MORRELL **

**Trial and error is time consuming. Goal Seek fixes that.**

**Goal Seek is a simple tool in Excel that lets you go straight to the answer and set it to anything you want.**

**Excel then works backwards and evaluates what input value is required in order for the answer to be achieved.**

**Here is what you'll find in this post.**

**FEATURE DOWNLOAD**

Grab the free Goal Seek cheat sheet

## Example 1: Using Goal Seek To Forecast Revenue

Letâ€™s start with a really simple example - a classic sales calculation.

Now, letâ€™s say we want to reach a revenue of $300.00,

Assuming the same quantity of widgets are ordered, what would we need to increase the price to?

OR â€¦ assuming we sell widgets at the same price, what quantity would the customer need to order?

Hereâ€™s the spreadsheet:

Hereâ€™s how we would use Goal Seek to calculate the new PRICE.

1. Select cell **B3**.

2. Select **Data** | **What If Analysis** | **Goal Seek**.

3. Change **Value** to **300**.

4. Set **By Changing Cell** to **B1**.

5. Click **OK** to see the results.

This tells us that we would need to increase the price from $0.13 to $0.15 to achieve our revenue target of $300.00

At this point you can click OK to keep the new price or Cancel to revert back to the original value.

Letâ€™s use Goal Seek again to calculate the new QUANTITY.

1. Select cell **B3**.

2. Select **Data** | **What If Analysis** | **Goal Seek**.

3. Change â€˜**To Value**â€™ to **300**.

4. Set â€˜**By Changing Cell**â€™ to **B2**.

5. Click **OK**.

This tells us that we would need to increase the quantity to 2,308 (rounding up the decimals) to achieve our revenue target of $300.00

## Example 2: Using Goal Seek To Calculate Monthly Mortgage Repayments

A mortgage repayment has 3 contributing factors

Hereâ€™s the spreadsheet, already populated.

This is a quick breakdown of the PMT function.

Notice how all the figures were adjusted to work on a common MONTHLY basis (i.e. B2/12 and B3*12).

There is a minus sign in front of the Present Value (PV). This makes it negative, which makes the final repayment figure positive.

Anyway back to Goal Seek!

The current monthly repayment based on these figures is $2,347.

Letâ€™s say you get a pay rise and you can now afford to pay $2,600 instead of $2,347.

Using Goal Seek you can discover 3 things:

Letâ€™s answer those questions.

1. Select cell **B4**.

2. Select **Data** | **What If Analysis** | **Goal Seek**.

3. Change â€˜**To Value**â€™ to **2600**.

4. Set â€˜**By Changing Cell**â€™ to **B1**.

5. Click **OK**.

The amount you can borrow has increased from **$450,000** to **$498,421**.

And setting â€˜By Changing Cellâ€™ to B2 â€¦ The interest rate could rise from 4.75% to 5.66%.

And setting â€˜By Changing Cellâ€™ to B3 â€¦ The term reduces from 30 years to 24.37 years.

## What next?

So thatâ€™s Goal Seek. A simple tool that provides incredible functionality and purpose.

**Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! **

**ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.**

Enjoy the rest of your day!

About the author

Jason Morrell

Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE