2024: Week 45 - SuperBytes Stock
Challenge by: Holly Jones
This is the final challenge that DS43 prepared for us earlier this year - over to Holly:
SuperBytes are looking to change how stock is reordered at stores. The current policy is to record the quantity of stock they have remaining on a Sunday, after the store closes. If they have 10 or fewer items remaining, they will place an order with the supplier for that product, to get them back to 30 items in stock.
Rather than applying such a blanket approach across products, SuperBytes would like to understand how many items are sold on average each week and how frequently stores are currently ordering more stock from suppliers, to see if there could be benefits in making changes to how they order stock.
Inputs
There are 3 inputs for this challenge:
- Available Stock
- Product
- Supplier Data
Requirements
- Input the data
- Join the tables together and remove unnecessary fields
- Identify weeks where orders were placed for new stock
- This occurs whenever the Quantity Available is 10 or less
- Calculate how much Stock was ordered in these weeks
- SuperBytes policy indicates they should order as many items as they need so their stock level is back to 30
- e.g. if the Quantity Available is 5 then they would order 25 items
- Calculate the Quantity Sold each week, taking into consideration the new stock that has been ordered
- Across the dataset calculate the Total Quantity that has been sold for each Product in each Store, as well as the Avg Quantity Sold per Week and Avg Order Frequency
- i.e. if the Order Frequency is 2 then this would mean the Store is ordering once every 2 weeks on average
- Round these values to 1 decimal place
- Output the data
Output
- 6 fields
- Store
- Category
- Product
- Total Quantity Sold
- Avg Quantity Sold per Week
- Avg Order Frequency
- 100 rows (101 including headers)
After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14 & @TomProwse1
You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!
