Car Payment and Car Fuel Economy, Excel Analysis help

Anonymous
timer Asked: Jun 13th, 2016
account_balance_wallet $10

Question Description

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.

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.

Tutor Answer

Ellah
School: Rice University

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

Task 1

Uhaul (Linear)
MPG=-22.473
Curb weight=12,600

Uhaul (Exponential)
MPG=5.27
Curb weight=12,600

Highway MPG

Curb weight vs.Highway MPG
45
40
35
30
25
20
15
10
5
0

Curb weight Vs Highway
MPG

y = -0.006x + 53.127

Linear (Curb weight Vs
Highway MPG)

0

2000

4000

6000

Curb weight

45
40

Curb weight vs Highway MPG

35

MPG

30
25
20

y = 65.506e-2E-04x

Curb weight vs MPG

MPG

25

Curb weight vs MPG

y = 65.506e-2E-04x

20
15

Expon. (Curb weight vs
MPG)

10
5
0
0

2000

4000

6000

Curb weight

45
40
35
30
25
20
15
10
5
0
3000

35000
30000
25000
20000
15000
10000
5000
3500
Curb weight

0
4000

Cost

MPG

Curb weight vs MPG and Cost

Curb weight vs MPG and
Cost
Cost

Cost of the Vehicle
$17,170
$27,995
$18,195
$23,403
$32,486
$21,545
$28,975
$24,895
$22,120
$25,990

45

Curb weight vs Highway MPG

40

Highway MPG

35
30

Curb weight vs
Highway MPG

25
20

Linear (Curb weight vs
Highway MPG)

15

Expon. (Curb weight vs
Highway MPG)

10
5
0
0

2000

4000

6000

Curb weight

40
35

MPG

30
25

Curb weight vs MPG
and Cost
35000
30000
25000
20000

Cost

45

Curb weight vs MPG and
Cost

25

20000

20

15000

15

10000

10

5000

5
0
0

2000
4000
Curb weight

0
6000

Cost

MPG

30

Curb weight vs MPG and
Cost
Cost


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 ...

flag Report DMCA
Review

Anonymous
Goes above and beyond expectations !

Similar Questions
Hot Questions
Related Tags
Study Guides

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors