Goal: Determine the most appropriate cost driver for predicting fuel and wage costs. Procedure: In B24,...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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. 2. 3. 4. 5. 6. 7. 8. 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. In B26, enter a function formula that determines the correlation between the driver wages C5:C16 to the miles travelled in F5:F16. 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. 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. 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. 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. 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. Home Paste 1 456TB92222222 27 3 7 8 10 11 12 13 14 Merge & Center Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. 24 Xfx 817/F17 B 15 16 17 18. 19 20 D Insert X Cut Copy Format 29 30 A Month Jan Ready Feb Mar COASTAL TRUCKING, INC. Cost Correlation Worksheet Apr May Page Layout Jun Jul Aug Sep Oct Nov Arial B IU. Dec Total 22 fuel to driver wages 24 fuel cost to miles 25 fuel cost to trips 26 wages to miles 27 wages to trips Formulas Data A- A Sheett Fuel Cost Sheet2 $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 -0.173 3.353 354.112 O C Shout Review Correlation CORREL(Range 1, Range2) Driver Wages $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 View D E Total Trucking Number of Cost Trips $85,610 150 $79,851 145 $78,420 $60,870 $59,014 160 $62,151 175 $64,049 $59,182 $102.820 $845,671 Ratio 137 148 181 174 $65,627 148 $62,567 147 $65,510 151 170 1,886 Cost Drivers Template (1)-Save Wrap Text F Miles Travelled 21,300 20,100 19,000 14,400 13,400 14,000 14,100 12,900 15.100 14,800 15,200 24.900 199,200 Number $% G > H 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. 2. 3. 4. 5. 6. 7. 8. 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. In B26, enter a function formula that determines the correlation between the driver wages C5:C16 to the miles travelled in F5:F16. 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. 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. 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. 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. 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. Home Paste 1 456TB92222222 27 3 7 8 10 11 12 13 14 Merge & Center Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. 24 Xfx 817/F17 B 15 16 17 18. 19 20 D Insert X Cut Copy Format 29 30 A Month Jan Ready Feb Mar COASTAL TRUCKING, INC. Cost Correlation Worksheet Apr May Page Layout Jun Jul Aug Sep Oct Nov Arial B IU. Dec Total 22 fuel to driver wages 24 fuel cost to miles 25 fuel cost to trips 26 wages to miles 27 wages to trips Formulas Data A- A Sheett Fuel Cost Sheet2 $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 -0.173 3.353 354.112 O C Shout Review Correlation CORREL(Range 1, Range2) Driver Wages $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 View D E Total Trucking Number of Cost Trips $85,610 150 $79,851 145 $78,420 $60,870 $59,014 160 $62,151 175 $64,049 $59,182 $102.820 $845,671 Ratio 137 148 181 174 $65,627 148 $62,567 147 $65,510 151 170 1,886 Cost Drivers Template (1)-Save Wrap Text F Miles Travelled 21,300 20,100 19,000 14,400 13,400 14,000 14,100 12,900 15.100 14,800 15,200 24.900 199,200 Number $% G > H
Expert Answer:
Answer rating: 100% (QA)
Correlation Correlation coefficient denotes the strength of the relationship between two variables I... View the full answer
Related Book For
Applied Regression Analysis and Other Multivariable Methods
ISBN: 978-1285051086
5th edition
Authors: David G. Kleinbaum, Lawrence L. Kupper, Azhar Nizam, Eli S. Rosenberg
Posted Date:
Students also viewed these accounting questions
-
2 3 4 5 3. Item Value 15 30 40 20 25 Weight 2 4 6 8 10 Considering the above table contains the items along with their profit and weight. Now, your task is to calculate the maximum profit for...
-
Using the accompanying table, determine the most appropriate forecasting technique for the accompanying data about the Dow Jones Industrial Average yearly closing value and implement the model. Year...
-
A 1 defects size B D E 88888888888888888888888888 2 21 3 24 4 16 5 12 6 15 7 5 8 28 9 20 10 31 11 25 12 20 13 24 14 16 15 19 16 10 17 17 18 13 19 22 20 18 21 39 22 30 23 24 24 16 25 19 26 17 27 15...
-
John Wilson is a conservative investor who has asked your advice about two bonds he is considering. One is a seasoned issue of the Capri Fashion Company that was first sold 22 years ago at a face...
-
In the model of the open economy just developed, two markets determine two relative prices. What are the markets? What are the two relative prices?
-
Differentiate. f() = sin ()/1 + cos ()
-
Consider a European Call option with strike \(K\) and time to expiration \(T\). Denote the price of the call for \(C(S, T)\) and let \(B(T)\) the price of one unit of a zero coupon bond maturing at...
-
On January 1, 2010, Furball Company had Accounts Receivable $98,000 and Allowance for Doubtful Accounts $8,100. Furball Company prepares financial statements annually. During the year the following...
-
I. TITLE: TEMPERATURE MEASUREMENT II. OBJECTIVES This simple experiment on temperature measurement is aimed to compare and explain the measured temperature data from the standard temperatures of...
-
1. Who owns the dominant easement estate? 2. Who owns the servient easement estate? 3. Explain the precedent on easement barriers and the courts requirements for proof of unreasonable interference...
-
Q.2 The following was the Balance sheet of A, B and C sharing profits and losses in the proportion of 5:3:2 Amount(RO) Liabilities Creditors Bills Payable General Reserves Capital Accounts A 40,000 B...
-
Explain the main difference between depreciation expense and accumulated depreciation. How are they related?
-
Create a box plot for the data given in Problems 19-22. \(18,27,34,52,54,59,61,68,78,82,85,87,91\),
-
Which accounting principle requires expenses to be matched against the revenues which they help to generate? a. Historical cost principle b. Accounting entity principle c. Expense recognition...
-
A professor gives five exams. Two students' scores have the same mean, although one student did better on all the tests except one. Give an example of such scores.
-
How was the osher (Ottoman tax on agriculture products) determined? To which accounting principle does this relate?
-
Calculate the depreciation expense for 2018 In 2015, Lyra Co. acquired land containing ore for a total cost of P60,000,000. Immovable tangible equipment costs totaled P5,000,000. Geologists estimate...
-
The data listed in the following table are from a study by Benignus and others (1981). Blood and brain levels of toluene (a commonly used solvent) were measured in rats following a 3-hour inhalation...
-
A random sample of data was collected on residential sales in a large city. The accompanying table shows the selling price (Y, in $ 1,000s), area (X1, in hundreds of square feet), number of bedrooms...
-
A manufacturer conducted a pricing experiment to explore the effects of price decreases on sales of one of its breakfast cereals. The two largest supermarket chains in a particular area participated...
-
What would the QA/QC planning grid (see Figure 12. 6) look like for a project you have been involved in? Data from Figure 12. 6 Table 12.6 QA/QC planning grid, with examples Quality assurance...
-
Why is the definition of quality critical to the success and acceptance of a project?
-
What is the purpose of the Quality Management Plan?
Study smarter with the SolutionInn App