What's new
Apple iPad Forum 🍎

Welcome to the Apple iPad Forum, your one stop source for all things iPad. Register a free account today to become a member! Once signed in, you'll be able to participate on this site by adding your own topics and posts, as well as connect with other members through your own private inbox!

Assists with a formula

pstar

iPF Noob
Joined
Feb 28, 2012
Messages
11
Reaction score
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.
 

twerppoet

iPad Fan
Joined
Jan 8, 2011
Messages
24,150
Reaction score
15,397
Location
Milton-Freewater, OR
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
 
OP
P

pstar

iPF Noob
Joined
Feb 28, 2012
Messages
11
Reaction score
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
 

twerppoet

iPad Fan
Joined
Jan 8, 2011
Messages
24,150
Reaction score
15,397
Location
Milton-Freewater, OR
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. ;)
 

twerppoet

iPad Fan
Joined
Jan 8, 2011
Messages
24,150
Reaction score
15,397
Location
Milton-Freewater, OR
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

iPad Fan
Joined
Jan 8, 2011
Messages
24,150
Reaction score
15,397
Location
Milton-Freewater, OR
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
 

twerppoet

iPad Fan
Joined
Jan 8, 2011
Messages
24,150
Reaction score
15,397
Location
Milton-Freewater, OR
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.
 
Last edited:
OP
P

pstar

iPF Noob
Joined
Feb 28, 2012
Messages
11
Reaction score
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
 

twerppoet

iPad Fan
Joined
Jan 8, 2011
Messages
24,150
Reaction score
15,397
Location
Milton-Freewater, OR
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
 
Last edited:

twerppoet

iPad Fan
Joined
Jan 8, 2011
Messages
24,150
Reaction score
15,397
Location
Milton-Freewater, OR
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
 
Last edited:
OP
P

pstar

iPF Noob
Joined
Feb 28, 2012
Messages
11
Reaction score
0
twerppoet said:
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.

<img src="http://www.ipadforums.net/attachment.php?attachmentid=10513"/>

I have downloaded it but I cannot get it to function, it seems to be just a picture?
 
OP
P

pstar

iPF Noob
Joined
Feb 28, 2012
Messages
11
Reaction score
0
I figured out how to open it in numbers......
 

twerppoet

iPad Fan
Joined
Jan 8, 2011
Messages
24,150
Reaction score
15,397
Location
Milton-Freewater, OR
Good.

My brain kicked in early this morning and I figured out how to get past the part of the equation I was stuck on, and I got the same result Wolfram Alpha gave me. Heck, I figured out how to simplify the equation a bit more. It did not take calculus after all. If you are interested I can show the work. It might come in handy if your royalty rate ever gets changed.
 

Gtatler

iPF Novice
Joined
Dec 5, 2011
Messages
487
Reaction score
69
Location
UK
Silly question, but may I ask which apps you are all using ? I don't recognise any of the screen shots.
 

Rockbottom

iPF Novice
Joined
Jun 15, 2011
Messages
112
Reaction score
7
Location
Montreal Canada
Numbers is the name of the app. With fancy formatting.

Sent from my iPad2 3G 32 gig (not JB'ed) / using iPF
 

Most reactions

Latest posts

Top