Question

**PLEASE SHOW WORK IN EXCEL**

A boutique retailer is planning to stock Arctic Duck brand parkas for the upcoming winter season. The demand forecast is for a mean of 6,000 parkas and a standard deviation of 2,000. Assume demand has a Normal distribution.

Each parka is purchased for $100. Unsold parkas will be sold through an outlet store in February for $75 (at that price, all the parkas are expected to sell), although it costs $15 per parka to process them and move them to the outlet store.

The boutique needs to place an order in September. There is strong disagreement on the part of the buying committee over how many parkas to order. One buyer wants to order 7,000 parkas and the other wants to order 8,000 parkas.

a. What is the unit cost of overstocking in dollars?

b. At what selling price (in dollars) would an order of 8,000 parkas make sense?

Hint: there are two ways to solve this problem. One way is direct: what is the z-score of 8,000? What does that mean for the critical fractile? Etc. The second way is indirect: set up the problem with any selling price and find the order quantity; then use a goal seek to find the selling price where 8,000 parkas is optimal.

c. At what selling price (in dollars) would an order of 7,000 parkas make sense?

IF DONE IN EXCEL, PLEASE SHOW WORK IN EXCEL

Answer 1

Solution in EXCEL is following:

EXCEL FORMULAS:

Mean demand	6000	parkas
Standard deviation of demand	2000	parkas
Purchase cost	100
Selling price to outlet store	75
Process and moving cost	15
a) Unit cost of overstocking, Co	=B4-B5+B6
b)
Given order quantity	8000	parkas
z-score	=(B12-B$2)/B$3
Critical fractile	=NORMSDIST(B13)
Using formula of critical fractile = Cu/(Cu+Co) and solving for Cu
Unit cost of understocking, Cu	=B14*B$9/(1-B14)
Selling price =Cu+purchase cost	=B16+B$4
c)
Given order quantity	7000	parkas
z-score	=(B20-B$2)/B$3
Critical fractile	=NORMSDIST(B21)
Using formula of critical fractile = Cu/(Cu+Co) and solving for Cu
Unit cost of understocking, Cu	=B22*B$9/(1-B22)
Selling price =Cu+purchase cost	=B24+B$4