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.
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.
Predict your future product sales and inventory levels.
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.
Here is our step-by-step guide on how to use this free tool to estimate future product sales and inventory levels.
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.
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.
Next, forecast how fast your order numbers will grow by inputting:
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.
Under the “Get Started” tab, you can also define up to 25 of your products, including for each:
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.
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.
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:
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.
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
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”.
The “Summary” tab can also be used to analyse sales data including:
To give you a more accurate representation of revenues, you can input other monthly expenses:
You can also refine your monthly cash balance data by inputting a starting cash balance and any funding, grants, equity, etc. you will receive:
Finally, the “Inventory” tab will be prepopulated with monthly data that can be used to forecast future monthly stock levels:
You can also enter optional beginning inventory levels to more accurately reflect monthly inventory additions and purchases:
Predict your future product sales and inventory levels.
About 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.
Track inventory, sales, and forecast revenue with the Sales & Inventory Forecast Tool.
© 2020 Intuit Inc. All rights reserved.
Intuit, QuickBooks, QB, TurboTax, Proconnect and Mint are registered trademarks of Intuit Inc. Terms and conditions, features, support, pricing, and service options subject to change without notice.
By accessing and using this page you agree to the Terms and Conditions. | Privacy Statement