please show formulas in excel. Really need help on this!! thank you in advance!
Fb A B E D Loss in production Spoilage SG&A 18 Total Cost $ S $ S $ S 15 16 17 Ship, Store, & Make Costs Price / Pound 19 Frieghter Cost / Container 20 Import Duties (% of price) 21 Trans to warehouse / Container 22 Storage/Month / Pallet 23 In/Out Fee / Pallet 24 Quality Control / Pallet 25 Inventory Carrying Charge (% of unit price) 26 Trans from warehouse / Container 27 Loss in shipment (%) 28 Loss in production (%) 29 SGA (% of price) 30 31 Quality Costs 32 Spoilage Cost Every Six Months 33 Pounds of Guava Every Six Months 34 Spoilage Cost Per Pound 35 36 37 Student Start 0.29 2,500.00 15% 250.00 6.50 6.25 4.00 24% 175.00 3% 2% 17% $ $ 25,000.00 #DIV/0! FO D E c F G H 1 2 3 Per Month Costs for Guava Bag/Box Pallet Container % of Total Pound 0.2900 4 5 S 2,500.00 0.0435 $ 250,00 5 $ $ 6.50 6.25 4.00 $ Price $ Freighter cost Import Duties $ Trans to warehouse Storage In/Out Fee Quality Control Trans from warehouse Inventory Carrying Charge Sub Total s Loss in shipment Loss in production Spoilage SGBA s Total Cost 7 8 9 10 11 12 13 14 15 16 17 175.00 0.3335 S S 16.75 S 2,925.00 0.0493 18 19 20 21 22 23 Student Start Per Month Costs for Gua Ba/BOX Pallet Pound 0.2300 price 50 40 0.0435 20 6.50 3 3 $ 4.00 1 Fresh Foods Case 2 3 Unit Conversions 4 Pounds / Bag Pounds/Box 5 Boxes/Pallet 5 Pounds / Pallet 7 Pallets / Container Boxes / Container 9 Pounds / Container 10 11 Inventory Information 12 Containers of Inventory / Month 13 Container Storage Time / Month 14 15 16 17 Ship Store, Make Costs 18 Price / Pound 19 Frighter Cost/Container 20 Import Duties (N. of price 21 Trans to warehouse / Container Storage/Month / Pallet 2) In/Out Fee / Pallet Student Start 5 Freighter cost Import duties $ Trans to warehouse Storage In/Out Fee Quality Control Trans from warehouse Inventory Carrying Charte Sub Totals Loss in shipment Loss in production Soolage SG&A 5 Total Cost 1 + 0.33355 $ 16.75 0.0493 $ $ 0.29 2,500.00 15% 250.00 650 525 $ 5 S FRESH FOODS CASE CACULATIONS IN EXCEL 1. Calculate the total cost of guava puree for Fresh Foods per pound, per pallet, and per container. You do not have to calculate a column for cost of guava per bag/box. Make sure you include the cost of spoilage ($25,000 every six months), as well as loss in storage and production Give the percent of the total cost that each cost category represents. 2. Fresh Foods current pays its suppliers 14.5 PHP at an exchange rate of SO PHP to 1 USD. Due to a severe economic downturn in the Philippines, however, the peso is expected to substantially weaken against the dollar. The consensus forecast is that over the next two years, the average exchange rate will be 60 PHP to 1 USD. Copy your original TCO model onto a separate sheet in your excel workbook Expand your model to incorporate an exchange rate. Calculate the total cost with the forecast exchange rate of 60 PHP to 1 USD. What is the difference in total cost between your original model and the new model that uses the 60 PHP to 1 USD. Provide your answer both in terms of change in dollar amount and percentage change. . Fb A B E D Loss in production Spoilage SG&A 18 Total Cost $ S $ S $ S 15 16 17 Ship, Store, & Make Costs Price / Pound 19 Frieghter Cost / Container 20 Import Duties (% of price) 21 Trans to warehouse / Container 22 Storage/Month / Pallet 23 In/Out Fee / Pallet 24 Quality Control / Pallet 25 Inventory Carrying Charge (% of unit price) 26 Trans from warehouse / Container 27 Loss in shipment (%) 28 Loss in production (%) 29 SGA (% of price) 30 31 Quality Costs 32 Spoilage Cost Every Six Months 33 Pounds of Guava Every Six Months 34 Spoilage Cost Per Pound 35 36 37 Student Start 0.29 2,500.00 15% 250.00 6.50 6.25 4.00 24% 175.00 3% 2% 17% $ $ 25,000.00 #DIV/0! FO D E c F G H 1 2 3 Per Month Costs for Guava Bag/Box Pallet Container % of Total Pound 0.2900 4 5 S 2,500.00 0.0435 $ 250,00 5 $ $ 6.50 6.25 4.00 $ Price $ Freighter cost Import Duties $ Trans to warehouse Storage In/Out Fee Quality Control Trans from warehouse Inventory Carrying Charge Sub Total s Loss in shipment Loss in production Spoilage SGBA s Total Cost 7 8 9 10 11 12 13 14 15 16 17 175.00 0.3335 S S 16.75 S 2,925.00 0.0493 18 19 20 21 22 23 Student Start Per Month Costs for Gua Ba/BOX Pallet Pound 0.2300 price 50 40 0.0435 20 6.50 3 3 $ 4.00 1 Fresh Foods Case 2 3 Unit Conversions 4 Pounds / Bag Pounds/Box 5 Boxes/Pallet 5 Pounds / Pallet 7 Pallets / Container Boxes / Container 9 Pounds / Container 10 11 Inventory Information 12 Containers of Inventory / Month 13 Container Storage Time / Month 14 15 16 17 Ship Store, Make Costs 18 Price / Pound 19 Frighter Cost/Container 20 Import Duties (N. of price 21 Trans to warehouse / Container Storage/Month / Pallet 2) In/Out Fee / Pallet Student Start 5 Freighter cost Import duties $ Trans to warehouse Storage In/Out Fee Quality Control Trans from warehouse Inventory Carrying Charte Sub Totals Loss in shipment Loss in production Soolage SG&A 5 Total Cost 1 + 0.33355 $ 16.75 0.0493 $ $ 0.29 2,500.00 15% 250.00 650 525 $ 5 S FRESH FOODS CASE CACULATIONS IN EXCEL 1. Calculate the total cost of guava puree for Fresh Foods per pound, per pallet, and per container. You do not have to calculate a column for cost of guava per bag/box. Make sure you include the cost of spoilage ($25,000 every six months), as well as loss in storage and production Give the percent of the total cost that each cost category represents. 2. Fresh Foods current pays its suppliers 14.5 PHP at an exchange rate of SO PHP to 1 USD. Due to a severe economic downturn in the Philippines, however, the peso is expected to substantially weaken against the dollar. The consensus forecast is that over the next two years, the average exchange rate will be 60 PHP to 1 USD. Copy your original TCO model onto a separate sheet in your excel workbook Expand your model to incorporate an exchange rate. Calculate the total cost with the forecast exchange rate of 60 PHP to 1 USD. What is the difference in total cost between your original model and the new model that uses the 60 PHP to 1 USD. Provide your answer both in terms of change in dollar amount and percentage change
