Calculating decreased cost with increasing quantity

Question

I have a hand made table I've been using to give customers price per unit on my items, which gives a better price for the more items that they buy. My sample table right now

I need to keep the maximum price at $3.82 and the minimum price at $0.52 (for this example) and have some way to calculate exact price for quantities in between these brackets. If the values in the middle need to be modified a bit, that's fine as long as the margin of error is slim enough.

What I'm trying to accomplish is similar to linear regression and predicting values on a line of best fit, which I am comfortable doing on a straight line, but this is a curve. I can not figure out any way to get the equation for that curve, or figure out how to search for answers on how to find it.

Answer 1

Well I would not normally do arbitrary cure fitting since to chose a good candidate functional form requires some knowledge of the underlying process. But given that I take it that this is a hand generated price model I will have a go.

Fist thing to do is because of the orders of magnitude range of quantity to use logarithms to the base 10 of the quantity as the independent variable in the fitting process.

Many curves can be fitted quite well by rational functions, so that is what I will try. Initially I will fit a model of the form:$$ P(Q)=\frac{1+a\;\log_{10}(Q)}{u+v\;\log_{10}(Q)} $$ I used the Solver in Gnumeric (an open source spread sheet) to fit a curve of this form to your data, minimising the sum of square errors between your data and the models prediction. I weighted the first and last to force agreement between the model and data at the ends of the range. This gave a poor fit so I went to a model of the form:$$ P(Q)=\frac{1+a\;\log_{10}(Q)+b\;(\log_{10}(Q))^2}{u+v\;\log_{10}(Q)+w\;(\log_{10}(Q))^2} $$ This gave what looks to me an adequate fit with $a$ and $v$ both zero. So the final model was:$$ P(Q)=\frac{1+0.02481\;(\log_{10}(Q))^2}{0.03156+0.20417\;(\log_{10}(Q))^2} $$

The table and plot below shows the data and model results

Blue line your data, Pink line fitted model