10. Compute the monthly cost of gas. a. In cell C21, enter a formula to calculate the number of miles you expect to drive each month. Divide the value of number of miles (cell A5 from the Data sheet) by the average MPG for the vehicle multiplied by the price of a gallon of gas (cell A6 from the Data sheet). b. Copy the formula to the appropriate cells for the other vehicles. c. If cells D21:F21 display an error or a value of 0, display formulas and check for errors. d. If you still can't find the error, try displaying the precedent arrows. Hint: The references to the cells on the Data sheet should use absolute references. If they do not, the formula will update incorrectly when you copy it across the row. e. H A B c D E F 1 Vehicle Purchase Assumptions 2 3 $9,000 Total Cash Available for Vehicle Purchase 4 $650 Total Monthly Amount Available to pay for Operating Vehicle 5 650 Number of Miles | Expect To Drive Each Month 6 $2.79 Gas Price Per Gallon 7 6 00 9 Registration Fee Lookup Table 2 10 column # 11 12 13 14 15 16 17 1 Cargo Van Farm Vehicle Motor Home Motorcycle Passenger Trailer Truck 100 68 100 60 100 30 1001 AutoSave Trevor Bottom-EX2019-Chalenge Yourself-3-3(2) - Excel o Trevor Bottom File Home Insert Draw Page Layout Formulas Data Review View Help Share CIS f If D Analysis Vehicle 2 Vehicle 3 Vehicle 4 1 Vehicle Purchase Vehkle 1 Description 2019 Ford Mustang Convertib Vehicle Type Passenger 2019 Ford F150 Truck 2017 Toyota Prius Passenger 2017 Ducati Super Sport Motorcycle Averara Lowest dealer $31,895 21 dealer $26.115 19 25 22 private seller $24.463 58 53 555 private seller $12,995 36 36 96 Price MPG City MPG Highway 26 Average MPG $600 100 $1,200 $500 100 $800 $300 100 $900 $500 60 $1,500 Maintenance/Yearl Registration Feel Insurance/Year NPR 1.90 Seller 7 Price UPG CITY 9 MPG Highway 10 Averape MPG 11 Yearly Costs 12 Maintenance/Year Registration fee Insurance/Year 15 Need Loan? 16 Amount to Borrow 17 Years 19 Monthly Costs 20 Loan Payment 21 Gas 22 Maintenance/Month 23 Insurance/Month 24 Total Monthly 25 Affordable? 27 28 29 30 3.203 5 2 90% 3 5.90 Amount to Borrow APA Years 3 2 Loan Payment Gas Maintenance/Month Insurance/Month Total Monthly 10. Compute the monthly cost of gas. a. In cell C21, enter a formula to calculate the number of miles you expect to drive each month. Divide the value of number of miles (cell A5 from the Data sheet) by the average MPG for the vehicle multiplied by the price of a gallon of gas (cell A6 from the Data sheet). b. Copy the formula to the appropriate cells for the other vehicles. c. If cells D21:F21 display an error or a value of 0, display formulas and check for errors. d. If you still can't find the error, try displaying the precedent arrows. Hint: The references to the cells on the Data sheet should use absolute references. If they do not, the formula will update incorrectly when you copy it across the row. e. H A B c D E F 1 Vehicle Purchase Assumptions 2 3 $9,000 Total Cash Available for Vehicle Purchase 4 $650 Total Monthly Amount Available to pay for Operating Vehicle 5 650 Number of Miles | Expect To Drive Each Month 6 $2.79 Gas Price Per Gallon 7 6 00 9 Registration Fee Lookup Table 2 10 column # 11 12 13 14 15 16 17 1 Cargo Van Farm Vehicle Motor Home Motorcycle Passenger Trailer Truck 100 68 100 60 100 30 1001 AutoSave Trevor Bottom-EX2019-Chalenge Yourself-3-3(2) - Excel o Trevor Bottom File Home Insert Draw Page Layout Formulas Data Review View Help Share CIS f If D Analysis Vehicle 2 Vehicle 3 Vehicle 4 1 Vehicle Purchase Vehkle 1 Description 2019 Ford Mustang Convertib Vehicle Type Passenger 2019 Ford F150 Truck 2017 Toyota Prius Passenger 2017 Ducati Super Sport Motorcycle Averara Lowest dealer $31,895 21 dealer $26.115 19 25 22 private seller $24.463 58 53 555 private seller $12,995 36 36 96 Price MPG City MPG Highway 26 Average MPG $600 100 $1,200 $500 100 $800 $300 100 $900 $500 60 $1,500 Maintenance/Yearl Registration Feel Insurance/Year NPR 1.90 Seller 7 Price UPG CITY 9 MPG Highway 10 Averape MPG 11 Yearly Costs 12 Maintenance/Year Registration fee Insurance/Year 15 Need Loan? 16 Amount to Borrow 17 Years 19 Monthly Costs 20 Loan Payment 21 Gas 22 Maintenance/Month 23 Insurance/Month 24 Total Monthly 25 Affordable? 27 28 29 30 3.203 5 2 90% 3 5.90 Amount to Borrow APA Years 3 2 Loan Payment Gas Maintenance/Month Insurance/Month Total Monthly
