Case Problem 1 Data File needed for this Case Problem: NP EX 1-2.xlsx W APPLY you to create a workbook that he can use to analyze this data. Complete the following: Cross State Trucking Brian Engleton is a dispatch manager at Cross State Trucking a major treibt hauler based in Chicago, Illinois Brian needs to develop a workbook that will summarize the one log of Cross State drivers Brian has a month of travel data from one the company's drivers and 1. Open the NP EX 1-2.xlsx workbook located in the Excell Case1 folder included with your files. Save the worldbook as NP_EX_1_Trucking in the location specified by your instructor 2. Change the name of the Sheetl worksheet using Travel Log as the name, and then move it to the range NS:N25. In cell B11, use the SUM function to calculate the total amount spent on tolls 14. In cell B13, use the SUM function to calculate the total gallons of gas the driver used during the month using the range 15:125. In cell B14, use the SUM function to calculate the total number 15. 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 16. Cross Suate Trucling wants to keep the cost of driving and delivering goods to less than 65 cents per mile. If the cost per mile weater than 65 cents, enter over budget in cell B4, otherwise 17. In cell Al, Increase the font size of the text to 20 points. In cell D3 and cell L3, increase the font size of the Mileage Table and Expense Table labels to 18 points. 18. Change the worksheet to landscape orientation. Scale the printout so that the worksheet is scaled to 1 page wide by 1 page tall. If you are instructed to print, print the entire workbook the end of the workbook. Rename the Sheet2 worksheet using Documentation as the name to the Documentation sheet, enter your name in cell B3 and the current date in cell B4. Mowa 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 fuel each day by multiplying the gallons value by the price per gallon value. 9. In cell B5, use the COUNT function to calculate the total number of driving days using the val- ues in the Date column in the range 05:025. 10. 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 15:25. 11. In cell B7, enter a formula that divides the hours of driving value (cell B6) by the days of driving value fcell B5) to calculate the average hours of driving per day. 12. 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. 13. In cell B10, use the SUM function to calculate the total amount the driver spent on fuel using and miscellaneous expenses using the range P5:Q25. miles driven that month using the range 15:125. 89) by the total miles driven (cell B14) to calculate the cost per mile. enter on budget in cell B4. 19. Save the workbook, and then close it. - A A 29 Cumency - $ -% 98 WE 29 O A. Canal amat Hatting Table Sole Style fonal Sart in Fier Olta Number f D E F 1 Cross State Travel Log 2 3 4 Summary 5 Days of Driving 6 Hours of Driving 7 Average Hours per Day 9 Total Expenses 10 Total Fuel Expenses 11 Other Expenses 12 13 Total Gallons Consumed 14 Total Miles Driven 15 Miles per Gallon 16 Total Cost per Mile 17 Mileage Table Date Starting Location Delivery Location 8/2/2021 Shipping Plant, Chicago IL Ace Foods, Omaha NE 8/3/2021 Warehouse, Lincoln NE Millers Foods, Tulsa OK 8/4/2021 Dispatch Center, Tulsa OK Ace Foods, Shrevport A 8/5/2021 Dispatch Center, Shrevport, LA Arch Foods, St. Louis MO 8/6/2021 Shipping Plant, St. Louis MO Food Warehouse, Chicago Il 8/7/2021 Shipping Plant, Chicago Il Shipping Plant, Chicago IL 8/9/2021 Shipping Plant, Chicago IL River Front Foods, La Crosse W 8/10/2021 Distribution Center, Winona MN Dakota Brothers, Chamberlain 5D 8/11/2021 Central Warehouse, Chamberlain SD Mountain Foods, Greely co 8/12/2021 Warehouse, Greely Co Ace Foods, Omaha NE 8/23/2021 Warehouse, Lincoln NE Shipping Plant, Chicago 8/16/2021 Shipping Plant, Chicago il Taylor Foods, Dayton OH 8/17/2021 Distribution Center, Dayton OH Ohio Foods, Cleveland OH 8/18/2021 Shipping Plant Cleveland OH Shipping Plant, Chicago IL 8/19/2021 Shipping Plant, Chicago IL Shipping Plant, Chicago Il 8/23/2021 Shipping Plant, Chicago IL Overland Foods, Overland Park 8/24/2021 Warehouse, Kansas City, KS OK Foods, Oklahoma City, OK Odometer Beginning Odometer 28249 28801 29198 29695 30312 30655 30889 31305 31742 32231 12800 33.278 33590 33842 14192 34449 34978 19 20 21 Travel Lagoon Type biere to search c MIL Prison em A 8 2 3 5 6 8 9 Formatting table Style Smith Syer Gall 99 H B 5 Days of Driving 6 Hours of Driving 7 Average Hours per Day 28249 28801 29199 29695 30312 30655 30889 9 Total Expenses 10 Total Fuel Expenses 11 Other Expenses 12 13 Total Gallons Consumed 14 Total Miles Driven 15 Miles per Gallon 16 Total Cost per Mile 17 18 19 D E 8/2/2021 Shipping Plant, Chicago IL Ace Foods, Omaha NE 8/3/2021 Warehouse, Lincoln NE Millers Foods, Tulsa OK 8/4/2021 Dispatch Center, Tulsa OK Ace Foods, Shrevport LA 8/5/2021 Dispatch Center, Shrevport, LA Arch Foods, St. Louis MO 8/6/2021 Shipping Plant, St. Louis MO Food Warehouse, Chicago IL 8/7/2021 Shipping Plant, Chicago il Shipping Plant, Chicago IL 8/9/2021 Shipping Plant, Chicago il River Front Foods, La Crosse W 8/10/2021 Distribution Center, Winona MN Dakota Brothers, Chamberlain SD 8/11/2021 Central Warehouse, Chamberlain SD Mountain Foods, Greely Co 8/12/2021 Warehouse, Greely Co Ace Foods, Omaha NE 8/13/2021 Warehouse, Lincoln NE Shipping Plant, Chicago IL 8/16/2021 Shipping Plant, Chicapo il Taylor Foods, Dayton OH 8/17/2021 Distribution Center, Dayton OH Ohio Foods, Cleveland OH 8/18/2021 Shipping Plant, Cleveland OH Shipping Plant, Chicago il 8/19/2021 Shipping Plant, Chicago IL Shipping Plant, Chicago it 8/23/2021 Shipping Plant, Chicago IL Overland Foods, Overland Park XS 8/24/2021 Warehouse, Kansas City, KS OK Foods, Oklahoma City, OR 8/25/2071 Central Warehouse, Oklahoma City, CAce Foods, Little Rock AR 8/26/2021 Shipping Plant, Little Rock AR Arch Foods, St. Louis MO 8/27/2021 Shipping Plant, St. Louis MO Food Warehouse, Chicago IL 8/20/2021 Shipping Plant. Chicago il Shiopine Plant, Chicago il 31305 31742 32231 32803 33278 33598 33849 34192 3349 3437 3532 21 22 16051 16405 Travelog Documentation Type here to search 96 A & 5 2 8 6 9 1 R U Torting Com Nam Syer 39 G H M 1 2 N Tolls 25 0 0 0 25 4 Odometer Beginning Odometer Ending Hours 5 28249 28725 9.5 G 28801 29186 7.75 7 29198 29661 9.25 29695 30296 12 9 30312 30655 6.75 10 30655 30878 4.25 11 30889 31251 7.25 12 31305 31738 8.25 13 31742 32223 9 14 32231 32753 9.75 15 32803 33269 9.25 16 33278 33577 5.5 17 33598 33835 4.75 18 33849 34186 6.75 19 34197 34426 4.25 20 34969 10.25 21 34978 35322 6.75 Expense Table Gallons Price per G Seller 87.23 2.61 HiTank Gas, Des Moines IA 72.61 2.83 Starland Oasis, Tusla OK 91.48 2.42 Tex Mart, Dallas TX 113.49 2.81 Gas Market, Little Rock AK 21.45 2.78 Steves Mart, Peoria IL 37.28 2.95 Archway Gas, Rockford IL 63.51 2.65 Prairie Market, Sun Prairie W 84.93 2.71 Altono Mart, Chamberlain SD 78.85 2.44 Pioneer Plaza, North Platte NE 100.38 2.44 Pioneer Plaza, North Platte NE 84.72 2.69 HITank Gas, Des Moines IA 63.63 2.85 Plaza Oasis, Indianapolis, IN 50.42 2.79 Median Mart, Columbus, OH 70.55 2.83 South Bend Gas, South Bend IN 37.85 2.95 Archway Gas, Rocidford IL 101.97 2.83 Steves Mart, Peoria il 59.31 2.41 Plains Plaza, Wichita 10 20 0 0 0 15 32 29 Misc 44.15 35.28 78.21 52.23 49.28 28.84 51.53 61.45 7201 68.49 61 58 48.28 43.78 3815 32.14 81 10 15 Travelog Type here to search O Dll Dytsen Home 2 9% 5 OS > 3 & 7 8 9 1 tom NO Nu Cou 9 G H K 28249 28.801 87.23 9.5 7:75 9.25 72.61 7 8 28725 29186 29661 30296 30655 30878 10 11 12 13 14 15 16 12 12 6.75 4.25 7.25 8.25 9 9.75 9.25 29198 29695 30312 30655 30889 31305 31742 32231 32803 33278 33598 33849 34197 34449 34978 35328 35668 36051 36405 Travel Los Documentation 31251 31738 32223 32753 33269 33577 33835 34186 34426 34969 35322 35659 36046 36399 36630 L M 2,61 HiTank Gas, Des Moines IA 2.83 Starland Oasis, Tula OK 2.42 TexMart, Dallas TX 2.81 Gas Market, Little Rock AX 2.78 Steves Mart, Peoria IL 2.95 Archway Gas, Rockford IL 2.65 Prairie Market, Sun Prairie W 2.71 Altono Mart, Chamberlain SD 2.44 Pioneer Plaza, North Platte NE 2:44 Pioneer Plaza, North Platte NE 2.69 HITank Gas, Des Moines IA 2.85 Plaza Oasis, Indianapolis, IN 2.79 Median Mart, Columbus, OH 2.83 South Bend Gas, South Bend IN 2.95 Archway Gas, Rockford IL 2.83 Steves Mart, Peoria il 2.41 Plains Plaza, Wichita KS 2.49 Fort Smith Plara Fort Smith AR 2.53 Arch Oasis, St. Louis MO 2.74 Steve Mart, Peoria IL 2.78. Archway Gas, Rockford 91.48 113.49 71.46 37:28 63.51 84.93 78.85 100.38 8472 63.53 50.42 70.95 37.85 101.97 59,31 57.07 67:51 66.92 40.91 5.5 N o 25 44.15 0 35.28 78.21 0 5223 25 49.28 10 2884 20 51.53 0 61.45 0 7241 68.49 15 61 58 32 48.28 29 13.75 21 38.15 10 32.14 15 781 O 5239 0 425 0 33 57 25 49.02 10 $1.15 19 20 21 4.75 6.75 4.25 10:25 6.75 65 75 7 24 25 Type here to search C DES A % 5 2 3 6 8 9 0 W E R T Y U o Case Problem 1 Data File needed for this Case Problem: NP EX 1-2.xlsx W APPLY you to create a workbook that he can use to analyze this data. Complete the following: Cross State Trucking Brian Engleton is a dispatch manager at Cross State Trucking a major treibt hauler based in Chicago, Illinois Brian needs to develop a workbook that will summarize the one log of Cross State drivers Brian has a month of travel data from one the company's drivers and 1. Open the NP EX 1-2.xlsx workbook located in the Excell Case1 folder included with your files. Save the worldbook as NP_EX_1_Trucking in the location specified by your instructor 2. Change the name of the Sheetl worksheet using Travel Log as the name, and then move it to the range NS:N25. In cell B11, use the SUM function to calculate the total amount spent on tolls 14. In cell B13, use the SUM function to calculate the total gallons of gas the driver used during the month using the range 15:125. In cell B14, use the SUM function to calculate the total number 15. 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 16. Cross Suate Trucling wants to keep the cost of driving and delivering goods to less than 65 cents per mile. If the cost per mile weater than 65 cents, enter over budget in cell B4, otherwise 17. In cell Al, Increase the font size of the text to 20 points. In cell D3 and cell L3, increase the font size of the Mileage Table and Expense Table labels to 18 points. 18. Change the worksheet to landscape orientation. Scale the printout so that the worksheet is scaled to 1 page wide by 1 page tall. If you are instructed to print, print the entire workbook the end of the workbook. Rename the Sheet2 worksheet using Documentation as the name to the Documentation sheet, enter your name in cell B3 and the current date in cell B4. Mowa 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 fuel each day by multiplying the gallons value by the price per gallon value. 9. In cell B5, use the COUNT function to calculate the total number of driving days using the val- ues in the Date column in the range 05:025. 10. 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 15:25. 11. In cell B7, enter a formula that divides the hours of driving value (cell B6) by the days of driving value fcell B5) to calculate the average hours of driving per day. 12. 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. 13. In cell B10, use the SUM function to calculate the total amount the driver spent on fuel using and miscellaneous expenses using the range P5:Q25. miles driven that month using the range 15:125. 89) by the total miles driven (cell B14) to calculate the cost per mile. enter on budget in cell B4. 19. Save the workbook, and then close it. - A A 29 Cumency - $ -% 98 WE 29 O A. Canal amat Hatting Table Sole Style fonal Sart in Fier Olta Number f D E F 1 Cross State Travel Log 2 3 4 Summary 5 Days of Driving 6 Hours of Driving 7 Average Hours per Day 9 Total Expenses 10 Total Fuel Expenses 11 Other Expenses 12 13 Total Gallons Consumed 14 Total Miles Driven 15 Miles per Gallon 16 Total Cost per Mile 17 Mileage Table Date Starting Location Delivery Location 8/2/2021 Shipping Plant, Chicago IL Ace Foods, Omaha NE 8/3/2021 Warehouse, Lincoln NE Millers Foods, Tulsa OK 8/4/2021 Dispatch Center, Tulsa OK Ace Foods, Shrevport A 8/5/2021 Dispatch Center, Shrevport, LA Arch Foods, St. Louis MO 8/6/2021 Shipping Plant, St. Louis MO Food Warehouse, Chicago Il 8/7/2021 Shipping Plant, Chicago Il Shipping Plant, Chicago IL 8/9/2021 Shipping Plant, Chicago IL River Front Foods, La Crosse W 8/10/2021 Distribution Center, Winona MN Dakota Brothers, Chamberlain 5D 8/11/2021 Central Warehouse, Chamberlain SD Mountain Foods, Greely co 8/12/2021 Warehouse, Greely Co Ace Foods, Omaha NE 8/23/2021 Warehouse, Lincoln NE Shipping Plant, Chicago 8/16/2021 Shipping Plant, Chicago il Taylor Foods, Dayton OH 8/17/2021 Distribution Center, Dayton OH Ohio Foods, Cleveland OH 8/18/2021 Shipping Plant Cleveland OH Shipping Plant, Chicago IL 8/19/2021 Shipping Plant, Chicago IL Shipping Plant, Chicago Il 8/23/2021 Shipping Plant, Chicago IL Overland Foods, Overland Park 8/24/2021 Warehouse, Kansas City, KS OK Foods, Oklahoma City, OK Odometer Beginning Odometer 28249 28801 29198 29695 30312 30655 30889 31305 31742 32231 12800 33.278 33590 33842 14192 34449 34978 19 20 21 Travel Lagoon Type biere to search c MIL Prison em A 8 2 3 5 6 8 9 Formatting table Style Smith Syer Gall 99 H B 5 Days of Driving 6 Hours of Driving 7 Average Hours per Day 28249 28801 29199 29695 30312 30655 30889 9 Total Expenses 10 Total Fuel Expenses 11 Other Expenses 12 13 Total Gallons Consumed 14 Total Miles Driven 15 Miles per Gallon 16 Total Cost per Mile 17 18 19 D E 8/2/2021 Shipping Plant, Chicago IL Ace Foods, Omaha NE 8/3/2021 Warehouse, Lincoln NE Millers Foods, Tulsa OK 8/4/2021 Dispatch Center, Tulsa OK Ace Foods, Shrevport LA 8/5/2021 Dispatch Center, Shrevport, LA Arch Foods, St. Louis MO 8/6/2021 Shipping Plant, St. Louis MO Food Warehouse, Chicago IL 8/7/2021 Shipping Plant, Chicago il Shipping Plant, Chicago IL 8/9/2021 Shipping Plant, Chicago il River Front Foods, La Crosse W 8/10/2021 Distribution Center, Winona MN Dakota Brothers, Chamberlain SD 8/11/2021 Central Warehouse, Chamberlain SD Mountain Foods, Greely Co 8/12/2021 Warehouse, Greely Co Ace Foods, Omaha NE 8/13/2021 Warehouse, Lincoln NE Shipping Plant, Chicago IL 8/16/2021 Shipping Plant, Chicapo il Taylor Foods, Dayton OH 8/17/2021 Distribution Center, Dayton OH Ohio Foods, Cleveland OH 8/18/2021 Shipping Plant, Cleveland OH Shipping Plant, Chicago il 8/19/2021 Shipping Plant, Chicago IL Shipping Plant, Chicago it 8/23/2021 Shipping Plant, Chicago IL Overland Foods, Overland Park XS 8/24/2021 Warehouse, Kansas City, KS OK Foods, Oklahoma City, OR 8/25/2071 Central Warehouse, Oklahoma City, CAce Foods, Little Rock AR 8/26/2021 Shipping Plant, Little Rock AR Arch Foods, St. Louis MO 8/27/2021 Shipping Plant, St. Louis MO Food Warehouse, Chicago IL 8/20/2021 Shipping Plant. Chicago il Shiopine Plant, Chicago il 31305 31742 32231 32803 33278 33598 33849 34192 3349 3437 3532 21 22 16051 16405 Travelog Documentation Type here to search 96 A & 5 2 8 6 9 1 R U Torting Com Nam Syer 39 G H M 1 2 N Tolls 25 0 0 0 25 4 Odometer Beginning Odometer Ending Hours 5 28249 28725 9.5 G 28801 29186 7.75 7 29198 29661 9.25 29695 30296 12 9 30312 30655 6.75 10 30655 30878 4.25 11 30889 31251 7.25 12 31305 31738 8.25 13 31742 32223 9 14 32231 32753 9.75 15 32803 33269 9.25 16 33278 33577 5.5 17 33598 33835 4.75 18 33849 34186 6.75 19 34197 34426 4.25 20 34969 10.25 21 34978 35322 6.75 Expense Table Gallons Price per G Seller 87.23 2.61 HiTank Gas, Des Moines IA 72.61 2.83 Starland Oasis, Tusla OK 91.48 2.42 Tex Mart, Dallas TX 113.49 2.81 Gas Market, Little Rock AK 21.45 2.78 Steves Mart, Peoria IL 37.28 2.95 Archway Gas, Rockford IL 63.51 2.65 Prairie Market, Sun Prairie W 84.93 2.71 Altono Mart, Chamberlain SD 78.85 2.44 Pioneer Plaza, North Platte NE 100.38 2.44 Pioneer Plaza, North Platte NE 84.72 2.69 HITank Gas, Des Moines IA 63.63 2.85 Plaza Oasis, Indianapolis, IN 50.42 2.79 Median Mart, Columbus, OH 70.55 2.83 South Bend Gas, South Bend IN 37.85 2.95 Archway Gas, Rocidford IL 101.97 2.83 Steves Mart, Peoria il 59.31 2.41 Plains Plaza, Wichita 10 20 0 0 0 15 32 29 Misc 44.15 35.28 78.21 52.23 49.28 28.84 51.53 61.45 7201 68.49 61 58 48.28 43.78 3815 32.14 81 10 15 Travelog Type here to search O Dll Dytsen Home 2 9% 5 OS > 3 & 7 8 9 1 tom NO Nu Cou 9 G H K 28249 28.801 87.23 9.5 7:75 9.25 72.61 7 8 28725 29186 29661 30296 30655 30878 10 11 12 13 14 15 16 12 12 6.75 4.25 7.25 8.25 9 9.75 9.25 29198 29695 30312 30655 30889 31305 31742 32231 32803 33278 33598 33849 34197 34449 34978 35328 35668 36051 36405 Travel Los Documentation 31251 31738 32223 32753 33269 33577 33835 34186 34426 34969 35322 35659 36046 36399 36630 L M 2,61 HiTank Gas, Des Moines IA 2.83 Starland Oasis, Tula OK 2.42 TexMart, Dallas TX 2.81 Gas Market, Little Rock AX 2.78 Steves Mart, Peoria IL 2.95 Archway Gas, Rockford IL 2.65 Prairie Market, Sun Prairie W 2.71 Altono Mart, Chamberlain SD 2.44 Pioneer Plaza, North Platte NE 2:44 Pioneer Plaza, North Platte NE 2.69 HITank Gas, Des Moines IA 2.85 Plaza Oasis, Indianapolis, IN 2.79 Median Mart, Columbus, OH 2.83 South Bend Gas, South Bend IN 2.95 Archway Gas, Rockford IL 2.83 Steves Mart, Peoria il 2.41 Plains Plaza, Wichita KS 2.49 Fort Smith Plara Fort Smith AR 2.53 Arch Oasis, St. Louis MO 2.74 Steve Mart, Peoria IL 2.78. Archway Gas, Rockford 91.48 113.49 71.46 37:28 63.51 84.93 78.85 100.38 8472 63.53 50.42 70.95 37.85 101.97 59,31 57.07 67:51 66.92 40.91 5.5 N o 25 44.15 0 35.28 78.21 0 5223 25 49.28 10 2884 20 51.53 0 61.45 0 7241 68.49 15 61 58 32 48.28 29 13.75 21 38.15 10 32.14 15 781 O 5239 0 425 0 33 57 25 49.02 10 $1.15 19 20 21 4.75 6.75 4.25 10:25 6.75 65 75 7 24 25 Type here to search C DES A % 5 2 3 6 8 9 0 W E R T Y U o