Assists with a formula

[pstar]

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.

[Ads]

[twerppoet]

Formula 1

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

[pstar]

I have figured out my first formula







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

[twerppoet]

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.



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

[twerppoet]

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.

[twerppoet]

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

[twerppoet]

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 ), 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.

[pstar]

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.

[twerppoet]

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.

[twerppoet]

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.



Profits Worksheet.numbers