Моделирование выручки от реализации при оценке инвестиционного проекта с помощью электронных таблиц

Андрей Владимирович Голубев
канд. экон. наук, ведущий менеджер образовательных программ,
Балтийский федеральный университет им. И. Канта, Калининград.
Вестник Балтийского федерального университета им. И. Канта.
Серия: Гуманитарные и общественные науки
№1. С.102—109 2016

Моделирование давно стало одним из важнейших подходов при планировании социально-экономических процессов. Его важность, пожалуй, только возрастает в последнее время, о чем пишут многие авторы [4, с. 40—48]. Это легко объяснимо в ситуации неопределенности конъюнктуры, что также широко обсуждается в научной периодике [2, с. 9 — 16]. Особенно важным представляется такой фактор, как спрос на товары и услуги, который формирует выручку от реализации. Этому фактору вполне справедливо уделяется все большее внимание [3, с. 77—83]. Именно поэтому моделирование продаж при оценке инвестиционного проекта представляется важным и актуальным вопросом.

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

Также имеет смысл отделять исходные условия от расчетных таблиц и размещать результаты расчетов ближе к исходным условиям. На листе Microsoft Excel это может выглядеть следующим образом (табл. 1).

Таблица 1. Расположение исходных данных, расчетов и их результатов на листе электронной таблицы

Результаты расчетов	Расчет
Исходные данные

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

В качестве основных параметров можно выделить следующие:

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

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

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

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

В том случае, если ассортиментный ряд состоит из 5 — 10 позиций, их легко отслеживать, применять к ним методы оценки рентабельности и величины затрат по отдельности. Можно выделять наиболее перспективные позиции и те, которые, возможно, следует заменить. В той ситуации, когда ассортиментный ряд состоит из нескольких десятков, сотен или тысяч наименований, включение их в модель по отдельности может привести к путанице и перегрузке лица, принимающего решение. Решить эту проблему можно условным разделением ассортиментного ряда на несколько групп. Довольно действенным будет применение метода АВС-XYZ, деление на близкие по сорту (хлебобулочные изделия, молочная продукция, соки-воды и пр.), способу продаж (оптовые и розничные продажи) или целевому сегменту (частные и корпоративные клиенты) товары. Основной целью будет создание ассортиментного ряда, который легко отслеживать, а также легко понимать влияние каждого из товаров на совокупные продажи.

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

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

Стартовые и плановые объемы продаж в натуральном выражении. Стартовые и плановые объемы продаж соответствуют периоду начала продаж и периоду выхода продаж на плановый объем. Необходимость включения этих параметров в модель очевидна: чаще всего продажи начинаются с малых объемов, преодолевая неизвестность продукта или услуги и недоверие к ним основного числа потребителей. Если же стартовый объем и плановый объем будут равны (продажи сразу начнутся с больших объемов), модель будет позволять установить их одинаковыми. Тем не менее остается вопрос о характере роста продаж от стартовой величины к плановой. На практике нельзя предсказать этот характер с достаточной точностью. Можно лишь предположить, что он будет равномерным или неравномерным. Неравномерность нарастания продаж спрогнозировать довольно сложно. Поэтому на практике можно рекомендовать моделирование равномерного нарастания продаж. Хотя, если все же неравномерный характер нарастания продаж можно как-то обрисовать, к ним следует применять более сложные алгоритмы.

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

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

При моделировании объемов продаж в натуральном выражении без учета сезонности предварительно можно рассчитать объем роста реализации в натуральном выражении за один период в течение времени выхода продаж на плановый объем. Если предполагается равномерный рост продаж от момента их начала до выхода на плановый объем, в первую очередь следует узнать то количество периодов, в течение которых будет происходить эта динамика. Если началом продаж будет период номер 5, а выход на плановый объем ожидается в 11-м месяце, то количество периодов роста будет равно 6.

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

Т_рост = Т_п - Т_н (1)

где Т_н — период начала продаж;
Т_п — период выхода на плановый объем.

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

Q_рост = Q_п - Q_н (2)

где Q_н — стартовый объем продаж;
Q_п — плановый объем продаж.

Формулы 1 и 2 позволяют сделать расчет роста объемов продаж за один период. Для этого объем роста следует разделить на количество периодов роста (120 / 6 = 20). Формула роста за период будет выглядеть так:

Q_период = Q_рост / Т_рост (3)

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

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

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

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

Таблица 2. Расположение исходных данных на листе Microsoft Excel

A	B	C	D	E	F	G	H	I
Продукт	(Тн)	(Qн)	(Тп)	(Qп)	Конец продаж	(Трост)	(Qрост)	(Qпериод)
1 продукт	5	10	11	130	20	6	120	20
2 продукт	6	20	12	260	21	6	240	40

Если временная шкала на листе электронных таблиц будет начинаться со столбца N и продолжаться далее вправо, если ячейке M1 будет соответствовать нулевой период, а ячейки M2 и M3 будут отведены для расчета объемов продаж соответственно первого и второго продукта в нулевом периоде, то для корректного отражения объемов продаж можно рекомендовать следующую формулу в ячейке M2:

= ЕСЛИ (М$1<$С3;0;ЕСЛИ(М$1>=$С3;0;ЕСЛИ(М$1=$С3;$Б3; ЕСЛИ = $E3;$F3;L2+$J3)))) (4)

Формула может быть скопирована на строки, ратоложенные ниже и на столбцы, находящиеся правее.

В этом случае результат на листе Microsoft Excel будет выглядеть как в таблице 3.

Таблица 3. Расчет объемов продаж

L	M	Q	R	S	T	U	V	W	X	Y	Z	...	AF	AG	AH
Период	0	4	5	6	7	8	9	10	11	12	13	...	19	20	21
1 продукт	0	0	10	30	50	70	90	110	130	130	130	...	130	0	0
2 продукт	0	0	0	20	60	100	140	180	220	260	260	...	260	260	0

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

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

• От какого значения будут отклоняться продажи — максимального, минимального или среднего.
• Будут ли сезонные отклонения от плановых значений индивидуальны для каждого продукта (услуги), или все они будут подвержены одинаковым сезонным колебаниям.

Важность ответа на первый вопрос обусловлена удобством моделирования. Если плановые объемы обозначены как максимальные, то заданные сезонные колебания могут их только снизить. Такого рода подход удобен тогда, когда сезонность приводит полному прекращению продаж в определенные месяцы. Например, продажа услуг по сдаче в аренду прогулочных лодок в северных широтах может характеризоваться сезонностью: 100 % от максимального объема с ноября по март, 50 % в апреле и октябре и 0 % в остальные месяцы.

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

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

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

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

Общий алгоритм расчета объемов продаж с учетом сезонных колебаний выглядит так:

1. Построение исходных данных по сезонным колебаниям.
2. Построение сезонных колебаний в расчетных таблицах.
3. Умножение величин объемов продаж без учета сезонных колебаний на единицу плюс сезонный фактор, соответствующий данному периоду.

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

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

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

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

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

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

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

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

Список литературы

1. Баев Л.А., Литке М.Т. К вопросу о формировании системы критериев оценки и управления инновационным потенциалом проекта // Менеджмент в России и за рубежом. 2014. № 5. С. 18—24.

2. Зимина Т.В. Формирование стратегии развития рынка в условиях нестабильности // Маркетинг в России и за рубежом. 2015. № 6. С. 9 — 16.

3. Лукьянова Н.Ю. Моделирование потребительского спроса на продукты питания в Калининградской области // Вестник Балтийского федерального университета им. И. Канта. 2015. Вып. 3. С. 77 — 83.

4. Подгорный В.В. Моделирование — главный принцип структурирования управления социальным развитием общества // Менеджмент в России и за рубежом. 2015. № 2. С. 40—48.