Using Goal Seek to Forecast OTB Margin

Formula Friday #19

Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.

This post is about a tool more than a formula. We’re going to look at how Goal Seek can be used to help forecast your OTB margin.

First let’s set the scene

One important responsibility of a Merchandiser is to work with Finance to agree on sales target for the year/season. And at the same time agree margin targets. Both of these targets, set at Department/Category level, will need to rollup into the overall Financial targets for the business. Which means these are likely to be lengthy conversations where compromises are going to need to be made.

As you can imagine, the numbers are calculated differently between the two different departments. Sometimes the Merchandisers come out with higher numbers, sometimes Finance has the higher numbers.

We won’t be covering how the targets are calculated in this post. But we will be touching on how to calculate The Buy or OTB – Open to Buy.

To calculate your OTB you need to know:

  • Opening stock for the period
  • Sales forecast/target 
  • MD spend 
  • POS spend 
  • Closing stock for the period

And the formula you will use is this:

=Closing stock+POS Spend+MD Spend+SalesOpening stock

On with the show

In this example our Spend – Total OTB – is £2.2million, with a margin target of 73.4%. I know super high margin, not sure what we’re selling, maybe perfume or cosmetics?!

From here we could calculate what the target Total Cost would be. As we know how much were want to spend and what our margin is. To do that you would use this formula:

=(buy/1.2)x(1-0.margin)

n.b VAT in the UK was 20% at the time this post was first published

However, because we want to use the Goal Seek tool. To help us forecast our OTB margin, we will leave this calculation out and I will explain why in a moment.

As you can see from the image the margin on our commitment is way under target – 69.7%. I don’t even know how we managed to get that signed off but we did. And to give us a fright and remind ourselves why it is important to keep a closer eye on targets. We can use Goal Seek to show us what our OTB Margin would need to be to get back to target.

Where to find Goal Seek

In my version of Excel, Goal Seek lives in the Data Ribbon. And is within the What-if Analysis drop down menu.

It might live elsewhere on your version but will likely still be within the Data Ribbon.

Now we’ve located the tool we can put it to action to get this calculation going. 

Once you have selected Goal Seek a pop-up box will appear

You will then need to; select the cell you want to change – in this example it is cell C25. Input the value you want to change it to, as a number, in this example it’s 0.734.

When working with percentages you will need to enter it as ‘0.something’ otherwise you will get some funny looking results. Finally you need to select the cell to change to help you get to your result.

This final step is the reason why I didn’t calculate the OTB cost by subtracting the cost of the commitment from the target cost. The cell you want to change to get you to your goal needs to be a fixed number. Otherwise Goal Seek will have too many factors to manipulate and will just error out.

Once you are happy with the instructions you have given, click OK and then let it get to work. It can take a couple of seconds to run through the calculations so be patient with it.

As you can see from the image, our OTB margin will need to be 76.2% to get back to target. Which I think we can all agree will be very unlikely.

What we can do is go back into Goal Seek, and adjust the inputs to give us a OTB margin and cost that we believe is more achievable.

 

 

Final thoughts

We will go through the specific processes to calculate sales and margin targets at another time. I just wanted to use this as an example of how the Goal Seek tool can be used in the context of Merchandising. And seeing as margin targets are always something that’s discussed, at length. I figured now is as good a time as ever to share a tool that, I hope, will help you to provide those short notice forecast requests.

Until next time,

Ax

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.