Business Growth, Insights & Analysis

Sales & Inventory Forecasting Model: How to use this free tool

BY Taylor Davidson 21 Sep, "17

Defining and tracking inventory and sales is key to product planning, cash management, and ultimately growing your business. Using this free sales and inventory forecasting model, eCommerce retailers and wholesale businesses can track inventory, sales, and forecast revenue all in the one place.

What can this sales and inventory forecasting model be used for?

Before we begin putting data into this model, let’s take a look at some of the ways this tool can be used to help track your business performance and drive growth.

Get ahead with cash management and product planning with TradeGecko's Sales & Inventory Forecast Model.

Download this FREE tool.

Forecast your future sales: Once you have defined your sales objectives and the timeframe in which you want to achieve them, you can use this tool to predict the number of future sales you will make on a month-by-month basis, and adjust if needed.

Compare sales channel performance: If you’re a business who tracks sales over multiple channels (such as direct from your website versus a third-party sales site), you can compare the performance of each channel and see how each is tracking individually.

Analyse return on sales: You can use this tool to find out how much cash you are left with minus the cost of goods sold and other expenses.

Make marketing spend decisions: If you’re planning for future marketing spend, this tool will help you find out which sales channels are most effective, and where you should be focusing your marketing efforts.

How to use this free tool


Here is our step-by-step guide on how to use this free tool to estimate future product sales and inventory levels.

  • Enter your model settings

Under the “Get Started” tab, enter the currency you want to use with the model, as well as the month you wish to begin forecasting your sales and inventory.

Forecast1.png

  • Identify your sales channels

Under the same tab, enter your sales channels. These are used as custom names for sections throughout the model, so that they make sense for your business. If you only sell directly to consumers through a single platform, your tool will look the same as this example. If you sell across multiple sites or have a tiered sales process (as a wholesaler, for example), you can add multiple sales channels to track each individually.

Alongside channels you already use, you can also include additional channels you plan on introducing in the future. If you only plan on ever using one or two channels, you can hide any unnecessary channels using your spreadsheet’s program “hide rows” function.

You can also rename conversions i.e. sales. In this instance, we have called a conversion an “order”. This custom label is helpful for understanding what each section does.

Forecast2.png

  • Define your order growth

Next, forecast how fast your order numbers will grow by inputting:

  • The number of orders you plan to make in the first month
  • The first month from which you want to forecast or track growth
  • The minimum or maximum number of orders you expect (optional)
  • When you will reach maximum order growth (optional)
  • What percentage of orders will be from new customers each month, broken down by channel

Forecast3.png

The structure of the inputs is created so that you can introduce growth curves into your forecast or orders. The initial month is used as a starting point for the first month where you want to forecast orders, in each channel.

The Min or Max input allows you to forecast the same amount of orders per month (make it equal to the first month),  grow orders (input a number into the Min or Max that is higher than the first month), or decrease orders (input a number into the Min or Max that is lower than the first month).

The model will then use the months to create a steady growth (or decline) from the  first month to the Min or Max during that time range. The model spans a maximum of 3 years, (36 months).

The % of orders from new customers allows you to create a forecast for orders from new customers in addition to orders from repeat customers.

For many businesses, purchasing behaviors and products will differ between first and repeat orders, so the model allows you to forecast orders (and later, product sales) from repeat customers separately.

Note: information inputted under the “Get Started” tab will automatically populate data for each of your forecast months using the sales forecast formula. To account for variability and seasonality, you can also edit the same data for each month individually under the “Products” tab, overwriting the automatically calculated data for each channel.

Forecast4.png

  • Build your product inventory

Under the “Get Started” tab, you can also define up to 25 of your products, including for each:

    • The product name
    • The channel through which you sell each product (select in the dropdown)
    • The product category (optional, select in the dropdown)
    • The average price of the product
    • The average cost (to you) of the product
  • The number of product units purchased per order from a new customer
  • The number of product units purchased per order from a repeat customer

Forecast5.png

If you wish to use product categories, enter them below the “Product Categories” label. These are used only for reporting purposes and do not impact the calculations in the model.

Forecast6.png

The inputs for the average prices and average costs per product are assumed in the model to stay the same during all months, but if you want to change these, you can edit them directly in the model on the “Product” tab by typing in the price and COGS per month. This allows you to account for situations such as increasing or declining per-product costs, discounting or sales strategies, holidays and seasonality on average selling price, etc.

The average number of products sold per order from new customers and repeat customers can also be changed directly on the “Product” tab. This allows you to set different start dates to sell (and discontinue) products over time, account for changing buying behaviors, new product launches, seasonality, and other shifting product mixes.

Of course, if you only want to forecast an “average” order, you can simply use these sections to reflect one product and call that one product an “average” order, or you could define multiple products as average.

Streamline your operations and always be prepared for order and fulfilment influxes with TradeGecko.

Click here to start your FREE trial now.

  • Forecast inventory

Under the same “Get Started” tab, you can define how you want to purchase and pay for the inventory necessary to fulfill your forecasted product sales, including:

  • How many months you want to keep each product in inventory, based on forward-looking forecast of sales
  • How many months it takes from placing a purchase order to being ready to sell (helping you forecast when to make purchases and maintain adequate inventory levels)
  • What percentage of the cost to purchase inventory you are required to pay at time of placing a purchase order
  • How many days payable you have for the remainder of the unpaid inventory costs

There are many different ways to purchase and fill inventory for forecasted sales. The data you input here is used to forecast inventory based on sales, but you can always overwrite the forecasts directly on the “Inventory” tab to account for real-life inventory situations that deviate from the forecasts.

  • Use key reports for business insights

Once you have inputted your product information and order forecasts, the “Key Reports” tab will be pre-populated with useful insights:

Monthly beginning and ending cash balance, net cash burn, and breakeven runrate

Forecast7.png

Cash balance and burnrate trends over time

Forecast8.png

Revenue broken down by sales channel

Forecast9.png

Note: in this example, we have used one sales channel – Direct to Consumer. Data for other channels will show on the graph when inputted under “Get Started”.

  • Analyse revenue and costs

The “Summary” tab can also be used to analyse sales data including:

  • Monthly revenues by channel
  • Monthly costs of goods sold by channel
  • Gross margin by channel

Forecast10.png

To give you a more accurate representation of revenues, you can input other monthly expenses:

Forecast11.png

You can also refine your monthly cash balance data by inputting a starting cash balance and any funding, grants, equity, etc. you will receive:

Forecast12.png

  • Forecast inventory levels

Finally, the “Inventory” tab will be prepopulated with monthly data that can be used to forecast future monthly stock levels:

Forecast13.png

You can also enter optional beginning inventory levels to more accurately reflect monthly inventory additions and purchases:

Forecast14.png

Taylor Davidson founder of Foresight financial modelsAbout the Sales & Inventory Forecast Model creator:

Taylor Davidson is the founder of Foresight, which helps entrepreneurs use financial models for business decisions. Through his template financial models and strategic advisory services, he has helped over 21,000 entrepreneurs on financial planning, projections, fundraising, and business strategy. He has worked with hundreds of ecommerce companies on financial forecasting and offers more models for eCommerce forecasting and full financial modeling through Foresight’s Ecommerce Financial Model.

---

See also;

 What Is Inventory Forecasting?

How to optimize your wholesale business with inventory forecasts.

Effective Inventory Management - 5 Mistakes to Avoid

 

Download your FREE eBook today!

Track inventory, sales, and forecast revenue with the Sales & Inventory Forecast Tool.

Download now

Subscribe for insights into building world-class eCommerce companies