Chris Sandvig Irrigation, Inc., has summarized the price list from four potential suppliers of an underground control valve.See the table below. Annual usage is 700 valves; order cost is $10 per order; and annual inventory holding costs are$7.55 per unit.Vendor AQuantity1-910-1415-4950-99100-299300+Price$35.0034.7533.5532.3531.1530.75Vendor BQuantity1-1415-4950-99100-299300+Price$34.7534.0032.8031.6030.50Vendor CQuantity1-2425-7475-199200+Vendor CVendor BVendor DPrice$34.5033.7532.5031.10Which vendor should be selected and what order quantity is best if Sandvig Irrigation wants to minimize total cost?Chris Sandvig Irrigation should order units at a time from(enter your response as a whole number).Vendor AVendor DQuantity1-7475-199200+Price$34.2533.0031.00

Instructions: EOQ = sqrt (2DS/H) D = Annual demand S = Ordering cost H = Holding cost Annual ordering cost = D/EOQ X S Annual holding cost = EOQ/2 X H Total unit cost = Price per unit X annual demand Total cost = Annual ordering cost + Annual holding cost + Total unit cost In case calculated EOQ is not in a particular range and calculated EOQ is higher than the quantity of given range, we will not consider the given range. We will compute the total cost for other given ranges by considering their minimum quantity as ordering quantity Vendor A Demand rate, D 700 Ordering cost, S 10 Holding cost %, I 7.55 Range 1 Range 2 Range 3 Range 4 Range 5 Range 6 Minimum quantity 1 10 15 50 100 300 Maximum quantity 9 14 49 99 299 99999 Unit Price, P 35 34.75 33.55 32.35 31.15 30.75 =B6 =C6 =D6 =E6 =F6 =G6 EOQ =SQRT(2*$B$2*$B$3/$B$4) =SQRT(2*$B$2*$B$3/$B$4) =SQRT(2*$B$2*$B$3/$B$4) =SQRT(2*$B$2*$B$3/$B$4) =SQRT(2*$B$2*$B$3/$B$4) =SQRT(2*$B$2*$B$3/$B$4) Order Quantity =IFS(B11>B8,"NA",B11>=B7,B11,B11<B7,B7) =IFS(C11>C8,"NA",C11>=C7,C11,C11<C7,C7) =IFS(D11>D8,"NA",D11>=D7,D11,D11<D7,D7) =IFS(E11>E8,"NA",E11>=E7,E11,E11<E7,E7) =IFS(F11>F8,"NA",F11>=F7,F11,F11<F7,F7) =IFS(G11>G8,"NA",G11>=G7,G11,G11<G7,G7) Holding cost =IF(B12="NA","NA",B12*$B$4/2) =IF(C12="NA","NA",C12*$B$4/2) =IF(D12="NA","NA",D12*$B$4/2) =IF(E12="NA","NA",E12*$B$4/2) =IF(F12="NA","NA",F12*$B$4/2) =IF(G12="NA","NA",G12*$B$4/2) Setup cost =IF(B12="NA","NA",$B$3*$B$2/B12) =IF(C12="NA","NA",$B$3*$B$2/C12) =IF(D12="NA","NA",$B$3*$B$2/D12) =IF(E12="NA","NA",$B$3*$B$2/E12) =IF(F12="NA","NA",$B$3*$B$2/F12) =IF(G12="NA","NA",$B$3*$B$2/G12) Unit costs =IF(B12="NA","NA",B9*$B$2) =IF(C12="NA","NA",C9*$B$2) =IF(D12="NA","NA",D9*$B$2) =IF(E12="NA","NA",E9*$B$2) =IF(F12="NA","NA",F9*$B$2) =IF(G12="NA","NA",G9*$B$2) Total cost, Tc =IF(B12="NA","NA",B14+B15+B16) =IF(C12="NA","NA",C14+C15+C16) =IF(D12="NA","NA",D14+D15+D16) =IF(E12="NA","NA",E14+E15+E16) =IF(F12="NA","NA",F14+F15+F16) =IF(G12="NA","NA",G14+G15+G16)