**Please Help! and show work!

CASE STUDY Northcutt Bikes: The Service Northcutt Bikes. It was information that he started to see the fute Department extent of the challenges that lay ahead. His first day on the fell really opened his eyes. One of the first items he had the history and shipped orders. was a status report on inventory history and shiped on has 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 on file are accurate, as we just completed our physical count of timen-entory last week. I was able to track down a demand histery for a to the job! 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 of 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. Unfortunately, the majority of customers viewed service parts as generic and would take their business elsewhere when parts were not available from Northcutt Bikes. What really hurt was when those businesses sometimes Introduction 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 the competitive strength of her company was developed misplaced. Increasing the inventory did not appear to be the around customer responsiveness and flexibility, she felt that answer, not only because a large amount was already being creating a separate department focused exclusively on after- held but also because the space in the warehouse (built less market service was critical in meeting that mission. than two years ago) had increased from being 45% utilized just When she established the department, she named Ann after it moved in to its present utilization of over 95%. Hill, one of her best clerical workers at the time, to establish Mike decided to start his analysis and development of and manage the department. At first, the department occu- solutions on the two items for which Art had already propied only a corner of the production warehouse, but now it has vided demand history. He felt that if he could analyze and corgrown to occupy its own 100,000-square-foot warehouse. The rect any problems with those two parts, he could expand the service business has also grown significantly, and it now rep-_ analysis to most of the others. The two items on which he had resents over 15% of the total revenue of Northcutt Bikes. The history and concentrated his initial analysis were the FB378 exclusive mission of the service department is to provide parts Fender Bracket and the GS131 Gear Sprocket. Northcutt Bikes (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 cost 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. To help her develop a more effective materials management The GS131 part, on the other hand, is a newer product program, she hired Mike Alexander, a recent graduate of an only recently being offered. A machine shop in Nashville outstanding business management program at North Carolina Tennessee, produces the part for Northcutt Bikes, and it give State University, to fill the newly created position of Materials Northcutt Bikes a fairly reliable six-week lead time. The cost o Manager of Aftermarket Service. placing an order with the machine shop is only about $15, an The Current Situation currently Northcutt Bikes orders 850 parts at a time. Nort During the interview process, Mike got the impression Butt Bikes buys the item for $10.75. that there was a lot of opportunity for improvement at Following is the demand information that Art gave Mike on his first day for the FB 378 and the GS131: 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 95% 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?" Q1 (3 points). What problems does the company face? Q2. Develop the forecasting models and find the forecasting accuracy. Q2-1 (3 points). Develop one exponential smoothing model (=0.4) for FB378 (week 1-week 26) and GS131 (week 11 -week 267). (Keep two decimals) Q2-2 (4 points). Calculate FE, MAD, and tracking signals for the exponential smoothing model ( =0.4 ) for FB378 (week 2-week 26) and GS131 (week 12-week 26). (Keep two decimals) \begin{tabular}{|c|c|c|c|c|c|} \hline & \multicolumn{5}{|c|}{ FB378 } \\ \hline Week & Actual demand & Forecast (=0.4) & Forecast Error (FE) & MAD & Tracking signal \\ \hline 1 & 45 & 40.00 & & & \\ \hline 2 & 44 & & & & \\ \hline 3 & 33 & & & & \\ \hline 4 & 39 & & & & \\ \hline 5 & 45 & & & & \\ \hline 6 & 30 & & & & \\ \hline \end{tabular} \begin{tabular}{|c|l|l|l|l|l|} \hline & GS131 & & & \\ \hline Week & Actual demand & Forecast (=0.4) & Forecast Error (FE) & MAD & Tracking signal \\ \hline 1 & & & & & \\ \hline 2 & & & & & \\ \hline 3 & & & & & \\ \hline 4 & & & & & \\ \hline 5 & & & & & \\ \hline 6 & & & & & \\ \hline \end{tabular} Q3. Answer several questions (below) Q3-1 (3 points) Determine if there is uptrend or downtrend from the actual demand of FB378 (week 1-26) and the actual demand of GS131. (Show a scatter plot with a linear line for the relationship between weeks and the actual demand of FB378 and weeks and the acutal demand of GS131.) A scatter plot (FB378) A scatter plot (GS131) (Insert here) (Insert here) Q3-2 (2 points) Determine whether the two forecasting models (for FB378 and GS131) are performing normally or not. Q4-1 (2 points). Use the available data to develop inventory policies such as EOQ and reorder point for the FB378 and GS131. Assume that the holding cost is 20% of unit price. (Fill out the table to the inventory policies) (52 weeks per year) \begin{tabular}{r|r|r|r|r|} \hline FB378 & GS131 & Annual demand: & 2480.00 \\ \hline Annual demand: & 1914.00 & Annual holding cost, per unit: & \\ \cline { 2 - 2 } Annual holding cost, per unit: & & Cost per order: & \\ Cost per order: & & Cost per unit:: & \\ \hline Cost per unit: & & & \\ \hline \end{tabular} EOQ annual ordering cost: EOQ annual ordering cost: EOQ total holding and ordering cost: EOQ total holding and ordering cost: Total savings using EOQ: Total savings using EOQ: Q6 (3 points). How many orders (per year) should the company make with the current order quantity to meet the customer demands for FB378 and GS131? How many orders (per year) should the company make with EOQ to meet the customer demands for FB378 \& GS131? (Show your calculations to receive full credits) Q7 (4 points). What are your suggestions to solve the current problem that the company is facing? (You need to answer the question within a minimum of 250 words)