James DeWalp is a senior buyer of fruit products for Fresh Foods, a major U.S. multinational food processing company. This company, based in California, uses a wide variety of fruit concentrates, purees, flavors, and extracts in many of its popular food products. One of James's responsibilities is to negotiate annual purchase contracts for these ingredients. One such ingredient, guava puree, is grown and harvested on a seasonal basis in various countries around the world.

James is currently examining the costs associated with using one of his existing suppliers, a Philippine grower/processor. Fresh Foods has used this supplier's high-quality product for a number of years. Farmers grow the product in a remote part of the Philippines and transport it to the processing plant where it is pureed and packaged for transoceanic shipment. This particular variety of guava is highly prized for its flavor, which the aseptic method of processing used by the supplier helps maintain. Unfortunately, guerilla activity by rebels has recently caused some problems for growers in this part of the Philippines.

The supplier aseptically packages the guava puree (currently priced at $0.29/pound, FOB vessel) in foil bags, each containing 50 pounds of product, which workers then place into corrugated boxes. The boxes are stacked on wooden pallets, 40 to a pallet, for loading into overseas containers. Each container holds 20 pallets and arrives via ocean freighter. The ocean freight charge is $2,500 per container. Once the containers reach the U.S. port, a trucking company moves each container to a local warehouse for storage at a charge of $250 per container. U.S. Customs calculates import duties to be 15 percent of the shipment's original purchase price excluding freight charges. Fresh Foods requires one container load per month.

Fresh Foods warehouses each container in a public warehouse until needed for processing (average storage is one month). The monthly storage charge is $6.50 per pallet. In addition, the warehouse charges a one-time in/out fee of $6.25 per pallet to cover administrative costs. Fresh Foods inventory carrying charge is 24 percent, which it applies against the unit price of material in storage at the warehouse (but not in-transit from the Philippines). The reason why the company does not apply the carrying charges to intransit inventory is that Fresh Foods typically does not have to pay the invoice for the guava puree until it reaches the local U.S warehouse. Material planners assume the demand for guava puree to be relatively constant over the year.

When a container of guava puree is required at the plant, a local freight company moves the container from the warehouse, which costs $175 per container. The company estimates that incoming receiving and quality-control procedures cost $4 per pallet. Because of the nature of the product and the distance involved in purchasing and storing the guava puree, the company estimates it incurs a loss of 3 percent of the total puree purchased.

Product engineers calculate the budgeted factory yield of the guava puree when blending into company products is 98 percent; this means the company wastes 2 percent of the product by volume during production, and this is not recoverable.

Occasionally, undetected spoilage of guava puree will require removing the product from grocery shelves. Out-of-pocket costs typically total $25,000 for each incident; these costs are not recoverable from the supplier. The company's records indicate that such an incident occurs about once every six months.

In addition to the other costs noted here, corporate accounting policy requires that cost estimators include a 17 percent assessment on purchased product unit cost to cover general and administrative overhead costs at Fresh Foods.

Based on the information above and the provided excel template, how do I fill out the Total Cost analysis excel spreadsheet? I'm having trouble computing the values and am not sure how to calculate them.

B C D E F. G $ 0.29 2,000 20 $ 2,500 15% 1 2 Conversions 3 4 Price / lb 5 lb / pallet 6 Pallets / Container 7 Shipping Costs 8 Ocean Freight 9 Duties (% value of container) 10 Transportation Costs 11 Trans Port to Warehouse / Container 12 Trans from Whse to Public / Pallet 13 Warehouse Charges / Pallet (pick & pack) 14 Inventory Carrying cost (% Purchase price 15 Warehouse to Plant / Container 16 Quality Control / pallet 17 Loss in Transit & Storage 18 Factory Yield Loss 19 Spoilage at customer (grocer) / 6 containers 20 21 $ 250 $ 6.50 $ 6.25 24% $ 175 $ 4.00 3% 2% $ 25,000 Costs Per Unit Pound Pallet Container Cost Drivers Price $ 0.2900 $ 580.00 $ 11,600.00 100% Shipping Costs To U.S. 0% Duties 0% Trans Costs To Warehouse 0% To Public 0% Warehouse Charges 0% Inventory Carrying Cost 0% Whse to Plant 0% Quality Control 0% Sub-Total $ 0.2900 $ 580.00 $ 11,600.00 Loss in Transit & Storage 0% Factory Yield Loss 0% Spoilage @ Customer 0% Total $0.2900 $ 580.00 $11,600.00 SG&A 0% Total with SG&A $0.2900 $ 580.00 $11,600.00 100% B C D E F. G $ 0.29 2,000 20 $ 2,500 15% 1 2 Conversions 3 4 Price / lb 5 lb / pallet 6 Pallets / Container 7 Shipping Costs 8 Ocean Freight 9 Duties (% value of container) 10 Transportation Costs 11 Trans Port to Warehouse / Container 12 Trans from Whse to Public / Pallet 13 Warehouse Charges / Pallet (pick & pack) 14 Inventory Carrying cost (% Purchase price 15 Warehouse to Plant / Container 16 Quality Control / pallet 17 Loss in Transit & Storage 18 Factory Yield Loss 19 Spoilage at customer (grocer) / 6 containers 20 21 $ 250 $ 6.50 $ 6.25 24% $ 175 $ 4.00 3% 2% $ 25,000 Costs Per Unit Pound Pallet Container Cost Drivers Price $ 0.2900 $ 580.00 $ 11,600.00 100% Shipping Costs To U.S. 0% Duties 0% Trans Costs To Warehouse 0% To Public 0% Warehouse Charges 0% Inventory Carrying Cost 0% Whse to Plant 0% Quality Control 0% Sub-Total $ 0.2900 $ 580.00 $ 11,600.00 Loss in Transit & Storage 0% Factory Yield Loss 0% Spoilage @ Customer 0% Total $0.2900 $ 580.00 $11,600.00 SG&A 0% Total with SG&A $0.2900 $ 580.00 $11,600.00 100%