Modeling advice: How to build item level discounts for set time periods
Looking for help and advice on how to build out a discount function in our model.
My company manufactures products and sells them to stores, for this example let's say our customers are Target and Walmart.
Our business will give special discounts on specific items (~10 items) for specific time periods, like May 2nd through June 5th. They also might extend the discounts, so that 5/2 to 6/5 discount is now 5/2 to 6/19. These discounts will be for a specific customer and specific items (we have a customer list and an item list in the model). The discounts seem to be $5 off each item for the time period, but I want the flexibility where it could also be a percentage discount.
Our sales order data is pretty straight forward, each line will give information of the item sold, the customer, the transaction date.
Ideally, we could have a system where a sales manager comes in and enters in the discount, but for now will be the model administrator.
How would you build this out? Target has a $5 discount on items A B C D from 5/2 to 5/19, Walmart has a 5% discount on items B C D E from 5/5 to 6/2.
My thoughts/confusion: I'm not sure it makes sense to have some sort of boolean in the sales order module to detect if there should be a discount, bc we'd have to keep adding line items (example: if Date is XXX, customer is XXX, item is XXX). I am not sure if we need to basically have a list of discounts where you add a list member and then connect it to a customer and items over dates. Maybe we have a boolean check for a specific discount thats added to a discount list where you check off the customer and the items, then calculate the sales for the date range (boolean check the dates?) and then calculate out a total rebate amount? I am not sure.
Would love some help or thoughts, thanks!
Answers
-
Hi @jakesachs
Below is a possible way of looking at this which I think is similar to what you were thinking:
3 modules – 2 inputs and 1 calculation.
Discount Master – holds the discount type, date range and value. You could use a UX form to create this and also use DCA for value entry validation.
Have a list item for each type of discount. We do something similar for Trade Promotion Planning.
Discount Allocation – use a Boolean to set the customers and products the discount will apply to.
Discount Calculation - based on the above plus the sales data, you can calculate the discount by day by customer by product. I’m working on the assumption that the sale data is available by day.
Hope this is useful to you. Let me know what you think.
Regards
Bill
2
