Sign up, and CougarBoard will remember which categories you want to view. Sign up
Apr 25, 2024
1:50:23pm
HBCoug All-American
Excel Question: Attempting to weight how much one factor impacts overall change
I've been working through market share exercises trying to determine how much growth/decline in one supplier's results impacts the overall blended share from one period to another.

Let's say we have anywhere from 13-16 tracked suppliers for whom we calculate our market share compared to competitors. Our share with one supplier changes each quarter based on sales over total available market for a period. I then take a weighted average to determine what our overall share position is with all 13-16 tracked suppliers.

A crude example:

supplier A: our sales grew 5% while that supplier's available market declined 5% ... we gained 100bps of share (let's say went from 15% to 16% QoQ)
supplier B: our sales declined 10% while that supplier's available market declined 12% ... we gained 20bps of share because our sales decelerated more slowly than the market did
supplier C: our sales declined 6% while the supplier's market grew 10% ... we lost 150bps of share
Overall share of the 13 suppliers combined went from 19.4% to 18.4% (a drop of 100bps overall)

I'm trying to figure out how much of the overall 100bp drop was due to supplier C dropping 150bps or supplier A gaining 100bps?

I don't know if this is best addressed by a regression tool or some kind of 2-step rate/volume exercise. Any thoughts?
HBCoug
Bio page
HBCoug
Joined
Jan 28, 2002
Last login
May 5, 2024
Total posts
4,334 (1 FO)
Messages
Author
Time

Posting on CougarBoard

In order to post, you will need to either sign up or log in.