| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291 |
- #!/usr/bin/env python3
- """
- DCF Model Validation Script
- Validates Excel DCF models for formula errors and common DCF mistakes
- """
- import sys
- import json
- from pathlib import Path
- class DCFModelValidator:
- """Validates DCF models for errors and quality issues"""
- def __init__(self, excel_path: str):
- try:
- import openpyxl
- except ImportError:
- raise ImportError("openpyxl not installed. Run: pip install openpyxl")
- self.excel_path = excel_path
- self.openpyxl = openpyxl
- if not Path(excel_path).exists():
- raise FileNotFoundError(f"File not found: {excel_path}")
- self.workbook_formulas = openpyxl.load_workbook(excel_path, data_only=False)
- self.workbook_values = openpyxl.load_workbook(excel_path, data_only=True)
- self.errors = []
- self.warnings = []
- self.info = []
-
- def validate_all(self) -> dict:
- """
- Run all validation checks
- Returns:
- Dict with validation results
- """
- from datetime import datetime
- self.check_sheet_structure()
- self.check_formula_errors()
- self.check_dcf_logic()
- results = {
- 'file': self.excel_path,
- 'validation_date': datetime.now().isoformat(),
- 'status': 'PASS' if len(self.errors) == 0 else 'FAIL',
- 'error_count': len(self.errors),
- 'warning_count': len(self.warnings),
- 'errors': self.errors,
- 'warnings': self.warnings,
- 'info': self.info
- }
- return results
-
- def check_sheet_structure(self):
- """Verify required sheets exist"""
- required_sheets = ['DCF', 'WACC', 'Sensitivity']
- sheet_names = self.workbook_values.sheetnames
- for sheet in required_sheets:
- if sheet not in sheet_names:
- self.warnings.append(f"Recommended sheet missing: {sheet}")
- else:
- self.info.append(f"Found sheet: {sheet}")
- def check_formula_errors(self):
- """Check for Excel formula errors in all sheets"""
- excel_errors = ['#VALUE!', '#DIV/0!', '#REF!', '#NAME?', '#NULL!', '#NUM!', '#N/A']
- error_details = {err: [] for err in excel_errors}
- total_errors = 0
- total_formulas = 0
- for sheet_name in self.workbook_values.sheetnames:
- ws_values = self.workbook_values[sheet_name]
- ws_formulas = self.workbook_formulas[sheet_name]
- for row in ws_values.iter_rows():
- for cell in row:
- formula_cell = ws_formulas[cell.coordinate]
- # Count formulas
- if formula_cell.value and isinstance(formula_cell.value, str) and formula_cell.value.startswith('='):
- total_formulas += 1
- # Check for errors
- if cell.value is not None and isinstance(cell.value, str):
- for err in excel_errors:
- if err in cell.value:
- location = f"{sheet_name}!{cell.coordinate}"
- error_details[err].append(location)
- total_errors += 1
- self.errors.append(f"{err} at {location}")
- break
- # Add summary info
- self.info.append(f"Total formulas: {total_formulas}")
- if total_errors == 0:
- self.info.append("✓ No formula errors found")
- else:
- self.errors.append(f"Total formula errors: {total_errors}")
- return error_details, total_errors
-
- def check_dcf_logic(self):
- """Validate DCF-specific logic and calculations"""
- self._check_terminal_growth_vs_wacc()
- self._check_wacc_range()
- self._check_terminal_value_proportion()
- def _check_terminal_growth_vs_wacc(self):
- """Critical check: Terminal growth must be less than WACC"""
- try:
- dcf_sheet = self.workbook_values['DCF']
- terminal_growth = None
- wacc = None
- # Search for terminal growth and WACC values
- for row in dcf_sheet.iter_rows(max_row=100, max_col=20):
- for cell in row:
- if cell.value and isinstance(cell.value, str):
- cell_str = cell.value.lower()
- if 'terminal' in cell_str and 'growth' in cell_str:
- # Look for value in adjacent cells
- for offset in range(1, 5):
- adjacent = dcf_sheet.cell(cell.row, cell.column + offset).value
- if isinstance(adjacent, (int, float)) and 0 < adjacent < 1:
- terminal_growth = adjacent
- break
- if 'wacc' in cell_str and wacc is None:
- for offset in range(1, 5):
- adjacent = dcf_sheet.cell(cell.row, cell.column + offset).value
- if isinstance(adjacent, (int, float)) and 0 < adjacent < 1:
- wacc = adjacent
- break
- if terminal_growth is not None and wacc is not None:
- if terminal_growth >= wacc:
- self.errors.append(
- f"CRITICAL: Terminal growth ({terminal_growth:.2%}) >= WACC ({wacc:.2%}). "
- "This creates infinite value and is mathematically invalid."
- )
- else:
- self.info.append(
- f"✓ Terminal growth ({terminal_growth:.2%}) < WACC ({wacc:.2%})"
- )
- else:
- self.warnings.append("Could not locate terminal growth and WACC values")
- except KeyError:
- self.warnings.append("DCF sheet not found")
- except Exception as e:
- self.warnings.append(f"Could not validate terminal growth vs WACC: {e!s}")
- def _check_wacc_range(self):
- """Check if WACC is in reasonable range"""
- try:
- wacc_sheet = self.workbook_values.get('WACC') or self.workbook_values['DCF']
- wacc = None
- for row in wacc_sheet.iter_rows(max_row=100, max_col=20):
- for cell in row:
- if cell.value and isinstance(cell.value, str):
- if 'wacc' in cell.value.lower():
- for offset in range(1, 5):
- adjacent = wacc_sheet.cell(cell.row, cell.column + offset).value
- if isinstance(adjacent, (int, float)) and 0 < adjacent < 1:
- wacc = adjacent
- break
- if wacc is not None:
- if wacc < 0.05 or wacc > 0.20:
- self.warnings.append(
- f"WACC ({wacc:.2%}) is outside typical range (5%-20%). Verify calculation."
- )
- else:
- self.info.append(f"✓ WACC ({wacc:.2%}) in reasonable range")
- else:
- self.warnings.append("Could not locate WACC value")
- except Exception as e:
- self.warnings.append(f"Could not validate WACC range: {e!s}")
- def _check_terminal_value_proportion(self):
- """Check if terminal value is reasonable proportion of enterprise value"""
- try:
- dcf_sheet = self.workbook_values['DCF']
- terminal_value = None
- enterprise_value = None
- for row in dcf_sheet.iter_rows(max_row=200, max_col=20):
- for cell in row:
- if cell.value and isinstance(cell.value, str):
- cell_str = cell.value.lower()
- if 'terminal' in cell_str and 'value' in cell_str and 'pv' in cell_str:
- for offset in range(1, 5):
- adjacent = dcf_sheet.cell(cell.row, cell.column + offset).value
- if isinstance(adjacent, (int, float)) and adjacent > 0:
- terminal_value = adjacent
- break
- if 'enterprise' in cell_str and 'value' in cell_str:
- for offset in range(1, 5):
- adjacent = dcf_sheet.cell(cell.row, cell.column + offset).value
- if isinstance(adjacent, (int, float)) and adjacent > 0:
- enterprise_value = adjacent
- break
- if terminal_value is not None and enterprise_value is not None and enterprise_value > 0:
- proportion = terminal_value / enterprise_value
- if proportion > 0.80:
- self.warnings.append(
- f"Terminal value is {proportion:.1%} of EV (typically should be 50-70%). "
- "Model may be over-reliant on terminal assumptions."
- )
- elif proportion < 0.40:
- self.warnings.append(
- f"Terminal value is {proportion:.1%} of EV (typically should be 50-70%). "
- "Check if terminal assumptions are too conservative."
- )
- else:
- self.info.append(f"✓ Terminal value is {proportion:.1%} of EV")
- else:
- self.warnings.append("Could not locate terminal value and enterprise value")
- except Exception as e:
- self.warnings.append(f"Could not validate terminal value proportion: {e!s}")
-
- def validate_dcf_model(excel_path: str) -> dict:
- """
- Validate a DCF model Excel file
- Args:
- excel_path: Path to Excel DCF model
- Returns:
- Dict with validation results
- """
- validator = DCFModelValidator(excel_path)
- return validator.validate_all()
- def main():
- """Command-line interface"""
- if len(sys.argv) < 2:
- print("Usage: python validate_dcf.py <excel_file> [output.json]")
- print("\nValidates DCF model for:")
- print(" - Formula errors (#REF!, #DIV/0!, etc.)")
- print(" - Terminal growth < WACC (critical)")
- print(" - WACC in reasonable range (5-20%)")
- print(" - Terminal value proportion of EV (40-80%)")
- print("\nReturns JSON with errors, warnings, and info")
- print("\nExample: python validate_dcf.py model.xlsx")
- print("Example: python validate_dcf.py model.xlsx results.json")
- sys.exit(1)
- excel_file = sys.argv[1]
- output_file = sys.argv[2] if len(sys.argv) > 2 else None
- try:
- results = validate_dcf_model(excel_file)
- # Print results
- print(json.dumps(results, indent=2))
- # Save to file if requested
- if output_file:
- with open(output_file, 'w') as f:
- json.dump(results, f, indent=2)
- # Exit with error code if validation failed
- sys.exit(0 if results['status'] == 'PASS' else 1)
- except Exception as e:
- error_result = {
- 'file': excel_file,
- 'status': 'ERROR',
- 'error': str(e)
- }
- print(json.dumps(error_result, indent=2))
- sys.exit(1)
- if __name__ == "__main__":
- main()
|