313 lines
14 KiB
JavaScript
313 lines
14 KiB
JavaScript
const XLSX = require("xlsx");
|
||
|
||
const wb = XLSX.readFile("Шаблон БП_ БКВ 2027 с займами.xlsx");
|
||
const sheets = wb.SheetNames;
|
||
|
||
console.log("📊 ПРОВЕРКА КОРРЕКТНОСТИ РАСЧЕТОВ В ШАБЛОНЕ\n");
|
||
console.log("Файл:", "Шаблон БП_ БКВ 2027 с займами.xlsx");
|
||
console.log("Листов:", sheets.length);
|
||
console.log("=" .repeat(70));
|
||
|
||
const errors = [];
|
||
const warnings = [];
|
||
const successChecks = [];
|
||
|
||
// Проверка 1: Анализ чувствительности (лист 12)
|
||
console.log("\n1️⃣ ПРОВЕРКА: Анализ чувствительности (лист 12.Анализ чувствительности)");
|
||
if (sheets.includes("12.Анализ чувствительности")) {
|
||
const sheet = XLSX.utils.sheet_to_json(wb.Sheets["12.Анализ чувствительности"], { header: 1 });
|
||
|
||
// Проверяем NPV базового сценария
|
||
const baseNPV = sheet[3]?.[2]; // Ячейка C4 (0-indexed: row 3, col 2)
|
||
const down10Revenue = sheet[4]?.[2]; // C5
|
||
const up10Expenses = sheet[4]?.[4]; // E5
|
||
const up10CAPEX = sheet[4]?.[6]; // G5
|
||
|
||
console.log(` Базовый NPV: ${baseNPV}`);
|
||
console.log(` NPV при -10% доходов: ${down10Revenue}`);
|
||
console.log(` NPV при +10% расходов: ${up10Expenses}`);
|
||
console.log(` NPV при +10% CAPEX: ${up10CAPEX}`);
|
||
|
||
// Проверка: NPV при снижении доходов должен быть меньше базового
|
||
if (baseNPV && down10Revenue) {
|
||
if (down10Revenue >= baseNPV) {
|
||
errors.push(`❌ Анализ чувствительности: NPV при -10% доходов (${down10Revenue}) >= базового NPV (${baseNPV})`);
|
||
} else {
|
||
successChecks.push(`✓ NPV корректно реагирует на снижение доходов`);
|
||
}
|
||
}
|
||
|
||
// Проверка: NPV должен становиться отрицательным при критическом снижении
|
||
if (baseNPV && baseNPV > 0) {
|
||
warnings.push(`⚠ Базовый NPV положительный (${baseNPV}), но в анализе чувствительности есть отрицательные значения`);
|
||
}
|
||
} else {
|
||
errors.push("❌ Лист '12.Анализ чувствительности' отсутствует");
|
||
}
|
||
|
||
// Проверка 2: EBITDA и риск-аппетит (лист 13.1 Карта рисков)
|
||
console.log("\n2️⃣ ПРОВЕРКА: Расчет риск-аппетита (лист 13.1 Карта рисков)");
|
||
if (sheets.includes("13.1 Карта рисков")) {
|
||
const sheet = XLSX.utils.sheet_to_json(wb.Sheets["13.1 Карта рисков"], { header: 1 });
|
||
|
||
const ebitda = sheet[0]?.[17]; // R1 (EBITDA)
|
||
const riskAppetitePercent = sheet[0]?.[18]; // S1 (7%)
|
||
const riskAppetiteValue = sheet[0]?.[19]; // T1 (3.86 млрд)
|
||
|
||
console.log(` EBITDA: ${ebitda} тыс. тг`);
|
||
console.log(` Риск-аппетит: ${riskAppetitePercent * 100}%`);
|
||
console.log(` Расчетное значение: ${riskAppetiteValue} млрд. тг`);
|
||
|
||
if (ebitda && riskAppetitePercent && riskAppetiteValue) {
|
||
const calculatedRiskAppetite = (ebitda * riskAppetitePercent) / 1000000000; // в млрд
|
||
const diff = Math.abs(calculatedRiskAppetite - riskAppetiteValue);
|
||
|
||
if (diff > 0.01) {
|
||
errors.push(`❌ Риск-аппетит: расчетное значение ${calculatedRiskAppetite.toFixed(3)} млрд ≠ ${riskAppetiteValue} млрд`);
|
||
} else {
|
||
successChecks.push(`✓ Риск-аппетит рассчитан верно: ${riskAppetiteValue} млрд. тг`);
|
||
}
|
||
}
|
||
} else {
|
||
errors.push("❌ Лист '13.1 Карта рисков' отсутствует");
|
||
}
|
||
|
||
// Проверка 3: Премия за специфичный риск (лист 13.2)
|
||
console.log("\n3️⃣ ПРОВЕРКА: Премия за специфичный риск (лист 13.2 Премия за специфич риск)");
|
||
if (sheets.includes("13.2 Премия за специфич риск")) {
|
||
const sheet = XLSX.utils.sheet_to_json(wb.Sheets["13.2 Премия за специфич риск"], { header: 1 });
|
||
|
||
// Ищем строки с оценками
|
||
let totalRiskPremium = 0;
|
||
let riskFactorsFound = 0;
|
||
|
||
for (let i = 6; i < Math.min(sheet.length, 35); i++) {
|
||
const row = sheet[i];
|
||
if (row && row[4] !== undefined) {
|
||
const riskValue = row[4]; // Столбец E - значение %
|
||
if (typeof riskValue === 'number' && riskValue > 0) {
|
||
totalRiskPremium += riskValue;
|
||
riskFactorsFound++;
|
||
console.log(` Фактор ${riskFactorsFound}: ${riskValue * 100}%`);
|
||
}
|
||
}
|
||
}
|
||
|
||
console.log(` Итого премия за риск: ${(totalRiskPremium * 100).toFixed(2)}%`);
|
||
|
||
// Проверка методики: сумма не должна превышать 5% (максимум по 4 факторам по 1%)
|
||
if (totalRiskPremium > 0.05) {
|
||
warnings.push(`⚠ Премия за специфичный риск ${(totalRiskPremium * 100).toFixed(2)}% превышает разумный предел (5%)`);
|
||
} else if (totalRiskPremium === 0) {
|
||
warnings.push("⚠ Премия за специфичный риск не рассчитана (все факторы = 0)");
|
||
} else {
|
||
successChecks.push(`✓ Премия за специфичный риск: ${(totalRiskPremium * 100).toFixed(2)}%`);
|
||
}
|
||
} else {
|
||
errors.push("❌ Лист '13.2 Премия за специфич риск' отсутствует");
|
||
}
|
||
|
||
// Проверка 4: Заявление о риск-аппетите (лист 13.3)
|
||
console.log("\n4️⃣ ПРОВЕРКА: Заявление о риск-аппетите (лист 13.3)");
|
||
if (sheets.includes("13.3 Заявление о риск аппетите")) {
|
||
const sheet = XLSX.utils.sheet_to_json(wb.Sheets["13.3 Заявление о риск аппетите"], { header: 1 });
|
||
|
||
// Проверяем наличие ошибок #DIV/0!
|
||
let divErrors = 0;
|
||
for (let i = 5; i < Math.min(sheet.length, 15); i++) {
|
||
const row = sheet[i];
|
||
if (row) {
|
||
for (let j = 0; j < row.length; j++) {
|
||
if (row[j] === "#DIV/0!") {
|
||
divErrors++;
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
if (divErrors > 0) {
|
||
errors.push(`❌ В листе '13.3 Заявление о риск аппетите' найдено ${divErrors} ошибок #DIV/0! - формулы не рассчитаны`);
|
||
} else {
|
||
successChecks.push(`✓ Заявление о риск-аппетите: формулы рассчитаны`);
|
||
}
|
||
} else {
|
||
errors.push("❌ Лист '13.3 Заявление о риск аппетите' отсутствует");
|
||
}
|
||
|
||
// Проверка 5: Лизинг (лист Лизинг)
|
||
console.log("\n5️⃣ ПРОВЕРКА: Расчеты лизинга (лист Лизинг)");
|
||
if (sheets.includes("Лизинг")) {
|
||
const sheet = XLSX.utils.sheet_to_json(wb.Sheets["Лизинг"], { header: 1 });
|
||
|
||
// Проверяем первый договор лизинга
|
||
const totalAmount1 = sheet[7]?.[5]; // F8 - Общая сумма первого договора
|
||
const paymentsSum = sheet[52]?.[4]; // E53 - Итого платежей
|
||
|
||
console.log(` Общая сумма договора 1: ${totalAmount1}`);
|
||
console.log(` Сумма платежей (график): ${paymentsSum}`);
|
||
|
||
if (totalAmount1 && totalAmount1 > 0) {
|
||
if (paymentsSum === 0 || paymentsSum === undefined) {
|
||
errors.push(`❌ Лизинг: сумма платежей (${paymentsSum}) не равна общей сумме договора (${totalAmount1})`);
|
||
} else {
|
||
successChecks.push(`✓ Лизинг: график платежей заполнен`);
|
||
}
|
||
} else {
|
||
warnings.push("⚠ Лизинг: общая сумма договора не заполнена или равна 0");
|
||
}
|
||
|
||
// Проверка: все ли платежи рассчитаны
|
||
let zeroPayments = 0;
|
||
for (let i = 10; i < 52; i++) {
|
||
if (sheet[i] && sheet[i][4] === 0) {
|
||
zeroPayments++;
|
||
}
|
||
}
|
||
|
||
if (zeroPayments > 40) {
|
||
warnings.push(`⚠ Лизинг: ${zeroPayments} платежей равны 0 - возможно, формулы не настроены`);
|
||
}
|
||
} else {
|
||
errors.push("❌ Лист 'Лизинг' отсутствует");
|
||
}
|
||
|
||
// Проверка 6: Финансовые показатели (лист 10.Фин.пок-ли)
|
||
console.log("\n6️⃣ ПРОВЕРКА: Финансовые показатели (лист 10.Фин.пок-ли)");
|
||
if (sheets.includes("10.Фин.пок-ли")) {
|
||
const sheet = XLSX.utils.sheet_to_json(wb.Sheets["10.Фин.пок-ли"], { header: 1 });
|
||
|
||
const discountRate = sheet[0]?.[19]; // T1 - ставка дисконтирования
|
||
const wacc = sheet[1]?.[19]; // T2 - WACC
|
||
|
||
console.log(` Ставка дисконтирования: ${discountRate ? (discountRate * 100).toFixed(2) + '%' : 'не заполнена'}`);
|
||
console.log(` WACC: ${wacc ? (wacc * 100).toFixed(2) + '%' : 'не заполнен'}`);
|
||
|
||
if (discountRate && discountRate > 0.3) {
|
||
warnings.push(`⚠ Ставка дисконтирования {(discountRate * 100).toFixed(1)}% выглядит завышенной`);
|
||
}
|
||
|
||
if (!discountRate || discountRate === 0) {
|
||
errors.push("❌ Ставка дисконтирования не заполнена или равна 0");
|
||
} else {
|
||
successChecks.push(`✓ Ставка дисконтирования: ${(discountRate * 100).toFixed(2)}%`);
|
||
}
|
||
} else {
|
||
errors.push("❌ Лист '10.Фин.пок-ли' отсутствует");
|
||
}
|
||
|
||
// Проверка 7: NPV, IRR, PI (лист 11.Заключение)
|
||
console.log("\n7️⃣ ПРОВЕРКА: Показатели эффективности (лист 11.Заключение)");
|
||
if (sheets.includes("11.Заключение")) {
|
||
const sheet = XLSX.utils.sheet_to_json(wb.Sheets["11.Заключение"], { header: 1 });
|
||
|
||
const npv = sheet[5]?.[19]; // T6 - NPV
|
||
const irr = sheet[6]?.[19]; // T7 - IRR
|
||
const pi = sheet[7]?.[19]; // T8 - PI
|
||
const pbp = sheet[8]?.[19]; // T9 - PBP
|
||
|
||
console.log(` NPV: ${npv}`);
|
||
console.log(` IRR: ${irr ? (irr * 100).toFixed(2) + '%' : 'не заполнен'}`);
|
||
console.log(` PI: ${pi}`);
|
||
console.log(` PBP: ${pbp} лет`);
|
||
|
||
// Проверка согласованности
|
||
if (npv !== undefined && npv < 0) {
|
||
warnings.push(`⚠ NPV отрицательный (${npv}) - проект неэффективен`);
|
||
}
|
||
|
||
if (irr && discountRate && irr < discountRate) {
|
||
errors.push(`❌ IRR (${(irr * 100).toFixed(2)}%) < ставки дисконтирования (${(discountRate * 100).toFixed(2)}%) - проект неэффективен`);
|
||
}
|
||
|
||
if (npv && npv > 0) {
|
||
successChecks.push(`✓ NPV положительный: ${npv} тыс. тг`);
|
||
}
|
||
} else {
|
||
errors.push("❌ Лист '11.Заключение' отсутствует");
|
||
}
|
||
|
||
// Проверка 8: Проверка на пустые ячейки в ключевых разделах
|
||
console.log("\n8️⃣ ПРОВЕРКА: Заполненность ключевых разделов");
|
||
const criticalSheets = ["1.Резюме проекта", "4.БКВ", "8.ОДДС", "9 КПЭ"];
|
||
criticalSheets.forEach(sheetName => {
|
||
if (sheets.includes(sheetName)) {
|
||
const sheet = XLSX.utils.sheet_to_json(wb.Sheets[sheetName], { header: 1 });
|
||
let emptyCells = 0;
|
||
let totalCells = 0;
|
||
|
||
for (let i = 0; i < Math.min(sheet.length, 20); i++) {
|
||
for (let j = 0; j < (sheet[i]?.length || 0); j++) {
|
||
totalCells++;
|
||
if (sheet[i][j] === undefined || sheet[i][j] === null || sheet[i][j] === "") {
|
||
emptyCells++;
|
||
}
|
||
}
|
||
}
|
||
|
||
const emptyPercent = totalCells > 0 ? (emptyCells / totalCells * 100).toFixed(1) : 0;
|
||
if (parseFloat(emptyPercent) > 50) {
|
||
warnings.push(`⚠ Лист '${sheetName}': ${emptyPercent}% пустых ячеек в первых 20 строках`);
|
||
} else {
|
||
successChecks.push(`✓ Лист '${sheetName}': заполненность ${100 - parseFloat(emptyPercent)}%`);
|
||
}
|
||
}
|
||
});
|
||
|
||
// ВЫВОД РЕЗУЛЬТАТОВ
|
||
console.log("\n" + "=".repeat(70));
|
||
console.log("📋 ИТОГОВЫЙ ОТЧЕТ");
|
||
console.log("=".repeat(70));
|
||
|
||
if (errors.length > 0) {
|
||
console.log("\n❌ КРИТИЧЕСКИЕ ОШИБКИ (" + errors.length + "):");
|
||
errors.forEach((e, i) => console.log(` ${i + 1}. ${e}`));
|
||
}
|
||
|
||
if (warnings.length > 0) {
|
||
console.log("\n⚠ ПРЕДУПРЕЖДЕНИЯ (" + warnings.length + "):");
|
||
warnings.forEach((w, i) => console.log(` ${i + 1}. ${w}`));
|
||
}
|
||
|
||
if (successChecks.length > 0) {
|
||
console.log("\n✅ КОРРЕКТНЫЕ РАСЧЕТЫ (" + successChecks.length + "):");
|
||
successChecks.forEach((s, i) => console.log(` ${i + 1}. ${s}`));
|
||
}
|
||
|
||
console.log("\n" + "=".repeat(70));
|
||
console.log("ВСЕГО НАЙДЕНО:");
|
||
console.log(` Ошибки: ${errors.length}`);
|
||
console.log(` Предупреждения: ${warnings.length}`);
|
||
console.log(` Корректных расчетов: ${successChecks.length}`);
|
||
console.log("=".repeat(70));
|
||
|
||
// Сохраняем отчет в файл
|
||
const reportData = [
|
||
["ПРОВЕРКА КОРРЕКТНОСТИ РАСЧЕТОВ В ШАБЛОНЕ"],
|
||
["Файл", "Шаблон БП_ БКВ 2027 с займами.xlsx"],
|
||
["Дата проверки", new Date().toLocaleString("ru-RU")],
|
||
[],
|
||
["КРИТИЧЕСКИЕ ОШИБКИ"],
|
||
...errors.map(e => [e]),
|
||
[],
|
||
["ПРЕДУПРЕЖДЕНИЯ"],
|
||
...warnings.map(w => [w]),
|
||
[],
|
||
["КОРРЕКТНЫЕ РАСЧЕТЫ"],
|
||
...successChecks.map(s => [s])
|
||
];
|
||
|
||
const reportWb = XLSX.utils.book_new();
|
||
const reportSheet = XLSX.utils.json_to_sheet(reportData.slice(5), { skipHeader: false });
|
||
XLSX.utils.sheet_add_aoa(reportSheet, reportData.slice(0, 5), { origin: "A1" });
|
||
XLSX.utils.book_append_sheet(reportWb, reportSheet, "Отчет о проверке");
|
||
|
||
const reportFile = "Отчет_о_проверке_расчетов.xlsx";
|
||
XLSX.writeFile(reportWb, reportFile);
|
||
|
||
console.log(`\n📁 Подробный отчет сохранен: ${reportFile}`);
|
||
|
||
// Выход с кодом ошибки если есть критические ошибки
|
||
if (errors.length > 0) {
|
||
process.exit(1);
|
||
}
|