Iterative calculation. What cells are processed first?

[UML]

Hi all

I have a relatively simple formula which calculates the actual selling price of an item when adding commision and specifying how much you want to earn either in terms of a % or an amount. I have a working formula that simply add's on £0.01 to the selling price until the target profit amount is reached. PROBLEM: This works fine for small profit items but the moment you start working with large numbers the number of iterations becomes too much AND when you have 25000 lines in a spreadsheet it can take hours to run.

I then came up with a different solution. Instead of adding £0.01, I added the difference between the target profit and the actual profit. This should reduce the number of iterations required to something more realistic but now my answers are all over the place.

I have attached the spreadsheet with an explanation of everything. The top formula is the one that I am trying to get working while the bottom one is working but with too many iterations (try making the markup % to 2000 which in our industry is quite common).

I can't explain this unless you look at the spreadsheet but I think I know what is happening and I have made a comment on the spreadsheet to this effect.

If you have ANY ideas then I would be forever greatfull.

Regards
Steve

[oeldere]

Question: your sell price is excluding the VAT (according to your example).

The other values are (I suppose excluding the VAT).

You abstract the selling price with the VAT.

The VAT is 16,77 * sell price (excl. VAT).

I wonder if the way of thinking is correct.

[UML]

Hi

If you reverse the calculation you will see it works out (I think I am about £0.02 out.

sell price (incl vat) - cost price (excl vat) - total vat on selling price - postage ex vat - commision ex vat = profit

If Im wrong then that part of the formula is easy to fix if I can just get the other piece to work.

Cheers

[MrShorty]

A couple of disjointed thoughts:

1)

I have a working formula that simply add's on £0.01 to the selling price until the target profit amount is reached. PROBLEM: This works fine for small profit items but the moment you start working with large numbers the number of iterations becomes too much AND when you have 25000 lines in a spreadsheet it can take hours to run.

This kind of "brute force" algorithm for root finding is the least efficient algorithm. It appears you already know this, but the key to solving this is a better, more efficient algorithm. I might first suggest you start with a review of some common root finding algorithms: http://en.wikipedia.org/wiki/Root-finding_algorithm

2) Successive approximations, or simple iteration, sounds like the algorithm you are trying to implement. You don't say what kind of difficulty you are having implementing this. This is usually a fairly simple algorithm for a spreadsheet (with iteration enabled). For something like this, add a column in N that adds A and M =A16+M16. Then enter =N16 into A16 and it should calculate it right up. The main difficulty I find with iterative calculations like this is implementing suitable "error" checking and convergence checking cells that will catch when the calculation fails to converge and allow you to reset the calculation when it goes wrong.

3) If successive approximations still requires too many iterations, you might re work your formulas to use more efficient algorithms like the newton raphson algorithm.

4) I don't have time to go through the algebra in detail, but I often find that one can avoid any kind of iterative approach if one works through the algebra to get a closed form solution. It seems that these kind of problems look something like y=0=x+x*r1+x*r2+constants, which, with a little bit of algebra work, can be solved explicitly for x [x=-(constants)/(1+r1+r2), for example]. A closed form solution like this will eliminate any need for an iterative solution, which will improve calculation times.

For something like this, I would probably suggest you start with 4 and see if the algebra works out.

[shg]

You don't need iteration, Goal Seek, Solver, or anything complex to solve this, just a little algebra, but I can't tell how the roll-up works. Is this correct?

commission = somePercent * sellPrice

VAT = 16.77% * sellPrice

profit = sellPrice - VAT - cost*(1+overhead) - commission


What is PP?

And you want to specify what, and calculate what?

[UML]

Hi

The problem is not the formula. That part works.

Think about it this way.. (remember that I am increasing my selling price until a particular amount of profit is reached)

If I have a selling price which is made up of variables such as commissions etc then it will calculate to a particular amount of profit. If this profit is lower than my target profit then I would add the difference between the target profit and the actual profit to the selling price and run the formula again. The higher selling price will result in the variables changing eg. vat amount, commission amount etc which means that I still might not be at the target profit but by rerunning this formula a few times I will end up where the difference between the target profit and the actual profit will become 0 and wont need processing any more.

The reason that the bottom solution works is because I can keep adding £0.01 to a particular cell and this runs in a loop and keeps increasing BUT I can't do that will a value that keeps changing.

BTW the PP is for packaging and postage

Believe it or not me typing this out have given me an idea. I will let you know if it works or not.

Cheers

[UML]

Okay, I got it working with the iteration and it's now working in a lot few iterations which is great so now I want to try an algebraic function to see if I can get rid of the iterations completely.

Remember that the goal is to calculate the selling price

This is the basic problem
Sell = cost + target profit + vat (sell/1.2)+ commission ((sell/100)*x%) + postage

since both the vat and commission need the original sell price to be calculated this leaves me in a hole.

Any ideas?

Thanks

[MrShorty]

Since we are going to (correctly) make this an algebra problem, and we have some uncertainty in how to do the algebra, I would probably first suggest a good refresher on how to solve these kinds of problems. This is a pretty good tutorial (note that this is page 3 of a 4 page tutorial): http://www.purplemath.com/modules/solvelin3.htm I chose this particular page because your algebra problem is almost identical to the last 5 or 6 examples she works through.

As a refresher: the tutorial examples show, consistent with my algebra instruction years ago (and I hope your algebra instruction), that the first two steps (not necessarily in this order) to a problem like this is to 1) combine like terms and 2) move all the x's (sell in your equation) to one side of the equals sign and move everything else to the other side.

Is that enough of a nudge to get you started, or do you need further help refreshing your algebra skills?

[shg]

	A	B	C
1	comm%	15.00%	B1: Input
2	VAT%	16.77%	B2: Input
3
4	cost	$ 9.00	B4: Input
5	postage	$ 1.00	B5: Input
6	target profit	$ 10.00	B6: Input
7	sell	$ 29.31	B7: =(B4+B5+B6)/(1-B1-B2)
8
9	check:
10	cost	$ 9.00	B10: =B4
11	profit	$ 10.00	B11: =B6
12	comm	$ 4.40	B12: =B1*B7
13	postage	$ 1.00	B13: =B5
14	VAT	$ 4.92	B14: =B2*B7
15	total	$ 29.31	Yup