One of the most important considerations in retail operations is the amount of inventory carried for seasonal products. Matching supply with demand is not easy because some of the seasonal products are manufactured overseas and have to be ordered with a long lead time. This puts retailers such as KSW in a difficult situation; order too few and you lose sales, and order too much and you have unsold merchandise at the end of the season. You must analyze the following situation and make recommendations to KSW:

One group of seasonal merchandise sold by KSW is childrens shoes. The company gathers regional sales forecasts for the upcoming year and orders shoes from the manufacturers well ahead of the sales season. KSW categorizes childrens shoes into 8 different product categories. Description of 8 items as well as purchasing cost per unit, sales price per unit, salvage value per unit at the end of the season, mean demand, and standard deviation of demand for each item are provided in worksheet Q5-Data and Templates worksheet.For many years, KSW has been aiming a 85% service level for all the items in the childrens shoes, i.e., the order quantity for each item is chosen such that stockouts occur no more than 15% of the time. KSW has asked you to analyze their inventory management practices.

A)ANALYSIS OF ORDER QUANTITIES

In this section you must complete Table 1 and Table 2 which are given in Q5-Data and Templates worksheet.

i. (6 points) Fill in the missing values in Table-1 as follows: Find the order quantity, expected lost sales, expected leftover inventory, expected sales, expected total profit, and fill rate as well as in-stock and out-of-stock probabilities for each item in order to avoid stock-outs 85% of the time, e.g., in-stock probability must be 85%. To find the expected lost sales use the formula below in Excel

E[Lost Sales] = *(NORM.DIST(z-value,0,1,0)- z-value*(1-NORM.S.DIST(z-value)))

where z-value=NORM.S.INV(service level) and is the standard deviation of demand.

To find the order quantity, use the formula below:

Q=(mean demand) +*(z-value)

where z-value=NORM.S.INV(service level) in Excel, or use the Excel function

Q=NORM.INV(service level, mean demand,).

After you are done filling Table 1 in Excel, copy and paste it as a picture to your answer report. For additional information on inventory management and the formulae required to compute relevant performance measures, please refer to the lecture notes.

ii.(6 points) Fill in the missing values in Table-2 as follows: Use the newsvendor model and determine the cost of overage, the cost of underage and the optimal order quantity for each item. Compute the corresponding expected lost sales, expected leftover inventory, expected sales, expected profit, fill rate, in-stock, and out-of-stock probabilities. To find the optimal order quantity, use the formula below in Excel:

Q=(mean demand) + *(z-value)

where z-value=NORM.S.INV(critical ratio) or

Q=NORM.INV(critical ratio, mean demand, ).

Use the formula below in Excel to compute the expected lost sales:

E[Lost Sales] = *(NORM.DIST(z-value,0,1,0)- (z-value)*(1-NORM.S.DIST(z-value)))

where z-value=NORM.S.INV(critical ratio).

After you are done filling Table 2 in Excel, copy and paste it as a picture to your answer report. Please refer to the lecture notes for information about the newsvendor model and the formulae required for other performance measures.

B)RECOMMENDATIONS

Answer the following questions:

i. (3 points) Should the manager use the same service level for all products in the next season? Why or why not? Justify your answer based on your analysis in part A.

ii. (3 points) Remember that current order quantities are determined based on a service level target of 85%. Make a suggestion to the manager on how he/she should change the order quantities for next season. Which items should be ordered more and which ones less? What will be the expected increase in expected profit next season if the newsvendor model is used to determine the order quantities?

Demand forecast: Demand is normally distributed Purchasing cost per Item No. Item description Sales price per unit Salvage value per unit Forecast error (standard deviation) Mean demand 20 564 27 655 22 355 32 951 6 543 7 856 12 541 18 653 $48,99 $75,99 $17,79 $8,99 Big Boy Winter Boots Bie Girl Winter Boots Toddler Boy Winter Boots Toddler Girl Winter Boots $2,00 $1,00 5,75 $4,44 13,45 $7,75 2231 6742 Summer Sandals 28,85 3,00 $3,50 Big Girl Summer Sandals Toddler Girl Summer Flats Big Girl Summer Flats 1200 7,22 36,98 45 Table-1 Inventory Performance given a common target service level Item Quantity ordered Expected pfit Expected lost sales Expected Sales Expected Leftover Inventory Expected fill rate probability probability: P(DSQ) TOTAL N/A N/A N/A