Spreadsheet: 168461d1342462878-gross-margin-rate-and-mix-formulas-gross-margin-scenario-analysis_mathforum
Sheet: simple example

 | PY = prior year
 | CY = current year
 | GM - gross margin
 | Rev = net revenue
 | ** numbers used in example for demsonstration purposes only
 | Formula A |  |  |  |  |  |  |  |  | [NAME]
 | Total Category |  |  |  |  |  |  |  |  | Total Category
 |  | FY12 | FY13 | YoY |  |  |  |  |  |  | FY12 | FY13 | YoY
 | Rev |  |  |  |  |  |  |  |  | Rev
 | GM |  |  |  |  | Rate | Mix | Total |  | GM |  |  |  |  | Rate | Mix | Total
 | GM% |  |  |  |  |  |  |  |  | GM%
 | To calculate RATE impact in Total |  |  |  |  |  |  |  |  | To calculate RATE impact in Total
 | To calculate MIX impact in Total |  |  |  |  |  | Recompute what the  CY GM % would have been had you used PY GM % and [NAME]; sum components; divide new GM$ by Total CY Rev; subtract newly computed GM% from CY GM% (CY GM % of 50.0% less recalculated 52.9% ) |  |  | To calculate MIX impact in Total |  |  |  |  |  | Recompute what the  CY GM % would have been had you used PY GM % and [NAME]; sum components; divide new GM$ by Total CY Rev; subtract PY GM% from the newly computed GM% (Recalculated 52.9% less PY GM% of 51.4% )
 | To calculate RATE impact per component |  |  |  |  |  |  |  |  | To calculate RATE impact per component: |  |  |  |  | Take category GM % change Yoy; multiply by size of business (formula below)
 | To calculate MIX impact per component |  |  |  |  |  | How does this Category's GM% compare to the Total GM% in the CY; and how much did this Category's revenue mix change YoY |  |  | To calculate MIX impact per component: |  |  |  |  |  | How does this Category's margin in PY compare to Total ? And how much did this Category's Revenues change in relation to others
 | RUNNING |  |  |  |  |  |  |  |  | RUNNING
 |  | FY12 | FY13 | YoY |  |  |  |  |  |  | FY12 | FY13 | YoY | B/(W)
 | Rev | 50000000 | 100000000 | 0.32 |  |  |  |  |  | Rev | 50000000 | 100000000 | 0.32
 | GM |  |  |  |  | Rate | Mix | Total* |  | GM |  |  |  |  | Rate | Mix | Total
 | GM% | 0.4 | 0.5 |  |  |  |  |  |  | GM% | 0.4 | 0.5
 | GM % of Ttl |  |  |  |  |  |  |  |  | GM % of Ttl
 | [NAME] |  |  |  |  | This Category's GM% vs. Total in CY |  |  |  | [NAME] |  |  |  |  | This Category's GM% vs Total in PY
 |  |  |  |  |  | Rev mix % YoY |  |  |  |  |  |  |  |  | This Category's Rev growth as % of Total Rev
 | FOOTBALL |  |  |  |  |  |  |  |  | FOOTBALL
 |  | FY12 | FY13 | YoY |  |  |  |  |  |  | FY12 | FY13 | YoY | B/(W)
 | Rev | 100000000 | 300000000 | 0.35 |  |  |  |  |  | Rev | 100000000 | 300000000 | 0.35
 | GM |  |  |  |  | Rate | Mix | Total* |  | GM |  |  |  |  | Rate | Mix | Total
 | GM% | 0.6 | 0.4 |  |  |  |  |  |  | GM% | 0.6 | 0.4
 | GM % of Ttl |  |  |  |  |  |  |  |  | GM % of Ttl
 | [NAME] |  |  |  |  | This Category's GM% vs. Total in CY |  |  |  | [NAME] |  |  |  |  | This Category's GM% vs Total in PY
 |  |  |  |  |  | Rev mix % YoY |  |  |  |  |  |  |  |  | This Category's Rev growth as % of Total Rev
 | NSW |  |  |  |  |  |  |  |  | NSW
 |  | FY12 | FY13 | YoY |  |  |  |  |  |  | FY12 | FY13 | YoY | B/(W)
 | Rev | 200000000 | 300000000 | 0.15 |  |  |  |  |  | Rev | 200000000 | 300000000 | 0.15
 | GM |  |  |  |  | Rate | Mix | Total* |  | GM |  |  |  |  | Rate | Mix | Total
 | GM% | 0.5 | 0.6 |  |  |  |  |  |  | GM% | 0.5 | 0.6
 | GM % of Ttl |  |  |  |  |  |  |  |  | GM % of Ttl
 | [NAME] |  |  |  |  | This Category's GM% vs. Total in CY |  |  |  | [NAME] |  |  |  |  | This Category's GM% vs Total in PY
 |  |  |  |  |  | Rev mix % YoY |  |  |  |  |  |  |  |  | This Category's Rev growth as % of Total Rev
Recalculated GM% |  |  |  |  |  |  |  |  |  | Recalculated GM%
Run |  |  |  |  |  |  | * component's contribution to Total GM% impact |  |  | Run
FB |  |  |  |  |  |  |  |  |  | FB
NSW |  |  |  |  |  |  |  |  |  | NSW