Data Analytics Question

User Generated

jn7fu844

Business Finance

Production operation analysis

CSU

Description

Please check what I will attach.

you will find instructions pdf file........................................

Requirements: it varies each question is different most of them are short.

Please complete Project 2 on excel file. ( check all 4 sheets).

Requirements: Depending on requirements.

Unformatted Attachment Preview

Pre-Pandemic Contract Scenario (US ONLY) Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits California - Total Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits California - 0.00% % Mix Hawaii Michigan - 0.00% 0.00% Ohio - 0.00% 0.00% California 1 0 0 0 0 California 0 1 0 0 0 Hawaii 0 0 1 0 0 Michigan 0 0 0 1 0 Ohio 0 0 0 0 1 Entry Counted 1 1 1 1 1 Min Count Needed X X X X X California California Input Costs $/Lb Hawaii Michigan Ohio California Freight Costs $/Lb Hawaii Michigan Ohio Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits California Current Mix Cost Pennsylvania - Total 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% Pennsylvania 0 0 0 0 1 1 X Pennsylvania Pennsylvania Low X X X X X High X X X X X X X Counted 1 1 1 1 2 Min Count X X X X X Outstanding Contract Amount = Total Cost = $ - $ - Post-Pandemic Contract Scenario (US ONLY) Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits California - Total Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits California - 0.00% % Mix Hawaii Michigan - 0.00% 0.00% Ohio - 0.00% 0.00% California 1 0 0 0 0 California 0 1 0 0 0 Hawaii 0 0 1 0 0 Michigan 0 0 0 1 0 Ohio 0 0 0 0 1 Entry Counted 1 1 1 1 1 Min Count Needed 1 1 1 1 1 California California Input Costs $/Lb Hawaii Michigan Ohio California Freight Costs $/Lb Hawaii Michigan Ohio Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits California Current Mix Cost Pennsylvania - Total 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% Pennsylvania 0 0 0 0 1 1 1 Pennsylvania Pennsylvania Low X X X X X High X X X X X X X Counted 1 1 1 1 2 Min Count 1 1 1 1 1 Outstanding Contract Amount = Total Cost = $ - $ - US & International Suppliers (Initial) Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits California - Total Max Input California - 0.00% Hawaii - 0.00% Michigan - 0.00% Ohio - 0.00% 0.00% X X X X X California 1 0 0 0 0 California 0 1 0 0 0 Hawaii 0 0 1 0 0 Michigan 0 0 0 1 0 Ohio 0 0 0 0 1 Entry Counted 1 1 1 1 1 Min Count Needed 1 1 1 1 1 California California Hawaii Michigan Ohio Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits Input Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits Freight Co Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits California California Hawaii Michigan Ohio Exc Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits California (USD) California (USD) Hawaii (USD) Michigan (USD) Ohio (USD) Pennsylvania - % Mix Australia - 0.00% Spain - 0.00% Thailand - 0.00% Vietnam - 0.00% Turkey - 0.00% Brazil - 0.00% 0.00% X X X X X X X Pennsylvania 0 0 0 0 1 Australia 1 0 0 0 0 Spain 1 0 0 0 0 Thailand 0 0 1 0 0 Vietnam 0 0 1 0 0 Turkey 0 1 0 1 0 Brazil 0 0 0 0 1 1 1 1 1 1 2 1 1 1 1 1 1 1 1 Spain Thailand Vietnam Turkey Brazil Freight Costs (Currency/Lb) Pennsylvania Australia Spain Thailand Vietnam Turkey Brazil Thailand (Baht) Vietnam (Dong) Turkey (Lira) Brazil (Real) Input (Currency/Lb) Pennsylvania Australia Exchange Rate Pennsylvania (USD) Australia (AUS Dllr) Spain (Euro) Current Mix Cost France 0.00% Total 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% Low X X X X X High X X X X X X X X France 0 0 0 0 1 1 1 France France France (Euro) Counted Min Count 3 1 2 1 3 1 2 1 4 1 Outstanding Contract Amount = Total Cost = $ - $ - US & International Suppliers (Final) Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits California - Total Max Input California - 0.00% Hawaii - 0.00% Michigan - 0.00% Ohio - 0.00% 0.00% X X X X X California 1 0 0 0 0 California 0 1 0 0 0 Hawaii 0 0 1 0 0 Michigan 0 0 0 1 0 Ohio 0 0 0 0 1 Entry Counted 1 1 1 1 1 Min Count Needed 1 1 1 1 1 California California Hawaii Michigan Ohio Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits Input Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits Freight Co Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits California California Hawaii Michigan Ohio Exc Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits California (USD) California (USD) Hawaii (USD) Michigan (USD) Ohio (USD) Pennsylvania - % Mix Australia - 0.00% Spain - 0.00% Thailand - 0.00% Vietnam - 0.00% Turkey - 0.00% Brazil - 0.00% 0.00% X X X X X X X Pennsylvania 0 0 0 0 1 Australia 1 0 0 0 0 Spain 1 0 0 0 0 Thailand 0 0 1 0 0 Vietnam 0 0 1 0 0 Turkey 0 1 0 1 0 Brazil 0 0 0 0 1 1 1 1 1 1 2 1 1 1 1 1 1 1 1 Spain Thailand Vietnam Turkey Brazil Freight Costs (Currency/Lb) Pennsylvania Australia Spain Thailand Vietnam Turkey Brazil Thailand (Baht) Vietnam (Dong) Turkey (Lira) Brazil (Real) Input (Currency/Lb) Pennsylvania Australia Exchange Rate Pennsylvania (USD) Australia (AUS Dllr) Spain (Euro) Current Mix Cost France 0.00% Total 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% Low X X X X X High X X X X X X X X France 0 0 0 0 1 1 1 France France France (Euro) Counted Min Count 3 1 2 1 3 1 2 1 4 1 Outstanding Contract Amount = Total Cost = $ - $ - 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. 2. 3. 4. Pre-Pandemic Contract Scenario (US ONLY) Post-Pandemic Contract Scenario (US ONLY) US & International Suppliers (Initial) US & International Suppliers (Final) The Pre-Pandemic Sources and Prices are as follows: Location California California Hawaii Michigan Ohio Pennsylvania Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits 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% 10% Mix % (High) 25% 25% 25% 25% 25% 25% Mix % (Low) 10% 10% 10% 10% 10% 10% Mix % (High) 25% 25% 25% 25% 25% 25% In the initial weeks of the pandemic, US prices changed to the following: Location California California Hawaii Michigan Ohio Pennsylvania Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits 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 When researching prices with international suppliers, Fresh Choice was able to negotiate a seven day lock on the following prices; however, since the company is seeking to diversify its supplier base. As such, NO COMPANY CAN ACCOUNT FOR MORE THAN 15% OF THE COMMODITY BEING SUPPLIED: US Prices (Same as “Post-Pandemic Contract Scenario”) Location California California Hawaii Michigan Ohio Pennsylvania Product Almonds Raisins Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits 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% Currency AUS Dollar Euro Lira $1 (USD) Equals “X” Currency 1.37 0.85 8.42 International Prices (Quoted in the home country’s currency) Location Australia Spain Turkey Product Almonds Almonds Raisins Thailand Vietnam Turkey Brazil France Coconut Shavings Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits Chocolate Bits Price (Curr/Lb) 3.069 1.955 10.272 Freight (Curr/Lb) 0.233 1.955 1.263 75.387 57,012.960 27.365 5.807 0.927 15.385 12,746.800 2.947 0.608 0.145 Baht Dong Lira Real Euro 30.77 23,176 8.42 5.53 0.85 In the time the international research was conducted to the time the meeting was held, the sales prices remained constant; however, the exchange rates changed to the following: International Prices (Quoted in the home country’s currency—Rates @ morning of meeting date) Location Australia Spain Turkey Product Almonds Almonds Raisins Thailand Vietnam Turkey Brazil France Coconut Shavings Coconut Shavings Dried Tart Sweet Cherries Chocolate Bits Chocolate Bits Price (Curr/Lb) 3.069 1.955 10.272 Freight (Curr/Lb) 0.233 1.955 1.263 75.387 57,012.960 27.365 5.807 0.927 15.385 12,746.800 2.947 0.608 0.145 Currency AUS Dollar Euro Lira Baht Dong Lira Real Euro $1 (USD) Equals “X” Currency 1.40 0.90 8.37 29.00 24,000 8.37 5.40 0.90 Project Deliverables: Calculate the Optimal Supplier solutions for the following: 1. 2. 3. 4. Pre-Pandemic Contract Scenario (US ONLY) Post-Pandemic Contract Scenario (US ONLY) US & International Suppliers (Initial) US & International Suppliers (Final) 20 Points 20 Points 20 Points 20 Points Provide a 1-Page Memorandum to the company management comparing the costs of each scenario (10 Points) as well as identifying potential benefits and pitfalls of changing the company’s strategy (10 Points). --------------------------------------------------------------------------------------------------------------------Assignment Deadline: ON-TIME: LATE: LATE: LATE: LATE: LATE: Friday, April 9th 11:59 PM (On-Time) = 100.00 Pts (Max) Saturday, April 10th by 11:59 PM = 90.00 Pts (Max) Sunday, April 11th by 5:59 AM = 65.00 Pts (Max) Sunday, April 11th by 11:59 AM = 40.00 Pts (Max) Sunday, April 11th by 5:59 PM = 15.00 (Max) Sunday, April 11th by 6:00 PM = NO POINTS
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached. Please let me know if you have any questions or need revisions.

MEMORANDUM
TO: The Chief Executive Officer, Fresh Choice Snack Foods\
FROM: Audrey Kamara
DATE: 9th April 2021
SUBJECT: ANALYSIS OF THE TRADING OPTIONS.
The aim of this memorandum is to address the steps taken as the consultant in analyzing the four
scenarios that were taken by the above mentioned company. The first option was before the
outbreak of the pandemic and involved trade only inside the country. I calculated the mixed costs
from the percentage mix and the mix cost for the scenario is 7.528 dollars compared to their
corresponding total costs of 103136546 dollars. The second option was also trading within the
country after the pandemic outbreak. The current and total costs reduced for the first weeks
compared to before the pandemic attributed many customers that liked the fresh foods during the
period.
The third option involve a research for expanding trade within and outside the country after the
pandemic. The mix cost is 39155 and the total cost is 536434597. This is seen to have increased
due to the research costs as well as the exchange rates and the new business environments
outside the States. The final option shows trading for the research initially done in scenario three.
The costs remain high but are lower than the research since the exchange rates increased in favor
of the United States dollar.
The company diversification of trade to other countries apart from the US, brought advantages
and disadvantages. Opening up trade increased the number of consumers and therefore the

profits of the company from the high sales of products. The quality of products will now improve
much further due to the close monitoring from the respective countries. The challenges of
growing the market outside are the risks of losing profits from the ever inconsistent rate...


Anonymous
Excellent resource! Really helped me get the gist of things.

Studypool
4.7
Indeed
4.5
Sitejabber
4.4

Similar Content

Related Tags