Problem
Description: Each year in May, P.A.M. Cosmetics (Professional Artistry Makeup) launches a selection of limited edition lipsticks. To ensure these lipsticks are in stock in time for the launch, orders must be placed with manufacturers in November. Because these lipsticks are limited edition and require 6 months of lead-time, P.A.M. Cosmetics will not reorder more units if they completely sell out. It costs P.A.M. Cosmetics $5.30 to produce each lipstick, and the selling price is $16. In the event that there are leftover units after the launch period (1 month), there will be a price markdown of 40 percent. In the past, demand for these yearly limited edition lipsticks has been 58,000 units. However, P.A.M. Cosmetics has initiated several social media campaigns to create more hype for new products.
• Construct a spreadsheet model to analyze this situation. Use an order quantity of 70,000 and a demand of 62,000 for your base case values. Be sure to explain what assumptions you made while constructing your model.
• Suppose P.A.M. Cosmetics orders only 62,500 units, and assume that no units are sold after the launch period ends. At what demand level will P.A.M. Cosmetics break even? (Round your answer to the nearest whole number)
Task
I. The Profit for the Best Case Scenario (sell 62,000 units during launch and remaining 8,000 units post-launch) and the Worst Case Scenario (sell 62,000 units during launch and none during post-launch). Make sure that the spreadsheet shows parameters, calculations, documentation of any assumptions and output.
II. Explain how you calculated this answer, including method and cells used.