1. 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/excel-q...les-units.html

2. 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

3. Hi Zeddy

Thanks for the info

4. 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".
This will create a circular reference because you are placing a calculated value based on column B back into Column 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((C2-B2)/(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 consultant

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•