(Solved): TEMPLATE TO USE Tim Madsen is the purchasing agent for Computer Center, a large discount comput ...

TEMPLATE TO USE

Tim Madsen is the purchasing agent for Computer Center, a large discount computer store. He has recently added the hottest new computer, the Power model, to the store's stock of goods. Sales of this model now are running at about 30 per week. Tim purchases these computers directly from the manufacturer at a unit cost of $$\$400$$, where each shipment takes 10 days to arrive. Tim routinely uses the EOQ model to determine the store's inventory policy for each of its more important products. For this purpose, he estimates that the annual cost of holding items in inventory is $$25\%$$ of their purchase cost. He also estimates that the administrative cost associated with placing each order is $$\$2000$$. a. Prepare an Excel Template (based on EOQ principles) - that indicates the Total Cost (including Fixed Ordering and Inventory Carrying) that would be incurred annually if the Power Model were to be ordered monthly. b. Use the Excel Template you prepared - to do a sensitivity analysis - that shows how the two costs (Total Annual Fixed Ordering and Total Annual Inventory Carrying) would change if the number of months between orders were to be changed to the following values: $$1,2,3,\dots ,6$$. c. Then, prepare a graph of various annual costs versus the corresponding order quantity (i.e. Vertical Axis is showing the three costs (total, fixed-ordering and inventory-carrying; whereas Horizontal Axis is showing $${}^{\text{" }}{\mathrm{Q}}^{?}$$ ). d. Use Solver to find the optimal order quantity. d. What is the "flow-time in days" under the optimal replenishment policy? That is, on the average how many days the Power Model spends in storage before being sold right after procured from the supplier? e. How many times would Tim Madison be turning the inventory of the product in a year? Analysis using the Solver