Question 1 Solar Pty Ltd manufactures solar panels. ihe panels are made from photovoltaic cells that convertthe sun's energy into electricity. Photovoltaic cells are placed between layers of semiaconducting materials such as silicon. The silicon is purchased from four suppliers, two from India, one from China and one from Australia. Solar Pty Ltd is currently assessing whether they should sign another five-year contract with oniyone supplier. After Covid, times are tough and profits have declined a little Any cost savings would be beneficial for the company, After holding discussions With senior managers, as part of the review process, the financial controller has conducted a study to determine the full cost of dealing With the suppliers. While the company uses a series of non-financial performance measures to measure most aspects of supplier performance, the nancial controller believes that the calculation ofthe total cost of ownership will provide an additional perspective to viewing supplier pe rformance. For the most recent year, the following supplier-related activities and costs have been identied: ACTIVITV TOTAL COST NUMBER OF ACIIVITIES Order silicon from supplier $567 000 2 500 orders Receive order $240 000 3 000 deliveries Return damaged silicon to supplier $150 000 900 returns Pay overtime to receiving staff for late deliveries $200 000 4 000 hours Production downtime due to defective material 3330 000 800 hours Process invoice and pay supplier $100 000 2 500 invoices Dispute invoice amount $225 000 1 200 disputes In addition to the above supplier related activities, Solar Pty Ltd also employees a supplier relationship manager, Roger Dames. Roger is in charge of developing and maintaining supplier relationships. negotiating contracts and pricing and resolving any issues with suppliers that cannot be resolved by other departments such as purchasing and accounts. Roger ls paid a salary of $150 000 annually. He spends his time equally on the iour suppliers. Last year Solar Pty Ltd purchased silicon from the four suppliers as follows; 8 000 boxes from Sidka limited at a price of $115 per box. 9 700 boxes from Tosca 8t Co at a price of $119 per box. a 500 boxes from Jordan Limited at a price of$125 per box 9 200 boxes from Twinkle Pty Ltd at a price of $117 per box. Last year the following activities related to the four suppliers: ACTIVITV Sidka Tosca & Co Jordan Twinkle Pty Limited limited Ltd order silicon from supplier ,529 . 399 gm, 1190 Receive order 900 450 1000 650 Return damaged silicon to supplier 300 so 47s 75 Pay overtime to receiving staff for late deliveries Production downtime due to defective material Process invoice and pay supplier Dispute invoice amount Using Excel: 1. Determine the cost per unit of activity driver for each supplier-related activity. 2. Calculate the total cost of ownership and the per unit cost of ownership for the four suppliers. 3. Calculate the supplier performance index for the four suppliers. 4. Analyse the performance of all the suppliers. Look into specific details. If Solar Pty Ltd were to choose only one supplier to do business with, which supplier would you recommend? 5. Do you recommend Solar Pty Ltd to deal with only one supplier? Explain your answer. 6. Suggest three ways Solar Pty Ltd can reduce costs associated with supplier related activities and three ways that the suppliers can help to reduce supplier related costs for Solar Pty Ltd. 7. What is the meaning of collaborative relationship with a supplier and how can Solar Pty Ltd benefit from having a collaborative relationship with its suppliers