Housing/Transportation/Loan – Portfolio
75 points
Build a workbook in Excelthat will compute expenses that will be incurred after college. The workbook will consist of three separate worksheets identified as: Housing Expenses, Transportation Expenses and Student Loans. The worksheet tabs should be labeled accordingly.
Housing Expenses
This worksheet will compute all expenses incurred by housing. An affordable house to purchase should be located in the area of your prospective employment and somewhat based on the projected salary. Include in the worksheet information such as the location of the house, some of the details of the house and a citation to the source of your information.
Use Excel to compute the house payment based on both a 15-year and a 30-year mortgage. Research should be done to establish the terms of the loan, which includes the interest rateand the associated number of years for the duration of the loan. Make sure to include a citation for this information as well.
Also research a place to rent in the area of your prospective employment. Include information about the rental location, some details about the rental and a citation to the source of your information.
Finally, include some estimations on the cost of utilities. These are also associated with the Housing Expenses and should be included on this worksheet.
This workbook should reflect the material learned in this class. Isolate and label all assumptions while building this worksheet. The worksheet to the right is an example of what this worksheet might contain.
If some of the data is determined by past experience (bills you already pay), this can be noted in the worksheet.
Transportation Expenses
The second worksheet on Automobile Expenses will contain research on the cost of purchasing an automobile. Include in the worksheet a picture of the automobile, along with some details about the automobile. Include a citation to the source of this information. Use Excel to compute the monthly payment for the vehicle, based on yearly interest rate, the down payment and the number of years for the loan.
Provide a citation to the site where the information for the terms of a 2-year, 4-year and 5-year loan were located.Finally, provide an estimate for monthly gas and repairs associated with owning an automobile. There are many sites that provide commuting calculators and average cost estimates for these two items.
Student Loan
The last worksheet will compute the amortization schedule for outstanding student loans. Before building the worksheet, research should be done to identify the rate of the loans and the number of years to pay the loans back. If there are multiple loans, they should be consolidated into one loan. If no student loans exist from the bank, then compute the amortization schedule for money you received from “others” (at a minimum, this should be $2500).
Locate a predefined Excel template that will produce an amortization schedule, then enter the appropriate values for the terms of the loan. When using a template, it may be necessary to turn off protected areas of the worksheet, so that you can change values and/or insert comments. Initially, compute the total interest paid for the loan without making any extra payments, then include one extra payment of $50 and indicate in a note at the bottom of the worksheet how much interest was saved by making the extra $50 payment.
***Please note: Excel requires the worksheet that will contain a template to be created FIRST, therefore you should create the worksheet with the template first, then add the Housing and Transportation worksheets later.
Workbook Requirements
All supporting data items provided by a source should be highlighted using a light blue cell fill color. Provide a comment (using the “insert comment” option) indicating the data source. Tables of source data should include just one comment in an appropriate spot in the table.
All input data provided by you should be highlighted using a light green cell fill color. Labels should be formatted to stand out but not detract from the readability of the worksheet and should be spelled correctly. All other values are calculated values and should have no fill color (there should be quite a few of these!). All numeric values (calculated, source & input) should be properly formatted with a numeric format that represents what they are (dollar amounts, percentages, etc.).
Finally, all worksheets and charts should be set up to print properly (print area set, fit-to-one page, landscape or portrait, with headers and footers). The header should contain your name right-justified. The footer should contain the date obtained from the system left-justified and the exercise name House/Transportation/Loan right justified.
Save the document as housetransloan.xlsx