Prediction of Future Ski Jacket Demand Using Simulation
Egress Inc. is a relatively new ski jacket production company. In order to predict the quantity of Ski Jackets Egress should manufacture for next season, we need to simulate the demand and also predict the quantity to manufacture that will maximize the profits.
Question No. 1
Egress management believes that a normal distribution is a reasonable model for the unknown demand in the coming year. What mean and standard deviation should Egress use for the demand distribution?
Answer No. 1
Assuming that the demand is distributed normally, using the twelve random values for demand predicted by the employees of the company as follows:
We calculate the mean of the demand by adding all the values in the above table and dividing them by 12. Alternatively we can use AVG function in excel as shown in the excel file.
Mean = 12,000
The standard deviation can be calculated using the STDEV function of excel as shown in the excel file.
Standard Deviation = 3497
Question No. 2
Use a spreadsheet model to simulate 1000 possible outcomes for demand in the coming year. Based on these scenarios, what is the expected profit if Egress produces Q = 7800 ski jackets? What is the expected profit if Egress produces Q = 12,000 ski jackets? What is the standard deviation of profit in these two cases?
Answer No. 2
To simulate the 1000 possible outcomes for demand, we first generate 1000 random numbers in excel using the RAND function. This generates 1000 random numbers between 0 and 1. As we have already assumed that demand is modeled on the normal distribution, we use these random numbers as probabilities in normal distribution and use them to calculate 1000 different values for demand. This is done in excel by using the NORM.INV function with the mean and standard...