Are you looking to forecast your company’s future performance without investing in expensive software? Would you like to build predictions based on your historical data and gain insights into trends that can drive better decisions?
In this post, I’ll walk you through a free Excel-based forecasting tool I created—designed to help you forecast sales or other KPIs using built-in Excel functionality and a bit of VBA magic. Whether you’re just getting started or want a customizable solution to fit your needs, this file can serve as the perfect starting point.
🔍 What’s Inside the Forecast Template?
The Excel file includes five powerful sheets:
1. User Interface
The heart of the tool. This dashboard lets you:
- Select your customer and item hierarchy.
- View historical data alongside forecasted figures.
- Choose your forecast start date and the number of months to project forward.
- Enter manual forecasts if needed.
- Instantly see visual charts update in real-time.
2. End of Life (EOL) Sheet
Easily flag items or customers that are no longer relevant. The file will automatically exclude them from future forecasts. Want more layers in your hierarchy (like region or product group)? You can add them—just tweak the formulas.
3. Sales Database
Here’s where you keep your historical data. The layout is simple:
- Customer and item columns
- Monthly sales values
- Manual inputs in blue, formula outputs in orange
You can continuously update this sheet with new data by adding monthly columns.
4. Forecast Database
This sheet auto-generates forecasts for each customer-item combination (excluding EOLs). It’s driven by VBA and includes:
- Forecast results
- Timestamp of generation
- Flexibility to manually override if needed
5. Forecast Dashboard
A pivot-table-based view of your full forecast. You can filter by customer, item, and timeframe to generate custom views for analysis or presentations.
📊 Dynamic Charts & Forecasting Features
The dashboard includes a live chart showing historical sales (solid line) and forecasted data (dotted line). The chart updates dynamically based on your selections—so you can:
- Change the start month for historical data
- Adjust forecast range
- Enter custom predictions manually
If you’re starting with a new product or customer with no history, you can still forecast! Just click “Update Forecast” and it will simulate a ramp-up curve.
⚙️ Automation with VBA
The tool comes with simple but effective VBA scripts to:
- Generate forecasts for all combinations in bulk
- Replace forecasts for a specific customer/item
- Update charts dynamically when inputs change
- Resize visuals after filtering to keep everything clean and readable
The forecast engine uses Exponential Smoothing, but you can adapt the formulas to apply other techniques like Moving Average or Linear Regression. More advanced methods like Holt-Winters can be added in future versions.
✅ Why Use This Instead of Buying a Tool?
- Cost-effective: Free to use and fully customizable
- Scalable: Add more hierarchy layers, items, customers
- Transparent: Understand every formula and logic step
- Extensible: Easily integrate with existing reports or systems