Successful marketers make informed decisions quickly using a combination of gut instincts and data analysis. They know the end goal, and figure out what it takes to get there.
One way to fill in those gaps is by using Goal Seek in Excel. The function is helpful when you know your desired result, but you're not sure how to reach it.
Maybe you want to improve your conversion rate to get more qualified leads but don't know how many people you need to attract. Or let's say your marketing team is aiming for an audacious revenue goal and you want to know how many customers you have to bring in with an upcoming campaign. If you're running a promotion, you likely need to figure out what discount to apply so you don't wind up with a loss.
Goal Seek is the answer. Understanding how to apply it to your marketing or sales efforts can inform your strategy by letting you calculate the numbers required to achieve your goals.
This post will explain how to use Goal Seek so you can start planning for your next campaign or making projections for the upcoming quarter.
What is Goal Seek in Excel?
Goal Seek is a powerful Excel function for conducting a what-if analysis. Also known as a sensitivity analysis, it helps you understand what can happen when you change one or more variables. Essentially, it's a way to conduct a reverse calculation within an Excel spreadsheet.
Imagine you're creating a marketing strategy for the next six months. You can use the Goal Seek Excel function to figure out the following unknowns.
- What percentage of month-over-month 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 break even on (and profit from) your upcoming email marketing campaign?
Finding answers to these questions can prevent unexpected outcomes and missed goals. Instead of wondering "what-if" when building a strategy, you can cut out uncertainty and give yourself a roadmap for success.
Before you finalize any plans, let's walk through the steps to conduct an analysis.
How To Use Goal Seek In Excel
Setting up a Goal Seek calculation is simple once your data is organized.
In the following example, I want to evaluate the percentage of customers coming in through various marketing channels. The goal is to bring in 50% of customers through marketing efforts by the end of the year.
I first populate the table using the average month-over-month (MoM) growth to see the projections for June to December. I know I have an email campaign planned for the beginning of December, and I want to see how many customers I'd have to bring in to reach my 50% goal.
Step 1: Select the cell with the output you want to change (i.e., % of customers from marketing).
Step 2: Under the Data tab, select What-If Analysis, then Goal Seek.
Step 3: A pop-up window will appear. Make sure the cell from Step 1 appears in Set cell.
Step 4: Write your desired value in To value.
Step 5: In the By changing cell box, select the cell you want to change to reach your desired outcome.
Step 6: Click OK to see the Goal Seek calculation. The new number will appear in the cell from Step 5, not in the pop-up box.
Step 7: If all looks good and you want to keep the calculation, click OK again.
Using Goal Seek, I can tell that if my MoM growth stays the same, I need to attract at least 16 customers through my December email campaign. Yes, this is a simple example. But you can expand it to much more complicated efforts, like projecting sales needed to meet revenue goals or calculating how much net income you'll earn from a campaign.
Goal Seek Analysis In Excel
Let's look at another example of Goal Seek analysis. I want to bring in 130 new customers, but I don't know how many visits I'll need to reach my goal. Before doing the Goal Seek analysis, I organize my data to find the average MoM visit-to-customer percentage.
Step 1: Select the cell with the output you want to change (In this case, the customer goal).
Step 2: Under the Data tab, select What-If Analysis, then Goal Seek.
Step 3: In the pop-up window, make sure the cell from Step 1 appears in Set cell.
Step 4: Type the number you want to hit into To value (My goal is 130 customers).
Step 5: Select the cell you want to change in the By changing cell box (Mine is for Projected Visits).
Step 6: Click OK to see the Goal Seek analysis. (Now, I know that in order to get 130 customers, I need to attract 5055 visits).
Once you fill in the missing variable using Goal Seek, you can figure out other variables. For instance, I found that with 5055 visits, I would need 910 leads to reach my desired number of customers. Having these numbers can also help me judge if the marketing and sales efforts for the month are on track to meet the goal.
Goal Seek Function In Excel
In business, uncertainty can spell the downfall of even the most thoughtful strategy. But you can take control of the variables that seem out of your control with the Goal Seek function.
Being proactive and judging the business impact of a marketing campaign or new sales effort can not only gain you respect within your company, but it can help you meet, and even exceed your goals. You'll be ready when the unexpected happens. And you'll know how to make informed decisions or tweak the strategy with your new what-if analysis skills.
