CSCI 1100 Excel Project II

Excel - thinking outside the book

For the second Excel project, you will work through a real-world scenario as we did in class together. Two people (Person A and Person B) will be compared over a number of years based on their purchasing/investing behavior to compare who is "better off" after a number of years. Set up your spreadsheet with your name and a title for the sheet at the top, and add appropriate cell labels (for years, column headings, etc.) and formulas to compute the scenarios presented. You'll produce one file (something.xls) that has three sheets (labeled Analysis, Scratch, and Charts).

• Both person A and person B will be tracked for 40 years. These two people are remarkably similar, in earnings power, disposable income, etc., but quite different when it comes to purchases and investments.
• Label the first sheet of your workbook "Analysis", and set up the spreadsheet with a title and your name.
• Add a cell for the "Rate of Return" to be used in the computations. Label this cell clearly (above or to the side), and enter 9% as the rate.
• Set up 5 columns each for Person A and Person B: Year (listing years 1-40), Monthly Disposable Income (wrap that title in a single cell), Car Payment, Investment Amount, Total Invested.
• In the Monthly Disposable Income column for each person, assume both start out with $400/month disposable income, and get a $50/month raise in disposable income each year over the next 40 years. (Year 1: $400, Year 2: $450, etc.)
• Assume Person A purchases the best car he can pay off in 2 years. Person B purchases the best car he can pay off in 5 years. Both people buy a new car every five years (A with two years payment, B pays all five years). By "best car", figure out the most expensive car each can afford based on their "disposable income" at the time of purchase, then figure out how expensive a car they can afford as follows:
• Label the second sheet of your workbook "Scratch" and enter =PMT formulas under person A and person B for the 8 cars they'll buy over their working lifetimes. Enter the Monthly payments and a rate of .07/12 (7% interest, monthly payments), and change the Present Value (PV) to see how expensive a car (to the nearest thousand) each can afford for that payment.
• Enter each of the monthly car payments in the Car Payments columns for persons A and B.
• Calculate Person A and B's monthly investment amount and Total Invested using the values in the cells. Do this for all 40 years (autofill from the second year down).
• On the third sheet of your workbook, produce a chart of person A's net worth (Total Invested) and person B's net worth over the 40 years (two charts, or a single chart comparing). Label the sheet "Charts".

Due online Wednesday, November 2, by the beginning of class.