Экономические допущения проекта

Вводная / Условные данные

Добавляем коэффициенты инфляции

Открываем рабочую книгу после модуля 2. Добавляем годовые коэффициенты инфляции и выбираем отдельные ряды для индексации доходов и расходов.

Используем один макропоказатель. Для других показателей создаём такой же блок.

Шаг 01 / Заголовки и единицы

Создаём таблицу коэффициентов

Работаем ниже фаз проекта. Дополнительные строки не вставляем: адреса соответствуют листу с разделительной строкой 25 и закреплением относительно P26.

К B161 применяем «Заголовок1(к)», к C162 — «Заголовок3», к подписям G — «Обычный». В H163 вводим «год», в H164:H170 — «% период». Для единиц используем «ед_изм».

В P163:Y163 вводим годы 2015–2024 по одному году в ячейке и применяем «Таблица». Строку 162 оставляем для заголовка «Прогноз».

Шаг 02 / Источники и допущения

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

В G164:G166 добавляем примечания через Shift + F2: документ, дату версии, показатель и ссылку.

• G164 — «МЭР»: прогноз на 2015 год и 2016–2017 годы; точная версия не подтверждена.
• G165 — «Всемирный банк»: доклад об экономике России №32, сентябрь 2014 года, таблица 12, страница 32. Инфляция — 10% в 2015 году и 8% в 2016 году. Для 2017–2024 годов продлеваем 8%. Первичный доклад ВБ
• G166 — «Консенсус-прогноз аналитиков»: точная версия ноябрьского прогноза не подтверждена.

Шаг 03 / Исходные коэффициенты

Вводим три исходных ряда

Таблица хранит коэффициенты роста цен: 1,067 означает рост на 6,7%, 1,1 — на 10%. В процентном формате они отображаются как 106,70% и 110,00%. Не заменяем 1,1 числом 10%: значения разные.

Семь коротких коэффициентов вводим числами. Для R164 используем вычисление ниже, затем сохраняем результат как значение. Не округляем R164 до 1,043: сохраняем исходную константу. К импортным значениям применяем «Тех. вставка», затем процентный формат 0,00%.

В строке МЭР исходные значения относятся к 2015–2017 годам, в строке ВБ — к 2015–2016. Для остальных лет используем ссылки.

=1+0,0429998280921837

Вводим эту формулу в R164. Копируем ячейку через Ctrl + C и, оставаясь в R164, выбираем «Главная → Вставить → Специальная вставка → Значения». Подтверждаем и нажимаем Esc. В строке формул должно остаться число без начального знака равенства. Так сохраняется исходная точность.

Шаг 04 / Продление рядов

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

Повторяем последнее исходное значение до конца горизонта. Вводим:

=$R$164

=$Q$165

=$R$166

Выделяем S164:Y164 и нажимаем Ctrl + R. Так же заполняем R165:Y165 и S166:Y166. Знак $ закрепляет ссылку на последнее исходное значение.

К формульным диапазонам S164:Y164, R165:Y165 и S166:Y166 применяем стиль «Процент_0,00%». Для всей таблицы P164:Y166 устанавливаем процентный формат 0,00%; оформление восьми импортных значений сохраняем. МЭР после 2017 года повторяет R164, ВБ после 2016 года — Q165, консенсус после 2017 года — R166.

Продление ряда — допущение модели. Значения за эти годы не являются прогнозом исходной организации.

Шаг 05 / Собственные сценарии

Добавляем фиксированный рост и его снижение

В J167 вводим 1,1, в J170 — 0,002. Второе значение означает ежегодное снижение темпа на 0,2 процентного пункта. В J168 ничего вводить не нужно. Вводим четыре формулы:

=J167

=$J$167

=$J$169

=P168-$J$170

Выделяем P167:Y167 и нажимаем Ctrl + R. Формула P168 задаёт только начало снижающегося ряда. Для продолжения выделяем Q168:Y168 и нажимаем Ctrl + R.

К J167, J169:J170 и P167:Y168 применяем стиль «Процент_0,00%» и формат 0,00%. Фиксированный ряд сохраняет 1,1. Снижающийся даёт 1,1; 1,098; 1,096 и далее до 1,082 в 2024 году. Соответствующие темпы — 10%; 9,8%; 9,6% и 8,2%.

Шаг 05 / Собственные сценарии

Сверяем таблицу коэффициентов

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

Шаг 06 / Выбор варианта

Создаём список выбора и выходной ряд

В G172 вводим «Выбор варианта для экспорта в другие листы», в H172 — «% период». Подпись оформляем «Обычный», единицу — «ед_изм».

1. Выделяем J172. Открываем «Данные → Проверка данных».
2. Выбираем тип «Список», источник =$G$164:$G$168.
3. Выбираем «МЭР». Вводим формулу P172, затем заполняем P172:Y172 через Ctrl + R.

=ИНДЕКС($P$164:$Y$168;ПОИСКПОЗ($J172;$G$164:$G$168;0);
СТОЛБЕЦ()-СТОЛБЕЦ($O$172))

ПОИСКПОЗ находит выбранную подпись в G164:G168. ИНДЕКС берёт коэффициент из числовой таблицы P164:Y168: выбирает нужную строку и столбец текущего года.

Часть СТОЛБЕЦ()-СТОЛБЕЦ($O$172) даёт в P номер 1, в Q — 2 и так далее до 10 в Y. Поэтому каждая ячейка возвращает одно годовое значение. Числовой диапазон начинаем со столбца P: если оставить G, номер 1 выберет подпись вместо коэффициента.

Первые значения ряда МЭР — 1,067; 1,044; 1,0429998280921837. Остальные повторяют R164. Для P172:Y172 устанавливаем формат 0,00%.

Шаг 06 / Выбор варианта

Сверяем выбранный ряд

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

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

Выбираем ряд для индексации доходов

К B174 применяем «Заголовок1(к)», к C175 — «Заголовок3», к единицам — «ед_изм». Копируем годы P163:Y163 в P175:Y175 со стилем «Таблица».

Копируем J172 в J176 вместе с проверкой данных. Выбираем «МЭР». Вводим:

=ИНДЕКС($P$164:$Y$168;ПОИСКПОЗ($J176;$G$164:$G$168;0);
СТОЛБЕЦ()-СТОЛБЕЦ($O$176))

Заполняем P176:Y176 через Ctrl + R и задаём процентный формат 0,00%. Этот ряд будет использоваться для индексации доходов при распределении по периодам.

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

Выбираем отдельный ряд для расходов

К C178 применяем «Заголовок3», к единицам — «ед_изм». Копируем годы P163:Y163 в P178:Y178 со стилем «Таблица». Копируем J172 в J179 вместе с проверкой данных и выбираем «Всемирный банк».

=ИНДЕКС($P$164:$Y$168;ПОИСКПОЗ($J179;$G$164:$G$168;0);
СТОЛБЕЦ()-СТОЛБЕЦ($O$179))

Заполняем P179:Y179 через Ctrl + R, устанавливаем формат 0,00%. В 2015 году получится 1,1, в 2016–2024 — 1,08.

Три списка независимы: J172 выбирает общий ряд для передачи на другие листы, J176 — индексацию доходов, J179 — расходов. Изменение одного списка не должно заменять два остальных.

Самопроверка / Пять вариантов

Проверяем переключение сценариев

1. В J172 выбираем «Собственный прогноз - фиксированный рост». Весь P172:Y172 должен повторять 1,1.
2. Выбираем «Собственный прогноз - снижение темпов роста». Начало — 1,1 и 1,098, конец — 1,082.
3. Убеждаемся, что J176 сохраняет «МЭР», J179 — «Всемирный банк».
4. Возвращаем J172 к «МЭР». Сверяем P176 с P164, P179 — с P165.
5. Меняем J167 в рабочей книге и проверяем оба собственных ряда. Затем возвращаем 1,1.

Группируем отдельно строки 162: 172 и 175: 179: выделяем строки по номерам и нажимаем Alt + Shift + →. Заголовки B161 и B174 останутся видимыми. В G161:G179 включаем перенос текста и автоподбор высоты строки. Закрепление P26 сохраняем.

Сохраняем рабочую книгу. Дальше введём мощность, цену и удельные расходы. Позже распределим коэффициенты по месяцам и кварталам на листе «РИД».

Вводная / Масштаб примера

От параметров производства к доходам

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

Максимальная мощность — 1000 тонн в год. В первый год эксплуатации загрузка равна 50%, во второй — 80%, в третий и последующие — 90%. Цена — 10 рублей за тонну.

Пока работаем в базовых ценах. Коэффициенты индексации уже подготовлены, но цена и удельные расходы в этом уроке постоянны. Распределение по периодам и индексацию выполняем позже, на листе «РИД».

Амортизацию не рассчитываем.

Шаг 01 / Производственные параметры

Заполняем блок доходов

В B181 вводим «Доходы» и применяем «Заголовок1(к)». Вводим подписи и параметры:

К подписям G применяем «Обычный», к единицам H — «ед_изм». К J183, J185:J187 и J189 — «Допущение». Для J185:J187 устанавливаем процентный формат без десятичных знаков. В J188 вводим формулу, не заменяем её числом.

Копируем годы P163:Y163 в P182:Y182 со стилем «Таблица». В G185:G187 должны находиться числа 1, 2 и 3.

Шаг 02 / Пользовательский формат

Сохраняем числа в подписях годов

Выделяем G185:G186, нажимаем Ctrl + 1, открываем «Число → Все форматы» и в поле «Тип» вводим:

0\ "год эксплуатации"

Для G187 задаём:

0\ "год эксплуатации и далее"

Обратная косая черта перед пробелом относится к коду формата. В ячейках появятся «1 год эксплуатации», «2 год эксплуатации», «3 год эксплуатации и далее». В строке формул останутся 1, 2 и 3. Выравнивание — слева, отступ — 2.

Для проверки в свободной ячейке вне готового блока складываем G185 с пустой ячейкой. Результат должен быть числом 1. Тестовую формулу в расчётный блок не переносим.

Формат меняет внешний вид, а не значение. Этот приём полезен для подписей физических единиц и номеров периодов.

Шаг 03 / Доступная мощность

Показываем начало эксплуатации

До эксплуатации доступная мощность равна нулю. Начиная с года, который определяем по Дата_начала_эксплуатации, она равна J183.

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

Заполняем P183:Y183 через Ctrl + R. Для исходного календаря 2015–2017 годы дадут нули, 2018–2024 — по 1000 тонн в год.

Год берётся из строки 182, дата — из уже созданного имени. Не вводим 2018 прямо в формулу: она должна сохранять связь с параметром проекта.

Шаг 04 / Ступени загрузки

Применяем три уровня загрузки

Для первого года используем J185, следующего — J186, третьего и всех последующих — J187:

=ЕСЛИ(P182=ГОД(Дата_начала_эксплуатации);$J$185;0)+
ЕСЛИ(P182=ГОД(Дата_начала_эксплуатации)+1;$J$186;0)+
ЕСЛИ(P182>=ГОД(Дата_начала_эксплуатации)+2;$J$187;0)

Заполняем P184:Y184 через Ctrl + R. Устанавливаем процентный формат без десятичных знаков.

Три условия выбирают нужный уровень загрузки. До эксплуатации ни одно не выполняется, поэтому сумма равна нулю.

Шаг 05 / Выпуск и средняя загрузка

Рассчитываем выпуск и среднюю загрузку

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

Умножаем максимальную мощность на загрузку:

=P183*P184

Заполняем P188:Y188 через Ctrl + R. Для расчёта средней загрузки сначала суммируем мощность и выпуск:

=СУММ(P183:Y183)

=СУММ(P188:Y188)

В J188 вводим:

=O188/O183

К O183 и O188 применяем «СУММА», к J188 — «Расчеты» и процентный формат без десятичных знаков. Выпуск за горизонт — 5800 тонн, доступная мощность за годы эксплуатации — 7000 тонн; средняя загрузка около 83%.

Шаг 06 / Цена и выручка

Рассчитываем годовую выручку

Цена появляется с начала эксплуатации. Пока она равна параметру J189, без индексации.

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

Заполняем P189:Y189 через Ctrl + R. Выручка — произведение выпуска и цены:

=P188*P189

Заполняем P190:Y190. Итог в столбце O:

=СУММ(P190:Y190)

К P190:Y190 применяем «Л_Операция», к O190 — «СУММА». Выручка: 5000 рублей в 2018 году, 8000 — в 2019, по 9000 — в 2020–2024. За весь горизонт — 58 000 рублей в базовых ценах.

Шаг 07 / Удельные расходы

Вводим три статьи расходов

В B192 вводим «Расходы» и применяем «Заголовок1(к)».

К J196:J198 применяем «Допущение», к единицам H — «ед_изм». Копируем годы P163:Y163 в P193:Y193 со стилем «Таблица». В J199 считаем сумму:

=СУММ(J196:J198)

К J199 применяем «Расчеты»: получится 3 рубля на тонну. К G199:H199 и G200:H200 — «Л_ИТОГО». Строка 195 — подпись, формулу расходов в P195 вводить не нужно.

ФОТ — фонд оплаты труда. В описании исходного примера 1 рубль на эту статью включает оплату труда и социальные начисления. Здесь сохраняем 1 рубль; отдельный процент самостоятельно к нему не добавляем.

Шаг 08 / Выпуск и первые статьи

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

Переносим годовой выпуск в строку 194:

=P188

Заполняем P194:Y194 через Ctrl + R. Оплата труда и материалы появляются только с начала эксплуатации:

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

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

По очереди заполняем P196:Y196 и P197:Y197 через Ctrl + R. До эксплуатации — нули, в эксплуатации — по 1 рублю на тонну.

В P$193 закреплена строка года, в $J$196 и $J$197 — и столбец, и строка параметра. При заполнении вправо год меняется, параметр остаётся тем же.

Шаг 09 / Прочее и годовая сумма

Завершаем расчёт расходов по годам

Для прочих расходов используем J198:

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

Заполняем P198:Y198. В строке 199 выводим общий удельный расход:

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

Заполняем P199:Y199 и применяем «Л_ИТОГО». Чтобы получить годовые расходы, умножаем выпуск на расходы на тонну:

=P194*P199

Заполняем P200:Y200 через Ctrl + R и применяем «Л_Операция». В эксплуатации строка 199 показывает 3 рубля на тонну, а не годовые расходы.

Шаг 10 / Итоги и оформление

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

Суммируем годовые строки:

=СУММ(P194:Y194)

=СУММ(P200:Y200)

К O194 и O200 применяем «СУММА». O194 должен совпадать с O188: оба блока используют один выпуск. Для исходных параметров получится 5800 тонн и 17 400 рублей расходов.

Группируем отдельно строки 182: 190 и 193: 200 через Alt + Shift + →. Заголовки B181 и B192 останутся видимыми. В G181:G200 включаем перенос текста и автоподбор высоты строк; закрепление P26 сохраняем.

Шаг 10 / Итоги и оформление

Сверяем годовые расходы

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

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

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

1. Меняем J185 с 50% на 60%. Выпуск первого года, его выручка и расходы должны измениться; остальные ступени сохранятся. Возвращаем 50%.
2. Меняем J189: выручка должна измениться, выпуск — сохраниться. Возвращаем цену 10.
3. Меняем один расход в J196:J198. J199 и годовые расходы должны измениться, выручка — сохраниться. Возвращаем 1.
4. Убеждаемся, что G185:G187 содержат числа, а не текстовые подписи.

Сохраняем рабочую книгу. В следующем уроке добавляем инвестиции и структуру финансирования. Доходы и расходы пока остаются в базовых ценах.

Вводная / Условия проекта

Инвестиции и источники финансирования

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

Условный проект стоит 10 000 рублей с НДС. Строительство длится три года: 2015–2017. Каждый год финансируем одну треть стоимости. Ставка НДС в этом упражнении — 18%.

Сохраняем календарь из модуля 2: первые 36 месяцев рассчитываются помесячно. Даты проекта относятся к учебному примеру 2015–2024 годов.

Сначала создаём имя НДС

Открываем «Формулы → Диспетчер имен → Создать». Вводим имя НДС, выбираем область «Книга», в поле «Диапазон» задаём =18%. Подтверждаем создание.

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

Перед вводом формул проверяем: «Формулы → Параметры вычислений → Автоматически». Формулы из курса вставляем в строку формул либо после F2, чтобы переносы не распределились по соседним ячейкам.

Шаг 01 / Стоимость проекта

Заполняем стоимость инвестиций

В B202 вводим «Инвестиции» и применяем стиль «Заголовок1(к)». Заполняем подписи:

К подписям G применяем «Обычный», к заполненным единицам H — «ед_изм». Для G205:G206 устанавливаем выравнивание слева и отступ 2. J204 и J212 оставляем пустыми. Стоимость без НДС и итоговые суммы рассчитываем на следующем шаге.

Шаг 01 / Сумма без НДС

Рассчитываем стоимость и сохраняем значение

Выделяем столбец J. Открываем «Главная → Формат → Ширина столбца», вводим 16 и подтверждаем. Теперь итог 10 000,00 будет полностью виден.

Чтобы получить точную исходную стоимость без ручного ввода длинной дроби, временно вводим в J205:

=10000/(1+18%)

Копируем J205 через Ctrl + C. Не меняя выделенную ячейку, выбираем «Главная → Вставить → Специальная вставка → Значения» и подтверждаем. Нажимаем Esc. Теперь в J205 хранится число около 8474,57627118644 вместо формулы. Проверяем строку формул: начального знака равенства быть не должно.

Затем рассчитываем НДС и итог:

=J205*НДС

=СУММ(J204:J206)

К J205:J206 применяем «Допущение», к J207 — «Расчеты». Для денежных ячеек в этом уроке используем формат с двумя знаками. Точный код вводится через Ctrl + 1 → «Число → Все форматы»:

_(* # ##0,00_);_(* (# ##0,00);_(* "-"??_);_(@_)

Получатся 8474,58 без НДС, 1525,42 НДС и 10 000,00 с НДС. Отображение двух знаков не округляет само значение. Этот же денежный формат применяем далее к годовым суммам и итогам в O.

Шаг 02 / Доли по годам

Задаём три равные доли

В G209:G211 уже находятся числа 1, 2 и 3. Для этих ячеек нажимаем Ctrl + 1, выбираем «Число → Все форматы» и задаём код:

0\ "год строительства"

Устанавливаем выравнивание слева и отступ 2. Числа будут отображаться как «1 год строительства», «2 год строительства», «3 год строительства», сохраняя числовой тип.

Вводим доли финансирования:

=1/3

=1/3

=1/3

Применяем к J209:J211 стиль «Проценты_0%». На листе будет видно по 33%, но в каждой ячейке останется точная дробь 1/3. Не заменяем эти формулы на введённые вручную 33%: три таких числа составят только 99%.

Каждая доля относится к соответствующему году строительства. Распределяем доли по календарным годам отдельными формулами на следующих шагах.

Шаг 03 / Годовая база

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

Копируем годы 2015–2024 из P163:Y163 в P203:Y203 со стилем «Таблица». В первом столбце годового ряда вводим:

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

=P205*НДС

=СУММ(P205:P206)

Выделяем P205:Y207 и нажимаем Ctrl + R. Формулы из P заполнят строки вправо до Y. К P205:Y206 применяем «Обычный», к P207:Y207 — «Л_ИТОГО»; для денежных сумм используем учётный формат с двумя знаками.

В 2015–2017 годах строка 207 содержит полную стоимость проекта — по 10 000 рублей. С 2018 года значения равны нулю.

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

Шаг 04 / График строительства

Распределяем инвестиции по годам

Для трёх годов строительства сопоставляем календарный год из строки 203 с датой начала строительства:

=ЕСЛИ(P$203=ГОД(Дата_начала_строительства);$J209;0)

=ЕСЛИ(P$203=ГОД(Дата_начала_строительства)+1;$J210;0)

=ЕСЛИ(P$203=ГОД(Дата_начала_строительства)+2;$J211;0)

=СУММ(P209:P211)

=P207*P212

Выделяем P209:Y213 и нажимаем Ctrl + R. В P209:Y211 применяем «Обычный» и процентный формат без десятичных знаков. К P212:Y212 — «Проценты_0%», к P213:Y213 — «Л_ВСЕГО» и учётный формат с двумя знаками.

Знак $ закрепляет столбец параметров J и строку годов 203 при заполнении вправо. Строка 212 показывает долю, относящуюся к конкретному году; строка 213 умножает эту долю на полную стоимость.

Проверка / Инвестиционный график

Сверяем сумму инвестиций

В O213 считаем итог по годам и применяем стиль «СУММА»:

=СУММ(P213:Y213)

Итог O213 должен составить 10 000 рублей. Три отображаемые суммы 3333,33 не заменяют точные значения формул: итог считается по значениям ячеек, а не по округлённым числам на экране.

Если сумма отличается, проверяем, что J209:J211 содержат =1/3, даты строительства не изменены, а формулы заполнены до Y.

Проверка / Инвестиционный график

Сверяем график инвестиций

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

Шаг 05 / Структура финансирования

Вводим доли капитала и долга

В B215 вводим «Структура финансирования» со стилем «Заголовок1(к)». Заполняем параметры:

=J207*$J$217

=J207*$J$221

К подписям G применяем «Обычный». В G217:G218 и G221:G223 задаём выравнивание слева и отступ 2. К единицам H — «ед_изм», к J217, J221 и J223 — «Проценты_0%», к J218 и J222 — «Расчеты». Денежные значения показываем с двумя знаками.

Получатся 3000 рублей акционерного финансирования и 7000 рублей долга. Доли 30% и 70% вместе покрывают стоимость проекта. Ставка 15% годовых — условие учебного примера.

Шаг 06 / Сроки источников

Почему капитал и долг пересекаются

До начала эксплуатации проценты не начисляем и не капитализируем; графики рассчитываем в М5.

Параметр срока долга в J147 равен 7 годам. В календаре сохранены начало долга 31.12.2015 и конец 30.11.2022, полученный формулой исходной модели. Здесь даты повторно не меняем.

За первый год требуется 3333,33 рубля инвестиций, а акционерное финансирование составляет 3000 рублей. Недостающие 333,33 рубля покрывает долг. Поэтому в исходном примере последняя дата акционерного финансирования одновременно является первой датой заёмного финансирования.

Пересечение 31 декабря 2015 года намеренное: в одном периоде доступны два источника. В верхнем календаре для декабря 2015 года AA21 = 1 и AA22 = 1; в январе 2016 года AB21 = 0 и AB22 = 1.

Годовой баланс ниже проверяет достаточность источников за год. Он не показывает, в какие дни внутри года деньги поступили и были потрачены.

Шаг 07 / Таблица источников

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

В G225 вводим «Проверка на дефицитность источников». Подготавливаем строки:

К подписям G применяем «Обычный», выравнивание слева и отступ 0; к единицам H — «ед_изм». Копируем годы 2015–2024 из P163:Y163 в P226:Y226 со стилем «Таблица».

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

В формулах используются имена дат из модуля 2. Их область действия — вся книга. Не заменяем имена вручную введёнными годами: календарь источников должен оставаться связанным с параметрами проекта.

Шаг 08 / Источники по годам

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

Вводим пять формул в столбец P:

=-P213

=ЕСЛИ(И(P$226>=ГОД(Дата_начала_акционерного_финансирования);P$226<=
ГОД(Дата_конца_акционерного_финансирования));$J218;0)

=СУММ(P227:P228)

=МИН(ЕСЛИ(И(P$226>=ГОД(Дата_начала_заемного_финансирования);P$226<=
ГОД(Дата_конца_заемного_финансирования));$J222;0);-P229)

=СУММ(P229:P230)

Выделяем P227:Y231 и нажимаем Ctrl + R. Формула акционерного финансирования проверяет границы его периода. Формула долга выбирает меньшую из доступной суммы долга и непокрытой потребности.

К P227:Y228 и P230:Y230 применяем «Обычный», к P229:Y229 — «Л_Операция», к P231:Y231 — «Л_Баланс». Для всех денежных строк устанавливаем учётный формат с двумя знаками.

Шаг 09 / Итоги по источникам

Суммируем годовые потоки

В столбце O собираем итоги и применяем стиль «СУММА»:

=СУММ(P227:Y227)

=СУММ(P228:Y228)

=СУММ(P229:Y229)

=СУММ(P230:Y230)

=СУММ(P231:Y231)

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

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

Добавляем четыре контрольные ячейки

Вводим формулы:

=ЕСЛИ(-O227=J207;0;1)

=ЕСЛИ(O228=J218;0;1)

=ЕСЛИ(O230=J222;0;1)

=ЕСЛИ(O231<>0;1;0)

Применяем к четырём ячейкам стиль «ТРЕВОГА». В исходном оформлении числовой формат "ОШИБКА!";"ОШИБКА!";"Ok" показывает ноль как Ok, ненулевой результат — как ошибку. При необходимости задаём этот код через Ctrl + 1 → «Все форматы».

При исходных параметрах каждая проверка равна 0. K227 сопоставляет инвестиции с их общей стоимостью, K228 — акционерное финансирование с J218, K230 — долг с J222, K231 проверяет нулевой итоговый баланс.

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

Проверка / Источники и потребность

Завершаем блок финансирования

Включаем «Главная → Перенос текста» в G202:G231, затем «Формат → Автоподбор высоты строки». Группируем отдельно строки 203:213 и 216:231 через Alt + Shift + →. Заголовки в B202 и B215 останутся видимыми; закрепление P26 сохраняем.

Сохраняем рабочую книгу. Для сверки этого этапа предусмотрен файл М03_После_урока_03_v5.xlsx. Продолжаем работу в рабочей книге; в следующем уроке собираем ставки налогов и страховых взносов в отдельном блоке.

Шаг 01 / Ставки модели

Собираем ставки на листе

Продолжаем работу в книге после урока об инвестициях. Имя НДС уже содержит константу 18%. Теперь размещаем ставки в ячейках, чтобы видеть и менять их в одном блоке.

В B233 вводим «Налоги» со стилем «Заголовок1(к)». Заполняем таблицу:

К подписям применяем «Обычный», выравнивание слева и отступ 0; к J234:J237 — «Проценты_0%». Для J237 дополнительно устанавливаем процентный формат с одним десятичным знаком, чтобы видеть 2,2%, а не округлённые 2%.

В строке 236 задаём страховые взносы. Сохраняем подпись «Ставка Социальных налогов» и техническое имя ЕСН для формул.

Шаг 02 / Именованные ставки

Связываем имена с ячейками

Открываем Формулы → Диспетчер имен. Выбираем существующее имя НДС и нажимаем «Изменить». В поле «Диапазон» заменяем =18% на ='Исходные данные'!$J$234. Подтверждаем изменение. Второе имя НДС создавать не нужно.

Затем через «Создать» добавляем три имени с областью «Книга»:

Строка НДС в таблице показывает уже изменённое имя; остальные три создаются впервые. Используем абсолютные ссылки со знаками $ и имя рабочего листа «Исходные данные».

В формулах писать НДС удобнее, чем каждый раз вводить адрес ставки. Само имя теперь ссылается на ячейку; параметр виден в таблице. Формулы J206 и P206:Y206 уже используют НДС. После изменения ссылки имени повторно вводим в J206 =J205*НДС, а в P206 — =P205*НДС. Затем выделяем P206:Y206 и нажимаем Ctrl + R. Так сохраняем единый вариант формул после переноса ставки в ячейку.

Изменение источника имени сохраняет общую логику расчёта. Если ставка в J234 меняется, формулы с именем НДС получают новое значение.

Проверка / Налоговый блок

Сверяем имена и значения

В «Диспетчере имен» проверяем четыре строки: имена без ошибок, область «Книга», ссылки на J234:J237. Значение НДС должно остаться 18%.

После изменения ссылки имени J206 по-прежнему показывает 1525,42, J207 — 10 000,00, O213 — 10 000,00. Проверки K227, K228, K230 и K231 остаются равными нулю. Ставку упражнения сейчас не меняем.

В G233:G237 включаем перенос текста и автоподбор высоты строк. Группируем 234:237 через Alt + Shift + →. Сохраняем рабочую книгу. Файл для сверки — М03_После_урока_04_v5.xlsx.

Вводная / Сроки расчётов

Задаём сроки расчётов

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

Пояснение / Кассовый разрыв

Учитываем задержку оплаты

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

Дебиторская задолженность отражает ожидаемые поступления от покупателей, кредиторская — отсроченные платежи поставщикам.

Шаг 01 / Сроки оборота

Вводим 60 и 30 дней

Для учебной модели используем укрупнённые параметры без детализации задолженности по поставщикам, персоналу и другим контрагентам. Приняты 60 дней для дебиторской задолженности и 30 дней для кредиторской.

В B239 вводим «Оборотный капитал» со стилем «Заголовок1(к)»:

К подписям G240:G241 применяем «Обычный», выравнивание слева и отступ 0; к J240:J241 — «Допущение». Нажимаем Ctrl + 1 → «Все форматы» и задаём код 0\ "дней". В ячейках останутся числа 60 и 30, а на листе появится подпись единицы измерения.

Проверка / Исходные данные

Завершаем исходные данные

В G239:G241 включаем «Главная → Перенос текста», затем «Формат → Автоподбор высоты строки». Группируем строки 240:241 через Alt + Shift + →; заголовок B239 остаётся видимым.

Перед переходом к расчётам проверяем:

• J240 и J241 содержат числа 60 и 30, отображаемые в днях.
• Все годовые таблицы модуля доходят до столбца Y и соответствуют 2015–2024 годам.
• НДС ссылается на J234; НП, ЕСН и НИ — на J235:J237.
• Инвестиции O213 равны 10 000; капитал J218 — 3000; долг J222 — 7000.
• K227, K228, K230 и K231 равны нулю. В формулах нет сообщений об ошибках.
• Группы блоков сворачиваются отдельно, закрепление P26 сохранено.

Сохраняем рабочую книгу. Итог этого модуля можно сравнить с файлом М03_Контроль_v5.xlsx.

Лист «Исходные данные» заполнен. В следующем модуле переходим к расчётному листу «РИД» и используем подготовленные параметры.