Your firm imports large wheels of French cheese and sells them to specialty food businesses. You currently have 22 wheels of Chateau L'Odeur cheese in storage, and have the capacity to store up to 50 wheels. You are trying to plan your inventory strategy for the next 12 months.
You do not know the exact demand for Chateau L'Odeur over the coming year. However, from an analysis of past orders, you have the following estimates of the expected (average) level of demand:
You may order up to 100 of wheels of cheese at a time, and it takes one month for an order to arrive from France. Thus, you can only meet the current month's orders from the 22 wheels you already have in stock, and similarly for following months. You estimate that the overhead of placing and handling an order (including the extensive paperwork required to import agricultural products) costs you $300 per order. Each wheel of cheese costs $170, and you sell it for $330. If you do are not able to meet demand on a given month, you lose the revenue associated with the portion of the demand you cannot meet (there are no "backorders").
If you overflow your storage capacity, you sell any extra wheels to a discount food emporium for $120 per wheel. Assume you will do the same with any cheese remaining at the end of the year. Keeping a wheel of cheese in storage for a month has a direct cost of $10. In addition, your firm's internal cost of funds is 0.2%, compounded monthly.
While in principle Chateau L'Odeur cheese has an essentially infinite shelf life, you find that under U.S. conditions each wheel has an independent 5% chance of "going off" each month it spends in your storage facility, after which you must immediately discard it at no cost. However, such units still incur the full $10 per unit direct storage cost in the month that they "go off".
Assume that your ordering policy is to order Q units whenever the starting inventory for the month reaches R units or below. You are considering R = 15, 20, 25, 30, or 35, and Q = 20, 25, 30, 35, or 40.
Create a simulation spreadsheet to try to determine the highest-EMV ordering policy from among those described above.