We have items which are marked up according to the cost price and also according to the size.
We use reference sizes Small = 0.12, Large = 0.385. Reference sizes are constants in the system. The user sets as many reference cost points as they wish. For example:
Cost = 5.00, Size <= Small Net = 6.75, Size >= Large Net = 9.45
Cost = 40.00, Size <= Small Net = 52.00, Size >= Large Net = 72.00
Cost = 80.00, Size <= Small Net = 100.00, Size >= Large Net = 140.00
Cost = 140.00, Size <= Small Net = 168.00, Size >= Large Net = 238.00
The cost of the item to be marked up can be any positive decimal (ie can fall between the reference costs or above or below).
The size of the item to be marked up can be any positive decimal (ie can fall between the reference sizes or above or below).
EDIT: Where the cost falls between reference costs, Small Net would fall between Small Net for the cost below and Small Net for the cost above; Large Net would fall between Large Net for the cost below and Large Net for the cost above.
For any given cost at any given size, how do we calculate the net?
I have a cool idea:
Initially generate random numbers using randbetween(1,140) for 1000 costs. To the side of it generate random numbers using round(rand(),2) for 1000 sizes. Bucket for each size and cost to the Net. Once you get that use Multiple regression of Net (Y) over Cost ( X1) and size ( X2) and find the betas. Now you can estimate the Net for any combination of cost and size using the betas. Images are shown below with formulas
Good Luck
The R^2 is 95% and does a good job.
Goodluck.