can someone please help with questions #1-3 and show formulas used on the spreadsheet please? (orange cells must have formulas in them, not just answers) i would greatly appreciate any assistance that would be provided to me... thank you so much in advance :)

CASE 11.2 Bob's Custom BBQs Bob's Custom BBQs, a manufacturer of outdoor grills, uses three primary carriers to move grills from its Texas factory to home improvement retailers in the United States and Canada. The owner, Bob Flambeau, wants to evaluate the performance of the three carriers and collected data for the following metrics over a three-month period. PERFORMANCE CRITERIA ALLIED TRANSPORT BESTWAY FREIGHT CERTAINT CARRIERS On-time delivery 99.5% 98.7% 98.2% Loads with damage claims 0.9% 1.6% 0.4% Customer satisfaction ratings 4.6 4.2 3.9 Billing accuracy 99.3% 99.6% 98.2% Loads rejected 1.3% 2.1% 0.9% Bob a sked h is transportation manager to develop a s corecard to help compare the carriers on these ve metrics. The manager came up with the following chart: PERFORMANCE CRITERIA PERFORMANCE EVALUATION WEIGHT FACTOR POTENTIAL SCORE ACTUAL SCORE On-time delivery 30 150 >98.5% = 5 96.01-98.5% = 4 93-96% = 2 2% = 0 20 100 Customer satis faction ratings >4.5 = 5 4.01-4.5 = 4 Customer satisfaction ratings 4.6 4.2 3.9 Billing accuracy 99.3% 99.6% 98.2% Loads rejected 1.3% 2.1% 0.9% Bob a sked h is transportation manager to develop a s corecard to help compare the carriers on these ve metrics. The manager came up with the following chart: PERFORMANCE CRITERIA PERFORMANCE EVALUATION WEIGHT FACTOR POTENTIAL SCORE ACTUAL SCORE On-time delivery 30 150 >98.5% = 5 96.01-98.5% = 4 93-96% = 2 2% = 0 20 100 Customer satis- faction ratings >4.5 = 5 4.01-4.5 = 4 3-4 = 2 99% = 5 97.01-99% = 4 95-97% = 2 3% = 0 Total Score 500 11:44:10. Transportation-Managing the Flow of the Supply Chain CASE QUESTIONS 1. Calculate the performance score for each of the three carriers. 2. Which carrier would you recommend that Bob consider for elimination? Why? 3. If Bob decides to keep all three carriers, what should each of them work to improve? 1 2 PERFORMANCE CRITERIA 3 On-time delivery 4 Load with damage claims 5 Customer satisfaction rating 6 Billing Accuracy 7 Load Rejects 8 CARRIERS PERFORMANCE ALLIED TRANSPORT BESTWAY FREIGHT 99.5% 98.7% 0.9% 1.6% 4.6% 4.2% 99.3% 99.6% 1.3% 2.1% CERTAIN CARRIERS 98.2% 0.4% 3.9% 98.2% 0.9% 9 10 PERFORMANCE CRITERIA WEIGHT FACTOR POTENTIAL SCORE On-time delivery 30 150 11 Load with damage claims 30 150 12 PEFORMANCE EVALUATION >98.5%=5, 96.1%-98.5%=4,93%- 96%=2, 2%=0 >4.5%=5, 4.01%-4.5%=4,3%- 4%=2, 99%=5, 97.01%-99%-4, 95%- 97%=2, 95%=0 3%=0 Customer satisfaction rating 20 100 13 Billing Accuracy 10 50 14 Load Rejects 10 50 15 ALLIED TRANSPORT BESTWAY FREIGHT CERTAIN CARRIERS 16 17 01. 18 PERFORMANCE CRITERIA 19 On-time delivery 20 Load with damage claims 21 Customer satisfaction rating 22 Billing Accuracy 23 Load Rejects 24 25 26 Q2. 0 0 0 27 28 03. 29 30