SHOW PICTURE FOR THIS ASSIGNMENT

Project Overview

You are interested in obtaining a loan to buy a new automobile, and you must convince a banker that you have the money required to afford the car payment for the vehicle of your choice. The first step in the process is to prepare a personal budget by tracking the last three months of your actual income and expenses. You will use this information to determine your average income and expenses for the three-month period. Based on this average, you will determine the maximum loan amount you can borrow based on the length of financing you choose and the current interest rates.

Project Instructions

Start with a blank workbook and create the Documentation, Budget and Car Loan sheets as described below. Be sure to format all sheets so they are professional looking and easy to read. Make sure each sheet is set up to print properly. Add your name, the file name and the sheet name as a header on all sheets. Remember, you will be presenting this information to a banker and the outcome of your car purchase depends on the quality of your work. Save your workbook as your Lastname_Firstname_Ind_Project_Budget. This assignment is worth 100 points.

Documentation Sheet

Start with a blank workbook. Rename the first sheet Documentation. Add labels and details showing your name, the date, and the purpose of the workbook. You may include additional information if you'd like and format in any way you choose. Including this information in a workbook is good practice because if you use Excel frequently, you can forget what a workbook is for. Also, if you get promoted or leave your job, the person taking over for you will have a better idea of what your files can be used for.

Budget Sheet

Insert a new Sheet 2 named Budget. Use this sheet to record your actual Income and Expenses for the past three months along with the Totals and Averages. Income refers to money you have coming into your bank account. Label the source of the income, such as Wages from a job, Financial Aid, Allowance from Parents, etc. Expenses refers to money going out of your bank account. Pick 5 10 things that you spend money on, and then go back and add up how much you spent for each of the past three months. Examples include groceries, eating out, entertainment (Hulu, movies, golf), gas, utilities, or rent.

Clearly label all data. Start by entering a title at the top of the sheet. Create column headings identifying each of the three months. Create row headings for Income and Expense entries. Organize your data in consecutive columns and rows.

Show the Total Income and the Total Expenses for each month. Use a formula to show your Cash left at the end of the month (Cash Flow) which would be found by taking Total Income (money in) and subtracting Total Expenses (money out).

After entering actual income and expenses for three months, use a formula to find the three-month total income and total expense for each income and expense category as well as the cash flow. Use a formula to find the three-month average income and average expense for each income and expense category as well as the cash flow.

Remember you will be sending this information to your banker, so format your results and set up the sheet to print on 1 page. Widen your column width or increase your row height as needed. Include appropriate accounting or number formats on all values.

NOTE: Information data used for this assignment may be fictional. However, data needs to be realistic and show that some thought has been put into the process. For example, some categories like rent might show the same amount each month but other categories like Food, Eating Out or Utilities would be different each month. When grading, I am NOT concerned about the personal values used for income or expenses. Grading is based on how well you use Excel to accomplish the tasks.

You can do this any way you choose so no two student's information will be exactly alike. You can use any three months you want, and you can use any Income and Expense categories that are appropriate for your situation. One suggestion for organizing your data is shown below:

Note: You do NOT have to use this exact layout. It is just a suggestion. Do NOT use this exact sample in your own workbook. Do Not include the yellow and gray fill as this is just for informational purposes.

Car Loan Sheet

On a new sheet named Car Loan, you need to calculate how much of a car loan you can afford based on the Cash you have left over in your budget. For example, if your three-month average shows you have $350 left over each month, then find a car and payment plan that is less than $350 a month. First, use the Internet to research and choose a car you would like to buy that you think you can afford. Also, research financing options to find current interest rates offered by your bank, car dealerships or any other source.

Enter labels and data to show the following:

Year, Make and Model of car you want to buy Purchase Price, Down Payment (if any), and the Loan amount (Purchase Price Down Payment). Interest rate and number of months you would use to finance your car such as 36, 48, 60 or 72 months. Use the PMT function to calculate your monthly payment. The PMT function has three required arguments and two optional arguments. =PMT (rate,nper,pv,[fv],[type]). Remember to use cell references in your formula rather than typing in values so the formula will automatically recalculate if you change your data.

The rate is the interest payment for the period. Interest rates are typically stated as APR or annual percentage rates. Since you are trying to find a monthly car payment, you will need to divide the APR rate by 12 to determine the monthly interest rate.

The nper is the total number of payment periods. Choose the number of months you would use to finance your car from 36, 48, 60 or 72 months.

The pv is the present value of the loan. This is the amount you will finance which would be the purchase price minus the down payment.

The PMT function result will be a negative value as it represents your debt. To display the value as a positive value, include a minus sign in front of the present value cell reference.

Remember to use cell references in your formula rather than typing in values so the formula will automatically recalculate if you change your data. For example, your formula might look like =PMT(B11/12,B12,-B9) if B11 was the annual percentage rate, B12 was the number of months, and B9 was the loan amount.

If your first PMT result is more than your budget allows, change the purchase price, down payment, interest rate or the number of months for financing until you come up with a combination that gives you a payment that is less than your projected Cash left over from your budget. You may have to go back and pick a cheaper car, put more money down or extend your loan over more months to meet your goal. Format the sheet professionally as you will be using this to convince the banker that you can afford the car!