A gift shop in a tourist area sells a certain item at a steady rate, year-round, with an annual demand of 15,000 units. When stock runs out, the shop calls the distributor to place an order for more stock, and this is delivered the next day. The distributor charges $0.50 each for the items, plus a flat fee of $10 for each order delivered, regardless of quantity. Like many small businesses, the gift shop runs on borrowed cash, so holding inventory is a major cost. The carrying cost of inventory is 25% per year of the value (cost) of the inventory.

Formulate your model for this problem on an Excel worksheet and optimize it using Solver in order to answer the following questions. Your worksheet should follow the design rules given and should be clearly labeled.

Questions:

2. To minimize total cost, how many units should the gift shop order each time?
4. How many orders will the shop place per year?(Answer does not need to be an integer.)
6. If the shop wanted to set an order quantity such that it would order once a month (on average), what would that quantity be, and how much of a cost penalty would there be relative to the optimal case?
8. Show a data table and use the results to produce an Excel chart showing total cost as a function of order quantity over a reasonable range of values (your choice).