We have already covered how a merchandise financial planning process typically works and shared an Open-to-Buy Excel template that you can use to drive this process. Now we will take you through how to use the template with step-by-step instructions.
To make the process more illustrative, let’s use an example scenario. You had a stellar year last year and have set these goals for the upcoming year:
You can use Toolio's merchandise financial planning template to capture last year's performance, automatically create this year's plan and seamlessly track your progress against it. Let's dive in and see how it works step by step!
First, you need to pull last year's sales and inventory data on a month-over-month basis from your systems of record. Sales data is typically exported from a POS system (e.g. Shopify or Demandware) and inventory from an ERP system (e.g. Nesuite or Microsoft Dynamics). Some companies may also store inventory information in spreadsheets or an accounting software like QuickBooks.
Note that we have gathered two key types of information: sales information (in retail dollars, costs and units) and inventory information (in dollars and units) for the end of each month and the beginning of the first month. That's all you need! Input this information into the gray section in the Inputs tab of your Open-to-Buy template which is shown in Figure 1. Once you enter in all that information and switch over to the Open-to-Buy tab, Figure 2 is what you will see.
You can clearly see the gross margin dollars and gross margin % you achieved along with the other retail metrics pertaining to last year's performance. Note that this year's forecasts are the same as last year, since we haven't added any target information for this year yet. That's what we will do next.
In order to plan for your goals, this year's targets are added back on the Inputs tab which is shown in Figure 3. Targets are most valuable when considered with respect to historical data. Here we input the targets that align to the goals we previously established:
Goal 1: Grow your sales by 10%
Goal 2: Increase gross margin by 2%
Goal 3: Improve your cash utilization by 15%
Once you input the targets in the Inputs tab, Figure 4 is an updated view of how the Open-to-Buy tab will look.
Now we have this year's forecast rows populated, and the % Var LY values (i.e. how much we differ from last year) are nicely flowing in. You can also see that our quarterly, half-year and annual aggregations are automatically populated, and we have an annual sales target that is 10% more than last year. At this point, we have a merchandise plan for the upcoming year in place. This will help us create a receipt plan so we know how much inventory to buy for each month, which you can also see on the Open-to-Buy tab.
Now that we have a plan, what next? The forecasts help us create an initial receipt plan pre-season, but after each month within a season, we need to actualize the data to see how we are performing against the plan and make adjustments as we get new information. That's what we will cover next.
Starting with February, once the month is over, pull that month's selling reports from your POS and inventory systems and add the actual sales and inventory metrics in the Inputs tab of the template. See Figure 5 below for what it would look like.
Note that these metrics from this year are the same data points we pulled from last year’s reports to import in the Inputs tab which helped us build the initial merchandise plan. Once the actuals are added, let's go back to the Open-to-Buy tab one last time to see how the new actuals have impacted our plan.
In Figure 6 we see that the first month has been actualized, i.e. completed. We also see that the Sales $ Act is 8% above what we forecasted and 19% above LY, which is superb! We first celebrate and then figure out what that means for our business. Now let's see how this impacted our gross margin and the inventory positions.
In Figure 7, we are able to see that the first month of Gross Margin % values are automatically updated , and our margin is almost the same as expected. Finally, we move on to the end-of-month inventory section.
In Figure 8 we see something interesting which should cause you to take action. We see that our Inv EOP $ % Var Fcst is -30%, i.e. we ended the month with 30% less inventory than we were anticipating. This, of course, is because we sold more than we were planning to. If we want to sustain this high sales trend, we will need to chase more inventory and bring in more receipts to get us back to the planned inventory levels. That takes us to the last, and probably the most important section, the Open-to-Buy row at the bottom of the sheet.
The Open-to-Buy $ row in Figure 9 shows us how much more inventory is required to bring in to catch back up to our original merchandise plan. Note that the same number, i.e. $806K is repeated month after month. That is because, unless we bring in more inventory, we will be behind our inventory plan for the entire year. What we need to do is to figure out when we can bring in more products, which will be dependent on the suppliers' lead times, and put in a receipt adjustment back on the Inputs tab.
Let's say that in this scenario we can bring in $750K more inventory in the month of March thanks to a super fast supplier. We add this number to our receipt adjustments shown in Figure 10 above, which should get us closer to our anticipated inventory position. Let's look back at the Open-to-Buy tab to see the impact of the new receipts.
In the updated Open-to-Buy tab in Figure 11 which reflects our receipt adjustment, this additional inventory puts us closer to plan at the end of March, deviating from the forecast by only 3%. Without the receipt adjustment we were trailing forecasts by 37% which you can see in Figure 8.
That's it for February! At the end of each upcoming month, we would repeat the actualization and adjustment to make sure we are staying as close to our merchandise plan as possible. This helps us meet our financial goals and keep our customers happy.
To summarize the steps we just walked through:
We hope that this merchandise financial planning template will help you better manage your inventory to meet sales targets. However, as you can probably see in this example, manually exporting and importing data from multiple systems, creating plans, and updating the information every month can be a very time consuming and error prone process -- and imagine having to do this for every one of your departments, classes and sales channels!
This challenge is exactly what the Toolio Merchandise Financial Planning software is designed to address.
If you want to learn more about how Toolio can help your business, let's talk!