Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Data File needed for this Case Problem: NP EX 1-2.xlsx Cross State Trucking Brian Eagleton is a dispatch manager at Cross State Trucking, a
Data File needed for this Case Problem: NP EX 1-2.xlsx Cross State Trucking Brian Eagleton is a dispatch manager at Cross State Trucking, a major freight hauler based in Chicago, Ilinois. Brian needs to develop a workbook that will summarize the driving log of Cross State drivers. Brian has a month of travel data from one of the company's drivers and wants you to create a workbook that he can use to analyze this data. Complete the following: 1. Open the NP_EX 1-2.xlsx workbook located in Week 4 Section of Canvas. Save the workbook as NP_EX_1_Trucking on Canvas when ready to submit. 2. Change the name of the Sheet1 worksheet using Travel Log as the name and then move it to the end of the workbook. Rename the Sheet2 worksheet using Documentation as the name. 3. In the Documentation sheet, enter your name in cell B3 and the current date in cell B4. Move the Documentation sheet to the front of the workbook. 4. Go to the Travel Log worksheet. Resize the columns so that all the data is visible in the cells. 5. Between the Odometer Ending column and the Hours column, insert a new column. Enter Miles as the column label in cell 14. 6. In the Miles column, in the range 15:125, enter formulas to calculate the number of miles driven each day by subtracting the odometer beginning value from the odometer ending value. 7. Between the Price per Gallon column and the Seller column, insert a new column, Enter Total Fuel Purchase as the label in cell N4. 8. In the Total Fuel Purchase column, in the range N5:N25, enter formulas to calculate the total amount spent on tuel. each day by multiplying the gallons value. by the price per gallon value. 9. In cell B6, use the SUM function to calculate the total number of driving hours using the values in the Hours column in the range J5:J25. 10. In cell B7, enter a formula that divides the hours of driving value (cell B6) by the days of driving value (cell B5) to calculate the average hours of driving per day. 11. In cell B9, use the SUM function to calculate the driver's total expenses for the month for fuel, tolls, and miscellaneous expenditures using the nonadjacent range N5:N25, P5:Q25. 12. in cell B10, use the SUM function to calculate the total amount the driver spent on fuel using the range N5:N25. In cell B11, use the SUM function to calculate the total amount spent on tolls and miscellaneous expenses using the range P5:Q25. 13. In cell B13, use the SUM function to calculate the total gallons of gas the driver used during the month using the range L5:L25. In cell B14, use the SUM function to calculate the total number of miles driven that month using the range 15:125. 14. In cell B15, enter a formula that divides the total miles (cell B14) by the total gallons (cell B13) to calculate the miles per gallon, In cell B16, enter a formula that divides the total expenses (cell B9) by the total miles driven (cell B14) to calculate the cost per mile.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
To complete this Excel task follow these steps Step 1 Prepare the Workbook 1 Open Workbook Open NPEX...Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started