Successful marketers combine intuition and data analysis to quickly make informed decisions. They know the ultimate goal and figure out what it takes to achieve it.

One way to fill in these gaps is to use Goal Seek in Spreadsheets. This feature is useful when you know the desired result, but are not sure how to achieve it.

Maybe you want Increase your conversion rate Get more qualified potential customers, but don’t know how many people you need to attract. Or, suppose your marketing team’s goal is a bold revenue goal, and you want to know how many customers you have to attract through upcoming events. If you are running a promotion, you may need to figure out the discount you want to apply for, so as not to end up with a loss.

Goal seeking is the answer. Knowing how to apply it to your marketing or sales work will allow you to calculate the numbers needed to achieve your goals, which will inform your strategy.

This article will explain how to use Goal Seek so that you can start planning your next event or forecast for the next quarter.

What is the target search in Excel?

Goal Seek is a powerful Excel function used for what-if analysis. Also called sensitivity analysis, it can help you understand what happens when you change one or more variables. Essentially, it is a method of reverse calculation in an Excel spreadsheet.

Imagine you are creating a marketing strategy In the next six months. You can use the Goal Seek Excel function to calculate the following unknowns.

  • By the end of the year, what percentage of month-on-month growth do you need to achieve to double your coverage?
  • How much can you spend on free design work without exceeding your outsourcing budget?
  • How much revenue do you need to bring in to break even (and profit from) your upcoming email marketing campaign?

Finding the answers to these questions can prevent unexpected results and missed goals. When formulating a strategy, you don’t have to want to know the “hypothesis”, but you can eliminate uncertainty and develop a road map for yourself to succeed.

Before you finalize any plans, let us step through the analysis steps.

How to use target search in Excel

After organizing the data, setting the target search calculation is very simple.

In the following example, I want to evaluate the percentage of customers entering through various marketing channels. The goal is to attract 50% of customers through marketing efforts by the end of the year.

I first populated the table with average month-on-month (MoM) growth to see the forecast from June to December. I know that I plan to launch an email marketing campaign in early December, and I want to see how many customers I need to attract to achieve the 50% goal.

Step 1: Select the cell you want to change the output (that is, the percentage of marketing customers).

Step 2: Under the “Data” tab, select What-if analysisAnd then Goal seeking.

How to use target searchImage Source

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

Step 4: Write the value you want Pay attention to.

Step 5: At By changing the cell Box, select the cell you want to change to achieve the desired result.

How to use target search for calculationsImage Source

Step 6: Click Ok View target search calculations. The new number will appear in the cell in step 5 instead of in the pop-up box.

Status in goal seekingImage Source

Step 7: If everything looks good and you want to keep the calculations, click Ok once again.

Using Goal Seek, I can see that if my MoM growth remains the same, I need to attract at least 16 customers through the December email campaign. Yes, this is a simple example. But you can extend it to more complex tasks, such as predicting the sales required to achieve your revenue goal or calculating how much net income you will get from an advertising campaign.

Goal seeking analysis in Excel

Let’s look at another example of goal seeking analysis. I want to attract 130 new customers, but I don’t know how many visits it takes to achieve my goal. Prior to the Goal Seek analysis, I collated my data to find the average percentage of MoM visiting customers.

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

Step 2: Under the “Data” tab, select What-if analysisAnd then Goal seeking.

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

Target search analysisImage Source

Step 4: Enter the number you want to type Pay attention to (My goal is 130 customers).

How to change the cell boxImage Source

Step 5: Select the cell to be changed By changing the cell Box (mine is the estimated number of visits).

Step 6: Click Ok View target search analysis. (Now, I know that in order to get 130 customers, I need to attract 5,055 visits).

Example of goal seeking statusImage Source

After using Goal Seek to fill in the missing variables, you can find other variables. For example, I found that out of 5,055 visits, I need 910 potential customers to reach the number of customers I want. Having these numbers can also help me judge whether the marketing and sales work of the month is expected to achieve my goals.

Target search function in Excel

In business, even the most deliberate strategy, uncertainty can lead to failure. But you can use the Goal Seek function to control variables that don’t seem to be under your control.

Proactively judging the business impact of marketing activities or new sales efforts can not only win your respect within the company, but also help you achieve or even exceed your goals. When an accident happens, you will be prepared. You will know how to use new what-if analysis skills to make smart decisions or adjust strategies.

New call to action

Source link