BridgePivot – An Automated Tool for Key Driver Analysis across Finance, Marketing, Big Data and More
Complete decomposition and generate Bridge & Contribution analysis in minutes with transparent and traceable processes.
Use Case
How has our profit margin been doing over the past few quarters?
Sales increased by 10%, but overall profit margin decreased by 7pt.
That's not good. Can you give me a breakdown and analysis report of the 7pt profit margin decline? I need to know what specific products and reasons caused this decline. It would be best if you could do a comprehensive analysis by region, product type, channel, or team across different dimensions. You can compare with different time periods like last year, the year before, or our budget. This way we can identify problems promptly and solve them immediately.
If it were just revenue analysis, that would be simple enough. But breaking down the 7pt profit margin decline to specific 1000 SKUs and knowing which factors—price, discount rate, cost, etc.—contributed how many pts to the overall decline, that's not an easy task. If we add regional, product type, and channel dimensions, it becomes even more complex. Then comparing with last year, budget, and other data versions—it's almost impossible...
Do you frequently encounter similar needs in your work? Do you spend a full day, or even days or longer (for multi-dimensional analysis) to reach an analytical conclusion? Does this analysis contain a lot of manual estimation, and the final aggregation of all impact factors doesn't accurately equal the 7pt profit margin decline, requiring manual adjustments? Do you always harbor doubts about whether the calculation process is 100% correct?
Please use BridgePivot to complete similar analysis in just minutes, getting accurate analytical results through algorithms and decomposing revenue, profit margin changes into the impact factors, different dimensions, and scenario comparisons you desire. This helps you pinpoint problems precisely, take timely action, and improve enterprise competitiveness.
Instructions
Using the BridgePivot tool requires Excel 2016 or higher, preferably the latest Microsoft 365 version. Older versions may cause the program to malfunction.
The following instructions use a "Electronics Stores" virtual dataset to demonstrate how to use the BridgePivot tool to automatically generate Bridge analysis and identify key driving factors.
Download Excel ExampleThe initial panel displays two buttons: "New Bridge File" and "Open Existing Bridge File"
1.1 Click "New Bridge File" button - Generates a new Excel file containing a new "Data" worksheet. Please enter the corresponding data in the "Data" worksheet for further analysis.
1.2 Click "Open Existing Bridge File" button - Select a previously used Excel file. This file should contain the relevant data and have Data Types configured.
In the Data worksheet starting from column B, enter data types in the first row, field names in the second row, and data in all rows from the third row onwards. Column A contains auto-generated identifiers for the first three rows that need data input. Column A itself has no impact on data and analysis.
In the first row of the "Data" worksheet, use the generated dropdown menu starting from column B to select the data type. The type depends on the meaning the data needs to represent. The program will automatically analyze based on the data type.
Data types are divided into 5 categories: Dimension, Key, SumY, SumN, Result. Each field must be defined with one data type, which can be selected using the dropdown menu.
Now using our simulated supermarket data as an example, let's explain the meaning of each data type. Each type is defined as follows:
For example: If "Product Name" is selected as Key, the tool will calculate the impact of each variable's change on the final result (Result) within this "Product Name" dimension.
For example: Quantity of different products in each row can be summed, and the total represents the total quantity of goods. Therefore, the Type of Quantity can be set to SumY.
SumY can contain direct numerical values or use formulas to calculate values through other fields. However, formulas can only use Excel's basic arithmetic operators: addition (+), subtraction (-), multiplication (*), division (/), left parenthesis "(", and right parenthesis ")". Formula Usage Guidelines
For example: Different products have different Prices. To calculate the average Price of all products, you cannot directly add Product A's Price plus Product B's Price to get the overall Price. Therefore, the Type of Price can be set to SumN.
SumN can contain direct numerical values or use formulas to calculate values through other fields. However, formulas can only use Excel's basic arithmetic operators: addition (+), subtraction (-), multiplication (*), division (/), left parenthesis "(", and right parenthesis ")". Formula Usage Guidelines
For example: If the Result field contains sales, then the cell should input = Price * Quantity. Or if it's margin%, then the cell should input = profit / revenue.
The formula in Result is crucial information that the tool uses to associate variables with results and analysis logic dependencies. Please ensure that each Result cell in every row contains a formula that can calculate the Result. The formula should be concise and clear. Result will analyze the impact of each variable in the formula on the Result, while variables not in the formula are not within the analysis scope. The Result formula should contain at least one field with Data Type SumY to enable aggregated analysis of data from different rows.
Result formulas can only use Excel's basic arithmetic operators: addition (+), subtraction (-), multiplication (*), division (/), left parenthesis "(", and right parenthesis ")". Formula Usage Guidelines
In the second row of the "Data" worksheet starting from column B to the right, enter field names. The meaning of the field names should correspond to the data types in the first row. Field names cannot be duplicated.
In the "Data" worksheet starting from the third row in column B, input specific data downwards.
After clicking "New Bridge File" or "Open Existing Bridge File" to open a file, blue buttons New Bridge and Refresh Bridge will appear on the panel.
3.1 New Bridge button - Generates new dropdown menus and Bridge worksheets and other analysis frameworks Baselined on the Data worksheet data for subsequent data analysis. (Note: Related worksheets such as Bridge worksheets and corresponding data in the original workbook will be deleted.) If there are any changes to the Data worksheet data, you must first click the "New Bridge" button to generate a new analysis framework, then: 1. Make selections in the new Baseline and Comparison dropdown menus; 2. Click the "Refresh Bridge" button to regenerate the required Bridge charts and Contribution Analysis.
3.2 Refresh Bridge button - After selecting options in the Baseline and Comparison dropdown menus according to analysis needs, click the "Refresh Bridge" button to generate the required Bridge charts and Contribution Analysis. Missing data or worksheets will trigger prompts; it's recommended to click New Bridge to regenerate a new Bridge.
The dropdown menu is divided into two areas: Baseline and Comparison
The fields in Baseline and Comparison dropdown menus contain exactly the same options. Users can filter which options serve as Baseline data and which serve as Comparison data in the dropdown menus, using these as the comparison and analysis data for both ends of the Bridge. Note: Initially, by default, all fields in the Baseline and Comparison dropdown menus are selected, so Baseline and Comparison get exactly the same dataset, resulting in a Bridge with no differences.
4.1 Baseline dropdown menu - Select the baseline data for comparison analysis. For example, we often use data from certain years as the comparison baseline, so here we select 2023.
4.2 Comparison dropdown menu - Select the Comparison data for comparison analysis. For example, here we select 2024 as the year of interest to compare with 2023.
The Bridge worksheet displays the final analysis results, including Bridge charts and Contribution Analysis tables.
5.1 Bridge Data Source - Starting from cell B3 in the upper left corner, field names and data will appear. This is the data used to generate Bridge charts.
5.2 Bridge Chart - Displays the Bridge analyzed and obtained after program execution. The blue bar on the left is Baseline, the middle shows the impact contributed by each variable, and the right bar is Comparison.
5.3 Contribution Analysis Table - Directly below the Bridge chart, a Contribution Analysis table will be displayed, showing the contribution of each Item in the Key field to overall changes under different factors.
Through the Bridge Chart and Contribution Analysis Table, you can quickly identify the main Items (SKUs) affecting changes and the factors causing changes (price? cost? or quantity mix?).
You can analyze different dimensions and different result data by modifying 2.1 Data Type Selection to choose which field is Key and which field is Result. You can also use the dropdown menus to filter which data serves as Baseline and Comparison, then click Refresh Bridge to generate updated Bridge charts and Contribution Analysis tables for analyzing actual business situations and proposing improvement suggestions.
6. SumY and SumN Calculation Rules
In the first row Data Type of the Data worksheet, numerical values are divided into two types: SumY and SumN, and are used in formulas in the Result column. To correctly analyze the data, we define some calculation rules for SumY and SumN:
SumY + SumY = SumY
SumY + SumN (will show error)
SumN + SumN = SumN
SumY * SumY = SumY
SumY * SumN = SumY
SumN * SumN = SumN
SumY / SumY = SumN
SumY / SumN = SumY
SumN / SumY = SumN
SumN / SumN = SumN
Use Same Formula for Each Column: The program will automatically copy the formula from the fourth row to the entire column after removing fixed cell reference symbols "$" to ensure all formulas in each column are identical.
6.4.1 In formulas, if parentheses are included, calculations inside parentheses are performed first according to the order of operations, and the result is used as a value to continue calculations using the above rules.
Example: (SumY + SumY) * SumN
Step 1: SumY + SumY = SumY
Step 2: SumY * SumN = SumY
Final result type: SumY
6.4.2 If the Result column formula references other cells that also contain formulas, the analysis will merge all nested formulas into one formula for analysis.
Example:
Column E formula: =B*C (Total Cost = Quantity * Unit Cost)
Column F Result formula: =D-E (Profit = Revenue - Total Cost)
After merging: The program will analyze =D-(B*C)
6.4.3 The program will automatically reorder variables and apply the distributive property based on formula structure for better data analysis.
Original formula: =SumY*(SumN1 + SumN2)
After processing: =SumY*SumN1 + SumY*SumN2
This helps analyze the individual contributions of SumY, SumN1, and SumN2 more clearly.
7. Process Worksheet
The Process worksheet contains the logic and algorithms for generating Bridge charts and data. You can intuitively see how these analysis results are calculated step by step, avoiding black box and inexplicable phenomena.
Bridge Analysis Calculation Logic and Principles
All variables are based on Baseline initial values. Each step changes one variable to the Comparison value and calculates the impact on each Key Item.
Example: Product A sales analysis
Baseline: Price=10, Quantity=100, Sales=1000
Comparison: Price=12, Quantity=80, Sales=960
Step 1: Only change Price: 10→12, Quantity remains 100, Sales=1200
Step 2: Then change Quantity: Price=12, Quantity=100→80, Sales=960
Calculate the impact of each variable change on the summary
Price Impact: 1200 - 1000 = +200
Quantity Impact: 960 - 1200 = -240
Total Change: +200 + (-240) = -40
Verification: 960 - 1000 = -40 ✓
After steps 7.1 to 7.2, replacing all variables from Baseline values to Comparison values, we obtain the impact of each influencing factor needed for the Bridge chart.
In the Contribution calculation table, we calculate the impact of each Key on the overall change under each factor variation.
Overall Price Impact: +500
Product A Contribution: +200 (40%)
Product B Contribution: +180 (36%)
Product C Contribution: +120 (24%)
Added Data represents newly added data compared to Baseline data (existing in Comparison data). Removed Data represents data deleted from Baseline data (not existing in Comparison). These two types of data generally need to be separated from other factor analysis first.
Scenario: Product mix change analysis
Baseline Data: Products A, B, C
Comparison Data: Products A, B, D (added D, removed C)
Added Data: Product D's contribution
Removed Data: Product C's impact (negative contribution)
These changes will be displayed separately, not mixed with price, quantity and other factors.
8. Other Worksheets
For the correct operation of the program, some other worksheets will be generated but hidden. Please do not modify these worksheets.
9. Data Security Assurance
All data exists only in local Excel files. The program runs only locally with no network communication during the analysis process.
10. Transparent & Explainable Analysis
Reject the ambiguous conclusions and inexplicability of AI analysis. The analysis calculation process is completely transparent and explainable.
Process Worksheet Transparency
Contains the logic and algorithms for generating Bridge charts and data. Every calculation step can be traced and verified.
Traditional Black Box Analysis:
❌ "AI model shows 7pt profit margin decline"
❌ Cannot explain specific calculation process
❌ Cannot verify result accuracy
Bridge Transparent Analysis:
✅ "Price factor: -3pt, Cost factor: -2pt, Quantity factor: -2pt"
✅ Every calculation step visible in Process worksheet
✅ All formulas and logic completely verifiable
Local Data Security
Zero network communication, all data processing completed locally, ensuring absolute enterprise data security.
Data Security Guarantee:
✅ Data never leaves local computer
✅ No need to upload to cloud servers
✅ No network connection required
✅ Complies with enterprise data security policies
✅ Avoids data breach risks
Precise Mathematical Calculation
Based on rigorous mathematical formulas, ensuring accuracy and reliability of analysis results.
Traditional Estimation Analysis:
❌ Manual estimation with potential errors
❌ Cannot precisely quantify impact of each factor
❌ Aggregated results require manual adjustment
Bridge Precise Calculation:
✅ Mathematical formulas guarantee 100% accuracy
✅ Each factor's contribution precise to decimal places
✅ Totals automatically balance, no adjustment needed
Efficient Analysis
Complete complex analysis that traditionally takes days in just minutes, dramatically improving work efficiency.
Multi-dimensional Analysis
Support in-depth driving factor analysis by product, region, time, and other dimensions.
Flexible Configuration
Adjust analysis dimensions and comparison scenarios anytime to meet various business analysis needs.