Number each computation below the table and demonstrate your calculations OR you may submit an Excel worksheet with ALL your computations here below is the case study that is used with these charts

1. Compute the Ordering Cost/SKU.

2. Compute the Inventory Holding Cost as a percentage of Item Cost

3. Compute the EOQ with safety stock for Strike Disinfectant in gallons

4. Compute the Reorder Point with safety stock for Strike Disinfectant

5. Compute the Total Order and Inventory Holding Costs for a fixed-quantity system (FQS), Compute the Order Q Total Costs and compare the results. Can you save money by adopting an FQS?

6. What are your final recommendations, including what you would recommend regarding regular and special orders, the state bidding system, and overall control of the university materials-management system? Discuss at least four recommendations.

Case Brief Overview

A university and university hospital's materials management systems are interdependent and neither is under control. The ordering and inventory control system experienced stock outs on some items, excessive inventory on other items, multiple stocking points throughout the hospital and university with no assigned process or inventory item owner, used a paper-based system, and an array of different types of regular and special orders. Originally the purpose of processing some hospital orders through the university system was to qualify for price breaks and lower total costs to the university. Inventory in the hospital was stocked at one central stocking point, 31 hospital departments, and 215 secondary stocking points. Deliveries were made daily to the central storeroom and the 31 departments. Orders for less than $800 did not go through the regular ordering processes. Real hospital data is provided in the case on one SKUs that is assumed to be representative of all SKUs. Given these data the Director of Materials Management wanted to evaluate if formal inventory control methods and systems could make a difference.

1. Ordering Cost	$18.00
2. Inventory-Holding Cost	27.24%
3. EOQ with Safety Stock	61.62
4. Reorder Point with Safety Stock	45
5. EOQ Total Order Costs	$353.32
6. Current Order Q Total Costs	$627.83
7. EOQ Total Savings	$274.51

What are your final recommendations? Discuss at least four recommendations. Include recommendations of regular and special orders, the state bidding system, and overall control of the university-management system.

Number and identify your recommendations.

FQS Safety Stock Template (2) Share Comments AutoSave OFF ASSE: Home Insert Draw Page Layout Av Calibri (Body) 11 BI U B O | 68 x fx Formulas A A A Data Review = = = View Sou E all Wrap Text Merge & Center Ayo General $ % D P D Conditional Format Formatting as Table Insert SX Delete T Z Paste 4800 % Cell Styles Sort & Filter Ideas Format Find & Select Sensitivity X F G H I J K L M N O P Q R S T U VW 1 Fixed Order Quantity Inventory System with Uncertain Demand Enter the data only in the yellow cells. Average Annual Demand Ordering Cost Unit Cost Carrying Charge Rate Lead time (weeks) Standard deviation of weekly demand Service level 15000.00 $45.00 $3.80 0.20 2.00 71.00 0.95 Economic Order Quantity Order cost Inventory cost Total cost 1332.78 $506.46 $506.46 $1,012.92 Average demand during lead time Standard deviation during lead time z-value Safety stock Reorder point 576.92 100.41 1.64 165 742 Sheet1 + @ J -G O + 100% AutoSave OFF S U E EOQ Template (1) Home Insert Draw Page Layout Formulas Data Review View Comments Share 5 = = = ab Wrap Text E 48- O Calibri (Body) BI U B 11 O A A A General $ % A 92 B Conditional Format Cell Formatting as Table Styles 3 insert v 9X Delete Format Paste E Merge & Center od -20 Sort & Filter Find & Select Ideas X Sensitivity L8 x fx 2 AA AB 1 Economic Order Quantity Model 2 Enter the data only in the yellow cells. Use the steps below to create the chart Annual Demand Rate Ordering Cost Unit Cost Carrying Charge Rate 24000.00 $28.00 $12.00 0.18 Inventory Costs Economic Order Quantity Order cost Inventory cost Total cost 918.94 $992.45 $992.45 $1.984.90 Cost 14 Sensitivity Analysis 15 Enter any order quantity to evaluate the cost and compare with EOQ Order Quantity Order cost $912,000.00 Inventory cost $1.08 Total cost $912,001.08 ---Order oost Holding cost Total cost EOQ Benefit $910,016.18 4594602917 5613619601 6432505084 736 1492668 827042925 918930083 1070.830242 1102.7230 1194.617560 1285.511217 1378 404875 rrier Al Chart Calculations Order Quantity Chart Title $3.000 DO 4591 $2,500.00 $2,000.00 551 543 735 8271 9191 1011 1103 1195 1287 1378 Order cost $1,984.90 $1.654.09 $1,417.79 $1,240.56 $1,102.72 $992.45 $902.23 $827.04 $763.42 $708.89 $661.63 Inventory cost $496.23 $595.47 $694.72 $793.96 $893.21 $992.45 $1,091.70 $1,190.94 $1,290.19 $1,389.43 $1,488.68 Total cost $2,481.13 $2,249.56 $2,112.50 $2,034.53 $1,995.93 $1,984.90 $1,993.93 $2,017.98 $2,053.61 $2,098.33 $2,150.31 &$1,500D Order most Inventory cost $1,000 Totalco $500.00 $0.00 459 551 543 735 1287 1379 827 919 1011 1108 1195 Order Quantity 44 HOW TO MAKE THE CHART 46 1. Select the range 330:041 47 2. Click the Insert tab, select Line Chart with Markers 48 3. Select the chart, click the Chart Elements ) tool, point to legend, click Right 49 4. Right click the chart, click Select Data. Click Edit Horizontal Category, select the range A31:041 SO S. Select the chart, click the Chart Elements tool . Click the Axes Title box 51 6. Click each Ads Title and type Order Quanity for horizontal, and cost for Vertical, press Enter after each entry Sheet1 + - - 75% FQS Safety Stock Template (2) Share Comments AutoSave OFF ASSE: Home Insert Draw Page Layout Av Calibri (Body) 11 BI U B O | 68 x fx Formulas A A A Data Review = = = View Sou E all Wrap Text Merge & Center Ayo General $ % D P D Conditional Format Formatting as Table Insert SX Delete T Z Paste 4800 % Cell Styles Sort & Filter Ideas Format Find & Select Sensitivity X F G H I J K L M N O P Q R S T U VW 1 Fixed Order Quantity Inventory System with Uncertain Demand Enter the data only in the yellow cells. Average Annual Demand Ordering Cost Unit Cost Carrying Charge Rate Lead time (weeks) Standard deviation of weekly demand Service level 15000.00 $45.00 $3.80 0.20 2.00 71.00 0.95 Economic Order Quantity Order cost Inventory cost Total cost 1332.78 $506.46 $506.46 $1,012.92 Average demand during lead time Standard deviation during lead time z-value Safety stock Reorder point 576.92 100.41 1.64 165 742 Sheet1 + @ J -G O + 100% AutoSave OFF S U E EOQ Template (1) Home Insert Draw Page Layout Formulas Data Review View Comments Share 5 = = = ab Wrap Text E 48- O Calibri (Body) BI U B 11 O A A A General $ % A 92 B Conditional Format Cell Formatting as Table Styles 3 insert v 9X Delete Format Paste E Merge & Center od -20 Sort & Filter Find & Select Ideas X Sensitivity L8 x fx 2 AA AB 1 Economic Order Quantity Model 2 Enter the data only in the yellow cells. Use the steps below to create the chart Annual Demand Rate Ordering Cost Unit Cost Carrying Charge Rate 24000.00 $28.00 $12.00 0.18 Inventory Costs Economic Order Quantity Order cost Inventory cost Total cost 918.94 $992.45 $992.45 $1.984.90 Cost 14 Sensitivity Analysis 15 Enter any order quantity to evaluate the cost and compare with EOQ Order Quantity Order cost $912,000.00 Inventory cost $1.08 Total cost $912,001.08 ---Order oost Holding cost Total cost EOQ Benefit $910,016.18 4594602917 5613619601 6432505084 736 1492668 827042925 918930083 1070.830242 1102.7230 1194.617560 1285.511217 1378 404875 rrier Al Chart Calculations Order Quantity Chart Title $3.000 DO 4591 $2,500.00 $2,000.00 551 543 735 8271 9191 1011 1103 1195 1287 1378 Order cost $1,984.90 $1.654.09 $1,417.79 $1,240.56 $1,102.72 $992.45 $902.23 $827.04 $763.42 $708.89 $661.63 Inventory cost $496.23 $595.47 $694.72 $793.96 $893.21 $992.45 $1,091.70 $1,190.94 $1,290.19 $1,389.43 $1,488.68 Total cost $2,481.13 $2,249.56 $2,112.50 $2,034.53 $1,995.93 $1,984.90 $1,993.93 $2,017.98 $2,053.61 $2,098.33 $2,150.31 &$1,500D Order most Inventory cost $1,000 Totalco $500.00 $0.00 459 551 543 735 1287 1379 827 919 1011 1108 1195 Order Quantity 44 HOW TO MAKE THE CHART 46 1. Select the range 330:041 47 2. Click the Insert tab, select Line Chart with Markers 48 3. Select the chart, click the Chart Elements ) tool, point to legend, click Right 49 4. Right click the chart, click Select Data. Click Edit Horizontal Category, select the range A31:041 SO S. Select the chart, click the Chart Elements tool . Click the Axes Title box 51 6. Click each Ads Title and type Order Quanity for horizontal, and cost for Vertical, press Enter after each entry Sheet1 + - - 75%