Reference no: EM131410207

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)

Turn in a hardcopy of the Excel spreadsheet showing all the above requirements.
Turn in a hardcopy of the Cost formula. Below is an example:
The formula in B18 (total cost) is: =VLOOKUP(B9,D5:E9,2,TRUE)*B9