Processing Monthly Sponsored Account Letters by 3B4Z110

VIEWS: 0 PAGES: 5

									Processing Monthly Sponsored Account Letters                                    June 1, 2012




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_

ASU – Research Operations                                                             1
     Processing Monthly Sponsored Account Letters                                       June 1, 2012


             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.

              Letter                     Exception Description
              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 less than $300.
              4. 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.



     ASU – Research Operations                                                                  2
Processing Monthly Sponsored Account Letters                                    June 1, 2012


         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                  In Dean column, copy entire rows which have the
                                    following names: Terrie Lee Ekin, Elizabeth
                                    Langland, and Patrick J Kenney and paste into the
                                    email Template to Terrie Lee Ekin. When sending
                                    email be sure to send from Awards.Management,
                                    and delete personal signature from bottom (if
                                    applicable).
         Overdraft                  Delete all references to Michael Crow
         60, End of Project &       For letters that show Teresa D Robinette in the
         Overdraft                  BOMName column, replace BOMEmail data with
                                    seserao@asu.edu


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.


ASU – Research Operations                                                               3
Processing Monthly Sponsored Account Letters                                      June 1, 2012


    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.

ASU – Research Operations                                                               4
Processing Monthly Sponsored Account Letters                                     June 1, 2012


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 3 rd
   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.




ASU – Research Operations                                                              5

								
To top