Department of Technology

TECH 394 - Tech Computer Applications

Summer 2004

E-mail Me

Syllabus

Schedule

Handouts

Assignments

All My Webs

Dept Home

 

Integration Assignment

Purpose:  This assignment is intended to demonstrate your ability to move selected data from one application to another, using OLE, import/export tools, or mail merge.

Your boss at the PinchPenny Savings Bank has been keeping her records on a spreadsheet, and typing individual letters to customers based on her calculations.   Her policy is to offer loans to customers if the total payment they would make is less than or equal to 32% of their gross monthly income.  She has recently added a new feature - onloan applications. The customers enter information on a web form, and their information is automatically recorded in a comma delimited text file. That file, PinchPenny.txt, contains the names, addresses, annual incomes, loan payment requested, and current debt payments for several customers.  A word file, PinchPenny.doc, shows a typical letter sent to customers.  Ms. Cash wants you to automate a process for writing the letters.

This project is worth 20 points. To earn a score of 14 (70%) on this project, you must:

  • Import the text file into a spreadsheet.
  • Add a column to determine which of the loans to award. Use and If( ) function to accomplish this.
  • Sort the data so that those whose loans will be accepted will appear above any that will be rejected.
  • Name the range containing data for loans to accept, including the labels, "Accept_Data".
  • Name the range including data for all loans, including the labels, "Merge_Data".
  • Add your name in a cell below any imported data.
  • Save the spreadsheet on your T-drive folder with the name PinchPenny.xls.

To earn a score of 16 (80%) on this project, your must also:

  • Link your name from the cell on the spreadsheet to the name of the loan officer in the letter.

  • Make the date on the letter update automatically when the document is opened,

To earn a score of 18 (90%) on this project, you must:

  • Add merge codes to the letter as appropriate.

  • Merge the data from Accept_Data to create letters for each accepted loan. Save this to your T-drive folder as "Accepted.doc"

To earn a score of 20 (100%) on this project, you must:

  • Add text to two cells on the spreadsheet to inform the applicant if their loan has been accepted. One might read, "Based on your information, we are pleased to offer you a loan at this time." The other might say, "Based on your information, we are sorry to have to deny your loan at this time." Include this text in the If( ) function that determines who gets a loan.

  • .Merge the data from Merge_Data to generate letters to all customers. Save this in your T-drive folder with the name "AllLetters.doc.

Do not merge your files to the printer.  E-mail me telling me that the files are in your folder, or attach all four files:

  • PinchPenny.xls
  • PinchPenny.doc - your modified merge file
  • Accepted.doc - the merged file containing letters for those whose loans have been accepted
  • AllLetters.doc

I MUST be able to find all four file in order for you to receive full credit!