'============ E x p o n e n t i a l P r i c i n g M o d e l ================
' shg 2013, 2021
' Qty = the number of items
' First = price of the first item
' Last = price of the last item if you bought an infinite number
' k (1...inf) controls the rate of decrease from First to Last;
' larger decreases slower.
' For large k, k is the qty at which the unit price is
' 1 - 1/e (~63.2%) of the way from First to Last:
' k 1 - (1 - 1 / k) ^ k
' 2 75.0%
' 5 67.2%
' 10 65.1%
' 20 64.2%
' 50 63.6%
' 100 63.4%
' See MrE552202 - Exponential Pricing.xlsm for additional explanation
Function ExpUnit(Qty As Long, _
First As Double, _
Last As Double, _
k As Double) As Double
' shg 2013
' Returns the incremental price of the Qtyth item
If k <= 1 Or Qty < 0 Then
ExpUnit = CVErr(xlErrValue)
Else
ExpUnit = (First - Last) * (1 - 1 / k) ^ (Qty - 1) + Last
End If
End Function
Function ExpTot(Qty As Long, _
First As Double, _
Last As Double, _
k As Double) As Double
' shg 2013
' Returns the total price for Qty items
If k < 1 Or Qty < 1 Then
ExpTot = CVErr(xlErrValue)
Else
ExpTot = (First - Last) * (1 - (1 - 1 / k) ^ Qty) * k + Qty * Last
End If
End Function
Function ExpDisc(Qty As Long, _
First As Double, _
Last As Double, _
k As Double) As Variant
' shg 2013
' Returns the total Discount for Qty items
' i.e., Net Price = Qty * First * (1 - ExpDisc(...))
If Qty <= 0 Then
ExpDisc = 0
ElseIf k <= 1 Or Qty < 0 Then
ExpDisc = CVErr(xlErrValue)
Else
'ExpDisc = 1 - (Last / First + (1 - Last / First) * (1 - (1 - 1 / k) ^ Qty) * k / Qty)
ExpDisc = 1 - ExpTot(Qty, First, Last, k) / (Qty * First)
End If
End Function
Function ExpDisc2(Qty As Long, _
MaxDisc As Double, _
k As Double) As Variant
' shg 2013
' Like ExpDisc, but requires only the max discount
' i.e., Net Price = Qty * First * (1 - ExpDisc(...))
If Qty <= 0 Then
ExpDisc2 = 0
ElseIf k <= 1 Or Qty < 0 Then
ExpDisc2 = CVErr(xlErrValue)
Else
ExpDisc2 = 1 - ExpTot(Qty, 1, 1 - MaxDisc, k) / Qty
End If
End Function
Function NetDisc(ListPrice As Double, _
DiscZero As Double, _
DiscInf As Double, _
k As Double) As Variant
' shg 2013
' Returns the Net Discount for ListPrice
' Applicable to cases where the discount is based on
' total order size rather than item quantity.
' DiscZero = Lowest discount (ListPrice = 0)
' DiscInf = Discount for a bazillion dollar order
' k = parameter that controls shape
' For large k, k is the ListPrice at which discount is 1/e
' (~ 36.8%) of the way from DiscZero to DiscInf.
If k < 1 Then
NetDisc = CVErr(xlErrNum)
Else
If ListPrice <= 0 Then
NetDisc = DiscZero
Else
NetDisc = DiscInf - (DiscInf - DiscZero) * (1 - (1 - 1 / k) ^ ListPrice) * k / ListPrice
End If
End If
End Function
