M&B is a local distributor in Ocean Springs, Mississippi, supplying pharmaceuticals and medical supplies to 55 independent drug stores in the Southern region of the US. One of the most popular products M&B distributes is Headache Remedy Formula (HRF) 5123. M&B purchases this generic drug product from a single supplier, the Chem Corporation. Sales of cases of HRF 5123 are relatively constant. The current inventory policy for M&B calls for purchasing HRF 5123 in lot order quantities of 155 cases from the Chem Corporation. M&B has an inventory control system that monitors inventory levels of all products on a weekly basis. Inventory records for HRF 5123 are attached (see excel template). M&B operations require that 155 cases of HRF 5123 be reordered when inventory levels drop to 55 cases within the warehouse. Each case of HRF 5123 cost $10 and M&B has a 14% annual holding cost rate for its inventory. It cost $12 to prepare a purchase order for 1 case of HRF 5123. Assume all shortage cost are negligible and all shortages are fulfilled with backorders. (20 pts)

1. What is the value of the total annual inventory costs for HRF 5123 from the data provided in the attached spreadsheet (need to figure out annual demand, use all data to calculate results)?

Week 2 5 6 7 7 8 10 11 12 13 130 108 10 14 11 12 16 17 18 19 0 20 21 22 14 15 16 16 17 18 19 20 21 22 23 24 44 24 29 24 31 32 Date Inventory Level Reorder Transaction 4-Jan-16 198 Begining Inventory 11-Jan-16 122 18-Jan-16 106 25-Jan-16 66 1-Feb-16 55 Order Placed 8-Feb-16 30 15-Feb-16 22-Feb-16 160 Order Received 29-Feb-16 141 7-Mar-16 14-Mar-16 21-Mar-16 28-Mar-16 4-Apr-16 Order Placed 11-Apr-16 18-Apr-16 25-Apr-16 2-May-16 155 Order Received 9-May-16 141 16-May-16 128 23-May-16 108 30-May-16 97 6-Jun-16 87 13-Jun-16 53 Order Placed 20-Jun-16 27-Jun-16 31 4-Jul-16 186 Order Received 11-Jul-16 174 18-Jul-16 130 25-Jul-16 28 1-Aug-16 118 8-Aug-16 88 15-Aug-16 56 | Order Placed 22-Aug-16 29-Aug-16 184 Order Received 5-Sep-16 12-Sep-16 19-Sep-16 119 26-Sep-16 3-Oct-16 10-Oct-16 Order Placed 17-Oct-16 | 24-Oct-16 31-Oct-16 7-Nov-161 155 1 Order Received 14-Nov-16 21-Nov-16 142 28-Nov-16 5-Dec-16 12-Dec-16 72 19-Dec-16 54 Order Placed 26-Dec-16 2-Jan-17 190 Order Received 9-Jan-17 167 16-Jan-17 151 23-Jan-171 118 30-Jan-17 101 6-Feb-17 79 13-Feb-171 68 Ending Inventory 34 35 25 26 27 28 29 30 31 31 32 33 34 35 36 36 37 38 39 40 41 42 43 37 44 45 46 47 48 49 50 51 52 38 39 40 40 41 42 43 44 45 46 47 47 48 49 50 51 52 54 37 55 56 57 58 60 61 Week 2 5 6 7 7 8 10 11 12 13 130 108 10 14 11 12 16 17 18 19 0 20 21 22 14 15 16 16 17 18 19 20 21 22 23 24 44 24 29 24 31 32 Date Inventory Level Reorder Transaction 4-Jan-16 198 Begining Inventory 11-Jan-16 122 18-Jan-16 106 25-Jan-16 66 1-Feb-16 55 Order Placed 8-Feb-16 30 15-Feb-16 22-Feb-16 160 Order Received 29-Feb-16 141 7-Mar-16 14-Mar-16 21-Mar-16 28-Mar-16 4-Apr-16 Order Placed 11-Apr-16 18-Apr-16 25-Apr-16 2-May-16 155 Order Received 9-May-16 141 16-May-16 128 23-May-16 108 30-May-16 97 6-Jun-16 87 13-Jun-16 53 Order Placed 20-Jun-16 27-Jun-16 31 4-Jul-16 186 Order Received 11-Jul-16 174 18-Jul-16 130 25-Jul-16 28 1-Aug-16 118 8-Aug-16 88 15-Aug-16 56 | Order Placed 22-Aug-16 29-Aug-16 184 Order Received 5-Sep-16 12-Sep-16 19-Sep-16 119 26-Sep-16 3-Oct-16 10-Oct-16 Order Placed 17-Oct-16 | 24-Oct-16 31-Oct-16 7-Nov-161 155 1 Order Received 14-Nov-16 21-Nov-16 142 28-Nov-16 5-Dec-16 12-Dec-16 72 19-Dec-16 54 Order Placed 26-Dec-16 2-Jan-17 190 Order Received 9-Jan-17 167 16-Jan-17 151 23-Jan-171 118 30-Jan-17 101 6-Feb-17 79 13-Feb-171 68 Ending Inventory 34 35 25 26 27 28 29 30 31 31 32 33 34 35 36 36 37 38 39 40 41 42 43 37 44 45 46 47 48 49 50 51 52 38 39 40 40 41 42 43 44 45 46 47 47 48 49 50 51 52 54 37 55 56 57 58 60 61