Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

future trend forecasting

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Future trend forecasting

Future trend forecasting

ResolvedVersion 365

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

Fri 20 Jun 2025: Automatically marked as resolved.

Excel tip:

Toggle Formulas and Results

Ctrl + 'The key above Tab with the

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.