BPPE: Labor Market Identification Data Report Support Guide

Creation date: 9/21/2023 1:22 PM Updated: 9/21/2026 3:30 PM annual annual report bppe data guide labor labor market report support guide

BPPE Labor Market Identification Data Report Guide

This guide explains how to prepare and populate the BPPE Labor Market Identification Data Workbook using information from OnlineSMART reports.

Note:

This process utilizes three separate files:

BPPE Labor Market Identification Data Workbook
90-10 Calculation By Student Report
Student General Report Export

Keep all three files open throughout the process.

Important:

The Student General Report date range is determined by the cohort dates listed in the BPPE Workbook.

Review cells D6 and D7 on the Dictionary tab before running reports.

Part 1 - Prepare the BPPE Workbook
The BPPE workbook contains three tabs: Workbook Tabs Info Dictionary Data Before beginning, verify the school has completed all yellow highlighted fields on the: Info tab These values will be used later in the reporting process. Note: The cohort reporting dates used later in this guide are found on the Dictionary tab in cells: D6 D7
Part 2 - Generate Required Reports
Two reports must be generated before any spreadsheet work begins. Student General Report Navigate to: Reports → Query → Student → General Report Query students using: Actual Graduation Date Use the cohort dates found in: Dictionary!D6 Dictionary!D7 Export the report as: CSV 90-10 Calculation by Student Report Run the report using: Start Date: January 1st of 5 years prior Example: Reporting Year: 2023 Start Date: 1/1/2018 End Date: December 31st of the cohort year Export the report as: Excel
Part 3 - Prepare the 90-10 Workbook
Open the exported: 90-10 Calculation by Student workbook. Create FedLoanDebt Column Create a new column: Pre-2025: Insert a new column at Column S Post-2026: Insert a new column at Column W In the header row, enter: FedLoanDebt In the first data row, enter the appropriate formula: Pre-2025 Formula =H3+I3+J3+N3+O3+P3 Post-2026 Formula =H3+I3+J3+P3+R3+S3 After entering the formula: 1. Click out of the cell. 2. Click back into the cell. 3. Double-click the fill handle in the lower-right corner of the cell. Create General Report Sheet Create a new worksheet in the 90-10 workbook. Rename the worksheet: General Report
Part 4 - Build the General Report Sheet
Open the exported: Student General Report CSV Copy the following columns: D E AA AJ AQ BG Paste them into the: General Report worksheet that was created in Part 3. Prepare Name Columns Right-click Column B and select: Insert Repeat this process three times. Highlight Column A. Navigate to: Data → Text to Columns Select: Delimited Then click: Next In the Delimiters section: Comma = Checked Space = Checked Treat Consecutive Delimiters As One = Checked Click: Next Leave: General selected and click: Finish Rename: Cell A1 = Last Name Cell B1 = First Name Clean Imported Data Perform the following Find & Replace operations. Note: For every replacement below, include the space after the colon. Column G Find: Actual Grad: Replace With: (blank) Click: Replace All Column H Find: SSN: Replace With: (blank) Click: Replace All Column I Find: Course: Replace With: (blank) Click: Replace All Column J Find: Hours: Replace With: (blank) Click: Replace All
Part 5 - Build the BPPE Data Sheet
Create BPPE Data Sheet Create an additional worksheet within the 90-10 workbook. Copy cells A1 through H1 from the: Data tab of the BPPE workbook. Paste them into: Cell A1 of the new worksheet. Map General Report Data Copy the following information from the: General Report worksheet into the new BPPE worksheet. General Report Column → BPPE Data Column A (Last Name) → A B (First Name) → B H (SSN) → C G (Actual Grad Date) → D I (Course) → F J (Hours) → H After completion, the BPPE worksheet should contain: Last Name First Name SSN Actual Graduation Date Course Hours The remaining columns will be completed in Part 6.
Part 6 - Match Federal Loan Debt
Match Federal Loan Debt In the BPPE worksheet, click into: Cell E1 Enter the appropriate formula. Pre-2025 Formula =VLOOKUP('General Report'!F2,'SmartShared - 90-10 Calculation'!A:S,19,FALSE) Post-2026 Formula =VLOOKUP('General Report'!F2,'SmartShared - 90-10 Calculation'!A:W,21,FALSE) After entering the formula: 1. Click outside the cell. 2. Click back into the cell. 3. Double-click the fill handle in the bottom-right corner. Highlight all of Column E. Copy the column. Paste it back onto itself using: Paste Values Only Perform a Find & Replace: Find: #N/A Replace With: 0 Click: Replace All Validate Data Select Cell A1 in the BPPE worksheet. Enable Filters: Home → Sort & Filter → Filter Sort Column F using: Sort A to Z Review Column E (Federal Loan Debt) for invalid data. Values should: Be whole numbers Not contain negative values Populate the BPPE Workbook Copy columns A through H from the BPPE worksheet. Paste the values into: Cell A2 of the Data tab of the BPPE workbook. Configure the remaining fields: Column G (CredentialType) Typically: Diploma/Certificate Verify this value with the school before populating the column. Column I (LengthType) Typically: Clock Verify this value with the school before populating the column. Column J (SOCCode) Locate the correct SOC Code on the: Info tab of the BPPE workbook. Use Excel search to locate the appropriate code. Columns K and L Institution Code Institution Name These fields should populate automatically. Final Verification: Confirm all required rows have been populated, formulas have been converted to values, and no validation issues remain before submitting the workbook to the school. Submission Save the completed workbook in a location that is easily accessible to the school. The file is now ready for BPPE submission.
Completion Checklist
✓ BPPE Workbook Prepared ✓ Required Reports Generated ✓ 90-10 Workbook Prepared ✓ General Report Sheet Built ✓ BPPE Data Sheet Created ✓ Federal Loan Debt Calculated ✓ Data Validated ✓ BPPE Workbook Populated ✓ File Saved For School Submission