This is what i have so far

This is the solution

Please help me understand how to get the solution

CHEMICAL COMPOUND CONSIGNMENT CONTRACT An Excel Template is available. We will work on completing it in the instructor videos. You have signed a consignment contract to supply a chemical compound to a major customer. This compound costs you $15/gallon and generates a contribution (margin) of $10/gallon. Delivery costs you $300 in shipping and handling. Demand for this year is estimated at 500 gallons and standard deviation is 50 gallons. The contract says that the compound is inventoried at your client's facility; he will draw the material from the stock as needed and pay you at such time. Annual cost of capital is 20%. The client proposes the following policy to control the stock-outs: Shortage/Stock-out Policy If the client finds that the inventory empty when needed, you are subject to a flat penalty charge of $600 per stock-out occasion. There is also variability in the lead-time; the expected lead-time is 30 days with a standard deviation of 15 days. a. Determine the optimal replenishment policy (specifically, the values of safety factor safety stock, order size, and re-order point). b. Report the implied the customer service level (its business meaning and the value). C. Suppose you could pay C dollars per year to get rid of the variability in its lead-time per order. What is the most you would be willing to pay to do this? Chemical compound consignment contract With variability in lead time and demand Inputs Without variability in lead time Inputs S $300 Setup cost per order i i 20% Annual cost of capital E(D) 500 Expected annual demand SiDev(D) 50 StDev of annual demand E(L) 0.0822 Expected lead time in years StDev(L) 0.04T StDev of lead time in years E(D) 41.10 Expected demand during lead time StDev(D) 25.05 StDey of demand during lead time P (shortage penalty) $600 Shortage cost incurred each cycle with a shortage $15 unit purchase cost Unit profit $10 contribution margin h(unit inventory holding cost per $3.00 Optimal solution using Solver to choose Q and k S $300 i 20% E(D) 500 StDev(D) 50 ELL) 0.0822 SiDev(L) 0.0411 E(D) 41.10 StDev(D) 25.05 p (shortage penalty) $600 $15 Unit profit $10 h(unit inventory holding cost pe $3.00 Optimal solution using Solver to choose Q and k Q k k"StDev(D) P(Zk) R 327.9 1.78 44.5 0.96 0.04 order size safety factor safety stook b) customer service level probability of stock out reorder point E(DL)+ k*StDev(DL) a) when the inventory drops to about 86 gallons, a reorder of about 328 gallons Q k k"StDev(D) P(Zk) R 327.9 1.78 44.5 0.96 0.04 order size safety factor safety stock b) customer service level probability of stock out reorder point 85.6 85.6 Annual setup cost Annual holding cost Annual shortage cost Total annual cost $457 $626 $35 $1,117 Annual setup cost Annual holding cost Annual shortage cost Total annual cost C Dollar should be willing to pay $457 $626 $35 $1,117 Qc studioadmin Share B. Chemical Compound Solution - Excel File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do Queries & Connections & Clear . 21 22 Properties T Reapply Get From From From Table/ Recent Existing Refresh 1 Sort Filter Text to Flash Remove Data Consolidate Relationship Manage What If Forecast Group Ungroup Subtotal Data Text/CSV Web Range Sources Connections All Edit Links Advanced Columns Fill Duplicates Validation Data Model Analysis Sheet Get & Transform Data Queries & Connections Sort & Filter Data Tools Forecast Outline Show Detail 2 Solver 3 Hide Detail Analyze G21 J K L M N o P Without variability in lead time Inputs B C D E F G H 1 Chemical compound consignment contract 2 With variability in lead time and demand 3 Inputs 4 5 S $300 Setup cost per order 6 20% Annual cost of capital 7 E(D) 500 Expected annual demand 8 StDev(D) 50 StDev of annual demand 9 E(L) 0.0822 Expected lead time in years 10 StDev(L) 0.0411 StDev of lead time in years 11 E(D) 41.10 Expected demand during lead time 12 StDev(D) 25.05 StDev of demand during lead time 13 p (shortage penalty) $600 Shortage cost incurred each cycle with a shortage 14 C (unit cost) $15 unit purchase cost 15 Unit profit $10 contribution margin 16 h (unit inventory holding cost per ye $3.00 17 Optimal solution using Solver to choose Q and k 18 19 Q 327.9 order size 20 K 1.78 safety factor 21 K'StDev(D) 44.5 safety stock 22 PIZk) 0.04 probability of stock out 24 R 85.6 reorder poin E(D.)+ k*StDev(D) 25 a) when the inventory drops 26 Annual setup cost $457 to about 86 gallons, a 27 Annual holding cost $626 reorder of about 328 gallons 28 Annual shortage cost $35 29 Total annual cost $1,117 30 31 Q S $300 i 20% E(D) 500 StDev(D) 50 E(L) 0.0822 StDev(L) 0.0000 ED) 41.10 StDev(D) 14.33 p (shortage penalty) $600 c (unit cost) $15 Unit profit $10 h (unit inventory holding cost pery $3.00 Optimal solution using Solver to choose Q and k -0 =0 There is variability in "L" no Q k k' StDeVID) PIZk) 3222 2.08 29.8 0.98 0.02 R 70.9 Annual setup cost Annual holding cost Annual shortage cost Total annual cost $466 $573 $18 $1,056 C dollar should willing to pay $62 Chemial compound Ready Scroll Lock . 120% Bi 9 ! Do 3:55 PM 2/3/2021 CHEMICAL COMPOUND CONSIGNMENT CONTRACT An Excel Template is available. We will work on completing it in the instructor videos. You have signed a consignment contract to supply a chemical compound to a major customer. This compound costs you $15/gallon and generates a contribution (margin) of $10/gallon. Delivery costs you $300 in shipping and handling. Demand for this year is estimated at 500 gallons and standard deviation is 50 gallons. The contract says that the compound is inventoried at your client's facility; he will draw the material from the stock as needed and pay you at such time. Annual cost of capital is 20%. The client proposes the following policy to control the stock-outs: Shortage/Stock-out Policy If the client finds that the inventory empty when needed, you are subject to a flat penalty charge of $600 per stock-out occasion. There is also variability in the lead-time; the expected lead-time is 30 days with a standard deviation of 15 days. a. Determine the optimal replenishment policy (specifically, the values of safety factor safety stock, order size, and re-order point). b. Report the implied the customer service level (its business meaning and the value). C. Suppose you could pay C dollars per year to get rid of the variability in its lead-time per order. What is the most you would be willing to pay to do this? Chemical compound consignment contract With variability in lead time and demand Inputs Without variability in lead time Inputs S $300 Setup cost per order i i 20% Annual cost of capital E(D) 500 Expected annual demand SiDev(D) 50 StDev of annual demand E(L) 0.0822 Expected lead time in years StDev(L) 0.04T StDev of lead time in years E(D) 41.10 Expected demand during lead time StDev(D) 25.05 StDey of demand during lead time P (shortage penalty) $600 Shortage cost incurred each cycle with a shortage $15 unit purchase cost Unit profit $10 contribution margin h(unit inventory holding cost per $3.00 Optimal solution using Solver to choose Q and k S $300 i 20% E(D) 500 StDev(D) 50 ELL) 0.0822 SiDev(L) 0.0411 E(D) 41.10 StDev(D) 25.05 p (shortage penalty) $600 $15 Unit profit $10 h(unit inventory holding cost pe $3.00 Optimal solution using Solver to choose Q and k Q k k"StDev(D) P(Zk) R 327.9 1.78 44.5 0.96 0.04 order size safety factor safety stook b) customer service level probability of stock out reorder point E(DL)+ k*StDev(DL) a) when the inventory drops to about 86 gallons, a reorder of about 328 gallons Q k k"StDev(D) P(Zk) R 327.9 1.78 44.5 0.96 0.04 order size safety factor safety stock b) customer service level probability of stock out reorder point 85.6 85.6 Annual setup cost Annual holding cost Annual shortage cost Total annual cost $457 $626 $35 $1,117 Annual setup cost Annual holding cost Annual shortage cost Total annual cost C Dollar should be willing to pay $457 $626 $35 $1,117 Qc studioadmin Share B. Chemical Compound Solution - Excel File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do Queries & Connections & Clear . 21 22 Properties T Reapply Get From From From Table/ Recent Existing Refresh 1 Sort Filter Text to Flash Remove Data Consolidate Relationship Manage What If Forecast Group Ungroup Subtotal Data Text/CSV Web Range Sources Connections All Edit Links Advanced Columns Fill Duplicates Validation Data Model Analysis Sheet Get & Transform Data Queries & Connections Sort & Filter Data Tools Forecast Outline Show Detail 2 Solver 3 Hide Detail Analyze G21 J K L M N o P Without variability in lead time Inputs B C D E F G H 1 Chemical compound consignment contract 2 With variability in lead time and demand 3 Inputs 4 5 S $300 Setup cost per order 6 20% Annual cost of capital 7 E(D) 500 Expected annual demand 8 StDev(D) 50 StDev of annual demand 9 E(L) 0.0822 Expected lead time in years 10 StDev(L) 0.0411 StDev of lead time in years 11 E(D) 41.10 Expected demand during lead time 12 StDev(D) 25.05 StDev of demand during lead time 13 p (shortage penalty) $600 Shortage cost incurred each cycle with a shortage 14 C (unit cost) $15 unit purchase cost 15 Unit profit $10 contribution margin 16 h (unit inventory holding cost per ye $3.00 17 Optimal solution using Solver to choose Q and k 18 19 Q 327.9 order size 20 K 1.78 safety factor 21 K'StDev(D) 44.5 safety stock 22 PIZk) 0.04 probability of stock out 24 R 85.6 reorder poin E(D.)+ k*StDev(D) 25 a) when the inventory drops 26 Annual setup cost $457 to about 86 gallons, a 27 Annual holding cost $626 reorder of about 328 gallons 28 Annual shortage cost $35 29 Total annual cost $1,117 30 31 Q S $300 i 20% E(D) 500 StDev(D) 50 E(L) 0.0822 StDev(L) 0.0000 ED) 41.10 StDev(D) 14.33 p (shortage penalty) $600 c (unit cost) $15 Unit profit $10 h (unit inventory holding cost pery $3.00 Optimal solution using Solver to choose Q and k -0 =0 There is variability in "L" no Q k k' StDeVID) PIZk) 3222 2.08 29.8 0.98 0.02 R 70.9 Annual setup cost Annual holding cost Annual shortage cost Total annual cost $466 $573 $18 $1,056 C dollar should willing to pay $62 Chemial compound Ready Scroll Lock . 120% Bi 9 ! Do 3:55 PM 2/3/2021