how do i find "wages to miles" and how do i find ratios?
Goal: Determine the most appropriate cost driver for predicting fuel and wage costs. Procedure: In B24, enter a function formula that determines the correlation between the fuel cost in B5:B16 to the miles travelled in F5:F16. 1. In B25, enter a function formula that determines the correlation between the fuel cost in B5:B16 to the number of trips in E5:E16. 2. In B26, enter a function formula that determines the correlation between the driver wages C5:C16 to the miles travelled in F5:F16 3. In B27, enter a function formula that determines the correlation between the driver wages in C5:C16 to the number of trips in E5:E16. 4 In D24, enter a formula that determines the fuel cost per mile, using the yearly fuel cost in B17 and the miles travelled during the year in F17. 5. In D25, enter a formula that determines the fuel cost per trip, using the yearly fuel cost in B17 and the number of trips during the year in E17 6. In D26, enter a formula that determines the driver wages per mile, using the yearly driver wages paid C17 and the miles travelled during the year in F17. 7. In D27, enter a formula that determines driver wages per trip, using the yearly driver wages paid in C17 and the number of trips during the year in E17. 8 fx COASTAL TRUCKI NG, INC A B E F COASTAL TRUCKING, INC. 1 Cost Correlation Worksheet 2 Total Trucking Number of Trips Miles Travelled Fuel Cost Month Driver Wages Cost 4 Jan 5 $72,068 $65,731 $64,349 $47,068 $44,824 $46,827 $47,268 $42,198 $51,003 $48,973 $50,507 $87.039 $667,855 $13,542 $14,120 $14,071 $13,802 $14,190 $15,324 $16,781 $16,984 $14,624 $13,594 $15,003 $15.781 $177,816 $85,610 $79,851 $78,420 $60,870 $59,014 $62,151 $64,049 $59,182 $65,627 $62,567 $65,510 $102.820 $845,671 150 21,300 20,100 19,000 14,400 13,400 14,000 Feb 6 145 7 Mar 137 Apr May 148 160 10 Jun 175 11 Jul 181 14,100 12 Aug Sep 174 12,900 15,100 13 148 14 Oct 147 14,800 15,200 15 Nov 151 16 Dec Total 170 24,900 199,200 17 1,886 18 Correlation CORREL(Range 1,Range2) 19 Ratio 20 22 fuel to driver wages -0.173 24 fuel cost to miles 25 fuel cost to trips 26 wages to miles 27 wages to trips 29 30 Sheet1 Sheet2 Sheet3 Ready Cost Drivers Template (1)-Save utoSave OF View Data Review Home Page Layout Formulas Insert X Cut A A Number Wrap Text Arial 11 Copy Merge & Center B Paste I U Format Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. x f817/F17 24 E G H B F C A COASTAL TRUCKING, INC. Cost Correlation Worksheet 2 Total Trucking Number of Trips Miles Month Driver Wages Fuel Cost Cost Travelled 4 Jan $72,068 $65,731 $64,349 $47,068 $44,824 $13,542 $14,120 $14,071 $13,802 $85,610 $79,851 $78,420 $60,870 $59,014 $62,151 $64,049 $59,182 $65,627 $62,567 21,300 150 6 Feb 145 20,100 7 Mar 137 19,000 8 Apr May Jun 148 14,400 $14,190 $15,324 $16,781 $16,984 $14,624 160 13,400 10 $46,827 175 14,000 11 Jul $47,268 181 14,100 12 Aug Sep Oct $42,198 $51,003 $48,973 $50,507 $87.039 $667,855 174 12,900 13 148 15,100 14,800 15,200 24.900 199,200 14 $13,594 $15,003 $15,781 $177,816 147 15 Nov $65,510 $102.820 $845,671 151 16 Dec 170 1,886 17 Total Correlation CORREL(Range1,Range2) 19 Ratio 20 22 fuel to driver wages -0.173 24 fuel cost to miles 25 fuel cost to trips 26 wages to miles 27 wages to trips 3.353 354.112 29 30 Sheett Sheet2 Sheet3 Ready
