Assists with a formula

Discussion in 'iWork Forum' started by pstar, Feb 28, 2012.

  1. pstar

    pstar
    Expand Collapse
    iPF Noob

    Joined:
    Feb 28, 2012
    Messages:
    7
    Thanks Received:
    0
    Need help with a numbers formula. Actually two formulas. The first is to determine profit after paying 7% royalties so a cost column a sales column and a column showing profit after royalty. Then a formula that would allow a user to enter a specific after 7% royally profit and have the sales price auto adjust. Any advice would be greatly appreciated.
     
  2. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,447
    Thanks Received:
    4,301
    Formula 1
    SalesForm_1.jpg
    The parentheses in this formula are not necessary. Like the second formula the priority of calculations (multiply before addition) will take care of it. I included them for my own reference at first, and did not bother to remove them.

    Formula 2
    SalesForm_2.jpg
     
  3. pstar

    pstar
    Expand Collapse
    iPF Noob

    Joined:
    Feb 28, 2012
    Messages:
    7
    Thanks Received:
    0
    I have figured out my first formula

    image-1546517148.jpg



    image-577098130.jpg

    I did a poor job explaining what I was looking for I hope these two picture help......... The second formula that I needed he with would have cost filled in and % filled in and then numbers would calculate the sale price taking into account the 7% royalties of the top...... I'm not even sure where to begin????

    Thanks
     
  4. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,447
    Thanks Received:
    4,301
    Well, I can help you begin.

    PR + Profit
    PR% = Profit Percentage

    You've already got the formula for Profit: PR = SELL - COST - (SELL x .07)

    Take the the formula for Percentage: PR% = PR / COST

    Substitute the formula for PR in the PR% formula for: PR% = ((SELL - COST - (SELL x .07)) / COST

    Now solve for SELL = ???


    Unfortunately my algebra is no longer up to the task. I tried, three times. The closest I could get was:

    (PR% x COST) + COST = SELL - (SELL x .07)

    SELL needs to be reduced to a single variable on the right side of the formula (and no entry on the left). I can almost feel my old math teacher beating me over the head with the solution, but only the scars from the tuler remain. Then again, I've probably made a basic mistake somewhere along the line.

    SalesFormula.jpg

    Oh well, it was fun trying to relive high school math, in a masochistic sort of way. ;)
     
  5. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,447
    Thanks Received:
    4,301
    I restated the problem to something that looked familiar to me. (SELL x 1) - (SELL x .07) and fed it into Wolfram Alpha and got SELL x .93

    So your formula (providing I made no other mistakes) should be:

    = (PR% x COST + COST)/.93

    I'm going to test it now.
     
  6. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,447
    Thanks Received:
    4,301
    Ok, it seems to work. At first I though I'd blown it, because the numbers didn't match your example, so I recreated the your first table and ran it backwards. Looks like whatever you are using to computer percentage in your first screen shot is incorrect (or maybe it is not a formula).

    SalesFormula_Final.jpg
     
  7. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,447
    Thanks Received:
    4,301
    For whatever reason I continued to play with this until I had an almost usable spreadsheet. If you want to download it and compare, or get a better look at what I did (and tell me I still didn't get it right :D ), you can get it here.

    Ooops. That did not work. Back to good old reliable Box.

    http://www.box.com/s/4qalqu1lgsom9048pl9c


    Edit: Unable to leave things alone. I fixed the percentage averaging formulas at the bottom, so they don't average the null (0%) entries. Could still cause slight errors if you sold something for no profit. Made the table a bit longer. Added a mostly for fun column that shows whether you meet, exceed, or miss a sales goal.

    I envision this being used for something like eBay tracking, though there are probably better tools on eBay. Or maybe tracking garage sale profits.

    But I am done, really done, and going to bed. I mean it this time. 12:37 AM here.

    Ok, 12:57 AM, and am shutting off computer NOW.
     
    #7 twerppoet, Feb 29, 2012
    Last edited: Feb 29, 2012
  8. pstar

    pstar
    Expand Collapse
    iPF Noob

    Joined:
    Feb 28, 2012
    Messages:
    7
    Thanks Received:
    0
    I own and manage a franchise, being a franchise I owe my home office 7% off the top. Sometimes when pricing needs to be competitive i have to run a bunch of numbers this worksheet would be a huge asset.
    The math only works one way. Meaning 21.47 plus 7% is not the same as the reverse........ 22.97 minus 7 % is not 21.47. This makes things complicated and it doesn't seem to become easier with my iPad and numbers.... . I'm not even sure at this point it's possible to do. ? I don't believe the screen shots I've seen solve the issue. I hope with the recent clarification and this extra screen shot will help clear up my dilemma. Thanks a ton for the effort put forth.

    image-2713190957.jpg
     
  9. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,447
    Thanks Received:
    4,301
    But the last formal does work. Notice that it does not use the .07 royalty figure.

    SELL = (PROFIT% x COST + COST)/.93

    I double checked it by running several sets of numbers through it, then plugging the results back into the other equations that computed your profit percentage. Which is:

    PROFIT% = PROFITAR/COST

    or the long way

    PROFIT% = (SELL - COST - SELL x .07)/COST

    I can't explain exactly why it works, because I plugged the problem equation into Wolfram Alpha for a solution, and got the .93 constant back. I suspect it takes a bit of calculus to solve the equation properly. However if you download the spreadsheet I linked, and play with it (and look at the equations) I think you will find that it works.

    SalesSheet.jpg
     
    #9 twerppoet, Feb 29, 2012
    Last edited: Feb 29, 2012
  10. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,447
    Thanks Received:
    4,301
    Edit: Ok, fixed. I had the Mark Up formulas wrong.

    Edit: The Mark Up computation sheet is not working properly. Have to do some trouble shooting. Arrggg.


    Ok, I've revised the worksheet to be more inline with what you want to do. Don't ask me why, guess I was just bored. :)

    It now consists of three tables. Each table includes these fields.

    Cost = Cost of item
    Sell = What the item does or will sell for
    Mark Up = Percentage the item is marked up from Cost
    Profit AR = Profit after royalties and Cost subtracted.
    Profit % = Percentage of profit after royalties and Cost are subtracted.

    Table one computes all other fields based on Cost and Sell price.

    Table two computes all other fields based on Cost and Mark Up (Percentage)

    Table three computes all other fields based on Cost and Profit %

    To the best of my knowledge all formulas work. They can be double checked by entering the output of one table into the input of the other and making sure that all values match.

    There are probably a few formatting mistakes I didn't catch.


    ProfitWksht.jpg
    Profits Worksheet.numbers
     
    #10 twerppoet, Mar 1, 2012
    Last edited: Mar 1, 2012

Share This Page



Search tags for this page

percentage formula

,

quickoffice formula

,

quickoffice formula to subtract

,

subtract in numbers

,

what formula in mac numbers to calculate profit