Hi there,

I have a problems to calculate a marketing mix. I thought it would be
straightforward to calculate and explain but it proves to be rather
hard. Probably Harlan will rip my head off but I will just give it a
try...
It is not really an explicit Excel-matter neither but I thought to
find the greatest number-jockeys over here!

The Case:
2007 2006
Volume Unit Sales Net Sales Volume Unit Sales Net Sales

Brand A Sugar 75.0 15.0 1,125.0 100.0 10.0 1,000.0
Sugarfree 130.0 9.0 1,170.0 80.0 7.0 560.0
SubTotal 205.0 2,295.0 180.0 1,560.0


Brand B Sugar 100.0 15.0 1,500.0 100.0 15.0 1,500.0
Sugarfree 100.0 8.0 800.0 60.0 10.0 600.0
SubTotal 200.0 2,300.0 160.0 2,100.0



I Would like to explain now my growth in Net Sales from '06 to '07
Net Sales 2006 3,660.0

Net Sales 2007 4,595.0

Increase in sales '06 to '07 935.0

How can this 935 USD be explained?

Incremental volume: formula?

Change In Product mix, ie. Sugar versus Sugarfree
formula?

Change In Brand mix, ie. Brand A versus Brand B
formula?

Change In Price formula?



I have tried to calculate the impact of those 4 components, but my sum
of those 4 does not equal to 935 ... the "gap" I want to explain.

Anybody any experience with this?
All help really appreciated!

If you want the file just drop a line!

Jen

Net sales is calculated, so I think you should only consider Volume and Unit
sales. It is not difficult to represent all Net sales products with "Volume *
Unit sales". The simplified table would look like this:



2007 2006 DIFF

Brand A Sugar 1125 1000 125
Sugarfree 1170 560 610
Brand B Sugar 1500 1500 0
Sugarfree 800 600 200

935


Also I don't see all four Ps included in your marketing mix, only price and
product, but from the available data, it looks like Brand A - Sugar is your
best seller, and you should consider repositioning Brand B - Sugar free.

If you need further assistance, please attach your xls and I'll give it a try
--
urkec


"Jen" wrote:

> Hi there,
>
> I have a problems to calculate a marketing mix. I thought it would be
> straightforward to calculate and explain but it proves to be rather
> hard. Probably Harlan will rip my head off but I will just give it a
> try...
> It is not really an explicit Excel-matter neither but I thought to
> find the greatest number-jockeys over here!
>
> The Case:
> 2007 2006
> Volume Unit Sales Net Sales Volume Unit Sales Net Sales
>
> Brand A Sugar 75.0 15.0 1,125.0 100.0 10.0 1,000.0
> Sugarfree 130.0 9.0 1,170.0 80.0 7.0 560.0
> SubTotal 205.0 2,295.0 180.0 1,560.0
>
>
> Brand B Sugar 100.0 15.0 1,500.0 100.0 15.0 1,500.0
> Sugarfree 100.0 8.0 800.0 60.0 10.0 600.0
> SubTotal 200.0 2,300.0 160.0 2,100.0
>
>
>
> I Would like to explain now my growth in Net Sales from '06 to '07
> Net Sales 2006 3,660.0
>
> Net Sales 2007 4,595.0
>
> Increase in sales '06 to '07 935.0
>
> How can this 935 USD be explained?
>
> Incremental volume: formula?
>
> Change In Product mix, ie. Sugar versus Sugarfree
> formula?
>
> Change In Brand mix, ie. Brand A versus Brand B
> formula?
>
> Change In Price formula?
>
>
>
> I have tried to calculate the impact of those 4 components, but my sum
> of those 4 does not equal to 935 ... the "gap" I want to explain.
>
> Anybody any experience with this?
> All help really appreciated!
>
> If you want the file just drop a line!
>
> Jen
>
>