The case. Please read and answer as needed. This is all one question. Thank you.

Instructions: - Read the business case and provide your response as outlined in this document. - Provide your responses on a word document and show your calculations on an excel spreadsheet. - Your analysis must be comprehensive. Provide explanations and calculations to support your analysis. Assume the role of the intern who has been tasked with determining appropriate forecasting tools and inventory management techniques to avoid stockouts. Your job is primarily to use the existing sales data to determine the appropriate forecasting technique. The forecasting techniques under consideration are (a) Exponential smoothing; (b) Simple linear regression; and (c) Decomposition using least squares regression. You will have to compute forecasts using each of these techniques and then determine which of the three provides a more accurate forecast based on Mean Absolute Deviation. You must then provide a recommendation regarding the choice of forecasting technique. This must be supported by your calculations and explanations. Structure of the report: Section 1 - Case background (i.e. context of the case) \& Problem statement - Provide a brief background of the company and its activities and then explain the situation or problem on hand. Section 2-Analysis and Discussion - This is where you will show your analysis, calculations, explanations, rationale etc. You will outline your approach to addressing the problem and then proceed to address the problem. Use excel spreadsheets for your calculations and include snapshots of the spreadsheet tables in the document to support your analysis. These snapshots could be part of the appendix section. - You can include any tables or charts to support your analysis. Additional guidelines: - USE THE SALES DATA PROVIDED TO YOU - For the exponential smoothing technique, assume that the forecast for Period 2 is equal to the actual sales in Period 1 . This will the initial forecast to begin with. - As per the case, the intern uses an Alpha of 0.4 for the exponential smoothing technique. Discuss what the most appropriate value of alpha should be in this situation. Support it with calculations. - The periods in the data sheet indicate months of the year. Period 1=Ian,2= Feb and so on. Period 13 would be Jan of the next year and so on. - Assume demand seasonality is based on months of the year. - Make sure your excel spreadsheet is well organized with borders and appropriate formatting. Show each forecasting model on a separate worksheet and name your worksheets accordingly. portfolio, which included several brands, its best-selling product remained Noto 37 , a Chinese herbal medicine used to control cholesterol and blood pressure levels. Jason Kwok, general manager of Chinese Pharmaceuticals, was very pleased with the strong sales of its marquee Noto 37 product. Those strong sales, though, were creating other challenges for the company. In the previous week, Jason had learned that Noto 37 was out of stock again. Retail space in Hong Kong was precious, and retailers like Mannings could not afford to have empty shelves. If his company could not get product delivered to Mannings shortly, Mannings. purchasing manager might be forced to stock other herbal medicine products. Jason needed to review the supply chain for Noto 37 and determine some ways to first improve the company's sales forecasts and then implement better inventory management practices. Chinese Pharmaceuticals (HK) Limited Chinese Pharmaceuticals followed the trajectory of many other Hong Kong companies. It began as a small, family-owned trading company in 1998, dealing primarily in Chinese herbs. As margins of trading companies continued to be squeezed and the Hong Kong government encouraged the development of higher- value industry sectors, Chinese Pharmaceuticals decided to develop its own, proprietary Chinese herbal medicine products. Through the government endowed Hong Kong Science and Technology Park, it established its first research and development office in 2007. Thus began its transformation from strictly a trading entity to a developer and marketer of its own branded range of Chinese herbal medicine products. Its first signature product was Noto 37 , an herbal remedy whose primary ingredient was Notoginseng, known to be effective in controlling cholesterol and also in improving cardiovascular health. Products. Over the years, the company had gradually developed additional products, such as Gaba, Banana Detox, and Water-Bean. Each of these new products addressed different ailments. Gaba, for example, was developed to address insomnia; Banana Detox was developed as an herbal colon cleansing and detoxification regimen; and Water-Bean was targeted at the booming slimming and weight loss market. Nevertheless, Noto 37 remained the star performer for the company and accounted for the majority. of the company's sales and revenues as much as 80% during the peak winter sales period Market. As a Hong Kong company with well-established roots in the local market, Chinese Pharmaceuticals decided to make Hong Kong the main foshs of its sales and marketing efforts. The Hong Kong market accounted for as much as 95% of the company's sales. The other market for the company was Macau, with sales having also begun to the Chinese community in the United States through an agent The Hong Kong Market for Chinese Herbal Medicines Main Brands in the Market. Traditional Chinese medicines had been used by people of Chinese origin for hundreds of years, with the earliest written documentation dating to almost 2,000yearsago. These traditional medicines, therefore, had always been a part of Hong Kong society. As Hong Kong society developed, traditional outlets for Chinese medicines, such as well-established players like Eu Yan Sang, adapted their business models. In addition to selling Chinese herbal medicines in traditional loose form, they also sold products in capsule form and in eye-catching retail packaging. While the. maiority of their retail products had been sold under their house brand-Eu Yan Sang-they also developed several sub-brands. While Eu Yan Sang had been a long-term player in the market, newer entrants included PuraPharm, which developed an extensive range of consumer Chinese herbal medicine products for almost every possible ailment. PuraPharm's products had been sold under the PuraPharm and PuraPharm Gold brands, as well as a multitude of other brands. Several other players had also entered the increasingly crowded market for Chinese herbal medicines in Hong Kong. Chinese Pharmaceuticals' Market Share. Chinese Pharmaceuticals confronted what was not necessarily a saturated market, but an increasingly competitive marketplace that featured companies with well-established brands and newer entrants eager to capitalize on the trend of packaged, easyto-consume Chinese herbal medicines. Nevertheless, with its initial product, Noto37, Chinese Pharmaceuticals had been able to capture 40% of the market share for Chinese herbal medicines targeting cholesterol, blood pressure, and related cardiovascular ailments. It had done so through effective marketing campaigns that included special promotions in a different mall each week, and. also the use of local celebrity spokespersons. Consumer Awareness of Health Issues. The development of the Chinese herbal medicine market in Hong Kong was also affected by greater awareness of major health issues through information obtained from readily accessible media outlets, including print, television, and the Internet. The Hong Kong government, for example, provided statistics from a 2009 study of the leading causes of death in Hong Kong on its Brand Hong Kong Web site. Two of the top four leading causes of death were diseases of the heart and stroke, both of which could be impacted by high cholesterol levels. Therefore, the launch of Chinese Pharmaceuticals" Noto 37, which addressed problems of the heart and cholesterol, coincided well with the increased awareness of such ailments among consumers in Hong Kong. China Vendor. The Notoginseng ingredient used in Chinese Pharmaceuticals" flagship product, Noto37, originated from Yunnan Province in China. Chinese Pharmaceuticals had an established supplier of the Notoginseng in Yunnan that was able to provide the raw material in a "super fine" form, enabling it to be prepared into ready-to-consume tablets or capsules. While the Notoginseng was available year-round, it was particularly abundant in the spring and winter. The excess supply during these times of the year also resulted in lower prices. Due to cash-flow constraints, Chinese Pharmaceuticals was not always able to take advantage of the lower prices and preferred instead to purchase regular quantities yearround. From the time of order placement, the supplier was usually able to deliver the Notoginseng in super fine form within four to six weeks. Testing of Raw Materials. Prior to taking delivery of new stock of Notoginseng, Chinese Pharmaceuticals conducted testing of random samples at independent labs in China to ensure that excess metals or toxins were not present. It also conducted a water content test, as excess water could contribute to bacteria growth. Such testing nomally took no more than seven to nine days. Hong Kong Manufacturer. Upon successful testing of the samples, new deliveries of Notoginseng were made to Chinese Pharmaceuticals' leased warchouse facility in Hong Kong. Stock was held in the warchouse until it was ready to be delivered to the Hong Kong based contract manufacturer, which then required up to two months to complete production of the Noto37 product. Newly manufactured Noto37 was delivered to the same warchouse facility. The total lead time, therefore, from the time Chinese Pharmaceuticals placed its initial order with its Yunnan supplier to having stock of new product to deliver to its local customers, was at least 90-100 days. Weekly Deliveries Chinese Pharmaceuticals worked with a local transportation company to arrange delivery of new stock of Noto37 to its local customers, usually once or twice a week. In addition to deliveries to its largest customer, Mannings, which operated 300 stores in Hong Kong deliveries also had to be coordinated to Mannings' main rival, Watson's, with over 180 outlets in Hong Kong and Macau, , as well as to over 500 local, neighborhood pharmacies throughout Hong Kong. Besides delivering new stock to the retail outlets, Chinese Pharmaceuticals also had to intermittently collect old or damaged stock from the stores. These would usually be minimal, and the company ordinarily simply disposed of retum stock. Seasonality of Sales. The winter months leading up to the Chinese New Year Festival were the clear peak season for sales of Noto 37 , with sales increasing as much as 50% above average during the coldweather season. Sales would then, ordinarily, decrease by as much as 50% below average during the subsequent spring season, while stabilizing toward their annual average during the summer and autumn months. Management of Inventory at the Hong Kong Warehouse As the company did not have its own warchouse facilities, but leased out storage space, it had to carefully manage its storage needs. Leading up to its peak winter sales period, the company would secure additional warehouse space to accommodate its higher inventory levels. Ideally, the company would seck to maintain at most three months' supply of stock, as this was also the capacity of its leased warchouse space for finished products. Reordering was triggered when stock levels shrunk to as little as 1.5 months' supply. The reality, however, was that various factors impacted the stock levels, including the biweckly deliveries of different quantities to almost 1,000 retail outlets, intermittent delays in supply of new stock from the manufacturer, and drought conditions in Yunnan that could impact supply of the key Notoginseng. Company sponsored weekly promotions plus the company's own intermittent promotions further contributed to fluctuations in demand and, consequently, on stock levels. While stock levels could be expected to fluctuate during the_@ourse of the year, Jason realized that an out-of-stock situation was certainly not acceptable, especially for the company's best-selling product and primary revenue driver. He struggled, therefore, to establish a more predictable means of managing the inventory. Inventory management for the company had been predicated more on circumstance than any systematic inventory management practice. For example, the company's inventory position 11 was likely affected more by such factors as the available warchouse space leased and less by any formal assessment and analysis of inventory positions in prior months. Jason realized that it would be prudent to incorporate more effective forecasting and more formalized inventory management practices. Jason vaguely recalled inventory management and forecasting exercises he had undertaken during one of his university courses many years earlier and wondered if such techniques would be appropriate for his company. As the company had recently hired an intem who was completing his MBA, Jason decided to assign the intem with the task of determining more appropriate inventory management and forecasting tools the company could utilize to improve its inventory management practices and avoid his current predicament of no stock. Forecasting Tools The Intern's Recommendation. Provided with access to the company's sales history for the previous three years [see Exhibit 2], the intem considered the options. He realized that Jason required an urgent assessment of the company's sales data and a recommendation for a forecasting tool that could be implemented in the near tem. With a better forecasting model, Jason would then be in a better position to consider revised inventory management practices based on an analysis of past demand plus current demand, as well as forecast demand for the Noto37. In reviewing the sales data, the intem realized that different variables could have impacted the data over the previous three years. So, how could he best use this historical data to foresee future sales? One method the intem identified was simple exponential smoothing. Importing the data into an Excel workshect and utilizing Excel tools, he proceeded to carry out the exponential smoothing exercise in the hopes of delivering useful forecasting results [see Exhibit 3 for simple exponential smoothing results]. Pleased with his efforts, the intem presented the results to Jason in the hopes that the information would be useful to Jason in improving the company's forecasting and inventory management challenges. Concerns about Accurate Forecasting Immediately after his mecting with Mannings, Jason had obtained an update on deliveries of Notoginseng from his supplier in Yunnan and convinced the supplier to use extra workers to gather and process an additional supply of Notoginseng for his contract manufacturer in Hong Kong. He had also confimed that testing for metals and toxins could be completed in half the nomal time. He had also then pressed the manufacturer in Hong Kong to jump the production queue so that he could get an initial small delivery of product in two wecks' time, with a larger quantity to then be delivered in thirty days. He hoped this would appease the purchasing managers at Mannings and other retail outlets. In addition, however, Jason reviewed the work done by his new intem. He was pleased with the quick tumaround on the part of the intem. Nevertheless, as Jason reviewed his company's sales data for the previous three years and the forecasts the intem had compiled, he wondered how accurate the calculations were. Might there be some additional forecasting methods that could produce more accurate results? Jason was keen to establish the right forecasting model for the company, which he hoped would then lead to improved inventory management of the company's bestselling Noto37, and fewer challenging mectings with purchasing managers. Conclusion Chinese Pharmaceuticals was a Hong Kong success story in more ways than one. Transforming itself from a trading company to a researcher, developer, and marketer of award -winning and market-leading Chinese herbal medicine products was no small task. Much effort and investment had gone into building up the company. The company's best-selling product, Noto37, was a good reflection on the company that developed it. It was a consumer-friendly Chinese herbal medicine product in capsule form that was promoted by celebrities and gladly given a prominent space on the shelves of the leading drugstore chain in Hong Kong, Mannings. With growth, however, had come some growing pains. Most critical for the company and its general manager, Jason Kwok, was a misalignment between production and delivery of the Noto 37 product and the high demand for the product in the market. This resulted in an out-of-stock situation for Noto37 and an unhappy purchasing manager at the company's biggest account, Mannings. One means to rectify the situation was to enhance the company's ability to improve the sales forecasts for its best-selling item. With an improved ability to forecast demand, the company could undertake the necessary adjustments in its supply chain to ensure that adequate inventory of product was always maintained at the local warchouses it leased. It was critical, therefore, that the forecasting method used would provide Jason with the most accurate estimates of future demands. \begin{tabular}{|c|c|} \hline Period & Demand \\ \hline 1 & 12666 \\ \hline 2 & 12427 \\ \hline 3 & 10656 \\ \hline 4 & 10642 \\ \hline 5 & 10949 \\ \hline 6 & 12005 \\ \hline 7 & 12166 \\ \hline 8 & 12240 \\ \hline 9 & 12234 \\ \hline 10 & 12216 \\ \hline 11 & 13296 \\ \hline 12 & 13443 \\ \hline 13 & 15043 \\ \hline 14 & 14981 \\ \hline 15 & 11845 \\ \hline 16 & 11866 \\ \hline 17 & 11888 \\ \hline 18 & 13661 \\ \hline 19 & 13728 \\ \hline 20 & 13766 \\ \hline 21 & 13755 \\ \hline 22 & 13992 \\ \hline 23 & 14717 \\ \hline 24 & 15326 \\ \hline 25 & 16986 \\ \hline 26 & 16920 \\ \hline 27 & 13032 \\ \hline 28 & 13141 \\ \hline 29 & 13210 \\ \hline 30 & 14990 \\ \hline 31 & 15267 \\ \hline 32 & 15259 \\ \hline 33 & 15310 \\ \hline 34 & 15432 \\ \hline 35 & 16128 \\ \hline 36 & 17058 \\ \hline \end{tabular}