Car Payment and Car Fuel Economy, Excel Analysis help

  

1. Car Payment Assignment: This assignment is to help you get more familiar with excel. This is a very challenging Excel analysis problem as it summarizes everything you have learned up to this point and even introduces you to a new built-in function called the “pmt” function. This will test your ability to understand how to input the correct data so that the built-in functions work properly. It will also test your ability to think logically about what your columns and rows of data actually mean in a worksheet.2. Car Fuel Economy: This assignment is to help you get more familiar with excel. This also helps you practice finding differences between reliable and non-reliable sources on the internet. You will be creating graphs to analyze data. You’ll find that Excel makes a very powerful calculator.
1._car_payment_in_class_assigned.docx

2._car_fuel_economy_sp_2016_online.docx

Don't use plagiarized sources. Get Your Custom Essay on
Car Payment and Car Fuel Economy, Excel Analysis help
Just from $10/Page
Order Essay

Unformatted Attachment Preview

Excel assignment
Background information
You are considering the purchase of a new car and taking out a loan. Here are the terms.



Loan Amount: $35,000
Interest rate: 6.5% (Annual), compounded monthly
Term: 60 months
Task #1: Determine the total cost of the loan (Payment per month * number of months). Payment per month can be
found using the PMT function. =PMT(rate/12,term_in_months,loan_amount)
Task #2: Now that you have the total cost of the car and the initial loan amount, determine the total amount of interest
paid on the life of the loan.
Task #3: Compute and show on the spreadsheet, the monthly balance and interest paid each month for all 60 months.


Interest is paid each month on the previous month’s balance.
The balance for each month is the previous month’s balance, plus interest, minus the payment.
Also answer these questions
Assume that you can negotiate some of the terms. Determine how much you could save if you could negotiate a 10%
better price, lower interest rate, or shorter term. You don’t have to re-calculate the monthly balance for each scenario.
This can easily be done by changing the values in the PMT function. If you have the total cost computed automatically,
you can make the change and the new value will be computed.
Task #4: Recalculate total cost, total interest paid and monthly payment, with price lowered to $31,500 (10% off).
Task #5: Recalculate total cost, total interest paid and monthly payment, with interest lowered to 6.1%.
Task #6: Recalculate total cost, total interest paid and monthly payment, with length of loan reduced to 54 months.
Task #7: Which of the 3 choices (Tasks 4, 5, 6) saves you the most total money (lowest total cost)? Mark clearly on the
Excel sheet.
Task #8: Which of the 4 choices (original problem and Tasks 4, 5, 6) gives you the lowest total interest on the life of the
loan? Mark clearly on the Excel sheet.
Task #9: Which of the 4 choices (original problem and Tasks 4, 5, 6) allows you to pay you the lowest monthly
payments? Mark clearly on the Excel sheet.
How to submit
Upload the excel spreadsheet (NOT AS A PDF) to the assignment submission on eCampus showing all of the requested
information (tasks 1-9). Make sure everything is aesthetically pleasing and clearly label and indicate your answers. You
do not have to submit these directions.
Name__________________________________
Car Fuel Economy
Objectives:
1.
2.
3.
4.
5.
6.
7.
8.
Create a chart of important values
Determine what type of graph best fits the available data (exponential, linear, logarithmic, polynomial… etc)
Create a plot using the correct values and determine which variable goes on which axis
Fit a line to the graph and show equation
Use the equation to predict a future value
Use the equation editor to display equations
Plot on a secondary axis
Change the bounds of an axis to take a closer look
Your task:
You want to determine whether the curb weight of the vehicle has any impact on the Highway MPG of the vehicle. You
also want to find out if there is any relationship between the Highway MPG, curb weight and the cost of the vehicle.
Manually input the following information into excel to do an analysis of car fuel economy.
Car Make and Model
Ford Focus
Dodge Charger
Subaru Impreza
Ford F150 2WD
Ford F150 4WD
Mazda CX 5
Honda Odyssey
Subaru Outback
Chevy Equinox
Kia Optima Hybrid
Curb Weight (lbs)
2907
3961
2911
5073
5375
3291
4470
3423
3777
2483
Highway MPG (mi/gal)
37
31
34
22
21
32
28
30
32
40
Cost of the Vehicle
$17,170
$27,995
$18,195
$23,403
$32,486
$21,545
$28,975
$24,895
$22,120
$25,990
On Excel, record the make, model and year of the vehicle, the curb weight, and the highway MPG (Miles per gallon).
(Please follow the correct homework heading format and please record your answers in blue color on this sheet)
General note: Whenever you create a graph, label the X-axis and the Y-axis. Give the graph an appropriate title and
include units for the axes. Use the Equation Editor to type answers as equations.
1.
2.
3.
4.
Create/insert a scatter plot of the Curb weight (X-axis) vs. Highway MPG (Y-axis).
Select the data points and add a linear trendline for the 10 cars you currently have. Make it a solid green line.
Select to display the equation on the chart. What is the equation? _______________________________
Using your equation, predict the MPG of a Uhaul that has a curb weight of 12,600lbs.
What is that MPG? ___________________________. Record the Uhaul’s curb weight and predicted MPG on the
excel sheet. Label clearly (linear) and use Equation Editor to show the equation used.
Does that answer make sense? Why or why not? ___________________________________________________
___________________________________________________________________________________________
5. According to the Uhaul website, a typical truck will get about 10 MPG. Create another scatter plot (do not delete
the original one) that shows the same data, but is fitted with an exponential trendline, but don’t include the
Uhaul in your second or third graph (dashed red line).
6. Select to display the equation on the chart. What is the equation? ________________________________
7. Using your equation, predict the MPG of a Uhaul that has a curb weight of 12,600lbs.
What is that MPG? ___________________________. Record the Uhaul’s curb weight and predicted MPG on the
excel sheet. Label clearly (exponential) and use Equation Editor to show the equation used.
Does that answer make sense? Why or why not? Is this prediction better or worse than the first equation?
___________________________________________________________________________________________
___________________________________________________________________________________________
8. Now create a third scatter plot of curb weight and highway MPG. Add both a linear and exponential trendline.
Make the linear trendline a solid green line and the exponential trendline a dashed red line (just like the two
previous graphs). Again, don’t include Uhaul here.
9. Now create a 4th scatter plot of Highway MPG and the Cost of the vehicle, both as functions of the curb weight
(do not plot the UHAUL).
a. Select curb weight and highway MPG to plot first.
b. Right click on the graph and click “select data.”
c. “Add” a new set of data by selecting curb weight as the x-axis and cost as the y-axis. Name it “Cost”
d. Plot the data
e. Right click on the data points and format data series
f. Select to plot on a secondary axis
g. Insert an arrow from the insert tab at the top (in “shapes”). Draw it from your curb weight vs MPG data
to the proper y-axis. The purpose of this is to show which data set on your graph goes to which axis. Do
the same for the Cost data set to the secondary axis on the right. Format the arrow so that it’s the same
color as the data set.
h. Analyze the data. Can you see any trends? What is the relationship between curb weight and MPG?
What is the relationship between curb weight and cost? Are there any outliers? What is causing them to
be an outlier?
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
_____________________________________________________________________________________
10. Let’s take a closer look at the graph. Copy the graph by clicking on the 4th graph and press ctrl + C.
a. Change this 5th graph so that the x-axis is now from 3000 to 4000.
b. Right click on the x-axis and click format axis”
c. Change the bounds so that the minimum is 3000 and the maximum is 4000.
d. This allows me to take a closer look at a specified section of the graph. How many data points do you
see here (originally, there were 20)? _________________ data points.
11. Make sure you included a title, legend, arrows and axis labels for all graphs.
12. Organize the excel sheet to make it look presentable.
13. Please make sure you have all the questions answered on this document and upload the Excel sheet that you
created in the assignment on eCampus.

Purchase answer to see full
attachment

Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more

Order your essay today and save 30% with the discount code ESSAYSHELP