Each scenario corresponds to a system of linear equations:
Hi @keith2511
).
Thank you for spending the time to reply. I didn't explain myself fully but I now have a solution. Best wishes.
Thank you so much for your reply. I've plugged your formula into Excel with some real data and it works!!!! Apart from when the scenario is 100% sales of product B - but I can add some additional formulae to compensate for that. I have been struggling for days to find a solution, so thanks again. Isn't maths wonderfulHi @keith2511
You could also use the formula I have created below to calculate (not "estimate") the proportion of sales of product B. I presume that what you mean is that you wish to know what percentage (of the total number of both units sold) the quantity of Product B sold represents.
For example, in Scenario 3, Product B accounted for 5 out of 10 units sold, so it was 50% of the sales.
If you want to know the proportion of the Value of sales, then that's a different proposition which my formula doesn't address (but could be modified to do so
First we need to define the variables:-
You know the unit price of Product A so let's call that: £A
and, similarly, let's call Product B's unit price: £B
You know the Total Value of the sales so we'll call that: £V
and you know the total number of units sold so let's call that: Z
I suspect you may wish to carry out this exercise when all of those variables take different values (from those listed in your table) but the formula works just as well if A = 10, B = 2, N = 10 and V only takes the four values in your Scenarios (£100, £84, £60 & £36).
Here is my formula:-
\(\displaystyle \sf\left(\Large 1\small \raisebox{0.25em}{$-\sf\frac{V-(B\times Z)}{Z\times(A-B)}$}\right)\times 100\%\)
And here is an example:-
If 12 of Product A are sold @ £5 (totaling £60) ⇒ A = 5
and 8 of Product B are sold @ £15 (totaling £120) ⇒ B = 15
Then Z = 20 (8 + 12) and total sales are £180 ⇒ V =180
Substituting A, B, Z & V into the formula we get:-
\(\displaystyle \quad\sf\left(\Large 1\small \raisebox{0.25em}{$-\sf\frac{180-(15\times 20)}{20\times(5-15)}$}\right)\times 100\%\)
\(\displaystyle =\left(\LARGE 1\footnotesize \raisebox{0.25em}{$-\frac{180-300}{20\times(-10)}$}\right)\times 100\%\)
\(\displaystyle =\left(\LARGE 1\footnotesize \raisebox{0.25em}{$-\frac{-120}{-200}$}\right)\times 100\%\)
\(\displaystyle =\left(\large 1-0.6\right)\times 100\%=0.4\times 100\%= \underline{\underline{40\%}}\)
So, in this case, Product B is 40% of the total sales (quantity), ie: 20 units were sold, 8 of them were Product B and 8 out of 20 is the same as 4 out of 10 or 40 out of 100 which is 40%
Hope that helps.
Please let us know if it does or explain how it fails to meet your requirements if it doesn't.
Cheers,
TH.
Forgive me, your formula does work with 100% sales of product B. Best wishes.Thank you so much for your reply. I've plugged your formula into Excel with some real data and it works!!!! Apart from when the scenario is 100% sales of product B - but I can add some additional formulae to compensate for that. I have been struggling for days to find a solution, so thanks again. Isn't maths wonderful
