You are the materials manager at Metro Hospital. Approximately one year ago, the hospital began stocking a new drug (Ziloene) that helps the healing process for wounds and sutures. It is your responsibility to forecast and order the monthly supply of Ziloene. The goal is to minimize the combined cost of overstocking and understocking the drug. Orders are placed and received at the beginning of the month, and demand occurs throughout the month. The following demand and cost data have been compiled:

Costs. If more is ordered than is demanded, a monthly holding cost of $1.00 per case is incurred. If less is ordered than is demanded, a $2.00 per case lost sales cost is incurred. The drug has a short shelf life, and any overstocked product at the end of the month is worthless and no longer available to meet demand.

Demand. The demand for the twelve months of last year was:

You believe this demand to be representative of Metro's normal usage pattern.

The data are also given in the attached Excel file. Using your intuition and any previous knowledge of forecasting you may have, please forecast monthly demand for the next two years, and determine and appropriate order of Ziloene cases for Metro Hospital, one month at a time. The Excel sheet will then show you the actual demand for the month and calculate your costs. Your forecast for the following months should account for the new information provided to you (demand up to the beginning of the month being forecasted). Please don't change your forecast and order inputs after you observe the actual demand for that same month. What matters is your methodology and what you learn from this exercise, not how close you are to a perfect forecast or optimum cost.

Please submit the Excel workbook that you worked on and a one-page (or less) report explaining

1. What you did to determine the forecast and ordering amount, and why you did it (basic intuitive argument or specific methods used, why you choose that approach, etc.)

2. What you learned from this exercise?