How to automate inventory forecasting using free AI tools for UK sellers

You can automate inventory forecasting for your UK e-commerce business by combining free open-source language models with Google Sheets or Microsoft Excel to analyse sales data and predict future stock requirements. This approach helps you make data-driven decisions without paid software subscriptions.

The workflow uses locally run models or free web interfaces to process historical sales patterns, seasonal trends and external factors, then generates reorder recommendations. It requires only basic spreadsheet skills and careful prompt design to produce reliable outputs.

Why inventory forecasting matters for UK sellers

Effective inventory forecasting balances having enough stock to meet demand while avoiding excess that ties up working capital. For small e-commerce operations, overstocking can quickly strain cash flow, especially when storage costs or platform fulfilment fees apply. Understocking leads to lost sales and damaged customer trust.

Manual forecasting often relies on simple averages that miss seasonal peaks, promotions or market shifts. Automating the process with free tools allows you to incorporate more variables and update predictions regularly without significant time investment. This is particularly useful for sellers on platforms such as Shopify, Amazon or Etsy who manage multiple SKUs.

Choosing free AI tools and spreadsheet setup

Start with open-source large language models that can run locally or through free tiers. Options include Ollama with models such as Llama 3 or Mistral, which you install on your computer. These allow private data processing without sending sales figures to external servers. For those preferring no installation, use free web interfaces of models with strict daily limits.

Set up a spreadsheet with columns for date, product SKU, units sold, price, promotion flag, and any external factors such as weather or holiday periods. Import at least 12-24 months of historical sales data exported from your e-commerce platform. Keep the file simple; avoid complex macros at this stage. Use separate sheets for raw data, processed features and model outputs.

Preparing your data for AI analysis

Clean the data by removing returns, cancelled orders and obvious errors. Add calculated columns for rolling averages, month-over-month growth and seasonal indices. For example, create a column that flags peak months such as November and December for many UK retailers.

Convert categorical information into numeric format where possible. A simple encoding for day of week or whether a promotion ran helps the model identify patterns. Limit the dataset to products with sufficient sales history; new items may need separate manual estimates. Save the prepared data as a CSV for easy input into the AI tool.

Step-by-step workflow using open-source models

Export and clean your sales data in the spreadsheet as described.
Run your chosen open-source model locally via Ollama or a similar tool.
Craft a detailed prompt that includes the data summary, product details, lead times from suppliers and target service level. Ask the model to output predicted units for the next 3, 6 and 12 months with explanations of its reasoning.
Paste the model's response back into the spreadsheet and convert the forecasts into reorder quantities by subtracting current stock and adding supplier lead time demand.
Review the output for logical consistency. Adjust the prompt with additional constraints, such as minimum order quantities or storage limits, and rerun if needed.
Schedule a monthly refresh by updating the data and repeating the process.

Use prompts that specify UK context where relevant, such as referencing common bank holidays or VAT-inclusive pricing if those affect your recorded sales.

Creating effective prompts for accurate forecasts

Good prompts provide clear structure and examples. Begin with role assignment: "You are an experienced retail inventory analyst for a UK e-commerce seller." Supply the actual numbers rather than vague descriptions. Include instructions such as "Base predictions only on the supplied data. Show step-by-step reasoning before giving final monthly forecasts."

Example prompt excerpt: "Here is monthly sales for Product A over 18 months: [paste data]. Supplier lead time is 4 weeks. Current stock is 120 units. Recommend monthly order quantities for the next six months to maintain 95% availability while minimising excess stock. Explain any seasonal adjustments."

Test several variations of the prompt on a single product to compare outputs. Record which versions produce the most sensible results for your product types. Avoid overly long prompts that exceed context limits of the free models.

Calculating reorder points and avoiding overstock

Once you have AI-generated demand forecasts, calculate reorder points using the formula: (average daily demand × lead time in days) + safety stock. Determine safety stock based on forecast error observed from past predictions. The spreadsheet can automatically compute these values using simple formulas once the AI outputs are entered.

Compare the recommended order quantity against economic order quantity principles, adjusting for any supplier minimums. Set rules to reduce orders if the forecast suggests declining demand. Track actual sales against predictions each month to refine future prompts and improve accuracy over time. This closed loop helps prevent gradual overstocking that can occur when forecasts consistently overestimate.

Risks, limitations and best practices

Free open-source models can produce inconsistent results if prompts are unclear or data is sparse. They do not replace professional accounting or inventory software for businesses with high transaction volumes. Always validate AI suggestions against your own knowledge of upcoming promotions, product launches or market changes.

Data privacy is important; running models locally reduces exposure but requires sufficient computer resources. Spreadsheet errors can compound quickly, so use version control by saving dated copies. Start with a small number of products to learn the workflow before scaling. Regularly check current platform terms, HMRC guidance and Companies House requirements as rules on business records and reporting can change.

Founders at places such as Loomworks in West Yorkshire can benefit from testing these automations in a supported environment while focusing on growth.

Frequently asked questions

Can I use this inventory forecasting method without coding experience?

Yes. The workflow relies on spreadsheets and natural language prompts rather than writing code. Basic skills in copying data, using simple formulas and installing free tools like Ollama are sufficient. Start small and build familiarity with the prompting process.

How much historical data do I need for reliable AI forecasts?

At least 12 months of consistent sales records is recommended to capture seasonal patterns. More data improves accuracy, but even 6-9 months can provide useful baseline predictions for stable products. New items with no history require manual estimation or proxy data from similar products.

What free AI models work best for inventory prediction?

Open-source models available through Ollama such as Llama 3, Mistral or Gemma perform adequately for this task when given well-structured prompts. They can analyse trends and explain reasoning. Choose models based on your computer's capabilities; smaller models run faster on modest hardware.

How do I handle seasonal products with this workflow?

Explicitly include seasonal flags and past peak period data in your prompts. Ask the model to identify and project recurring patterns. After the first cycle, feed previous forecast errors back into new prompts so the model learns to adjust for your specific seasonality.

What should I do if the AI forecast seems unrealistic?

Treat the output as one data point rather than a final decision. Compare it with simple moving averages from your spreadsheet. Refine the prompt with additional constraints or recent market information. Over time you will learn which prompting styles work best for your product range.