Description
A clear and deep understanding of the market environment and trends can provide invaluable insights into business activities and competitive dynamics. These insights play a crucial role in identifying growth opportunities and informing future investment decisions.
It is essential that this market analysis be performed easily and simply, which is possible by keeping all market and company data available in one Excel file. This file should have the capability to select the scope and drill down into data as required to display and interpret results easily.
The proposed Excel product with macros is specifically designed to facilitate market trend analysis and comparative assessments for a company. It offers multiple capabilities to analyze the market by dates (using three Excel slicers for year, quarter, and month) and market segments to display results using graphs. It notably includes tailwinds and headwinds calculations for any segment configuration.
This product is available in three versions, varying based on the number of segment slicers available:
- Single-Segment Version: Includes one slicer, allowing data to be filtered along a single axis for segmentation.
- Two-Segments Version: Includes two slicers, allowing for multiple segment selections, for instance, across regions and P&L.
Four-Segments Version: Includes four slicers, allowing for multiple segment selections, for instance: region, countries, P&L, and sub-modalities.
Key Features of the Market Share & Tailwinds and Headwinds Tool
Dynamic Excel Slicers for Market Data
- Analyze the market by dates (year, quarter, month) and segments using Excel slicers.
- Filter data dynamically to focus on specific market segments or time periods.
Tailwinds and Headwinds Calculations
- Calculate tailwinds and headwinds for any segment configuration.
- Understand the impact of market growth or decline on your market share.
Market Share Visualization
- Visualize market share trends and growth drivers with customizable graphs.
- Compare market share across segments to identify opportunities and risks.
Multiple Versions for Flexibility
- Single-Segment Version: Filter data along a single axis.
- Two-Segments Version: Select segments across two dimensions (e.g., regions and P&L).
- Four-Segments Version: Analyze data across four dimensions (e.g., region, countries, P&L, sub-modalities).
How to Use the Tool
- Enter Your Data: Use the dedicated data entry tab to input market and company data.
- Select Segments and Dates: Use Excel slicers to filter data by segments and time periods.
- Analyze Tailwinds and Headwinds: Review the calculated tailwinds and headwinds for each segment.
- Visualize Results: Use the graphs to visualize market share trends and growth drivers.
Use Cases
Market Share Growth Analysis
- Analyze market share trends to identify growth opportunities and risks.
- Use tailwinds and headwinds calculations to understand market dynamics.
Segment-Level Insights
- Compare market share across different segments to identify strengths and weaknesses.
Use the tool to guide investment decisions and strategic planning
Advanced Features
Data Selection
- Select the number of lines for the table between 2 and 100.
- Erase all input data or only non-visible data as needed.
- Update names for axes and table legends.
Graph Customization
- Show or hide names on graph bubbles.
- Adjust bubble text size and relative bubble size.
- Display horizontal and vertical axis values on graph bubbles.
Color Schemes
- Set colors for bubbles by category or individually.
- Copy and save bubble color codes for consistency.
Metrics Display
For the selection of dates and segments, the table is created dynamically by simply changing the slicer selection (otherwise, press F9 if the tool is set for manual calculations). For each segment and the two selected time periods, the following metrics are displayed:
- Segment: List of selected segments per segment slicer(s).
- Market Past: Market total for the segment in the past period.
- Company Past: Company total for the segment in the past period.
- Share Past: Market share percentage in the past period.
- % Market Past: Share of the total market represented by this segment.
- Market Current: Market total for the segment in the current period.
- Market Growth %: Market growth percentage from the past period.
- Company Current: Company total for the segment in the current period.
- Company Growth %: Company growth percentage from the past period.
- Share Current: Market share percentage in the current period.
- % Market Current: Share of the total market represented by this segment in the current period.
- Share Gain/Loss: Market share gain (positive) or loss (negative) between the two periods.
- Contribution to Headwinds/Tailwinds: Impact of individual segments on total market share tailwinds and headwinds.
The total for the selected segments is displayed above the table of metrics, which can accommodate up to 510 lines of segment metrics. It sums all the metrics from the displayed segments, calculates market share for past and current periods, and determines the total effect of tailwinds and headwinds, impacting positively or negatively the total market share gain.
Additionally, the lines of this table can be filtered (using Excel filters), and the total for visible data will be recalculated, including the headwinds/tailwinds percentage. In that case, graphs will display only visible lines from the first 19 rows of results.
Headwinds/Tailwinds Percentage Definition
This is the market share gain or loss resulting from market growth or decline in each market segment:
- Positive Value: Indicates tailwinds supporting total market share growth.
- Negative Value: Indicates headwinds due to market mix effects negatively impacting the total market share gain.
Concretely, tailwinds and headwinds are calculated by considering the total effect on market share if the company achieved the same market share in every displayed segment as in the past period. This approach helps understand if a company is best positioned in faster-growing segments by having a larger relative share in segments growing faster (or declining less fast) than other market segments.
Graphs
Seven graphs are proposed in total, each configurable to display metrics independently. One graph is located on the “Results” tab, and the other six are on a dedicated tab named “Graphs.” These graphs are set as two-axis graphs with bubbles (format XY scatter), providing great flexibility. Two drop-down menus allow dynamic selection of axes values from twelve different metrics, and one drop-down menu allows selection of bubble sizes from eight metrics. Buttons allow for the selection of information, including “names,” “X values,” “Y values,” and “B values” (bubble metric).
By selecting parameters for these graphs, you can easily determine predefined formats preferred for discussions and presentations, where some graphs will be dedicated to specific “storytelling.” For instance, graphs can highlight:
- Market segments as a function of market growth or decline.
- Company positioning versus market segment growth.
- Company share gain or loss.
Metrics Calculation Details
Two periods can be selected to compare data from the first period with data from the second period. Each period is defined by selecting dates using three slicers each. This allows great flexibility since each slicer may include various dates along different scales. Slicers allow the selection of multiple dates at once, typically by keeping the control key down.
Additional slicers allow for the selection of market segments. Three versions of this Excel tool are proposed, differing only by this capability:
- Single-Segment Version: One segment slicer allows selection across all listed segments created when the data was populated.
- Two-Segments Version: Two segment slicers allow for various P&L or region selections. One slicer can be used for regions and the other for modalities. Alternate data organizations are possible.
- Four-Segments Version: Four segment slicers allow storing data hierarchically. For instance, a slicer can identify regions, another countries, a third P&Ls, and the fourth another organizational structure.
For the “Two-Segments” and “Four-Segments” versions, slicers can be hidden except for one, so all data under hidden slicers are aggregated. At least one segment slicer must remain visible. It is not possible to hide the slicer in the “Single-Segment” version but the total across selected segments is displayed.
Data Entry
Data is entered using a dedicated tab so that data cannot be inadvertently modified when handling the results and graphs tabs. It allows data to be entered gradually over time, and data is refreshed automatically with the “refresh all” button.
Dates entry uses three slicers to enter data periods (e.g., year, quarter, or year, quarter, and month) in all versions. Multiple fields can be used for the same slicer, allowing different period definitions in the same slicer. However, it is important to avoid double accounting that may occur if data is selected multiple times due to inappropriate selection.
Depending on the version, one to four segment columns can be defined. Data must be numeric for “Market” and “Company,” representing the market volume such that the ratio of Company over Market will represent the share. If data is incomplete – missing descriptions for dates and segments – or if there is a non-numeric value for “market” and “company,” an error message will prompt for correction.
Additional Information
For those who wish to display both orders and sales data in the same file, it is recommended to use a segment slicer to select either one. If comparing orders to sales, a date slicer can be allocated to orders or sales. However, it is recommended to use a waterfall analysis for comparison where orders and sales details can be discussed. It is unusual to obtain both market orders and sales for the total market with the same level of granularity across segments.
It is generally not wise to compare oneself with competitors on the same graph since data quality for one’s own data is usually very good, while data for competitors is not. However, one or multiple competitors can be displayed by allocating a segment slicer to competitor (and oneself) names. In that case, the market volume can be set to the same amount for all competitors for the same time period. To ensure consistency and avoid summing up market data across competitors, select only one competitor at a time. It is probably safe to allocate one version of this Excel file to independently analyze competition, allowing another file to be more detailed by segment and sub-segments for own company data.
The mathematical demonstration for tailwinds/headwinds calculation can be determined by considering an intermediary step between the “past” and “current” periods, where all segment shares are the same as in the “past” period for all segments when market volumes are the same as in the “current” period.
Links and References
Tailwinds and headwinds analysis depends on clear market structure. See Market Mix for the foundation behind these dynamics.
Understanding where growth and decline occur helps guide solution investment and portfolio choices. To place these insights in context, refer to our Solution Mix page.
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
