PLEASE DO QUESTION NUMBER 2"""ALWAYS FRESH NEW ANSWERS

Please look at below business operations question #2 and answer it. You are encouraged to use an excel spreadsheet to produce the final result and submit that as the answer if need be. For all excel functions used, please clearly show the excel formulas/functions you used. For all the math that was done to arrive at the answer, please show your work. Please complete asap. I understand I may have posted this question before, but please do not copy and past the same exact answer your other Chegg teammates provided from the previous post. I NEED EACH ANSWER TO BE UNIDENTICAL TO THE LAST. Thank you for your help and understanding. Also, I will be sure to give you a big thumbs up.

CASE STUDY Northcutt Bikes: The Service Department Northcutt Bikes. It was only after he selected his startin and requested some information that he started to see the ful extent of the challenges that lay ahead. His first day on the jo really opened his eyes. One of the first items he had requested status report on inventory history and shipped orders. In response, the following note was on his desk the first day from the warehouse supervisor, Art Demming: We could not compile the history you requested, as we keep no such records. There's just too much stuff in here to keep a close eye on it all. Rest assured, however, that we think the inventory positions on file are accurate, as we just completed our physical count of inven- tory last week. I was able to track down a demand history for a couple of our items, and that is attached to this memo. Welcome Mike decided to investigate further. Although the records were indeed difficult to track down and compile, by the end of his second week he had obtained a fairly good picture the situation, based on an investigation of 100 parts selected at random. He learned, for example, that although there was an average of over 70 days' worth of inventory (annual sales average inventory), the fill rate for customer orders was less than 80%, meaning that only 80% of the items requested were in inventory; the remaining orders were backordered. Unfor- tunately, the majority of customers viewed service parts as generic and would take their business elsewhere when parts were not available from Northcutt Bikes. Introduction What really hurt was when those businesses sometimes Several years ago, Jan Northcutt, owner of Northcutt Bikes, canceled their entire order for parts and placed it with another recognized the need to organize a separate department to deal parts supplier. The obvious conclusion was that while there with service parts for the bikes her company makes. Because was plenty of inventory overall, the timing and quantities were misplaced. Increasing the inventory did not appear to be the answer, not only because a large amount was already bein the competitive strength of her company was developed around customer responsiveness and flexibility, she felt that creating a separate department focused exclusively on after- market service was critical in meeting that mission held but also because the space in the warehouse (built le than two years ago) had increased from being 45% utilized just When she established the department, she named Ann Hill, one of her best clerical workers at the time, to establish after it moved into its present utilization of over 95%. Mike decided to start his analysis and development of pied only a corner of the production warehouse, but now it has vided demand history. He felt that if he could analyze and cor ervice business has also grown significantly, and it now rep- analysis to most of the others. The two items on which he had exclusive mission of the service department is to provide parts Fender Bracket and the GS131 Gear Sprocket. Northcutt Bikes and manage the department. At first, the department occu- grown to occupy its own 100,000-square-foot warchouse. The resents over 15% of the total revenue of Northcutt Bikes The solutions on the two items for which Art had already pro- rect any problems with those two parts, he could expand the initial analysis were the FB378 history and concentrated his i (tires, seats, chains, etc.) to the many retail businesses that sell purchases the FB378 from a Brazilian source. The lead time and service Northcutt Bikes has remained constant, at three weeks, and the estimated co While Ann has turned out to be a very effective manager of a purchase order for these parts is given at $35 per order. (and now holds the title of Director of Aftermarket Service), Currently Northcutt Bikes uses an order lot size of 120 for the she still lacks a basic understanding of materials management. FB378 and buys the items for $5 apiece The GS131 part, on the other hand, is a newer prod To help her develop a more effective materials management program, she hired Mike Alexander, a recent graduate of an tstanding business management program at North Carolina Tennessee, produces the part for Northcutt Bikes, and State University, to fill the newly created position of Materials only recently being offered. A machine shop in Nashville, Northcutt Bikes a fairly reliable six-week lead time. The cost of Manager of Aftermarket Service The Current Situation During the interview process, Mike got the impression that there was a lot of opportunity for improvement at placing an order with the machine shop is only about $15, an currently Northcutt Bikes orders 850 parts at a time. cutt Bikes buys the item for $10.75 Following is the demand information that Art gave to Mike on his first day for the FB378 and the GS131 CHAPTER 11 MANAGING INVENTORY THROUGHOUT THE SUPPLY CHAIN 363 GS131 FB378 GS131 ACTUAL RECAST DEMAND FORECAST DEMAND ACTUAL WEEK ACTUAL ACTUAL WEEK FORECAST DEMAND FORECAST DEMAND 30 32 35 34 35 38 36 54 36 35 36 42 26 27 45 53 39 48 30 26 Mike realized he also needed input from Ann about her perspective on the business. She indicated that she felt strongly that with better management, Northcutt Bikes should be able to use the existing warehouse for years to come, even with the anticipated growth in business. Currently, however, she views the situation as a crisis because "we're bursting at the seams with inventory. It's costing us a lot of profit, yet our service level is very poor, at less than 80%. I'd like to see us maintain a 37 36 37 38 30 27 12 13 14 15 40 31 38 32 49 24 30 42 5% or better service level without back orders, yet we need to be able to do that with a net reduction in total inventory. What do you think, Mike? Can we do better?" 52 36 54 52 53 53 52 53 Questions 37 35 37 36 34 36 37 36 57 59 46 62 1. Use the available data to develop inventory policies (order 2. Compare the inventory costs associated with you 3. Do you think the lost customer sales should be included as quantities and reorder points) for the FB378 and GS131. Assume that the holding cost is 20% of unit price. r sug- 20 21 24 52 gested order quantities with those of the current order quantities. What can you conclude? 30 37 54 53 60 46 58 a cost of inventory? How would such an inclusion impact the ordering policies you established in question 1? CASE STUDY Northcutt Bikes: The Service Department Northcutt Bikes. It was only after he selected his startin and requested some information that he started to see the ful extent of the challenges that lay ahead. His first day on the jo really opened his eyes. One of the first items he had requested status report on inventory history and shipped orders. In response, the following note was on his desk the first day from the warehouse supervisor, Art Demming: We could not compile the history you requested, as we keep no such records. There's just too much stuff in here to keep a close eye on it all. Rest assured, however, that we think the inventory positions on file are accurate, as we just completed our physical count of inven- tory last week. I was able to track down a demand history for a couple of our items, and that is attached to this memo. Welcome Mike decided to investigate further. Although the records were indeed difficult to track down and compile, by the end of his second week he had obtained a fairly good picture the situation, based on an investigation of 100 parts selected at random. He learned, for example, that although there was an average of over 70 days' worth of inventory (annual sales average inventory), the fill rate for customer orders was less than 80%, meaning that only 80% of the items requested were in inventory; the remaining orders were backordered. Unfor- tunately, the majority of customers viewed service parts as generic and would take their business elsewhere when parts were not available from Northcutt Bikes. Introduction What really hurt was when those businesses sometimes Several years ago, Jan Northcutt, owner of Northcutt Bikes, canceled their entire order for parts and placed it with another recognized the need to organize a separate department to deal parts supplier. The obvious conclusion was that while there with service parts for the bikes her company makes. Because was plenty of inventory overall, the timing and quantities were misplaced. Increasing the inventory did not appear to be the answer, not only because a large amount was already bein the competitive strength of her company was developed around customer responsiveness and flexibility, she felt that creating a separate department focused exclusively on after- market service was critical in meeting that mission held but also because the space in the warehouse (built le than two years ago) had increased from being 45% utilized just When she established the department, she named Ann Hill, one of her best clerical workers at the time, to establish after it moved into its present utilization of over 95%. Mike decided to start his analysis and development of pied only a corner of the production warehouse, but now it has vided demand history. He felt that if he could analyze and cor ervice business has also grown significantly, and it now rep- analysis to most of the others. The two items on which he had exclusive mission of the service department is to provide parts Fender Bracket and the GS131 Gear Sprocket. Northcutt Bikes and manage the department. At first, the department occu- grown to occupy its own 100,000-square-foot warchouse. The resents over 15% of the total revenue of Northcutt Bikes The solutions on the two items for which Art had already pro- rect any problems with those two parts, he could expand the initial analysis were the FB378 history and concentrated his i (tires, seats, chains, etc.) to the many retail businesses that sell purchases the FB378 from a Brazilian source. The lead time and service Northcutt Bikes has remained constant, at three weeks, and the estimated co While Ann has turned out to be a very effective manager of a purchase order for these parts is given at $35 per order. (and now holds the title of Director of Aftermarket Service), Currently Northcutt Bikes uses an order lot size of 120 for the she still lacks a basic understanding of materials management. FB378 and buys the items for $5 apiece The GS131 part, on the other hand, is a newer prod To help her develop a more effective materials management program, she hired Mike Alexander, a recent graduate of an tstanding business management program at North Carolina Tennessee, produces the part for Northcutt Bikes, and State University, to fill the newly created position of Materials only recently being offered. A machine shop in Nashville, Northcutt Bikes a fairly reliable six-week lead time. The cost of Manager of Aftermarket Service The Current Situation During the interview process, Mike got the impression that there was a lot of opportunity for improvement at placing an order with the machine shop is only about $15, an currently Northcutt Bikes orders 850 parts at a time. cutt Bikes buys the item for $10.75 Following is the demand information that Art gave to Mike on his first day for the FB378 and the GS131 CHAPTER 11 MANAGING INVENTORY THROUGHOUT THE SUPPLY CHAIN 363 GS131 FB378 GS131 ACTUAL RECAST DEMAND FORECAST DEMAND ACTUAL WEEK ACTUAL ACTUAL WEEK FORECAST DEMAND FORECAST DEMAND 30 32 35 34 35 38 36 54 36 35 36 42 26 27 45 53 39 48 30 26 Mike realized he also needed input from Ann about her perspective on the business. She indicated that she felt strongly that with better management, Northcutt Bikes should be able to use the existing warehouse for years to come, even with the anticipated growth in business. Currently, however, she views the situation as a crisis because "we're bursting at the seams with inventory. It's costing us a lot of profit, yet our service level is very poor, at less than 80%. I'd like to see us maintain a 37 36 37 38 30 27 12 13 14 15 40 31 38 32 49 24 30 42 5% or better service level without back orders, yet we need to be able to do that with a net reduction in total inventory. What do you think, Mike? Can we do better?" 52 36 54 52 53 53 52 53 Questions 37 35 37 36 34 36 37 36 57 59 46 62 1. Use the available data to develop inventory policies (order 2. Compare the inventory costs associated with you 3. Do you think the lost customer sales should be included as quantities and reorder points) for the FB378 and GS131. Assume that the holding cost is 20% of unit price. r sug- 20 21 24 52 gested order quantities with those of the current order quantities. What can you conclude? 30 37 54 53 60 46 58 a cost of inventory? How would such an inclusion impact the ordering policies you established in question 1