SHOW FORMULAS IN EXCEL PLEASE!!!!!

QUESTION #1 - Total Cost Analysis Primo Caf Inc. has been doing well. Revenue from its three lines of coffee makers have exceeded expectations. In fact, things have been going so well that lise, the company's innovative CEO and founder, has decided Primo Caf should offer its own brand of coffee beans. Gianna, the chief marketing officer, has already come up with a name - Primo's Premium Coffee - and estimates the company can sell 8,000- 10,000 pounds of coffee per month at $25 per pound. There's only one thing left to do-find a supplier for the coffee beans. Marco, the CoQ and your boss, has given you the task of finding a supplier and you've narrowed the field to two candidates: African Coffee Unlimited LLC., located in Uganda, and South American Espresso Company, located in Colombia. You've come up with the following cost information for both suppliers. African Coffee Unlimited SA Espresso Company Price $0.90/pound $0.95/pound $2,000/container $20/pallet Shipping to port of export Ocean freight $36/pallet $10,000/container Trucking Costs (per container) $2.60/mile $2.60/mile Duties on imports are going to be 5% of the price for both suppliers. African Coffee Unlimited will be shipping to the Port of Long Beach in California, where port handling fees are 8% of the price of a container. Once the beans are offloaded in California, they will be transported via truck 2,190 miles to Primo's Grand Rapids facility. South American Espresso Company will be shipping to the Port of Charleston in South Carolina, where port handling fees are 12% of the price of a container. Once the beans are offloaded in South Carolina, they will be transported via truck 955 miles to Primo's Grand Rapids facility. Primo's warehousing costs in Grand Rapids will be the same for both $50 per pallet. You also know that for a standard product like coffee, there are 250 pounds of coffee per pallet and 275 pallets per container. Questions on the next page! African Coffee Unlimited Pound Pallet Container South American Espresso Company Pound Pallet Container % % Price Ship to port of export Ocean Freight Duties Port Handling Trucking to Grand Rapids Warehousing Costs Total Cost Conversions Price / pound Pounds / Pallet Pounds / Container Shipping to port of export Ocean Freight Duties Port Handling Trucking to Grand Rapids Warehousing Costs Apply the total cost framework presented in class to complete the questions below. Complete all calculations in the excel file you will upload with your exam. 1. Based on this information, provide a complete total cost analysis for each supplier. 2. What is the cost per pound from each supplier? 3. Other than price, what are the major cost drivers of the total cost analysis for each supplier? 4. Marco wants a recommendation on what Primo Caf should do. Given the information above- and what you know about the product you are sourcing - what would you recommend? Why