Please can you show the MATH work and DATA in excel to better explained the problem.

Company Background

MediCrystals Co. is a leading German pharmaceutical glass manufacturer offering a solution for the entire range of basic volume tubular glass parenteral packaging including ampoules, vials, cartridges and sterile pre-fillable syringes. They have multiple manufacturing plants in Europe, America and South-East Asia. MediCrystals has customers all over the world and supplies to over 120 countries.

Changing Landscape

MediCrystals Co. aims to provide pharma vials for Covid-19 vaccines to Payima Biotec (a global generic and specialty pharmaceutical and vaccine maker) and various other similar entities. They have signed agreements with major pharmaceutical firms to supply pharma glass products for the packaging of two billion doses of Covid-19 vaccines. The vaccine being developed currently can only be stored in glass vials. When the vaccine is approved, globally, manufacturers will struggle to source enough glass vials, syringes, and ampoules to bottle enough for the global immunization drive. MediCrystals manufactures over 8 billion pharma glass containers for drugs annually. There's a glass shortage currently due to the sudden increase in demand for such products. Many of the suppliers being small and localized, MediCrystals needs to be sure that none of their suppliers are facing business continuity challenges due to the pandemic. Manufacturers globally are facing financial and operational challenges; the demand is unpredictable, and there are concerns related to shortages and stockouts.

Current Supply Chain Operations

VP Operations at MediCrystals has hired your team to leverage expertise around three critical areas her team has identified. First, the Manager of Procurement and Sourcing team has heard from the CPO that some of the critical suppliers are facing operational challenges due to the pandemic. She thereby, intends to develop a high-level risk scoring methodology or rationale to score the suppliers and review mitigation activities currently in place. Based on the supplier data available, the Risk Compliance Manager has currently identified two risk domains - Finance, Regulatory. As part of the Regulatory risk domain, she intends to score each supplier based on the risk factors Labor unrests, and Environmental incidents. She wants your team to identify other risk domains and factors contributing to the risk domains based on the available supplier data. She wants your team to develop a comprehensive risk scoring methodology and identify suppliers that pose the most risk.

The marketing team has gathered insights regarding the anticipated demand trend for various SKUs based on their product families. Your team as part of this project, has been requested to estimate the change in the inventory position to understand the working capital impact in one of the most critical plants in Switzerland, GlasWork. Based on the marketing insights gathered, she also wants to understand the potential obsolete inventory that they currently have.

And the third area of concern is to identify if there is any capacity constraint for Fabricadas, the new facilities in Chicago, due to the sudden increase in demand for their products in the US. The Plant Controller feels the current capacity is not adequate to meet his forecasted demand. However, the VP Operations for MediCrystals is certain that there is enough capacity to meet the requirements. Your team needs to evaluate if there is genuinely a capacity constraint for Fabricadas and provide recommendations on how to maximize utilization to meet the demand at current capacity. Based on the operations of MediCrystals, provide recommendations on how it should meet the increase in demand for these products. Explain why the Plant Manager feels the capacity is inadequate and why the VP Operations feels the capacity is enough.

The products are needed for vaccines for other diseases, too. Glass vials are very hard and time-consuming to make. Sometimes, they take months for the manufacturing process. Even though coronavirus is the most pressing health issues in the world, MediCrystals wants to maintain a supply of their products for other critical vaccines which cannot be distorted for Covid 19 vaccine vials supply.

This sheet contains data about the suppliers of MediCrystals collected by the 'Risk Complaince' team. Find the description of cach column below: Data Exhibit-1 Supplier Name - Name of the supplier Location - Location of the supplier warehouse from which the product is supplied to MediCrystals Revenue - Revenue of the suppliers for FY 2013 Cash from Operations - Cash flow from Operations reported by the supplier in their annual financial statements Credit Rating - Credit rating provided by D&B S-OTD - Supplier On-time delivery (S-OTD) performance for the past 12 months Single Source -"Y" - if the supplier is the only qualified source of supply for the product; "N" - if alternate suppliers are available to supply the product IP Protection - "Y" - if the supplier owns an IP for the product supplied and "N" if the supplier doesn't own any IP Data security - Internal scoring done for each supplier by MediCrystals assessing supplier's systems & Data security on scale of 0-10 (10 indicates excellent systems & data security and indicates very poor systems & data security) Labor Unrest - "Y" indicates if the supplier has faced any labor unrests in the past 12 months. "N" if no labor unrests have been faced by the supplier in the past 12 months. Environmental Incidents - "Y" indicates if the supplier has faced any environmental incidents in the past 12 months. "N" if no environmental incidents have been faced by the supplier in the past 12 months. This sheet contains data about the inventory at Glaswork. Each column describes the SKU information as described below: SKU - SKU NUmber Std. Price ($) - Price of each unit of SKU On-Hand Stock ($) - Total stock on-hand as of 30th Sep 20 APU (units) - Average monthly consumption (30 days) of the SKU APUTrend - Anticipated APU trend provided by marketing (c.g. 50% indicates 50% increase in APU consumption) S-OTD - Supplier On-time delivery (S-OTD) performance for the past 12 months Data Exhibit-2 Demand variability (COV). Demand Cocfficient of variance for the past 12 months Lead Time (days) - Lead time in days to procure the SKU Assumptions to be taken into consideration: - The Minimum Order Quantity (MOQ) is equal to one lead-time demand - The service level targeted can be assumed to be 95% - The expiry for all the mentioned SKUs is one year 7 This sheet has 4 tables with data related to demand for the products from Fabricadas. The first table has the demand projections for the year 2020, starting from Q3 of 2020 to Q2 of 2021 for each product in units of lots of cach product manufactured in a single quarter. Data Exhibit-3 The second table mention the cycle time for each product in number of hours for each lot. The third table mentions cycle time for each product broken up according to the respective process times, in number of hours for cach lot. The final table of the sheet contains the net lot equivalents rejected in a quarter by Fabricadas, averaged from the previous year's data. 8 According to the plans shared with the team, steps for manufacturing can be said to have the following processes: the glass tubing capacity unit, the hot-forming process unit that creates the required shape, washing and inspection unit and finally the packing and distribution unit. Data Exhibit-4 Each plant manufactures the three products: Ampoules, Tubular Vials and Glass Syringes. Each product has it's own separate movement through the manufacturing chain which is chown in the figure of this chest. Below figure is the data on shutdowns of Fabricadas provided by the Plant Manager. Each column gives the number of days a particular unit of the plant remains shut in a year, on an average and for what reasons. Note: For one plant, only one product can undergo one process at a time. Mention any ascumptions it required with clear reasoning mentioned alongside. 10 1 S-OTD Data security 9 8 ZZZ- EZZE Data as of 30th Sep'20 Supplier Location Revenue Name Plaxian US $6,040M GutesGlas Germany $8,293M Boavidro Brazil $9,287M Saanch India $3,147M RealGlass China $37,719M Optikiet China $113M BestoGlass US $5,379M MedicMetric Philippines $10M Shale Saudi Arabia $2,431M Opticful Italy $95M basicPharm US $395M PharmyLeaf India $1,431M 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 10 Cash from Operations $354M $905M $364M $436M $3,996M $23M $24M $10M $309M $6M $9M $340M Credit Rating 3 3 3 2 1 4 4 2. 4 3 3 5 2 0.91 0.85 0.63 0.9 0.82 0.88 0.78 0.94 0.86 0.92 0.88 0.79 Single Source Y N N N Y Y Y Y N N Y N N IP Protection N N N N Y Y N Y Y N N Y N Labour Unrests Y N N N Y N N N N N N N N 0000OOOOOO Environmental Incidents N N N N Y N N N N N Y N N N N 10 Z