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