306 lines
14 KiB
Python
306 lines
14 KiB
Python
#!/usr/bin/env python3
|
||
# -*- coding: utf-8 -*-
|
||
import openpyxl
|
||
from datetime import datetime
|
||
|
||
print("📊 ПРОВЕРКА КОРРЕКТНОСТИ РАСЧЕТОВ В ШАБЛОНЕ\n")
|
||
print("Файл:", "Шаблон БП_ БКВ 2027 с займами.xlsx")
|
||
|
||
wb = openpyxl.load_workbook("Шаблон БП_ БКВ 2027 с займами.xlsx", data_only=True)
|
||
sheets = wb.sheetnames
|
||
|
||
print(f"Листов: {len(sheets)}")
|
||
print("=" * 70)
|
||
|
||
errors = []
|
||
warnings = []
|
||
success_checks = []
|
||
|
||
# Проверка 1: Финансовые показатели (лист 10.Фин.пок-ли)
|
||
print("\n1️⃣ ПРОВЕРКА: Финансовые показатели (лист 10.Фин.пок-ли)")
|
||
if "10.Фин.пок-ли" in sheets:
|
||
sheet = wb["10.Фин.пок-ли"]
|
||
|
||
# T1 - ставка дисконтирования, T2 - WACC
|
||
discount_rate = sheet.cell(row=1, column=20).value # T1
|
||
wacc = sheet.cell(row=2, column=20).value # T2
|
||
|
||
print(f" Ставка дисконтирования: {discount_rate}")
|
||
print(f" WACC: {wacc}")
|
||
|
||
if discount_rate is None or discount_rate == 0:
|
||
errors.append("❌ Ставка дисконтирования не заполнена или равна 0")
|
||
elif discount_rate > 0.3:
|
||
warnings.append(f"⚠ Ставка дисконтирования {discount_rate*100:.1f}% выглядит завышенной")
|
||
else:
|
||
success_checks.append(f"✓ Ставка дисконтирования: {discount_rate*100:.2f}%")
|
||
|
||
if wacc and wacc > discount_rate:
|
||
warnings.append(f"⚠ WACC ({wacc*100:.2f}%) > ставки дисконтирования ({discount_rate*100:.2f}%)")
|
||
else:
|
||
errors.append("❌ Лист '10.Фин.пок-ли' отсутствует")
|
||
|
||
# Проверка 2: NPV, IRR, PI (лист 11.Заключение)
|
||
print("\n2️⃣ ПРОВЕРКА: Показатели эффективности (лист 11.Заключение)")
|
||
if "11.Заключение" in sheets:
|
||
sheet = wb["11.Заключение"]
|
||
|
||
npv = sheet.cell(row=6, column=20).value # T6
|
||
irr = sheet.cell(row=7, column=20).value # T7
|
||
pi = sheet.cell(row=8, column=20).value # T8
|
||
pbp = sheet.cell(row=9, column=20).value # T9
|
||
|
||
print(f" NPV: {npv} тыс. тг")
|
||
print(f" IRR: {irr}")
|
||
print(f" PI: {pi}")
|
||
print(f" PBP: {pbp} лет")
|
||
|
||
if npv is None:
|
||
errors.append("❌ NPV не заполнен")
|
||
elif npv < 0:
|
||
warnings.append(f"⚠ NPV отрицательный ({npv}) - проект неэффективен")
|
||
else:
|
||
success_checks.append(f"✓ NPV положительный: {npv} тыс. тг")
|
||
|
||
if irr is None:
|
||
errors.append("❌ IRR не заполнен")
|
||
elif discount_rate and irr < discount_rate:
|
||
errors.append(f"❌ IRR ({irr*100:.2f}%) < ставки дисконтирования ({discount_rate*100:.2f}%) - проект неэффективен")
|
||
else:
|
||
success_checks.append(f"✓ IRR: {irr*100:.2f}%")
|
||
|
||
if pi is None:
|
||
errors.append("❌ PI не заполнен")
|
||
elif pi < 1:
|
||
warnings.append(f"⚠ PI < 1 ({pi}) - проект неэффективен")
|
||
else:
|
||
success_checks.append(f"✓ PI: {pi:.3f}")
|
||
else:
|
||
errors.append("❌ Лист '11.Заключение' отсутствует")
|
||
|
||
# Проверка 3: Анализ чувствительности (лист 12.Анализ чувствительности)
|
||
print("\n3️⃣ ПРОВЕРКА: Анализ чувствительности")
|
||
if "12.Анализ чувствительности" in sheets:
|
||
sheet = wb["12.Анализ чувствительности"]
|
||
|
||
base_npv = sheet.cell(row=4, column=3).value # C4
|
||
down_10_rev = sheet.cell(row=5, column=3).value # C5 (-10% доходы)
|
||
up_10_exp = sheet.cell(row=5, column=5).value # E5 (+10% расходы)
|
||
up_10_capex = sheet.cell(row=5, column=7).value # G5 (+10% CAPEX)
|
||
|
||
print(f" Базовый NPV: {base_npv}")
|
||
print(f" NPV при -10% доходов: {down_10_rev}")
|
||
print(f" NPV при +10% расходов: {up_10_exp}")
|
||
print(f" NPV при +10% CAPEX: {up_10_capex}")
|
||
|
||
if base_npv and down_10_rev:
|
||
if down_10_rev >= base_npv:
|
||
errors.append(f"❌ Анализ чувствительности: NPV при -10% доходов ({down_10_rev}) >= базового NPV ({base_npv})")
|
||
else:
|
||
success_checks.append("✓ NPV корректно реагирует на снижение доходов")
|
||
|
||
if base_npv and base_npv > 0:
|
||
if down_10_rev and down_10_rev < 0:
|
||
success_checks.append("✓ При -10% доходов NPV становится отрицательным (высокая чувствительность)")
|
||
else:
|
||
errors.append("❌ Лист '12.Анализ чувствительности' отсутствует")
|
||
|
||
# Проверка 4: EBITDA и риск-аппетит (лист 13.1 Карта рисков)
|
||
print("\n4️⃣ ПРОВЕРКА: Расчет риск-аппетита (лист 13.1 Карта рисков)")
|
||
if "13.1 Карта рисков" in sheets:
|
||
sheet = wb["13.1 Карта рисков"]
|
||
|
||
ebitda = sheet.cell(row=1, column=18).value # R1
|
||
risk_appetite_pct = sheet.cell(row=1, column=19).value # S1
|
||
risk_appetite_val = sheet.cell(row=1, column=20).value # T1
|
||
|
||
print(f" EBITDA: {ebitda} тыс. тг")
|
||
print(f" Риск-аппетит: {risk_appetite_pct*100 if risk_appetite_pct else None}%")
|
||
print(f" Расчетное значение: {risk_appetite_val} млрд. тг")
|
||
|
||
if ebitda and risk_appetite_pct and risk_appetite_val:
|
||
calculated = (ebitda * risk_appetite_pct) / 1_000_000_000
|
||
diff = abs(calculated - risk_appetite_val)
|
||
|
||
if diff > 0.1:
|
||
errors.append(f"❌ Риск-аппетит: расчетное значение {calculated:.3f} млрд ≠ {risk_appetite_val} млрд")
|
||
else:
|
||
success_checks.append(f"✓ Риск-аппетит рассчитан верно: {risk_appetite_val:.3f} млрд. тг")
|
||
else:
|
||
errors.append("❌ Лист '13.1 Карта рисков' отсутствует")
|
||
|
||
# Проверка 5: Премия за специфичный риск (лист 13.2)
|
||
print("\n5️⃣ ПРОВЕРКА: Премия за специфичный риск (лист 13.2)")
|
||
if "13.2 Премия за специфич риск" in sheets:
|
||
sheet = wb["13.2 Премия за специфич риск"]
|
||
|
||
total_risk_premium = 0
|
||
risk_factors = 0
|
||
|
||
for row in range(7, 35):
|
||
cell_value = sheet.cell(row=row, column=5).value # E7:E34
|
||
if cell_value and isinstance(cell_value, (int, float)) and cell_value > 0:
|
||
total_risk_premium += cell_value
|
||
risk_factors += 1
|
||
print(f" Фактор {risk_factors}: {cell_value*100:.2f}%")
|
||
|
||
print(f" Итого премия за риск: {total_risk_premium*100:.2f}%")
|
||
|
||
if total_risk_premium == 0:
|
||
warnings.append("⚠ Премия за специфичный риск не рассчитана (все факторы = 0)")
|
||
elif total_risk_premium > 0.05:
|
||
warnings.append(f"⚠ Премия за специфичный риск {total_risk_premium*100:.2f}% превышает разумный предел (5%)")
|
||
else:
|
||
success_checks.append(f"✓ Премия за специфичный риск: {total_risk_premium*100:.2f}%")
|
||
else:
|
||
errors.append("❌ Лист '13.2 Премия за специфич риск' отсутствует")
|
||
|
||
# Проверка 6: Заявление о риск-аппетите (лист 13.3)
|
||
print("\n6️⃣ ПРОВЕРКА: Заявление о риск-аппетите (лист 13.3)")
|
||
if "13.3 Заявление о риск аппетите" in sheets:
|
||
sheet = wb["13.3 Заявление о риск аппетите"]
|
||
|
||
div_errors = 0
|
||
for row in range(6, 15):
|
||
for col in range(1, 10):
|
||
cell_value = sheet.cell(row=row, column=col).value
|
||
if cell_value == "#DIV/0!":
|
||
div_errors += 1
|
||
|
||
if div_errors > 0:
|
||
errors.append(f"❌ В листе '13.3 Заявление о риск аппетите' найдено {div_errors} ошибок #DIV/0!")
|
||
else:
|
||
success_checks.append("✓ Заявление о риск-аппетите: формулы рассчитаны")
|
||
else:
|
||
errors.append("❌ Лист '13.3 Заявление о риск аппетите' отсутствует")
|
||
|
||
# Проверка 7: Лизинг (лист Лизинг)
|
||
print("\n7️⃣ ПРОВЕРКА: Расчеты лизинга")
|
||
if "Лизинг" in sheets:
|
||
sheet = wb["Лизинг"]
|
||
|
||
total_amount = sheet.cell(row=8, column=6).value # F8
|
||
payments_sum = sheet.cell(row=53, column=5).value # E53 (Итого)
|
||
|
||
print(f" Общая сумма договора: {total_amount}")
|
||
print(f" Сумма платежей по графику: {payments_sum}")
|
||
|
||
if total_amount and total_amount > 0:
|
||
if not payments_sum or payments_sum == 0:
|
||
errors.append(f"❌ Лизинг: сумма платежей ({payments_sum}) не равна общей сумме ({total_amount})")
|
||
else:
|
||
success_checks.append("✓ Лизинг: график платежей заполнен")
|
||
else:
|
||
warnings.append("⚠ Лизинг: общая сумма договора не заполнена или равна 0")
|
||
|
||
zero_payments = 0
|
||
for row in range(11, 52):
|
||
payment = sheet.cell(row=row, column=5).value
|
||
if payment == 0:
|
||
zero_payments += 1
|
||
|
||
if zero_payments > 40:
|
||
warnings.append(f"⚠ Лизинг: {zero_payments} платежей равны 0 - формулы не настроены")
|
||
else:
|
||
errors.append("❌ Лист 'Лизинг' отсутствует")
|
||
|
||
# Проверка 8: Заполненность БКВ (лист 4.БКВ)
|
||
print("\n8️⃣ ПРОВЕРКА: Заполненность БКВ")
|
||
if "4.БКВ" in sheets:
|
||
sheet = wb["4.БКВ"]
|
||
|
||
empty_cells = 0
|
||
total_cells = 0
|
||
|
||
for row in range(1, min(21, sheet.max_row + 1)):
|
||
for col in range(1, min(21, sheet.max_column + 1)):
|
||
total_cells += 1
|
||
cell_value = sheet.cell(row=row, column=col).value
|
||
if cell_value is None or cell_value == "":
|
||
empty_cells += 1
|
||
|
||
empty_pct = (empty_cells / total_cells * 100) if total_cells > 0 else 0
|
||
print(f" Пустых ячеек в первых 20 строках: {empty_pct:.1f}%")
|
||
|
||
if empty_pct > 50:
|
||
warnings.append(f"⚠ Лист '4.БКВ': {empty_pct:.1f}% пустых ячеек")
|
||
else:
|
||
success_checks.append(f"✓ Лист '4.БКВ': заполненность {100-empty_pct:.1f}%")
|
||
else:
|
||
errors.append("❌ Лист '4.БКВ' отсутствует")
|
||
|
||
# Проверка 9: ОДДС (лист 8.ОДДС)
|
||
print("\n9️⃣ ПРОВЕРКА: Отчет о движении денежных средств (8.ОДДС)")
|
||
if "8.ОДДС" in sheets:
|
||
sheet = wb["8.ОДДС"]
|
||
|
||
# Проверяем наличие отрицательных накопленных потоков в начале проекта
|
||
negative_dcf = 0
|
||
for col in range(5, 20): # Квартальные столбцы
|
||
dcf = sheet.cell(row=10, column=col).value # Накопленный дисконтированный поток
|
||
if dcf and dcf < 0:
|
||
negative_dcf += 1
|
||
|
||
if negative_dcf > 0:
|
||
success_checks.append(f"✓ ОДДС: {negative_dcf} кварталов с отрицательным накопленным DCF (нормально для начала проекта)")
|
||
else:
|
||
errors.append("❌ Лист '8.ОДДС' отсутствует")
|
||
|
||
# ВЫВОД РЕЗУЛЬТАТОВ
|
||
print("\n" + "=" * 70)
|
||
print("📋 ИТОГОВЫЙ ОТЧЕТ")
|
||
print("=" * 70)
|
||
|
||
if errors:
|
||
print(f"\n❌ КРИТИЧЕСКИЕ ОШИБКИ ({len(errors)}):")
|
||
for i, e in enumerate(errors, 1):
|
||
print(f" {i}. {e}")
|
||
|
||
if warnings:
|
||
print(f"\n⚠ ПРЕДУПРЕЖДЕНИЯ ({len(warnings)}):")
|
||
for i, w in enumerate(warnings, 1):
|
||
print(f" {i}. {w}")
|
||
|
||
if success_checks:
|
||
print(f"\n✅ КОРРЕКТНЫЕ РАСЧЕТЫ ({len(success_checks)}):")
|
||
for i, s in enumerate(success_checks, 1):
|
||
print(f" {i}. {s}")
|
||
|
||
print("\n" + "=" * 70)
|
||
print("ВСЕГО НАЙДЕНО:")
|
||
print(f" Ошибки: {len(errors)}")
|
||
print(f" Предупреждения: {len(warnings)}")
|
||
print(f" Корректных расчетов: {len(success_checks)}")
|
||
print("=" * 70)
|
||
|
||
# Сохраняем отчет
|
||
report_wb = openpyxl.Workbook()
|
||
report_sheet = report_wb.active
|
||
report_sheet.title = "Отчет о проверке"
|
||
|
||
report_data = [
|
||
["ПРОВЕРКА КОРРЕКТНОСТИ РАСЧЕТОВ В ШАБЛОНЕ"],
|
||
["Файл", "Шаблон БП_ БКВ 2027 с займами.xlsx"],
|
||
["Дата проверки", datetime.now().strftime("%d.%m.%Y %H:%M")],
|
||
[],
|
||
["КРИТИЧЕСКИЕ ОШИБКИ"] + [len(errors)],
|
||
] + [[e] for e in errors] + [
|
||
[],
|
||
["ПРЕДУПРЕЖДЕНИЯ"] + [len(warnings)],
|
||
] + [[w] for w in warnings] + [
|
||
[],
|
||
["КОРРЕКТНЫЕ РАСЧЕТЫ"] + [len(success_checks)],
|
||
] + [[s] for s in success_checks]
|
||
|
||
for row_idx, row_data in enumerate(report_data, 1):
|
||
for col_idx, value in enumerate(row_data, 1):
|
||
report_sheet.cell(row=row_idx, column=col_idx, value=value)
|
||
|
||
report_file = "Отчет_о_проверке_расчетов.xlsx"
|
||
report_wb.save(report_file)
|
||
|
||
print(f"\n📁 Подробный отчет сохранен: {report_file}")
|
||
|
||
if errors:
|
||
exit(1)
|