Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    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/excel-q...les-units.html
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    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

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the info

  4. #4
    Gold Lounger Maudibe's Avatar
    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".
    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
    Attached Images Attached Images
    Last edited by Maudibe; 2013-05-28 at 15:53.

Posting Permissions

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