Исходные данные и рабочий каркас

Подготовка / Макроэкономические допущения

Сбор исходных данных

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

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

В курсе используем один показатель — инфляцию. Для других показателей повторяем те же действия.

Подготовка / Выручка и расходы

Оцениваем выручку, мощность и расходы

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

Для предварительной оценки можно принять постепенный выход на 80% заявленной мощности. Для своего проекта уточняем загрузку по техническим и рыночным данным.

Составляем перечень расходов: фонд оплаты труда, налоги и взносы, материалы, амортизацию и прочие затраты. Разделяем постоянные и переменные, производственные и административные расходы. Оставляем 5–10 свободных строк для детализации.

Сопоставляем выручку и расходы до дальнейшего построения модели.

Подготовка / Капитал и проверка комплекта

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

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

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

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

Шаг 01 / Рабочая книга

Внешний вид листов
и применение стилей

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

1. Открываем файл «М01_Старт.xlsx» из приложения к модулю.
2. Через «Файл → Сохранить как» сохраняем свою рабочую копию под удобным именем.
3. Продолжаем работу на листе «Исходные данные». Он уже назван: переименовывать его не требуется.

Что уже подготовлено

Верхняя часть A1:Y11 оставлена пустой. Ниже, в B12:O68, находится палитра — образцы оформления. В книге также сохранены именованные стили, которые мы будем выбирать в Excel.

Работаем в одной книге. Палитру оставляем на месте. Все действия этого урока выполняем в своей копии; отдельный файл с результатом понадобится только для сверки.

Шаг 02 / Ширины и назначение столбцов

Разделяем лист
на рабочие зоны

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

1. Выделяем нужные столбцы щелчком по их буквенным заголовкам.
2. Щёлкаем по выделенному диапазону правой кнопкой мыши и выбираем «Ширина столбца».
3. Вводим значение из таблицы. Повторяем действие для остальных диапазонов.

Значения относятся к полю Excel «Ширина столбца»; это не сантиметры и не пиксели. Столбцы I:L можно выделить вместе, поскольку их ширина одинакова.

Один принцип для модели. Столбец O отведён под итоги, периоды начинаются с P. Сейчас создаём десять периодов; на следующем уроке расширим горизонт до 120.

Шаг 03 / Учебные подписи

Заполняем верхнюю
часть листа

Вводим подписи точно по таблице. Заголовки 1–6 пока демонстрируют уровни оформления; их рабочие названия появятся позже.

Продолжаем последовательность периодов

1. В Q11 вводим «Период 2».
2. Выделяем P11:Q11.
3. Перетаскиваем маркер заполнения из правого нижнего угла выделения вправо до Y11.
4. Проверяем: R11 — «Период 3», а Y11 — «Период 10».

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

Резерв в M9 и N10 расположен в узких столбцах. Если подпись видна не полностью, выделяем ячейку и проверяем её текст в строке формул.

Шаг 04 / Применение готовых стилей

Применяем стили
к учебным ячейкам

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

1. Выделяем ячейку или диапазон из таблицы. Для точного выбора можно ввести адрес в поле имени слева от строки формул и нажать Enter.
2. На вкладке «Главная» раскрываем галерею «Стили ячеек».
3. Выбираем стиль с точным именем из правого столбца.

В имени «Заголовок1(к)» нет пробелов, а в тексте ячейки B2 — «Заголовок 2». Не путаем подпись ячейки и имя выбранного стиля. Стиль первой строки применяем до EE заранее, под будущий горизонт расчёта.

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

Проверка / Результат урока

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

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

При необходимости открываем «М01_После_урока_02.xlsx» для сверки. Этот файл показывает результат данного урока. «М01_Контроль.xlsx» содержит более поздний итог всего модуля.

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

Исходные данные / Рабочая область

Создаём
временные ряды

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

Продолжаем работу в рабочей книге после урока 1.2, на листе «Исходные данные». Если начинаем с этого урока, открываем «М01_После_урока_02.xlsx» и сохраняем отдельную рабочую копию.

Резервируем горизонт расчёта

В учебном примере проект длится 10 лет: 3 года строительства и 7 лет эксплуатации. Чтобы при необходимости перейти к помесячной детализации, оставляем место для каждого месяца.

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

1. На подготовленном листе вводим в P11 подпись «Период 1».
2. Протягиваем последовательность вправо до EE11. Последняя подпись — «Период 120». Выделяем столбцы P:EE и задаём ширину 13, чтобы все периоды имели одинаковую ширину.
3. Оставляем столбцы A:O для названий, параметров и служебных полей.

Граница диапазона. Первый рабочий столбец — P, последний — EE. Столбец EF находится уже за пределами 120 периодов.

Шаг 02 / Видимая граница

Убираем лишние столбцы
из поля зрения

Рабочая зона заканчивается столбцом EE. Столбцы справа можно сгруппировать и свернуть либо просто скрыть.

1. Щёлкаем заголовок EF, затем нажимаем Ctrl + Shift + →, чтобы выделить столбцы до правого края листа.
2. Для группировки нажимаем Alt + Shift + →. Сворачиваем созданную группу кнопкой «−» над столбцами.
3. Альтернатива: щёлкаем выделение правой кнопкой мыши и выбираем «Скрыть».

Что меняется. Группировка и скрытие управляют видимостью. Если формула ссылается на скрытую ячейку, эта ячейка по-прежнему участвует в вычислениях.

Столбцы скрываем. Команду «Очистить всё» не применяем.

Шаг 03 / Переход к конечной сетке

Освобождаем место
для верхнего каркаса

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

1. В рабочей копии выделяем целые строки 12:24. Щёлкаем их номера правой кнопкой мыши и выбираем «Вставить». Появятся 13 новых строк; блок «Форматы ячеек» переместится со строки 12 на строку 25.
2. Вырезаем диапазон G8:L8 и вставляем его начиная с G4. В G4 заменяем подпись на «Наименование статей».
3. Вырезаем диапазон O11:EE11 и вставляем его начиная с O24. «Итоги» окажутся в O24, а «Период 1» — в P24.
4. Очищаем только демонстрационные подписи в C3, D4, E5, F6, M9 и N10. Остальные образцы оформления сохраняются ниже строки 24.

Проверяем перемещения

Теперь можно вводить подписи. В следующих двух шагах заполняем свободные ячейки G5:G24 по таблицам. Пустые строки 7, 15, 19 и 23 будут разделять блоки.

Шаг 04 / Календарный каркас

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

Размещаем подписи в столбце G сразу на окончательных строках. Ячейки справа пока остаются пустыми: даты и порядковые номера появятся после ввода формул.

Календарный год и порядковый номер года проекта — разные признаки. Первый относится к календарю, второй показывает положение периода внутри проекта.

Шаг 05 / Фазы и графики

Добавляем фазы проекта
и финансирование

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

Выбираем достаточную детализацию

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

На этом этапе вводим только подписи. Распределение фаз и финансирования по периодам будет настроено далее.

Шаг 06 / Служебные поля

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

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

A1 · Формула названия листа

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

Очищаем прежнее содержимое A1, нажимаем F2 и вставляем формулу целиком. Перенос добавлен для печати: формула должна остаться в одной ячейке. Используется русская запись функций и разделитель «;».

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

К B2 и B3 применяем стиль «Заголовок1(к)» через «Главная → Стили ячеек». Остальные подписи столбца G оставляем со стилем «Обычный».

Пока это только подписи. Они ещё не выбирают сценарий и не проверяют формулы. Соответствующие связи будут добавлены в последующих разделах курса.

Шаг 07 / Группы строк

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

Группировка позволяет менять объём видимой информации. Создаём одну внешнюю группу и три вложенные группы по таблице ниже.

1. Выделяем строки 7:24 по их номерам слева и нажимаем Alt + Shift + →.
2. Тем же способом группируем каждый из трёх вложенных диапазонов.
3. Проверяем уровни структуры: 1 скрывает весь блок, 2 оставляет подзаголовки, 3 раскрывает подробности.

На иллюстрации показаны ячейки, экспортированные из Excel. Кнопки уровней «1, 2, 3» находятся в интерфейсе Excel слева от номеров строк и в печатный фрагмент не входят.

Шаг 08 / Навигация по листу

Закрепляем названия
и верхнюю часть листа

При прокрутке вправо должны оставаться видимыми названия строк, а при прокрутке вниз — временной ряд. В готовом каркасе закрепляем столбцы A:O и строки 1:24.

1. Если закрепление уже установлено, открываем «Вид → Закрепить области → Снять закрепление областей».
2. Нажимаем Ctrl + Home, чтобы вернуться к A1. Если строки 1:25 и столбцы A:P не видны целиком, временно уменьшаем масштаб. Затем выбираем ячейку P25.
3. В меню «Вид → Закрепить области» выбираем команду «Закрепить области». После этого можно вернуть удобный масштаб.

Почему выбираем P25

Excel закрепляет строки выше выбранной ячейки и столбцы слева от неё. Сама P25 — первая ячейка незакреплённой рабочей области.

Схема поясняет принцип закрепления; это не снимок интерфейса Excel.

Почему после переноса нужна P25

До переноса подписи периодов находились в строке 11: ей соответствовало бы закрепление P12. Теперь верхний блок занимает строки 1–24; ему соответствует P25. Закрепление устанавливаем после завершения переноса.

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

Проверка / Итог раздела

Каркас готов
к заполнению дат

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

Следующий раздел — «Заполняем временной ряд». В нём зададим параметры времени и даты проекта, затем построим календарь. Эти расчёты относятся к модулю 2. Сохраняем рабочую книгу и сверяем её с файлом «М01_Контроль.xlsx».