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

Вводная / Год и расчётный период

Переносим исходные данные на временную шкалу

Открываем М03_Контроль_v5.xlsx и сохраняем отдельную рабочую копию. Исходные файлы оставляем без изменений.

На листе «Исходные данные» подготовлены годовые суммы и коэффициенты. На новом листе «РИД» распределяем их по календарю модели: первые 36 месяцев помесячно, далее поквартально до конца 2024 года. РИД означает «распределение исходных данных».

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

Сохраняем структуру финансирования 30/70, НДС 18% и налог на прибыль 20%. Долг пока начинается 31.12.2015; позже изменим эту дату.

На РИД годовые таблицы и расчётные строки используют одинаковые буквы столбцов, но разные шкалы времени. Проверяем заголовок нужного блока: например, Q31 — коэффициент 2016 года, а Q32 — годовой коэффициент для февраля 2015 года по календарю строк 5:6.

Шаг 01 / Заготовка

Создаём лист для дальнейших расчётов

1. В рабочей копии добавляем новый пустой лист и называем его «Пустой лист».
2. С листа «Исходные данные» копируем A1:EE25 и вставляем на новый лист через «Специальная вставка → Форматы». Затем копируем A1:O25 и вставляем только «Значения»: так перенесутся подписи и статические данные. Формулы календаря и флагов задаём ссылками в следующих шагах. Отдельной специальной вставкой «Ширины столбцов» сохраняем ширины каркаса.
3. Ниже строки 25 пока ничего не размещаем. Палитру стилей и блоки исходных расчётов на заготовку не переносим.
4. Выделяем столбцы EF:XFD и выполняем «Главная → Формат → Скрыть или отобразить → Скрыть столбцы». Расчётная область P:EE останется видимой.
5. Нажимаем Ctrl + Home и временно уменьшаем масштаб, чтобы одновременно видеть строки 1:26 и столбцы A:P. Выбираем P26 и выполняем «Вид → Закрепить области → Закрепить области»: строки 1:25 и столбцы A:O останутся видимыми. После закрепления возвращаем удобный масштаб.

Сохраняем параметры предыдущего модуля: ширина G — 37, J и O — 16, K — 12, P:EE — 13 в диалоге Excel. Ширина столбца K нужна, чтобы целиком видеть предупреждение «ОШИБКА!» в контрольных ячейках. Для длинных подписей G включаем перенос текста и автоподбор высоты строки; после этого восстанавливаем отступ 2. Для единиц в H сохраняем стиль «ед_изм».

В «Формулы → Диспетчер имён» проверяем, что используемые имена имеют область «Книга». Новые имена уровня листа не создаём.

Шаг 01 / Заголовок листа

Связываем заголовок с новой вкладкой

После вставки значений A1 содержит прежнее название листа. Заменяем его формулой, которая возвращает имя текущей вкладки:

=ПСТР(ЯЧЕЙКА("ИМЯФАЙЛА";A1);ПОИСК("]";ЯЧЕЙКА("ИМЯФАЙЛА";
A1))+1;255)

Сначала сохраняем книгу, затем нажимаем F9. В A1 должен появиться текст «Пустой лист». После копирования заготовки на новый лист эта же формула покажет его имя.

«Сценарий» и «Наличие ошибок» пока только служебные подписи. В этом уроке они не заменяют проверок, которые мы создадим ниже.

Шаг 02 / Связи календаря

Берём даты и годы из исходных данных

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

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

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

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

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

Каждую формулу P заполняем вправо отдельно до EE через Ctrl + R. Формат дат строк 5:6 и формат года строки 9 сохраняем из каркаса. O6 — единственная опорная дата перед первым периодом.

Шаг 02 / Номера периодов

Связываем остальные номера календаря

Аналогично восстанавливаем строки нумерации. Каждую строку заполняем от P до EE.

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

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

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

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

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

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

Шаг 02 / Флаги

Связываем этапы проекта и финансирования

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

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

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

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

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

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

Проверяем последнюю ячейку каждой заполненной строки: в EE ссылка должна вести на ту же строку и столбец EE листа «Исходные данные».

Шаг 03 / Лист РИД

Проверяем заготовку и создаём рабочий лист

1. Сопоставляем первые даты и последние активные периоды с «Исходными данными». Строки 5:6, 9:14, 17:18, 21:22 и 24 должны повторять готовый календарь.
2. В январе 2016 года накопительный номер месяца в AB14 равен 13, а не 1.
3. При исходной дате долга 31.12.2015 ноябрьский флаг Z22 равен 0, декабрьский AA22 — 1.
4. Проверяем группировки календаря. Если они не сохранились при вставке, сначала выделяем строки 7:24 и выполняем «Данные → Группировать». Затем отдельно группируем 9:14, 17:18 и 21:22: внутри общего блока появится второй уровень.
5. Через меню вкладки «Переместить или скопировать → Создать копию» копируем «Пустой лист». Новую вкладку называем «РИД».

Нажимаем F9: A1 нового листа должен показывать «РИД». Заготовку оставляем для следующего урока. Дальше все адреса без уточнения листа относятся к РИД.

Шаг 04 / Индексация доходов

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

Вводим B27 «Индексация», C28 «Доходы», D29 «Темпы роста», D38 «Темп прироста». Применяем к главному заголовку «Заголовок1(к)», к подзаголовкам — «Заголовок3».

Подписи оформляем стилем «Обычный», единицы — «ед_изм». Переносим годы из P175:Y175 «Исходных данных» в P30:Y30 как значения и применяем стиль «Таблица». Получится 2015–2024.

Шаг 04 / Годовой коэффициент

Выбираем индекс для года каждого периода

Связываем годовой ряд индексации доходов с уже выбранным сценарием М3. Формула связи сохраняет автоматическое обновление при смене сценария.

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

Заполняем P31:Y31 через Ctrl + R и применяем «Вход_данные». Это годовой ряд, он заканчивается в Y.

=ЕСЛИ(P$5=" ";" ";СМЕЩ($O31;;ПОИСКПОЗ(ГОД(P$6);$P$30:$Y$30;
0)))

Заполняем P32:EE32. ГОД берёт год из даты конца периода, ПОИСКПОЗ находит его в P30:Y30, СМЕЩ выбирает соответствующий коэффициент строки 31.

Для января 2015 года коэффициент равен 1,067 и отображается как 106,70%. Это коэффициент роста; темп прироста составляет 6,7%.

Шаг 05 / Индекс периода

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

='Исходные данные'!P$116

='Исходные данные'!P$117

=ЕСЛИ(P$5=" ";" ";P32^(P33/P34))

=ПРОИЗВЕД($P35:P35)

Каждую строку заполняем от P до EE. P33 — количество дней в периоде, P34 — в году. Степень P33/P34 переводит годовой коэффициент в коэффициент месяца или квартала.

Строка 36 перемножает все периодные коэффициенты от начала проекта до текущего столбца. В P берётся только P35, в Q — произведение P35:Q35.

Для коэффициентов строк 31:32 и 35:36 задаём формат 0,00%. Количество дней в строках 33:34 показываем целыми числами.

Шаг 05 / Индекс на конец проекта

Отделяем коэффициент от темпа прироста

=P36-1

Заполняем P39:EE39. Здесь из накопленного коэффициента вычитается 1: например, коэффициент 1,56 соответствует приросту 56%. Формат строки — 0,00%.

=ПРОИЗВЕД(P31:EE31)

=ПРОИЗВЕД(P35:EE35)

=СУММПРОИЗВ($P36:$EE36*($P$6:$EE$6=Дата_конца_проекта))

=СУММПРОИЗВ($P39:$EE39*($P$6:$EE$6=Дата_конца_проекта))

O31 перемножает годовые коэффициенты, O35 — коэффициенты расчётных периодов. СУММПРОИЗВ в O36 и O39 выбирает значение именно на дату конца проекта из календарной строки 6.

В O31:O39 не нужно складывать индексы. Для рассматриваемого ряда накопленный коэффициент на конец проекта — около 1,56005106, прирост — около 56,01%.

Шаг 05 / Контроль индекса

Сравниваем два способа накопления

=O31-O35

=O31-O36

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

Для самопроверки принимаем допуск 0,000001: модуль каждой разницы должен быть меньше этой величины. Не заменяем формулы вручную нулями.

В последнем столбце EE35 использует EE32 и долю EE33/EE34; EE36 перемножает $P35:EE35; EE39 вычитает 1 из EE36. В этих формулах начальный столбец накопления остаётся закреплённым.

Группируем строки 29:39, сохранив заголовок доходов видимым. Формулы итогов оформляем стилем «СУММА», контрольные K — «Обычный».

Итоги O31, O35 и O36 показаны обычным числом: коэффициент около 1,56 соответствует 156%. Прирост O39 — около 0,56, то есть 56%. В расчётных столбцах те же виды коэффициентов отображаются процентным форматом.

Шаг 06 / Индексация расходов

Повторяем блок для расходов

Копируем блок строк 29:39 в строки 42:52 вместе с оформлением. В C41 вводим «Расходы», D42 — «Темпы роста», D51 — «Темп прироста».

В G43 оставляем «Период», в G44 вводим «Выбор варианта индексации расходов». Остальные подписи и единицы повторяют блок доходов со сдвигом на 13 строк. Годы P43:Y43 повторяют P30:Y30.

После копирования восстанавливаем следующие ссылки, в том числе на общий ряд годов:

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

=ЕСЛИ(P$5=" ";" ";СМЕЩ($O44;;ПОИСКПОЗ(ГОД(P$6);$P$30:$Y$30;
0)))

='Исходные данные'!P$116

='Исходные данные'!P$117

Строку 44 заполняем до Y, строки 45:47 — до EE. В P44 не добавляем точку после адреса. К P44:Y44 применяем «Вход_данные».

Шаг 06 / Накопление расходов

Рассчитываем отдельный индекс расходов

=ЕСЛИ(P$5=" ";" ";P45^(P46/P47))

=ПРОИЗВЕД($P48:P48)

=P49-1

Каждую строку заполняем до EE. Для строк 44:45, 48:49 и 52 задаём формат 0,00%; для дней 46:47 — целые числа.

Два блока независимы: доходы используют выбор J176 исходного листа, расходы — J179. В исходном сценарии это разные ряды.

В последнем столбце EE48 использует EE45, EE46 и EE47, накопление EE49 начинается с $P48, EE52 равно EE49−1.

Шаг 06 / Контроль расходов

Сверяем итоги второго индекса

=ПРОИЗВЕД(P44:EE44)

=ПРОИЗВЕД(P48:EE48)

=СУММПРОИЗВ($P49:$EE49*($P$6:$EE$6=Дата_конца_проекта))

=СУММПРОИЗВ($P52:$EE52*($P$6:$EE$6=Дата_конца_проекта))

=O44-O48

=O44-O49

В O44, O48 и O49 получится около 2,19890509; в O52 — около 119,89%. Для K48 и K49 используем тот же допуск 0,000001.

Группируем строки 42:52. Годовые ряды по-прежнему занимают P:Y, периодные — P:EE.

Итоги O44, O48 и O49 показаны как коэффициенты — около 2,20, то есть 220% от исходного уровня. В P:EE используем процентный формат: он меняет отображение коэффициентов, но не их значения.

Шаг 07 / Выручка

Связываем годовую выручку с исходными данными

В B54 вводим «Выручка». Годы P55:Y55 берём из P30:Y30 как значения.

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

=ЕСЛИ(P$5=" ";" ";СМЕЩ($O56;;ПОИСКПОЗ(ГОД(P$6);$P$30:$Y$30;
0)))

Заполняем P56:Y56 и применяем «Вход_данные». P57 заполняем до EE. Годы указаны в строке 55, а годовая выручка берётся именно из исходной строки 190.

Шаг 07 / Выручка по периодам

Распределяем выручку по дням эксплуатации

='Исходные данные'!P$116

='Исходные данные'!P$117

=ЕСЛИ(P$5=" ";" ";P57*(P58/P59)*(P18=1))

Заполняем каждую строку до EE. Формула P60 умножает годовую выручку на долю дней периода и на флаг эксплуатации. Во время строительства флаг равен 0, поэтому выручка отсутствует.

В месячных периодах в числителе стоит число дней месяца; в квартальных — число дней квартала. Делитель — 365 или 366 дней соответствующего года. В EE60 должен сохраниться множитель (EE18=1).

Шаг 07 / Контроль выручки

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

=СУММ(P56:Y56)

=СУММ(P60:EE60)

=O56-O60

Оба итога должны быть 58 000 рублей, а K60 — нулём с допуском 0,000001. В 2015–2017 годах выручки нет; первый год эксплуатации — 2018.

К O56/O60 применяем «СУММА», к денежным расчётным строкам — «Расчеты». Строки 58:59 оформляем целыми числами. Группируем строки 55:60.

Проверяем два участка календаря: P:T показывает первые пять месяцев строительства; AY:BC — декабрь 2017 года и четыре квартала 2018 года. Номера и даты над столбцами должны соответствовать выбранному периоду.

Шаг 07 / Проверка по Excel

Смотрим первый год эксплуатации

Сопоставляем даты с выручкой по периодам. В декабре 2017 года эксплуатация ещё не началась; четыре следующих столбца относятся к кварталам 2018 года.

Шаг 08 / Расходы

Повторяем распределение расходов

В B62 вводим «Расходы». Вводим годы в P63:Y63. В G63:H68 повторяем подписи выручки, заменив её расходами: «Расходы по годам», «Годовые показатели расходов для данного периода модели», дни периода, дни года, «Расходы для данного периода модели».

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

=ЕСЛИ(P$5=" ";" ";СМЕЩ($O64;;ПОИСКПОЗ(ГОД(P$6);$P$30:$Y$30;
0)))

='Исходные данные'!P$116

='Исходные данные'!P$117

P64 заполняем до Y и оформляем «Вход_данные»; строки 65:67 — до EE. Ссылки на дни должны вести в исходные строки 116 и 117.

Шаг 08 / Контроль расходов

Учитываем фазу и проверяем сумму расходов

=ЕСЛИ(P$5=" ";" ";P65*(P66/P67)*(P18=1))

Заполняем P68:EE68. Как и выручка, операционные расходы в этом учебном примере возникают только при флаге эксплуатации 1.

=СУММ(P64:Y64)

=СУММ(P68:EE68)

=O64-O68

Итог исходных годовых расходов и итог расходов по расчётным периодам — 17 400 рублей; K68 близко к нулю в пределах 0,000001. В EE68 должны использоваться EE65, EE66/EE67 и флаг EE18.

Сохраняем те же стили денежных строк и дней. Группируем строки 63:68.

Шаг 09 / Инвестиции

Распределяем инвестиционный бюджет

В B70 вводим «Инвестиции». Годы располагаем в P71:Y71, а в G71:H76 повторяем структуру предыдущих блоков: годовая сумма, показатель года для периода, дни периода, дни года, сумма инвестиций периода.

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

=ЕСЛИ(P$5=" ";" ";СМЕЩ($O72;;ПОИСКПОЗ(ГОД(P$6);$P$30:$Y$30;
0)))

='Исходные данные'!P$116

='Исходные данные'!P$117

P72 заполняем до Y; строки 73:75 — до EE. Годовые инвестиции связаны с исходной строкой 213. К годовым связям применяем «Вход_данные».

Шаг 09 / Контроль инвестиций

Используем флаг строительства

=ЕСЛИ(P$5=" ";" ";P73*(P74/P75)*(P17=1))

Заполняем P76:EE76. Здесь нужен флаг строительства строки 17, а не эксплуатации: инвестиции распределяются на строительный этап.

=СУММ(P72:Y72)

=СУММ(P76:EE76)

=O72-O76

Оба итога составят 10 000 рублей; K76 должен укладываться в допуск 0,000001. В EE76 проверяем множитель EE17=1. Группируем строки 71:76.

Распределение по доле календарных дней — допущение примера. Если есть фактический или договорный график затрат, он может потребовать другого распределения; выбранный здесь способ не заменяет фактический или договорный график.

Шаг 10 / Акционерные средства

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

В B78 вводим «Акционерное финансирование», в P79:Y79 переносим годы.

В H79 вводим «год», в H80:H86 — «руб.».

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

=СУММ(P80:Y80)

Заполняем P80:Y80. В Y80 должна стоять ссылка на Y228 «Исходных данных». К P80:Y80 применяем «Вход_данные». Общая сумма O80 равна 3000 рублей.

Шаг 10 / Движение капитала

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

=O84

=$P$80*(P14=1)

=-МИН(P76;P81+P82)*(P21=1)

=СУММ(P81:P83)

Заполняем каждую строку до EE. O84 оставляем пустой: до первого периода остатка капитала нет.

P81 берёт конечный остаток предыдущего периода. Вклад P82 поступает один раз: P14=1 означает первый месяц всего проекта. В январе 2016 года номер равен 13, поэтому нового вклада не возникает.

МИН ограничивает финансирование инвестиций доступными средствами. Использование в строке 83 имеет отрицательный знак и действует только при флаге акционерного финансирования P21=1. Строка 84 суммирует начальный остаток, поступление и использование.

Шаг 10 / Остаток потребности

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

=-P83

=ЕСЛИ(P$5=" ";" ";P76-P85)

Заполняем обе строки до EE. Строка 85 меняет знак использованных средств: для сравнения с инвестициями вклад источника показывается положительно. Строка 86 — инвестиции периода минус эта сумма.

В ноябре 2015 года инвестиции составят около 273,97260 рубля, а доступный остаток капитала — 223,74429 рубля. Оставшаяся потребность — около 50,22831 рубля. Пока определяем только оставшуюся потребность. В следующем блоке проверяем, доступен ли в этот период долг.

В EE81 ссылка должна вести на ED84; в EE83 используются EE76, EE81, EE82 и флаг EE21. Группируем строки 79:86.

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

Сверяем движение акционерных средств

=СУММ(P82:EE82)

=СУММ(P83:EE83)

=СУММ(P85:EE85)

=СУММ(P86:EE86)

=O80+O83

=O82+O83

O82 и O85 равны 3000, O83 — −3000. Суммарная потребность после использования капитала O86 составляет около 7000 рублей.

K83 сравнивает годовую сумму капитала с его использованием, K84 — поступление капитала с использованием. Оба результата проверки должны быть близки к нулю. Знак плюс в этих проверках нужен потому, что строка использования отрицательная.

Шаг 11 / Заёмный источник

Задаём годовой объём долга

В B88 вводим «Заёмное финансирование», в P89:Y89 переносим годы.

В H89 вводим «год», в H90:H96 — «руб.».

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

=СУММ(P90:Y90)

Заполняем P90:Y90; это ссылки на исходную строку 230. Итог O90 — около 7000 рублей. Годовой ряд оформляем «Вход_данные».

Y89 означает 2024 год; Y91:Y96 — октябрь 2015 года по календарю 5:6. Различаем годовую и расчётную шкалы.

Шаг 11 / Доступность лимита

Отделяем лимит от фактического финансирования

=O94

=$O$90*(И(P22=1;O22=0))

Заполняем строки до EE. O94 оставляем пустой: до первого периода доступного остатка ещё нет.

Строка 91 переносит остаток доступного лимита с конца предыдущего периода. В строке 92 общий лимит O90 становится доступен при первом переходе флага долга из 0 в 1. Условие И сравнивает текущий P22 с предыдущим O22.

Доступный лимит и фактически привлечённое финансирование — разные величины. Эта формула не означает, что весь лимит сразу потрачен или привлечён на инвестиции. Фактическое использование определяется отдельно и ограничено потребностью периода.

Шаг 11 / Использование долга

Покрываем оставшуюся потребность периода

=-МИН(P86;P91+P92)*(P22=1)

=СУММ(P91:P93)

=-P93

=ЕСЛИ(P$5=" ";" ";P86-P95)

Заполняем каждую строку до EE. МИН выбирает меньшую из оставшейся потребности P86 и доступного лимита P91+P92. Пока флаг P22 равен 0, финансирование не используется.

Строка 94 — неиспользованный остаток доступного лимита, строка 95 — положительная величина фактического финансирования инвестиций долгом. Строка 96 показывает потребность, которую в данном периоде не покрыл ни один источник.

В последнем столбце EE91 ссылается на ED94; EE92 сравнивает EE22 и ED22; EE93 использует EE86 и доступный остаток EE91+EE92. Группируем строки 89:96.

Шаг 11 / Проверка исходной даты

Не пропускаем непокрытый период

=СУММ(P92:EE92)

=СУММ(P93:EE93)

=СУММ(P95:EE95)

=СУММ(P96:EE96)

=O90+O93

=O92+O93

Сейчас J146 «Исходных данных» по-прежнему равна 31.12.2015. Просматриваем окно Y:AC: октябрь, ноябрь, декабрь 2015 года, январь и февраль 2016 года.

В ноябре строка 86 уже содержит нехватку около 50,22831 рубля, но Z22 ещё равен 0. Поэтому Z95 не покрывает эту потребность, а Z96 показывает разрыв. В декабре AA22 равен 1, но более поздняя доступность денег не устраняет нехватку предыдущего периода.

Сохраняем отдельную копию этого состояния «М04_До_переноса_даты.xlsx». Она нужна, чтобы на следующем шаге сравнить состояние до и после изменения и проследить причину результата. Сохраняем разрыв до изменения даты долга.

Шаг 12 / Исправление графика

Сдвигаем доступность долга на 60 дней

Сохраняем структуру финансирования 30/70. Чтобы закрыть помесячный разрыв, меняем только дату доступности долга.

Переходим на лист «Исходные данные» и в J146 вводим:

=Дата_конца_акционерного_финансирования-60

Результат — 01.11.2015. Вычитаются именно 60 календарных дней; это не общее правило вычитания двух календарных месяцев.

Формулу J151 оставляем прежней. Для проверки она имеет вид:

=КОНМЕСЯЦА(J146;J147*Число_месяцев_в_году-1)

При прежнем сроке 7 лет конец долга автоматически станет 31.10.2022 вместо 30.11.2022. Отдельную дату константой в J151 не вводим.

Шаг 12 / Проверка изменения

Убеждаемся, что ноябрьский разрыв закрыт

Возвращаемся на «РИД» и нажимаем F9. Сопоставляем то же окно Y:AC до и после перехода.

Лимит становится доступен в ноябре, а фактическое использование равно потребности ноября. Остаток доступного лимита переносится в следующие периоды.

Проверяем, что O95 близко к 7000, O96, K93 и K94 — к нулю. Небольшие остатки порядка 0,000000000001 допустимы как результат машинной арифметики; разрыв в 50 рублей такой погрешностью не является.

Шаг 13 / Баланс инвестиций

Собираем потребность и источники рядом

В B98 вводим «Потребность в инвестициях», в B101 — «Источники». В G99 — «Инвестиции», G100 — «Итого», G102 — «Акционерное финансирование», G103 — «Заёмное финансирование», G104 — «Итого», G106 — «Проверка баланса инвестиций и источников». Единицы H — «руб.».

=P76

=СУММ(P99)

=P85

=P95

Заполняем каждую строку до EE. Потребность берётся из инвестиций строки 76; источники — из положительных величин строк 85 и 95.

Шаг 13 / Контроль каждого периода

Сравниваем инвестиции с суммой источников

=СУММ(P102:P103)

=P100-P104

Заполняем строки до EE. К P106:EE106 применяем существующий стиль «ТРЕВОГА». Итоги P100:EE100 и P104:EE104 оформляем «СУММА».

Сначала проверяем каждый активный период строки 106, особенно ноябрь 2015 года. В общей сумме положительная ошибка одного периода и отрицательная ошибка другого могут взаимно сократиться.

В EE106 должно сравниваться EE100−EE104. Инвестиции и источники показываются в одной временной сетке с теми же фазовыми флагами.

Шаг 13 / Итоговые суммы

Завершаем контроль листа РИД

=СУММ(P99:EE99)

=СУММ(P100:EE100)

=СУММ(P102:EE102)

=СУММ(P103:EE103)

=СУММ(P104:EE104)

=СУММ(P106:EE106)

=O106

Ожидаемые итоги: инвестиции — около 10 000, капитал — 3000, фактическое финансирование долгом — около 7000. K106 и O106 должны быть близки к нулю при допуске 0,000001.

К O99:O104 применяем «СУММА», к O106 и K106 — «ТРЕВОГА». Группируем строки 99:100 и 102:106, сохранив заголовки блоков видимыми.

Самопроверка / Завершённое распределение

Проверяем связи и сохраняем результат

1. Сверяем все годовые ссылки: доходы — исходная строка 190, расходы — 200, инвестиции — 213, капитал — 228, долг — 230. Особенно проверяем Y80 → Y228.
2. Проверяем правый край периодных формул в EE, накопление индексов от P и ссылки начальных остатков на предыдущий столбец.
3. Календарь содержит 36 месячных периодов. После изменения даты начало долга — 01.11.2015, конец — 31.10.2022; доли 30/70 сохранены.
4. Убеждаемся, что K35/K36/K48/K49, K60/K68/K76, K83/K84, K93/K94 и K106 близки к нулю, а строка 106 проверена по каждому периоду.
5. В G27:G106 включаем перенос текста и автоподбор высоты строк; в H сохраняем читаемые единицы. Проверяем, что суммы в O видны целиком при ширине 16.

Распределяем суммы по календарным дням. Формулы не заменяем фактическими значениями.

Сохраняем отдельный результат урока «М04_После_урока_01.xlsx». Лист «Пустой лист» оставляем для следующего урока, где появятся расчёты в номинальных ценах.

Вводная / Постоянные и номинальные цены

Переходим от распределения к расчётам

Продолжаем работу в отдельной книге после первого урока М4. На «РИД» уже есть распределённые потоки и накопленные индексы. Теперь собираем их на листе «Расчеты».

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

Для перехода от базовой суммы к номинальной умножаем её на накопленный индекс. Это не изменение физического объёма производства: объём и базовая цена уже учтены в выручке.

Например, при накопленном коэффициенте 1,1 сумма 100 рублей в постоянных ценах соответствует 110 рублям в номинальных ценах периода. Величина индекса определяет изменение уровня цен.

Шаг 01 / Новый расчётный лист

Создаём вкладку «Расчеты»

1. Через меню вкладки «Пустой лист» выбираем «Переместить или скопировать», устанавливаем «Создать копию».
2. Называем новую вкладку «Расчеты». Нажимаем F9 и проверяем A1.
3. Убеждаемся, что календарь и флаги остаются ссылками на «Исходные данные», а ниже строки 25 пока нет блоков другого урока.
4. В меню «Цвет ярлычка» выбираем различимые цвета для трёх рабочих листов «Исходные данные», «РИД», «Расчеты». Цвет помогает ориентироваться и не участвует в расчётах.

Сохраняем закрепление P26 и ширины: G — 37, J/O — 16, P:EE — 13. Для длинных подписей в G включаем перенос текста и автоподбор высоты строк. Палитру и лишние блоки в расчётный лист не переносим.

Шаг 02 / Выручка

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

В B27 вводим «Выручка» со стилем «Заголовок1(к)». Подготавливаем подписи:

=РИД!P60

=РИД!P36

Заполняем обе строки до EE. К исходным диапазонам «РИД» P60:EE60 и P36:EE36 применяем «Выход_данные», к связанным P28:EE29 на «Расчеты» — «Вход_данные».

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

Шаг 02 / Номинальная выручка

Умножаем базовую сумму на накопленный индекс

=ЕСЛИ(P$5=" ";" ";P28*P29)

Заполняем P30:EE30 и применяем «Расчеты». В каждой ячейке умножается выручка этого же периода на индекс, накопленный к его концу.

Пока эксплуатации нет, базовая выручка равна нулю, поэтому индексирование не создаёт выручку во время строительства. В EE30 должны использоваться именно EE28 и EE29.

=СУММ(P28:EE28)

=СУММПРОИЗВ($P29:$EE29*($P$6:$EE$6=Дата_конца_проекта))

=СУММ(P30:EE30)

O28 суммирует базовую выручку, O30 — номинальную. O29 выбирает индекс на конец проекта по календарной строке 6; складывать коэффициенты по периодам нельзя.

Ориентиры исходного сценария: база 58 000 рублей, номинальная выручка — около 79 557,58636 рубля, конечный индекс — около 1,56005106. O28 и O30 оформляем «СУММА», O29 — числовым форматом коэффициента.

Шаг 03 / Расходы

Связываем расходы с их собственным индексом

В B32 вводим «Расходы». Создаём отдельный блок:

=РИД!P68

=РИД!P49

Заполняем обе строки до EE. На «РИД» передаём в расчёты диапазоны P68:EE68 и P49:EE49; применяем «Выход_данные». На «Расчеты» к P33:EE34 применяем «Вход_данные».

Для строки 34 задаём 0,0000. Она использует накопленный индекс расходов из строки 49 РИД, а не индекс доходов.

Шаг 03 / Номинальные расходы

Рассчитываем и сверяем расходы

=ЕСЛИ(P$5=" ";" ";P33*P34)

Заполняем P35:EE35 и применяем «Расчеты». В EE35 умножаем EE33 на EE34.

=СУММ(P33:EE33)

=СУММПРОИЗВ($P34:$EE34*($P$6:$EE$6=Дата_конца_проекта))

=СУММ(P35:EE35)

Базовые расходы O33 — 17 400 рублей, номинальные O35 — около 30 388,98734 рубля. O34 выбирает конечный индекс расходов — около 2,19890509.

Группируем строки 28:30 и отдельно 33:35, оставив заголовки выручки и расходов видимыми.

Сценарий / Индексы

Проверяем выбранные индексы

Сравниваем накопленные индексы доходов и расходов на конец проекта. В этом сценарии индекс расходов выше.

Шаг 04 / Инвестиции и источники

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

Создаём три блока: B37 «Инвестиции», B40 «Акционерное финансирование», B43 «Заёмное финансирование». В G38, G41 и G44 вводим «Номинальные цены расчётного периода», единицы H — «руб.».

=РИД!P76

=РИД!P85

=РИД!P95

Заполняем каждую строку до EE. Это инвестиции и фактическое финансирование из «РИД», а не доступный кредитный лимит. Для исходных рядов РИД применяем «Выход_данные», для связей на «Расчеты» — «Вход_данные».

Инвестиционный бюджет задан в ценах расчётных периодов. Повторно его не индексируем.

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

Сверяем перенесённые суммы

=СУММ(P38:EE38)

=СУММ(P41:EE41)

=СУММ(P44:EE44)

Итоги должны совпадать с соответствующими суммами на РИД: около 10 000 рублей инвестиций, 3000 рублей капитала и около 7000 рублей фактического финансирования долгом.

Проверяем правый край: EE38 ссылается на РИД!EE76, EE41 — на РИД!EE85, EE44 — на РИД!EE95. Все три строки используют те же периоды, что и календарь над расчётами.

Группируем отдельно строки 38:39, 41:42 и 44:45. Итоговые ячейки в O оформляем «СУММА».

Шаг 05 / Баланс

Повторяем баланс на расчётном листе

В B46 вводим «Потребность в инвестициях», B49 — «Источники». Подписи: G47 «Инвестиции», G48 «Итого», G50 «Акционерное финансирование», G51 «Заёмное финансирование», G52 «Итого», G54 «Проверка баланса инвестиций и источников». В H47 указываем «руб.»; все денежные строки блока также выражены в рублях.

=P38

=СУММ(P47)

=P41

=P44

Заполняем каждую строку до EE. Повторение блока нужно для наглядности: при работе со следующими сценариями потребность, источники и контроль будут находиться на одном расчётном листе.

Шаг 05 / Поэлементный контроль

Проверяем равенство по каждому периоду

=СУММ(P50:P51)

=P48-P52

Заполняем строки до EE. К P48:EE48 и P52:EE52 применяем «СУММА», к P54:EE54 — «ТРЕВОГА».

Просматриваем каждый активный период. В ноябре 2015 года финансирование уже учитывает начало долга 01.11.2015. В EE54 сравниваем EE48 с EE52.

Нулевой общий итог не заменяет эту проверку: суммы ошибок разных периодов могли бы взаимно сократиться.

Шаг 05 / Итоги баланса

Завершаем контроль расчётного листа

=СУММ(P47:EE47)

=СУММ(P48:EE48)

=СУММ(P50:EE50)

=СУММ(P51:EE51)

=СУММ(P52:EE52)

=СУММ(P54:EE54)

=O54

O47, O48 и O52 близки к 10 000; O50 — 3000, O51 — 7000. O54 и K54 должны быть нулевыми с допуском 0,000001.

Для итогов денежных рядов используем «СУММА», для O54/K54 — «ТРЕВОГА». Группируем строки 47:48 и 50:54.

Самопроверка / Перед расчётами долга

Проверяем новый лист и сохраняем контрольную книгу

1. Убеждаемся, что базовые строки выручки и расходов названы постоянными ценами 2014 года, индексированные — номинальными ценами периода.
2. Проверяем независимость индексов: строка 29 ссылается на РИД!36, строка 34 — на РИД!49. Их единицы — коэффициент.
3. Меняем выбранный вариант индексации доходов в своей рабочей копии. Должна измениться номинальная выручка, а базовая выручка сохраниться. Возвращаем «МЭР».
4. Аналогично проверяем выбор индекса расходов и возвращаем «Всемирный банк». Не меняем два списка одновременно: так легче проследить каждую связь.
5. Проверяем, что инвестиции не индексируются второй раз, а их источники и контроль повторяют РИД.
6. Проверяем каждое значение строки 54 и итог K54 с допуском 0,000001.

Включаем перенос текста в G27:G54, подбираем высоты строк, проверяем единицы и видимость итогов O. Сохраняем отдельную книгу «М04_Контроль.xlsx».

На этом модуль заканчивается. Выбор графика и вариантов погашения долга начнётся в следующем модуле.