Colored Cosmetics is a mineral cosmetics company that ships their custom-blended products all over the world. The company produces various colors of lipsticks, eyeliners, eye shadows, blushes, and foundations. You are the operations manager for the company and your goal is to determine how many units of each product to produce each month to maximize revenue.
Operational data from the last three months is recorded and can be found in the ch3_P7cosmetics file, which can be downloaded from the companion website (www.informit.com/title/9780133760354 > Downloads > here). This data consists of average processing time, costs of raw materials, and monthly demand for the last three months. There are currently 46 different color product combinations. Costs of producing each product vary depending on the different types of pigments used in the particular colors. There is a different cost for each product because certain pigments are more costly than others. A total of $3,000 is available every month to purchase raw materials. Also, a staff member puts in 10 hours per week (40 hours per month) at a labor cost of $12 per hour. The cosmetic facility wants to limit its production capacity to no more than the maximum demand for each line during the last three months.
1. Using a pivot table, process the Excel file data to calculate the average processing time, cost of raw materials, and maximum monthly demand for each cosmetic product.
2. Prepare an Excel template that calculates the values of the average net profit for each cosmetic product, average time usage per each cosmetic, total profit for a given product, and actual usage of labor hours for a given production mix. Assume an initial production level of one unit for each product.
3. User Solver to set up the objective function and constraints and generate an optimal solution.
4. Analyze results using the Sensitivity Report and the Limits Report.