Priya has attended:
Excel Advanced course
Power BI Modelling, Visualisation and Publishing course
Future trend forecasting
I'm working with data from multiple tables in Excel to forecast when a reorder might be needed. One table contains historical usage data, while another includes baseline figures like minimum order quantities. I want to determine at what future point the projected usage will reach the reorder threshold. Is there a more effective or efficient way to do this in Excel?
RE: 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