Эффективность проекта и годовая отчётность

Начало / Показатели

Показатели эффективности и ставка дисконтирования

Рассчитываем NPV — чистую приведённую стоимость, IRR — внутреннюю норму доходности, PB — срок окупаемости и PI — индекс доходности. С помощью ставки дисконтирования приводим будущие потоки к выбранной начальной дате.

Продолжаем рабочую книгу после М6. Входной файл для сверки — «М06_Контроль_v2.xlsx».

Методика / Ставка

Принимаем метод расчёта ставки

Для упражнения принимаем ставку дисконтирования равной среднему трёх заданных величин: инфляции, ставки депозита и ставки кредита. Расчёт CAPM или WACC в эту модель не добавляем.

Методика / Исторические числа

Три ориентира исходного примера

Используем эти величины как условные параметры упражнения.

Методика / Границы ориентира

Рассчитываем среднее

Из 12,91%, 7% и 16,5% получаем 12,1366666667%, или 12,14% при двух знаках после запятой. В формуле сохраняем полную точность, округляем только отображение.

Полученное среднее ниже исходной инфляции 12,91%. Используем его как заданную ставку упражнения.

Шаг 1 / Ставка в Excel

Добавляем ставку дисконтирования

На листе «Исходные данные» вводим в B309 «Ставка дисконтирования», в G310 — «Ставка дисконтирования для проекта». Применяем к B309 стиль «Заголовок1(к)», к G310 — «Обычный». H310 оставляем пустой, как в исходном блоке. Перед вводом формул выбираем «Формулы → Параметры вычислений → Автоматически».

=(7%+12,91%+16,5%)/3

К J310 применяем стиль «Процент_0,00%»: Excel покажет 12,14%. Не заменяем формулу округлённой константой. Для длинной подписи в G310 включаем «Главная → Перенос текста», затем «Формат → Автоподбор высоты строки».

Выделяем строку 310 и выполняем «Данные → Группировать → Строки». Заголовок B309 должен остаться виден при сворачивании. Проверяем, что J310 хранит формулу, а не текст, и что значение равно среднему трёх исходных процентов. Сохраняем рабочую копию; файл для сверки результата — «М07_После_урока_01.xlsx».

Шаг 1 / Ставка в Excel / Сверка результата

Сверка в Excel

Добавляем ставку дисконтирования. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Шаг 1 / Структура

Денежный поток проекта, NPV и IRR

Продолжаем ту же книгу после урока 1. На листе «Расчеты» вводим в B305 «Показатели инвестиционной привлекательности», в C306 — «IRR и NPV проекта». Для B305 задаём стиль «Заголовок1(к)», для C306 — «Заголовок3».

Подписи G307:G310 оформляем стилем «Обычный», G311:G312 — «Заголовок3». В H307:H309 и H311 указываем «руб.», в H310 и H312 — «%». К заполненным ячейкам столбца H применяем «ед_изм». Для G307:G312 включаем перенос текста и автоподбор высоты строк. Строку 313 сохраняем пустой.

Шаг 2 / Периодные потоки

Связываем поток с отчётом о движении денег

Проверяем автоматический режим вычислений. В P307 берём поток от операционной деятельности из строки 257, в P308 — поток от инвестиционной деятельности из строки 260.

=P257

=P260

=СУММ(P307:P308)

В строке 309 складываем два потока с их знаками. Инвестиционные выплаты уже отрицательные, поэтому повторно вычитать их нельзя.

В каждом из трёх рядов выделяем диапазон от P до EE и выполняем «Главная → Заполнить → Вправо» или Ctrl+R. Относительные ссылки перейдут в соответствующий столбец. Для P307:EE308 задаём стиль «Обычный», для P309:EE309 — «Л_ИТОГО»; денежный формат — учётный, два десятичных знака, без обозначения валюты.

Шаг 3 / Итоги и начальная дата

Отделяем суммы от технического начального значения

=СУММ(P307:EE307)

=СУММ(P308:EE308)

=-0,00000001

O307 и O308 — суммы операционных и инвестиционных потоков по всем 120 столбцам P:EE; применяем стиль «СУММА». O309 оформляем стилем «Тех_вставка» с тем же денежным форматом.

O309 не является итогом строки 309. В ней задаём техническое начальное значение −0,00000001 на дату O6. Это малая искусственная величина, а не финансовый платёж. Проверяем содержимое в строке формул.

O309 не заменяем суммой или видимым нулём.

Шаг 4 / Расчёт по датам

Рассчитываем NPV и IRR проекта

='Исходные данные'!$J$310

=ЧИСТНЗ($I$310;$O$309:$CA$309;$O$6:$CA$6)

=ЧИСТВНДОХ($O$309:$CA$309;$O$6:$CA$6)

I310 получает ставку с листа «Исходные данные». Для I310 и O312 используем стиль «Процент_0,00%», для O311 — «СУММА» с денежным форматом, два десятичных знака.

ЧИСТНЗ рассчитывает приведённую стоимость с учётом дат, ЧИСТВНДОХ — годовую внутреннюю норму доходности для такого календаря. Здесь есть месячные и квартальные периоды, поэтому используем функции по датам. Обычными ЧПС и ВСД их не заменяем.

В обоих случаях диапазон денежных потоков O309:CA309 и диапазон дат O6:CA6 имеют одинаковую длину. Даты должны быть числовыми датами Excel; их начальная точка — O6. Описание функций: ЧИСТНЗ / XNPV и ЧИСТВНДОХ / XIRR.

Шаг 5 / Вывод результатов

Показываем показатели рядом с подписями

=O311

=O312

J311 оформляем стилем «Выход_данные» и денежным форматом с двумя десятичными знаками, J312 — «Процент_0,00%». Строки 310:312 содержат отдельные параметры и результаты: формулы O311/O312 и J311/J312 не нужно протягивать по периодам. P310:EE312 оставляем пустыми.

В исходном календаре O6 соответствует 31.12.2014, а CA6 — 31.12.2024. В P:CA находится 64 активных периода. Все периодные формулы мы заполнили до EE, но в ЧИСТНЗ и ЧИСТВНДОХ используется только O:CA. Конечный столбец не выбирается автоматически: при изменении календаря его нужно проверять вместе с составом потоков.

Убеждаемся, что отрицательные инвестиционные и положительные операционные значения попадают в расчёт. ЧИСТВНДОХ требует потоки обоих знаков и использует итерационный поиск; наличие формулы само по себе не гарантирует, что расчёт сойдётся при любом сценарии.

Шаг 5 / Вывод результатов / Сверка результата

Сверка в Excel · 1/2

Показываем показатели рядом с подписями. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Шаг 5 / Вывод результатов / Сверка результата

Сверка в Excel · 2/2

Показываем показатели рядом с подписями. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Проверка / Состав потока

Что именно измеряет исходный показатель

В строке 309 нет поступлений акционерного и заёмного финансирования, а также прямых выплат процентов и тела долга. Однако поток не полностью независим от финансирования: налог на прибыль в строке 255 связан с расчётом 240 → 237, а прибыль до налога учитывает выбранные проценты из строки 231.

При смене долговой схемы налог и рассчитанный IRR проекта могут измениться. Бездолговой денежный поток отдельно не рассчитываем.

Расчёт сохраняет два списания НДС и связанный актив из М6. Ставки НДС и налога на прибыль — 18% и 20%.

Завершение / Самопроверка

Сохраняем показатели проекта

Проверяем три заполненных ряда P307:EE309, суммы в O307/O308, отдельное техническое начальное значение O309 и четыре ячейки вывода O311/O312/J311/J312. Ставка I310 должна совпадать с J310 листа «Исходные данные», а J311/J312 — с O311/O312.

В базовом примере NPV составляет около 6 713,35 руб., IRR — 27,32%. При расхождении проверяем календарь, выбранную долговую схему и налоговые расчёты.

Сначала группируем строки 306:313, затем внутри них — 307:313 через «Данные → Группировать → Строки». B305 остаётся вне внешней группы, C306 — вне вложенной. Сохраняем рабочую копию. Файл для сверки результата — «М07_После_урока_02.xlsx».

Шаг 1 / Поток кредиторов

Доходность кредиторов и акционеров

Продолжаем рабочую книгу после урока 2. Доходность участников зависит от сумм и дат денежных потоков. Для кредиторов выдача финансирования — отток, проценты и возврат тела долга — притоки. На листе «Расчеты» вводим в C314 «IRR кредиторов» и применяем стиль «Заголовок3».

G315:G318 оформляем стилем «Обычный», G319 — «Заголовок3». В H315:H318 указываем «руб.», в H319 — «%»; стиль «ед_изм». Для длинных подписей G315:G319 включаем перенос текста и автоподбор высоты строк. Строка 320 остаётся пустой.

Шаг 2 / Знаки и итоги

Рассчитываем поток со стороны кредитора

=-P264

=-P266

=-P267

=СУММ(P315:P317)

Формулы меняют знак соответствующих потоков проекта: привлечение займа в строке 264 становится выдачей кредита, а выплаты по строкам 266/267 — поступлениями кредитору. В строке 318 суммируем все три составляющие.

Каждый из четырёх рядов заполняем вправо от P до EE через Ctrl+R. Для P315:EE317 задаём стиль «Обычный», для P318:EE318 — «Л_ИТОГО». Во всех четырёх рядах используем учётный денежный формат с двумя десятичными знаками без обозначения валюты.

Шаг 3 / IRR кредитора

Сводим поток и рассчитываем IRR

=СУММ(P315:EE315)

=СУММ(P316:EE316)

=СУММ(P317:EE317)

=-0,00000001

=ЧИСТВНДОХ($O$318:$CA$318;$O$6:$CA$6)

O315:O317 оформляем стилем «СУММА» и денежным форматом. O318 — «Тех_вставка»: это техническое начальное значение −0,00000001 на дату O6, а не сумма денежных потоков. O319 оформляем стилем «Процент_0,00%».

ЧИСТВНДОХ использует поток O318:CA318 и соответствующие даты O6:CA6. Столбец CA — конец исторического календаря примера. В P319:EE319 ничего не вводим: IRR — один результат для выбранного ряда, а не отдельная формула каждого периода.

Шаг 4 / Сравнение ставок

Сопоставляем IRR с договорной ставкой

='Исходные данные'!J223

=O319

I319 показывает заданную в исходных данных ставку кредита, J319 — рассчитанный IRR кредитора. Обе ячейки оформляем стилем «Процент_0,00%». Убеждаемся, что J319 совпадает с O319, а I319 — с J223 листа «Исходные данные».

В сохранённом исходном примере задано 15%, а IRR кредитора составляет около 10,46%. Это свойство конкретного графика: до эксплуатации предусмотрено 791 день без начисления процентов; отложенные за этот срок проценты также не капитализируются. Поэтому договорную ставку нельзя отождествлять с доходностью всего потока с учётом дат.

Этот результат не означает, что эффективная доходность любого кредита ниже его ставки. При изменении условий проверяем выдачи, даты и выплаты. Здесь сохраняются выбранная схема обслуживания и календарная долговая фаза исходного упражнения.

Шаг 4 / Сравнение ставок / Сверка результата

Сверка в Excel

Сопоставляем IRR с договорной ставкой. Проверяем адреса и значения по своей книге; полный ряд остаётся в Excel.

Шаг 5 / Копия блока

Подготавливаем блок акционеров

Копируем диапазон A314:EE319 и вставляем его начиная с A321 обычной вставкой ячеек, без вставки строк. Получится шесть скопированных строк 321:326. Блок акционеров занимает только 321:325, поэтому очищаем содержимое A326:EE326 и P325:EE325. Так убираем лишние копии формул внутри нового блока. Существующие строки листа сохраняем.

Для C321 и G325 задаём «Заголовок3», для G322:G324 — «Обычный». H322:H324 — «руб.», H325 — «%» со стилем «ед_изм». I325 оставляем пустой. Для G322:G325 включаем перенос и автоподбор высоты. Далее заменяем все скопированные расчёты O322:O325 и P322:EE324 формулами потока акционеров.

Шаг 6 / Условный поток

Рассчитываем доступные акционерам деньги

=-P263

=P268*(P18=1)*(P22=0)+ЕСЛИ(И(O22=1;P22=0;P18=1);P273;0)

=СУММ(P322:P323)

Капитал из строки 263 меняет знак: для акционера это вложение. Первая часть строки 323 допускает поток 268 только при эксплуатации (строка 18 равна 1) и вне долговой фазы (строка 22 равна 0).

Вторая часть добавляет начальный денежный остаток строки 273 один раз — когда предыдущий период ещё относился к долговой фазе, текущий уже не относится, а эксплуатация продолжается. Строка 324 складывает вложения и этот условно доступный поток.

Заполняем все три ряда от P до EE. Для P322:EE323 задаём «Обычный», для P324:EE324 — «Л_ИТОГО»; используем денежный формат с двумя десятичными знаками.

Шаг 7 / IRR акционеров

Выводим результат условного расчёта

=СУММ(P322:EE322)

=СУММ(P323:EE323)

=-0,00000001

=ЧИСТВНДОХ($O$324:$CA$324;$O$6:$CA$6)

=O325

O322/O323 — суммы со стилем «СУММА» и денежным форматом. O324 — техническое начальное значение −0,00000001 со стилем «Тех_вставка», а не итог вложений и поступлений. O325 и J325 оформляем стилем «Процент_0,00%». Результат находится именно в J325; I325 остаётся пустой.

Не протягиваем формулы IRR по строке 325. Проверяем, что O325 и J325 совпадают. В O324:CA324 должны быть отрицательные вложения и положительные значения условно доступного потока, а в O6:CA6 — соответствующие даты.

Проверка / Смысл потока

Условно доступные деньги и дивиденды

Строка 323 показывает условно доступные распределения, а не фактически выплаченные дивиденды. В ней показываем доступный поток после завершения календарной долговой фазы и добавляем накопленные деньги в первом периоде после её завершения. В исходном примере этот переход приходится на I квартал 2023 года.

Строка фактических дивидендов 269 остаётся пустой; соответствующие выплаты равны нулю. Условное распределение из 323 не уменьшает денежный остаток 274 и деньги в балансе 292. Поэтому рассчитанный IRR акционеров нельзя называть фактической доходностью выплаченных дивидендов или обещанным результатом для инвестора.

В сохранённом исходном примере O322 составляет −3 000,00 руб., O323 — около 22 813,81 руб., а J325 — около 27,94%. Эти значения описывают условный поток данной модели.

Даже если схема «Всё на долг» погасила основной долг раньше, формула 323 не переносит автоматически завершение календарного флага строки 22. Условие распределения остаётся прежним.

Проверка / Смысл потока / Сверка результата

Сверка в Excel · 1/2

Условно доступные деньги и дивиденды. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Проверка / Смысл потока / Сверка результата

Сверка в Excel · 2/2

Условно доступные деньги и дивиденды. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Объяснение / учебный опыт

Проверяем изменение сроков финансирования

Для эксперимента меняем сроки привлечения и возврата кредита в отдельной рабочей копии. После изменения дат проверяем календарные флаги, состав выплат и границу CA в формулах по датам.

Завершение / Самопроверка

Сохраняем два расчёта доходности

Проверяем четыре периодных ряда кредиторов 315:318 и три ряда акционеров 322:324: каждый заполнен от P до EE. В O315:O317 и O322:O323 находятся суммы, в O318/O324 — технические начальные значения, в O319/O325 — IRR. Ячейки I319/J319 и J325 оформлены процентами.

В P319:EE319 и P325:EE325 формул быть не должно. Строки 320 и 326 остаются пустыми. В исходном календаре на границе BS → BT, IV квартал 2022 года → I квартал 2023 года, проверяем завершение долгового флага и однократное включение накопленных денег в строку 323. Фактические дивиденды строки 269 от этого не появляются.

Продлеваем внешнюю группу: выделяем строки 314:326 и группируем их, чтобы вместе с 306:313 она охватывала 306:326. Затем создаём вложенные группы 315:320 и 322:325. C314 и C321 останутся вне вложенных групп. Используем «Данные → Группировать → Строки».

Сохраняем рабочую копию. Файл для сверки — «М07_После_урока_03.xlsx». Далее переходим к сроку окупаемости и индексу доходности.

1 / Подписи и смысл

Подготавливаем блок срока окупаемости

Продолжаем книгу после урока 7.3. Сохраняем исторический пример 2015–2024 годов и его параметры, включая условные ставки НДС 18% и налога на прибыль 20%. Новых рыночных допущений здесь нет.

PB показывает, когда проект возвращает вложения. В этой модели строка 261 уже объединяет денежный поток с отрицательными инвестициями; поэтому граница возврата — переход накопленного значения через ноль. Сравнивать строку 328 с суммой инвестиций повторно не нужно.

Включаем перенос длинных подписей и автоподбор высоты строк. Сохраняем исходные адреса: это общий блок показателей эффективности, продолжающий уроки 7.1–7.3.

2 / Накопление и флаг

Отмечаем периоды с накопленным потоком не выше нуля

=СУММ($P$261:P261)

=1*(P328<=0)

='Исходные данные'!P116*P329

Каждую из трёх формул протягиваем вправо до EE: P328:EE328, P329:EE329 и P330:EE330. Абсолютная начальная ссылка $P$261 остаётся на месте, правая граница суммы сдвигается. В P330 используется длительность соответствующего периода из строки 116 исходных данных; используем фактическое имя листа «Исходные данные» без завершающего пробела.

Флаг равен 1, если накопленный поток на конец периода меньше или равен нулю. Умножая длительность периода на флаг, учитываем только такие периоды. При положительном накопленном потоке результат равен нулю. Строка 331 не заполняется вправо: P331:EE331 оставляем пустыми.

3 / Итоги

Выводим срок окупаемости в годах

=МАКС(P328:EE328)

=СУММ(P330:EE330)

=O330/Число_дней_в_году

=O331

O329 оставляем пустой. O328 — максимум накопленного потока, а не сумма его накопленных значений. O330 — сумма дней тех периодов, в конце которых накопленный поток не выше нуля. Существующее имя «Число_дней_в_году» ссылается на J85 листа «Исходные данные» и равно 365; нового имени не создаём.

В базовом примере получаем O328 = 22 558,99 руб.; O330 = 2 008 дней; O331 = 5,501369863… года. При двух знаках O331 отображается как 5,50. Исходный формат J331 содержит один знак и слово «лет», поэтому там видно «5,5 лет». Число знаков после запятой влияет только на отображение. Значение и формула не меняются.

4 / Оформление PB

Оформляем флаг и итог срока

Единицы в столбце H: H328 — «руб.»; H329 — «Флаг»; H330 — «дней»; H331 — «лет». Стиль единиц — «ед_изм».

Для P329:EE329 задаём исходное условное форматирование: значение ячейки равно 1, узор заливки lightUp с цветом темы 9. Обычная заливка стиля «Флаг» остаётся базой для остальных значений. Числа 1 и 0 в формулах не заменяем вручную.

Продлеваем существующую внешнюю группу строк 306:326 до 336: строки 327:336 должны иметь уровень 1. Внутри группируем 328:331 до уровня 2. Не создаём вторую перекрывающуюся внешнюю группу: она повысит уровень лишний раз. Строка 337 — пустой разделитель вне группы.

Проверка / Граница PB

Проверяем границу окупаемости

Сверяем накопленный поток: BI328 на 30.06.2020 равен −894,90 руб., BJ328 на 30.09.2020 — 280,45 руб. Расчётные 5,50 года соответствуют началу квартала перехода в плюс.

При отсутствии перехода через ноль формула возвращает весь горизонт. Проверяем весь ряд 328: периоды, в которых накопленный поток снова стал нулевым или отрицательным, также включаются в расчёт.

Проверка / Граница PB / Сверка результата

Сверка в Excel

Проверяем начало квартала окупаемости: 5,50 года. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

5 / Подписи PI

Подготавливаем индекс доходности

В H334 и H335 указываем «руб.», в H336 — «индекс». Для этих единиц используем стиль «ед_изм». Индекс рассчитывается в итоговом столбце O и выводится в J336; P334:EE336 оставляем пустыми.

6 / Расчёт PI

Связываем инвестиции и NPV

=-O259

=O311

=O335/O334+1

=O336

O259 содержит отрицательные капвложения, поэтому знак минус в O334 даёт положительную инвестиционную базу. В O335 получаем NPV из O311. Формула O336 делит NPV на эту базу и добавляет 1; J336 повторяет итог без нового расчёта.

Сохраняем условные ставки и расчёты предыдущих уроков. Этот шаг не меняет финансирование, дивиденды или состав денежного потока.

Проверка / Смысл PI

Сверяем индекс и инвестиционную базу

В O334 используем номинальные капвложения. Индекс рассчитываем как 1 + NPV / O334; капвложения в знаменателе не дисконтируем.

Сверяем показатели с таблицей и сохраняем «М07_После_урока_04.xlsx».

Проверка / Смысл PI / Сверка результата

Сверка в Excel

Сверяем индекс и его инвестиционную базу. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Самопроверка

Проверяем понимание PB и PI

1. Почему накопленный поток в строке 328 сравнивается с нулём?

   Строка 261 уже включает отрицательные инвестиции. Повторное сравнение с суммой инвестиций вычло бы их второй раз.

2. Что означает результат PB 5,50 года в базовом примере?

   Алгоритм не учёл дни квартала перехода в плюс, поэтому результат соответствует началу этого квартала; это не точный момент возврата инвестиций внутри квартала.

3. Доказывает ли число в O331 окупаемость, если все значения строки 328 не выше нуля?

   Нет. В этом случае формула суммирует дни всего горизонта; переход через ноль нужно проверять отдельно.

4. Какая инвестиционная база стоит в знаменателе PI 1,67?

   Положительная номинальная сумма капвложений −O259, а не их дисконтированная стоимость.

1 / Подписи

Подготавливаем блок рентабельности

Продолжаем книгу после урока 7.4. Здесь используем начисленную выручку и прибыль отчёта о прибылях и убытках. Оплаты покупателей и денежные остатки не подставляем в знаменатель.

ROS рассчитываем как валовая прибыль / выручка. По используемому числителю этот показатель соответствует валовой марже. NPM — чистая прибыль / выручка. Оба показателя рассчитываем за каждый период и за весь проект.

Включаем перенос длинных подписей и автоподбор высоты строк. Пустые строки 343 и 348 сохраняют разделение блоков.

2 / ROS по периодам

Связываем выручку и валовую прибыль

=P243

=P245

=ЕСЛИОШИБКА(P341/P340;0)

Протягиваем каждую формулу вправо до EE. Строка 340 повторяет выручку из строки 243, строка 341 — валовую прибыль из строки 245. В строке 342 делим валовую прибыль на выручку. При положительной выручке доля показывает, сколько валовой прибыли приходится на рубль выручки.

ЕСЛИОШИБКА возвращает 0 при ошибке деления. В периодах без выручки это техническая подстановка для 0 / 0, а не измеренная рентабельность нулевых продаж. Такая защита также скрывает другие ошибки выражения, поэтому корректность входных строк 340 и 341 проверяем отдельно.

3 / ROS за проект

Делим общую валовую прибыль на общую выручку

=СУММ(P340:EE340)

=СУММ(P341:EE341)

=ЕСЛИОШИБКА(O341/O340;0)

=O342

O340 и O341 суммируют соответствующие периодные ряды. O342 делит общую валовую прибыль на общую выручку; не заменяем его простым средним процентов по периодам. Вклад каждого периода в итог зависит от его выручки.

J342 повторяет итог O342 для удобства чтения формы. Это один показатель, показанный в двух ячейках.

4 / NPM по периодам

Используем чистую прибыль

=P243

=P249

=ЕСЛИОШИБКА(P346/P345;0)

Протягиваем каждую формулу вправо до EE. Выручку берём из той же строки 243, чистую прибыль — из строки 249. В числителе NPM уже учтены проценты и налог по сохранённым правилам примера, поэтому повторно вычитать их здесь не нужно.

При отсутствии выручки ноль в строке 347 так же является технической подстановкой ЕСЛИОШИБКА. Он не доказывает отсутствие убытка: саму чистую прибыль проверяем в строке 346.

5 / NPM за проект

Рассчитываем NPM за весь проект

=СУММ(P345:EE345)

=СУММ(P346:EE346)

=ЕСЛИОШИБКА(O346/O345;0)

=O347

O345 — общая выручка; она должна совпасть с O340. O346 — сумма чистой прибыли. O347 — отношение общих сумм. J347 повторяет итог O347 для удобства чтения формы.

Эти формулы не меняют ОПУ, график кредита или фактические дивиденды. NPM характеризует начисленную прибыль относительно выручки и не заменяет денежный поток либо доходность акционеров.

6 / Оформление

Сохраняем исходную точность отображения

В H340, H341, H345 и H346 указываем «руб.», в H342 и H347 — «%». Стиль всех единиц — «ед_изм».

Внешняя группа — строки 339:348, уровень 1. Вложенные группы — 340:342 и 345:347, уровень 2. B338 остаётся вне групп. Пустой разделитель 343 имеет уровень 1, пустая строка 348 также входит во внешнюю группу.

В исходном стиле процентные результаты показываются без десятичных знаков: ROS — 62%, NPM — 47%. Более точные значения 61,80% и 46,68% видны при увеличении числа знаков после запятой; при этом меняется только отображение, а не расчёт показателя. Для сверки можно временно показать два знака и затем вернуть исходный 0%.

Проверка / Итоги

Сверяем числители, знаменатели и доли

Ориентиры относятся к базовому дифференцированному графику и сохранённым условиям учебного примера. После переноса формул проверяем полные ряды и их итоги; одно совпавшее округлённое число не заменяет такую проверку.

Сохраняем результат как «М07_После_урока_05.xlsx». Расчётная часть этой главы завершена. Следующий шаг — годовая «Отчетность», где периодные потоки и остатки будут представлены за 2015–2024 годы. Лист «Резюме» относится к следующему модулю.

Проверка / Итоги / Сверка результата

Сверка в Excel · 1/2

Сверяем числители, знаменатели и доли. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Проверка / Итоги / Сверка результата

Сверка в Excel · 2/2

Сверяем числители, знаменатели и доли. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Самопроверка

Проверяем понимание рентабельности

1. Какой результат стоит в числителе ROS?

   Валовая прибыль из строки 245. По составу числителя это валовая маржа.

2. Что означает 0% в P342 или P347 при нулевой выручке?

   Это техническая подстановка ЕСЛИОШИБКА после деления на ноль, а не измеренная рентабельность отсутствующих продаж.

3. Почему итог O342 нельзя заменять средним процентов по периодам?

   Исходная формула делит общую валовую прибыль на общую выручку; простое среднее долей по периодам учитывает каждый период с одинаковым весом.

4. Почему 62% / 47% в Excel согласуются с 61,80% / 46,68% в проверке?

   Стиль Проценты_0% округляет отображение до целого процента. При временном увеличении числа знаков видны 61,80% и 46,68%; формулы и значения не меняются.

Начало / Годовая форма

Годовая отчётность

В предыдущих уроках модель рассчитывала месяцы и кварталы. Теперь сводим её результаты в десять годовых столбцов, чтобы сравнивать годы и видеть общую картину. Получатся годовые формы прибыли, движения денег и баланса.

В годовой форме объединяем показатели уже построенной учебной модели.

Продолжаем рабочую копию после урока 5. Входной файл для сверки — «М07_После_урока_05.xlsx». Нажимаем на ярлык листа «Расчеты», удерживаем Ctrl и перетаскиваем ярлык вправо: должен появиться дубликат «Расчеты (2)». Переименовываем только его в «Отчетность» и задаём ярлыку красный цвет #FF0000 через меню ярлыка → «Цвет ярлычка».

Все дальнейшие удаления строк и изменения в этом уроке выполняем только на новом листе «Отчетность». Исходный лист «Расчеты» сохраняет все месячные и квартальные расчёты.

Шаг 1 / Копия листа

Подготавливаем структуру годовой формы

На новом листе «Отчетность» раскрываем группы и выделяем целые строки 27:241 через поле имени. Выполняем «Главная → Удалить → Удалить строки с листа». Удаляется расчётная часть перед отчётом о прибылях и убытках; сам заголовок отчёта сохраняется и временно перемещается в B27.

В дубликате появляются нарушенные ссылки на удалённые расчёты. Последовательно заменяем их ссылками на сохранённый лист «Расчеты». Не подменяем ошибки нулями. Включаем «Формулы → Параметры вычислений → Автоматически».

В A1 должна по-прежнему находиться формула, которая показывает название листа. После сохранения рабочей книги она показывает «Отчетность». Служебные подписи B2/B3 и заголовки G4:O4 сохраняются. Поля G2/G3 пока пусты: сценарий и резюме создаются в следующем модуле.

После этого этапа исправляем календарь. Второе удаление строк 11:14 описано после ввода годовых признаков: выполняем действия по порядку, чтобы адреса совпадали с инструкцией.

Оформление / Годовые столбцы

Настраиваем ширины нового годового листа

Только на «Отчетности» задаём ширины по таблице. Выделяем указанные целые столбцы через поле имени, затем «Главная → Формат → Ширина столбца». Вводим число из правого столбца. Ширины на прежних листах сохраняются.

Это значения окна настройки ширины Excel. После заполнения длинных подписей включаем перенос текста и автоподбор высоты строк. Неиспользуемые столбцы EF:XFD скрываем. Закрепление P22 настроим после календаря.

Шаг 2 / Границы года

Перестраиваем начало и конец периода

Проверяем исходную дату в O6, затем заменяем первые формулы дат на годовые.

='Исходные данные'!O6

=ЕСЛИ(O6<Дата_конца_проекта;O6+1;" ")

=ЕСЛИ(O6<Дата_конца_проекта;КОНМЕСЯЦА(P5;Число_месяцев_в_году-1);" ")

Заполняем P5:EE5 и P6:EE6 вправо через Ctrl+R. Сначала заполняем даты начала периода, затем даты конца. Для O6 и P5:EE6 сохраняем стиль «Дата» и формат «Д МММ ГГ».

Первый год начинается 01.01.2015 и заканчивается 31.12.2015; следующий начинается на следующий день. КОНМЕСЯЦА с числом месяцев в году минус один находит декабрь. После окончания проекта формулы возвращают один пробел, как в исходной конструкции.

Здесь проверяется календарь полных лет 2015–2024. Формула не обрезает последний год по произвольной дате окончания внутри года; неполные первый и последний годы в этом уроке не рассматриваем.

Шаг 3 / Годы

Оставляем годовые признаки

=ЕСЛИОШИБКА(ГОД(P5);" ")

=ЕСЛИ(P5=" ";0;P9-$P$9+1)

=МАКС(P9:EE9)

Заполняем P9:EE9 и P10:EE10 вправо до EE. В строке 9 находятся календарные годы, в строке 10 — порядковые номера. O9 показывает максимальный год и вводится отдельно. Используем стиль «Обычный»: строка 9 и O9 имеют общий формат, строка 10 сохраняет исходное числовое оформление.

Теперь только на «Отчетности» удаляем целые строки 11:14 — квартальные и месячные признаки. Заголовок отчёта о прибылях и убытках переместится в B23. Фазы строительства и эксплуатации окажутся в строках 13/14, финансирование — 17/18, фактические данные — 20.

Проверяем P9:Y9: годы 2015–2024; P10:Y10: номера 1–10. Z:EE остаются неактивным продолжением формул: год и даты содержат пробел, номер периода — ноль. Не очищаем это продолжение и не заменяем исходные пустые ячейки других строк константами 0.

Шаг 4 / Годовые флаги

Пересчитываем фазы на годовом календаре

=ЕСЛИ(P5<Дата_конца_строительства;1;0)

=ЕСЛИ(И(P5>=Дата_начала_эксплуатации;P6<=Дата_конца_эксплуатации);1;0)

=ЕСЛИ(P5<Дата_конца_акционерного_финансирования;1;0)

=ЕСЛИ(И(P5>=Дата_начала_заемного_финансирования;
P6<=Дата_конца_заемного_финансирования);1;0)

=ЕСЛИ(И(P5>=Дата_начала_фактических_данных;
P6<=Дата_конца_фактических_данных);1;0)

Каждый из пяти рядов заполняем от P до EE. Сохраняем стиль «Флаг» и единицы «Флаг» в H13/H14/H17/H18/H20. Подписи G13/G14/G17/G18/G20 остаются: «Строительство», «Эксплуатация», «Акционерное финансирование», «Долг», «Фактические данные».

Долговой флаг срабатывает, только если весь год находится внутри долговой фазы. Поэтому флаг 2015 и 2022 годов равен 0, хотя в этих годах есть выдача или погашение займа. Флаги не заменяют денежные суммы и не означают отсутствия платежей. Ниже суммируем годовые обороты по году исходных периодов, а не умножаем их на эти флаги.

Шаг 5 / Вид календаря

Проверяем оформление и закрепление

Через «Главная → Условное форматирование → Управление правилами → Этот лист» проверяем диапазоны годового листа. Для каждой строки флагов 13, 14, 17, 18 и 20 действует правило «значение ячейки равно 1» на P:EE.

Для каждой строки дат P5:EE5 и P6:EE6 нужны три условия по текущему столбцу: =P$20=1 для фактических данных, =P$14=1 для эксплуатации и =P$13=1 для строительства. После удаления строк ссылки должны указывать именно на эти годовые флаги. Сохраняем порядок правил, заливки, узоры и границы соответствующих фаз. В пользовательском формате каждого правила задаём чёрный шрифт #000000: при срабатывании правила он должен применяться и к датам, и к активным флагам. Обычный светлый цвет нулевых флагов не меняем.

Позже добавляем правило для проверок денежных потоков P57:EE58. Всего на этом годовом листе должно быть 12 правил; лишних копий после преобразования листа оставаться не должно. Меняем эти правила только на новой «Отчетности».

Снимаем закрепление областей, нажимаем Ctrl+Home и при необходимости временно уменьшаем масштаб, чтобы были видны A:P и строки 1:22. Выбираем P22 и выполняем «Вид → Закрепить области → Закрепить области». Теперь закреплены A:O и строки 1:21. Возвращаем удобный масштаб.

Для длинных подписей включаем перенос текста и автоподбор высоты строк. Заполненные ячейки единиц H сохраняют стиль «ед_изм». Столбцы EF:XFD остаются скрытыми.

Шаг 5 / Вид календаря / Сверка результата

Сверка в Excel · 1/3

Проверяем оформление и закрепление. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Шаг 5 / Вид календаря / Сверка результата

Сверка в Excel · 2/3

Проверяем оформление и закрепление. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Шаг 5 / Вид календаря / Сверка результата

Сверка в Excel · 3/3

Проверяем оформление и закрепление. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Шаг 6 / Прибыль

Проверяем строки годового отчёта о прибыли

После удаления строк в B23 остаётся «Отчет о прибылях и убытках». Проверяем соответствие строк годовой формы исходным расчётам.

Подписи и знаки «(-)» в F25/F27/F29 сохраняются после копирования. B23 имеет стиль «Заголовок1(к)», подписи C — «Заголовок3», подписи G — «Обычный». Единицы H24:H30 — рубли со стилем «ед_изм». Все денежные значения показываем в учётном формате с двумя десятичными знаками без обозначения валюты.

СУММЕСЛИ будет выбирать все месяцы и кварталы одного года по строке 9 листа «Расчеты». Выручка и расходы не усредняются, а суммируются с их исходными знаками.

Шаг 7 / Годовые обороты

Суммируем основные статьи прибыли

=СУММЕСЛИ(Расчеты!$P$9:$EE$9;Отчетность!P$9;Расчеты!$P$243:$EE$243)

=СУММЕСЛИ(Расчеты!$P$9:$EE$9;Отчетность!P$9;Расчеты!$P$244:$EE$244)

=СУММЕСЛИ(Расчеты!$P$9:$EE$9;Отчетность!P$9;Расчеты!$P$246:$EE$246)

=СУММЕСЛИ(Расчеты!$P$9:$EE$9;Отчетность!P$9;Расчеты!$P$248:$EE$248)

Первый аргумент СУММЕСЛИ — закреплённый диапазон лет исходных периодов. Второй — год текущего столбца «Отчетности», третий — диапазон исходных денежных значений. Заполняем каждый из четырёх рядов от P до EE. Стиль P24:EE25, P27:EE27 и P29:EE29 — «Обычный», денежный формат — два десятичных знака.

При заполнении вправо меняется только столбец года в условии; диапазоны листа «Расчеты» должны остаться закреплёнными. В исходном примере активны десять лет P:Y, а продолжение Z:EE возвращает нулевые обороты.

Шаг 8 / Подытоги прибыли

Сохраняем расчёт подытогов

=СУММ(P24:P25)

=СУММ(P26:P27)

=СУММ(P28:P29)

Эти суммирующие строки остаются после копирования. Сверяем их формулы и заполнение до EE: для валовой прибыли прибавляем к выручке отрицательные расходы, для прибыли до налога — к валовой прибыли отрицательные проценты, для чистой прибыли — к прибыли до налога отрицательный налог.

Для P26:EE26 сохраняем «Л_Операция», для P28:EE28 — «Л_ИТОГО», для P30:EE30 — «Л_ВСЕГО». Денежный формат у всех трёх рядов — два десятичных знака. Не меняем знаки в суммах: расходы и налог уже отрицательные.

Шаг 9 / Итоги прибыли

Сводим десять лет в столбце O

=СУММ(P24:EE24)

=СУММ(P25:EE25)

=СУММ(P26:EE26)

=СУММ(P27:EE27)

=СУММ(P28:EE28)

=СУММ(P29:EE29)

=СУММ(P30:EE30)

Сверяем или вводим эти формулы и задаём O24:O30 стиль «СУММА» с денежным форматом. Они суммируют весь диапазон P:EE; неактивные годы не должны добавлять обороты. Это суммы потоков за весь срок проекта.

Шаг 10 / Контроль прибыли

Сверяем годовые и периодные итоги

=O24-Расчеты!O243

=O25-Расчеты!O244

=O26-Расчеты!O245

=O27-Расчеты!O246

=O28-Расчеты!O247

=O29-Расчеты!O248

=O30-Расчеты!O249

K24 можно заполнить вниз до K30: оба незакреплённых номера строк увеличиваются вместе. Сравниваем результат с приведёнными выражениями. Для K24:K30 применяем «ТРЕВОГА»: формат «"ОШИБКА!";"ОШИБКА!";"Ok"» показывает нулевое расхождение как Ok.

Контроль проверяет итог за весь горизонт, а не распределение внутри каждого года. Дополнительно выбираем один год, например 2018, и сравниваем его четыре исходных квартала с годовым столбцом S. Нулевой общий контроль сам по себе не обнаружит перенос суммы из одного года в другой.

Шаг 10 / Контроль прибыли / Сверка результата

Сверка в Excel · 1/3

Сверяем годовые и периодные итоги. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Шаг 10 / Контроль прибыли / Сверка результата

Сверка в Excel · 2/3

Сверяем годовые и периодные итоги. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Шаг 10 / Контроль прибыли / Сверка результата

Сверка в Excel · 3/3

Сверяем годовые и периодные итоги. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Шаг 11 / Денежные потоки

Проверяем структуру годового ДДС

В B32 сохраняется исправленный заголовок «Отчёт о движении денежных средств». Основные подзаголовки — C33 «Операционная деятельность», C39 «Инвестиционная деятельность», C43 «Финансовая деятельность».

Сохраняем без изменений подписи скопированного листа, знаки в F и рубли в H. B32 — «Заголовок1(к)», подзаголовки C — «Заголовок3», подписи D — «Заголовок4», G — «Обычный». Для длинных подписей в G включаем перенос текста. В F37 вводим знак «±»; полная подпись возврата и уплаты НДС остаётся в G37. Для D46 после применения «Заголовок4» задаём размер 13 пт, сохранив подчёркивание; оставляем эту подпись без переноса, чтобы она полностью помещалась до единицы измерения H46.

Шаг 12 / Операционная деятельность

Переносим операционные денежные потоки

Копируем текст формулы из P29 отчёта о прибыли в P34. В последнем аргументе меняем строку исходных данных: 248 → 253.

=СУММЕСЛИ(Расчеты!$P$9:$EE$9;Отчетность!P$9;Расчеты!$P$253:$EE$253)

=СУММЕСЛИ(Расчеты!$P$9:$EE$9;Отчетность!P$9;Расчеты!$P$254:$EE$254)

=СУММЕСЛИ(Расчеты!$P$9:$EE$9;Отчетность!P$9;Расчеты!$P$255:$EE$255)

=СУММЕСЛИ(Расчеты!$P$9:$EE$9;Отчетность!P$9;Расчеты!$P$256:$EE$256)

Проверяем соответствие исходным строкам 253, 254, 255 и 256, затем каждый ряд заполняем до EE. Во всех четырёх рядах сохраняем стиль «Обычный» и денежный формат с двумя знаками. Суммы НДС переносятся по исходной модели вместе с её ограничениями, разобранными в М6.

Шаг 13 / Инвестиционная деятельность

Сводим операции и инвестиции

=СУММ(P34:P37)

=СУММЕСЛИ(Расчеты!$P$9:$EE$9;Отчетность!P$9;Расчеты!$P$259:$EE$259)

=СУММ(P40)

=СУММ(P38:P40)

P38 складывает четыре операционных потока. P40 суммирует годовые капвложения из строки 259 листа «Расчеты». P41 сохраняет итог инвестиционного раздела, P42 — суммарный поток проекта. В P42 диапазон включает строку 39 с заголовком; её периодные ячейки остаются пустыми и не добавляют сумму.

Заполняем все четыре ряда до EE. Для P38:EE38 и P41:EE41 — «Л_ИТОГО», P40:EE40 — «Обычный», P42:EE42 — «Л_ВСЕГО». Денежный формат — два десятичных знака. Строки 39 и 43 являются заголовками: периодные формулы в них не вводятся.

Шаг 14 / Финансирование

Суммируем финансирование и выплаты долга

=СУММЕСЛИ(Расчеты!$P$9:$EE$9;Отчетность!P$9;Расчеты!$P$263:$EE$263)

=СУММЕСЛИ(Расчеты!$P$9:$EE$9;Отчетность!P$9;Расчеты!$P$264:$EE$264)

=СУММЕСЛИ(Расчеты!$P$9:$EE$9;Отчетность!P$9;Расчеты!$P$266:$EE$266)

=СУММЕСЛИ(Расчеты!$P$9:$EE$9;Отчетность!P$9;Расчеты!$P$267:$EE$267)

Исходные ряды 263/264 содержат привлечение капитала и долга, 266/267 — выплаты процентов и тела долга. Заполняем каждый ряд от P до EE и сохраняем «Обычный» с денежным форматом, два знака.

Эти суммы выбираем по году из строки 9. Их нельзя обнулять по годовому долговому флагу 18: в 2015 и 2022 годах долговая фаза занимает только часть года, но финансирование или обслуживание есть, хотя этот флаг равен нулю.

Шаг 15 / Подытоги ДДС

Сохраняем связь всех денежных разделов

=СУММ(P42;P44:P45)

=СУММ(P46:P48)

=СУММ(P44:P45;P47:P48;P50)

=P38+P41+P51

Проверяем эти сохранённые подытоги и заполняем ряды до EE. P46 — деньги, доступные для обслуживания долга, P49 — поток после обслуживания долга, P51 — только финансовая деятельность, P52 — общий денежный поток трёх разделов.

P46:EE46 и P49:EE49 оформляем «Л_ИТОГО», P51:EE51 — «Л_ВСЕГО», P52:EE52 — «Обычный», везде денежный формат с двумя знаками. Не включаем P46 или P49 повторно в P52: это промежуточные суммы.

P50:EE50 остаются исходно пустыми со стилем «Пустая_ячейка»: фактических дивидендов нет. Условное распределение акционерам из строки 323 листа «Расчеты» сюда не переносится. Строка 50 участвует в финансовом подытоге как пустая, а не как искусственно добавленная выплата.

Шаг 16 / Итоги ДДС

Сводим операционный и инвестиционный разделы

=СУММ(P34:EE34)

=СУММ(P35:EE35)

=СУММ(P36:EE36)

=СУММ(P37:EE37)

=СУММ(P38:EE38)

=СУММ(P40:EE40)

=СУММ(P41:EE41)

=СУММ(P42:EE42)

Для этих ячеек используем «СУММА» и учётный формат с двумя знаками. O39 остаётся пустой ячейкой строки заголовка. Суммы годовых оборотов должны совпадать с соответствующими итогами листа с исходными периодами.

Шаг 17 / Итоги финансирования

Сводим финансовый раздел и общий поток

=СУММ(P44:EE44)

=СУММ(P45:EE45)

=СУММ(P46:EE46)

=СУММ(P47:EE47)

=СУММ(P48:EE48)

=СУММ(P49:EE49)

=СУММ(P50:EE50)

=СУММ(P51:EE51)

=СУММ(P52:EE52)

Сохраняем стиль «СУММА» и денежный формат с двумя знаками. O43 остаётся пустой ячейкой заголовка. O50 содержит исходную сумму пустого ряда дивидендов и возвращает ноль; сам диапазон P50:EE50 оставляем пустым.

Шаг 18 / Контроли ДДС

Проверяем первые статьи движения денег

=O34-Расчеты!O253

=O35-Расчеты!O254

=O36-Расчеты!O255

=O37-Расчеты!O256

=O38-Расчеты!O257

=O39-Расчеты!O258

=O40-Расчеты!O259

=O41-Расчеты!O260

=O42-Расчеты!O261

=O43-Расчеты!O262

Заполняем формулы проверок вниз: незакреплённые номера строк меняются. Сверяем полученные формулы с приведёнными выражениями. K39/K43 сохранены и для строк заголовков: сравниваются соответствующие пустые ячейки O. Формат всех K — «ТРЕВОГА».

Шаг 18 / Контроли ДДС / Сверка результата

Сверка в Excel · 1/2

Проверяем первые статьи движения денег. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Шаг 18 / Контроли ДДС / Сверка результата

Сверка в Excel · 2/2

Проверяем первые статьи движения денег. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Шаг 19 / Контроли финансирования

Проверяем финансирование и общий поток

=O44-Расчеты!O263

=O45-Расчеты!O264

=O46-Расчеты!O265

=O47-Расчеты!O266

=O48-Расчеты!O267

=O49-Расчеты!O268

=O50-Расчеты!O269

=ОКРУГЛ(O51-Расчеты!O270;0)

=O52-Расчеты!O271

Для всех ячеек сохраняем «ТРЕВОГА». В K51 разница округляется до целого рубля; этот контроль не проверяет совпадение копеек.

Если контроль показывает расхождение, проверяем исходную строку, закрепление диапазонов и знак потока. Не заменяем контроль константой 0.

Шаг 19 / Контроли финансирования / Сверка результата

Сверка в Excel · 1/2

Проверяем финансирование и общий поток. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Шаг 19 / Контроли финансирования / Сверка результата

Сверка в Excel · 2/2

Проверяем финансирование и общий поток. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Шаг 20 / Остатки денег

Связываем начало и конец года

C54 — «Баланс денежных средств на начало периода», C55 — «Баланс денежных средств на конец периода». В H54/H55 сохраняются рубли; подписи C оформлены «Заголовок3».

=ЕСЛИ(P$5=" ";0;O55)

=P54+P52

Заполняем оба ряда до EE. O55 оставляем пустой со стилем «Пустая_ячейка»: на неё ссылается начальный остаток первого года. P54 берёт предыдущий конечный остаток только для активного года; P55 добавляет текущий чистый денежный поток.

Для P54:EE55 сохраняем «Л_Баланс» и денежный формат с двумя знаками. Это остатки на даты, а не обороты. Начало 2015 года равно нулю, начало следующего года совпадает с концом предыдущего. Годовой конечный остаток должен совпасть с последним периодным остатком того же года на листе «Расчеты».

Шаг 21 / Отрицательный поток

Проверяем годы с отрицательным денежным потоком

=1*(ОКРУГЛ(P52;2)<0)

=СУММ(P57:EE57)

Заполняем P57:EE57 вправо. Исходная подпись G57 — «Проверка сходимость ОДДС по периоду». Формула отмечает отрицательный чистый поток года после округления до копеек. O57 — количество таких годовых периодов, а не денежная сумма.

Сохраняем стиль «Флаг» для P57:EE57 и исходное отображение с одним десятичным знаком; O57 — «СУММА». Единица «руб.» в H57 унаследована из исходного оформления, но значения 0/1 здесь безразмерные. Ненулевой O57 сам по себе не доказывает нехватку денег: отрицательный годовой поток может покрываться ранее накопленным остатком.

В базовом примере за 2018 год S52 = -427,90 руб. Начальный остаток S54 = 1 397,26 руб. уменьшается до S55 = 969,36 руб., но остаётся положительным. Поэтому S57 = 1, а S58 = 0: отрицательный поток года и отрицательный остаток денег — разные сигналы.

Шаг 22 / Недостаток денег

Проверяем накопленный остаток и дефицит

=1*(ОКРУГЛ(P55;2)<0)

=СУММ(P58:EE58)

=O58

=МИН(P55:EE55)

Заполняем P58:EE58. Подпись G58 — «Проверка сходимость ОДДС кумулятивно»; формула отмечает отрицательные конечные остатки денег. P58:EE58 имеют «Флаг» с одним десятичным знаком, O58/O59 — «СУММА», K58 — «ТРЕВОГА». Единица «руб.» в H58 не меняет безразмерный смысл флага. G59 — «Дефицит».

Для P57:EE58 сохраняем одно правило условного форматирования «равно 1» с исходной светлой диагональной штриховкой #FF99FF; срабатывающий текст делаем чёрным. Отрицательный годовой поток строки 57 и отрицательный остаток строки 58 — разные проверки.

O59 берёт минимум по всем P55:EE55, включая неактивные нулевые столбцы. Поэтому ноль здесь может означать отсутствие отрицательного остатка, даже если в каждом активном году деньги положительны. Не трактуем O59 как минимальный положительный остаток денег активных лет.

Шаг 22 / Недостаток денег / Сверка результата

Сверка в Excel · 1/3

Проверяем накопленный остаток и дефицит. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Шаг 22 / Недостаток денег / Сверка результата

Сверка в Excel · 2/3

Проверяем накопленный остаток и дефицит. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Шаг 22 / Недостаток денег / Сверка результата

Сверка в Excel · 3/3

Проверяем накопленный остаток и дефицит. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Шаг 23 / Движение основных средств

Отделяем начальный остаток от ввода

B61 — «Баланс», C62 — «Основные средства». Строки G63:G66: «Баланс на начало», «Ввод в эксплуатацию», «Амортизация», «Баланс на конец»; H63:H66 — рубли.

=ИНДЕКС(Расчеты!$P$282:$EE$282;1;ПОИСКПОЗ(P$9;Расчеты!$P$9:$EE$9;0))

=СУММЕСЛИ(Расчеты!$P$9:$EE$9;P$9;Расчеты!$P$283:$EE$283)

В P63 ИНДЕКС выбирает начальный остаток первого исходного периода нужного года; ПОИСКПОЗ с последним аргументом 0 ищет точное совпадение года. P64 суммирует ввод за год из строки 283. Обе формулы заполняем до EE; стиль — «Обычный», денежный формат с двумя знаками.

Для 2018 года проверяем начальный остаток 0 и ввод около 8 474,58 руб.

Шаг 24 / Конечный остаток ОС

Сохраняем пустую амортизацию и итог движения

=СУММ(P64:EE64)

=СУММ(P65:EE65)

=СУММ(P63:P65)

P65:EE65 остаются пустыми со стилем «Пустая_ячейка»: амортизация в исходной модели не рассчитывается. Не добавляем в эти ячейки диагностические формулы с нулевым результатом. При этом O65 сохраняет исходную СУММ и возвращает ноль.

O64 и O65 оформляем «СУММА», P66:EE66 — «Л_Баланс»; денежный формат — два знака. Заполняем формулу P66 вправо до EE. O63 и O66 оставляем пустыми: здесь нет суммарного остатка или искусственного начального значения.

Проверяем каждый активный год: начало + ввод + амортизация = конец. Эти формулы меняют начальный остаток и ввод 2018 года, а также общий ввод O64; конечная стоимость основных средств и денежные потоки исходного примера сохраняются. Новую амортизационную политику этот урок не вводит.

Шаг 24 / Конечный остаток ОС / Сверка результата

Сверка в Excel

Сохраняем пустую амортизацию и итог движения. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Шаг 25 / Активы

Проверяем состав годовых активов

C68 — «Активы». В следующей таблице указаны годовые статьи и источники конечных остатков на листе «Расчеты».

C68 имеет «Заголовок3», подписи G69:G74 — «Обычный», D75 — «Заголовок4». В H69:H75 сохраняются рубли со стилем «ед_изм». Сохраняем унаследованную единицу в H76, хотя строка служит пустым разделителем.

Эти статьи отражают состояние на конец года. Их нельзя суммировать по месяцам или кварталам: иначе одни и те же остатки будут учтены несколько раз. Прочие оборотные активы сохраняют исходную конструкцию НДС из М6 с разобранным там ограничением.

Шаг 26 / Остатки активов

Выбираем конечные остатки каждого года

=ПРОСМОТР(P$6;Расчеты!$P$6:$EE$6;Расчеты!$P288:$EE288)

=ПРОСМОТР(P$6;Расчеты!$P$6:$EE$6;Расчеты!$P289:$EE289)

=ПРОСМОТР(P$6;Расчеты!$P$6:$EE$6;Расчеты!$P290:$EE290)

=ПРОСМОТР(P$6;Расчеты!$P$6:$EE$6;Расчеты!$P291:$EE291)

=ПРОСМОТР(P$6;Расчеты!$P$6:$EE$6;Расчеты!$P292:$EE292)

=ПРОСМОТР(P$6;Расчеты!$P$6:$EE$6;Расчеты!$P293:$EE293)

Для этих конечных остатков сохраняется ПРОСМОТР по дате конца года. Можно ввести P69, заполнить вниз до P74 и затем каждый ряд вправо до EE: номер исходной строки меняется, поскольку он не закреплён; столбцы P:EE закреплены. Сверяем все шесть выражений, чтобы не спутать движение основных средств 63/64 с остатками баланса.

Сохраняем «Обычный» и денежный формат с двумя знаками. Для исходного календаря дата конца каждого активного года есть в строке 6 «Расчетов»; эту формулу не рассматриваем как универсальный способ выбора остатков за пределами исходного календаря.

Шаг 27 / Итоги активов

Сохраняем подытог и суммы столбца O

=СУММ(P69:P74)

=СУММ(P69:EE69)

=СУММ(P70:EE70)

=СУММ(P71:EE71)

=СУММ(P72:EE72)

=СУММ(P73:EE73)

=СУММ(P74:EE74)

=СУММ(P75:EE75)

P75 заполняем до EE и сохраняем «Л_Баланс». O69:O75 имеют стиль «СУММА» и денежный формат с двумя знаками. В скопированном столбце O заменяем прежние МАКС на показанные СУММ.

O69:O75 — суммы остатков на конец каждого года, как предусмотрено исходной моделью, а не баланс на конец проекта. Активы на конец проекта находятся в последнем активном годовом столбце Y. Такая сумма может быть нужна для воспроизведения исходной таблицы, но не является денежным оборотом.

Шаг 27 / Итоги активов / Сверка результата

Сверка в Excel · 1/2

Сохраняем подытог и суммы столбца O. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Шаг 27 / Итоги активов / Сверка результата

Сверка в Excel · 2/2

Сохраняем подытог и суммы столбца O. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Шаг 28 / Пассивы

Выбираем конечные остатки пассивов

C77 — «Пассивы»; G78 «Уставный капитал», G79 «Нераспределенная прибыль», G80 «Заемные средства», G81 «Кредиторская задолженность». H78:H81 — рубли.

=ПРОСМОТР(P$6;Расчеты!$P$6:$EE$6;Расчеты!$P297:$EE297)

=ПРОСМОТР(P$6;Расчеты!$P$6:$EE$6;Расчеты!$P298:$EE298)

=ПРОСМОТР(P$6;Расчеты!$P$6:$EE$6;Расчеты!$P299:$EE299)

=ПРОСМОТР(P$6;Расчеты!$P$6:$EE$6;Расчеты!$P300:$EE300)

Начав с P78, заполняем вниз до P81 и вправо до EE; проверяем соответствие исходным строкам 297:300. Можно скопировать формулу конечного остатка активов, но в ней нужно изменить ссылку на исходную строку. Переименования статьи недостаточно. Периодные ячейки имеют «Обычный» и денежный формат с двумя знаками. C77 оформляется «Заголовок3», подписи G — «Обычный».

Шаг 29 / Итоги пассивов

Сводим пассивы и сохраняем смысл суммы

=СУММ(P78:P81)

=СУММ(P78:EE78)

=СУММ(P79:EE79)

=СУММ(P80:EE80)

=СУММ(P81:EE81)

=СУММ(P82:EE82)

D82 — «Итого пассивы» со стилем «Заголовок4», H82 — рубли. P82 заполняем до EE со стилем «Л_Баланс». O78:O82 — «СУММА», денежный формат с двумя знаками. В O76/O77 новые суммы не вводим: это разделительный ряд и заголовок.

В O здесь также складываем остатки на конец десяти лет. Например, уставный капитал 3 000 руб. в каждом году даёт O78 = 30 000 руб.; это не размер капитала на конец проекта. Для конечного состояния используем Y78:Y82.

Шаг 30 / Проверка баланса

Сверяем активы и пассивы по каждому году

=P75-P82

=СУММ(P84:EE84)

=O84

G84 сохраняет подпись «проверка баланса», H84 — рубли. P84 заполняем до EE, стиль — «Обычный», денежный формат с двумя знаками. O84 имеет «СУММА», K84 — «ТРЕВОГА».

Сначала проверяем каждое годовое расхождение P84:Y84. Затем — общий итог O84 и отображение K84. Одного нулевого O84 недостаточно: положительная ошибка одного года может погаситься отрицательной ошибкой другого.

Сопоставляем деньги P73:Y73 с остатками на конец года P55:Y55 и последними исходными периодами соответствующих лет. Нулевой баланс проверяет арифметическую связь отчётных форм, но не доказывает корректность всех финансовых допущений, налоговой конструкции или условных выплат.

Шаг 30 / Проверка баланса / Сверка результата

Сверка в Excel · 1/2

Сверяем активы и пассивы по каждому году. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Шаг 30 / Проверка баланса / Сверка результата

Сверка в Excel · 2/2

Сверяем активы и пассивы по каждому году. Сопоставляем адреса и значения со своей книгой; полный ряд остаётся в Excel.

Шаг 31 / Структура листа

Завершаем структуру годовых форм

После сверки формул удаляем целиком строки 85:130 только на новой «Отчетности». На ней остаются календарь и три формы до строки 84. На листе «Расчеты» эти строки и расчётные блоки сохраняются.

Раскрываем группы и проверяем их границы после удаления строк. Не добавляем новую группировку поверх уже правильной. Если границы отличаются, на этом новом листе выполняем «Данные → Разгруппировать → Удалить структуру» и восстанавливаем её по таблице. Сначала выделяем целые строки внешних групп, затем вложенных; для каждого диапазона используем «Данные → Группировать → Строки».

В исходном представлении свёрнуты только подгруппы 13:14 и 17:18; их можно раскрыть для проверки флагов. Остальные строки годовых форм видимы. Внешние заголовки 23, 32 и 61 остаются вне своих групп. Повторно проверяем закрепление P22.

Проверка / Годовые формы

Проверяем годовую отчётность

Числа для основных средств относятся к принятому историческому примеру. При изменении исходных данных важны связи и смысл проверок, а не буквальное совпадение сумм.

Сохраняем рабочую копию. Для сверки результата используем файл М07_Контроль_v2.xlsx. Годовые формы помогают рассматривать модель по годам; это учебные формы, а не заявление о соответствии нормативной финансовой отчётности. Условности НДС, налога на прибыль, DSCR и потока акционеров из предыдущих уроков сохраняются.