I have two tables. One with information on commissions payable for selling items on different websites. The other holding items which I would like to create a sale price for.
Please see both tables:
Channel <- c("DIRECT", "EBAY", "AMAZON")
Commission_percentage <- c(.1, .5, .4)
Min_commission <- c(0, 0, 2)
Processing <- c(0.05, 0.05, 0.03)
Commission_table <- as.data.frame(cbind(Channel, Commission_percentage,
Min_commission, Processing))
The commissions table above shows commission percentages for each website, but shows that AMAZON will charge £2 minimum for each sale. It also shows another % charge for payment processing. The table below shows 5 items which are to be sold. It shows the website and also the amount paid for the item previously (cost).
Item <- c("A1", "A2", "A3", "A4", "A5")
Cost <- c(10, 14, 18.85, 20, 5)
Channel <- c("DIRECT", "EBAY", "AMAZON", "AMAZON", "AMAZON")
Price_table <- as.data.frame(cbind(Item, Cost, Channel))
I need to create a column for the Price_table which shows the "break-even" price for each item. I need to find the applicable commission charges depending on the channel on which the item is being sold, and for the Amazon items, I need to make sure that the minimum commission of £2 is charged if the standard commission percentage is calculated to be less than this.
To clarify, the break-even price needs to be; Cost + max(commission_percentage * cost, min_commission) + (processing*(cost + max(commission_percentage * cost, min_commission)))
Ideally this needs to be memory efficient as the database is very, very large. Hence why I haven't just gone for a merge function and removed the extra columns once the price is calculated.
Many thanks, Dan
Aucun commentaire:
Enregistrer un commentaire