Part 1: Optimization
OBJECTIVE: Analyze the model constraints and develop a bag assignment process
Inbound tab:
- This active inventory list includes data on total on hand inventory, products in the May 2019 glam bag program, and other products that are contingency items. If there is any issue with a May 2019 glam bag product, we can use a contingency item from the same product category as a substitute.
- Each customer receives one glam bag which contains five products only.
- We are shipping to 18,000 customers, but we aim for an additional 1% extra units of each product for accommodations when products get damaged in transit or the glam bag shipment does not reach the customer.
Constraints tab:
- This tab shows two types of constraints:
A bag combination cannot have two or more products of the same product category. For example, the first constraint in the Constraints tab shows that a bag combination cannot include both the Pacifica Lipstick in Nudie Red and Pacifica Lipstick in Sweet One.
Another type of constraint is based on a group of categories. For example, the Color constraint shows that each bag can only contain between 3 and 4 products from lipstick, blush, brush, and eye shadow categories.
- These are hard constraints that must be followed.
Quiz Answers tab:
- This table contains the quiz answers for 18,000 customers.
Please answer the below questions and describe the steps you took and assumptions you made.
Submit both your responses and your Excel file.
1(a) Fill out columns G, H and I in the Constraints tab. What are the data inconsistencies and how would you resolve
them?
1(b) What are the constraint flaws in this model? What constraints would you change or remove to resolve these
issues?
1(c) What is the customer breakdown by each skin tone and hair type from the Quiz Answers tab?
1(d) Describe how you would assign the bags to the customers. What other information would improve the bag
assignment process? Do not assign bags to the customers.
Part 2: Warehouse Shortage
OBJECTIVE: Create a set of recommendations for the following scenario.
It is 5/1/19 and we already assigned each user a bag combination and sent the assignments to the warehouse for processing. The warehouse just let us know that they have discovered a product shortage. What next steps do you recommend? What are the business and customer impacts to consider? Include any assumptions you made. Note that there is no data for Part 2. This is intended to be an exploratory investigation and a thought exercise.