Award Load Overview

Overview

The Award Load process expedites the initial entry of awards for users who enter at least 20 awards per year. Instead of manually entering awards for one student at a time on the Award Entry/Manual pages, users with the FA AWARD LOAD USER role can load a spreadsheet with up to 300 awards for multiple students via a single process. This should decrease the initial award entry efforts for departments that enter multi-year awards annually, or who already record their award data on spreadsheets. This process does not replace Award Entry/Manual, which continues to be used to revise awards, including those entered via Award Load.

The Award Load process is capable of initially entering awards that have:

• only Action values of B (Offer/Accept) or O (Offer)
• either a manually specified amount or an amount calculated by M-Pathways to cover target charges based on the award’s Calc Flag value
• if desired, a different Charge Priority value than the default value for the Financial Aid Item Type (FAIT)

The Award Load process cannot be used to revise the amount of an existing award, nor can it be used to enter awards:

• with a custom split of their disbursement (i.e., awards with a Split Code of XX)
• with an Action of A (Accept), D (Decline), or C (Cancel)
• with a FAIT number that was previously awarded to the same student in the same target aid year
• for students who have more than one active Career

Contents

• Process Steps
• Award Load Spreadsheet
• Award Load Page
• Query Results
• Related Documentation

Process Steps

The Award Load process consists of three main tasks, which are to first record the award data on a spreadsheet, second to use the Award Load page in M-Pathways to import and pre-edit the award data, and third to validate and post the awards by clicking a single button. The end-to-end steps consist of:

Download the Award Load Spreadsheet Template:

1. Download the Award Load Spreadsheet Template from the Financial Aid Forms & Instructions website.

In your local system:

2. Record the award data for up to 300 awards on your copy of the Award Load Spreadsheet Template and save it to a local file location. Each department decides how to name and save their files so they can be administered internally.

In M-Pathways (Wolverine Access):

3. Navigate to the Award Load page in M-Pathways and add a new Run Control ID to access the page for the first time. After adding a Run Control ID, it is recommended that you re-use it each time you use the Award Load page.
   Note: Refer to Award Load for more information.
4. Set up the Run Control ID on the Award Load page by entering your Department ID and the Target Aid Year of the awards to be processed.
5. Begin to import your spreadsheet data by clicking the IMPORT FILE button on the Award Load page, browsing to your local file location, and then selecting the spreadsheet file.
6. Pre-edit the award data in M-Pathways by clicking the Upload button, which runs a process for up to two minutes depending on the file size. Then view the automatic query results to verify the correct IDs and Financial Aid Item Type numbers were imported, and view a list of Pre-Edit Results to identify formatting and validity errors in the award data. At this point, the award data is in a temporary staging table so any errors are not saved in M-Pathways.

In your local system:

7. If necessary, correct the spreadsheet file in your local file location to resolve Pre-Edit errors.

In M-Pathways:

8. If necessary, re-import/re-upload the spreadsheet data via the Award Load page in M-Pathways.
9. When the Pre-Edits Results are acceptable, click the AWARD ENTRY button on the Award Load page to validate and then post the successfully validated awards. Awards that do not pass validation are not posted. Expect approximately one minute per ten awards for this process.
10. Review the results of the Award Entry process on the Award Load page via two automatic queries. The “Award Entry Successes” query lists the awards that were posted while the “Pre-Edit & Award Entry Failures” query lists the awards that did not pass validation.
11. If there were awards that could not be posted because they did not pass validation, export the results of the “Pre-Edit & Award Entry Failures” query to Excel, save an electronic copy of the results, and send them as an email attachment to sfps@umich.edu to be resolved by the Office of Financial Aid.
12. Run the UM Award Activity by OperID report to verify the awards were posted as expected.

 

Award Load Spreadsheet

Spreadsheet File Options

The Award Load Spreadsheet template includes cell validation to prevent invalid data entry. Although it does not restrict the number of awards, adding more than 300 awards is not recommended. The two options for adding your award data to the spreadsheet are to:

1. Enter your department’s award data in the Award Load Spreadsheet Template, which prevents most types of invalid data entry with pre-formatted cell validation.
2. Copy the award data from an existing spreadsheet in which you already record and maintain your department’s awards, and then paste the data into the Award Load Spreadsheet Template. You need to use the template to check for invalid values using the Formula Auditing Tool.

Data Requirements

The first row of the Award Load Spreadsheet Template identifies the type of award data for each column:

Important: To prevent the first row from being processed as a row of award data, the cells in the first row of the template have a number format of Text. This is different than some of the cells for the award data, which are detailed in the following table.

Value	Required?	Number Format	Data Requirements
EmplID	Yes	Number - no decimals	Format: 8-digit number, except for IDs that begin with 0 (zero). IDs have their leading zeros dropped from the spreadsheet, but they are restored when imported into M-Pathways. Validity: ID must exist for the student in M-Pathways student must have an FA Term built in the Target Aid Year student cannot have more than one active Career in the Target Aid Year
Aid Year	Yes	Number - no decimals	Format: 4-digit number Validity: must match the Aid Year on the Award Load page must be an active Aid Year for the student
Award Period	Yes	Text	Format: 1 capital character Valid values: A (Academic) N (Non-Standard) B (Both)
Action	Yes	Text	Format: 1 capital character Valid values: B (Offer/Accept) O (Offer)
Item Type	Yes	Number - no decimals	Format: 12-digit number Validity: must be active for Target Aid Year cannot already be awarded to student in M-Pathways for Target Aid Year cannot be awarded to same student more than once in spreadsheet file
Disb Plan	Yes	Text	Format: 2 characters Validity: must be valid for student’s Academic Career when a Calc Flag value is provided for the same award row, the Disb Plan must be 01 (per Term)
Split Code	Yes	Text	Format: 2-digit number Validity: must be valid for student’s Academic Career and the Disb Plan provided for the same award row when a Calc Flag value is provided for the same award row, Split Code must be 01 (Fall, Winter), 02 (Fall Only), or 03 (Winter Only)
Calc Flag	No	Text	Format: 2-characters in all caps Validity: required only when Award Amount not provided must be valid for student’s Academic Career and the Disb Plan provided for the same award row the calculated amounts assume full-time enrollment must be one of the 19 valid values: HF 50% (Tuition + Reg + Mand Fees) HM 50% (Tuition and Mandatory Fees) HO 50% (Tuition Only) HR 50% (Tuition and Reg Fees) MO (Mandatory Fees Only) QF (25% Tuition + Reg + Mand Fees) QM (25% Tuition and Mandatory Fees) QO (25% Tuition Only) QR (25% Tuition and Reg Fees) RM (Registration and Mandatory Fee) RO (Registration Fees Only) SF (75% Tuition + Reg + Mand Fees) SM (75% Tuition and Mandatory Fees) SO (75% Tuition Only) SR (75% Tuition and Reg Fees) TF (100% Tuitn + Reg + Mand Fees) TM (100% Tuition and Mandatry Fees) TO (100% Tuition Only) TR (100% Tuition and Reg Fees)
Award Amount	No	Number - no decimals	Format: must be a positive number no greater than 99,999.99 Validity: required only when Calc Flag not provided
Charge Priority	No	Text	Format: 6-8 characters in all caps Validity: Do not provide for Non-Refundable Item Types, which includes all tuition valued awards. Only provide to change the default values for Refundable Item Types. Must be a valid value. Lists of the valid values are available on Financial Aid Item Types - Charge Priority Instructions on the Financial Aid Forms & Instructions website.

Using the Award Load Spreadsheet Template

When you download an electronic copy of the Award Load Spreadsheet Template, each column and the first row of headings are formatted with the required number formats. Additionally, the template includes cell validation to help prevent inaccurate data entry. This restricts the values that may be entered in the template, which varies for each column.

Important: IDs that begin with 0 (zero) have the leading zeroes dropped from the spreadsheet because it must be formatted as a number, not text. The leading zeroes are restored during the Award Load process when the data is imported into M-Pathways.

Cell Descriptions:

To display a description of the cell’s data requirements, select the heading cell of that column.

Drop-Down Lists:

Some columns in the template have drop-down lists for selecting the valid values. This is a useful reminder that the Award Load process does not accept the lower case equivalents of specific values.

Cell Validation When Typing Award Data:

If you type a value that does not meet the cell’s validation requirements, click the Retry button to enter the value differently, or click the Cancel button to proceed without completing the cell.

Numbers Formatted as Text:

Only the Disb Plan and Split Code values should have numbers formatted as text, as indicated by the Error Indicator in the upper-left corner of the cells. Do not convert those values to numbers.

Pasting Data – Match Required Cell Formatting:

1. Select the cell(s) in which you need to paste data.
2. Right-click the cell(s) and click the Values icon in the Paste Options section of the right-click menu.
   
   • When you roll over the Paste Special icons, the rest of the right-click menu grays out.

Pasting Data – Convert EmplIDs from Text to Number:

If you paste EmplDs that you copied from a query or report generated in M-Pathways, the EmplID may have been stored as a text value. In that case:

1. Select the cell(s).
2. Click the Warning drop-down list.
3. Select Convert to Number.

Pasting Data – Circle Invalid Values:

The template’s cell validation does not restrict invalid values from being pasted into the template. To circle the values that are invalid based on the cell validation:

1. On the Data tab, click the Data Validation button, then select Circle Invalid Data.

2. Except for the column headings, manually correct the circled values on the spreadsheet.

Updating the Number Format of Cells:

It may be necessary to update the number format of the Award Load Spreadsheet Template if the number formatting was changed when pasting data.

1. For each column:

1. Highlight the column
2. Right-click the highlighted column and select Format Cells, or select Cells from the Format menu.
3. Select the applicable number format in the Category list.
4. If necessary, update the number of decimals in the Decimal places field.
5. Click OK.

2. For the first row with column headings in the template only, highlight the first row and update the number format of that row to Text.

Award Load Page

The following descriptions and screenshots explain how and when the buttons and links on the Award Load page are activated after each step in the Award Load process.

Letter	Field/Button	Description
A	Step 1 - Set Up Run Control Values	This example of the Award Load page was accessed with a new Run Control ID. Only the IMPORT FILE button under Step 2 is active when the page first displays. The Department and Target Aid Year fields are required to set up the Award Load process because: The Target Aid Year needs to match the awards on the spreadsheet because the process can only load awards for one Aid Year. The Department ID enables the fields on this page to be used by a departmental user. No additional buttons/links are activated by completing these fields. If you click the IMPORT FILE button before completing them, error messages prevent you from proceeding.

The next two examples of the Award Load page explain how to complete Step 2 – Import File and Review Pre-Edit Results with a Run Control ID that was already used to complete the Award Load process for a different spreadsheet of awards.

Letter	Field/Button	Description
C	Begin Step 2 - Import File and Pre-Edit Results	To begin Step 2, click the IMPORT FILE button, browse to the file location, select the spreadsheet file, and upload it. As it is uploaded, the pre-edit process checks for file format errors and for valid values.
B	Links and Pre-Edits Results	Before completing Step 2, the Award Entry Successes and Pre-Edit & Award Entry Failures links and the Pre-Edits Results list under Step 4 reflect the results of the previous run of the process.

Letter	Field/Button	Description
D	Complete Step 2 - Import File and Pre-Edit Results	To complete Step 2: Immediately verify the Row Count field displays the total number of rows imported from the file because it disappears upon clicking the Import Results link. Click the newly activated Import Results link to open a new browser window that lists each award row that displays the student’s name and Item Type description for each award row to verify the correct EmplIDs and Item Type numbers were in the spreadsheet. If errors result from the pre-editing, they display in a Pre-Edit Results list under Step 4. In many cases the errors can be immediately corrected on the spreadsheet, then repeat Step 2 to update the Pre-Edits Results list without exiting the Award Load page. Note: Once a file is successfully imported with or without Pre-Edit errors, the AWARD ENTRY button under Step 3 is activated so that you can enter the awards without Pre-Edit errors.
E	Links and Pre-Edits Results under Step 4	When Step 2 is completed: Only the Pre-Edits Results list is updated with pre-edit errors from the new file, or it is cleared when there are no errors. The the Award Entry Successes and Pre-Edit & Award Entry Failures links under Step 4 will not be updated until Step 4 is completed.

This is an example of the Award Load page after the AWARD ENTRY button was clicked under Step 3, and after the REFRESH button was clicked under Step 4.

Letter	Field/Button	Description
F	Step 3 - Run Award Entry	Clicking the AWARD ENTRY button runs the validation process for each award that passed the pre-edits, and then it posts the awards that passed validation. This is similar to clicking the both the Validate and Post buttons on Award Entry/Manual, except that the Award Load process does not post awards that failed validation.
G	Step 4 - Review Award Entry Successes and Failures	After completing Step 3, the Award Entry processing may take a few minutes to complete. The REFRESH button can be clicked occasionally until a message indicates the processing status is Success, at which time the two links are updated to provide the results via automated queries: The Award Entry Successes query lists the awards that were posted. The Pre-Edit and Award Entry Failures query lists the awards that did not pass validation, including those with unresolved pre-edit errors.
H	Pre-Edit Results	Errors that are not resolved/cleared from the Pre-Edit Results list are not processed during Step 3. They will remain on the list until another spreadsheet is imported, which has no impact because the lists reflects the data from a temporary staging table.

Query Results

The three queries linked from the Award Load page automatically return different results from the Award Load process. The purpose of each query, and the values that are unique to each, are described next.

Import Results

This query is activated when the award data is imported in Step 2 on the Award Load page. It lists a row for each award so you can verify the IDs and Item Type numbers were recorded correctly in the spreadsheet. If the award row resulted in a pre-edit error when imported, the error message is provided.

Letter	Field/Button	Description
A	Seq	Displays the number of the award row in the Award Load Spreadsheet.
B	Charge Priority	Displays the Charge Priority provided on the Award Load Spreadsheet, if applicable.
C	Message	If the award row resulted in a pre-edit error when the file was imported, it displays the error message.

Award Entry Successes

This query is activated or updated when the Award Entry process is completed successfully in Step 4 on the Award Load page. It lists a row for each successfully posted award.

Letter	Field/Button	Description
A	Seq	Displays the number of the award row in the Award Load Spreadsheet.
B	Date/Time	Displays the date at which the Award Entry processing completed.

Pre-Edit & Award Entry Failures

This query is activated or updated when the Award Entry process is completed in Step 4 on the Award Load page. It lists awards that failed validation, including awards with unresolved pre-edit errors.

When an award(s) has a validation error, click the Excel SpreadSheet link, save a copy of the query results as an Excel spreadsheet in your local file system, and send an electronic copy of the query results as an email attachment to sfps@umich.edu so the error can be resolved.

Letter	Field/Button	Description
A	Seq	Displays the number of the award row in the Award Load Spreadsheet.
B	Item Type	Displays the Item Type number of the award you uploaded. If the validation process determined the award you uploaded would result in a validation error for a different award in the student’s aid package, the Item Type Number of the award that would have been impacted is identified in the Message column.
C	Status	If the failure was due to a pre-edit error, displays P. If the award failed validation, displays V.
D	Message	Identifies the Item Type number and error message for the award that resulted in a validation error.

Related Documentation

• In addition to the Award Load Spreadsheet Template, there are a variety of instructions, forms, and calendars related to the award entry process on the Financial Aid Forms & Instructions website:
  • Creating, Revising, and Inactivating FAIT Instructions – This document provides comprehensive instructions for understanding the different aspects and requirements of FAITs.
  • Merit-Aid Values for FAITs – This document provides condensed lists and descriptions for quickly referencing the values most applicable to the FAITs used by merit-aid staff. This includes Charge Priorities, Disbursement Plans, Split Codes, and Disbursement Rules.
  • Aid Disbursement Calendars – Calendars that detail the award entry deadlines and associated funds delivery dates for each Disbursement Plan.
  • Exceptions to Disbursement – All the instructions and forms for requesting exceptions to disbursement.
• Award Load Process
• Enter an Award
• Cancel, Decline, or Revise an Award
• Report Manager Inventory for Financial Aid
• Windows PC Users: Run a Report to a Window
• Mac Users: Run a Report to the Report Manager