5 основ Excel (обучение): как написать формулу, как посчитать сумму, сложение с условием, счет строк и пр.

Подумать только: складывать в автоматическом режиме значения из одних формул в другие, искать нужные строки в тексте, создавать собственные условия и т.д. — в общем-то, по сути мини-язык программирования для решения “узких” задач (признаться честно, я сам долгое время Excel не рассматривал за программу, и почти его не использовал) .

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

То есть эта статья будет что-то мини гайда по обучению самому нужному для работы (точнее, чтобы начать пользоваться Excel и почувствовать всю мощь этого продукта!) .

Возможно, что прочти подобную статью лет 17-20 назад, я бы сам намного быстрее начал пользоваться Excel (и сэкономил бы кучу своего времени для решения “простых” задач. 👌

Общее представление

При разработке шаблонов для экономического планирования учитываются основные показатели деятельности предприятия, к числу которых относятся:

  • доходы и расходы;
  • прибыль;
  • денежные потоки;
  • активы и обязательства.

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

Построение финмодели в Excel от Microsoft или в Google docs предусматривает внесение части информации вручную, тогда как зависимые данные определяются с помощью базовых формул. Благодаря этому любая корректировка позволяет моментально пересчитать итоговые значения, оценить риски и проанализировать перспективы получения прибыли.

За счет проделанной работы формируются три типа отчетности:

  • балансовые показатели предприятия;
  • аналитика результатов (ОФР);
  • отчет по движению денежных средств (ОДДС).

Грамотное применение полученных результатов упрощает управленческий процесс — так же, как и использование программных решений от компании «Клеверенс», исключающих вероятность технических ошибок при учете активов организации.

показатели прибыльности

2. Выполнение арифметических операций внутри функции СУММПРОИЗВ

Среди массивов могут встречаться такие, которые сначала нужно просуммировать (или вычесть, или разделить) прежде, чем перемножать.

Например, такая ситуация: есть количество заказов, есть цена товара (отличная для каждого города), есть стоимость доставки. Задача: прибавить к цене стоимость доставки >> полученное помножить на количество заказов.

В этом случае заменяем точку с запятой на необходимый нам арифметический оператор (*,/,+,-). Если требуется настроить порядок вычислений при использовании арифметики внутри функции следует использовать круглые скобки, как в обычной арифметике.

Без использования функции пришлось бы формулу прописывать вот так:

=B2*(C2+D2)+B3*(C3+D3)+B4*(C4+D4)+B5*(C5+D5)+B6*(C6+D6)+B7*(C7+D7)

Согласитесь, что это прошлый век:)

Формируем кадровую политику компании (лист «Персонал»)

На этом листе будут сформированы таблицы:

  • «Кадровая политика»;
  • «Штатное расписание»;
  • «ФОТ, налоги и отчисления, тыс. руб.».

Для наглядности задаем значения следующим показателям: инфляция по заработной плате, НДФЛ, страховые взносы (СВ).

Для удобства расчета присваиваем значениям имена:

инфляция по заработной плате — Sindex;

Теперь можем рассчитать фонд оплаты труда по категориям сотрудников, их налоги и отчисления. Для этого задаем формулу (на примере управленческого персонала, отчетный период — 2015 г.):

где $C4 — среднемесячная заработная плата управленческого персонала (40 000 руб.);

SIndex — инфляция по заработной плате (1 %);

C$15 — порядковый номер периода (2015 году присваиваем значение 0);

$D4 — занятость (12 месяцев);

C9 — численность управленческого персонала (8 чел.).

Получаем таблицу, в которой представлены значения фонда оплаты труда, налоги и отчисления по годам (табл. 1).

Таблица 1. Фонд оплаты труда, налоги и отчисления по категориям персонала

Страховые взносы + НДФЛ

ФОТ с отчислениями

Как посчитать накопительную сумму в Excel?

На рисунке ниже Вы можете видеть таблицу Excel, в которой представлен объем продаж по дням за январь месяц 2014 года. В ячейке B20 отображается общая сумма продаж за весь месяц, вычисленная с помощью функции СУММ. Требуется посчитать объем продаж на каждую дату января относительно начала месяца, т.е. накопительную сумму.

Накопительная сумма в Excel

При подсчете накопительной суммы в Excel, необходимо, чтобы суммирование всегда начиналось с третьей строки (ячейки B3) и заканчивалось рассматриваемой строкой. Например, ячейка C3 должна содержать следующую формулу: =СУММ(B3), а если говорить точнее, то =СУММ(B3:B3), т.е. суммирование диапазона, состоящего из одной ячейки.

Накопительная сумма в Excel

Ячейка C4 такую формулу: =СУММ(B3:B4)

Накопительная сумма в Excel

Ячейка C19 такую формулу: =СУММ(B3:B19)

Накопительная сумма в Excel

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

Накопительная сумма в Excel

Если скопировать данную формулу во все ячейки таблицы, получим следующий результат:

Накопительная сумма в Excel

Поскольку значения в ячейках B20 и C19 совпадают, то можно быть уверенным, что накопительная сумма в Excel подсчитана верно. Как видите, все просто!

Расчет доходности к погашению для облигаций

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

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

Расчет доходности к погашению облигации в EXCEL

В примере показан прогноз доходности к погашению для облигации с купоном 40 руб (два раза в год) и текущей стоимостью 98% (980 р) и погашением в 2024 году. Предполагается, что облигация держится до погашения. В данном случае имеет релевантность только последнее значение IRR (в момент погашения), так как изменение цены облигации прогнозировать очень сложно. IRR за 6 периодов тоже большого смысла для облигаций не имеет.

Функция ПРОДУКТ в Excel (формулы, примеры) | Как пользоваться?

Функция Product excel – это встроенная математическая функция, которая используется для вычисления произведения или умножения данного числа, предоставленного этой функции в качестве аргументов, например, если мы предоставим аргументы этой формулы как 2 и 3 как = PRODUCT (2,3) тогда отображается результат 6, эта функция умножает все аргументы.

Функция произведения в excel принимает аргументы (вводятся как числа) и выдает произведение (умножение) в качестве вывода. Если ячейки A2 и A3 содержат числа, то мы можем умножить эти числа с помощью ПРОДУКТА в Excel.

Формула ПРОДУКТА в Excel

= ПРОДУКТ (число1, [число2], [число3], [число4],….)

Объяснение

Формула ПРОДУКТА в Excel имеет как минимум один аргумент, а все остальные аргументы являются необязательными. Всякий раз, когда мы передаем одно входное число, оно возвращает значение как 1 * число, то есть само число. ПРОДУКТ в Excel относится к категории математических / тригонометрических функций. Эта формула ПРОДУКТА в Excel может принимать до 255 аргументов в более поздней версии после Excel 2003. В Excel версии 2003 аргумент был ограничен до 30 аргументов.

Формула ПРОДУКТА в Excel не только принимает в качестве аргумента введенный номер один за другим, но также может принимать диапазон и возвращать продукт. Итак, если у нас есть диапазон значений с числами и нам нужен их продукт, мы можем сделать это либо умножением каждого из них, либо напрямую с помощью формулы ПРОДУКТ в Excel, минуя диапазон значений.

На приведенном выше рисунке мы хотим умножить все значения вместе, указанные в диапазоне A1: A10, если мы сделаем это с помощью математического оператора multiply (*), потребуется много времени по сравнению с достижением того же самого с помощью функции PRODUCT в excel. так как нам нужно будет выбрать каждое значение и умножить, тогда как, используя продукт в excel, мы можем передать значения напрямую как диапазон, и он даст результат.

= ПРОДУКТ (A1: A10)

Следовательно, формула ПРОДУКТ в Excel = ПРОДУКТ (A1: A10) эквивалентна формуле = A1 * A2 * A3 * A4 * A5 * A6 * A7 * A8 * A9 * A10

Однако единственное отличие состоит в том, что когда мы используем функцию ПРОДУКТ в Excel и если мы оставили ячейку пустой, ПРОДУКТ в Excel принимает пустую ячейку со значением 1, но с помощью оператора умножения, если мы оставили ячейку пустой, Excel примет значение равно 0, и результат будет 0.

Когда мы удалили значение ячейки A4, excel считает его 0 и возвращает результат 0, как показано выше. Но когда мы использовали функцию ПРОДУКТ в excel, он взял диапазон ввода A1: A10, кажется, что ПРОДУКТ в excel игнорирует ячейку A4, которая была пустой, однако он не игнорирует значение пустой ячейки, а берет пустое ячейка со значением 1. Он принимает диапазон A1: A10, рассматривает A4 со значением 1 и умножает значения ячеек вместе. Он также игнорирует текстовые значения и логические значения. В продукте Excel даты и числовые значения рассматриваются как числа. Каждый аргумент может быть предоставлен как отдельное значение или ссылка на ячейку или как массив значений или ячеек.

Для небольших математических вычислений мы можем использовать оператор умножения, но в случае, если нам нужно иметь дело с большим набором данных, в котором задействовано умножение нескольких значений, эта функция ПРОДУКТ служит большой цели.

Итак, функция ПРОИЗВОДИТ в Excel полезна, когда нам нужно умножить множество чисел, заданных в диапазоне.

Примеры

Давайте посмотрим ниже на некоторые примеры функции ПРОДУКТ в Excel. Эти примеры функций ПРОДУКТ в Excel помогут вам изучить использование функции ПРОДУКТ в Excel.

Вы можете скачать этот шаблон Excel для функции PRODUCT здесь – Шаблон для функции PRODUCT Excel

Пример # 1

Предположим, у нас есть набор значений в столбцах A и B, который содержит числовые значения с некоторыми пустыми ячейками, и мы хотим умножить каждое значение столбца A на столбец B таким образом, чтобы если какая-либо из ячеек содержала пустое значение, мы получить пустое значение иначе возвращает произведение двух значений.

Например, ячейка B2 пуста, поэтому результатом должно быть пустое значение в ячейке C2. Таким образом, мы будем использовать условие ЕСЛИ вместе с функцией ИЛИ. Если одно из значений ячейки ничего не возвращает, ничто другое не возвращает произведение чисел.

Итак, формула ПРОДУКТА в Excel, которую мы будем использовать,

= ЕСЛИ (ИЛИ (A2 = ””, B2 = ””), ””, ПРОИЗВОД (A2, B2))

Применяя формулу ПРОДУКТА в Excel к каждой ячейке, которую мы имеем

Выход:

Пример # 2 – Вложение функции продукта

Когда ПРОДУКТ в Excel используется внутри другой функции в качестве аргумента, это называется вложением функции ПРОДУКТ в Excel. Мы можем использовать другие функции и передавать их в качестве аргумента. Например, предположим, что у нас есть четыре набора данных в столбцах A, B, C и D. Нам нужно произведение значения суммы из первого набора данных и второго набора данных на сумму значений из третьего и четвертого наборов данных.

Итак, мы будем использовать функцию SUM и передадим ее в качестве аргумента функции PRODUCT в excel. Нам нужно произведение суммы значений набора данных A и набора данных B, которая равна 3 + 3, умноженная на сумму значений набора данных C и C, которая равна (5 + 2), поэтому результат будет (3 + 3 ) * (5 + 2).

= ПРОДУКТ (СУММ (A2: B2); СУММ (C2: D2))

В приведенном выше примере функция суммы передается в качестве аргумента функции ПРОДУКТ в Excel, это называется вложением. Мы можем даже другие функции.

Пример – # 3

Например, предположим, что у нас есть шесть отделов с разным количеством занятых на работе. У нас есть две таблицы с количеством человек в каждом отделе и часами работы каждого человека в каждом отделе. Мы хотим подсчитать общее количество часов работы каждого подразделения.

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

Итак, формула с вложенной ВПР будет такой:

= ПРОДУКТ (ВПР (G2, $ A $ 2: $ B $ 7,2,0), ВПР (G2, $ D $ 2: $ E $ 7,2,0))

Таким образом мы можем выполнять вложение функций в зависимости от требований и проблем.

Финансово-экономический анализ предприятия

/>Конечно же, тема финансово-экономического анализа предприятий, особенно в части касающейся оценки кредитоспособности и рисков неплатежеспособности в сфере банковского кредитования, хорошо изучена, систематизирована, унифицирована и доступно представлена как в профессиональной, так и популярной литературе, а также Интернете. Но мы все же для полноты изложения нашей рубрики «управление финансами» коснемся, по крайней мере, основ этой темы.

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

СКАЧАТЬ
/>ФИНМОДЕЛЬ С ФИНАНСОВО-ЭКОНОМИЧЕСКИМ АНАЛИЗОМ ИНВЕСТПРОЕКТА />

Также все нижеизложенные в настоящем разделе формулы расчета коэффициентов и показателей финансово-хозяйственной деятельности предприятия аккумулированы нами в следующем Excel-файле, в котором читатель может самостоятельно заполнить стандартные бухгалтерские финансовые формы отчетности (баланс, финансовый результат, движение ДС) своего предприятия и с легкостью получить расчеты классических финансовых коэффициентов, тем самым самостоятельно провести, так называемый, экспресс-анализ финансово-экономического состояния предприятия:

СКАЧАТЬ
/>Финансово-экономический анализ с формулами />

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

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

Для анализа и оценки ликвидности активы баланса компании обычно ранжируют по степени ликвидности, а пассивы в части обязательств – по срочности и «важности» погашения.

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

Наиболее срочными обязательствами являются просроченные обязательства (долги неоплаченные в срок), причем первостепенно перед коммерческими банками, бюджетом и персоналом, потом перед поставщиками, поскольку с последними еще как-то можно договариваться без существенного ущерба для репутации. Далее, по мере убывания срочности и «важности» – кредиторская задолженность, краткосрочные кредиты и займы, резервы предстоящих расходов, прочие краткосрочные обязательства и, наконец, долгосрочные обязательства.

Представим в виде следующей таблицы ранжирование активов и пассивов баланса:

Ранжирование активов и пассивов по степени ликвидности

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

Заметим, что одновременное выполнение первых трех неравенств, автоматически влечет за собой выполнение четвертого.

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

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

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

А1 + А2 + А3 >= П1 + П2 + П3.

Также для оценки уровня ликвидности используются следующие коэффициенты:

Коэффициенты ликвидности

Указанные нормативы «пришли» к нам с Запада, с, так сказать, развитых рынков. Получены они (нормативы) были следующим путем. На протяжении значительного периода времени (несколько десятков лет) приведенные в таблице коэффициенты рассчитывались для большого количества предприятий США и Западной Европы, причем в первую очередь для публичных компаний, то есть тех, чьи акции котировались на известных мировых фондовых биржах. Далее для стабильно успешных компаний были рассчитаны средние уровни различных финансово-экономических коэффициентов и эти статистически полученные уровни и были взяты в качестве нормативов или рекомендуемых значений для всех компаний, которые обращались за заемными средствами в коммерческие банки и инвестиционные компании.

Но здесь необходимо отметить то, что, например, норматив «>=2» для коэффициента текущей ликвидности (L1) был получен на основе изучения финансовой статистики крупных промышленных американских компаний, и применять его для любого предприятия не имеет смысла. Допустим нам необходимо проанализировать ликвидность ООО, занимающегося исключительно торговой деятельностью, с уставным капиталом в 10тыс.руб., краткосрочными кредитами (на срок до одного года) на пополнение оборотного капитала в среднем в размере 1млн.руб., ежемесячной выручкой 500тыс.руб. и чистой маржой (процент отношения чистой прибыли к выручке) в 5%. Тогда получаем ежегодный прирост собственного капитала в размере 300тыс.руб., и срок выхода на норму в «2 раза» по коэффициенту текущей ликвидности, равный трем годам и четырем месяцам, при условии, что вложения во внеоборотные активы в течение всего этого срока будут отсутствовать.

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

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

СОС = стр.1300 – стр.1100.

Если разность СОС – стр.1200 = 0 или близко к нулю, что встречается крайне редко, то это означает полную финансовую независимость предприятия, или то, что все активы финансируются за счет собственного капитала.

Уровень достаточности/недостаточности финансирования запасов (стр.1210 баланса) за счет собственных оборотных средств определяет уровень финансовой независимости/зависимости компании. Представим градацию уровней финансовой независимости в виде следующей таблицы:

Уровни финансовой независимости

Отметим, что в случае высокого уровня финансовой зависимости компании (I3), если на покрытие запасов помимо СОС, краткосрочных кредитов и краткосрочной кредиторской задолженности перед поставщиками необходима еще и кредиторская задолженность перед бюджетом и персоналом, то положение предприятия с точки зрения финансовой независимости считается критическим.

Приведем таблицу основных коэффициентов финансовой устойчивости/независимости предприятия:

Коэффициенты финансовой устойчивости

Отметим, что норматив для коэффициента финансового рычага, который еще называют коэффициентом финансового левериджа, прямо вытекает из норматива для коэффициента автономии.

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

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

Ключевым показателем эффективности, которым владеет каждый коммерсант и который всегда контролируется на предприятии в рамках текущей операционной деятельности, а также с которым напрямую связаны такие составляющие управленческого процесса, как управление себестоимостью и ценообразованием, является рентабельность проданной продукции (R) равная процентному отношению валовой прибыли к себестоимости продаж за период или выражая через коды строк формы 2 (отчет о прибылях и убытках):

R = стр.2100 / стр.2120.

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

но также под этим понятием иногда скрывается и отношение маржинальной прибыли к выручке

(стр.2100 – стр.2210) / стр.2110,

которое часто используют финансисты в контексте расчета точки безубыточности, и отношение операционной прибыли или прибыли от продаж к выручке

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

В финансовой науке показатель рентабельность продаж чаще определяется, как отношение валовой прибыли к выручке (вторая из приведенных выше формул):

ROS = (Return On Sales) = стр.2100 / стр.2110.

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

ROA = (Return On Assets) =

= стр.2400 / ((стр.1600 на начало периода + стр.1600 на конец периода)/2).

Для акционеров и инвесторов важными показателями эффективности являются рентабельность собственного капитала (чистая прибыль к собственному капиталу) и рентабельность инвестиций или инвестиционного капитала (EBITDA к сумме собственного капитала и долгосрочных обязательств) соответственно:

ROE = (Return On Equity) =

= стр.2400 / ((стр.1300 на начало периода + стр.1300 на конец периода)/2),

ROIC = (Return On Invested Capital) =

= стр.2200 / (((стр.1300 + стр.1400) на нач. пер. + (стр.1300 + стр.1400) на кон. пер.)/2).

Эффективность текущей деятельности операционного блока предприятия оценивается маржинальной рентабельностью рабочего (оборотного) капитала равной отношению маржинальной прибыли к объему оборотного капитала (оборотных активов):

ROWC = (Return On Working Capital) =

= (стр.2100 – стр.2210) / ((стр.1200 на начало периода + стр.1200 на конец периода)/2).

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

Как мы уже отмечали ранее, помимо уровня рентабельности продаж крайне важным показателем является время, за которое предприятие реализует продукцию с такой рентабельностью или в более широком смысле – период оборачиваемости оборотных активов. Дело в том, что оборотные активы «перетекают» в рамках замкнутого операционного цикла из одного состояния в другое: денежные средства в сырье, материалы и т.п. активы, которые формируют себестоимость, и преобразуются в готовую продукцию, готовая продукция при продаже преобразуется в дебиторскую задолженность, дебиторская задолженность при ее погашении покупателями преобразуется в денежные средства и т.д. В данном контексте выделяют три основных цикла коммерческого предприятия:

финансовый цикл – это период между оттоком денежных средств и дальнейшим их притоком или возвратом в полном объеме плюс/минус заработанные/потерянные денежные средства в результате операционной деятельности предприятия;

производственный цикл – это период между поступлением в собственность предприятия сырья и материалов или моментом формирования себестоимости продукции и реализацией продукции;

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

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

Оборачиваемость товарных запасов за Период рассчитывается, как отношение выручки за Период к среднему уровню товарных запасов за Период:

ОбТЗ = стр.2110 / ((стр.1210 на нач.пер. + стр.1210 на кон.пер.)/2).

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

Оборачиваемость дебиторской задолженности за Период рассчитывается, как отношение выручки за Период к среднему уровню дебиторской задолженности за Период:

ОбДЗ = стр.2110 / ((стр.1230 на нач.пер. + стр.1230 на кон.пер.)/2).

Оборачиваемость кредиторской задолженности за Период рассчитывается, как отношение закупок за Период к среднему уровню кредиторской задолженности за Период, но в бухгалтерской отчетности величины закупок в явном виде нет, поэтому можно рассматривать в качестве объема закупок величину:

себестоимость продаж за Период + ТЗ на конец Периода – ТЗ на начало Периода,

тогда формула для оборачиваемости кредиторской задолженности за период будет иметь следующий вид:

ОбКЗ = (стр.2120 + стр.1210 на кон.пер. – стр.1210 на нач.пер.) /

/ ((стр.1520 на нач.пер. + стр.1520 на кон.пер.)/2).

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

P(ОбТЗ) = кол-во дней в периоде / ОбТЗ,

P(ОбДЗ) = кол-во дней в периоде / ОбДЗ,

P(ОбКЗ) = кол-во дней в периоде / ОбКЗ.

В терминах периодов оборачиваемости длина финансового цикла (ФЦ) в днях имеет следующее представление:

ФЦ = P(ОбТЗ) + P(ОбДЗ) – P(ОбКЗ).

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

Для примера рассмотрим показатель периода оборачиваемости товарных запасов. Пусть компания 15 января 2014 года приобрела для перепродажи некий товар (далее – Товар) за 100 руб. и смогла его продать только 15 ноября 2014 года за 120руб., причем за 2014 год больше она (компания) ничего не продала, т.е. выручка за 2014 год равна 120руб. Если компания производственная, то можно считать, что она произвела Товар в январе и в среднем себестоимость в 100 руб. сформировалась 15 января.

С точки зрения физического смысла понятия период оборачиваемости товарных запасов период оборачиваемости нашего Товара – это количество дней между продажей (15.11.14) и покупкой (15.01.14) Товара, равное 304 дням.

Теперь посмотрим, какой результат мы получим, применяя формулу расчета периода оборачиваемости товарных запасов для трех периодов: за 2014г., за 4-ый квартал 2014г. и за ноябрь 2014г.

Количество дней в 2014 году равно 365, выручка за 2014 год равна 120 руб., товарные запасы как на начало 2014 года, так и на конец, равны нулю, таким образом, получаем:

P(ОбТЗ) = 365 / (120 / ((0 + 0)/2)),

т.е. при расчете оборачиваемости мы получаем деление на ноль, а значит невозможность адекватного применения формулы оборачиваемости. Отметим, что в случае проведения подобных расчетов внутри компании, когда есть доступ к первичным документам или к учетной информационной системе предприятия, вместо среднего уровня товарных запасов на начало и конец периода, сотрудники компании используют среднедневные уровни товарных запасов за период, тогда получается что 304 дня в году средние остатки товарных запасов были равны 100 рублям, а в оставшихся 61 днях – 0 рублей. Следовательно, средние товарные запасы за год составили:

Читайте также:  Примеры функции ЧАСТНОЕ для деления без остатка в Excel

(100*304 + 0*61) / 365 = 83,3руб.

В этом случае период оборачиваемости товарных запасов равен:

365 / (120 / 83,3) = 253,3 дня,

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

ОбТЗ = себестоимость проданной за период продукции / средние товарные запасы за период =

P(ОбТЗ) = 365 / ОбТЗ = 365 / 1,2 = 304 дня.

Таким образом, можно утверждать, что целесообразнее использовать в формуле оборачиваемости товарных запасов в числителе не выручку, а себестоимость проданной продукции:

ОбТЗ = стр.2120 / ((стр.1210 на нач.пер. + стр.1210 на кон.пер.)/2).

Пусть теперь в качестве периода взят 4-ый квартал 2014г. Количество дней в периоде равно 92, себестоимость продаж за период равна 100 руб., остаток товарных запасов на начало периода (01.10.14) равен 100 руб., на конец – 0 руб. Рассчитаем период оборачиваемости товарных запасов за 4-ый квартал 2014 года:

92 / (100 / ((100 + 0)/2)) = 92 / (100/50) = 92 / 2 = 46 дней << 304 дней,

что существенно меньше реального периода оборачиваемости в размере 304 дней. Очевидно, что если мы в качестве периода рассмотрим ноябрь 2014 года, то формула нам даст еще более отличный от реальности результат, а именно 15 дней.

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

Автору приходилось сталкиваться с такой проблемой. На одном из предприятий информационная система рассчитывала финансовый цикл для определения кассовых разрывов и их сроках как раз на основе приведенных здесь «классических» формул. В один из периодов оказалось так, что компании по факту не хватает существенного объема оборотных средств, хотя информационная система показывала, что должно хватать. В результате детального анализа ситуации оказалось, что финансовый цикл, который рассчитывался в рамках учетной системы, оказался вдвое меньше чем реальный, в результате чего пришлось срочно привлекать дополнительные банковские кредиты, причем из-за срочности ставки оказались выше, чем обычно. Обратим здесь лишь внимание на то, что у этой компании было два акционера, которые в процессе выявления указанной выше проблемы успели обвинить как друг друга, так и топ-именеджеров компании в воровстве денег, что в последствие крайне плохо сказалось на общем уровне корпоративного климата.

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

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

где через Т(ЧП), Т(В) и Т(А) обозначены темпы роста чистой прибыли, валовой выручки и (совокупных) активов соответственно:

Т(ЧП) = стр.2400 текущего периода / стр.2400 предыдущего периода;

Т(В) = стр.2110 текущего периода / стр.2110 предыдущего периода;

Т(А) = стр.1600 на конец периода / стр.1600 на начало периода.

В заключение данного раздела кратко опишем методику анализа кредитоспособности предприятий, которая обычно используется коммерческими банками, как экспресс-анализ кредитоспособностипотенциального заемщика.

Для определения кредитоспособности компании в рассмотрение берется некоторый набор ключевых финансово-экономических показателей, например, четыре показателя K1, K2, K3 и K4, соответственно равные коэффициенту текущей ликвидности (L1), коэффициенту промежуточного покрытия (L2), коэффициенту абсолютной ликвидности (L3) и коэффициенту автономии (S2), определение и формулы расчетов этих коэффициентов см. выше.

Для значений каждого показателя из состава ключевых (в нашем случае K1, K2, K3 и K4) вводится ранжирование по степени риска (чем ниже ранг, тем выше риск). Например, мы хотим использовать три степени риска:

Ранг2 – средний риск;

Ранг1 – высокий риск;

причем для показателя K1, коэффициента текущей ликвидности, ранжирование определяется следующим образом:

Ранг3, если K1 >= 2;

Ранг2, если 1 <= K1 < 2;

Ранг1, если K1 < 1;

для показателя K2, коэффициента промежуточного покрытия:

Ранг3, если K2 >= 1;

Ранг2, если 0,5 <= K2 < 1;

Ранг1, если K2 < 0,5;

для показателя K3, коэффициента абсолютной ликвидности:

Ранг3, если K3 >= 0,2;

Ранг2, если 0,15 <= K3 < 0,2;

Ранг1, если K3 < 0,15;

для показателя K4, коэффициента автономии:

Ранг3, если K4 >= 0,5;

Ранг2, если 0,4 <= K4 < 0,5;

Ранг1, если K4 < 0,4.

Далее для каждого из показателей K1, K2, K3 и K4 определяем его «вес» (v1, v2, v3 и v4) в системе выбранных показателей таким образом, чтобы вес каждого был от нуля до единицы, и сумма всех весов равнялась единице:

0 < vi < 1, для каждого i = 1,2,3,4;

v1 +v2 + v3 + v4 = 1.

Наконец считаем итоговый показатель кредитоспособности (K) по формуле:

K = v1*Ранг(K1) + v2*Ранг(K2) + v3*Ранг(K3) + v4*Ранг(K4),

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

Ранг3 (безрисковая зона), если K >= 2,5;

Ранг2(зона среднего риска), если 1,5 <= K < 2,5;

Ранг1(зона высокого риска), если K < 1,5.

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

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

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

СКАЧАТЬ
/>Пример финансово-экономического анализа />
С НАМИ ЭФФЕКТИВНЕЕ!

Формулы количества и суммы в Excel

формулы эксельФормулы количества и суммы в Excel — это самые популярные формулы Эксель, которые чаще всего могут понадобиться для составления различных видов отчетов. При этом может понадобиться выбрать не все значения подряд. Иногда нужны формулы Excel, с помощью которых нужно выбрать и посчитать значения количества и суммы, удовлетворяющие определенным условиям. Для решения этих задач подходят следующие формулы:

Формулы количества Excel

СЧЁТ

Подсчитывает количество ячеек, содержащих числа.
Учитывает аргументы, являющиеся числами, датами или текстовым представлением чисел (например, число, заключенное в кавычки, такое как «1»).

Синтаксис: СЧЁТ(значение1;[значение2];…)
Пример формулы: =СЧЁТ(A1:A20)

СЧЁТЗ

Учитывает данные любого типа, включая значения ошибок и пустой текст («»). Например, если в диапазоне есть формула, которая возвращает пустую строку, функция СЧЁТЗ учитывает это значение. Функция СЧЁТЗ не учитывает пустые ячейки.

Синтаксис: СЧЁТЗ(значение1;[значение2];…)
Пример формулы: =СЧЁТЗ(A2:A7)

СЧИТАТЬПУСТОТЫ

Подсчитывает пустые ячейки в указанном выше диапазоне.

Синтаксис: СЧИТАТЬПУСТОТЫ (значение1;[значение2];…)
Пример формулы: =СЧИТАТЬПУСТОТЫ(A2:A7)

СЧЁТЕСЛИ

Подсчитывает количество ячеек, отвечающих определенному условию (например, число поставщиков из определенного города). Функция СЧЁТЕСЛИ не учитывает регистр символов.

Синтаксис: =СЧЁТЕСЛИ(где нужно искать; что нужно найти)
Пример формулы: =СЧЁТЕСЛИ(A2:A5;»Лондон»); =СЧЁТЕСЛИ(A2:A5;A4)

СЧЁТЕСЛИМН

Подсчитывает количество ячеек, которые удовлетворяют нескольким условиям. Важно: Каждый дополнительный диапазон должен состоять из такого же количества строк и столбцов, что и аргумент диапазон_условия1. Эти диапазоны могут не находиться рядом друг с другом.

Синтаксис: =СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)
Пример формулы: =СЧЁТЕСЛИМН(B2:B5,»=Да»,F2:F5,»>1″)

Формулы суммы Excel

СУММ

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

Пример формулы: =СУММ(A2:A10), =СУММ(A2:A10;C2:C10)

СУММЕСЛИ

Суммирует значения, которые соответствуют указанному условию. Можно задать условие для текущего диапазона, а просуммировать соответствующие значения из другого диапазона.
Например, формула =СУММЕСЛИ(B3:B9; «Яблоки»; C3:C9) суммирует только те значения из диапазона C3:C9, для которых соответствующие значения из диапазона B2:B5 равны «Яблоки».

Синтаксис: = СУММЕСЛИ(диапазон; условие;[диапазон_суммирования])
Пример формулы: =СУММЕСЛИ(B2:B25;»> 5″), =СУММЕСЛИ(A2:A7;»Фрукты»;C2:C7)

СУММЕСЛИМН

Суммирует все значения, которые удовлетворяют нескольким условиям. Например, с помощью функции СУММЕСЛИМН можно найти число всех поставщиков, (1) находящихся в определенном городе, (2)которые продают определенный товар.

Синтаксис: СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)
Пример формулы: =СУММЕСЛИМН(B2:B9; C2:C9; «Рязань»; E2:E9; «Бананы»)

Выполнение расчетов с помощью финансовых функций

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

Переход к данному набору инструментов легче всего совершить через Мастер функций.

  1. Выделяем ячейку, куда будут выводиться результаты расчета, и кликаем по кнопке «Вставить функцию», находящуюся около строки формул.

Переход в мастер функций в Microsoft Excel

Мастер функций в Microsoft Excel

Переход к группе финансовых функций в Microsoft Excel

Выбор конкретной финансовой функции в Microsoft Excel

В Мастер функций также можно перейти через вкладку «Формулы». Сделав переход в неё, нужно нажать на кнопку на ленте «Вставить функцию», размещенную в блоке инструментов «Библиотека функций». Сразу вслед за этим запустится Мастер функций.

Переход в мастер функций через вкладку Формулы в Microsoft Excel

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

Переход к выбору финансовых функций через кнопку на ленте в Microsoft Excel

ДОХОД

Одним из наиболее востребованных операторов у финансистов является функция ДОХОД. Она позволяет рассчитать доходность ценных бумаг по дате соглашения, дате вступления в силу (погашения), цене за 100 рублей выкупной стоимости, годовой процентной ставке, сумме погашения за 100 рублей выкупной стоимости и количеству выплат (частота). Именно эти параметры являются аргументами данной формулы. Кроме того, имеется необязательный аргумент «Базис». Все эти данные могут быть введены с клавиатуры прямо в соответствующие поля окна или храниться в ячейках листах Excel. В последнем случае вместо чисел и дат нужно вводить ссылки на эти ячейки. Также функцию можно ввести в строку формул или область на листе вручную без вызова окна аргументов. При этом нужно придерживаться следующего синтаксиса:

Функция ДОХОД в Microsoft Excel

Главной задачей функции БС является определение будущей стоимости инвестиций. Её аргументами является процентная ставка за период («Ставка»), общее количество периодов («Кол_пер») и постоянная выплата за каждый период («Плт»). К необязательным аргументам относится приведенная стоимость («Пс») и установка срока выплаты в начале или в конце периода («Тип»). Оператор имеет следующий синтаксис:

Фнкция БС в Microsoft Excel

Оператор ВСД вычисляет внутреннюю ставку доходности для потоков денежных средств. Единственный обязательный аргумент этой функции – это величины денежных потоков, которые на листе Excel можно представить диапазоном данных в ячейках («Значения»). Причем в первой ячейке диапазона должна быть указана сумма вложения со знаком «-», а в остальных суммы поступлений. Кроме того, есть необязательный аргумент «Предположение». В нем указывается предполагаемая сумма доходности. Если его не указывать, то по умолчанию данная величина принимается за 10%. Синтаксис формулы следующий:

Фнкция ВСД в Microsoft Excel

Оператор МВСД выполняет расчет модифицированной внутренней ставки доходности, учитывая процент от реинвестирования средств. В данной функции кроме диапазона денежных потоков («Значения») аргументами выступают ставка финансирования и ставка реинвестирования. Соответственно, синтаксис имеет такой вид:

Фнкция МВСД в Microsoft Excel

ПРПЛТ

Оператор ПРПЛТ рассчитывает сумму процентных платежей за указанный период. Аргументами функции выступает процентная ставка за период («Ставка»); номер периода («Период»), величина которого не может превышать общее число периодов; количество периодов («Кол_пер»); приведенная стоимость («Пс»). Кроме того, есть необязательный аргумент – будущая стоимость («Бс»). Данную формулу можно применять только в том случае, если платежи в каждом периоде осуществляются равными частями. Синтаксис её имеет следующую форму:

Функция ПРПЛТ в Microsoft Excel

Оператор ПЛТ рассчитывает сумму периодического платежа с постоянным процентом. В отличие от предыдущей функции, у этой нет аргумента «Период». Зато добавлен необязательный аргумент «Тип», в котором указывается в начале или в конце периода должна производиться выплата. Остальные параметры полностью совпадают с предыдущей формулой. Синтаксис выглядит следующим образом:

Фнкция ПЛТ в Microsoft Excel

Формула ПС применяется для расчета приведенной стоимости инвестиции. Данная функция обратная оператору ПЛТ. У неё точно такие же аргументы, но только вместо аргумента приведенной стоимости («ПС»), которая собственно и рассчитывается, указывается сумма периодического платежа («Плт»). Синтаксис соответственно такой:

Фнкция ПС в Microsoft Excel

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

Функция ЧПС в Microsoft Excel

СТАВКА

Функция СТАВКА рассчитывает ставку процентов по аннуитету. Аргументами этого оператора является количество периодов («Кол_пер»), величина регулярной выплаты («Плт») и сумма платежа («Пс»). Кроме того, есть дополнительные необязательные аргументы: будущая стоимость («Бс») и указание в начале или в конце периода будет производиться платеж («Тип»). Синтаксис принимает такой вид:

Функция СТАВКА в Microsoft Excel

ЭФФЕКТ

Оператор ЭФФЕКТ ведет расчет фактической (или эффективной) процентной ставки. У этой функции всего два аргумента: количество периодов в году, для которых применяется начисление процентов, а также номинальная ставка. Синтаксис её выглядит так:

Функция ЭФФЕКТ в Microsoft Excel

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

ЗакрытьМы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12021 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

ЗакрытьОпишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Функция СУММЕСЛИ при условии соответствия тексту

Возьмем второй пример, когда СУММЕСЛИ используется для подсчета суммы в ячейках, подпадающих под соответствие надписями в другом диапазоне блоков. Это пригодится, например, когда выполняется подсчет общей цены всех товаров, находящихся в одной категории, или рассчитываются затраты компании на зарплаты сотрудникам на конкретных должностях. Пример оформления в этой ситуации еще интересен тем, что синтаксис немного меняется, поскольку в условие подпадает второй диапазон ячеек.

    В этот раз помимо диапазона суммируемых ячеек определите и те, где присутствуют надписи, попадающие в условие.

Выбор диапазона ячеек для использования функции СУММЕСЛИ в Excel при сопоставлении названий

Объявление функции СУММЕСЛИ в Excel при ее использовании для сопоставления названий

Объявление диапазона ячеек при использовании функции СУММЕСЛИ в Excel при сопоставлении названий

Объявление суммируемых ячеек при использовании функции СУММЕСЛИ в Excel для сопоставления названий

Заменяйте слово или вписывайте целую фразу, учитывая регистр символов, чтобы создавать СУММЕСЛИ при подсчете требуемых значений.

Разработка финансовой модели ритейла в EXCEL

/>Уважаемые посетители! Перечень всех финансовых и инвестиционных моделей нашего сайта, которые можно бесплатно скачать, Вы найдете здесь.

Крайне сложно переоценить роль доверия в нашей жизни, тем более в бизнесе. Коммерсант, доверяя своему поставщику, производит предоплату за товар, который будет поставлен через определенное в договоре количество дней. Или же отгружая готовую продукцию заказчику, доверяя ему по крайней мере на основании подписанного договора, надеется, что оплата поступит своевременно, согласно подписанной в том же договоре отсрочке платежа. Генеральный директор при утверждении бюджета компании, доверяя своему коммерческому директору, старается быть уверенным в том, что необходимые объемы продукции будут проданы в течение бюджетного периода, и по окончании которого не будет стыдно отчитываться перед акционерами и инвесторами предприятия.

Опираясь на Доверие, мы строим финансовые планы. А вот на что опирается наше Доверие?

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

В любом случае владение методами финансового моделирования дает достаточно глубокое понимание того, каким образом подходить к решению вопросов о выборе системы ключевых показателей эффективности (системы KPI) компании и, что самое главное, каким образом на практике производить расчет целевых значений показателей системы KPI.

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

Поэтому, чтобы внести большую определенность, и, как можно ближе быть к практике, мы начнем с описания методологии наиболее простой финансовой модели розничной торговли, а именно финансовой модели ритейла с системой бюджетирования «сверху вниз» (Top-Down) и с аутсорсингом основных операционных подразделений, таких как служба поддержки клиентов (кол-центр), входящая, складская и исходящая логистика. Также мы опустим на начальном этапе учет внеоборотных активов, например, таких как основные средства, поскольку они, очевидно, не играют существенной роли для создания финансовой модели торговой деятельности

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

СКАЧАТЬ
/>Финансовая модель ритейла Top-Down OutSource />

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

Конечно же далее в разделах нашего сайта мы представим вниманию читателя описание как других типов финансовых моделей ритейла (например, когда бюджетирование происходит «снизу вверх» и операционные подразделения не выведены в аутсорсинг), так и финансовых моделей других направлений бизнеса, например, таких как продажа, монтаж и пуско-наладка оборудования, строительство, эксплуатация недвижимости, производство наукоемкой продукции, производство продукции тяжелого машиностроения и т.п. Со списком и описанием всех финансовых моделей, представленных на нашем сайте, можно ознакомиться здесь, где их также можно бесплатно скачать.

Также сразу выкладываем урезанную версию финансовой модели ритейла в виде нижеследующего EXCEL-файла для коммерческих подразделений, менеджеров отделов продаж, категорийных менеджеров и т.п. В данной версии отсутствует балансовый отчет (Balance Sheet), вкладка с заданием финансовых условий («CF_условия») движения денежных средств и соответственно отчет о движении денежных средств (отчет Cash Flow). Таким образом, на основе этой финансовой модели, как минимум, можно моделировать структуру доходной части (отчет P&L – прибыли и убытки), а также объем и структуру товарооборота в разрезе направлений продаж и категорий товаров.

СКАЧАТЬ
/>Финмодель ритейла для коммерсантов Top-Down OutSource />

EXCEL-файл с этой же, но только пустой, незаполненной финансовой моделью, т.е. с нулевыми входящими параметрами во вкладке “условия” можно скачать в конце раздела.

Итак, приступим к описанию методологии финансового моделирования. Начнем с содержания нашей финансовой модели. Поскольку модели представлены нами в виде EXCEL-файлов, то мы будем использовать такие структурные понятия EXCEL, как лист/вкладка, ячейка, срока, столбец, формула и т.п. А читателю в связи с этим для лучшего понимания того, о чем идет речь, предлагаем систематически заглядывать в скаченные EXCEL-файлы с финансовыми моделям.

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

Оглавление финансовой модели

Для удобства пользования финансовой моделью переход во все разделы организован посредством гиперссылок из оглавления, а обратно в оглавление из каждого раздела можно перейти по гиперссылке, находящейся в левом верхнем углу каждого листа EXCEL-файла.

Все разделы в нашей финансовой модели разделяются на следующие группы:

– вкладки с исходными данными – начальные условия финансовой модели;

– вкладки с расчетами – функционал финансовой модели;

– вкладки с отчетами – результат финансового моделирования.

К коммуникационно-методологическим вкладкам мы относим:

Вкладка “методология” примерно повторяет содержание настоящего раздела сайта, только в более техническом виде. Эта вкладка содержит полную информацию только в FULL-версии финансовой модели, которую мы как раз и продаем. Содержание этой вкладки может оказаться крайне полезным для IT-специалистов Вашей компании, если, например, Вы решите самостоятельно внедрять нашу финансовую модель в процесс управления финансами своей компании.

С помощью материалов листа “методология” можно, с одной стороны, разобраться, как адаптировать финансовую модель под конкретную специфику вашего предприятия, например, добавить или удалить из рассмотрения товарную категорию или добавить еще какое-либо направление бизнеса, допустим продажи в Белоруссии и Казахстане, или же рассчитывать точку безубыточности не по выручке, а по количеству товаров и т.д. С другой стороны, можно использовать для написания технического задания на доработку автоматизированной системы управления в части финансового моделирования бизнеса на базе корпоративной информационной системы.

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

В полной FULL-версии финансовой модели вкладка «показатели» содержит Глоссарий с определениями и описанием всех входящих в модель показателей.

Вкладки с исходными данными

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

Ячейки для ввода исходных данных финмодели

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

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

Функционал финансовой модели сосредоточен в двух вкладках

и представляет собой системный набор EXCEL-формул, которые преобразуют начальные данные модели из вкладок «условия» в итоговые отчетные данные вкладок с результатами финансовой модели, структурированными согласно управленческим формам финансово-экономической отчетности.

Наконец вкладки с отчетами финансовой модели – это вкладки с формами управленческой отчетности, которые автоматически заполняются и пересчитываются при изменении начальных данных, которые в свою очередь обычно разделяются на две группы: стандартные формы финансовой отчетности такие, как отчет о прибылях и убытках (P&L), отчет о движении денежных средств (Cash Flow) и прогнозный баланс (Balance Sheet), плюс к тому мы добавляем к этому списку отчет о движении товарных запасов (Stock Flow), как важный отчет для розничной торговли; и дополнительные формы, которые предназначены для более детального и всестороннего раскрытия финансово-экономической информации с учетом специфики того типа бизнеса, который формализуется в рамках финансовой модели. В качестве дополнительных форм отчетности в нашем случае представлены следующие отчеты:

– “SF_age” – отчет о возрастной структуре товарных запасов;

– “Turnover” – отчет о товарообороте;

– “FinCycle” – расчет финансового цикла;

– “mPL” – маржинальный отчет о прибылях и убытках;

– “UE” – экономика на один проданный заказ.

В качестве примера приведем здесь формат управленческого отчета о прибылях и убытках (отчет P&L), который используется нами в финансовой модели:

Пример отчета PL финмодели

Начинаем моделирование бюджета продаж с указания срока бюджетирования или горизонта моделирования. В нашем случае мы предлагаем стандартное моделирование на один год и для задания бюджетного периода достаточно во вкладке “условия” указать начальную дату бюджетного года. Допустим это будет 2016 год, тогда вносим в соответствующую ячейку вручную с клавиатуры (ячейка с пунктирной границей) дату 01.01.16г.:

Старт бюджетирования финмодели

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

Шаг моделирования

Обращаем внимание, что ячейка для выбора типа периодичности имеет сплошную границу, а это значит, что в качестве значений указанной ячейки могут быть только значения из заданного «выпадающего» списка:

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

Ежеквартальное моделирование

Или если Вы выберете «ежемесячно», то появится такая «шкала»:

Ежемесячное моделирование

Приступаем к выбору метода бюджетирования. Во-первых, напомним, что мы изначально считаем, что система управления нашей компании предполагает выстраивание процесса бюджетирования сверху вниз, см.выше. Обычно при таком подходе укрупненно план продаж «устанавливается сверху» собственниками или крупными инвесторами. Чаще всего это происходит либо утверждением собственниками конкретного объема продаж, выраженного в деньгах, на бюджетный год с возможной разбивкой по периодам, либо через утверждение плановых процентов прироста объемов продаж бюджетного года по отношению к фактическим объемам продаж предыдущего года. Иногда бывает и так, что план объемов продаж утверждается не в деньгах, а в количестве клиентских заказов или в количестве штук продаж товаров, параллельно сопровождаемый плановым средним чеком продаж или плановой средней ценой продажи одного товара, что, кстати говоря, существенно лучше. Такой вот «план» спускается на плечи Генерального директора. И далее «Вперед Топ-Менеджмент!»

Читайте также:  Как рассчитать последний день месяца в Excel

В нашей финансовой модели предусмотрены три типа продаж:

– розничная продажа товаров/заказов (B2C);

– доходы от продажи услуг по доставке клиентских заказов;

Причем B2C-продажи в свою очередь могут детализироваться в трех «измерениях»:

– детализация по направлениям бизнеса;

– детализация по категориям товаров.

Для того чтобы наш пример не был тривиальным, но, с другой стороны, чтобы не был и слишком перегруженным, мы представили вариант, когда у нашей гипотетической торговой компании может быть два бизнес-направления в части B2C-продаж:

– Продажи со склада (offline продажи);

– VMI-продажи (online продажи);

два региональных направления:

– продажи по Москве и Московской области;

и три товарные категории:

Под «продажами со склада» мы понимаем классические продажи из обычных offline-магазинов, под VMI-продажами – online продажи через Интернет-магазин нашей компании, когда под клиентские заказы блокируется соответствующий сток у Поставщиков (еще такие продажи называют Block Stock), подробнее о VMI-продажах можно прочитать здесь.

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

В части B2B-продаж мы рассматриваем только возможность детализации по двум направлениям бизнеса, в нашем случае мы выбрали следующие направления:

– комиссионные продажи offline;

– продажи услуг Market Place online.

О том, что такое Market Place, можно прочитать здесь.

Тем самым, структура выручки в нашей финансовой модели может иметь максимум четырнадцать комбинаций – двенадцать для непосредственно продаж товаров и две комбинации для продаж B2B-услуг.

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

А это значит, что в рамках нашего же ограничения на модель процесса бюджетирования (сверху вниз) необходимо, чтобы наша финансовая модель после внесения в нее данных об утвержденных сверху объемов продаж могла распределить эти объемы между всеми указанными выше четырнадцатью комбинациями.

Опишем, как все это реализовано в финансовой модели.

Для внесения в модель утвержденного плана продаж товаров верхнего уровня предлагается девять вариантов, которые выбираются из выпадающего списка, как показано на рисунке:

Методы бюджетирования торговой деятельности

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

Финансовый метод бюджетирования продаж товаров предполагает внесение данных о плановых объемах продаж в разрезе кварталов/месяцев (периодов) сразу в тысячах рублей. Метод бюджетирования «товары» предполагает внесение данных о продажах в штуках товаров, соответственно метод «заказы» – в количестве заказов.

Дополнительные атрибуты метода «прямой», «приросты г/г» и «приросты пер/пер» означают способ задания объемов продаж:

«прямой» – задается вручную, внесением объемов продаж (в деньгах, в штуках товаров или в количестве заказов) непосредственно в план бюджетного года для каждого периода;

«прямой г/г» – задается вручную, путем сначала внесения фактических объемов продаж за каждый период прошлого года и потом внесения утвержденных плановых процентов прироста год к году для каждого соответствующего периода бюджетного года;

«прямой пер/пер» – задается вручную, путем сначала внесения фактического объема продаж последнего периода (либо декабрь при выборе ежемесячной, либо 4-ый квартал – при выборе ежеквартальной разбивки) прошлого года и потом внесения утвержденных плановых процентов прироста каждого периода бюджетного года к предыдущему периоду.

На предыдущем рисунке представлено заполнение объемов продаж при выборе метода бюджетирования «финансовый-прямой» в случае ежеквартальной разбивки бюджетного года – на нем мы видим, что с первого по четвертый квартал планируются объемы продаж в размере 260, 300, 270 и 380 млн.руб. соответственно, которые просто внесены в ячейки «с пунктирными границами» вручную с клавиатуры.

Для примера рассмотрим еще пару вариантов внесения объемов продаж для методов «товары-приросты г/г» и «заказы-приросты пер/пер».

Допустим в предыдущем году по факту компания продала 40тыс. штук товаров в первом квартале, 50тыс. штук во втором квартале, 45тыс. шт – в третьем и 60тыс. – в четвертом. Пусть также на бюджетный год собственники компании утвердили плановые приросты объемов продаж в штуках товаров в следующих размерах: 10% – плановый прирост объемов первого квартала бюджетного года к первому кварталу предыдущего года; 20% – прирост второго квартала; 15% и 30% – соответственно приросты третьего и четвертого. Тогда, выбирая метод бюджетирования «товары-приросты г/г», получаем следующую «картинку» при заполнении вышеуказанных данных во вкладке «условия» финансовой модели:

Годовые приросты продаж

Мы видим, что первая строчка, в которую мы вносили данные при прямом методе пустая и более того напротив нее не стоит «красная звездочка», т.е. финансовая модель не предлагает заполнять эту строчку. А предлагается заполнить следующие две строки (со «звездочками») – одна для фактических данных прошлого года, а другая для плановых процентов прироста год к году. В последней итоговой строке финансовая модель нам рассчитала план продаж в штуках товаров на бюджетный год: 44тыс., 60тыс., 51 750 и 78тыс. штук товаров для каждого квартала.

При выборе метода бюджетирования «заказы-приросты пер/пер» вносим количество проданных заказов в четвертом квартале прошлого года, пусть это количество было равно 70тыс., после чего вносим плановые приросты объемов продаж в количестве заказов: допустим (-5%) – плановый прирост количества заказов 1кв бюджетного года к факту 4кв прошлого года; 10% – прирост 2кв к 1кв бюджетного года; 5% и 30% – соответственно приросты 3кв и 4кв по отношению к 2кв и 3кв бюджетного года. Тогда получаем:

Планирование заказов

Таким образом, получаем итоговый план продаж товаров в количестве заказов в поквартальной разбивке: 66 500, 73 150, 76 808 и 99 850 заказов.

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

Планирование B2B-продаж значительно проще, поэтому мы перейдем к этому разделу чуть ниже.

Теперь возникает следующий нюанс. Если мы выбираем финансовый метод бюджетирования, то, очевидно, что без особых усилий мы получаем план продаж в тысячах рублей с разбивкой по бюджетным периодам и можно приступать к его детализации. Другое дело – это когда план продаж изначально вносится в штуках товаров или в количестве продаваемых заказов. В этом случае, чтобы получить план продаж в деньгах, нам потребуются либо плановые средние стоимости продажи одной товарной единицы (одной штуки), либо плановые средние чеки для одного продаваемого заказа.

В этом случае финансовая модель естественно предлагает (см. EXCEL-файлы с финмоделями) внести данные о плановом среднем чеке одного клиентского заказа или о плановой средней стоимости продажи одного товара в зависимости от выбранного метода бюджетирования («заказы» или «товары»). Но вносить эти средние величины уже необходимо не одной суммой для каждого бюджетного периода, а для каждого типа детализации в отдельности – у нас их в самом общем случае 12-ть для B2C-продаж.

Поэтому сразу после внесения плановых объемов продаж верхнего уровня, причем не важно в деньгах, в штуках товаров или в количестве заказов, финансовая модель «задает» три вопроса:

– нужна ли детализация по направлениям бизнеса;

– нужна ли региональная детализация;

– нужна ли детализация по категориям товаров.

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

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

Планирование средних чеков

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

Распределение плана продаж на онлайн и офлайн

Здесь по онлайн направлению задаются проценты объемов продаж вручную, а для офлайн направления рассчитываются, как «100% минус процент онлайна».

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

Детализация бюджета продаж

Распределение продаж задается во вкладке «условия» в виде процентов распределения, а расчет в деньгах, штуках товаров или количестве заказов происходит во вкладке «расчеты».

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

Детализация средних чеков

После чего во вкладке «расчеты» производится расчет итоговых средних чеков (средних стоимостей одного товара) для каждого бюджетного периода финансовой модели, и они выводятся в итоговую строку под внесенными данными о средних чеках в различных комбинациях, см. последний рисунок – последняя строка.

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

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

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

Схему построения бюджета закупок можно посмотреть здесь.

По аналогии с заданием среднего чека задаем вручную рентабельность продаж:

Детализация рентабельности продаж

Итоговая рентабельность также рассчитывается через вкладку «расчеты» и выводится в итоговую строку рентабельности во вкладку «условия», см. рисунок.

Производим расчет себестоимости по формуле:

COGS = Sales * (1 – R).

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

Распределение продаж по дням недели

Здесь для каждого дня кроме понедельника процент распределения продаж вносится вручную, а для понедельника рассчитывается как 100% минус сумма процентов по всем остальным дням.

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

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

Операционный цикл онлайн заказов

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

Отметим здесь, что мы крайне поверхностно в настоящей финансовой модели учитываем специфику интернет-торговли, например, мы даже не закладываем в модель уровень фулфилмента (по умолчанию он у нас равен 100%). Дело в том, что полная детальная финансовая модель онлайн ритейла с учетом всех особенностей операционного цикла онлайн продаж выложена на нашем же сайте здесь . Но все-таки, чтобы обратить на это внимание пользователей, мы внесли изложенную выше специфику в условия финмодели.

Выручка от предоставления услуг доставки в рамках направления B2C-продаж задается через процент продаж, доставляемых до клиента. Понятно, что такой процент для офлайн торговли невысок (у нас он задается на уровне 20-25%, см. финмодель), а для онлайн направления – стремится к 100% (у нас он на уровне 90-95%).

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

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

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

Для понимания этой незамысловатой технологии сравним наш метод расчета бюджета закупок с классическим, представленным во “всех книжках” по этой теме, суть которого состоит в том, чтобы сначала, отталкиваясь от объемов товарных запасов на начало Периода (обозначим ТЗ(0)), через себестоимость утвержденного бюджета продаж Периода и заданный в финмодели коэффициент оборачиваемости запасов ОбТЗ (не путать с периодом оборачиваемости!) рассчитать товарные остатки на конец Периода (обозначим ТЗ(1)) по формуле:

ТЗ(1) = 2 * C / ОбТЗ – ТЗ(0).

После чего бюджет закупок SF(+) за Период рассчитывается по такой формуле:

SF(+) = ТЗ(1) + C – ТЗ(0).

Все очень даже логично выглядит, если только не вдаваться в смысл “классической” формулы расчета коэффициента оборачиваемости запасов или формулы расчета периода оборачиваемости P(ОбТЗ) в днях через коэффициент оборачиваемости ОбТЗ за Период:

P(ОбТЗ) = (кол-во дней Периода) / ОбТЗ =

= (кол-во дней Периода) / [ C / ( ТЗ(0) + ТЗ(1) ) / 2 ].

Рассмотрим простой пример, на подобие тех которые мы уже не один раз предлагали вниманию на страницах нашего сайта. Пусть мы купили товар за 100руб. 31-ого июля и продали его 2-ого августа. Тогда если взять в качестве нашего Периода август, то

P(ОбТЗ) = 31день / [ 100руб. / ( 100руб. + 0руб. ) / 2 ] = 15,5 дней.

То есть классическая формула, представленная во всех учебниках говорит нам о том, что период оборачиваемости запасов в нашем случае равен чуть более чем 15-ти дням, но ведь товар-то у нас физически был всего лишь два дня!

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

Отличие же нашего подхода от “классического” к финансовому моделированию состоит в том, что мы используем прямой метод формирования бюджетов, например, как в данном случае, бюджета закупок, смысл которого состоит в том, что все типовые операции распределяются по датам бюджетного периода. Если в рамках ежедневного распределения плана продаж нашей финмодели мы планируем 16-ого августа произвести продажу электроники на 100руб. в себестоимости и при этом в условиях по оборачиваемости запасов на август внесен плановый период оборачиваемости по категории “Электроника” в размере 15 дней, то соответственно в бюджете закупок 01-ого августа появится сумма 100руб. Далее, собирая по дням рассматриваемого Периода все плановые закупки, получаем бюджет закупок Периода.

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

Забегая вперед, отметим лишь то, что итог функционального отчета о прибылях и убытках (функциональный P&L) по всему бюджетному году в общем случае не совпадает с приростом собственного капитала в прогнозном балансе, а итог маржинального P&L совпадает. При этом в разрезе по бюджетным периодам ситуация ровно наоборот. А также на основе управленческого маржинального отчета о прибылях и убытках система управления выстраивается более эффективно.

Задаем условия на возвраты товаров клиентами (по закону имеют право и это не редко случается) путем внесения процента возврата товаров относительно продаж в разрезе товарных категорий и среднего срока возврата товаров:

Планирование условий возврата товаров

Теперь у нас есть все чтобы сформировать отчет о движении товарных запасов (Stock Flow) и рассчитать периоды оборачиваемости продаж и товарных запасов в целом:

Отчет о движении товаров SF

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

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

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

В нашей финмодели мы выделяем следующие статьи переменных расходов:

– расходы входящей логистики;

– расходы складской логистики;

– расходы исходящей логистики;

– аренда торговых площадей;

– мотивация коммерческого персонала;

– переменные финансовые расходы.

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

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

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

В том случае если пользователем предполагается учет продаж в количестве заказов, то расходы входящей логистики предлагается рассчитать по следующей схеме. Поскольку мы находимся в условиях, когда операционные расходы выносятся на аутсорсинг, то это значит, что нашему предприятию потребуется заказывать машины в транспортных компаниях для доставки закупаемых товаров/заказов от Поставщиков. Поэтому для расчета бюджета входящей логистики устанавливается тип машины с точки зрения возможных объемов перевозки и стоимость использования машины в сутки, а далее через задание среднего объема одного заказа во вкладке «расчеты_ежедн» рассчитывается необходимое количество машин на каждый день. Внесение данных об условиях расчетов бюджета входящей логистики выглядит так:

Бюджет входящей логистики

Во вкладке «расчеты_ежедн» расходы входящей логистики отнесены к датам соответствующих закупок товаров.

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

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

Во вкладке «расчеты_ежедн» расходы складской логистики и кол-центра для B2C-продаж отнесены к средне арифметическим датам между датами продажи и закупки соответствующих товаров.

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

Прежде чем переходить к блоку постоянных расходов разберемся с методологией формирования отчета о прибылях и убытках или коротко – отчета P&L. Обычная классическая схема формирования этого отчета, ее еще называют функциональным подходом, состоит в том, что все расходы попадают в период по дате проведения расходной операции, за исключением себестоимостных затрат.

Допустим мы планируем какие-либо акции под праздничный день 8 марта, чтобы в этот день объемы продаж были существенно больше, чем в обычный день. Проведение соответствующих маркетинговых мероприятий может начинаться за месяц до 8-ого марта, т.е. в феврале. Тогда получается, что, так сказать, сверх выручка этого праздничного дня окажется в марте, а маркетинговые расходы, которые на самом деле непосредственно связаны с этими продажами, «лягут» в февраль, согласно методологии функционального отчета P&L. В результате такой отчет о прибылях и убытках является просто статистическим отчетом о доходах и расходах, распределенных по функциональным подразделениям компании и по периодам.

Кстати, при функциональном подходе совсем необязательно разделять расходы на переменные и постоянные – не имеет смысла, будет правильнее, если расходы будут разделены по принципу разделения предприятия на функциональные подразделения. Хотя мы разделяем, но в финансовой модели для бюджетирования «снизу в верх» сделаем отчет P&L с функциональной разбивкой.

С другой стороны смотрит на экономику предприятия маржинальный отчет P&L (в финансовой модели это вкладка «mPL»). Методология маржинального отчета о прибылях и убытках предполагает, что переменные расходы попадают в период, если они непосредственно связаны с выручкой данного периода, причем независимо от того, когда (в каких периодах) они были произведены. При таком подходе мы видим реальную экономику продаж и можем сравнивать периоды между собой, для того чтобы понимать повышается или понижается эффективность операционной деятельности и если понижается, то в рамках каких операционных подразделений.

В «продвинутых» компаниях, где финансовые директоры доносят смысл функционального и маржинального подхода к управлению до руководства компаний, обычно управленческая отчетность в части отчета о прибылях и убытках содержит одновременно две формы: функциональную и маржинальную. Причем обычно маржинальный отчет P&L в таких компаниях может иметь даже ежедневную детализацию (в одной из крупнейших российских торговых онлайн компаний автор создал такую ежедневную финансовую отчетность – эта компания одной из первых среди топ-30 Интернет-ритейлеров вышла в точку безубыточности).

Отметим, что в нашей финмодели в маржинальном P&L-отчете мы не учитываем возвраты товаров.

Также при маржинальном подходе появляется смысл в понятии эффективная маржа, см. вкладку «Turnover», а также в понятии «экономика на один заказ», см. вкладку «UE».

Постоянные расходы в нашей финансовой модели имеют следующую разбивку по статьям:

– социальные сборы (ПФР, ФСС, ФФОМС);

– расходы на персонал;

– постоянные финансовые расходы;

Бюджет ФОТ формируется в финмодели через внесение планового штатного расписания, включающего список должностей, количество сотрудников и их оклады:

Бюджет ФОТ

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

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

Для получения отчета о прибылях и убытках нам осталось внести ставку налога на добавленную стоимость (НДС). В нашем случае мы предлагаем внести в финмодель две ставки: для исходящего НДС и входящего НДС, поскольку, например, продажи могут идти всегда со ставкой 18%, а вот закупки чаще всего бывают смешанными, поэтому в заполненной нами финансовой модели, мы внесли ставку входящего НДС в размере 17%.

Читайте также: 

Внеся все необходимые данные во вкладку «условия», наша финансовая модель автоматически рассчитает и сформирует отчет о прибылях и убытках, см. вкладку «PL».

Ранее в этом разделе мы уже представили формат подробного отчета P&L нашей финмодели, соответственно здесь представим его “свернутый” вариант:

Укрупненный отчет PL

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

Ключевые показатели PL-отчета

Это позволяет не перескакивать каждый раз во вкладку «PL» для того, чтобы посмотреть, какой результат получился, после внесения Вами очередных изменений в начальные условия.

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

Условия определяющие движение денежных средств вносятся во вкладке «CF-условия». Необходимо, чтобы для каждой доходно-расходной операции, учитываемой во вкладке «расчеты_ежедн» и попадающей потом в отчет P&L, был определен порядок ее оплаты. Под порядком оплаты того или иного действия понимается распределение долей оплаты во времени. Например, мы покупаем партию товаров у поставщика общей стоимостью в 100руб. на следующих условиях по оплате: предоплата в размере 30% за 15 дней до отгрузки и доплата или полный расчет через 45 дней после отгрузки в размере 70%.

Например, период, в течение которого выручка, полученная после продажи клиенту заказа, поступает на расчетный счет в виде денежных средств, называется периодом оборачиваемости дебиторской задолженности. Соответственно, чтобы финансовой модели рассчитать план поступлений денежных средств (ДС) в рамках формирования прогнозного отчета о движении денежных средств (ДДС) или, как его еще называют, Cash Flow, необходимо задать вручную период оборачиваемости дебиторской задолженности.

Внесение периода оборачиваемости дебиторской задолженности для B2C-торговли вполне понятно – задается количество дней для каждой комбинации детализации продаж с момента продажи клиенту товара до момента поступления ДС на расчетный счет компании – обычно это занимает один-два дня в зависимости от времени суток и расторопности инкассаторской службы.

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

Условия поступления денежных средств

Здесь нумерация месяцев начинается с нуля – если нулевой, то это отчетный, если первый, то это следующий за отчетным месяц и т.д. Если мы в нашем примере рассмотрим онлайн продажи B2B-услуг, то «единица» напротив «№мес» означает что агентское вознаграждение за текущий отчетный месяц нам поступит в виде ДС на следующий месяц, причем если быть точным, то 20-ого числа следующего месяца за отчетным, о чем говорит число 20 напротив «число_мес».

Период оборачиваемости кредиторской задолженности или что тоже самое условия оплат закупки товаров и расходов в части оплат товаров и маркетинговых условий задаются через внесение во вкладку «CF_условия» средних процентов предоплат/доплат и сроков в днях относительно времени проведения операций закупки или начисления расходов. Причем периоды оборачиваемости предоплат вносятся со знаком «минус», а доплат – со знаком «плюс».

Условия оплат остальных расходов, кроме оплаты ФОТ, задаются через внесение номера месяца оплаты, где нулевым является отчетный месяц, и число месяца оплаты. После чего во вкладке «расчеты_ежедн» все эти условия обрабатываются и попадают в сведенном виде в отчет ДДС во вкладке «CF».

Оплаты ФОТ задаются через проценты аванса и оплаты ФОТ сотрудникам, а также через номера месяцев и числа месяцев выплаты аванса и доплаты – в нашем случае аванс составляет 60% и выплачивается 25-ого числа отчетного месяца, соответственно полный расчет с сотрудниками в размере 40% от ФОТ производится 10-ого числа месяца, следующего за отчетным.

Условия оплаты расходов

Итак, внося в самом начале вкладки «CF_условия» начальный капитал бюджетного года, как сумму денежных средств оставшихся с прошлых периодов, получаем отчет о движении денежных средств (отчет Cash Flow), находящийся и автоматически формирующийся во вкладке «CF» нашей финансовой модели:

Отчет о движении денежных средств CF

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

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

Почему самым важным? Контроль в виде основного балансового равенства активов пассивам не сойдется если в финансовой модели допущены любые типы ошибок как в формулах финмодели – ошибки технического характера, так и в методологии расчета тех или иных финансово-экономических показателей – методологические ошибки.

У нас в активах баланса выделяются только оборотные активы (в начале раздела мы говорили о том, что в финмоделях настоящего раздела мы опустили учет внеоборотных ативов, таких как, например, основные средства, чтобы не усложнять модели) разбитые по следующим статьям:

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

Пассивы прогнозного баланса разделены на статьи:

– задолженность по оплате НДС.

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

Выглядит балансовый отчет (Balance Sheet) так:

Баланс торговой компании

Обратим внимание на строку с контролем, расположенную под строкой «нераспределенная прибыль/убыток». Формула данного контроля в общем виде имеет следующий вид:

Нераспределенная прибыль/убыток на конец бюджетного года =

= EBITDA маржинального P&L

В нашем случае, когда маржинальный P&L формируется без учета возвратов товаров, формула контроля такая:

Нераспределенная прибыль/убыток на конец бюджетного года =

= EBITDA маржинального P&L +

+ Валовая прибыль в разрезе возвратов товаров

Это именно то, о чем мы говорили выше про различие функционального и маржинального P&L.

В заключение отметим следующее. Конечно бюджетирование сверху вниз имеет право на существование, но все-таки формирование планов и прогнозов на основе маркетингового анализа таких экономических сущностей как

– объем и структура каналов вероятного траффика с учетом конкурентной среды,

– уровни средних чеков или покупательной способности потенциальных клиентов,

– причины отказов от покупок;

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

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

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

ДЗ(1) = 2 * S / ОбДЗ – ДЗ(0),

ДЗ(1) – дебиторская задолженность на конец Периода;

S – бюджет продаж за Период;

ОбДЗ – плановый коэффициент оборачиваемости дебиторской задолженности;

ДЗ(0) – объем дебиторской задолженности на начало периода.

Гипотетически, зная объем продаж за Период, а также объемы дебиторской задолженности на начало и конец Периода, можно рассчитать объем поступлений ДС CF(+) за Период:

CF(+) = ДЗ(0) + S – ДЗ(1).

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

Все вышесказанное аналогично применимо и к кредиторской задолженности. Приведем еще один подобный пример применения “красивых” формул. Речь пойдет о, так называемом, косвенном методе расчета финансового потока (Cash Flow). Обратим внимание на следующее. Если у нас в распоряжении есть баланс на начало Периода:

Активы(0) = ДС(0) + ТЗ(0) + ДЗ(0);

Пассивы(0) = СК(0) + КЗ(0),

где известны все строки

и есть баланс на конец Периода:

Активы(1) = ДС(1) + ТЗ(1) + ДЗ(1);

Пассивы(1) = СК(1) + КЗ(1),

где неизвестными являются только объемы денежных средств ДС(1) и собственного капитала СК(1), поскольку объемы на конец Периода товарных остатков ТЗ(1), а также дебиторской ДЗ(1) и кредиторской задолженностей КЗ(1) мы “рассчитали” через коэффициенты оборачиваемости. Тогда, очевидно, отнимая построчно из баланса на конец Периода баланс на начало Периода должно сохраниться основное балансовое равенство:

D(Активы) = Активы(1) – Активы(0) = Пассивы(1) – Пассивы(0) = D(Пассивы),

где через D обозначаем “дельту” или разность между значениями показателя на конец и начало Периода. Раскладывая это равенство по статьям баланса получаем тождество:

D(ДС) + D(ТЗ) + D(ДЗ) = D(СК) + D(КЗ),

элементарно преобразовав которое, получаем формулу косвенного метода расчета финансового потока Cash Flow (напомним, что финансовый поток Периода это разница между притоком и оттоком ДС за Период, подробнее см. здесь, которая в том числе равна нашей дельте D(ДС)):

D(ДС) = D(СК) + D(КЗ) – D(ТЗ) – D(ДЗ).

Наконец, учитывая тот факт, что в нашем случае изменение собственного капитала равно итогу отчета о прибылях и убытках (функционального отчета P&L) или равно EBITDA:

получаем окончательный вид формулы косвенного расчета финансового потока за Период:

CF = D(ДС) = EBITDA + D(КЗ) – D(ТЗ) – D(ДЗ).

В общем виде формула косвенного метода аналогична приведенной здесь, только дополнительно содержит изменения по остальным строкам баланса и остальные слагаемые из строк полного отчета о прибылях и убытках (P&L), от чего суть не меняется. Кстати, НДС мы не стали учитывать, просто, чтобы не перегружать представление расчетов. На самом деле формула косвенного метода – это обычное балансовое тождество, представленное немного в ином виде, и уж если оно сошлось в финансовой модели, по какому бы принципу эта модель не строилась, т.е. активы сравнялись с пассивами, то при прочих равных условиях некий уровень финансово-экономической корректности в модели уже точно присутствует.

Вот такая арифметика!

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

Дело в том, что изначально разработка универсального финансово-экономического инструментария оценки как текущей деятельности, так и перспектив развития действующих предприятий и новых проектов, требовалась в банковской и инвестиционной сфере, для создания конвейерных процессов принятия решений по кредитованию и инвестированию. Причем требовалось именно на основе внешней финансовой отчетности создать такой инструментарий, поскольку в свою очередь сама по себе финансовая отчетность универсальна и, что безусловно важно, должна по закону формироваться и публично предоставляться. (Банк просто физически не может “заходить” в каждую компанию, которая обращается к нему за кредитом, чтобы проанализировать всю первичку и произвести расчеты с учетом каждой отдельной специфики.) Ну и конечно же многочисленными теоретиками-экономистами в финансовых институтах за пару десятков лет были разработаны соответствующие формулы, часть из которых можно посмотреть у нас на сайте в разделе финансово-экономического анализа, и еще некоторая часть, а именно инвестиционная аналитика, которая изложена у нас в рамках следующей страницы раздела “управление финансами”.

С другой стороны, это вопрос методологии образования и написания методической литературы – для того, чтобы написать книгу по управлению финансами, всегда крайне заманчивой является возможность “сразу объять все”. Ведь изложение материала через финансово-экономические коэффициенты, которые можно рассчитать для любого предприятия одним и тем же способом через баланс и отчет о прибылях и убытках, получается лаконичным и красивым, хотя и далеко от реальной практики.

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

В заключение затронем еще один важный вопрос, а именно расчет покрытия кассовых разрывов. Если посмотреть на отчет о движении денежных средств (Cash Flow) нашей финансовой модели, то видим, что по третьему кварталу по строке «Остаток денежных средств на конец периода» стоит отрицательное значение в размере «минус 12млн.руб.» – это как раз-таки и есть кассовый разрыв или объем нехватки денежных средств для реализации смоделированных планов.

Соответственно сразу выкладываем для скачивания EXCEL-файл с финансовой моделью, рассчитывающей, кредитование кассовых разрывов:

СКАЧАТЬ
/>Финмодель ритейла с покрытием кассовых разрывов />

Эту же финансовую модель, только в незаполненном виде можно скачать здесь:

СКАЧАТЬ
/>Незаполненная финмодель ритейла с покрытием кассовых разрывов />

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

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

Будем предполагать, что у нашей торговой компании потенциально есть возможность заключения с каким-либо банком договора на кредитную линию в форме овердрафта, с достаточным лимитом. Годовую процентную ставку пользователь может задать во вкладке «CF_условия», мы ее задали на уровне 17%. Обычно под овердрафт не требуется залогов, поскольку это оперативный инструмент финансирования нехватки средств для проведения текущих платежей, но конечно же предполагается, что заемщик имеет «хорошее» финансовое состояние, что это значит можно, например, посмотреть здесь.

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

Не вдаваясь сейчас в тонкости решения вопросов о финансировании кассовых разрывов, отметим лишь только то, что такая модель кредитования является наиболее дешевой.

Во вкладке «расчеты_ежедн» финмодели мы реализовали с помощью формул EXCEL все указанные выше условия кредитования кассовых разрывов по модели овердрафта, в результате чего наш отчет о движении денежных средств (Cash Flow) дополнился блоком финансовой деятельности с оборотами по привлечению и возврату кредитных средств, а также оплатами процентов за пользование заемными деньгами. Выглядит теперь наш Cash Flow так:

Отчет ДДС после покрытия кассовых разрывов

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

Также не забываем, что теперь после учета финансовой деятельности ниже показателя EBITDA необходимо добавить начисление процентов по кредитам и в нашем случае в качестве итога отчета P&L мы вместо EBITDA получаем показатель EBT – Earnings Before Tax или прибыль до налога на прибыль, поскольку мы предположили, что в текущих финансовых моделях обойдемся без учета основных средств и амортизации.

Ну а в балансе, чтобы он сошелся, мы добавили в пассивах три статьи:

– задолженность по кредитам и займам;

– задолженность по возврату тела кредитов;

– задолженность по оплате %-тов по кредитам;

и перенастроили формулы по статье «нераспределенная прибыль/убыток» с показателя EBITDA отчета P&L на показатель EBT. Аналогично мы обошлись с «денежными средствами» активов баланса нашей финансовой модели.

Анализ данных с помощью формулы массива

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

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

Показатели рентабельности

Они рассчитываются как отношение чистой прибыли к сумме активов или потокам, за счет которых она была получена.

Для этого используются данные бухгалтерской отчетности (Форма № 1 и № 2).

Чем выше значения, тем эффективнее задействованы анализируемые ресурсы предприятия.

В показателях рентабельности заинтересованы собственники и акционеры.

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

3 способа подсчитать итоги по условию в Excel

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

Рассмотрим небольшой пример. На рисунке ниже Вы видите базу в виде таблицы и итоговый свод, который нужно заполнить данными. В базе есть расходы по датам и по статьям (причем в одну дату может быть несколько строк расходов по любой статье). Задача – свести данные по месяцам и по статьям.

Сегодня мы рассмотрим 3 способа это сделать:

1) Функция СУММЕСЛИМН

2) Функция СУММПРОИЗВ

3) Сводная таблица

Но сначала поделимся с Вами небольшой хитростью. В итоговом своде в ячейках с названиями месяцев на самом деле введен не текст ("Январь", "Февраль" и т.д.), а даты первого дня каждого месяца (01.01.2017, 01.02.2017 и т.д.), а уже после к этим ячейкам применен пользовательский формат "ММММ".

Этот небольшой трюк впоследствии нам очень поможет при подсчете итоговых сумм.

Способ 1. Функция СУММЕСЛИМН

Один из очевидных способов решения задачи – использование специальной функции суммирования по нескольким условиям. Это умеет делать функция СУММЕСЛИМН. Она суммирует значения заданного диапазона только в тех строках/столбцах, в которых выполняются заданные условия.

К сожалению, она умеет воспринимать диапазоны условий только в том виде, в котором они представлены на листе, и не может "на лету" обработать их. Это значит, что если нам нужно свести данные по месяцам, то функция СУММЕСЛИМН требует наличия дополнительной колонки с месяцем. Добавим колонку "Номер месяца", в которой пропишем формулу

и протянем ее вниз на всю высоту таблицы. Получим столбец с порядковым номером месяца (определяется по дате в первом столбце).

Теперь в ячейку для Статьи 1 месяца Январь (в примере это ячейка H3) введем формулу:

$C:$C – столбец "Сумма затрат" (первым указывается тот диапазон, итоги по которому нужно подсчитать).

$D:$D – столбец проверки первого условия ("Номер месяца").

МЕСЯЦ(H$2) – первое условие. Ячейка H2 это "Январь". Так как мы вначале ввели туда 01.01.2017, а потом просто применили числовой формат, мы можем обработать эту ячейку функцией МЕСЯЦ и узнать порядковый номер месяца (и тогда функция сможет сравнить порядковый номер месяца в H2 и порядковые номера в столбце "Номер месяца").

$B:$B – столбец проверки второго условия ("Статья").

$G3 – второе условие. Ячейка с названием статьи затрат, по которой подводим итог.

Обратите внимание на закрепление ссылок. Это сделано для того, чтобы формулу можно было копировать. После ввода формулы, в ячейке H3 будет подсчитан итог по Январю и статье 1. Скопируйте формулу в другие ячейки и получите нужный результат (при копировании в другие кварталы, не забудьте перетянуть ссылку на строку месяцев, как показано на гифке ниже).

Способ 2. Функция СУММПРОИЗВ

Избежать создания доп.столбца (как в первом способе) можно путем применения функции СУММПРОИЗВ. Ее особенность в том, что она может обрабатывать внутри себя массивы данных, но при этом не требует ввода через Ctrl+Shift+Enter, то есть формально не является формулой массива.

В ячейку для Статьи 1 месяца Январь (в примере это ячейка H3) введем формулу:

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

Разберем пошагово, как эта формула работает:

  • Первая часть формулы (МЕСЯЦ($A$2:$A$1000)=МЕСЯЦ($H2)) делает следующее. Она берет диапазон А2:A1000 и к каждой ячейке применяет функцию МЕСЯЦ, то есть из каждой даты получает номер месяца. Затем каждый из полученных номеров сравнивается с номером месяца в ячейке H2 (это наш Январь в итоговом своде). Результат такого сравнения – столбец из значений ИСТИНА (если номера совпали) и ЛОЖЬ (если не совпали). Все эти вычисления происходят внутри формулы и не выносятся на лист. Обратите внимание, что сравнение нужно обязательно заключать в скобки!
  • Вторая часть формулы ($B$2:$B$1000=$G3) делает то же самое, но для колонки Статьи (здесь нам не требуется применение функции МЕСЯЦ, названия статей сравниваются напрямую). Сравнение также берется в скобки и на выходе так же дает столбец значений ИСТИНА и ЛОЖЬ.
  • На третьем этапе столбцы перемножаются между собой. В Excel при умножении логических значений ИСТИНА и ЛОЖЬ на выходе получается ноль или единица.

Полученный столбец нулей и единиц умножается на столбец, сумму по которому нужно свести. В итоге в тех строках, где оба условия совпали, сумма умножится на 1, то есть не изменится. А в тех строках, где хотя бы одно условие неверно, сумма умножится на 0 и станет равна 0.

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

Правила работы с функцией при подобных расчетах:

  • не указывать целые столбцы и строки в качестве аргументов;
  • перемножаемые диапазоны-аргументы должны быть равны (А2:А1000 и B2:B1000, например);
  • все выражения сравнения нужно заключать в скобки.

Способ 3. Сводная таблица

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

Поместите поле "Дата" в область строк, поле "Статьи" в область столбцов, а поле "Сумма затрат" в область значений, как показано на рисунке ниже.

Если Excel сразу не сгруппировал даты помесячно, то кликните на столбце с датами в сводной таблице правой кнопкой мыши и выберите "Группировать" с шагом "Месяцы". Для красоты можете применить к таблице встроенный или собственный стиль. В результате получится примерно такая таблица.

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

  • простое копирование и вставка. Подходит, если сводная не будет обновляться и менять размеры (не будут добавляться новые статьи);
  • использование функции ИНДЕКС, для извлечения данных из сводной. Про эту функцию мы недавно подробно рассказывали . Этот способ подойдет, если сводная будет обновляться, но новые статьи не будут появляться;
  • использование функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Самый интересный способ, который позволяет гибко менять сводную и всё равно подтягивать данные. Разберем его подробнее.

Встаньте в ячейку Января и Статьи 1 итогового свода и попробуйте сослаться на соответствующую ячейку сводной таблицы. Скорее всего, Excel вместо простой ссылки, вроде =А15, вставит огромную формулу

Она то нам и нужна. Если формула не появилась, Вы можете ввести ее вручную или включить в настройках. Кликните на сводной таблице, найдите на ленте вкладку "Анализ", нажмите маленькую стрелочку рядом с кнопкой "Параметры" и поставьте галочку "Создать GetPivotData":

Вернемся к функции. Она имеет 2 обязательных аргумента и дополнительные.

1) Поле сводной таблицы, которое находится в области значений. В нашем случае это поле "Сумма затрат". Можете оставить в аргументе этот текст, а можете сослаться на шапку соответствующего столбца базы, чтобы сократить формулу;

2) Адрес любой ячейки сводной таблицы. Указывается на случай, если на листе их несколько и Excel должен понять, с какой именно работать;

3) Дополнительные аргументы парные. Они состоят из названия поля, по которому задается условие отбора, и самого условия (похоже на функцию СУММЕСЛИМН).

В нашем случае нужно указать поле "Дата", в качестве условия для него – месяц. Так как в сводной месяц представлен в виде текста "янв", "фев" и т.д., нам нужно превратить наш заголовок в такой же текст. Так как мы вводили туда даты (делали это в начале статьи), получить нужное сочетание поможет формула

Вторая пара условий – поле "Статья" и ссылка на название статьи в итоговом своде. В результате для ячейки Января и Статьи 1 получим формулу:

В данном примере сводная начинается в ячейке $M$2. Формулу можно копировать, как и все предыдущие.

Ссылка на основную публикацию
Adblock
detector