Simulation problem, must be done in Excel spreadsheet (showing formulas) please.
A distilled water distribution company wants to open a new branch but faces a problem: the climate in the city is very variable and according to the type of climate, there is data on how many liters of water are sold per type of day.
The company qualifies in 4 types of climates with the following probabilities of occurrence:
Hot - 50%
Tempered - 25%
Cold - 15%
Frozen - 10%
According to each climate, the sale of a liter follows the corresponding probability.
Hot:
Uniform distribution between 300 and 350 liters per day
Tempered:
Bernoulli Distribution with 85% success. If success is met, 285 liters are sold, in the other case (failure) 185 liters are sold.
Cold:
Exponential distribution with a mean of 150 liters.
Frozen:
Uniform distribution between 100 and 200 liters.
The company wishes to simulate 10 years of daily sales with 20 replicates of the 10 simulated years to have correct information. Considering that the sale price per liter is $7.5 and the fixed daily cost is $820 to maintain the new branch, answer the following questions:
- What was the sum of the profits for each type of day?
- What was the average daily profit for each type of day?