Google Answers Logo
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
Answer  
There is no answer at this time.

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

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy