View Question
Q: Word problem - develop equation. ( No Answer,   16 Comments )
 Question
 Subject: Word problem - develop equation. Category: Computers > Algorithms Asked by: barchibald-ga List Price: \$75.00 Posted: 21 Jul 2005 19:20 PDT Expires: 20 Aug 2005 19:20 PDT Question ID: 546449
 ```Given a sequence of volume and price (two values for each element of a sequence): 1000 units @ \$50 per 3000 units @ \$35 per 7000 units @ \$20 per etc. etc. etc. How can one derive intermediate volume and sequences at 1000 units (fill in 3000 to 7000 with 4000, 5000, 6000) with cost per units such that: 1.) increase in per unit cost results in an increase in volume & vice versa. Hard rule. 2.) such that all per unit costs of the derived sequences (the 4000,5000,6000 in the example above) as close in value to the average of the original sequence per unit costs, but still in keeping with number 1. 3.) must work with any set of values, assuming 1000 increments. Let me know any questions. I'm looking ideally for an equation or computer function (java, javascript..something I can read) into which I can plug the top and bottom values of a given "band" in the sequence (the 3000 & 7000 sequence and their price per unit values) and the desired 1000 increment and get back the price per unit for that increment. Interface something like FancyFunction(LowBand,LowBandPricePer,HighBand,HighBandPricePer,IncrementSize,4000) Questions?``` Clarification of Question by barchibald-ga on 21 Jul 2005 19:27 PDT ```In reference to the "average" in criteria number 2, I mean the average of the immediately higher provided item in the sequence and immediate lower. I'm referring to the average of the price per unit for 7000 units and for 3000 units, not to the average of the entire spectrum```
 There is no answer at this time.

 Subject: Re: Word problem - develop equation. From: shockandawe-ga on 22 Jul 2005 06:15 PDT
 ```You said... 1.) increase in per unit cost results in an increase in volume & vice versa. Hard rule. At the risk of sounding dumb, it appears that increasing the volume DECREASES unit cost and vice versa.```
 Subject: Re: Word problem - develop equation. From: barchibald-ga on 22 Jul 2005 06:29 PDT
 ```Yes...sorry. Decreases. An increase in volume should always DECREASE per unit and vice versa. Thanks shockandawe-ga.```
 Subject: Re: Word problem - develop equation. From: shockandawe-ga on 22 Jul 2005 07:23 PDT
 ```Imagine graphing your unit costs as a function of Volume. Volume on the X axis, unit cost on the Y axis. You have two points for instance (1000,50) and (3000,35) You're formula just needs to be the straight line between the two points. Remember back to basic algebra ... given 2 points (x1,y1), (x2,y2) the line connecting them is: y - y1 = m(x - x1) m = (y2-y1)/(x2-x1) FancyFunction(LowBand,LowBandPricePer,HighBand,HighBandPricePer,Volume) fancyfunction = (HighBandPricePer - (LowBandPricePer)/(Highband - LowBand))*(Volume-LowBand) + LowBandPricePer In this example fancyfunction(1000,50,3000,35,1000)=50 fancyfunction(1000,50,3000,35,2000)=42.5 fancyfunction(1000,50,3000,35,3000)=35 This advice was free.```
 Subject: Re: Word problem - develop equation. From: shockandawe-ga on 22 Jul 2005 07:26 PDT
 ```OOPs screwed up the parenthesis.. fancyfunction = ((HighBandPricePer - LowBandPricePer)/(Highband - LowBand))*(Volume-LowBand) + LowBandPricePer```
 Subject: Re: Word problem - develop equation. From: barchibald-ga on 22 Jul 2005 07:34 PDT
 ```You are ignoring one of the criteria: 2.) such that all per unit costs of the derived sequences (the 4000,5000,6000 in the example above) as close in value to the average of the original sequence per unit costs, but still in keeping with number 1. for example, a result that improves your particular example: fancyfunction(1000,50,3000,35,1000)=50 CHANGE to 49.9 fancyfunction(1000,50,3000,35,2000)=42.5 Keep the same fancyfunction(1000,50,3000,35,3000)=35 CHANGE to 35.9 is a better answer, but still not optimized for criteria number 2 but does meet criteria number two.```
 Subject: Re: Word problem - develop equation. From: barchibald-ga on 22 Jul 2005 07:36 PDT
 ```The example above assumes top and bottom bands of 2000 and 5000 respectively. Algorithm must optimize the "squeeze" (i need a better vocabulary) toward the average of the cost per unit of the top and bottom bands without preventing the ordered descending of cost-per-unit as volum increases.```
 Subject: Re: Word problem - develop equation. From: shockandawe-ga on 22 Jul 2005 07:55 PDT
 ```There is no optimum. You can squeeze the price range as close to the average as you'd like. Whether they are all within one cent of the average or one billionth of a cent you can always make a function closer. For example it can easily be made so that fancyfunction(1000,50,3000,35,1000)=42.5000001 fancyfunction(1000,50,3000,35,2000)=42.5 Keep the same fancyfunction(1000,50,3000,35,3000)=42.4999999 Decide How tight do you want to squeeze it and I'll give you a formula. But note "As tight as possible" isn't really going to work, because the computer at some point will have to round it to the average.```
 Subject: Re: Word problem - develop equation. From: shockandawe-ga on 22 Jul 2005 07:58 PDT
 ```Do you want the whole price range to be "Increment Size" ? That is certainly possible.```
 Subject: Re: Word problem - develop equation. From: barchibald-ga on 22 Jul 2005 10:53 PDT
 ```Holy crapola, I left off a serious piece of criteria. I've been thinking about this and I assumed you were sharing my brain. The other criteria is: "An increase in volume must also create in increase in price." So...based on my example and your suggestion: Provided 1000 units @ \$50 per Provided 3000 units @ \$35 per = \$100,500 Derived 4000 units @ \$27.6 = \$110,400 Derived 5000 units @ \$27.5 = \$137,500 Derived 6000 units @ 27.4 = \$164,400 Provided 7000 units @ \$20 per = \$140,000 (!!!) Note that we broke my third rule. I'll increase the payment to \$75 as part apology part...uh...something else, like...guilt. Thanks for your patience - I've been wondering why you were walking me through my 8th grade math class.```
 Subject: Re: Word problem - develop equation. From: shockandawe-ga on 22 Jul 2005 11:23 PDT
 ```barchibald-ga, I'm not a google researcher, at any time a researcher can lock this question, attempt to answer it, and I become irrelevant. I'm just a dude who likes to be helpful, and I don't get paid for it. Incidently-- My profession is developing pricing / cost equations for military acquisitions -- So I am fairly confident that if I don't understand your problem as it is posed, then others won't either. The real researchers will tend to ignore hard-to-understand questions, because there is a risk that they could spend a lot of time on it, and have solved the wrong problem. If you and I work through the details so that a ga researcher then feels confident enough to tackle it -- so be it --- I will still have been successfull in being helpful. I apologize if I offended you by walking you through eighth grade math. I'll be thinking about your problem - just to be kind to a stranger, and I'll let you know what I come up with. I hope a GA-researcher will give you a full answer now that the problem is more fully defined.```
 Subject: Re: Word problem - develop equation. From: barchibald-ga on 22 Jul 2005 11:30 PDT
 ```You don't get paid if you give me my answer? I'm happy to pay you for a solution one way or another, and will take the risk that i'll have to double pay in the event that it gets locked. Here is a reiterated version of my question. I can be found at barchibaldga - at - unsaltedbutter.com . Given the following information (should work for any information): Volume=1000, Price Per Unit=\$40 Volume=3000, Price Per Unit = \$35 Volume=6000, Price Per Unit = \$20 What is an algorithm (equation, ideally!) that : 1.) Can calculate the Price Per unit at all "missing" 1000 volume units (in the example above 2K, 4K,5K) 2.) Ensures that in increase in volume results a.) an increase in total cost (volume x price per unit) and b.) a decrease in per unit. This must be true both within the derived areas (the 3-6k area) as well as across (the 5-7k area) 3.) Maximizes the proximity in price per unit of the derived price per units of the derived volumes to the average of the price per unit of the upper and lower provided bounds. I want the price per unit to "bubble" around this average the maximum extent possible without breaking rules 1 and 2.```
 Subject: Re: Word problem - develop equation. From: shockandawe-ga on 22 Jul 2005 14:50 PDT
 ```There's several issues to deal with. First I'd like to make the case that the unit cost function is going to be a straight line. On an interval [LB,UB] the function that minimizes the absolute distance of the function value from its mean is a line of slope zero passing through the mean. But, that function doesn't pass the criteria that increased volume decreases the unit cost (since the unit cost would be a constant.) So... The function we are looking for has a slope greater then zero, at all places. Furthermore, if you imagine a tiny interval, upon which the slope is as small as possible, over the next interval, if the slope gets bigger, the distance from the mean is increasing. That's a bad thing -- so the optimal unit cost function won't ever change slope larger then the smallest slope possible. A function that's slope never changes must be a straight line. So generically, we'll say... UCF(x) = mx+b now the total cost function is TCF=x*UCF(x) = x*(mx+b) The total cost function is a downward opening parabola (since m<0 {strictly decreasing UCF} and b>0 {since the UCF is non-negative} ) You specify that the Total cost function must be strictly increasing. Picture the parabola TCF = x*(mx+b) it has zeroes at 0 and x=-b/m Its vertex is at x=-b/2m Hence the interval upon which the TCF is strictly increasing is when x<-b/2m. Let us specify UUB (stands for Upper-Upper bound) as a parameter we can define. UUB=the point at which the TCF stops being strictly increasing. Now, I have 2 equations in 2 unknowns. Since I want the UCF to pass through the average value of the given quantity cost intervals. Yavg = m*Xavg+b and also know that UUB = -b/2m Writing those two equations in matrix form Xavg 1 Yavg 2UUB 1 0 Reduced row echelon form gives 1 0 -Yavg/(2*UUB-Xavg) 0 1 2*UUB*Yavg/(2*UUB-Xavg) m = -Yavg/(2*UUB-Xavg) b = 2*UUB*Yavg/(2*UUB-Xavg) Here's the function in VBA for excel -- with the arguments mostly as you requested. Public Function FancyFunction(LB_Qty As Double, LB_Dol As Double, UB_Qty As Double, UB_Dol As Double, UUB As Double, X) Dim Qty_Avg As Double Dim Dol_Avg As Double Dim Intercept As Double Dim Slope As Double Qty_Avg = (UB_Qty + LB_Qty) / 2 Dol_Avg = (UB_Dol + LB_Dol) / 2 Slope = -Dol_Avg / (-Qty_Avg + 2 * UUB) Intercept = 2 * UUB * Dol_Avg / (2 * UUB - Qty_Avg) FancyFunction = Slope * X + Intercept End Function Notice UUB is a controllable parameter.. You control exactly where the Total Cost Function stops being strictly increasing. You will find that the problem with the straight line that passes through the end points is that the slope is too great! -b/2m < 7000. It turns out, making the line as shallow as possible garuntees that the cost function is strictly increasing. In other words if you want to make the costs extremely close to the average set UUB in the function Higher -- not lower). Play around with it and lets talk about it more, its kind of interesting.```
 Subject: Re: Word problem - develop equation. From: barchibald-ga on 22 Jul 2005 18:29 PDT
 ```Definately landed on the shape I'm looking for, but not quite "optimized" as you note. I've put together a simple web page that allows you (me) to play a bit. Find it at http://alpha dot remedyint.com/price2.cfm (fix the url). You'll get a divide by zero error if you don't fill in the values well. Further, the UUB factor at the bottom is applied to all bands indiscriminately and is a multiple of the upper bound (1 = UpperBound). Seems that the challenge is to find the optimal UUB for each band. I can of course use your logic here to do some programatic trial-and-error and find the optimal UUB to apply to each derived element in the band and then just do so. That is not very graceful and i'd prefer to solve for it. (or have some help doing so!). It does perplex me a bit. Must be a way to solve for the fact that small price per unit changes over large bands result in more "problems". I'll play a bit as well and see what I can come up with.```
 Subject: Re: Word problem - develop equation. From: shockandawe-ga on 22 Jul 2005 20:36 PDT
 ```As you read this, please allow for the possibility that I am totally misunerstanding very important aspects of what you have said. In fact, I am assuming I am missing something, so don't be offended by my patronizing tone. Ok. You said... 3.) Maximizes the proximity in price per unit of the derived price per units of the derived volumes to the average of the price per unit of the upper and lower provided bounds. I want the price per unit to "bubble" around this average the maximum extent possible without breaking rules 1 and 2. If you removed the criteria that the unit costs must be strictly decreasing. then wouldn't the "optimum" solution on each interval be the average cost? for example... for the set Qty: 1000, 40\$ Qty: 5000, 35\$ Define: UC(x) == 37.5\$ (no matter what the x value) TC(1000)= 37.5 * 1000 TC(2000)= 37.5 * 2000 TC(3000)= 37.5 * 3000 TC(4000)= 37.5 * 4000 TC(5000)= 37.5 * 5000 Obviously, Total cost is strictly increasing. and Obviously, the unit price is optimized with respect to the difference between it and the average. (It's identically equal to the average!) The only problem is that the unit costs are "non-increasing with qty" and you specified "strictly decreasing with qty" So the next-to-optimal solution will be the line that passes through the average, that has a decreasing slope, but is as close to zero slope as possible. However, I can't imagine this is useful for you... For instance set UUB extremely high in the fancyfunction for these parameters. Set UUB crazy high -- like 100,000 Qty Unit Cost Total Cost 1000 \$37.88 \$37,881 2000 \$37.69 \$75,381 3000 \$37.50 \$112,500 4000 \$37.31 \$149,239 5000 \$37.12 \$185,596 6000 \$36.93 \$221,574 7000 \$36.74 \$257,170 8000 \$36.55 \$292,386 9000 \$36.36 \$327,221 10000 \$36.17 \$361,675 So... The unit costs are Extremely close to the average on the 1000-5000 interval. The Total cost is strictly increasing, and the unit costs are strictly decreasing. Does it really buy you anything to get closer to the average? Then increase UUB to 1,000,000. There is no limit to how close you can set the unit cost function to the average. There is no optimum solution -- you have to decide how close is close enough. It strikes me very strange that you don't want the unit cost function to actually hit the end points. However you clearly told me that you don't want it to. Let me know if you figure out where we are on different pages.```
 Subject: Re: Word problem - develop equation. From: barchibald-ga on 22 Jul 2005 22:13 PDT
 ```Aaaaah. The bounds supplied are fixed/unchangeable. Soo...in the example you provide: Qty: 1000, 40\$ Qty: 5000, 35\$ 3000 = 37.5 2000 = the closest number we can get to 37.5 but that obeys the rules. ditto for 4000 And...although the bounds are fixed, the rules of ascending/descending volume/price-per-unit should apply to the entire multi bound spectrum. Does that clear things up? All we're doing is calculating the derived values between the bounds, but preserving the bounds themselves.```
 Subject: Re: Word problem - develop equation. From: sethahoyt-ga on 13 Aug 2005 00:12 PDT
 ```The question you are asking has no precise answer, as I believe shockandawe has indicated. Your question can only be answered in the *limit* of your specifications. Furthermore, this limit is degenerate and is probably of little value to you, as shockandawe has also noted previously. Just so you understand, the limiting case I describe is as follows: Given your initial values, for the lower range (1000-3000), the least volume that will yield a total cost >= 50000 with unit cost = 42.5 (the average value) is 50000/42.5 = 1177 (rounded up). Of course this would result in the total cost being essentially unchanged from 1000 to 1177, fixed at 50000. To make this strictly increasing is trivial and does not alter the substance of the solution, which is why it is degenerate. The next critical point would occur at (3000 * 35) / 42.5 = 2470 (rounded down). For any volume above this, the total cost must remain fixed at 3000*35 = 105000. Therefore, the function (from 1000 to 3000) would be piecewise defined as: 1000 <= volume <= 1177 ===> unit cost = 1000*50/volume (total = 50000) 1177 < volume < 2470 ===> unit cost = 42.5 (average of 50 and 35) 2470 <= volume <= 3000 ===> unit cost = 3000*35/volume (total = 105000) This can be easily extended to all other ranges and volume increments as well as to any specified marginal increase in total cost.```