Skills › Financial Services › dcf-model › validate_dcf.py
validate_dcf.py
292 lines · plugins/vertical-plugins/financial-analysis/skills/dcf-model/scripts/validate_dcf.py at 6056506
1#!/usr/bin/env python3
2"""
3DCF Model Validation Script
4Validates Excel DCF models for formula errors and common DCF mistakes
5"""
7import sys
8import json
9from pathlib import Path
10from typing import Optional
13class DCFModelValidator:
14 """Validates DCF models for errors and quality issues"""
16 def __init__(self, excel_path: str):
17 try:
18 import openpyxl
19 except ImportError:
20 raise ImportError("openpyxl not installed. Run: pip install openpyxl")
22 self.excel_path = excel_path
23 self.openpyxl = openpyxl
25 if not Path(excel_path).exists():
26 raise FileNotFoundError(f"File not found: {excel_path}")
28 self.workbook_formulas = openpyxl.load_workbook(excel_path, data_only=False)
29 self.workbook_values = openpyxl.load_workbook(excel_path, data_only=True)
30 self.errors = []
31 self.warnings = []
32 self.info = []
34 def validate_all(self) -> dict:
35 """
36 Run all validation checks
38 Returns:
39 Dict with validation results
40 """
41 from datetime import datetime
43 self.check_sheet_structure()
44 self.check_formula_errors()
45 self.check_dcf_logic()
47 results = {
48 'file': self.excel_path,
49 'validation_date': datetime.now().isoformat(),
50 'status': 'PASS' if len(self.errors) == 0 else 'FAIL',
51 'error_count': len(self.errors),
52 'warning_count': len(self.warnings),
53 'errors': self.errors,
54 'warnings': self.warnings,
55 'info': self.info
56 }
58 return results
60 def check_sheet_structure(self):
61 """Verify required sheets exist"""
62 required_sheets = ['DCF', 'WACC', 'Sensitivity']
63 sheet_names = self.workbook_values.sheetnames
65 for sheet in required_sheets:
66 if sheet not in sheet_names:
67 self.warnings.append(f"Recommended sheet missing: {sheet}")
68 else:
69 self.info.append(f"Found sheet: {sheet}")
71 def check_formula_errors(self):
72 """Check for Excel formula errors in all sheets"""
73 excel_errors = ['#VALUE!', '#DIV/0!', '#REF!', '#NAME?', '#NULL!', '#NUM!', '#N/A']
74 error_details = {err: [] for err in excel_errors}
75 total_errors = 0
76 total_formulas = 0
78 for sheet_name in self.workbook_values.sheetnames:
79 ws_values = self.workbook_values[sheet_name]
80 ws_formulas = self.workbook_formulas[sheet_name]
82 for row in ws_values.iter_rows():
83 for cell in row:
84 formula_cell = ws_formulas[cell.coordinate]
86 # Count formulas
87 if formula_cell.value and isinstance(formula_cell.value, str) and formula_cell.value.startswith('='):
88 total_formulas += 1
90 # Check for errors
91 if cell.value is not None and isinstance(cell.value, str):
92 for err in excel_errors:
93 if err in cell.value:
94 location = f"{sheet_name}!{cell.coordinate}"
95 error_details[err].append(location)
96 total_errors += 1
97 self.errors.append(f"{err} at {location}")
98 break
100 # Add summary info
101 self.info.append(f"Total formulas: {total_formulas}")
102 if total_errors == 0:
103 self.info.append("✓ No formula errors found")
104 else:
105 self.errors.append(f"Total formula errors: {total_errors}")
107 return error_details, total_errors
109 def check_dcf_logic(self):
110 """Validate DCF-specific logic and calculations"""
111 self._check_terminal_growth_vs_wacc()
112 self._check_wacc_range()
113 self._check_terminal_value_proportion()
115 def _check_terminal_growth_vs_wacc(self):
116 """Critical check: Terminal growth must be less than WACC"""
117 try:
118 dcf_sheet = self.workbook_values['DCF']
120 terminal_growth = None
121 wacc = None
123 # Search for terminal growth and WACC values
124 for row in dcf_sheet.iter_rows(max_row=100, max_col=20):
125 for cell in row:
126 if cell.value and isinstance(cell.value, str):
127 cell_str = cell.value.lower()
128 if 'terminal' in cell_str and 'growth' in cell_str:
129 # Look for value in adjacent cells
130 for offset in range(1, 5):
131 adjacent = dcf_sheet.cell(cell.row, cell.column + offset).value
132 if isinstance(adjacent, (int, float)) and 0 < adjacent < 1:
133 terminal_growth = adjacent
134 break
135 if 'wacc' in cell_str and wacc is None:
136 for offset in range(1, 5):
137 adjacent = dcf_sheet.cell(cell.row, cell.column + offset).value
138 if isinstance(adjacent, (int, float)) and 0 < adjacent < 1:
139 wacc = adjacent
140 break
142 if terminal_growth is not None and wacc is not None:
143 if terminal_growth >= wacc:
144 self.errors.append(
145 f"CRITICAL: Terminal growth ({terminal_growth:.2%}) >= WACC ({wacc:.2%}). "
146 "This creates infinite value and is mathematically invalid."
147 )
148 else:
149 self.info.append(
150 f"✓ Terminal growth ({terminal_growth:.2%}) < WACC ({wacc:.2%})"
151 )
152 else:
153 self.warnings.append("Could not locate terminal growth and WACC values")
155 except KeyError:
156 self.warnings.append("DCF sheet not found")
157 except Exception as e:
158 self.warnings.append(f"Could not validate terminal growth vs WACC: {str(e)}")
160 def _check_wacc_range(self):
161 """Check if WACC is in reasonable range"""
162 try:
163 wacc_sheet = self.workbook_values.get('WACC') or self.workbook_values['DCF']
164 wacc = None
166 for row in wacc_sheet.iter_rows(max_row=100, max_col=20):
167 for cell in row:
168 if cell.value and isinstance(cell.value, str):
169 if 'wacc' in cell.value.lower():
170 for offset in range(1, 5):
171 adjacent = wacc_sheet.cell(cell.row, cell.column + offset).value
172 if isinstance(adjacent, (int, float)) and 0 < adjacent < 1:
173 wacc = adjacent
174 break
176 if wacc is not None:
177 if wacc < 0.05 or wacc > 0.20:
178 self.warnings.append(
179 f"WACC ({wacc:.2%}) is outside typical range (5%-20%). Verify calculation."
180 )
181 else:
182 self.info.append(f"✓ WACC ({wacc:.2%}) in reasonable range")
183 else:
184 self.warnings.append("Could not locate WACC value")
186 except Exception as e:
187 self.warnings.append(f"Could not validate WACC range: {str(e)}")
189 def _check_terminal_value_proportion(self):
190 """Check if terminal value is reasonable proportion of enterprise value"""
191 try:
192 dcf_sheet = self.workbook_values['DCF']
194 terminal_value = None
195 enterprise_value = None
197 for row in dcf_sheet.iter_rows(max_row=200, max_col=20):
198 for cell in row:
199 if cell.value and isinstance(cell.value, str):
200 cell_str = cell.value.lower()
201 if 'terminal' in cell_str and 'value' in cell_str and 'pv' in cell_str:
202 for offset in range(1, 5):
203 adjacent = dcf_sheet.cell(cell.row, cell.column + offset).value
204 if isinstance(adjacent, (int, float)) and adjacent > 0:
205 terminal_value = adjacent
206 break
207 if 'enterprise' in cell_str and 'value' in cell_str:
208 for offset in range(1, 5):
209 adjacent = dcf_sheet.cell(cell.row, cell.column + offset).value
210 if isinstance(adjacent, (int, float)) and adjacent > 0:
211 enterprise_value = adjacent
212 break
214 if terminal_value is not None and enterprise_value is not None and enterprise_value > 0:
215 proportion = terminal_value / enterprise_value
216 if proportion > 0.80:
217 self.warnings.append(
218 f"Terminal value is {proportion:.1%} of EV (typically should be 50-70%). "
219 "Model may be over-reliant on terminal assumptions."
220 )
221 elif proportion < 0.40:
222 self.warnings.append(
223 f"Terminal value is {proportion:.1%} of EV (typically should be 50-70%). "
224 "Check if terminal assumptions are too conservative."
225 )
226 else:
227 self.info.append(f"✓ Terminal value is {proportion:.1%} of EV")
228 else:
229 self.warnings.append("Could not locate terminal value and enterprise value")
231 except Exception as e:
232 self.warnings.append(f"Could not validate terminal value proportion: {str(e)}")
236def validate_dcf_model(excel_path: str) -> dict:
237 """
238 Validate a DCF model Excel file
240 Args:
241 excel_path: Path to Excel DCF model
243 Returns:
244 Dict with validation results
245 """
246 validator = DCFModelValidator(excel_path)
247 return validator.validate_all()
250def main():
251 """Command-line interface"""
252 if len(sys.argv) < 2:
253 print("Usage: python validate_dcf.py <excel_file> [output.json]")
254 print("\nValidates DCF model for:")
255 print(" - Formula errors (#REF!, #DIV/0!, etc.)")
256 print(" - Terminal growth < WACC (critical)")
257 print(" - WACC in reasonable range (5-20%)")
258 print(" - Terminal value proportion of EV (40-80%)")
259 print("\nReturns JSON with errors, warnings, and info")
260 print("\nExample: python validate_dcf.py model.xlsx")
261 print("Example: python validate_dcf.py model.xlsx results.json")
262 sys.exit(1)
264 excel_file = sys.argv[1]
265 output_file = sys.argv[2] if len(sys.argv) > 2 else None
267 try:
268 results = validate_dcf_model(excel_file)
270 # Print results
271 print(json.dumps(results, indent=2))
273 # Save to file if requested
274 if output_file:
275 with open(output_file, 'w') as f:
276 json.dump(results, f, indent=2)
278 # Exit with error code if validation failed
279 sys.exit(0 if results['status'] == 'PASS' else 1)
281 except Exception as e:
282 error_result = {
283 'file': excel_file,
284 'status': 'ERROR',
285 'error': str(e)
286 }
287 print(json.dumps(error_result, indent=2))
288 sys.exit(1)
291if __name__ == "__main__":
292 main()