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.