Cross State Trucking Brian Eagleton is a dispatch manager at Cross State Trucking, a major freight...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Cross State Trucking Brian Eagleton is a dispatch manager at Cross State Trucking, a major freight hauler based in Chicago, Illinois. Brian needs to develop a workbook that will summarize the drivin log of Cross State drivers. Brian has a month of travel data from one the company's drivers and want 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 the Excel > Casel folder included with your dat files. Save the workbook as NP_EX 1_Trucking in the location specified by your instructor. 2. Change the name of the Sheett 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 NS:N25, enter formulas to calculate the total amount spent on fuel cach 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 D5:D25. 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:125. 11. In cell B7, enter a formula that divides the hours of driving value icell B6) by the days of driving value icell 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 NS:N25,PS:Q25. 13. In cell B10, use the SUM function to calculate the total amount the driver spent on fuel using the range NS:N25. In cell B11, use the SUM function to calculate the total amount spent on tolls and miscellaneous expenses using the range P5:Q25. 14. In cell B13, use the SUM function to calculate the total gallons of gas the driver used during the month using the range L5:125. In cell B14, use the SUM function to calculate the total number of miles driven that month using the range 15:125. 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 89) by the total miles driven icell B14) to calculate the cost per mile. 16. Cross State Trucking wants to keep the cost of driving and delivering goods to less than 65 cents per mile, the cost per mile is greater than 65 cents, enter over budget in cell B4, otherwise enter on budget in cell B4. 17. In cell Al, increase the font size of the test to 28 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 I page tall. you are instructed to print, print the entire workbook 19. Save the workbook, and then close it EX 1-6: Formatting Table Styles mat Filter Select Clipbord Aigment Font Number Styles Cefls Editing G21 SUMIG15:G19) 1 MediT Conference Miscellaneous Expenses 3 Summary 4 Expense Categories 5 Total Expenses 4 $9,064.97 7 Host 8 Address 9 City Harbor Convention Center 1082 Suncrest Avenue Boston 10 State Massachussets 11 Postal Code 02128 12. Phone (617)555-1082 13 Tax Rate 5% 14 Account ID Expense Category Subcategory Description 9010 Printing of brochures and conference materials 9020 Decorations for banquet 9025 Flowers for the banguet tables Units Cost per Unit Total 15 E2-9010 12 1600 $2.45 $3.920.00 $850.55 16 E2-9020 E2 S850.55 17 E2-9025 E2 20 $250.00 $6.25 $3,281.25 $55.25 S12.50 18 E2-9040 E2 9040 Gift bags for conference attendees S25 19 E2-9045 E2 9045 Gifts for banquet speakers $331.50 20 21 Subtotal S8.613.30 22 23 Est.tax 5431.67 TOTAL S9.064.97 24 25 Documentation Miscellaneous Expenses Reaty P Type here to search 1/12/2021 Prt Scn Home End Pgup m F4 16 %23 & 2 3 4. 5 7 8 9. Cross State Trucking Brian Eagleton is a dispatch manager at Cross State Trucking, a major freight hauler based in Chicago, Illinois. Brian needs to develop a workbook that will summarize the drivin log of Cross State drivers. Brian has a month of travel data from one the company's drivers and want 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 the Excel > Casel folder included with your dat files. Save the workbook as NP_EX 1_Trucking in the location specified by your instructor. 2. Change the name of the Sheett 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 NS:N25, enter formulas to calculate the total amount spent on fuel cach 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 D5:D25. 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:125. 11. In cell B7, enter a formula that divides the hours of driving value icell B6) by the days of driving value icell 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 NS:N25,PS:Q25. 13. In cell B10, use the SUM function to calculate the total amount the driver spent on fuel using the range NS:N25. In cell B11, use the SUM function to calculate the total amount spent on tolls and miscellaneous expenses using the range P5:Q25. 14. In cell B13, use the SUM function to calculate the total gallons of gas the driver used during the month using the range L5:125. In cell B14, use the SUM function to calculate the total number of miles driven that month using the range 15:125. 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 89) by the total miles driven icell B14) to calculate the cost per mile. 16. Cross State Trucking wants to keep the cost of driving and delivering goods to less than 65 cents per mile, the cost per mile is greater than 65 cents, enter over budget in cell B4, otherwise enter on budget in cell B4. 17. In cell Al, increase the font size of the test to 28 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 I page tall. you are instructed to print, print the entire workbook 19. Save the workbook, and then close it EX 1-6: Formatting Table Styles mat Filter Select Clipbord Aigment Font Number Styles Cefls Editing G21 SUMIG15:G19) 1 MediT Conference Miscellaneous Expenses 3 Summary 4 Expense Categories 5 Total Expenses 4 $9,064.97 7 Host 8 Address 9 City Harbor Convention Center 1082 Suncrest Avenue Boston 10 State Massachussets 11 Postal Code 02128 12. Phone (617)555-1082 13 Tax Rate 5% 14 Account ID Expense Category Subcategory Description 9010 Printing of brochures and conference materials 9020 Decorations for banquet 9025 Flowers for the banguet tables Units Cost per Unit Total 15 E2-9010 12 1600 $2.45 $3.920.00 $850.55 16 E2-9020 E2 S850.55 17 E2-9025 E2 20 $250.00 $6.25 $3,281.25 $55.25 S12.50 18 E2-9040 E2 9040 Gift bags for conference attendees S25 19 E2-9045 E2 9045 Gifts for banquet speakers $331.50 20 21 Subtotal S8.613.30 22 23 Est.tax 5431.67 TOTAL S9.064.97 24 25 Documentation Miscellaneous Expenses Reaty P Type here to search 1/12/2021 Prt Scn Home End Pgup m F4 16 %23 & 2 3 4. 5 7 8 9.
Expert Answer:
Related Book For
Accounting
ISBN: 978-0324662962
23rd Edition
Authors: Jonathan E. Duchac, James M. Reeve, Carl S. Warren
Posted Date:
Students also viewed these accounting questions
-
Your instructor will show you how to create data files using the software for your course. Use it to create the data file you constructed by hand in Exercise 1.17 or 1.18.
-
Open the workbook OM455. Save the file under a new name, OM455COUNTIF.xls. Suppose we wish to automatically count the number of each letter grade. a. Begin by putting the letters A, B, C, D, and F in...
-
Open the workbook OM455. Save the file under a new name, OM4555Revised.xls. Suppose we wish to use a more refined grading system, as shown below: Update the file to use this more refined grading...
-
You have a project to build a new fence. The fence is four sided as shown below. Each side is to take one day to build and is budgeted for $1,000 per side. The sides are planned to be completed one...
-
Why and when would you want to use ANOVA in marketing research?
-
Write a method called inOrderList that returns a list containing the sequence of values obtained from an inorder traversal of your binary tree of integers. For example, if a variable t refers to...
-
How can you protect yourself against credit card theft or loss?
-
Refinancing of Short-Term Debt On December 31, 2010, Santana Company has $7,000,000 of short-term debt in the form of notes payable to Golden State Bank due in 2011. On January 28, 2011, Santana...
-
NPV and maximum return??? A firm can purchase new equipment for a ?$16,000 initial investment. The equipment generates an annual? after-tax cash inflow of ?$5,000 for 6 years. a. Determin...
-
The bar codes above represent locations in a warehouse. How many units would be left in each location? If you picked 250 units from location 00000113774360000 If you picked 250 units from location 00...
-
XYZ Company is exploring two mutually exclusive opportunity. There are two available opportunities for XYZ with the following information: a. ABC Company has projected annual returns of Php7 Billion...
-
Oceanview Hotel, a renowned establishment in a bustling coastal city, has recently been under scrutiny due to discrepancies in its financial statements. As an external auditor or a concerned...
-
As a financial manager (consultant), undertake an investigation on your allocated company to evaluate its strategic financial position. Prepare a business report for the board of directors of the...
-
Fellar Corporation has identified the following information: Activity pools Materials handling Machine maintenance Cost drivers $ 33,580 $ 18,630 Number of material moves Number of machine hours 730...
-
Three uniform spheres are located at the corners of an equilateral triangle. Each side of the triangle has a length of 1.32 m. Two of the spheres have a mass of 4.84 kg each. The third sphere (mass...
-
In 2 0 2 3 , Active Lifestyle Inc. purchased $ 4 , 3 6 4 , 0 0 0 of new plant & equipment, resulting in $ 4 , 3 6 4 , 0 0 0 in cash flows from investing activities. During the year, it raised $ 5 , 0...
-
Por lo general, se entiende que los flujos de efectivolibres representan lo que queda fuera de los flujos de efectivo delas operaciones despus de 1 answer
-
Create a data model for one of the processes in the end-of-chapter Exercises for Chapter 4. Explain how you would balance the data model and process model.
-
How is the income of the investor impacted by equity method investments?
-
Explain the difference between the admission of a new partner to a partnership (a) By purchase of an interest from another partner and (b) By contribution of assets to the partnership.
-
To match revenues and expenses properly, should the expense for employee vacation pay be recorded in the period during which the vacation privilege is earned or during the period in which the...
-
A Mrs T Hulley is paid monthly. For part of April 19X6 she earns 860 and then goes on maternity leave, her maternity pay for April being 90. She has pay free of tax 320, whilst on the next 250 she...
-
A P Urmston is paid monthly. For June 19X6 he earns 1,500 and also receives statutory sick pay of 150. He pays 90 superannuation which is allowed as a relief against income tax and he has further...
-
The data which follows was extracted from the books of account of H Kirk, an engineer, on 31 March 19X6, his financial year end. You are required to allocate each or part of the items above to either...
Study smarter with the SolutionInn App