Calculate the Optimal Supplier solutions for the pre-pandemic contract scenario.

BCOR 3750 Course Project #2 Fresh Choice Snack Foods is a trail-mix supplier who sources products from all over the world. Their primary snack mix uses ingredients nuts from California, dried fruit from Hawaii, and chocolate pieces both Ohio and Pennsylvania. Recently, supplier restrictions and plant closures in the United States began limiting the company's ability to supply its product into the market. Furthermore, since the start of the pandemic, the company is now experiencing price increases as its suppliers have concurrently observed a dramatic increase in demand for Fresh Choice's inputs (thus causing significant increases in price). That said, the company has long standing contracts that still need to be honored and fulfilled. The volumes are set; however, the mix of inputs has some flexibility (which could allow some flex based on variations in prices). Fresh Choice Snack Foods hired you as a consultant to help identify the potential "optimal solutions" to fulfill their current orders while also helping with global sourcing decisions for the inputs that can be purchased from suppliers in other countries. The company has an outstanding contract for 10,000,000 Lbs of its trail mix but the changing pricing landscape is destroying the company's margins. There are four scenarios Fresh Choice Snack Foods is requesting you to analyze. 1. Pre-Pandemic Contract Scenario (US ONLY) 2. Post-Pandemic Contract Scenario (US ONLY) 3. US & International Suppliers (Initial) 4. US & International Suppliers (Final) The Pre-Pandemic Sources and Prices are as follows: Location Product California Almonds California Raisins Hawaii Coconut Shavings Michigan Dried Tart Sweet Cherries Ohio Chocolate Bits Pennsylvania Chocolate Bits Price ($/Lb) $ 2.50 $0.99 $ 2.00 $ 4.00 $ 0.75 $ 0.85 Freight ($/Lb) $ 0.10 $ 0.12 $ 0.50 $ 0.35 $ 0.12 $ 0.10 Mix % (Low) 10% 10% 10% 10% 10% Mix % (High) 25% 25% 25% 25% 25% 25% 10% In the initial weeks of the pandemic, US prices changed to the following: Location Product California Almonds California Raisins Hawaii Coconut Shavings Michigan Dried Tart Sweet Cherries Ohio Chocolate Bits Pennsylvania Chocolate Bits Price ($/Lb) $ 2.25 $ 1.25 $ 2.50 $ 3.50 $ 1.10 $ 1.10 Freight ($/Lb) $ 0.15 $ 0.13 $ 0.60 $ 0.40 $ 0.15 $ 0.16 Mix % (Low) 10% 10% 10% 10% 10% 10% Mix % (High) 25% 25% 25% 25% 25% 25% Pre-Pandemic Contract Scenario (US ONLY) Current Mix Cost $ % Mix Michigan California California Hawaii Ohio Pennsylvania Outstanding Contract Amount = Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits Total 0.00% 0.00% 0.00% 0.00% 0.00% Low 10% 10% 10% 10% 10% High 25% 25% 25% 25% 25% Total Cost = $ Total 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 100% 100% Hawaii California 0 Michigan 0 Ohio 0 Counted 1 Min Count X 0 Pennsylvania 0 0 0 California 1 0 0 0 1 0 0 0 Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits 1 1 0 1 0 0 0 X 0 0 0 1 0 0 1 0 0 0 0 1 1 2 Entry Counted 1 1 1 1 1 1 Min Count Needed X X X California Input Costs $/Lb Hawaii Michigan California $ 2.50 Ohio Pennsylvania $ 0.99 Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits $ 2.00 $ 4.00 $ 0.75 $ 0.85 Freight Costs $/Lb Hawaii Michigan California California $ 0.10 Ohio Pennsylvania $ 0.12 Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits $ 0.50 $ 0.35 0.12 $ 0.10 BCOR 3750 Course Project #2 Fresh Choice Snack Foods is a trail-mix supplier who sources products from all over the world. Their primary snack mix uses ingredients nuts from California, dried fruit from Hawaii, and chocolate pieces both Ohio and Pennsylvania. Recently, supplier restrictions and plant closures in the United States began limiting the company's ability to supply its product into the market. Furthermore, since the start of the pandemic, the company is now experiencing price increases as its suppliers have concurrently observed a dramatic increase in demand for Fresh Choice's inputs (thus causing significant increases in price). That said, the company has long standing contracts that still need to be honored and fulfilled. The volumes are set; however, the mix of inputs has some flexibility (which could allow some flex based on variations in prices). Fresh Choice Snack Foods hired you as a consultant to help identify the potential "optimal solutions" to fulfill their current orders while also helping with global sourcing decisions for the inputs that can be purchased from suppliers in other countries. The company has an outstanding contract for 10,000,000 Lbs of its trail mix but the changing pricing landscape is destroying the company's margins. There are four scenarios Fresh Choice Snack Foods is requesting you to analyze. 1. Pre-Pandemic Contract Scenario (US ONLY) 2. Post-Pandemic Contract Scenario (US ONLY) 3. US & International Suppliers (Initial) 4. US & International Suppliers (Final) The Pre-Pandemic Sources and Prices are as follows: Location Product California Almonds California Raisins Hawaii Coconut Shavings Michigan Dried Tart Sweet Cherries Ohio Chocolate Bits Pennsylvania Chocolate Bits Price ($/Lb) $ 2.50 $0.99 $ 2.00 $ 4.00 $ 0.75 $ 0.85 Freight ($/Lb) $ 0.10 $ 0.12 $ 0.50 $ 0.35 $ 0.12 $ 0.10 Mix % (Low) 10% 10% 10% 10% 10% Mix % (High) 25% 25% 25% 25% 25% 25% 10% In the initial weeks of the pandemic, US prices changed to the following: Location Product California Almonds California Raisins Hawaii Coconut Shavings Michigan Dried Tart Sweet Cherries Ohio Chocolate Bits Pennsylvania Chocolate Bits Price ($/Lb) $ 2.25 $ 1.25 $ 2.50 $ 3.50 $ 1.10 $ 1.10 Freight ($/Lb) $ 0.15 $ 0.13 $ 0.60 $ 0.40 $ 0.15 $ 0.16 Mix % (Low) 10% 10% 10% 10% 10% 10% Mix % (High) 25% 25% 25% 25% 25% 25% Pre-Pandemic Contract Scenario (US ONLY) Current Mix Cost $ % Mix Michigan California California Hawaii Ohio Pennsylvania Outstanding Contract Amount = Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits Total 0.00% 0.00% 0.00% 0.00% 0.00% Low 10% 10% 10% 10% 10% High 25% 25% 25% 25% 25% Total Cost = $ Total 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 100% 100% Hawaii California 0 Michigan 0 Ohio 0 Counted 1 Min Count X 0 Pennsylvania 0 0 0 California 1 0 0 0 1 0 0 0 Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits 1 1 0 1 0 0 0 X 0 0 0 1 0 0 1 0 0 0 0 1 1 2 Entry Counted 1 1 1 1 1 1 Min Count Needed X X X California Input Costs $/Lb Hawaii Michigan California $ 2.50 Ohio Pennsylvania $ 0.99 Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits $ 2.00 $ 4.00 $ 0.75 $ 0.85 Freight Costs $/Lb Hawaii Michigan California California $ 0.10 Ohio Pennsylvania $ 0.12 Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits $ 0.50 $ 0.35 0.12 $ 0.10