Docstoc

Monthly Account Letters Job Aid.docx - Research Administration

Document Sample
Monthly Account Letters Job Aid.docx - Research Administration Powered By Docstoc
					Job Aid: Processing Monthly Sponsored Account Letters
Updated: 12/30/11


Table of Contents

Purpose
AMT Student Procedures
   Obtain Data Files
   Modify Data File
   Create .CSV file
   Email Letters Using Mail Merge
AMT Assistant Director Procedures


Purpose

Every month, the student workers on the Award Management Team (AMT Student) send letters via e-
mail to Principal Investigators (PI), Department Administrative Contacts (BOM), Department Chairs, and
Deans notifying them on the status of their sponsored accounts. These notices assist Units in complying
with ASU Policy RSP 509-04.

   60 Day E-mails – Sent to PI and BOM approximately 60 days before the obligated spending period on
    a sponsored account so they can begin preparing the account for closeout or submit requests to
    extend account.
   Project Obligated Period has Ended E-mails – Sent to PI and BOM when the obligated end date on a
    sponsored account has passed so they can prioritize account closeout.
   Notification of Account Deficit E-mails – Sent to PI, BOM, Chair and Dean when the total
    expenditures on a sponsored account exceed its budget by $300.

Data from the monthly Cash Deficit Report is used to prepare the e-mails. The process begins when the
Cash Management Team notifies the AMT Student the Cash Deficit Report data is available.


AMT Student Procedures

Obtain Data Files

1. Create new sub-folders in each of the following locations. The new sub-folders should be labeled
   with the current month & year.
    ACCTG\Award Monitoring and Change\Account Notification Letters\60 Day Letter
    ACCTG\Award Monitoring and Change\Account Notification Letters\Project Period Ended Letter
    ACCTG\Award Monitoring and Change\Account Notification Letters\Overdraft Letter
2. Locate the latest Cash Deficit Report saved in SharePoint folder ACCTG\Management
   Reports\Weekly Cash Management. Folders are arrange by fiscal year then by date so open the
   latest fiscal year folder followed by the folder with the most recent date.
3. Copy and paste the Date Excel file (i.e. 11 07 11) into each of the sub-folders created in step 1.
   4. Rename the Date Excel File within each sub-folder by adding the following title to the beginning of
      the file name (before the date).
       60-Day Letter: 60Days_
       Project Obligated Period has Ended Letter: PeriodEnded_
       Overdraft Letter: OverdraftData_
5.    Within each of the subfolders, open the Excel file and delete all the tabs with the following
      exceptions. Save changes and close Excel file.
       60 Day and Period Ended files: Retain “Download” tab
       Overdraft file: Retain “Working Copy” tab

 Modify Data File

 1. Modify the Excel file saved in the letter sub-folder.
     60-Day and Period Ended Letters
        1. Unhide any columns/rows and remove any filters.
        2. Sort the “EndDate” field by Oldest to Newest.
        3. Delete rows.
              60 Day Letters – Delete rows which have an end date before and after the next month.
                 The objective of this step is to modify the worksheet to only show accounts with an end
                 date that falls in the next month (EX: The current month is November 2011 so the list
                 should only show accounts with an end date in December 2011).
              Period Ended Letters - Delete the rows which have an end date” before and after the
                 previous month. The objective of this step is to modify the worksheet to only show
                 accounts with an end date that falls in the previous month (EX: The current month is
                 November 2011 so the list should only show accounts with an end date in October
                 2011).
        4. Proceed to step 2.
     Overdraft Letters
        1. On the Working Copy tab, unhide any columns/rows and remove any filters.
        2. Sort the “Overdraft” field by Largest to Smallest.
        3. Delete the rows which have an Overdraft balance greater than $300.
        4. Update the Overdraft Letter Tally Sheet (located in SharePoint folder \Award Monitoring
             and Change\Account Notification Letters\Overdraft Letter).
        5. Proceed to step 2.
 2. Sort by the “Account” field and delete any rows with a blank “Account” field.
 3. Delete the following columns: Sponsor Award #, Final Expiration Date, Status, Accountant, PI Dept,
    PI College, Basis of Payment, Method of Payment, and all the columns showing dollar amounts.
 4. Modify column headings.
     Delete spaces between multiple-word headings
     Change “Unit Head” to “ChairName”
     Change “Unit Head E-mail” to “ChairEmail”
     Change “Other Dept Contact” to “BOMName”
     Change “Other Department Contact Email” to “BOMEmail”
     Change “SPO Name” to “GCO”
     Change “SPO Email” to GCOEmail”
 5. Create a new column next to “GCOEmail” and label the heading as “GCOPhone”.
6. Sort by GCO Name and type in the GCO’s phone number in the “GCOPhone” column. Use format
   (###) ###-#### and copy and paste can be used for GCOs with multiple lines.
7. Revise file with the following exceptions. All other exception requests need to be approved by AMT
   AD first.
     Letter          Exception Description
     Overdraft       In Dean column, replace Elizabeth Capaldi with Robyne Clark
                     Robyne.Clark@asu.edu
     Overdraft       In Dean column, replace Sayfe Kiaei and Edward Hall with Stacy Esposito
                     Stacy.Esposito@asu.edu
     Overdraft       In Dean column, replace Sid Bacon and Robert Page with Terrie Lee Ekin
                     ekin@asu.edu
     Overdraft       Delete all references to Michael Crow

8. Review the data to make sure there is either a PI e-mail OR a BOM e-mail for every account.
9. Save Excel file.
10. For Overdraft Letters only
      1.   Send e-mail to the AMT GCOs, using the GCO Overdraft Notification Email Template saved in
           the SharePoint Overdraft Letter folder.
      2.   AMT AD will notify AMT Student when overdraft list has been approved. Approval from
           AMT AD is required before proceeding to following steps.

Create .CSV file

1.   Open the “Save As” window within the Excel File.
2.   Change the “Save As Type” drop-down field to the .CSV (MS-DOS) type.
3.   Click Save. Click “Yes” when the message “file name already exist” appears.
4.   Click Ok/Yes for every prompt after that.
5.   Close file. More prompts may appear upon closing so click Ok/Yes for each of them.
6.   Verify the .CSV file (icon has “a” in bottom right-hand corner) is saved in the appropriate SharePoint
     folder.

Email Letters Using Mail Merge

1. Open the appropriate letter templates saved in the following SharePoint locations.
    60-Day Letters: ACCTG\Award Monitoring and Change\Account Notification Letters\60 Day
       Letter
    Period Ended Letters: ACCTG\Award Monitoring and Change\Account Notification
       Letters\Project Period Ended Letter
    Overdraft Letters: ACCTG\Award Monitoring and Change\Account Notification Letters\Overdraft
       Letter
2. On the letter, type in the date you are sending the letter but don’t save changes.
    For Overdraft Letters, type in Month and Year
3. Complete the Easy Mail Merge for each letter.
   1. Open the Easy Mail Merge application in Outlook and choose the “Start Session” option.
   2. Click the External CSV File option and click Next.
   3. Browse for the appropriate .CSV file.
   4. Click the checkbox under “Browse” labeled “Use the first line of the file to compose the field
       names of the other lines”. Click Next.
   5. Depending on the group (BOM, PI, Chair, or Dean) receiving the letters, choose the appropriate
       email option from the “Use e-mail addresses” drop-down menu.
        For letters to BOM choose “BOMEmail” option
        For letters to PI choose “PIEmail” option
        For letters to Chair choose “ChairEmail” option
        For letters to Dean choose “DeanEmail” option
   6. Click the checkbox labeled “Allow duplicate email addresses” and click Next.
   7. Choose “Yes” to warnings.
   8. Erase signature from e-mail body.
   9. In the “From” field, search for the Awards.Management@asu.edu email address in the Outlook
       Exchange Directory and click OK to add.
   10. In the “Subject: field type in the following comments for the appropriate letter:
        60-Day Letters: “Project Period Ending in 60 Days”
        Period Ended Letters: “Project Obligated Period has Ended”
        Overdraft Letters: “Notification of Account Deficit”
   11. Copy and paste appropriate MS Word letter template into the body of the e-mail. Close Word
       letter template without saving changes.
   12. Within the body of the e-mail, highlight the blue sections one at a time (with the exception of
       the Date fields), mail merge with the correct fields, and select “Insert”.
   13. Click on the “Preview” button to verify the following items.
        Number of e-mails that will be generated.
        Letter is correctly formatted with appropriate merge fields.
        Open account in Coeus and with every 10th letter verify the PI, GCO, and BOM information
            coincides with the information on the Investigator tab.
   14. Click the Send option in the mail merge ribbon and choose the following options before clicking
       OK.
        Send Emails = Now
        How emails will be sent = Send 1 emails every 5 seconds
4. Repeat process until all letters have been sent to each person in the appropriate group
         60 Day and Period Ended Letters – Send to BOM and PI
         Overdraft Letters – Send to BOM, PI, Chair, and Dean
5. Notify the AMT GCOs that all account notifications have been sent.
6. From your Outlook Sent File, drag and drop the e-mails sent to the PIs in the appropriate SharePoint
   letter sub-folder.


AMT Assistant Director Procedures

1. Review exception requests received from GCOs against OverdraftData file. A one-time exception
   per account will be granted for actions in the AMT GCOs queue. (EX: AMT GCO requested an
   exception for account ABC1234 to not include on the deficit list in November so they can process a
   deficit transfer. If the same account appears on the deficit list the following month another
   exception will not be granted.)
    No exceptions will be granted for accounts waiting on incremental funding as the account can
       be put on At-Risk status while waiting for the additional funding.
2. Revise OverdraftData file to remove accounts with approved exceptions.
3. Update the Overdraft Letter Tally Sheet (located in SharePoint folder ACCTG\Award Monitoring and
   Change\Account Notification Letters\Overdraft Letter).
4. Notify AMT Student that list has been approved and it’s OK to send the Overdraft Letters.
5. Review Overdraft Letter Tally Sheet and notify RAs of accounts ready to receive their 3rd consecutive
   notification or accounts that will have deficits transferred to IIA or RID (See email template: Red
   Alert Deficit Accounts).
    RAs will be given 1 courtesy notification per account.
    RAs will be given till the end of the current month to clear up deficits.
    Deficit transfers should be processed for accounts that show up on the next month’s deficit list.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:23
posted:2/24/2012
language:English
pages:5