In this assignment you\'ll solve the basic economic order quantity (E0Q) model i
ID: 348038 • Letter: I
Question
In this assignment you'll solve the basic economic order quantity (E0Q) model in Excel using two methods: (A) a trial-and-error approach to find a very good but approximate answer, and (8) the EOQ formula to find the "exact" answer. Set up your spreadsheet similar to that shown below. The situation: Roxie is responsible for purchasing the paper used in all copy machines and laser printers at Budco. After looking at her records, Roxie has found demand for paper averages 600 boxes per month. The price of a box of paper is $20 (regardless of the number ordered). Placing and handling an order costs $70. Annual unit holding costs per box are 2S% of the unit price. last year, Roxie ordered paper once every 2 months, but she wants to know if another ordering policy would be cheaper 1Annual Demand Unit Price Ordering Cost B. EOQ Formula Optimal Annual E|Recorder OrderOrer+Holding 4 Appual Holding Cost bo Quantity Costs at Q* A. Trial-and-Error Method AnnualAnnual Or Annual Order Holdi 91 10 Quantity (Q) 25 50 75 Costs Costs Costs 12 A. Trial-and-Error (Approximate) Method Order Quantity: type in 25 for cell A10; then use the formula 25+A10 for cell A11 and copy this formula down, until Q reaches 1200 Annual Ordering Costs (DS/Q): for cell 810, use SBS1'SBS3/A10; then copy formula down. Annual Holding Costs (HQ/2): for cell C10, use $BS4 A10/2; then copy formula down. Annual Ordering Holding Costs: for cell D10, use B10+C10: then copy formula down 1. Approximately what quantity Q minimizes total annual ordering & holding costs? boxes In Excel, make a scatter plot of the (Annual Ordering+ Holding Costs) vs. Order Quantity (x) Print out this plot and turn it in, along with this page and a printout of your spreadsheet. B. EOQ (Exact) Formul 2. What order quantity Q' minimizes total annual order+ holding costs? boxes 3. The Excel cell formula required for cell ES is SQRT 2 81 83/34). What Excel cell formula is required for cell F5? 4, At Q*, what are the total annual ordering + holding costs? 5. How many times per year will Roxie place an order of size Q"? 6. Last year, Roxie ordered every 2 months. Her total annual grdsrinetholdine costs were 7. What is the percentage reduction in annual ordering holding costs achieved by Roxie in foilowing the optimal inventory policy instead of last year's orderine policy? 0 o1 428Explanation / Answer
Student has asked for annual demand only.
Annual demand = (600 boxes per month)*(12 months per year)
= 600*12
= 7200 boxes per year