NEED HELP ON THIS CASE ASAP. IT HAS TO BE IN EXCEL.

Economic Order Quantity Model (D) (S) (p) (H) (Q) (T) (SS) (L ) (I) TAC Annual use of a particular item, in number of items per year (Demand) Order-processing cost, in $/order Price per item, in $/unit Holding cost per unit per year, in $/unit/year Number of items ordered in one purchase order, in units Time periods between purchase orders Safety Stock, in units Lead time, in fraction of a year Current inventory on hand, units Total annual cost * Price per item/unit provided by the instructor TheFine Garments Company sells fashion clothing. The forecasted annual demand for its premium leat order processing cost is $40, the inventory holding cost is $35/item/year. How many leather jakcets sho 2,000 units/year $40 $500* $35 68 12 154 2 Purchase Cost = p x D Average Inventory Level = (SS + Q + SS)/2 = SS + Q/2 Annual Holding Cost = (SS + Q/2) H Annual Order Processing Cost = (D/Q) / 2 Total Annual Cost (TAC) = Purchase Cost + Holding Cost + Order Processing Cost = p AD + (SS + Q/2) H + (D/Q) S EOQ = 2DS H D S H 1,200 units/ per year $25 $50 EOQ = 2DS = H 2 x 2,000 x 40) 35 = 67.61 68 The Fine Garments Company wants to use a re-order point system. It has the order quantity set at 35 u in delivery and in customer demand, it wishes to hold 4 weeks of demand for safety stock. What should delivery lead time is 2 weeks? ROP = D X L + SS Average Inventory Level = (SS + Q +SS)/2 = SS + SS = D X L = 2,000 x (4/52) = 153.84 = 154 = 154 + 6 = 188 ROP = 2,000 X (2/52) + 154 = 230.92 = 231 Total annual cost (TAC) = Purchase Cost + Holding cost + Order-processing cost OrdeR-processing cost = (D+Q)/ S Purchase cost = p x D = $500 x 2,000 = ### Annual Holding Cost = (SS + Q/2 = 188*(35) = $6,580 Annual order processing cost =(D/Q) S = (2,000/68) * 40 = $1,176 TAC = $1,000,000 + $6,580 + $1,176 = $1,007,756 ther jacket is 2,000 units. The ould it order in one shipment ? units. To allow for uncertainties ld the reorder point be if the + Q/2 68/2 Economic Order Quantity Model (D) (S) (p) (H) (Q) (T) (SS) (L ) (I) TAC Annual use of a particular item, in number of items per year (Demand) Order-processing cost, in $/order Price per item, in $/unit Holding cost per unit per year, in $/unit/year Number of items ordered in one purchase order, in units Time periods between purchase orders Safety Stock, in units Lead time, in fraction of a year Current inventory on hand, units Total annual cost * Price per item/unit provided by the instructor TheFine Garments Company sells fashion clothing. The fore order processing cost is $40, the inventory holding cost is $ 2,000 units/year $40 $500* $35 68 12 154 2 102 Purchase Cost = p x D Average Inventory Level = (SS + Q + SS)/2 = SS + Q/2 Annual Holding Cost = (SS + Q/2) H Annual Order Processing Cost = (D/Q) / 2 Total Annual Cost (TAC) = Purchase Cost + Holding Cost + Order Processing Cost = p AD + (SS + Q/2) H + (D/Q) S EOQ = 2DS H D S H 1,200 units/ per year $25 $50 EOQ = 2DS = H 2 x 2,000 x 40) 35 = 67.61 68 The Fine Garments Company wants to use a periodic inven If on a review the inventory of the premium leather jacket i delivery lead time is 2 weeks? Fixed Time Peiod, T T = EQO/D = (68/2,000) T = = 0.034 Year = 0.034 * 365 days = 12.41 = 12 Target Minimum Level, M M = D (L+T) + SS M = 2,000 * (2/52 + 12/365) + 2,000 * (5/52) M = 143 + 192 M= 335 The order quantity for given inventory level is, Q = M - I Q = 335 - 102 = 233, 233 jackets should be ordered Total annual cost (TAC) = Purchase Cost + Holding cost + O OrdeR-processing cost = (D+Q)/ S Purchase cost = p x D = $500 x 2,000 = ### Annual Holding Cost = (SS + Q/2 = 188*(35) = $6,580 Annual order processing cost =(D/Q) S = (2,000/68) * = $1,176 TAC = $1,000,000 + $6,580 + $1,176 = $1,007,756 forecasted annual demand for its premium leather jacket is 2,000 units. The t is $35/item/year. How many leather jakcets should it order in one shipment ? nventory control system. It wishes to hold five weeks of demand as safety stock. ket is 102, how many leather jackets should be ordered? -I ered when there are 102 on hand. t + Order-processing cost 68) * 40 6 The following table presents the Net Sales of each major product category (and related services) for fiscal year 2015 (dollar amounts in millions): Product Category Kitchen 8,403 Indoor Garden 7,550 Paint 7,300 Outdoor Garden 6,394 Building Materials 6,055 Lumber 6,050 Flooring 5,986 Plumbing 5,740 Electrical 5,648 Tools 5,384 Hardware 4,974 Millwork 4,694 Bath 3,923 Dcor 2,576 Lighting 2,499 Total 83,176 Coffee Industry Statistics Total percentage of Americans over the age of 18 that drink coffee everyday 54% Average size of coffee cup 9 ounces Average price of an espresso-based drink $2.45 Average price for cup of brewed coffee $1.38 Total percentage of coffee drinkers who prefer their coffee black 35% Total percentage of coffee consumption that takes place during breakfast hours 65% Total amount of money spent by importing coffee to U.S. each year $4 billion Total percentage of coffee Brazil produces of entire worlds output 30% Total amount of cups of coffee (9 ounces) a coffee drinker consumes daily 3.1 Total average of money spent on coffee each year by coffee drinker $164.71 Total number of U.S. daily coffee drinkers 100 million Total number of U.S. daily coffee drinkers who drink specialty beverages (lattes, cappuccinos, mochas,30 million Total percentage of coffee drinkers who drink 13 or more cups of coffee each week 24% Percent of coffee drinkers who go to premium places (Starbucks, Coffee Bean) when they get coffee 34% Percent of people who go to lower-price outlets (Mcdonalds, Dunkin Donuts, etc.) when they get cof 29% Total percentage of coffee consumed between meals 30% Total percentage of coffee drinkers who add cream and/or sugar 65% Total amount of U.S. coffee drinkers who claim to need a cup of coffee to start their day 60% Total percentage of coffee drinkers who say coffee makes them feel more like their self 54% Total percentage of coffee drinkers who have a a cup within the first hour of waking up 68% Total amount of yearly money spent on specialty coffee in the U.S. 18 billion Starbucks Company Statistics Breakdown of Starbucks Product Sales Percent of Sales Beverages 75% Food 19% Packaged and Single Serve Coffee 4% Coffee-making equipment and other merchand 2% COCA-COLA ENTERPRISES, INC. REPORTS FOURTH-QUARTER AND FULL-YEAR 2015 RESULTS CCE achieved full-year diluted earnings per share of $2.54 on a reported basis or $2.58 on a comparable basis, including a negative currency impact of 18 percent. Full-year net sales totaled $7.0 billion, a decline of 15 percent on a reported basis, or down 1 percent on a currency-neutral basis; volume declined percent, and net pricing per case declined 1 percent. Full-year operating income was $866 million on a reported basis, down 15 percent; comparable operating income was $949 million, down 13 percent or up 1 percent on a comparable and currency-neutral basis. Fourth-quarter diluted earnings per share totaled 67 cents on a reported basis, or 53 cents on a comparable basis, including a negative currency impact of 7 cents CCE affirms guidance for 2016, including slightly positive growth for full-year comparable and currencyneutral net sales. http://www.coca-colacompany.com/press-center/press-releases/the-coca-cola-company-reports-fourth-quarter-and-full-year-2015-results/ Fourth quarter 2015 results included: Global comparable sales increase of 5.0% Consolidated revenues decrease of 4% (increase of 5% in constant currencies) Consolidated operating income increase of 7% (16% in constant currencies) Diluted earnings per share of $1.31, an increase of 16% (26% in constant currencies) Full year results included: Global comparable sales increase of 1.5% Consolidated revenues decrease of 7% (increase 3% in constant currencies) Consolidated operating income decrease of 10% (flat in constant currencies) Diluted earnings per share of $4.80, flat (increase 10% in constant currencies) http:/ews.mcdonalds.com/Corporate/Press-Releases/Financial-Release?xmlreleaseid=123073