Assignment is subject to corrections and changes! If printing this assignment, please be sure to check the online version for changes and to access any links. PLEASE ALWAYS LOOK BACK AT THE WEB VERSION WHEN COMPLETING THIS ASSIGNMENT.

POINTS: 50 points

SUMMARY: Work with What If Analysis ( data table & goal seek ), loan amortization & data filters, Build formulas!

SKILLS NEEDED:

  • Critical Thinking!
  • Math:-Basic algebra
  • Use formulas: Create formulas by referring to cells. Use PMT & SUM functions
  • Work with worksheets: Use fill by series. Refer to cells on other worksheets. Insert images. Delete rows. Import by copy & paste
  • Work with Data: Create a what-if data table. Use goal seek. Filter data.
  • Formatting: Change: font size, bold, italic, text color, number formats, row height, column widths.
  • Terminology - any word that you are unfamiliar with such as words related to loans - principal, term, interest. Use a search engine to find the meanings!


Distance Learning Students: If you have completed the lessons and training and still are not sure how to do a particular step or the instructions are unclear, email the instructor for help.

Classroom Students: Ask the instructor in class.

NOTE: The learning exercises must be completed and submitted BEFORE this assignment. If software assignment is turned in without the learning exercises being completed, the learning excises will be give a score of 0. Even if you think you know how to do things, you might not know the most efficient method. Do the learning exercises and learn!

REMEMBER TO REFER TO CELLS!!!! No points will be awarded for formulas that have numbers typed where a cell reference should be,

Preliminary: Look over your Software Assignment: Excel 1 score to see if you had any errors. Be sure not to make the same type of error on this exercise. Double points will be taken off for making the same type of error.

Open the file as LoanAnalysis-yourname (where your name is your first name) from your ICS101 - Spreadsheets folder. This spreadsheet has three different sheets, which are named on the tabs. Follow these steps

  1. Do the lessons BEFORE doing the assignment. Somewhere in the lessons are code phrases.
    1. (1 pt) In cell H2 enter the code for spreadsheet lesson - Data & Large Spreadsheets
    2. (1 pt) In cell H3 enter the most important concept when you create a formula.
  2. Concept: Build a What if - Table for analysis. We will be completing a What if- data table to compare payments for a loan by varying principal (loan amounts) and interest.
    1. (3 pt) On the Loan Comparison sheet use the PMT function to calculate the monthly payment for the payment amount. In the cell shown below
      Cell next to Payment function
      • Remember to get a monthly interest rate by dividing the interest by 12.
    1. (0.5 pt) In the cell below the formula enter 2.5% then format as percent with 2 decimal places
    2. (0.5 pt) Use the fill series capability to fill the range as shown below stepping the amount by .005
      Range of cells from B7 to B15
    3. (0.5 pt) In cell to the right of the payment function, type in 700000, format as $ with 0 decimal places
    4. (0.5 pt) Use the fill series capability to fill in the range as shown below to Column M, stepping the amount by 50000.
      Range C6 to M6
    5. (2 pt) Select the range as shown below. Create a what if data table. (This is not a table for filtering data. ) Row input cell is the cell with the principal, Column input cell is the cell with the interest rate.
      Full ranne b6 to M13
    6. (0.5 pt) Format the all numbers in the table as currency except for the column previously formatted as percents.
    7. (1 pt) The company currently pays an average $5,000 per month in rent. Given an interest rate of 4%, what is the most they could pay to purchase a store if they do not want to exceed their current rent by more than $100?
      HINT: Go across the 4% row until you find the number that is closest to $5,0000. Then go UP the column to find the amount of the loan (Do NOT stop on the monthly payment).
    8. (1 pt) Use Excel's naming capability to name this cell loan.
      SAVE!

  3. Concept: Build an amortization spreadsheet and work with long lists.
    1. (1 pt) On the Loan Amortization sheet, in the cell next to principal the amount of the loan you determined in previous step should be entered by using the cell name.
    2. (0.5 pt) Copy the PMT formula from the Loan Comparison sheet to the appropriate cell on the Loan Amortization sheet. (Copy means copy & paste the actual formula - it does not mean refer to the cell with the formula. That cell is using a different set of data, so your amortization will be off.)
    3. (0.5 pt) Format the alignment of cells in row 8 to be Angle Counterclockwise (This was covered in the gradebook example video, just because you don't turn something in doesn't mean you don't have to do it.)
    4. (2 pt) The cell below the word, Balance, should display the balance at the start of the loan. Create a formula to be equal to cell that contains the amount of the loan.
    5. (0.5 pt) Fill in the payment numbers from 1 to 360 in column A by following this method:
      • Click on cell A10. Then while holding the shift key, press but don't hold the End Key (or equivalent) then the down arrow key. This selects the column from A10 on down. If you don't have an End key, highlight down to cell A400
      • From the Home tab Click on the Fill button. Select Series.
        • Mac users the fill button is on the left side of the screen on the Home tab
      • In the dialog box, the step value should already be 1, then fill in the stop value is 360. Click OK
    6. (0.5 pt) In cell B10 enter the formula which will calculate the amount of interest paid on the loan for that month. =D9*$B$3/12
    7. (0.5 pt) In cell C10, enter the formula which will calculate how much of the monthly payment will be applied to the principal of the loan. It would also be easier to work with if we use the absolute value of the payment. =abs($B$6)-B10
    8. (3 pt) In cell D10, enter a formula which will take the previous month's balance (row above) and subtract the current month's principal paid (Simple math - I'm sure you can figure out the formula,)
    9. (0.5 pt) Format cells B10 to D10 as $
    10. (0.5 pt) Copy the formulas in cells B10 to D10 down the columns to the payment 360.
    11. (0.5 pt) Freeze the panes above row 9, so that the titles stay on the screen when you scroll down.
    12. Scroll down to payment 360. Does the balance become 0? If not, something is wrong. Check over each step above to ensure you have completed everything correctly.
      1. If you can not find what the error, EMAIL your file to the instructor and ask for assistance.
      2. 5 additional points will be deducted if you turn in the assignment without correcting the problem.
    13. (3 pt) Create a formula in cell D3 to sum all of the interest paid over the life of the loan (Column B from B10 down through Payment 360) You may need to widen to column to see the amount. Yes the amount is quite large.

      SAVE the file!
  4. Concept: Perform What if- goal seek on a copy of the data.
    1. (1 pt) Copy the sheet Loan Amortization sheet and rename the new sheet Analysis before the Properties sheet
    2. (0.5 pt) Change top cell on the Analysis sheet to read What If Analysis.
    3. (3 pt) Perform a what if analysis - goal seek to determine what the interest rate should be so that the total interest paid is 600000 - click OK to accept the change.
      Mac users: The What if icon is on the left side of the screen on the Data tab.
    4. (1 pt) Make a copy of the Analysis sheet placing it after the Analysis sheet
    5. What if the term of the loan were changed from a 30 year loan to a 20 year loan at the interest determined in the last step. On the Analysis2 sheet make the following changes:
      1. (1 pt) Change term of the loan to 240
      2. (2 pt) Delete all the rows after payment 240 (That's the payment number NOT the row number)
      3. How much money is saved by decreasing the term of the loan? Is it worth paying the extra amount for the monthly payment to save that amount? (No need to respond to these questions, just something for you to think about.)
        SAVE!
  5. Concept: Import data and using filtering.
    1. (1 pt) Copy the information from the data page. (The phrase "data page" is a link! Go back to the online version of these instructions and click on the link.) On the Properties sheet paste what you just copied. IF you are using Chrome browser and you have trouble pasting the data, please try a different browser.
    2. (0.5 pt) Widen the columns so you can see all the data.
    3. Turn on the automatic filters and filter on the following criteria for the whole sheet, not just one column
      • (2 pts) Use number filter on Sales Price for Less than or equal to the amount on the Amortization sheet in cell for the Loan Amount - this is one time where you can't refer to cells, type in the amount.
        (Do not uncheck all the numbers less than amount - work smart and use the number filter!)
      • (2 pts) Use number filter on Square Feet >=1500 AND <=5000 (between)
      • (1 pt) Area = Honolulu (Use checkboxes)
    4. SAVE
  6. (2 pt) On the Loan Comparison sheet, insert the image CCC-logo.gif.
    1. Drag the image so the upper left corner is in cell E1 and proportionally size the image so that it only covers 5 rows.
  7. (0.5 pt) Format cell A1 on all sheets (except the last sheet) with the text color of your choice. (Your choice can not be black.)
  8. (1 pt) Add a new sheet after the properties sheet. Name the sheet Function.
  9. Find one function in the financial category that we haven't covered. (Office for Mac doesn't display functions by category. See Microsoft's list of Financial Functions) Use this formula on the sheet just added - follow these steps:
    1. (2 pts) Determine what parameters are needed for this formula (it's best if you select a function that has only a few required parameters). For the parameters, add data in cells and add labels for this data in adjacent cells.
    2. (3 pts) Enter the function and refer to the cells for the parameters.
    3. (2 pts) In the cell beneath the function explain in your own words what the function does.
  10. An additional 2 points will be taken off if the document does not look professional: all numbers are formatted appropriately ($, %, or general) and all columns are wide enough to see the contents of all cells in that column. )

Submit the file on Laulima for Software Assignment - Excel 2 in Assignments,Tests & Surveys section.

Need Help?
Contact the instructor
via UH email
Please attach your file to the email.