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

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

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)

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.

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 moreEach 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 moreThanks 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 moreYour 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 moreBy 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