Calculating Promotional & Base Volume

[anabanana253]

Hi

It might be more of a mathematical question. I would like to calculate how many units were sold in full price vs how many units were sold in promotional price based on the facts below:

Base Price (usually sold at) = £7.98
Promo Price (during this period) = £7.00
AUP (Average Unit Price) = £7.22
Units Sold at Base Price = ?
Units Sold at Promo Price = ?
Total Units Sold = 1,891

I've tried a few calculations but couldn't get it to work. I want to insert it as a formula later in excel

Thanks,
Anna

[Pete_UK]

It's quite straightforward algebra. Suppose the number of base-price units sold is represented by b (which is probably cell B4 the way you have laid it out above), and the number sold at the promotional price is called p (B5). You know from B6 that the total number sold is 1891, so you can say that:

b + p = 1891

You can write this as:

p = 1891 - b

or the other way round.

You also know that the average price is 7.22, and that is made up of the (income from base-price sales + income from promotional-price sales) all divided by the number sold. The income from base-price sales is b * 7.98 (from B1), and the other income is p * 7, so you have another equation:

7.22 = ( (7.98 * b) + (7 * p) ) / 1891

So, you now have two equations and two unknowns, so you can solve this by substituting the equation for p given above into this later equation, and that will give you the value for b (and hence p).

I trust this is enough of a hint, without me solving it for you.

Hope this helps.

Pete

[anabanana253]

I see what I did here - I've added an extra pair of parenthesis and that's why my values were higher than the total volume.
Thanks Pete!

[Pete_UK]

Glad to be of help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete