Pricing model based on specific amounts

[bruce24444]

I'm trying to create a pricing model that changes the price based a curve by volume.
As you buy more units, the price per unit is less but I have key points with specific values and am having trouble creating a formula to calculate the variables in between

For example
1650 units are $14,700
2750 units are $19,185
3650 units are $20,878
4350 units are $20,358

If you buy 2000 units, the price would be _____________ based on the curve between 1650 units and 2750 units

 

[dcardno]

There are two ways of looking at this - are you trying to have the incremental total cost increase linearly between the 'fixed' stipulated values, or the unit costs decrease linearly? They will come to slightly different results.
Assuming you want the unit prices to reduce linearly, your 1,650 unit order is at 12.21/unit, and your 2,200 order is at 10.83/unit. That's a reduction of 1.39 ea over an volume increase of 550 units, or ~0.25 per hundred. Using the same layout you have (and only dealing with the "painted" costs (the other ones will be analogous) insert some additional columns:
In column C insert the unit costs:

• C2 contains the formula =B2 / A2 (the unit price at the lowest volume)
• C13 contains =B13 / A13 (the unit price at the highest volume in this tier)
• insert a new column D with calculated unit prices:
• D3 contains = $C$2 + ($C$13 - $C$2) / ($A$13 - $A$2) * ($A3 - $A$2)
• that formula calculates the unit price by taking the base price in C2 (for the lowest volume) and then adding the incremental price per unit times the increase in volume above the lowest unit volume - since that 'price increase' is negative, it is actually subtracting 0.2526/unit for every 100 unit increase.
• Copy that formula down to D13
• In column E multiply the unit volume by the unit price, to get the order total.

Sorry I couldn't paste a mini-spreadsheet, but Excel is acting up, and won't let me install the XL2BB add-in.

This will be easier with a UDF I wrote a few years ago to do this same sort of work (piece-wise linear interpolation).

Option Explicit

Function Interpolate(X As Double, XRange As Range, YRange As Range, Optional InterpType As Integer = 0) As Variant

' Function returns interpolated value of "Y" for supplied X, based on supplied ranges of known
' X and Y values.  Optional "InterpType" argument controls how function responds to X arguments
' outside the known X values:
'   If InterpType is:
'   = 0: returns an error (default)
'   = 1: extrapolates based on last two X-Y pairs (either two highest or two lowest)
'   = 2: extrapolates based on first and last X-Y pair (full range of supplied values)
'   = 3: extrapolates based on first or last X-Y pair and the origin (0-0)
'    Other values return an error

' Function is based on the "InterpolateVLOOKUP" UDF developed by Myrna Larson, and published in the
' Excel Expert's E-Letter, which can be found at: http://www.j-walk.com/ss/excel/eee/eee002.txt

Dim blErr      As Boolean
Dim iBase      As Integer
Dim iComp      As Integer
Dim i          As Integer
Dim dX0        As Double
Dim dX1        As Double
Dim dY0        As Double
Dim dY1        As Double
Dim Temp       As Variant

On Error Resume Next

' ensure that XRange is sorted ascending so match function result is reliable
For i = 1 To XRange.Count - 1
    If XRange(i + 1) < XRange(i) Then blErr = True
Next

Temp = WorksheetFunction.Match(X, XRange, 1)
If IsError(Temp) Then
    Interpolate = CVErr(Temp)
Else
    iBase = CInt(Temp)
    Select Case iBase
        Case 0
            'match function did not find a match - X =< min(XRange)
            Select Case InterpType
                Case 0
                    If X = XRange(1) Then   'X is not "less than" lowest value in range, it is equal
                        iBase = 1
                    Else                    'X is IS < lowest value in range - represents an error
                        blErr = True
                    End If
                Case 1
                    iBase = 1
                    iComp = 2
                Case 2
                    iBase = 1
                    iComp = XRange.Count
                Case 3
                    iBase = 1
                    iComp = 0
                Case Else
                    blErr = True
            End Select
        Case XRange.Count
            'match returns last value in XRange, so X is >= max(XRange)
            Select Case InterpType
                Case 0
                    If X <> XRange(XRange.Count) Then    'X is IS > highest value in range - represents an error
                        blErr = True
                    End If
                Case 1
                    iComp = iBase - 1
                Case 2
                    iComp = 1
                Case 3
                    iComp = 0
                Case Else
                    blErr = True
            End Select
        Case Else
            'match returned position of value next larger than X, within XRange
            iComp = iBase + 1
    End Select
    dX0 = XRange(iBase)
    dY0 = YRange(iBase)
    If X = dX0 Then
        Interpolate = dY0
    Else
        dX1 = XRange(iComp)
        dY1 = YRange(iComp)
    End If
    If blErr = True Then
        Err.Raise Number:=11
        Interpolate = CVErr(Err)
        'return "div0" error, to ensure that error propagates through s/sheet calculations
    Else
        Interpolate = (X - dX0) / (dX1 - dX0) * (dY1 - dY0) + dY0
    End If
End If

End Function

To use this, would create a table with the 'fixed' volumes and unit prices in increasing order (this would be rows 2, 13, and onward of your data, with the unit cost for each specified combination). The function will return the unit cost for any given order volume, from which you can then extend the order total