Share
Explore BrainMass

Wine Ordering System Simulation

The Wine Cellar places orders for a popular Pinot Noir on a weekly basis. The weekly demand for this wine was found to approximately follow the probability distribution shown below. The Wine Cellar buys this wine at $10 per bottle and sells it for $16 per bottle. The inventory carrying cost is $0.50 per bottle per week, and the shortage cost is $1.00 per bottle. The Wine Cellar does not permit backlogs, so shortages incur a cost only in the week they occur and then are ignored

Demand.......Probability
15....................0.20
16....................0.25
17.....................0.4
18....................0.15
Total................1.00

a) Using an order size of 16 bottles per week, simulate this ordering system for one year (52 weeks) and determine the expected profit. (Please include your simulation spreadsheet with this exam).

b) In at most 3 sentences, explain how your simulation system may be used to determine the best order size for this store.

Solution Preview

The following text is included for search purposes -- please use the files to see the complete answer:

a) Using an order size of 16 bottles per week, simulate this ordering system for one year (52 weeks) and determine the expected profit. (Please include your simulation spreadsheet with this exam).

The simulation spreadsheet is attached. The simulation was done as follows:
-First, the demand was simulated for each of the 52 weeks in a year, using Excel's RAND() function. Following that, 100 such possible years were created. The demand for each year in each simulation is included in the first table in Excel, called 'Demand'.

-Next, the inventory remaining for each week was computed. The ...

Solution Summary

The wine ordering system simulations are examined.

$2.19