Goal Seek

Excel calculates the input to deliver a defined output!

Goal Seek is perfect for that Management question "What do we need X to be, in order to achieve Y?" or "What annual % price increase must we implement, to achieve sales of £100m in 5 years?"

Goal Seek is part of Excel's Data Analysis tools. We select an output, tell Excel what we want that output to be, then we select an associated input - Goal Seek will iterate through values for the input until it finds the perfect result...perhaps it's easier to just watch the video.

A good associated video to follow this up with is Data Tables Intro. Rather than Management knowing what they WANT sales to be in 5 years, they might ask what WILL they be IF an input is X, Y or Z...this is Sensitivity Analysis, will get you in their good books, and will probably earn you a promotion ;)

Transcript

We've sales today of 1,000 and in two years time we want that to have increased to 2,000. What annual % growth do we need to get there? We need Goal Seek! We can define what we want the answer to be and then it'll find what the assumption needs to be to get there. It's perfect for Management questions such as "What would we need to believe X to be in order to achieve Y?" I'm not going to explain it any further, it's easier to see it in action. We'll start with the basics of Goal Seek, and then we'll apply it to a simple financial model.

We'll start with a couple of simple examples to demo Goal Seek. In Example A we've got these 3 cells which I've called A, B and C. C is just a simple addition of A and B. We can see it's 1 + 2 = 3. The question is "What value must A be for C to equal 5?" So we want to get the result C to 5 and we get there by changing A. So we use Goal Seek.

We go to Data -> What-If Analysis -> Goal Seek. It asks us to Set Cell - and we've got to choose the cell which will ultimately have the result - that's C... To Value - we type in 5. By Changing Cell - and that will be A, as A is the cell that we want to change to achieve that 5. We press OK. It gives a quick iteration and has found the solution and put it in A... which is obviously 3. So if I press OK it will accept that.

Moving onto Example B - we've got numbers of cars sold. So in Year 1 we sell 250 and then each year after we grow the previous year by the 5% growth. We ultimately achieve 304 in Year 5. The question asks "What growth do we require to achieve 400 cars sold in Year 5?" So basically we're saying we achieve 250 here. 304 isn't good enough by Year 5. We actually want it to be 400. What growth do we need to get there?

Again I can use Goal Seek. I go to Data -> What-If Analysis -> Goal Seek. Set cell - and I need to select Year 5 here. To Value - 400. By Changing Cell - we choose the growth. Press OK and straight away it's found a solution - it's 12.5%. I'll press OK and accept that. You can see it's just growing that 250 by the 12.5% and we eventually get to 400.

Moving onto Example 2, we've got something a little more complicated, but more realistic. It's a simple cinema model. It's really important to go further than Example 1 and see these tools & techniques applied in a more complex context. I'm going to take the next minute to explain how this works so you can see how Goal Seek is being applied. So hang in there! I also use similar setups in my Data Tables and Scenarios videos. Really relevant tools if you're interested in sensitivities, scenarios and commercial analysis.

We've got a P&L of a cinema and above it "Drivers" - the drivers supply numbers to calculate the P&L. So if we look at what's in here, we've got:

• Gross Sales = Ticket + Popcorn & Coke revenue.
• Ticket sales = Admissions x Average Ticket Price.
• Popcorn & Coke sales = Admissions x Average Popcorn & Coke spend.
• Direct Costs = Film Rental + Cost of Goods Sold.
• Film Rental = Ticket Sales x Film Rental %.
• Costs of Goods Sold = Popcorn & Coke Revenue x Cost of Goods Sold %.
• We've got Gross Profit.
• Overheads = Gross Sales x Overheads as % of Sales
• Finally we have Profit.

So these yellow cells in the drivers are our inputs. We've got Year 1 inputs, and then we grow those by these %s. For instance, if we start with 1,875 Admissions in Year 1. In Year 2 we're growing it by 4.5% each year. That's broadly the same for all of these drivers.

The question asks "What Average Ticket Price growth do we need to achieve £35m of Ticket sales in Year 5?" I've marked that result here with blue. Right now we've got just over £30m. We want to get that to £35m by changing the Average Ticket Price growth, which is this.

We use Goal Seek. We go to Data -> What-If Analysis -> Goal Seek. And we want to set the result to £35m. (In this case I'm putting 35,000 because this is denoted in thousands). By changing cell - and we choose the Average Ticket Price growth. I press OK. You can see those quick few iterations and it's achieved a result which is roughly 8%. I press OK and that's accepted it into the model. You can see it's just over 8% there. It's worked out an exact solution.

The second question is "What Admissions growth do we need to achieve £14m of Profit in Year 4?" So after this result has gone through our Profit is now £12.7m. We want to increase that to £14m. We'll do that by changing our Admissions growth.

So again, I'll do that by using Goal Seek. I go to Goal Seek. Set cell - pick the result we're interested in. We want to get it to £14m, by changing Admissions growth. Press OK. Again, it's about 8%. I press OK and it's accepted it. And that's the answer we're looking for - just over 8% growth per year will get us to an Admissions figure which then, when calculated through the P&L with the other drivers, will give us a Profit of £14m.

I hope you found that interesting. I'm sure you'll be able to use it in some of your analytical work. Or when Management ask questions like "What do we need X to be, in order to achieve Y?" Like I say, head to my Data Tables video if you want to see how we sensitise a model for different assumptions. Or my Scenarios video, where we look at a technique for flexing the assumptions of a model to create different outcomes. Both really relevant to Commercial and Finance Analysts and actually a lot of other roles. Thank you for watching!