Marketing Analytics: Total Distribution Cost Instructions: Read the problem below and refer to the spreadsheet provided. The spreadsheet values highlighted in yellow can be manipulated in order to determine possible outcomes and answer the questions. You must answer the follow-up questions (a-e) correctly to receive full credit D E Problem: Proto Company has been producing various items made of plastic. It recently added a line of plain plastic cards that other firms (such as banks and retail stores) will imprint to produce credit cards. Proto offers its customers the plastic cards in different colors, but they all sell for $40 per box of 1,000. Tom Phillips. Proto's product manager for this line is considering two possible physical distribution systems. He estimates that if Proto uses airfreight transportation costs will be $7.50 a box, and its cost of carrying inventory will be 5 percent of total annual sales dollars. Alternatively. Proto could ship by rail for $2 a box. But rail transport will require renting space at four regional warehouses--at $26,000 a year each. Inventory carrying cost with this system will be 10 percent of total annual sales dollars. Phillips prepared a spreadsheet to compare the cost of the two alternative physical distribution systems Spreadsheet The spreadsheet values outlined in yellow can be changed in order to determine possible outcomes. You can find the initial values in the corresponding blue cells in columns E and F. Start by entering the initial values into columns B and C. Then review the questions below and adjust the values in columns B and C to determine the correct answers B F Initial Initial 2 Airfreight Railroad Airfreight Railroad 3 Selling Price 5 40.00 5 40.00 Expected Sales Quantity 20.000 20,000 Inventory Carrying Cost% 5.00% 10.00% Unit Shipping Cost 2.00 Number of Warehouses Cost per Warehouse $ 000 $ 26,000.00 Total Cost - Transport $ 150,000.00 $ 40,000.00 10 Total Cost - Inventory $ 40,000.00 $ 80,000.00 11 Total Cost - Warehouse $ 104,000.00 12 Total Distribution Cost $ 190,000.00 $ 224,000.00 Total Revenue $ 500,000.00 $ 800.000.00 1 4 5 6 $ $ 7.50 7 0 8 9 $ 0.00 13 Questions: a. If Proto Company expects to sell 20,000 boxes a year, what is the difference between total physical distribution costs for each of the systems? O $o-The total is the same for both O $26.000.00 O $34,000.00 $190,000.00 O $224,000.00 b. If Phillips can negotiate cheaper warehouse space for the rail option so that each warehouse costs only $20,000 per year, which physical distribution system has the lowest overall cost? Airfreight is lower cost O Rail transport is lower cost O Both options would have the same overall cost c. Using the warehouse cost of S20,000 per year, what would the sales quantity need to be for the options to have the same overall cost? O 14,355 20,000 22,857 29,714 O 38,646 d. Proto's finance manager predicts that interest rates are likely to be lower during the next marketing plan year and suggests that Tom Phillips use inventory carrying costs of 4 percent for airfreight and 75 percent for railroads (with warehouse cost at $20,000 each and the original sales quantity forecast). If interest rates are in fact lower, which alternative would have the lowest overall cost? O Airfreight is lower cost D Rail transport is lower cost O Both options would have the same overall cost e. Using the original interest rates and warehouse cost, what would Phillips need to negotiate the Railroad shipping cost to in order for the total distribution cost to be the same for both options? O $0.30 O $1.25 $175 O $2.00 O $2.90