Description
Quantitative forecasting aims to quantify market trends and assess, for instance, the growth or decline of specific market segments on a yearly basis. These techniques typically leverage quantitative data from multiple sources, including public and private institutions (such as the OECD and industry associations), to analyze historical data and generate future trend estimates.
These tools primarily serve as comparison instruments, aligning insights from various sources to evaluate market conditions and develop future scenarios. Their value lies in providing clarity and simplicity when presenting market data, making it easier for company management to discuss and refine development strategies.
The proposed Excel tool is designed to present different metrics side by side, consolidating them into a single market trend estimate by creating a weighted average of multiple sources. It functions like a “cocktail of metrics,” where each quantitative input contributes to the final estimate, serving as either a forecast or a market baseline.
For data-driven specialists comfortable with large datasets, this tool provides a structured platform for managing multiple metrics simultaneously. For those aiming to become more comfortable with numerical analysis, it simplifies the process of comparing and communicating market trends using diverse data sources.
Key Features of the Market Quantitative Forecasting Tool
The Excel tool proposed here is targeted towards Quantitative Forecasting. The tool organizes and presents data using a consistent time interval offering two display modes:
- Absolute Mode: Displays different metrics together on the same graph, where each value retains its original scale.
- Relative Mode: Aligns all metrics at a single pivot point—a specific year where they intersect—allowing a clearer focus on variations over time.
With the relative approach, a combined forecast—calculated using weighted factors across multiple metrics—can be assessed and displayed on the same graph, enabling a more comprehensive market trend analysis.
The tool consists of three tabs for data entry and metric visualization:
Tab “1. Data by Segment”
This tab is used to enter data and is the only tab where data by segment and year should be input.
The organization of the data is with a single data point for a metric, a segment and a year by row. This allows additional data to be added gradually. For calculation purposes such metrics should be additive such as population and market volume. It is not recommended to use ratio or growth rates that cannot be added across multiple segments when absolute values can be.
Up to 4 Excel slicers can be used, allowing multiple segments to be considered for each slicer. For instance, two slicers can be allocated for region and country levels, one slicer for Business Unit level and a fourth for technology.
There is no restriction on the number of rows used for metrics except evidently the size of the Excel file.
A refresh button allows the data in the forecast views to be refreshed.
When data is wrongly entered, with text instead of a numeric value, an indication is posted to help identify the issue.
Tab “2. Absolute Metrics”
This tab provides a side-by-side view of absolute metrics. It is ideal for metrics measured in the same units. When metrics cannot be summed – such as total population and GDP, which use different units – they can still be displayed on the same graph, provided an appropriate legend is used to avoid confusion. Any of the four available filters can be applied to filter metrics based on their units.
Tab “2. Absolute Metrics” – Features
- The first and last years of the range to be displayed can be selected. A year representing the current year can also be entered to highlight the pivot year between past data and future forecasts.
- A slicer allows the selection of which metric(s) should be displayed on the graph and in the table below it. A maximum of 50 rows of data can be displayed at once.
- The four Excel slicers can be used to select which segments to display in the table and graph. If details by segment for a slicer are not needed, the slicer can be hidden. In that case, the displayed data will represent the total for those metrics based on the selected segments. Any combination of slicers can be shown or hidden.
- Upon refreshing the graph, legends can be displayed above the graphs either as numbers or percentages.
Tab “3. Relative Metrics”
This tab is highly versatile, allowing all metrics to be displayed on the same graph. The approach involves analyzing variations introduced by each metric, using a specific year – referred to as the “current year” – as the pivot year. All metrics can be displayed, and a weighted sum can be calculated to generate an average forecast by applying selected weighting factors.
It functions very similarly to Tab 2. Absolute Metrics, with one major difference: all metrics are shown as percentage growth relative to the pivot year. This allows for the calculation of a forecasted growth rate as a weighted sum across all selected metrics.
Tab “3. Relative Metrics” – Features
- The first and last years of the range to be displayed can be selected. A year representing the current year can also be entered to highlight the pivot year between past data and future forecasts.
- A slicer allows the selection of which metric(s) should be displayed on the graph and in the table below it. A maximum of 50 rows of data can be displayed at once.
- The four Excel slicers can be used to select which segments to display in the table and graph. If segment details for a slicer are not needed, the slicer can be hidden. In that case, the displayed data will represent the total for those metrics based on the selected segments. Any combination of slicers can be shown or hidden.
- Upon refreshing the graph, legends can be displayed above the graphs either as numbers or percentages.
Tab “3. Relative Metrics” – Forecasting Feature
- You can choose to display a forecast that combines all selected metrics. For example, if your market growth baseline is a mix of two metrics, the tool can calculate a weighted sum of their trends.
- You can set forecast weight factors directly in the table.
- Those can be refreshed to 100% with a button click.
- The Graph can be displayed with or without the forecast.
Note: You have full access to the macros of this file, so you can adapt or create new macros. Please share the needs you’d like to see addressed in a future version.
Links and References
Forecasting is more reliable when market drivers are defined. Visit Market Mix for the structural foundations behind forecasts.
Usage & Licensing
Please review the Sales Conditions published on this website to fully understand your rights and the restrictions regarding the use of this product. Our products are intended for professional use only and are not designed for consumer purposes.
The products provided under this agreement are exclusively for the individual buyer or members of the same business unit. Sharing, distributing, or making them available to individuals or teams outside the designated user or relevant business unit is strictly prohibited. For software licensing purposes, a business unit is limited to a maximum of 100 employees, including full-time, part-time, and temporary staff. Additional licenses must be purchased in multiples of 100 if this limit is exceeded.
The provided download link is valid for two months and allows up to 10 downloads. Be sure to save a copy before the link expires or the download limit is reached. Users have full access to the application product, including any embedded macros, and may make necessary adjustments to suit their needs. However, all branding, trademarks, and references to marketingdecision.org must remain visible and intact within the application.
Ensure that you have Microsoft Excel and a PC running Windows before purchasing this application. This application uses advanced dynamic range calculations introduced by Microsoft. Please ensure that you are using a compatible version of Excel, such as Microsoft 365, or any version released after Excel 2019 which supports this feature.
We appreciate your feedback on how to improve this application. Feel free to share your suggestions with us at contact@marketingdecision.org
