Excel Project one
Learning Goal: I’m working on a data analytics project and need a sample draft to help me learn.
Your credit union is offering a See the Worldtravel loan with a great annual interest rate of 1.9%. You
can borrow up to a maximum of $5,000 and take up to 3 years to pay it back.
You are extremely tempted by this offer and are trying to decide if it is worth it to take out the loan so
you can go on a big vacation. Of course you will use Excel to help you decide. How you set up each
worksheet is up to you, but be sure you are creating the most flexible worksheets possible while using
all of the skills you have learned so far in the class.
You are going to create a workbook with three worksheets that will help you calculate the following:
● Estimated Cost of the trip
● Monthly payments to pay back the loan
● Total amount you will pay back
● How much more you will pay back than what you borrowed
Be sure to include somewhere in the workbook where you are going for your trip and a brief
summary of the trip itself. Also, include a picture or two!
Before you get started, make sure you have reviewed the “Excel Good Practice Guidelines”
document located in the “Project Instructions” module and in the Module 5 “Read” section in
Canvas.
Worksheet 1 – Trip Expenses
For this worksheet, you will need to track and calculate all of the expected expenses for your trip. You
will want to set this up using formulas so that you can easily change the values (for example, how
many days you rent a car or how much your hotel costs each night). Find an actual hotel in the city
where you want to stay and use that information. If you are flying, be sure to track and calculate the
cost of airfare for you and anyone who might be traveling with you.
Some example expenses might be:
● Airfare
● Car rental
● Lodging (hotel, campground, hostel, Air BnB)
● Food (3 meals a day for the # of people going – everyone needs to eat!)
● Souvenirs
● Excursions (find some specific fun things to do in the place you going)
Requirements for each expense
● Description
● Quantity
● Individual Cost
● Total Cost (for the expense)
● Percent of Total Trip Expenses
● URL for more information, if applicable (like the hotel website, excursion information)
Additional calculation requirements (consider using the functions you learned about in Chapters 2 and
3 such as Sum, Average, Count, CountA, etc.)
● Total cost of the trip
● Average cost of all the expenses
● Number of expenses
Worksheet 2 – Loan Information
On this worksheet you will calculate the loan information. It is up to you to decide how much of the
$5,000 maximum you want to borrow. Your Loan Amount might be the same as the Estimated Trip
Cost, or it might be less if you are planning on using money you have saved up.
This worksheet needs to show the following:
● Estimated Trip Cost (cell reference to the Trip Expense worksheet)
● Loan Amount (maximum of $5,000)
● Monthly payment (remember to use cell references and you must use the payment function!)
● Total amount paid on loan payments
● Difference between the amount borrowed and amount paid
● IF function that results in either Yes or No for taking the trip; you choose what your criteria will
be. For example, maybe you will take the trip if the monthly payment is below a certain dollar
amount, or if the difference between the amount borrowed and the amount paid is below a
certain dollar amount.
Include a paragraph about your decision regarding borrowing the money for the trip. Would you
borrow it? Why or why not? What other factors might you use to help you decide?
Worksheet 3 – Analysis
Structure Analysis – why did you set up the worksheets the way you did? How did you decide what
columns to create and which formulas to use?
Formatting Analysis – describe why you chose the formatting that you did for each worksheet. What
prompted you to choose the colors, number formatting, etc. that you used?
Print Setup Analysis – what print setup options did you use? Why did you use them?