One of the items sold at a museum of natural history is a Christmas ornament carved of wood. The gift shop purchases the ornament from a vendor. The unit cost forall ornaments it orders depends on the number ordered. The quantity discount structure is as follows:

• - If the number ordered is less than 3, the unit cost is $12;

• - For at least 3 ornaments, the price is $10;

• - For at least 5 ornaments, the price is $9;

• - For at least 8 ornaments, the price is $8.5.

  If the ornament is sold during the season, it is sold at $25 per ornament. When the season is over, the leftover ornaments can be sold back to the vendor for $5. The demand for ornaments has the following distribution:

Demand Quantity	Probability
0	0.01
1	0.03
2	0.08
3	0.14
4	0.18
5	0.17
6	0.15
7	0.10
8	0.08
9	0.05
10	0.01

Develop a spreadsheet to find the optimal order quantity (Q). Q can be any integer from 0 to 10.

• - Your spreadsheet should show the ordering quantity discount structure.

• - Your spreadsheet should show profits for every combination of Q and demand. These

  profits should be shown in a two-way table.

• - Your spreadsheet should show the expected profits for each possible value of Q.

• - Your spreadsheet should show a graph (bar chart) of the expected profits versus Q.

• - Your spreadsheet should identify the optimal order quantity along its associated

  expected profit.
  (Hint: Use the Sam’s Bookstore example as template)

1. Turn in a hardcopy of the Excel spreadsheet showing all the above requirements.

2. Turn in a hardcopy of the Cost formula. Below is an example:

4

The formula in B18 (total cost) is: =VLOOKUP(B9,D5:E9,2,TRUE)*B9