Your friend Jane, an entrepreneur living in Boston, needs your help. Jane is considering opening an online store to sell a product that will have a logo of one of the top universities in Boston. She is very secretive at this point and will not tell you the type of product – just that it comes in five colors and will contain one of the 8 logos (i.e., there are 40 different configurations of the product). She asked another friend to make demand forecasts. The forecast indicates level demand over the year, with the average weekly demand and the standard deviation of the weekly demand forecast shown in Table 1. Demand will be independent across products but demand will be positively correlated over time.
For this assignment, create an Excel spreadsheet to evaluate the two options. The comparison should compare: (a) the annual holding cost of safety stock for the “no aggregation” option, where all 40 final product configurations are held in inventory, and (b) the annual holding cost of safety stock plus the increased printing cost for the “aggregation” option, where inventory is held by color so that only 5 different items are held in inventory. When analyzing the two alternatives, use a target product fill rate of 99%.