Curriculum Development Evaluation

CURRICULUM DEVELOPMENT EVALUATION: INSTITUTIONAL FINANCIAL & OPERATIONS FRAMEWORK

Author: Senior Financial Architect & Operations Lead
Version: 4.2-PROD
Target Audience: Chief Academic Officer, Director of Curriculum Operations, FP&A Directorate

---

SECTION 1: WORKBOOK ARCHITECTURE & TAB BREAKDOWN

This institutional financial model is engineered to govern the lifecycle, capital expenditure, operational costing, and projected economic return of enterprise curriculum development.

[01_MASTER_DATA] ---> [02_CAPEX_OPEX_ENGINE] ---> [03_ROI_MODEL]
         ^
         |
[04_LOOKUP_PARAMS] (Static Validation Sources)
         |
[05_EXECUTIVE_SUMMARY] (Dashboard & KPI Cards)

Tab 1: 01_MASTER_DATA

• Purpose: The foundational database tracking every curriculum module/course under development.
• Schema: Captures structural metadata, academic ownership, delivery modalities, baseline hours, and direct financial allocations.
• Data Flow: Feeds all downstream calculation engines, cost roll-ups, and variance dashboards.

Tab 2: 02_CAPEX_OPEX_ENGINE

• Purpose: Granular cost-accounting engine separating Capital Expenditure (content creation, software licensing, platform build) from Operating Expenditure (instructional delivery, maintenance, server hosting).
• Schema: Time-phased financial allocation mapped across Fiscal Quarters (Q1–Q4) with contingency buffers and inflation adjustments.
• Data Flow: Pulls module identifiers from 01_MASTER_DATA via =XLOOKUP() and outputs total lifecycle costs to 03_ROI_MODEL.

Tab 3: 03_ROI_MODEL

• Purpose: Financial return engine calculating Net Present Value (NPV), Internal Rate of Return (IRR), Payback Period (Months), and Student-Lifetime-Value (LTV) to Customer-Acquisition-Cost (CAC) ratios.
• Schema: Revenue projections based on enrollment velocity, tuition models, and direct cost-of-goods-sold (COGS) offsets.
• Data Flow: Consumes total cost vectors from 02_CAPEX_OPEX_ENGINE and enrollment metrics from 01_MASTER_DATA.

Tab 4: 04_LOOKUP_PARAMS

• Purpose: Referential data integrity sheet housing static validation lists, academic departments, delivery modalities, faculty pay scales, and discount rates.
• Schema: Single-column and matrix reference tables used strictly for data validation and dynamic lookup arrays.
• Data Flow: Serves as the source array for all drop-down validations across the workbook.

Tab 5: 05_EXECUTIVE_SUMMARY

• Purpose: High-level C-Suite dashboard featuring KPI cards, variance analyses against budgetary baselines, and portfolio health matrices.
• Schema: Aggregated metric blocks utilizing conditional formatting and dynamic text callouts.
• Data Flow: Aggregates metrics dynamically from 01_MASTER_DATA, 02_CAPEX_OPEX_ENGINE, and 03_ROI_MODEL.

---

SECTION 2: MASTER DATA TABLE

Note: Monetary values are denominated in USD ($).

---

SECTION 3: EXPLICIT FORMULA GLOSSARY

The following production-grade formulas are deployed across the workbook structure:

1. Total Portfolio Expenditure (02_CAPEX_OPEX_ENGINE) =SUM(C5:C100) + SUM(D5:D100)

   • Explanation: Aggregates all direct Capex and Opex line items across the entire curriculum database to calculate total capital allocation.

2. Conditional Department Budget Average (05_EXECUTIVE_SUMMARY) =AVERAGEIF('01_MASTER_DATA'!$C$5:$C$100, $A5, '01_MASTER_DATA'!$I$5:$I$100)

   • Explanation: Calculates the mean capital expenditure (Column I) specifically for the academic department defined in cell $A5 (e.g., "FinTech").

3. Dynamic Module Lead Extraction (02_CAPEX_OPEX_ENGINE) =XLOOKUP($B5, '01_MASTER_DATA'!$A$5:$A$100, '01_MASTER_DATA'!$G$5:$G$100, "Unassigned", 0)

   • Explanation: Searches for the Module ID in cell $B5 within the Master Data table, returning the designated Development Lead (G), with error-handling fallback text.

4. Error-Safe Return on Investment Calculation (03_ROI_MODEL) =IFERROR((K5 - (I5 + J5)) / (I5 + J5), 0)

   • Explanation: Computes individual module ROI (Revenue - Total Cost) / Total Cost. If division by zero or structural calculation faults occur, it defaults safely to 0.

5. Multi-Condition Active Module Counter (05_EXECUTIVE_SUMMARY) =COUNTIFS('01_MASTER_DATA'!$H$5:$H$100, "Active", '01_MASTER_DATA'!$N$5:$N$100, "Annual")

   • Explanation: Counts how many curriculum modules are currently flagged as "Active" and possess an "Annual" review cycle requirement.

---

SECTION 4: DATA VALIDATION & CONDITIONAL FORMATTING RULES

A. Data Validation Specifications

• Department Field (01_MASTER_DATA!C5:C100):
  • Validation Type: List (In-Cell Dropdown)
  • Source Range: ='04_LOOKUP_PARAMS'!$A$2:$A$10 (Contains: FinTech, Medicine, Engineering, Business, Physics, Economics, Computer Science, Bioengineering, Psychology, Law, Environmental Sci)
• Delivery Modality (01_MASTER_DATA!D5:D100):
  • Validation Type: List (In-Cell Dropdown)
  • Source Range: ='04_LOOKUP_PARAMS'!$B$2:$B$4 (Contains: Online, Hybrid, In-Person)
• Risk Level (01_MASTER_DATA!N5:N100):
  • Validation Type: List (In-Cell Dropdown)
  • Source Range: ='04_LOOKUP_PARAMS'!$C$2:$C$4 (Contains: Low, Medium, High)
• Target Enrollment (01_MASTER_DATA!F5:F100):
  • Validation Type: Whole Number
  • Data Criteria: Greater than or equal to 10 and less than or equal to 5000.

B. Conditional Formatting Rules

• Rule 1: High-Risk Budget Flagging (01_MASTER_DATA!N5:N100)
  • Condition: = $N5 = "High"
  • Formatting Applied: Soft Red Fill (#FADBD8) with Dark Red Text (#78281F), Bold.
• Rule 2: High Capital Expenditure Alert (01_MASTER_DATA!I5:I100)
  • Condition: = $I5 > 75000
  • Formatting Applied: Amber/Yellow Fill (#FCF3CF) with Dark Yellow Text (#7D6608).
• Rule 3: Completed Module Positive Indicator (01_MASTER_DATA!H5:H100)
  • Condition: = $H5 = "Complete"
  • Formatting Applied: Soft Green Fill (#D4EFDF) with Dark Green Text (#196F3D).

---

SECTION 5: EXECUTIVE SUMMARY KPI CARDS

The following table serves as the layout and computational engine for the C-Suite Dashboard located in Tab 05_EXECUTIVE_SUMMARY.

Note: All summary metrics dynamically pull from the dataset established in Section 2.