Simulation

Club analysts utilize simulation to model the club's shirt sales decision. They start by tackling the uncertainties in the problem. They first use insight from the supplier in conjunction with historical records and weather forecasts to estimate a probability of 67% that the shirts are delivered on time. Then, they produce two sets of subjective assessments for demand shown below. In case the shirts are delivered on time, the analysts believe the club will not sell more than 50,000 shirts. Assessments for the case of delayed delivery, shown in the rightmost figure below, are 50% of on-time assessments, reflecting the belief that demand will be cut in half in case of delay.

The analysts then use @RISK to fit theoretical distributions to the two assessments. Those are shown below. For on-time delivery, shirt demand is modeled as a general beta distribution with parameters α1=1.5304, α2=1.0321, minimum=0, and maximum=50,000. Demand in case of delayed delivery is also modeled as a general beta distribution with α1=1.5304, α2=1.0321, minimum=0, and maximum=25,000.

Next, the analysts build a mathematical model using Excel. The inputs to the model are as follows:

Price of a shirt P=€50
Cost of a shirt C=€20
Discounted price of a shirt S=€10
On-time demand for shirts X=RiskBetaGeneral(1.5304, 1.0321, 0, 50,000)
Delayed demand for shirts Y= RiskBetaGeneral(1.5304, 1.0321, 0, 25,000)

The club purchases shirt orders in increments of 1,000. The number of shirts ordered is modeled using a Sim Table to sequentially vary order quantity between 5,000 and 50,000, a total of 46 different quantities. The quantities are written in an Excel column and input into the @RISK function for quantity ordered. The final function for quantity ordered Q is of the format RiskSimTable(G2:G47).

Using the preceding inputs, the following quantities are computed:

Expected Demand D= 0.67*X+0.33*Y
Revenue R= Minimum(Q,D)*P
Expenses E= Q*C
Discounted Revenue Z= Maximum(0,Q-D)*S
Total Profit T= R+Z-E

Next, 46 simulations of 1,000 iterations each are executed using @RISK, with T defined as output. The figure below shows output results for the first 10 quantities, 5,000 to 14,000. Results reveal that as the expected profit of a certain order quantity increases, so does its standard deviation, or riskiness. This is an unsurprising result; by ordering more shirts, the club risks losing more money in the event of a delay.

The figure to the right shows the top 10 obtained values for expected profit. The values are associated with order quantities between 28,000 and 37,000 shirts. The maximum expected profit is linked with ordering 32,000 shirts and amounts to €639,466.01. Club analysts identify 32,000 as the optimum order quantity and relay this information to management.

Before committing to an order, club management decide to weigh other options. They reach out to a local supplier in Tenerife who would charge €18 per shirt. However, the supplier informs management that with less than 3 weeks to go for Valentine's Day, he cannot guarantee a specific production quantity. Instead, he provides the probability assessments shown to the right. The club are guaranteed no less than 10,000 shirts and no more than 50,000 shirts.

Upon hearing of this proposition, club higher-ups enlist the help of their analysts, who proceed to run a second simulation to determine the better supply option. They start by fitting a theoretical distribution to the above assessments. They decide that supply is best modeled in this case by a general beta distribution with parameters α1=2.7791, α2=2.5431, minimum=0, and maximum=50,000.

The analysts then build another mathematical model using Excel. The following are used as inputs to the model:

Cost per shirt C= €18
Price per shirt P= €50
Discounted price of a shirt X=€10
Supply S= RiskBetaGeneral(2.7791, 2.5431, 0, 50,000)
Demand D= RiskBetaGeneral(1.5304, 1.0321, 0, 50,000)

The following computations are subsequently made:

Revenue R= Minimum (S,D)*P
Total expense E= S*C
Discounted Revenue Q= X*Maximum(0, S-D)
Total Profit T= R-E+Q

With the model complete, the analysts run a simulation using @RISK of 1,000 iterations, with T defined as output. The graph on the left shows the resulting output. The expected profit for this supply option is €638,814.87, with a standard deviation of €343,637.27.

Comparing the two options, the German alternative edges the local one by a fine margin in terms of expected profit, and with a smaller standard deviation (€316,330.77 vs $343,637.27), is less risky than the local alternative. The analysts relay this information to management, recommending sourcing the shirts from Germany.

Page updated

Report abuse