A local retailer plans to sell cashmere sweaters in its regular catalog for $150 each. The manager estimates that the demand can be characterized by a triangular distribution with a minimum, most likely, and maximum sales of 2000, 3000, and 5,000 units, respectively. Toward the end of the season, the retailer sends out a clearance catalog with discounted prices on unsold items. Understandably, the discounted price determines the demand in response to the clearance catalog. The manager has decided to charge a discount price of p = max ($25, $150n/20), where n is the number of sweaters left over after the regular catalog. At this clearance price p, the manager believes that the demand for cashmere sweaters follows a normal distribution with a mean of 10005p and a standard deviation of (10005p)/3. If there are still any leftover sweaters after clearance catalog, they will be donated to a local charity. Each sweater costs the retailer $50. As such, the donation to charity will generate $20 in tax benefits. The manager would like to identify the optimal number of sweaters to order at the beginning of the winter season. Please run a simulation for 1000 times with order quantities from 2000 to 5000 with increment of 500. Can you give the manager a suggestion on how many sweaters to order based on the criterion of maximizing the average (expected) profit? Please use the template down below.

PLEASE USE EXCEL FORMULAS CLEARLY AND DO NOT USE RISK FUNCTION FOR THUMBS UP!!!!!!!!

Problem data

Triangular distribution

Discount price

Minimum

2000

Regular price

150

Most likely

3000

Clearance demand (normal)

Maximum

5000

Regular demand (triangular)

Mean

Range

Range = Max - Min

Minimum

2000

Standard deviation

Ratio

Ratio = (MostLikely - Min)/Range

Most likely

3000

Rand #

Maximum

5000

Tax benefit (donation)

20

per donated unit

Excel formula to generate a triangular input if range names are properly defined.

=IF(Rand

Quantity to order

2000

Order quantity to try

2500

3000

3500

4000

4500

5000

Simulation

Leftover

Clearance

Donated

Tax

Regular demand

Cost

Regular revenue

Leftover

demand

revenue

unit

benefit

Profit

Summary measures

Order quantity

Average

Standard deviation

Best order quantity: