Excel help..

[Manji]

I'm trying to tidy up a spreadsheet at work that contains some fudge formulas to give a near enough answer. But it's a bit beyond me as it contains cyclic formulas

So I have cost of job. Call that x.
On top of x, a levy is added, as well as a comission.
Then a fixed 12% markup is applied to all of that to get to sell. Call that y.
Then 15% is added for sales tax. Now we're at z.

Where I'm coming unstuck, is the levy is supposed to be 4% of the presales tax sell, (y)
And the sales commission is 2.5% of the after sales tax amount (z). Yes, I realise that this forms a presales tax component that then will get sales tax applied. But this is how the sales staff are paid.

So my issue is how write the formula in the cells for the levy and the commission, because excel doesnt like that they are being calculated from a figure that they are also a part of.

Any ideas?

[Amaya]

Whut? I don't even know how this can possibly be. I want to be paid commission based on sales tax which is based on commission which is based on sales tax which is based on commission. . .
Just put 0.047887 of x as the levy amount and 0.034425 of x as the commission amount?

[Manji]

Originally Posted by Amaya

Whut? I don't even know how this can possibly be. I want to be paid commission based on sales tax which is based on commission which is based on sales tax which is based on commission. . .
Just put 0.047887 of x as the levy amount and 0.034425 of x as the commission amount?

Prob is, the margin and the commission amounts can change, so don't want to do it with fixed figures..

[Amaya]

Once you fill in the rates and simplify just solve for y. Use that value of y (expressed as a value of x) to calculate the amount of the levy (expressed as a value of x) and the value of z (expressed as a value of x). Then use that value of z to calculate the amount of commission (again expressed as a value of x). It's not mathematically correct (unless I'm wrong in which case I'm right) but it should be close enough for reality. If you want a mathematically rigorous solution then I'm not gonna be much help.

[aegisdrgn]

Amaya, I think it would be more helpful to solve for y (the sell), as it makes calculating everything else easier. Besides, X is a known quantity.


This is classic plug and chug algebra with systems of equations.


Let's state the facts. The actual percentages don't actually matter during equation solving.
Capital letters are CONSTANTS, meaning they can be treated as numbers.
A = Commission rate: 2.5% = 0.025
B = Levy rate: 4% = 0.04
T = Tax rate: 15% more than 100% = 1.15
M = Markup: 12% more than 100% = 1.12
X = Cost of job = some constant = One miiiiiilllion dollars
y = presales tax sell amount in dollars
z = after sales tax amount in dollars
l = Levy amount in dollars
c = Commission amount in dollars


Some equations.

Cost of job + levy dollars + commission dollars, mark all that up, you get presales tax sell.
(X + l + c) * M = y

Calculate the after tax amount
z = T * y

Levy amount in dollars is a percentage of presales tax amount
l = B * y

Commission amount in dollars is a percentage of after sales tax amount
c = A * z

What do you need to know? Pick a starting point, and then using the other equations, substitute in until you only have 1 unknown - the one you want to solve.

Presales tax sell amount in dollars - y
Whatcha got? You don't know l, c, or y, but you want to find out y. You have two equations to replace l, c. Nice!

(X + (B * y) + (A * z)) * M = y

Well, shit, now you have a z in there. But wait, you have an equation that gets rid of z!

(X + (B * y) + (A * (T * y))) * M = y

Do some algebra, move some shit around, and simplify.

(M * X) / (1 - M * B - A * T * M ) = y

Awesome. The hard part is over, do the rest yourself (basically plug your numbers into excel, "define name" to be exactly the same as my variables, boom, done). I tested this in excel, pretty sure it works. If it doesn't, it's friggin midnight and I'm tired. Good luck.

[Amaya]

The problem is that the commission/levy will continually increase but because the rate at which they increase is getting less and less and is a very small value this is probably accurate enough. A more accurate answer would be slightly higher than what I came up with.
Edit: now that I've had a little sleep I realize that I'm wrong about it increasing and it just requires straight forward algebra to solve

[aegisdrgn]

Originally Posted by Amaya

The problem is that the commission/levy will continually increase but because the rate at which they increase is getting less and less and is a very small value this is probably accurate enough. A more accurate answer would be slightly higher than what I came up with.

The issue with that assumption is that sales people don't want every penny of their hard earned money, and that accountants like unbalanced books. Good try though.

[Amaya]

Originally Posted by aegisdrgn

Amaya, I think it would be more helpful to solve for y (the sell), as it makes calculating everything else easier. Besides, X is a known quantity.


This is classic plug and chug algebra with systems of equations.


Let's state the facts. The actual percentages don't actually matter during equation solving.
Capital letters are CONSTANTS, meaning they can be treated as numbers.
A = Commission rate: 2.5% = 0.025
B = Levy rate: 4% = 0.04
T = Tax rate: 15% more than 100% = 1.15
M = Markup: 12% more than 100% = 1.12
X = Cost of job = some constant = One miiiiiilllion dollars
y = presales tax sell amount in dollars
z = after sales tax amount in dollars
l = Levy amount in dollars
c = Commission amount in dollars


Some equations.

Cost of job + levy dollars + commission dollars, mark all that up, you get presales tax sell.
(X + l + c) * M = y

Calculate the after tax amount
z = T * y

Levy amount in dollars is a percentage of presales tax amount
l = B * y

Commission amount in dollars is a percentage of after sales tax amount
c = A * z

What do you need to know? Pick a starting point, and then using the other equations, substitute in until you only have 1 unknown - the one you want to solve.

Presales tax sell amount in dollars - y
Whatcha got? You don't know l, c, or y, but you want to find out y. You have two equations to replace l, c. Nice!

(X + (B * y) + (A * z)) * M = y

Well, shit, now you have a z in there. But wait, you have an equation that gets rid of z!

(X + (B * y) + (A * (T * y))) * M = y

Do some algebra, move some shit around, and simplify.

(M * X) / (1 - M * B - A * T * M ) = y

Awesome. The hard part is over, do the rest yourself (basically plug your numbers into excel, "define name" to be exactly the same as my variables, boom, done). I tested this in excel, pretty sure it works. If it doesn't, it's friggin midnight and I'm tired. Good luck.

This is what I did on paper but I didn't feel like typing it out on my phone :p

[aegisdrgn]

Now I'm curious, I kinda want to see your paper now. =)

[Sideways?]

Start at the end and work forwards, fix the sale price and work from there to determine whether it is high enough to cover the job cost, then your post-sales value is directly related to the sale value and commissions and levy can be calculated from there...

[Amaya]

Originally Posted by aegisdrgn

Now I'm curious, I kinda want to see your paper now. =)

I'll show you mine since you showed me yours kinda thing? :p But it's several papers in and torn out of notebooks (that already had stuff written in them) and it isn't exactly formatted or even written in any sort of order. I tend to write things in the space between other things I've written. I would be most embarrassed to reveal just how disorganized my writings are :eek:
It can be fun to go back and read notes from classes I took several years ago. Some of my more interesting notes were from a chemistry class. Between and around and inside all the chemical notes/nonsense I drew random shapes and objects and made lists of car specs/parts/plans.

[Manji]

Originally Posted by aegisdrgn

Amaya, I think it would be more helpful to solve for y (the sell), as it makes calculating everything else easier. Besides, X is a known quantity.


This is classic plug and chug algebra with systems of equations.


Let's state the facts. The actual percentages don't actually matter during equation solving.
Capital letters are CONSTANTS, meaning they can be treated as numbers.
A = Commission rate: 2.5% = 0.025
B = Levy rate: 4% = 0.04
T = Tax rate: 15% more than 100% = 1.15
M = Markup: 12% more than 100% = 1.12
X = Cost of job = some constant = One miiiiiilllion dollars
y = presales tax sell amount in dollars
z = after sales tax amount in dollars
l = Levy amount in dollars
c = Commission amount in dollars


Some equations.

Cost of job + levy dollars + commission dollars, mark all that up, you get presales tax sell.
(X + l + c) * M = y

Calculate the after tax amount
z = T * y

Levy amount in dollars is a percentage of presales tax amount
l = B * y

Commission amount in dollars is a percentage of after sales tax amount
c = A * z

What do you need to know? Pick a starting point, and then using the other equations, substitute in until you only have 1 unknown - the one you want to solve.

Presales tax sell amount in dollars - y
Whatcha got? You don't know l, c, or y, but you want to find out y. You have two equations to replace l, c. Nice!

(X + (B * y) + (A * z)) * M = y

Well, shit, now you have a z in there. But wait, you have an equation that gets rid of z!

(X + (B * y) + (A * (T * y))) * M = y

Do some algebra, move some shit around, and simplify.

(M * X) / (1 - M * B - A * T * M ) = y

Awesome. The hard part is over, do the rest yourself (basically plug your numbers into excel, "define name" to be exactly the same as my variables, boom, done). I tested this in excel, pretty sure it works. If it doesn't, it's friggin midnight and I'm tired. Good luck.

Thanks for that dude. It's worked a treat.

[aegisdrgn]

Originally Posted by Manji

Thanks for that dude. It's worked a treat.

You're welcome.