Formula Friday #19
Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.
This Formula Friday post outlines how to calculate OTB margin using the Goal Seek function in Excel. OTB, open to buy, is what Merchandisers use to plan the amount of money available to purchase new products at the start of a season. OTB can also be generate throughout the season if sales over perform versus targets.
Goal seek is a “what if” analysis tool which makes it possible to see how different values impact each other. When used to calculate OTB margin, it can provide target prices needed to secure target margin.
What is OTB Margin, and Why Does it Matter?
OTB margin is a calculation to drive the target cost price of sourced goods. At the beginning of a planning phase the OTB margin will be equal to the target margin. As goods are purchased, throughout the trading period, the OTB margin will change. If sourced goods are purchased at a low margin versus target, OTB margin will be higher than target and vice versa.
Once in the trading period the goal of OTB margin is to bring the total spend back in line with target margin. OTB margin is an important KPI for monitoring the profitability of a business. It can highlight profitability risks based on the current margin of goods on hand.
The tricky part is calculating OTB margin, as it will be unlikely that the cost of goods will be known prior to them first being sourced. That’s where goal seek comes in estimate the cost of goods based on specific input values.
How to use Goal Seek to calculate OTB Margin
Example data will be used to help support the instructions of how to use Goal Seek to calculate OTB margin. The step before calculating OTB margin is calculating OTB – Open to Buy aka The Buy aka Spend.
OTB is the total amount of money available to spend on sourced goods in order to achieve sales, markdown and terminal stock targets. This post won’t go into details about what OTB is, but it will quickly cover off how it is calculated.
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+Sales–Opening stock
In this example our Total OTB is £2.2million – this can be seen in the image as Spend. The target margin in this example is 73.4%. Super high margin, not sure what we’re selling, maybe perfume or cosmetics.
At this planning stage, it is possible to calculate the total cost of goods to meet margin targets. As the OTB value and target margin are known. The formula to do this would be:
=(OTB/1.2)x(1-0.margin)
=(2.2m/1.2)x(1-0.734)
Now the seen has been set with the example data. We’ll move onto the instructions for how to use goal seek to calculate OTB margin.
1. Where to find Goal Seek
Goal seek lives within the What-if Analysis menu. The position of the menu varies between different versions of Excel, in the version used for this example it lives within the Data Ribbon.
2. Set the selection criteria
A pop-up will appear after selecting Goal Seek from the menu. Three values will need to be input in this window to set the selection criteria.
- The first input value will be the cell that is to be changed. In this example it is cell C25 (Margin).
- The second input value will be what the cell needs to change to. In this example it is 0.734, the target margin value. Note input values need to be numerical, so percentages are to be entered with the leading “0.”, otherwise the returned results will not be accurate.
- The third input value will be the cell to use as part of the calculation to get to the result. In this example it is cell C22 (OTB cost). Because this cell will be used in the what-if analysis calculation it needs to be a fixed number. Using a cell that includes a formula will cause the calculation to error out. As the scope of the What-if analysis will be too large.
3. Run the calculation
Once the selection criteria has been made, all that’s left to do is, click OK. A new pop-up window will appear as it runs through the calculations. It can take a couple of seconds to run through the calculations, so be patient with it.
After completing the calculation with the example data the margin to achieve in the remaining OTB is 76.2%.
Achieving this margin on the remained of purchases for the trading period, will get the overall margin back to target margin.
To continue to expand on this examples. Let’s say the Merchandiser and Buyer have agreed that it will be unlikely to achieve 76.2% margin on the remaining purchases. And agree to aim for a revised total margin of 71.9%.
The amendment to make in Goal Seek this time is to the target value. This time the target is 0.719, compared to 0.734 in the first example.
From this analysis the forecast OTB margin is 73.6%.
What-if analysis tools are a great way to answer questions that first rely on an assessment of how different values impact each other.
Margin is a topic frequently discussed between Merchandisers and Buyers. And is a great example of where the outcome of one decision can have an influence in other areas. Goal Seek makes it possible to complete that assessment quickly, so that action can be taken to sway the impact in the most desired direction.
Like this post? You should see my newsletter.
Subscribe to the Excel & ME newsletter to get updates that will empower you to inform your business decisions with data.
