UMB Vehicle Purchase Assumptions and Fee Lookup Excel Table Worksheet

User Generated

ghnagena123

Business Finance

University Of Massachusetts Boston

Description

  1. Calculate the average MPG for each vehicle.
  2. Calculate the registration fee for each vehicle.
  3. Determine whether or not you will need a loan for each potential purchase.
  4. Calculate how much you would need to borrow for each purchase.
  5. Calculate the monthly payment amount for each loan.
  6. Compute the monthly cost of gas.
  7. Compute the monthly cost of maintenance.
  8. Compute the monthly cost of insurance.
  9. Determine which vehicles are affordable.
  10. Complete the Analysis section using formulas with statistical functions. Use named ranges instead of cell references in the formulas.
    • Hint: Select cells B7:F24 and use Excel’s Create from Selection command to create named ranges for each row using the labels at the left side of the range as the names.
    • Hint: Open the Name Manager and review the names Excel created. Notice that any spaces or special characters in the label names are converted to _ characters in the names.
    • Hint: To avoid typos as you create each formula, try using Formula AutoComplete to select the correct range name.

    Unformatted Attachment Preview

    Vehicle Purchase Description Vehicle 1 Vehicle 2 2019 Ford Mustang Convertible Vehicle Type Passenger Seller Price MPG City MPG Highway Average MPG 2019 Ford F150 Truck dealer $31,895 21 31 dealer $28,115 19 25 $600 $500 $1,200 $800 3.20% 5 2.90% 3 Yearly Costs Maintenance/Year Registration Fee Insurance/Year Need Loan? Amount to Borrow APR Years Monthly Costs Loan Payment Gas Maintenance/Month Insurance/Month Total Monthly Affordable? Analysis Vehicle 3 Vehicle 4 2017 Toyota Prius Passenger 2017 Ducati Super Sport Motorcycle private seller $24,463 58 53 private seller $12,995 36 36 $300 $500 $900 $1,500 Maintenance/Year Registration Fee Insurance/Year 5.90% 2 Amount to Borrow APR Years 1.90% 3 Average Price MPG City MPG Highway Average MPG Loan Payment Gas Maintenance/Month Insurance/Month Total Monthly Analysis Highest Lowest Vehicle Purchase Assumptions $9,000 $650 650 $2.79 Total Cash Available for Vehicle Purchase Total Monthly Amount Available to Pay for Operating Vehicle Number of Miles I Expect To Drive Each Month Gas Price Per Gallon Registration Fee Lookup Table column # 1 Cargo Van Farm Vehicle Motor Home Motorcycle Passenger Trailer Truck 2 100 68 100 60 100 30 100
    Purchase answer to see full attachment
    User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

    Explanation & Answer

    Hey, I have completed the spreadsheet. Do let me know if you find any error in this. I will be happy to help you out.

    Vehicle Purchase
    Description

    Vehicle 1

    Vehicle 2

    2019 Ford Mustang Convertible
    Vehicle Type
    Passenger
    Seller
    Price
    MPG City
    MPG Highway
    Average MPG

    2019 Ford F150
    Truck

    dealer
    $31,895
    21
    31
    26

    dealer
    $28,115
    19
    25
    22

    $600
    100
    $1,200

    $500
    100
    $800

    Yearly Costs
    Maintenan...


    Anonymous
    Excellent resource! Really helped me get the gist of things.

    Studypool
    4.7
    Trustpilot
    4.5
    Sitejabber
    4.4

    Related Tags