Резюме, сценарии и представление результатов

Введение / Резюме

Собираем итоговый лист

Создаём лист «Резюме». Размещаем проверки, потребность в финансировании, источники средств, показатели эффективности, сценарии и график накопленного потока.

Введение / Резюме / Сверка результата

Сверка в Excel

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

Шаг 1 / Новый лист

Создаём лист «Резюме»

Сохраняем свою копию книги. Добавляем новый лист кнопкой «+» у ярлыков, переименовываем в Резюме. Через контекстное меню ярлыка задаём красный цвет #FF0000. Копируем работающую формулу A1 листа «Исходные данные» в A1 нового листа. К A1:Q1 применяем стиль «Название листа»; сохраняем книгу, чтобы формула могла определить имя листа.

В C3 вводим дату 16.05.2016. Для B2:C3 применяем «Заголовок1(к)», затем для C3 через Ctrl+1 выбираем формат даты с кратким названием месяца и двухзначным годом, например «16 май 16».

Это историческая подпись примера. Она не управляет дисконтированием: начальная дата расчёта NPV в предыдущем модуле — 31.12.2014.

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

Везде, где далее указан финансовый формат, через Ctrl+1 выбираем «Финансовый» и устанавливаем «Обозначение: Нет». Число десятичных знаков задаём по соответствующему шагу. Единицы измерения указаны отдельно; знак валюты к числам не добавляется.

Шаг 1 / Новый лист / Продолжение

Задаём ширины столбцов «Резюме»

Выделяем указанные целые столбцы через поле имени слева от строки формул. Затем открываем «Главная → Формат → Ширина столбца» и вводим значение из таблицы.

Ширины измерены в проверенной учебной копии Excel и приведены с округлением до двух десятичных знаков. Сохраняем исходный шрифт книги: единица ширины связана с размером символа в стиле «Обычный». Видимая ширина в пикселях также зависит от масштаба и DPI, а окно Excel округляет значение. Сверяем полноту подписей и чисел; для устранения обрезки используем перенос текста и высоту строки, размер шрифта не уменьшаем.

Шаг 2 / Подписи

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

Для подписей применяем «Обычный». Если длинный текст обрезан, включаем «Главная → Перенос текста» и «Формат → Автоподбор высоты строки». Числа проверок будут в I3:I4 и O3:O4.

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

Шаг 3 / Проверки

Переносим контрольные значения

=Расчеты!K277

=Расчеты!K303

=Расчеты!K54

=Расчеты!K266+Расчеты!K267

К I3:I4 и O3:O4 применяем «ТРЕВОГА». Числовой формат этого стиля показывает ноль как Ok, а ненулевое значение — как «ОШИБКА!». Это формат ячейки, отдельное правило условного форматирования создавать не нужно.

Каждый индикатор проверяем отдельно. В O4 складываем результаты двух проверок, поэтому их значения могут взаимно погаситься.

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

Сверка в Excel

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

Шаг 4 / Потребности

Размещаем заголовки и потребности

Выделяем B6:Q6, объединяем ячейки и применяем «Заголовок2». Для B8 задаём «Заголовок0», для B10 — «Заголовок1(к)», для C11:D11 — «Таблица», для B12:B14 — «Обычный».

C12 содержит капитальные вложения, C14 — итог. C13 оставляем пустой: отдельную потребность в оборотном капитале здесь не заполняем.

Шаг 5 / Потребности

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

=-Расчеты!O259

=СУММ(C12:C13)

=C12/$C$14

=C13/$C$14

=C14/$C$14

C12 оформляем «Обычный», C14 — «Л_ИТОГО»; денежным суммам задаём финансовый формат без десятичных знаков. D12:D14 оформляем «Проценты_0%». При ненулевом C14 итоговая доля D14 равна 100%. Знак минус в C12 переводит отрицательный инвестиционный отток расчётного листа в положительную потребность.

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

Сверка в Excel

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

Шаг 6 / Источники

Размещаем источники финансирования

F10 оформляем «Заголовок1(к)», G11:H11 — «Таблица», подписи F12:F14 — «Обычный». Формулы ниже связывают резюме с рассчитанными поступлениями капитала и долга.

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

Шаг 7 / Источники

Переносим суммы и доли источников

=Расчеты!O263

=Расчеты!O264

=СУММ(G12:G13)

=G12/$G$14

=G13/$G$14

=G14/$G$14

G12:G13 — «Обычный», G14 — «Л_ИТОГО», финансовый формат без десятичных знаков. H12:H13 сохраняют стиль «Процент_0,00%», но числовой формат ячеек задаём 0%; H14 — «Л_ИТОГО» с тем же форматом 0%. При ненулевом G14 итоговая доля равна 100%.

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

Сверка в Excel

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

Шаг 8 / Показатели

Переносим IRR, NPV и PB

=Расчеты!J312

=Расчеты!J311

=Расчеты!J331

J10 — «Заголовок1(к)», K11 — «Таблица», J12:J14 — «Обычный». Для K12 применяем «Проценты_0%», для K13 — «Обычный» и финансовый формат без десятичных знаков. Для K14 сохраняем «Обычный», числовой формат 0,0 "лет".

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

Шаг 9 / Показатели

Переносим ROS, NPM и PI

=Расчеты!J342

=Расчеты!J347

=Расчеты!J336

N11 оформляем «Таблица», M12:M14 — «Обычный». Для N12:N13 применяем «Проценты_0%». Для N14 — «Коэффициенты» и числовой формат 0,00 "x": PI здесь коэффициент, не процент.

ROS и NPM относятся к историческому условному расчёту. В знаменателе PI сохраняем номинальные капвложения, как разобрали ранее. Ни одна ссылка не добавляет новой методики оценки.

Шаг 10 / Участники

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

=Расчеты!J319

=Расчеты!O316

=Расчеты!J325

Q11 — «Таблица», P12:P14 — «Обычный». Q12/Q14 — «Проценты_0%» с числовым форматом 0,0%; Q13 — «Обычный», финансовый формат без десятичных знаков. Подпись «% выплаченные» означает сумму уплаченных процентов в рублях, а не процентную ставку.

IRR акционеров относится к условному потоку предыдущего модуля. Фактические выплаты акционерам в годовой проекции остаются пустыми; этот показатель нельзя называть доходностью уже выплаченных дивидендов.

Шаг 10 / Участники / Сверка результата

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

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

Шаг 10 / Участники / Сверка результата

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

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

Проверка / Резюме

Сверяем первый результат

Проверяем, что A1 показывает «Резюме», C3 содержит дату, а четыре индикатора ссылаются на нужные ячейки «Расчеты». C13 должна оставаться пустой. Доли D12:D14 рассчитываем от итога потребностей, а H12:H14 — от итога источников.

Сопоставляем все девять показателей с исходными ячейками расчётного листа. Для процентов, рублей, лет и PI используем разные форматы. Для сверки временно увеличиваем число десятичных знаков в сравниваемых ячейках («Главная → Увеличить разрядность»), затем возвращаем заданные форматы. Строка формул показывает выражение; округляется только отображение результата.

Для числовых столбцов C:D, G:I, K, N:O и Q при необходимости выполняем «Главная → Формат → Автоподбор ширины столбца». Для длинных подписей включаем перенос текста и автоподбор высоты строки. В заголовке B6:Q6 при необходимости задаём высоту строки вручную, чтобы текст объединённой ячейки читался полностью.

Сохраняем книгу. Контрольный результат урока — М08_После_урока_01.xlsx. Продолжаем собственную книгу в следующем уроке.

Шаг 1 / Матрица

Анализ чувствительности и сценариев

Продолжаем книгу после урока 1. На листе «Резюме» в B16 вводим «Анализ сценариев», к B16:Q16 применяем «Заголовок0». В B17 вводим «Вариант сценария», в D17:P17 — номера 1–13; Q17 оставляем пустой. Заголовки B17:P17 оформляем стилем «Таблица».

В B18:B30 вводим подписи из таблицы. Применяем «Обычный», включаем перенос текста и автоподбор высоты строк. Затем выделяем D18:P30, вводим 0 и нажимаем Ctrl+Enter: ноль должен появиться во всех ячейках матрицы.

Шаг 2 / Отклонения

Вводим исходные изменения

В матрице, заполненной нулями, изменяем только перечисленные ячейки. Остальные оставляем равными нулю. На первом этапе все отдельные отклонения равны +10% или −10%; более глубокое снижение цены рассмотрим позже.

К D18:P30 применяем «Проценты_0%» и пользовательский учётный процентный формат без десятичных знаков: положительное отклонение показывается процентом, отрицательное — в скобках, ноль — тире. Это оформление не меняет числовые значения. Номера сверху — переключатели сценария, а проценты внутри матрицы — отклонения. Не смешиваем эти два вида чисел.

Шаг 2 / Отклонения / Продолжение

Задаём точный формат матрицы

Для каждого указанного диапазона выделяем ячейки, нажимаем Ctrl+1 и на вкладке «Число» выбираем «Все форматы». В поле «Тип» вставляем соответствующий код целиком и подтверждаем. Коды приведены для русской версии Excel.

Код 1 — D18:P30

_(* # ##0%_);_(* (# ##0%);_(* "-"??_);_(@_)

Формат управляет отображением процентов, знаков и нулей и не меняет значения сценариев. Сохраняем все символы кода, включая пробелы, кавычки, подчёркивания и точки с запятой. Значение H22, равное −25,9%, при этом формате отображается округлённо как (26%). Точное значение этой ячейки проверяем в строке формул.

Шаг 2 / Отклонения / Сверка результата

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

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

Состояние на рисунке: выбран сценарий 1, снижение цены -10%. В сохранённой контрольной книге остаются сценарий 1 и снижение цены −25,9%.

Шаг 2 / Отклонения / Сверка результата

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

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

Состояние на рисунке: выбран сценарий 1, снижение цены -10%. В сохранённой контрольной книге остаются сценарий 1 и снижение цены −25,9%.

Шаг 2 / Отклонения / Сверка результата

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

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

Состояние на рисунке: выбран сценарий 1, снижение цены -10%. В сохранённой контрольной книге остаются сценарий 1 и снижение цены −25,9%.

Шаг 3 / Выбор

Создаём переключатель и коэффициенты

В B4 вводим 1. Выделяем B4, в поле имени слева от строки формул вводим Выбор_сценария и нажимаем Enter. Открываем «Данные → Проверка данных»: тип «Список», источник =$D$17:$P$17. Выбираем только целые номера 1–13.

Для B4 применяем «Заголовок1(к)» и тип "Сценарий" 0. Для C17 — «Таблица» и тип "Выбран №" 0; цифра 0 в формате обязательна.

=Выбор_сценария

=1+СМЕЩ(C18;0;Выбор_сценария)

Выделяем C18:C30 и нажимаем Ctrl+D. Функция СМЕЩ выбирает отклонение из нужного столбца. Прибавляя 1, получаем множитель. C18:C30 оформляем «Проценты_0%»: базовые множители видны как 100%.

C30 — проверка последней строки

=1+СМЕЩ(C30;0;Выбор_сценария)

Шаг 4 / Название

Показываем выбранный сценарий

=ИНДЕКС($B$18:$B$30;Выбор_сценария)

Применяем к C4 «Заголовок1(к)». При B4=1 надпись должна быть «Базовый сценарий». При B4=8 — «Рост ФОТ», а C25 должно показывать 110%. Возвращаем B4=1.

Пустая ячейка B4, ноль и номер за пределами 1–13 недопустимы для выбора сценария. При нулевом или пустом значении СМЕЩ может обратиться к собственной ячейке. Список помогает выбрать нужный номер. Даже если настройки разрешают оставлять ячейку пустой, это не означает, что формулы корректно обработают пустое значение.

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

Сверка в Excel

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

Состояние на рисунке: выбран сценарий 1, снижение цены -10%. В сохранённой контрольной книге остаются сценарий 1 и снижение цены −25,9%.

Шаг 5 / Имена

Добавляем префикс АЧ_ к именам

Чтобы создать имена для C19:C30 из соседних подписей, сначала подготавливаем временные тексты. В A19 вводим =СЦЕПИТЬ("АЧ_";B19); выделяем A19:A30 и нажимаем Ctrl+D. В A30 должна получиться =СЦЕПИТЬ("АЧ_";B30).

1. Копируем B19:B30 в Q19:Q30, сохранив исходные названия.
2. Копируем A19:A30. Выбираем B19 и выполняем Ctrl+Alt+V → «Значения» → OK.
3. Выделяем B19:C30, нажимаем Ctrl+Shift+F3. Оставляем отметку «В столбце слева» и подтверждаем.
4. Возвращаем прежние подписи из Q19:Q30 в B19:B30. Очищаем содержимое временных A19:A30 и Q19:Q30 клавишей Delete.

Имена создаются для значений C19:C30; диапазон B19:B30 служит источником названий. Очистка относится только к временным ячейкам нашей учебной копии.

Проверка / Имена

Проверяем ссылки имён

Открываем «Формулы → Диспетчер имён» или Ctrl+F3. Проверяем каждое имя и его адрес. Пробелы названий сценариев Excel заменяет подчёркиваниями.

Префикс АЧ_ отделяет коэффициенты анализа чувствительности от остальных имён модели. После восстановления подписей имена должны продолжать ссылаться на столбец C.

Шаг 6 / Исходные данные

Мощность и сценарий

Переходим на лист «Исходные данные». Множитель связывает сценарии увеличения и снижения мощности. Условие начала эксплуатации сохраняется. Вводим формулы в указанные ячейки.

=АЧ_Снижение_мощности*АЧ_Увеличение_мощности

=ЕСЛИ(P182>=ГОД(Дата_начала_эксплуатации);$J$183;0)*$I183

Выделяем P183:Y183 и нажимаем Ctrl+R. Не заменяем диапазон константами. Сверяем крайнюю формулу:

Y183 — проверка протяжки

=ЕСЛИ(Y182>=ГОД(Дата_начала_эксплуатации);$J$183;0)*$I183

I183 оформляем «Проценты_0%». Существующие форматы годового ряда сохраняем: «Обычный», финансовый формат с двумя десятичными знаками. Другие строки исходных данных не меняем.

Шаг 7 / Исходные данные

Цена и сценарий

Переходим на лист «Исходные данные». Цена меняется отдельно от физического выпуска. Условие начала эксплуатации сохраняется. Вводим формулы в указанные ячейки.

=АЧ_Снижение_цен*АЧ_Увеличение_цен

=ЕСЛИ(P182>=ГОД(Дата_начала_эксплуатации);$J$189;0)*$I189

Выделяем P189:Y189 и нажимаем Ctrl+R. Не заменяем диапазон константами. Сверяем крайнюю формулу:

Y189 — проверка протяжки

=ЕСЛИ(Y182>=ГОД(Дата_начала_эксплуатации);$J$189;0)*$I189

I189 оформляем «Проценты_0%». Существующие форматы годового ряда сохраняем: «Обычный», финансовый формат с двумя десятичными знаками. Другие строки исходных данных не меняем.

Шаг 8 / Исходные данные

Фонд оплаты труда и сценарий

Переходим на лист «Исходные данные». Этот множитель относится к строке ФОТ. Его влияние на общие расходы разбираем отдельно. Вводим формулы в указанные ячейки.

=АЧ_Снижение_ФОТ*АЧ_Рост_ФОТ

=ЕСЛИ(P$193>=ГОД(Дата_начала_эксплуатации);$J$196;0)*$I196

Выделяем P196:Y196 и нажимаем Ctrl+R. Не заменяем диапазон константами. Сверяем крайнюю формулу:

Y196 — проверка протяжки

=ЕСЛИ(Y$193>=ГОД(Дата_начала_эксплуатации);$J$196;0)*$I196

I196 оформляем «Проценты_0%». Существующие форматы годового ряда сохраняем: «Обычный», финансовый формат с двумя десятичными знаками. Другие строки исходных данных не меняем.

Шаг 9 / Исходные данные

Материальные расходы и сценарий

Переходим на лист «Исходные данные». Сохраняем полные имена без пробелов и без сокращения. Вводим формулы в указанные ячейки.

=АЧ_Снижение_материальных_расходов*АЧ_Рост_материальных_расходов

=ЕСЛИ(P$193>=ГОД(Дата_начала_эксплуатации);$J$197;0)*$I197

Выделяем P197:Y197 и нажимаем Ctrl+R. Не заменяем диапазон константами. Сверяем крайнюю формулу:

Y197 — проверка протяжки

=ЕСЛИ(Y$193>=ГОД(Дата_начала_эксплуатации);$J$197;0)*$I197

I197 оформляем «Проценты_0%». Существующие форматы годового ряда сохраняем: «Обычный», финансовый формат с двумя десятичными знаками. Другие строки исходных данных не меняем.

Шаг 10 / Исходные данные

Прочие расходы и сценарий

Переходим на лист «Исходные данные». Условие начала эксплуатации и ссылку на исходный уровень расходов сохраняем. Вводим формулы в указанные ячейки.

=АЧ_Снижение_прочих_расходов*АЧ_Рост_прочих_расходов

=ЕСЛИ(P$193>=ГОД(Дата_начала_эксплуатации);$J$198;0)*$I198

Выделяем P198:Y198 и нажимаем Ctrl+R. Не заменяем диапазон константами. Сверяем крайнюю формулу:

Y198 — проверка протяжки

=ЕСЛИ(Y$193>=ГОД(Дата_начала_эксплуатации);$J$198;0)*$I198

I198 оформляем «Проценты_0%». Существующие форматы годового ряда сохраняем: «Обычный», финансовый формат с двумя десятичными знаками. Другие строки исходных данных не меняем.

Шаг 11 / Исходные данные

Агрегат расходов и сценарий

Переходим на лист «Исходные данные». Как и в исходной формуле, перемножаем три коэффициента. Вводим формулы в указанные ячейки.

=I196*I197*I198

=ЕСЛИ(P$193>=ГОД(Дата_начала_эксплуатации);$J$199;0)*$I199

Выделяем P199:Y199 и нажимаем Ctrl+R. Не заменяем диапазон константами. Сверяем крайнюю формулу:

Y199 — проверка протяжки

=ЕСЛИ(Y$193>=ГОД(Дата_начала_эксплуатации);$J$199;0)*$I199

I199 оформляем «Обычный», финансовый формат с двумя десятичными знаками. Существующие форматы годового ряда сохраняем: «Л_ИТОГО», финансовый формат с двумя десятичными знаками. Другие строки исходных данных не меняем.

Шаг 12 / Исходные данные

Капитальные вложения и сценарий

Переходим на лист «Исходные данные». Сохраняем условие окончания строительства; исходный бюджет J205 и лимиты финансирования автоматически не меняются. Вводим формулы в указанные ячейки.

=АЧ_Снижение_капвложений*АЧ_Рост_капвложений

=ЕСЛИ(P203<=ГОД(Дата_конца_строительства);$J$205;0)*$I205

Выделяем P205:Y205 и нажимаем Ctrl+R. Не заменяем диапазон константами. Сверяем крайнюю формулу:

Y205 — проверка протяжки

=ЕСЛИ(Y203<=ГОД(Дата_конца_строительства);$J$205;0)*$I205

I205 оформляем «Проценты_0%». Существующие форматы годового ряда сохраняем: «Обычный», финансовый формат с двумя десятичными знаками. Другие строки исходных данных не меняем.

Шаг 12 / Исходные данные / Сверка результата

Сверка в Excel

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

Разбор / Расходы

Сравниваем общие расходы с суммой отдельных статей

Выбираем на «Резюме» сценарий 8 — «Рост ФОТ». На листе «Исходные данные» коэффициенты I196:I198 равны 1,1; 1; 1. Их произведение I199 равно 1,1.

При исходных удельных расходах по 1 рублю на тонну для каждой статьи отдельные строки расходов дают 1,1 + 1 + 1 = 3,1. Однако в строке 199 используем исходные общие расходы 3 и множитель 1,1: 3 × 1,1 = 3,3. Именно этот общий показатель участвует в дальнейшем расчёте расходов.

Сохранённая формула увеличивает общие расходы в расчёте на 10%, а не только ФОТ. Нельзя утверждать, что фактически изменились все три строки расходов: изменился используемый общий показатель, и он расходится с их суммой. В этом упражнении сохраняем такую условность и учитываем её при чтении результатов.

Возвращаем B4=1. При изменении капитальных вложений учитываем фиксированное финансирование: при росте исходного бюджета 10 000 на 10% потребность становится 11 000, тогда как исходные 3 000 капитала и 7 000 долга сами не увеличиваются. Это условный арифметический пример, а не автоматический план привлечения средств.

Разбор / Расходы / Сверка результата

Сверка в Excel

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

Состояние на рисунке: выбран сценарий 8, снижение цены -25,9%. В сохранённой контрольной книге остаются сценарий 1 и снижение цены −25,9%.

Шаг 13 / Выходы

Подготавливаем четыре результата таблицы данных

Возвращаемся на «Резюме». В B32:B36 вводим подписи. B32 оформляем «Таблица», B33:B36 — «Обычный».

=Выбор_сценария

=K12

=K13

=K14

=Расчеты!O278

В D32:P32 вводим номера 1–13, применяем «Таблица». C32 оформляем как C17. C33 — «Проценты_0%» с типом 0,0%; C34:C36 — «Выход_данные»: C34 финансовый без десятичных знаков, C35 тип 0,0 "лет", C36 финансовый с двумя знаками.

Шаг 14 / Таблица данных

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

Выделяем C32:P36. Открываем «Данные → Анализ “что если” → Таблица данных». Номера сценариев расположены горизонтально, в строке 32. Для входа строки (Row input cell; в старом русском окне книги — «Подставлять значения по столбцам в») указываем $B$4. Второе поле оставляем пустым и нажимаем OK.

Excel последовательно подставляет номера в B4 и возвращает четыре показателя. Диапазон результатов — D33:P36: 13 столбцов × 4 строки. Не вводим туда числа вручную и не редактируем отдельные ячейки полученной таблицы.

Копируем C33:C36 и переносим на соответствующие строки D33:P36 только форматы, без значений и формул. Результаты удобно выровнять по центру.

Для обычной работы выбираем «Формулы → Параметры вычислений → Автоматически». Если выбираем режим автоматического вычисления без таблиц данных, для их обновления выделяем D33:P36 и нажимаем F9. Общий пересчёт книги в этом режиме не гарантирует пересчёт таблицы.

Ориентация и отдельный пересчёт описаны в справке Microsoft о таблицах данных.

Проверка / Сценарии

Сверяем таблицу, выбирая сценарии вручную

По очереди выбираем в B4 номера 1–13. Для каждого номера сравниваем четыре текущих значения C33:C36 с соответствующим столбцом D:P. Временно увеличиваем число знаков после запятой у сравниваемых результатов через «Главная → Увеличить разрядность». После сверки возвращаем заданный формат. Строка формул показывает выражение, поэтому числовые результаты сравниваем в ячейках.

После проверки возвращаем B4=1. Проверяем, что C4 снова показывает «Базовый сценарий», коэффициенты C18:C30 равны 1, а D33:D36 соответствуют базовым текущим значениям.

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

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

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

Состояние на рисунке: выбран сценарий 1, снижение цены -10%. В сохранённой контрольной книге остаются сценарий 1 и снижение цены −25,9%.

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

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

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

Состояние на рисунке: выбран сценарий 1, снижение цены -10%. В сохранённой контрольной книге остаются сценарий 1 и снижение цены −25,9%.

Шаг 15 / Эксперимент

Повторяем историческое снижение цены

Начинаем с H22=−10%. Смотрим столбец H33:H36: это сценарий снижения цены. Затем изменяем H22 и после каждой операции обновляем таблицу данных. Смотрим одновременно IRR, NPV, PB и минимальный денежный остаток — положительный NPV сам по себе не исключает дефицита денег.

В конце упражнения вводим в H22 −25,9% и пересчитываем таблицу данных.

В итоговой матрице H22 остаётся −25,9%, остальные отдельные изменения — ±10%. Поэтому снижение цены здесь больше по величине, чем остальные изменения. Возвращаем B4=1; столбец H продолжает показывать отдельный стресс-сценарий.

Шаг 15 / Эксперимент / Сверка результата

Сверка в Excel

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

Шаг 16 / Индикатор

Сводим выбранные проверки

В B5 вводим «Наличие ошибок», применяем «Заголовок1(к)». В C5 вводим формулу и применяем «ТРЕВОГА».

=I3+I4+O3+O4

Это сумма четырёх выбранных индикаторов, а не доказательство отсутствия ошибок во всей книге. При проверке открываем I3/I4/O3/O4 по отдельности. Например, +1 и −1 могут взаимно погаситься в сумме, хотя обе проверки ненулевые.

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

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

Сверка в Excel

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

Шаг 17 / Шапка

Сценарий на листе «Исходные данные»

На листе «Исходные данные» вводим следующие формулы. G2 оформляем «Заголовок1(к)», G3 — «ТРЕВОГА». Существующие формулы календаря и название листа A1 не изменяем.

G2 — Исходные данные

=СЦЕПИТЬ("№"; ;Выбор_сценария;" - ";Резюме!C4)

G3 — Исходные данные

=Резюме!C5

После пересчёта G2 должна показывать номер и название выбранного сценария. G3 повторяет только общий индикатор выбранных проверок. Этот индикатор имеет те же ограничения, что и на «Резюме».

Шаг 17 / Шапка / Сверка результата

Сверка в Excel

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

Шаг 18 / Шапка

Сценарий на листе «РИД»

На листе «РИД» вводим следующие формулы. G2 оформляем «Заголовок1(к)», G3 — «ТРЕВОГА». Существующие формулы календаря и название листа A1 не изменяем.

G2 — РИД

=СЦЕПИТЬ("№"; ;Выбор_сценария;" - ";Резюме!C4)

G3 — РИД

=Резюме!C5

После пересчёта G2 должна показывать номер и название выбранного сценария. G3 повторяет только общий индикатор выбранных проверок. Этот индикатор имеет те же ограничения, что и на «Резюме».

Шаг 18 / Шапка / Сверка результата

Сверка в Excel

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

Шаг 19 / Шапка

Сценарий на листе «Расчеты»

На листе «Расчеты» вводим следующие формулы. G2 оформляем «Заголовок1(к)», G3 — «ТРЕВОГА». Существующие формулы календаря и название листа A1 не изменяем.

G2 — Расчеты

=СЦЕПИТЬ("№"; ;Выбор_сценария;" - ";Резюме!C4)

G3 — Расчеты

=Резюме!C5

После пересчёта G2 должна показывать номер и название выбранного сценария. G3 повторяет только общий индикатор выбранных проверок. Этот индикатор имеет те же ограничения, что и на «Резюме».

Шаг 19 / Шапка / Сверка результата

Сверка в Excel

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

Шаг 20 / Шапка

Сценарий на листе «Отчетность»

На листе «Отчетность» вводим следующие формулы. G2 оформляем «Заголовок1(к)», G3 — «ТРЕВОГА». Существующие формулы календаря и название листа A1 не изменяем.

G2 — Отчетность

=СЦЕПИТЬ("№"; ;Выбор_сценария;" - ";Резюме!C4)

G3 — Отчетность

=Резюме!C5

После пересчёта G2 должна показывать номер и название выбранного сценария. G3 повторяет только общий индикатор выбранных проверок. Этот индикатор имеет те же ограничения, что и на «Резюме».

Шаг 20 / Шапка / Сверка результата

Сверка в Excel

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

Проверка / Урок

Сохраняем сценарную модель

Проверяем: B4=1; H22=−25,9%; остальные заданные изменения ±10%; все 12 имён АЧ_ указывают на C19:C30; временные A19:A30 и Q19:Q30 пусты. Для выбора сценария используем только целые номера 1–13, не оставляем B4 пустой и не вводим 0.

Таблица D33:P36 должна содержать вычисляемые результаты, совпадающие с результатами ручного выбора сценариев. На всех четырёх рабочих листах подпись сценария соответствует C4. Четыре отдельных контроля проверены независимо от C5.

Убеждаемся, что общий показатель расходов в сценарии ФОТ и сумма отдельных статей различаются именно описанным способом; не заменяем формулу I199 другой методикой. Сохраняем книгу. Контрольный результат — М08_После_урока_02.xlsx.

Шаг 1 / Производство

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

Продолжаем свою книгу после урока 2. На «Резюме» в B38 вводим «Производство (тон)», к B38:Q38 применяем «Заголовок0». В B41 вводим «Выпуск готовой продукции», стиль «Обычный» и перенос текста. В C40 вводим «ИТОГО», для C40:M40 задаём «Таблица».

=Отчетность!P9

Выделяем D40:M40, нажимаем Ctrl+R. Последняя ссылка должна вести к последнему году:

M40 — проверка протяжки

=Отчетность!Y9

Получаем десять лет: 2015–2024. Производство измеряем в тоннах. Годы не являются датами начала или конца периода и должны отображаться целыми числами.

Шаг 2 / Выпуск

Переносим физические объёмы

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

Выделяем D41:M41 и нажимаем Ctrl+R. Проверяем крайнюю формулу, затем рассчитываем итог:

M41 — проверка протяжки

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

=СУММ(D41:M41)

D41:M41 оформляем «Вход_данные», C41 — «СУММА»; финансовый формат без десятичных знаков сохраняет оформление исходной таблицы, но единица здесь — тонны.

Берём ряд готовой продукции P188:Y188 листа «Исходные данные». Ссылка на денежную выручку исказила бы единицу измерения. В неизменённом историческом примере общий выпуск составляет 5 800 тонн. При проверке своей книги сопоставляем каждый год с соответствующей ячейкой количества.

Изменение цены само по себе не должно менять физический выпуск. Изменение мощности может его менять. После проверки сценариев возвращаем B4=1.

Строка D41:M41 содержит тонны. Если применяем финансовый формат, в Ctrl+1 обязательно выбираем «Обозначение: Нет»: символ рубля к физическому объёму не добавляется.

Шаг 2 / Выпуск / Сверка результата

Сверка в Excel

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

Шаг 3 / Проекция

Подготавливаем годовой денежный поток

В B43 вводим «Проекция денежного потока (руб. с НДС, если не указано иное)», применяем «Заголовок0». В C45 вводим «ИТОГО», к C45:M45 применяем «Таблица».

=D40

Выделяем D45:M45, нажимаем Ctrl+R:

M45 — проверка протяжки

=M40

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

Строки 44 и 61 оставляем пустыми. C62 и C63 не заполняем суммами: они находятся рядом с остатками на дату, а не с потоками за год.

Шаг 4 / Подписи

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

К B46:B52 применяем «Обычный», выравнивание влево, перенос текста и автоподбор высоты строки. Обозначение руб. относится к денежным строкам. НДС и налог на прибыль сохраняют условные схемы предыдущих уроков; эти таблицы не устанавливают действующий налоговый порядок.

Во всех формулах следующей части вводим формулу первого года в D, затем заполняем до M через Ctrl+R. Формула в M показана для проверки последнего года. В C рассчитывается итог строки.

Шаг 5 / Операционные потоки

Выручка и расходы

На листе «Резюме» вводим ссылки и итоги. После каждого ввода в D заполняем соответствующий диапазон D:M через Ctrl+R.

=Отчетность!P34

M46 — проверка протяжки

=Отчетность!Y34

=СУММ(D46:M46)

=Отчетность!P35

M47 — проверка протяжки

=Отчетность!Y35

=СУММ(D47:M47)

Годовые ссылки оформляем «Вход_данные», итоги C — «СУММА». Для этих ячеек используем финансовый формат без десятичных знаков. Сверяем исходные знаки: выплаты обычно отрицательны, поступления положительны.

Шаг 6 / Операционные потоки

Налог на прибыль и НДС

На листе «Резюме» вводим ссылки и итоги. После каждого ввода в D заполняем соответствующий диапазон D:M через Ctrl+R.

=Отчетность!P36

M48 — проверка протяжки

=Отчетность!Y36

=СУММ(D48:M48)

=Отчетность!P37

M49 — проверка протяжки

=Отчетность!Y37

=СУММ(D49:M49)

Годовые ссылки оформляем «Вход_данные», итоги C — «СУММА». Для этих ячеек используем финансовый формат без десятичных знаков. Сверяем исходные знаки: выплаты обычно отрицательны, поступления положительны.

Шаг 7 / Проекция

Итог операционной деятельности

Суммируем выручку, расходы, налог на прибыль и поток НДС. Заполняем первый год, протягиваем D50:M50 через Ctrl+R и проверяем крайнюю формулу.

=СУММ(D46:D49)

M50 — проверка протяжки

=СУММ(M46:M49)

=СУММ(D50:M50)

Годовой ряд — «Л_ИТОГО», финансовый формат с двумя десятичными знаками. Итог C50 — «СУММА», финансовый формат без десятичных знаков.

Шаг 8 / Проекция

Инвестиционный поток

Переносим годовые инвестиционные выплаты из строки 40 листа «Отчетность». Сохраняем знак выплат. Заполняем первый год, протягиваем D51:M51 через Ctrl+R и проверяем крайнюю формулу.

=Отчетность!P40

M51 — проверка протяжки

=Отчетность!Y40

=СУММ(D51:AD51)

Годовой ряд — «Вход_данные», финансовый формат без десятичных знаков. Итог C51 — «СУММА», финансовый формат без десятичных знаков.

Начиная со строки 51 исходные итоги в C охватывают D:AD. Сохраняем этот диапазон; N:AD в строках 51:60 остаются пустыми. Не добавляем туда другие данные: они попадут в итог.

Шаг 9 / Проекция

Денежный поток проекта

Суммируем операционный и инвестиционный денежные потоки. Заполняем первый год, протягиваем D52:M52 через Ctrl+R и проверяем крайнюю формулу.

=СУММ(D50:D51)

M52 — проверка протяжки

=СУММ(M50:M51)

=СУММ(D52:AD52)

Годовой ряд — «Л_ИТОГО», финансовый формат с двумя десятичными знаками. Итог C52 — «СУММА», финансовый формат без десятичных знаков.

Шаг 9 / Проекция / Сверка результата

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

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

Шаг 9 / Проекция / Сверка результата

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

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

Шаг 10 / Подписи

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

Для всех подписей применяем «Обычный», выравнивание влево, перенос текста и автоподбор высоты. Исходное название строки 55 сохраняется, но её смысл разбираем вместе с формулой: она включает вновь полученные капитал и долг.

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

Переносим капитал и долг

Переносим поступления из годового отчёта. После ввода в D заполняем каждый ряд до M через Ctrl+R.

=Отчетность!P44

M53 — проверка протяжки

=Отчетность!Y44

=СУММ(D53:AD53)

=Отчетность!P45

M54 — проверка протяжки

=Отчетность!Y45

=СУММ(D54:AD54)

D53:M54 — «Вход_данные», C53:C54 — «СУММА», финансовый формат без десятичных знаков. Это поступления финансирования, они не являются операционной выручкой.

Шаг 12 / Ликвидность

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

=СУММ(D52:D54)

Заполняем D55:M55 через Ctrl+R.

M55 — проверка протяжки

=СУММ(M52:M54)

=СУММ(D55:AD55)

D55:M55 — «Л_ИТОГО», финансовый формат с двумя десятичными знаками; C55 — «СУММА», без десятичных знаков.

В строке 55 к потоку проекта прибавляем привлечённые капитал и долг. Получаем денежный поток с учётом нового финансирования. Самостоятельный поток проекта до обслуживания долга здесь не выделяем.

Шаг 13 / Долг

Переносим проценты и основной долг

Вводим годовые ссылки, заполняем каждый ряд до M и рассчитываем итоги. Не меняем исходный знак выплат.

=Отчетность!P47

M56 — проверка протяжки

=Отчетность!Y47

=СУММ(D56:AD56)

=Отчетность!P48

M57 — проверка протяжки

=Отчетность!Y48

=СУММ(D57:AD57)

D56:M57 — «Вход_данные», C56:C57 — «СУММА», финансовый формат без десятичных знаков. Проценты и погашение основного долга остаются отдельными потоками.

Шаг 14 / После выплат

Получаем поток после обслуживания долга

=СУММ(D55:D57)

Заполняем D58:M58 через Ctrl+R.

M58 — проверка протяжки

=СУММ(M55:M57)

=СУММ(D58:AD58)

D58:M58 — «Л_ИТОГО», финансовый формат с двумя знаками; C58 — «СУММА», без десятичных знаков. Формула прибавляет к потоку строки 55 отрицательные выплаты процентов и основного долга.

Шаг 15 / Чистый поток

Сохраняем пустые фактические дивиденды

D59:M59 оставляем пустыми, стиль «Пустая_ячейка». В упражнении фактические выплаты акционерам не задаются. Итог C59 при этом сохраняет исходную формулу.

=СУММ(D59:AD59)

=СУММ(D58:D59)

Заполняем D60:M60 через Ctrl+R.

M60 — проверка протяжки

=СУММ(M58:M59)

=СУММ(D60:AD60)

D60:M60 — «Л_ВСЕГО», финансовый формат с двумя знаками. C59:C60 — «СУММА», без десятичных знаков. Нулевая сумма пустой строки не превращает условный IRR акционеров в доходность фактических выплат.

Шаг 16 / Остатки

Связываем начальные и конечные остатки

C63 оставляем пустой, стиль «Пустая_ячейка»: на неё ссылается начальный остаток первого года. C62 также остаётся пустой. Вводим D62 и D63, затем заполняем D62:M63 вправо через Ctrl+R.

=C63

=D60+D62

M62 — проверка последнего начала

=L63

M63 — проверка последнего конца

=M60+M62

D62:M62 — «Обычный», D63:M63 — «Л_Баланс», финансовый формат без десятичных знаков. Начало каждого следующего года равно концу предыдущего. Остатки относятся к датам; их не нужно суммировать по годам в C62/C63.

Проверка / Проекция

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

Сопоставляем все девять рядов прямых ссылок с «Отчетностью»: 46→34, 47→35, 48→36, 49→37, 51→40, 53→44, 54→45, 56→47, 57→48. В каждом ряду сравниваем все десять лет D:M с P:Y.

Проверяем промежуточные суммы 50/52/55/58/60, последовательность начальных и конечных остатков и пустые ячейки C63/D59:M59. В N:AD строк 51:60 не должно быть посторонних данных. Для сверки временно увеличиваем число отображаемых знаков у сравниваемых ячеек через «Главная → Увеличить разрядность», затем возвращаем заданные форматы. Проверяем вычисленные результаты в ячейках: строка формул показывает выражения.

Числовым столбцам C:M при необходимости задаём автоподбор ширины. Для B46:B63 используем перенос и автоподбор высоты. Не скрываем столбец C: в нём находятся итоги потоков.

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

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

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

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

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

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

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

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

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

Шаг 17 / Данные графика

Рассчитываем накопленный поток

B66 — «Заголовок3», B67:B68 — «Обычный». C67 оставляем пустой. Вводим формулы и заполняем D67:M68 вправо через Ctrl+R.

=СУММ($D$52:D52)

M67 — проверка накопленной суммы

=СУММ($D$52:M52)

=ЕСЛИ(И((D67>0);(C67<0));1;"")

M68 — проверка индикатора

=ЕСЛИ(И((M67>0);(L67<0));1;"")

D67:M68 — «Расчеты», финансовый формат без десятичных знаков. Строка 67 накапливает поток проекта из строки 52. Строка 68 выдаёт условную единицу только там, где текущая накопленная сумма строго положительна, а предыдущая строго отрицательна.

Шаг 17 / Данные графика / Сверка результата

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

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

Шаг 17 / Данные графика / Сверка результата

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

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

Шаг 18 / Диаграмма

Создаём обычный линейный график

Выделяем D45:M45, затем с нажатой Ctrl добавляем D67:M68. Выбираем «Вставка → Диаграммы → График» и обычный линейный график. В варианте через «Рекомендуемые диаграммы» также выбираем именно график с двумя рядами.

Через «Конструктор диаграмм → Выбрать данные» проверяем обе серии и годы 2015–2024. Если Excel иначе распознал выделение несмежных диапазонов, задаём эти диапазоны явно. Удаляем основные линии сетки диаграммы. Ячейки источника данных должны остаться на листе.

Шаг 19 / Маркер

Выделяем точку второго ряда

В исходной базовой диаграмме выделена точка 2020 года. Сначала проверяем строку 68: выбираем именно точку того года, где стоит 1. Нажимаем на второй ряд, затем ещё раз на нужную точку, чтобы выделить только её. Открываем «Формат точки данных».

На вкладке маркера выбираем встроенный круг размером 25. Заливка — #C00000, граница — #002060, ширина границы — 1 пт. Это оформление одной точки, не области построения.

Затем выделяем весь второй ряд и для линии выбираем «Нет линии». Остальным точкам маркеры не задаём. Условная высота выделенной точки равна 1; это визуальная метка года перехода, а не рассчитанное пересечение кривой с нулём.

Шаг 20 / Линия

Оформляем накопленный поток и легенду

Выделяем первый ряд. В «Формат ряда данных» задаём цвет линии #C00000 и ширину 5 пт; маркеры первого ряда не включаем.

Открываем «Конструктор диаграмм → Выбрать данные», выбираем первый ряд и нажимаем «Изменить». В поле имени ряда указываем =Резюме!$B$67. Значения первого ряда остаются D67:M67.

В легенде выбираем только запись второго ряда и нажимаем Delete. Сам ряд из источника данных не удаляем: он нужен для маркера. Проверяем, что в «Выбрать данные» по-прежнему два ряда, а в легенде видна подпись накопленного потока.

Шаг 21 / Название

Связываем название с показателем PB

Выделяем название диаграммы и в строке формул вводим исходную ссылку =Резюме!$J$14:$K$14, затем нажимаем Enter. Связь выводит подпись показателя PB и значение K14. После ввода убеждаемся, что название показывает и подпись PB, и значение K14; повторяем проверку после изменения сценария.

Для названия задаём Arial Narrow, полужирный, 24 пт, как в исходном оформлении. Через «Вставка → Фигуры» добавляем стрелку от названия к выделенной точке. Это декоративная стрелка: её положение не рассчитывается по формулам.

Поднимаем диаграмму над B66:M68, чтобы она визуально закрыла вспомогательные данные. Не удаляем эти данные; для их проверки диаграмму можно временно сдвинуть.

Шаг 21 / Название / Сверка результата

Сверка в Excel

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

Разбор / Окупаемость

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

Показатель PB в заголовке сохраняет определение предыдущего модуля. Годовую кривую строим по накопленному потоку из строки 52, а маркер — по строгому переходу из отрицательной суммы в положительную. Они не рассчитывают точную дату окупаемости и не интерполируют её внутри года.

Если сумма попала точно в ноль, строгое условие строки 68 может не дать метки. Если положительного перехода нет, пустой индикатор нельзя объявлять окупаемостью. Если знак меняется несколько раз, проверяем сам ряд и не ограничиваемся одной выделенной точкой.

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

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

Сохраняем итоговую учебную модель

Возвращаем B4=1, оставляем H22=−25,9% как исторический стресс матрицы и пересчитываем таблицу данных. Сверяем производство с физическими объёмами, денежную проекцию — с годовой отчётностью. Проверяем четыре индикатора отдельно и ошибки формул на рабочих листах.

На диаграмме должны остаться два ряда, одна запись легенды, связь названия с PB и проверенный вручную маркер. Числа, подписи и годы должны читаться полностью; данные под диаграммой не удаляем.

Сохраняем книгу. Итоговый контрольный файл курса — М08_Контроль.xlsx. Он объединяет построенные в упражнении исходные данные, расчёты, отчётность и представление результатов.