Free Template + Guide: How to Use Excel for Merchandise Financial Planning

Do you have a winning merchandise financial plan?

Merchandise financial planning is the process of mapping your financial targets into merchandise receipts and sales plan for the year. It helps you capture last year's performance, create a plan for this year and track your progress against it.

We’ve already covered how the merchandise financial planning process typically works but now the question remains: how do you create a winning plan for your business? Having the right template is the first step. A good template should help you do the following:

• Input the financial targets you want to hit, i.e. net sales dollars, gross margin percentage and weeks of supply targets.
• Generate a receipt plan to meet desired inventory levels.
• Measure actual performance against last year and current year forecasts.
• Help decide how to tweak marketing, markdown and inventory purchase adjustments, based on current performance.
• Maintain a centralized source of truth for your plans and actuals.

We've created an open-to-buy template to help you do all of this, which you can download for free to plan more effectively and accurately. Below is a step-by-step guide on how you can use it.

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:

• Grow your sales by 10%
• Increase gross margin by 2%
• Improve your cash utilization by 15%

Now, let’s dive in!

1. Gather last year's data

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. NetSuite 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.

2. Set your annual targets

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%

• Target: Set Sales $ Growth to 10% increase
• Target: Keep Average Unit Retail (AUR) Growth as 0%, unless there is a good reason to believe that AUR will change. Reasons for this could be, changing the product mix, anticipating inflation, planning reducing markdowns, etc.

Goal 2: Increase gross margin by 2%

• Target: Set Gross Margin % Diff from LY to 2%. This indicates that we are planning to improve our profitability, which could be achieved by changing product mix, getting better deals from suppliers or reducing markdowns.

Goal 3: Improve your cash utilization by 15%

• Target: Set weeks of stock (WOS) values for each month as 15% lower than what we had last year. This will indicate that we are planning to increase our inventory turnover.
• Target: Set the beginning of plan inventory targets, i.e. how much inventory in dollars and units we are planning to start the year with. This typically matches your EOP inventory from the previous year.

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.

3. Add your actuals at the end of each month

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.

4. Adjust your receipt plan as you actualize each month

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:

1. We gathered historical sales and inventory data
2. We set the annual targets to prepare a financial plan
3. We actualized the data at the end of each month
4. We made adjustments to our receipts plan to stay on course

Now you're ready to create a winning merchandise financial plan!

Sounds like a lot of work?

We get that. That’s why we’ve designed Toolio to streamline this planning process. We centralize your data, automate your workflows, and provide you actionable insights for your merchandise financial plan. If you’re ready to see how we can make your merchandising better, request a demo of Toolio.