1. EXECUTIVE SUMMARY
1.1 Project Overview
This project establishes a robust financial framework to evaluate the educational center’s performance across three critical pillars: Commercial Efficiency (Marketing), Operational Excellence (Capacity & Teachers), and Financial Health (P&L & Liquidity).
1.2 Key Findings
- profitability Variance: A significant disparity exists in Gross Profit per Class among teaching staff. High-performing part-time instructors are outperforming some full-time staff in unit economics, suggesting an opportunity for resource reallocation.
- Revenue Leakage: Analysis of the “Partial Payment” and “None Payment” categories reveals a material risk in revenue collection. Historical data suggests that uncollected tuition typically becomes bad debt due to a lack of aging enforcement.
- Capacity Utilization: There is tangible “slack” in current class sizes. Increasing the fill rate remains the highest leverage lever for margin expansion without increasing fixed costs.
1.3 Strategic Recommendations
- Optimize Teacher Allocation: Shift course loads toward Tier-1 (High Margin) instructors and freeze hiring for low-margin profiles.
- Enforce Collection Policy: Implement a stricter credit control policy for 2025. Move from a “reactive” collection model to a “proactive” deposit/installment structure to minimize bad debt.
- Data-Driven Forecasting: Shift 2025 budgeting from simple historical run-rates to a driver-based approach (Enrollment x Fill Rate x Collection Rate).
The objective of this project was to construct a dynamic, scalable financial model capable of forecasting revenue, analyzing cost efficiency, and optimizing resource allocation for the education center. Unlike static budgets, this model integrates seasonality factors, teacher performance tiers, and sensitivity analysis to support data-driven strategic planning for FY2025.
2. METHODOLOGY & DATA ARCHITECTURE
The model is built exclusively in Excel, utilizing relational data principles to link disparate datasets (Enrollment, Classes, Teachers, Marketing).
2.1 Model Structure (Sheet by Sheet)
The portfolio is organized into the following tabs for logical flow:
(0) Assumptions & Drivers: Centralized input variables (Tuition rates, Teacher hourly rates, Cost drivers).
(1) Executive Summary: High-level dashboard for strategic decision-making.
(2) Monthly Summary P&L: Granular view of financial performance.
(3) Marketing Efficiency: ROI analysis of acquisition channels.
(4) Operational Efficiency: Capacity utilization and student flow analysis.
(5) Teacher Performance Matrix: Unit economics per instructor.
(6) Cash Flow & Liquidity: Revenue leakage analysis and 2025 Cash forecasts.
3. DETAILED MODULE ANALYSIS
3.1 Marketing Efficiency Analysis
- Objective: Determine the Cost of Acquisition (CAC) and conversion effectiveness.
- Metrics Tracked: Leads generated, Conversion Rates, Cost per Lead (CPL), and CAC.
- Insight: Identifies which channels deliver students who actually convert to paid enrollment versus high-volume/low-quality leads.
3.2 Operational Efficiency & Capacity Optimization
- Objective: Measure how well the center utilizes its fixed assets (classrooms) and variable assets (teaching hours).
- Key Metric: Fill Rate (Occupancy Rate).
- Analysis: We compared
Actual Enrollmentvs.Maximum Capacity. - Implication: A fill rate below 60% indicates wasted margin. The model identifies specific classes/time slots causing this drag.
3.3 Teacher Performance & Margin Analysis
- Objective: Evaluate instructors not just by academic quality, but by Unit Economics (Gross Profit per Class).
- Methodology:
- Revenue Attribution: Total tuition generated by students in specific classes.
- Direct Cost: Teacher salary (Hourly rate $\times$ Hours) or allocated Fixed Salary.
- Contribution Margin: Revenue – Direct Cost.
- Critical Finding: Data reveals that
Employment Type(Full-time vs. Part-time) is not a predictor of profitability. Several part-time teachers generate higher margins due to high class popularity and flexible cost structures.
3.4 Liquidity Management & Revenue Leakage
- Data Integrity Note: Historical Accounts Receivable (AR) data for 2024 was cumulative (“Stock” data) rather than transactional (“Flow” data), preventing accurate aging analysis (<30 vs >30 days).
- Analytical Pivot: Instead of analyzing defective historical aging, we focused on Revenue Leakage Quantification:
- Calculated total
Tuition Lostfrom “None” and “Partial” payment statuses. - Established a “Collection Failure Rate” baseline to apply as a bad debt provision for the 2025 Forecast.
- Calculated total
- 2025 Forecast Model: The model projects 2025 cash flow based on Drivers (New Enrollment goals) adjusted by a Collection Schedule assumption (e.g., 85% collected in Month 0, 10% in Month +1, 5% Bad Debt).
4. ASSUMPTIONS & LIMITATIONS
To ensure transparency for the CFO, the following limitations apply:
- Teacher Allocation: The recommendation to reallocate classes based on margin assumes that Tier-1 teachers have the capacity to take on more hours. It does not account for qualitative HR factors (soft skills, loyalty) or labor contract constraints.
- Historical AR: Due to the lack of “Invoice Date” and “Payment Date” in the raw data, the 2024 AR analysis is a static snapshot. It represents total uncollected potential revenue, not a dynamic cash flow statement.
- Seasonality: The 2025 Forecast assumes standard seasonality based on 2024 trends. External market shifts are not modeled.
5. NEXT STEPS FOR FINANCE TEAM
- Scenario Planning (Sheet 6): Develop a “What-If” sensitivity analysis.
- Scenario A: Increase Class Fill Rate by 10%.
- Scenario B: Replace bottom 20% of low-margin classes with high-margin subjects.
- HR & Finance Alignment: Review the “Teacher Performance Matrix” with the Academic Director to validate if low-performing teachers can be up-skilled or if contract renegotiation is required.
- System Upgrade: Recommend implementing a CRM/ERP system that records specific dates for payments to enable accurate Cash Flow Aging analysis in the future.
Thank you for reaching here!
phamnguyenkhanh.home.blog 