Results 1 to 4 of 4
Thread: Computing expected sales Units

20130525, 08:13 #1
 Join Date
 Feb 2008
 Posts
 1,420
 Thanks
 124
 Thanked 5 Times in 5 Posts
Computing expected sales Units
I have a spreadsheet containing the branch names in Col a and the actual profit figures achieved. Where a branch has not achieved the desired result, I would like to put in the desired profit in Col B. I would like to be able to determine based on the desired net profit in Col B, how many units I would need to sell using an average selling price or average gross profit
http://www.mrexcel.com/forum/excelq...lesunits.html

20130525, 10:27 #2
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,823
 Thanks
 135
 Thanked 482 Times in 459 Posts
Hi Howard
If your target net profit is 15% higher for any Branch, then the number of units required to meet that target for any branch is 15% higher than the existing unit sales for that Branch.
So, if Branch 3 sold 20 units with a net profit of 76,007, then to achieve the NP Target of 87,408 it needs to sell an additional 3 units (i.e. 20 x 15% = 3 units)
zeddy

20130525, 10:48 #3
 Join Date
 Feb 2008
 Posts
 1,420
 Thanks
 124
 Thanked 5 Times in 5 Posts
Hi Zeddy
Thanks for the info

20130527, 11:12 #4
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,634
 Thanks
 115
 Thanked 649 Times in 592 Posts
Howard,
The problem is in the way you set up the spreadsheet. Entering the Net Profit in column B affects the NP Target in Column C because the formula for NP Target in col C=B2*1.15. Therefore, whatever value you put in the Net Profit (Col B), it will always be 15% less than the NP Target value and the branch will never acheive its goal. Ther request then states,"Where a branch has not achieved the desired result, I would like to put in the desired profit in Col B".
The Net Profit and NP Target Values both need to be number values, not formulas. Now that they are independent from each other you can write formulas based on them. The formula would be:
=IF(MAX(B2,C2)=C2,ROUNDUP((C2B2)/(B2/B18),0),"")
which is saying:
If the branch did not meet its goal (MAX(B2,C2)=C2) then find the difference of how much it needs to make (NP Target) minus how much it made (Net Profit) and divide it by the Net profit per unit (Np /#units sold). By rounding up, this will give you the minimum number of additional units that need to be sold to make up the difference in profit
Zeddy has demonstrated that as long as the NP Target has not been met and that the NP Target is 15% above the actual Net Profit, it can be measured by percentages. Nicely Done! But what if the Net Profit approaches or falls much shorter of the NP Target? The percentage may change.
HTH,
Maud
A very wise man has reminded me that any information that is offered as help should not be taken as financial advice or instruction. Please be responsible and cautious using any code provided. I for one am not a financial consultantLast edited by Maudibe; 20130528 at 15:53.