Future trend forecasting

Hi Priya,

Thank you for the forum question.

Yes, Excel can be quite powerful for forecasting reorder points, especially when combining historical usage data with baseline inventory parameters. Here’s a structured and efficient approach you can take:

1. Organize Your Data
Ensure you have two main tables:

Usage Table: Contains historical usage data (e.g., Date, Item, Quantity Used).
Inventory Table: Contains baseline data (e.g., Item, Reorder Point, Minimum Order Quantity, Lead Time).

2. Calculate Average Usage Rate
Use a formula like this to calculate average daily or weekly usage:


You can also use a rolling average or exponential smoothing for more accuracy.

3. Forecast Future Usage
Use Excel’s FORECAST.LINEAR or TREND function to project future usage:


Or use a time series model with Data Analysis Toolpak or Power BI for more advanced forecasting.

4. Determine Reorder Point Breach
Create a running total of projected usage and compare it to your reorder point:


You can use a helper column to calculate the cumulative projected usage over time.

5. Automate with Conditional Formatting or Alerts
Highlight rows where reorder is needed using conditional formatting or create a dashboard with visual alerts.

6. Optional: Use Power Query or Power Pivot
If your data is large or from multiple sources:

Use Power Query to merge and clean data.
Use Power Pivot to create relationships and DAX measures for dynamic forecasting.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector