Internship Evaluation Form

WORKBOOK ARCHITECTURE & TAB BREAKDOWN

The Internship Evaluation & Performance Management Framework is structured as a multi-tier, relational financial and operational evaluation model. It is engineered to capture granular intern performance data, normalize qualitative managerial inputs, execute compensation/return-offer modeling, and generate institutional-grade reporting dashboards.

[DATA: Master Roster] ---> (XLOOKUP / FILTER) ---> [CALC: Scoring Engine]
                                                        │
        ┌───────────────────────────────────────────────┴───────────────────────────────┐
        ▼                                                                               ▼
[RPT: Executive Dashboard]                                                    [AUDIT: HR Compliance]

Tab Breakdown

1. 01_Executive_Dashboard
   • Purpose: High-level executive reporting layer for the University Relations and HR Steering Committee.
   • Data Flow: Pulls aggregated metrics, division-level performance averages, and conversion status via dynamic array formulas (FILTER, UNIQUE, SUMIFS, AVERAGEIFS) from the calculation engines.
2. 02_Master_Evaluations
   • Purpose: Primary data intake and operational ledger. Stores individual intern metadata, tracked competencies, project deliverables, and raw evaluator scores.
   • Data Flow: Serves as the single source of truth (SSOT). Receives direct user input and data validation constraints; feeds downstream transformation sheets.
3. 03_Scoring_Engine
   • Purpose: Normalizes raw manager evaluations using weighted rubrics (Technical Competency, Execution, Cultural Alignment, Leadership Potential) to generate a composite score out of 100.
   • Data Flow: Uses row-by-row matrix multiplication (SUMPRODUCT) against dynamic weight parameters stored in the control panel.
4. 04_Comp_Return_Model
   • Purpose: Deterministic financial modeling sheet that calculates return-offer tiers, signing bonus allocations, and annualized entry-level starting salary projections based on performance thresholds.
   • Data Flow: References composite scores from 03_Scoring_Engine to lookup compensation brackets via XLOOKUP.
5. 05_Data_Dictionary
   • Purpose: Reference control sheet containing valid dropdown lists, evaluation rubrics, scalar weightings, and system configuration tables.
   • Data Flow: Acts as the reference source for all Data Validation rules across input tabs.

MASTER DATA TABLE (MARKDOWN FORMAT)

The table below represents the core data stored in sheet 02_Master_Evaluations, populated with 15 institutional records.

EXPLICIT FORMULA GLOSSARY

The following formulas represent the production-grade calculations utilized across the calculation and reporting engines of the workbook:

1. Composite Score Calculation (03_Scoring_Engine cell H2): =SUMPRODUCT(H2:J2, $D$1:$F$1) / SUM($D$1:$F$1) Purpose: Multiplies individual raw scores (Technical, Execution, Soft Skills) by their absolute weighted scalar values retrieved from the control panel, establishing a normalized composite score out of 100.

2. Return Offer Allocation Lookups (04_Comp_Return_Model cell D2): =IFERROR(XLOOKUP(C2, '05_Data_Dictionary'!$A$10:$A$13, '05_Data_Dictionary'!$B$10:$B$13, "Not Eligible", 0), "Error") Purpose: Evaluates the composite score against tiered benchmark ranges to automatically assign compensation grades, preventing systemic lookup errors.

3. Division-Level Average Performance (01_Executive_Dashboard cell B5): =AVERAGEIF('02_Master_Evaluations'!$C$2:$C$100, A5, '03_Scoring_Engine'!$H$2:$H$100) Purpose: Calculates divisional performance means by matching department strings against the evaluation matrix.

4. Return Offer Conversion Rate (01_Executive_Dashboard cell F5): =COUNTIF('02_Master_Evaluations'!$M$2:$M$100, "Extended") / COUNTA('02_Master_Evaluations'!$A$2:$A$100) Purpose: Determines organizational conversion efficiency by dividing total extended offers by total active cohort headcounts.

5. Data Integrity & Duplicate Prevention (02_Master_Evaluations cell A2 validation rule): =AND(ISNUMBER(VALUE(RIGHT(A2, 3))), ISERROR(MATCH(A2, A1:A$1, 0))) Purpose: Ensures unique ID structure and blocks duplicate entries during operational batch-loading.

DATA VALIDATION & CONDITIONAL FORMATTING RULES

Data Validation Rules

• Column Division (02_Master_Evaluations!C2:C500)
  • Validation Criteria: List
  • Source: ='05_Data_Dictionary'!$A$2:$A$7 (Values: Investment Banking, Quantitative Research, Risk Management, Private Equity, Global Markets, Wealth Management, Technology & Operations)
• Column Return_Offer_Status (02_Master_Evaluations!M2:M500)
  • Validation Criteria: List
  • Source: ='05_Data_Dictionary'!$C$2:$C$4 (Values: Extended, Pending Review, No Offer)
• Column Attendance_Pct (02_Master_Evaluations!L2:L500)
  • Validation Criteria: Decimal between 0 and 1 (Error Alert: "Must be a valid decimal percentage between 0% and 100%.")

Conditional Formatting Rules

• Rule 1: High-Performer Callout (Composite Score >= 90.0)
  • Target Range: 03_Scoring_Engine!H2:H500
  • Condition: Formula Is =H2>=90
  • Formatting Style: Light Emerald Green Fill (#D1E7DD), Dark Green Text (#0F5132), Bold Font.
• Rule 2: At-Risk Intern Alert (Composite Score < 80.0 OR Attendance < 0.90)
  • Target Range: 02_Master_Evaluations!A2:M500 (Entire Row Highlight)
  • Condition: Formula Is =OR($H2<80, $L2<0.90)
  • Formatting Style: Soft Rose Fill (#F8D7DA), Dark Red Text (#842029).
• Rule 3: Pending Review Flag
  • Target Range: 02_Master_Evaluations!M2:M500
  • Condition: Cell Value Equal to "Pending Review"
  • Formatting Style: Amber/Gold Fill (#FFF3CD), Dark Yellow-Brown Text (#664D03).

EXECUTIVE SUMMARY KPI CARDS

The table below reflects the operational metrics dynamically aggregated on sheet 01_Executive_Dashboard.