Your Brief Guide to Using Goal Seek in Excel

0
19
Your Brief Guide to Using Goal Seek in Excel

Successful marketers make informed decisions quickly using a combination of gut feeling and data analysis. You know the end goal and you figure out what it takes to get there.

One way to fill these gaps is to use Destination Finder in Excel. The feature is useful when you know your desired result but are not sure how to achieve it.

You might want to improve your conversion rate to get more qualified leads, but you don’t know how many people to attract. Or let’s say your marketing team has a bold sales target and you want to know how many customers you need to get with an upcoming campaign. If you are running a promotion, you will likely need to figure out which discount to apply so that you do not suffer a loss.

Finding a destination is the answer. Knowing how to apply it to your marketing or sales activities can help shape your strategy by being able to calculate the numbers it will take to achieve your goals.

This post explains how to use Destination Finder so you can plan your next campaign or make forecasts for the coming quarter.

What is Destination Search in Excel?

Goal Seek is a powerful Excel function for performing what-if analysis. Also known as sensitivity analysis, it helps you understand what can happen when you change one or more variables. In essence, it’s a way of doing an inverse calculation within an Excel spreadsheet.

Imagine creating a marketing strategy for the next six months. You can use Excel’s Destination Search feature to find out the following unknowns.

  • What percentage of the monthly growth do you need to double your reach by the end of the year?
  • How much can you spend on freelance design work without exceeding your outsourcing budget?
  • How much revenue do you need to bring in to fund (and benefit from) your upcoming email marketing campaign?

Finding answers to these questions can prevent unexpected results and missed goals. Rather than wondering what if you were wondering as you develop a strategy, you can avoid uncertainty and provide yourself with a roadmap for success.

Before finalizing any plans, let’s go through the steps to run an analysis.

How to use Destination Finder in Excel

Setting up a goal calculation is easy once your data is organized.

In the example below, I want to evaluate the percentage of customers who come in through different marketing channels. The aim is to win 50% of customers through marketing measures by the end of the year.

I first fill in the table with month-to-month (month) average growth to see the projections for June through December. I know there is an email campaign planned for early December and I want to see how many customers I would need to acquire to meet my 50% goal.

Step 1: Select the cell with the expense that you want to change (i.e.% of customers from Marketing).

Step 2: On the Data tab, select the option What if the analysis, then Target search.

How to use Destination FinderImage source

Step 3: A pop-up window will appear. Make sure that the cell from step 1 appears in Set cell.

Step 4: write your desired value in Estimate.

Step 5: im By changing the cell In the box, select the cell that you want to change to get the result you want.

How to calculate with the destination searchImage source

Step 6: Click OK to view the target calculation. The new number will appear in the cell from step 5, not in the pop-up window.

a status in the destination searchImage source

Step 7: If everything looks fine and you want to keep the calculation, click OK again.

With the destination search, I can see that if MoM growth remains the same, I need to acquire at least 16 customers with my email campaign in December. Yes, that’s a simple example. However, you can extend it to much more complicated tasks, such as: For example, forecasting the sales required to meet sales targets or calculating the net income you will generate from a campaign.

Target search analysis in Excel

Let’s look at another example of homing analysis. I want to attract 130 new customers, but I don’t know how many visits I will need to reach my goal. Before doing the Goal Seek analysis, I organize my data to determine the average MoM visitor to the customer.

Step 1: Select the cell with the output that you want to change (in this case the customer target).

Step 2: On the Data tab, select the option What if the analysis, then Target search.

Step 3: In the pop-up window, make sure the cell from step 1 is shown in Set cell.

Analysis in the target searchImage source

Step 4: Enter the number you want to hit Estimate (My goal is 130 customers).

How to change a cell fieldImage source

Step 5: Select the cell you want to change in the By changing the cell (Mine is for scheduled visits).

Step 6: Click OK to view the target search analysis. (Now I know I need to attract 5055 visits to get 130 customers).

An example of the destination search statusImage source

After you fill in the missing variable with the target search, you can find out other variables. For example, I’ve found that with 5055 visits, I need 910 leads to reach my desired number of customers. These numbers also help me to judge whether the month’s marketing and sales efforts are on track to meet the goal.

Destination search function in Excel

In business, uncertainty can mean the downfall of even the most elaborate strategy. But you can use the finder function to take control of the variables that appear to be out of your control.

Being proactive and assessing the business impact of a marketing campaign or new sales venture can not only earn you respect within your company, but can also help you achieve and even exceed your goals. You are ready when the unexpected happens. And you’ll know how to make informed decisions or use your new what-if analysis skills to optimize strategy.

New call-to-action