Jan Michael started a small grocery store in Florida. The store is open for three hundred and sixty days a year. He sells five thousand four hundred cases of cases of candy bars at a constant daily rate every year. He purchases the candy from a certain wholesaler in Ohio, who then charges approx. $1.50 per case plus an additional $0.50 per case to cover the shipping cost in another state. The delivery happens the day after an order is placed by Jan Michael. The purchasing department calls the wholesaler at the start of each week to place an order for one hundred cases of candy. The cost is ten dollars per order and it doesn't matter how many are ordered. Monies(capital) have been borrowed from USA bank at an annual interest rate of ten percent. Also, Jan has to pay tax of five percent of the annual inventory value and another five percent for insurance purposes. Jan makes the determination that operating costs are either fixed in nature or don't depend on the amount of candy that is ordered.
The following questions are asked using the economic order quantity model:
1) What is the total annual relevant cost of the company's current inventory policy?
2) What are the optimal order quantity and its cost? Will ordering that amount provide significant savings?
3) Joe Blow wants to apply the EOQ model to a product with lower sales, with a different variety of candy bars that sells 1,080 cases annually. The cost is twenty dollars per case. The order is placed and the order cost $100.00, independent of the number of cases ordered which now arrive seven days later. The holding cost allocation are the same as the regular candy. (A) What are the optimal order quantity and the total annual relevant cost of the special variety candy?
I need the answers in excel and the explanation on how the number were derived.