Marketing Analytics: Vendor Analysis 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. Problem: CompuTech, Inc., makes circuit boards for personal computers. It is evaluating two possible suppliers of electronic memory chips. The chips do the same job. Although manufacturing quality has been improving, some chips are always defective. Both suppliers will replace defective chips. But the only practical way to test for a defective chip is to assemble a circult board and burn it in"-run it and see if it works. When one chip on a board is defective at that point, it costs $2.00 for the extra labor time to replace it. Supplier 1 guarantees a chip failure rate of not more than 1 per 100 (that is, a defect rate of 1 percent). Supplier 2 has a 2 percent defect rate which is higher, but its price is lower Supplier 1 has been able to improve its quality because it uses a heavier plastic case to hold the chip. The only disadvantage of the heavler case is that it requires Computech to use a connector that is somewhat more expensive. Transportation costs are added to the price quoted by either supplier, but Supplier 2 is further away so transportation costs are higher And because of the distance, delays in supplies reaching Computech are sometimes a problem. To ensure that a sufficient supply is on hand to keep production going, Computech must maintain a backup inventory--and this increases inventory costs. Computech figures inventory costs-the expenses of finance and storage-as a percentage of the total order cost. To make its vendor analysis easier, Computech's purchasing agent has entered data about the two suppliers on a spreadsheet He based his estimates on the quantity he thinks he will need over a full year. 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 c B D Initial Initi 1 4 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 D E F Initial Initial 2 Supplier 1 Supplier 2 Supplier 1 Supplier 2 3 Quantity of Chips Needed 100,000 100,000 Cost per Chip $ 1.91 $ 1.87 5 Cost for Total Order of Chips $ 0.00 $ 0.00 $ 191,000.00 $ 187,000.00 6 Defective Rate (% Detective) 1.00% 2.00% 7 Number of Detective Chips 0 1,000 2,000 B Cost to Replace Defective Chip $ 2.00 5 2.00 Total Cost of Replacing Chiper $ 0.00 $ 0.00 2,000.00 $ 4,000.00 10 Connector Cost for Each Chip $ 0.10 $ 0.08 11 Total Cost-Connectors $ 0.00 $ 0.00 $ 10,000.00 $ 8,000.00 12 Inventory Cost as % of Total Order 200% 5.40% 13 Total Inventory Cout $ 0.00 $ 0.00 $ 3,820.00 $ 10,098.00 14 Transportation Cost per Chip $ 0.02 $ 0.03 15 Total Transportation Cost $ 0.00 $ 0.00 $ 2,000.00 $ 3,000.00 16 Total Costs for Vendor $ 0.00 $ 0.00 $ 208.820.00 $ 212,098.00 0 9 Questions: a. Based on the results shown in the initial spreadsheet, which supplier do you think Computech should select? O Supplier 1 is the better choice Supplier 2 is the better choice The cost is the same for either supplier b. Computech estimates it will need 100.000 chips a year if sales go as expected. But if sales are slow, fewer chips will be needed. This isn't an issue with Supplier 2: Its price is the same at any quantity. However, Supplier T's price per chip will be $1.95 if CompuTech buys less than 90,000 during the year. If Computech only needs 84,500 chips, which supplier would be more economical? Supplier 1 is the better choice O Supplier 2 is the better choice The cost is the same for either supplier c. If the actual purchase quantity will be 84,500 and Supplier 1's price is $1.95, what is the highest price at which Supplier 2 will still be the lower-cost vendor for Computech? $1.84 $1.87 O $1.90 $1.95 $2.25 d. CompuTech shared thelr concern about the higher failure rate with Supplier 2. The supplier said they believe they can bring the fallure rate down to 0.5% but it will increase the cost per chip to $1.94. The connector cost would remain $0.08 each. At a quantity of 84,500, which supplier would be most cost effective? O Supplier 1 is the better choice O Supplier 2 is the better choice The cost is the same for either supplier e. Continuing the scenario d above, which cost represents the most significant difference between Supplier 1 and Supplier 2 prices for this scenario? Total cost of chips Total cost of replacing chips Total cost of connectors Total inventory cost Total transportation cost