CC BY
67
УДК 381.74
ПЛАНИРОВАНИЕ СБЫТА ВЗАИМОДОПОЛНЯЮЩИХ ТОВАРОВ В СРЕДЕ ЭЛЕКТРОННЫХ ТАБЛИЦ
© Александр Валерьевич ТЕН
Тамбовский государственный университет им. Г.Р. Державина, г. Тамбов, Российская Федерация, кандидат экономических наук, e-mail: troveo@mail.ru
В статье рассмотрена компьютерная модель, реализующая расчеты по совместной оптимизации цен и объемов продаж условных комплектов взаимодополняющих товаров. Автором приведены особенности расчетов в трех блоках общей компьютерной модели планирования сбыта взаимодополняющих товаров: «Расчет оптимума продаж»; «План продаж»; «Продажи дополняющих товаров». С помощью данной модели можно быстро оценить: в каком направлении, на сколько следует изменять цену основного товара.
Ключевые слова: основной товар; дополняющий товар; объем продаж; оптимум цен; переменные затраты; компьютерная модель совместной оптимизации цен и объемов продаж.
Для того чтобы рассмотреть планирование сбыта взаимодополняющих товаров в среде электронных таблиц, введем понятие условного комплекта товаров, под которым будем понимать основной товар и некоторую совокупность дополнительных товаров, часто покупаемых совместно с ним [1].
Продажа основного товара с определенной степенью вероятности приводит к продаже дополняющих его товаров. Так, например, приобретая цифровые фотоаппараты, смартфоны, коммуникаторы и другую подобного рода продукцию, многие покупатели одновременно покупают дополнительные карты памяти большой емкости, запасные аккумуляторы, чехлы и т. д. При этом можно говорить только о статистической взаимосвязи покупок данных товаров.
Условный комплект товаров характеризуется следующими параметрами:
1) ценой основного товара и переменными затратами на его производство (приобретение) и реализацию;
2) ценами дополнительных товаров;
3) средним объемом закупки дополнительных товаров при приобретении единицы основного товара.
Продажа каждой единицы основного товара фактически означает продажу некоторого условного комплекта товаров. Поэтому выручку и маржинальную прибыль от продажи основного товара следует рассматривать в совокупности с выручкой и маржинальной прибылью от продажи дополнительных товаров, входящих в условный комплект.
Пусть р - цена основного товара, п -число товаров, входящих в условный комплект, р ^ - цена дополнительного товара у
из условного комплекта, а Ь. - средний объем закупки дополнительного товара у при приобретении единицы основного товара. Тогда общая цена условного комплекта я может быть определена по формуле:
П
э = р + £Ь]Р] . (1)
1=1
Коэффициенты Ь1 являются статистически выявленными параметрами взаимосвязи продаж основного и дополнительных товаров. Они могут быть определены по данным продаж прошлого периода как отношение проданного совместно с основным товаром количества дополнительного товара к числу проданных единиц основного товара.
Если V - переменные затраты на производство (закупку) и реализацию единицы основного товара, а V. - переменные затраты на единицу дополнительного товара у, то маржинальная прибыль от продажи условного комплекта - т - составит:
т = р - v + £Ь1 (р; - v]). (2)
1=1
Пусть к - средневзвешенная маржинальная прибыль, приносимая дополнительными товарами условного комплекта:
И = І Ъ1 (( - V').
(3)
1=1
Если к > 0, то т > р — V. Поэтому при определенных условиях фирма может быть заинтересована в увеличении продаж условных комплектов даже при снижении маржинальной прибыли от продаж основного товара, поскольку такое снижение может быть перекрыто увеличением объемов продаж дополнительных товаров. Рассмотрим задачу оптимизации объема продаж условных комплектов товаров за счет изменения цены основного товара и приносимой им маржинальной прибыли на единицу продаж.
Пусть Х0 - известный объем продаж
основного товара в базовом периоде, р0 -
средняя цена, по которой он продавался, а Е - эластичность спроса на основной товар.
Если р - цена продаж основного товара в плановом периоде, то для любого р0 не равного нулю существует такое и, при котором:
( + и )р0.
(4)
В соответствии с определением коэффициента эластичности спроса, плановый объем продаж основного товара X составит:
Х = (1 - иЕ )Хо .
(5)
Если переменные затраты на основной и дополнительные товары, а также коэффициенты взаимосвязи Ь■ в плановом периоде
останутся неизменными, то маржинальная прибыль от продажи X условных комплектов при цене основного товара, равной р составит:
М = тХ = (р-у + И )Х =
= (р + и )р0 - у + И )(1 - иЕ )Х0
(6)
Выражение (6) может быть преобразовано к виду (7):
М = -р0 ЕХ0 Х2 +
+ роХо - РоЕХо +уЕХо - ИЕХо К
+ ІРоХо - ^Хо +ИХо
Зависимость (7) представляет собой полином второй степени от и. Коэффициент при его старшем члене отрицателен. Поэтому он достигает глобального максимума при:
ро -роЕ + уЕ -ИЕ 2 Ро Е
(8)
Значение ит , определяемое формулой
(8), позволяет по формулам (4) и (5) рассчитать такие цену и объем продаж основного товара, при которых обеспечивается максимальная маржинальная прибыль от продажи определяемых им условных комплектов товаров.
Для выполнения практических расчетов по представленной модели можно использовать электронные таблицы. Общий вид электронной таблицы, реализующей расчеты по модели, представлен на рис. 1.
Компьютерная модель, реализующая расчеты по совместной оптимизации цен и объемов продаж условных комплектов взаимодополняющих товаров включает три блока: «Расчет оптимума продаж»; «План продаж»; «Продажи дополняющих товаров».
В блоке «Продажи дополняющих товаров» пользователем вручную вводятся данные об объемах продаж, выручке и переменным затратам, связанным с продажей дополнительных товаров, включенных в условный комплект. При формировании этих данных необходимо иметь ввиду, что здесь должны фигурировать только данные по продажам дополнительных товаров, приобретенных совместно с продажей основного товара, а не по всем продажам дополнительных товаров. Такого рода данные могут быть получены на основе обработки данных по накладным на отгрузку или товарным чекам. Если из соответствующих документов следует, что дополнительные товары условного комплекта проданы параллельно с основным товаром, то они включаются в расчет. В противном случае данные о продажах дополнительных товаров в расчет не включаются, поскольку данная продажа не инициирована непосредственной продажей основного товара.
и
т
А В С 0 Е Р С н I
1 Эластичность спроса 3
2 Расчет оптимума продаж
3 Показатель Основной товар Дополняющие товары Итого
4 База Оптимум Изм.,% База Оптимум База Оптимум Изм.,%
5 Объем продаж 100 00 138,13 38,13% 100,00 138,13 100,00 138,13 38,13%
6 Выручка 40 000.00 48 228,65 20,57% 8 250,00 11 395,31 48 250,00 59 623,96 23,57%
7 Средняя цена 400,00 349,17 -12,71% 82,50 82,50 482,50 431,67 10,54%
8 Переменные затраты 20 000.00 27 625,00 38,13% 4 750,00 6 560,94 24 750,00 34 185,94 38,13%
9 - на единицу 200,00 200,00 0,00% 47,50 47,50 247,50 247,50 0,00%
10 Маржинальная прибыль 20 000,00 20 603,65 3,02% 3 500,00 4 834,38 23 500,00 25 438,02 8,25%
11 - на единицу 200,00 149,17 -25,42% 35,00 35,00 235,00 184,17 -21,63%
12 - в % к выручке 50,00% 42,72% 42,42% 42,42% 48,70% 42,66%
13 План продаж
14 Показатель Основной товар Дополняющие товары Итого
15 План Изм.,% План Изм.,% План Изм.,%
16 Объем продаж 137,50 37,50% 137,50 37,50% 137,50 37,50%
17 Выручка 48 125,00 20,31% 11 343,75 37,50% 59 468,75 23,25%
13 Средняя цена 350,00 -12,50% 82,50 0,00% 432,50 -10,36%
19 Переменные затраты 27 500,00 37,50% 6 531,25 37,50% 34 031,25 37,50%
20 - на единицу 200,00 0,00% 47,50 0,00% 247,50 0,00%
21 Маржинальная прибыль 20 625,00 3,13% 4 812,50 37,50% 25 437,50 8,24%
гг - на единицу 150,00 25,00% 35,00 0,00% 185,00 21,28%
гг Продажи дополняющих товаров
24 Показатель/Товар Всего 1 2 3 4 5
25 Объем продаж X 50.00 80.00 70.00 300.00 10 00
26 Выручка 8 250,00 250 00 1 600 00 4 200,00 1 200.00 1 000,00
27 - в % к базовому товару 20,63% 0,63% 4,00% 10,50% 3,00% 2,50%
28 Средняя цена 82,50 5,00 20,00 60,00 4,00 100,00
29 - в % к базовому товару 20,63% 1,25% 5,00% 15,00% 1,00% 25,00%
30 Переменные затраты 4 750,00 150.00 1 200.00 2 500.00 600.00 300.00
31 - на единицу 47,50 3,00 15,00 35,71 2,00 30,00
32 Маржинальная прибыль 3 500,00 100,00 400,00 1 700,00 600,00 700,00
33 - на единицу 35,00 2,00 5,00 24,29 2,00 70,00
34 - в % к выручке 42,42% 40,00% 25,00% 40,48% 50,00% 70,00%
35 Коэффициент зависимости X 0,50 0,80 0,70 3,00 0,10
Рис. 1. Общий вид компьютерной модели совместной оптимизации цен и объемов продаж условных комплектов взаимодополняющих товаров1
На основе этих данных рассчитываются три группы однотипных показателей, характеризующих продажи основного и дополнительных товаров, а также итоговые продажи условных комплектов товаров. Базовые данные по основному товару вычисляются на основании значений суммарных объема продаж, выручки и переменных затрат. Соответствующие исходные данные, как уже указывалось выше, должны формироваться вне модели - на основании данных бухгалтерского и оперативного учета. Описание порядка их формирования выходит за рамки данной статьи.
Средняя цена продаж базового периода вычисляется как отношение суммарной выручки к суммарному объему продаж основного товара. Переменные затраты на единицу основного товара рассчитываются как отно-
1 Источник: авторский.
шение суммарных переменных затрат к общему объему продаж. Маржинальная прибыль определяется как разница между выручкой и переменными затратами [2], а маржинальная прибыль на единицу продаж - как разница между средней ценой и переменными затратами на единицу основного товара.
Базой для расчета оптимальных значений показателей продаж основного товара является характеристика изменения цены базового периода, обеспечивающая формирование такой цены, которая обеспечивает максимум маржинальной прибыли от продажи всей совокупности условных комплектов при данной эластичности спроса на основной товар. Она формируется в ячейке Б7 и вычисляется в соответствии с формулой (8). На основе значения изменения базовой цены в ячейке С7 вычисляется оптимальная цена и соответствующий ей объем продаж основного товара. Эти вычисления производятся на
основании формул (4) и (5). Выручка рассчитывается как произведение оптимальной цены на соответствующий ей объем продаж, а переменные затраты - как произведение оптимального объема продаж на уровень переменных затрат на единицу основного товара в базовом периоде. Здесь учитывается тот факт, что переменные затраты на единицу товара предполагаются неизменными при любом объеме продаж.
Расчет показателей продаж дополняющих товаров производится на основании итоговых результатов расчета в блоке «Продажи дополняющих товаров» и, фактически, является сводом данных о продажах всех дополняющих товаров условных комплектов. Поскольку продажи условных комплектов определяются продажами основного товара, объемные показатели продаж совокупности дополняющих товаров приравниваются к объему продаж основного товара - данные о сводных продажах дополняющих товаров в базовом периоде приравниваются к объему продаж основного товара базового периода, а оптимум рассчитывается в привязке к оптимуму объема продаж основного товара. Таким образом, если в базовом периоде было продано 1оо единиц основного товара, то это означает, что всего было продано 1оо условных комплектов.
Данные о средней цене и переменных затратах на единицу условного комплекта копируются из результата расчета этих показателей в блоке «Продажи дополняющих товаров», где они формируются как средневзвешенные величины цен продаж и переменных затрат на единицу продаж дополняющих товаров. Выручка от продажи дополняющих товаров рассчитывается как произведение средней цены комплекта дополняющих товаров на объем продаж условных комплектов, а переменные затраты - как произведение средних переменных затрат на единицу дополняющих товаров условного комплекта на общее число условных комплектов. На основе этих данных по очевидным правилам формируются показатели общей маржинальной прибыли, полученной от продаж дополняющих товаров, маржинальной прибыли на единицу условного дополняющего товара и доли маржинальной прибыли в выручке от дополняющих товаров.
Указанные правила идентичны как для показателей базового периода, так и для оптимальных данных. Относительные отклонения оптимальных значений показателей продаж дополняющих товаров от показателей базового периода в таблице не выводятся, поскольку по относительным показателям они совпадают (средняя цена и переменные затраты на единицу дополняющих товаров условного комплекта предполагаются неизменными; отсюда следует и неизменность относительных показателей, характеризующих маржинальную прибыль), а валовые показатели изменяются в точности в той же пропорции, что и показатели объема продаж основного товара. Это постулируется математической моделью, лежащей в основе расчетов.
Итоговые данные по продажам условных комплектов формируются путем сложения одноименных показателей продаж основного и дополняющих товаров. Исключение составляет лишь объем продаж условных комплектов, который приравнивается к объему продаж основного товара.
В блоке «План продаж» рассчитываются основные показатели, характеризующие план продаж. Этот блок необходим для того, чтобы пользователь мог сформировать реальные плановые показатели, поскольку не факт, что рассчитанные по модели оптимальные величины могут быть использованы без изменений. Так, например, в результате расчета оптимальной величины цены основного товара может быть получено значение «с копейками», что не всегда приемлемо. Оптимальное значение объема продаж также, в общем случае, может получиться не целым значением. Кроме того, по экспертной оценке пользователя оптимальные значения могут оказаться недостижимыми. Вследствие этого, вычисленные по модели оптимальные значения показателей выступают, главным образом, как рекомендации, отталкиваясь от которых пользователь может составить план с учетом не формализованных в модели факторов.
При формировании плана вручную задается только одна величина - цена продаж основного товара (В18). На ее основе в ячейке С18 вычисляется отклонение от цены базового периода, которое далее используется при вычислении планового объема продаж. В
1оо
этом основное отличие порядка расчета плановых показателей от метода их формирования в блоке «Расчет оптимума продаж», где автоматически формируется оптимальное отклонение от цены базового периода и уже на его основании рассчитываются оптимальные цена и объем продаж. Возможность задавать цену вручную позволяет оценить последствия, к которым приведет ее установка на данном уровне и сравнить значения плановых показателей с их оптимальными величинами, рассчитанными по модели.
Все остальные показатели плана вычисляются автоматически по тем же правилам, что и при их расчете в блоке «Расчет оптимума продаж». Изменения формируются как процент отклонения плановых показателей от одноименных показателей базового периода.
В блоке «Продажи дополняющих товаров» сводятся данные по продажам дополняющих товаров и формируются средневзвешенные показатели, характеризующие продажи дополняющих товаров условного комплекта.
По каждому дополняющему товару вводятся данные о совокупных объемах продаж, выручке и переменным затратам. По этим данным рассчитываются все производные показатели продаж данного дополняющего товара. В строке 35 по каждому дополняющему товару рассчитывается коэффициент зависимости его продаж от продаж основного товара - основа для формирования средневзвешенных показателей по всей совокупности дополняющих товаров. Он формируется как отношение объема продаж дополняющего товара к объему продаж основного товара в базовом периоде. Таким образом, коэффициент зависимости характеризует сколько единиц дополнительного товара продано совместно с единицей основного товара.
В блоке ячеек «Всего» формируются общие и средние показатели по продажам дополняющих товаров в базовом периоде. Выручка и переменные затраты выводятся путем суммирования соответствующих данных по дополняющим товарам. Средняя цена и переменные затраты на условную единицу дополняющего товара рассчитываются как средневзвешенное значение цен и переменных затрат по каждому дополняющему това-
ру с коэффициентами зависимости дополняющих товаров от основного.
При необходимости, формулы расчета итоговых показателей могут быть модифицированы для любого числа дополняющих товаров.
Из представленных на рис. 1 данных видно, что в базовом периоде было продано 100 единиц основного товара, выручка от продажи которых составила 40000 (единицы измерения денежных средств не имеют значения - это могут быть рубли, тысячи рублей, доллары, евро и т. д.), а совокупные переменные затраты составили 20000. Соответственно средняя цена продаж основного товара составляла 400, а приносимая его единицей маржинальная прибыль - 200.
Параллельно с основным товаром продавались 5 дополняющих его товаров. Доля выручки, приносимой каждым из них по отношению к выручке от продаж основного товара в большинстве случаев относительно невелика. Исключение составляет лишь товар «3», доля выручки от которого составила 10,5 % от выручки основного товара. Наиболее часто параллельно с продажами основного товара продается товар «4» - в среднем 3 штуки на единицу продаж основного товара. Коэффициенты зависимости по остальным товарам меньше единицы, что свидетельствует о том, что отнюдь не все покупатели склонны приобретать эти дополняющие товары совместно с основным. Так, например, приносящий наибольшую выручку товар «3» в среднем приобретают только 70 % покупателей основного товара.
В совокупности продажи дополняющих товаров совместно с основным приносят довольно значительную выручку - более 20 % от выручки, приносимой основным товаром. Поэтому фирма может быть заинтересована в увеличении объема продаж условных комплектов даже за счет снижения цены на основной товар и приносимой им относительной нормы маржинальной прибыли, рассчитывая на то, что продажи дополняющих товаров перекроют снижение прибыли от продаж основного товара.
Эта возможность подтверждается данными расчета оптимума продаж условных комплектов. В соответствии с ними, для достижения максимума маржинальной прибыли от совокупных продаж основного и допол-
няющих товаров, цена на основной товар при данном значении коэффициента эластичности спроса на него равного 3, должна быть снижена на 12,71 %. В результате, объем продаж основного товара (а соответственно, и условных комплектов товаров) должен вырасти на 38,13 %. При этом, несмотря на то, что удельная маржинальная прибыль от продаж основного товара снизится на 25,42 %, за счет роста выручки на 2о,57 % общая маржинальная прибыль от продаж основного товара вырастет на 3,о2 %.
Одновременно с ростом объема продаж основного товара можно ожидать и роста продаж дополняющих товаров. Если продажи дополняющих товаров в среднем вырастут в той же пропорции, что и объем продаж основного товара, то в совокупности маржинальная прибыль от продажи условных комплектов, образуемых основным товаром вырастет на 8,25 %.
В результате расчета оптимальной цены по модели получено ее значение, равное 349,17. Однако использовать оптимальное значение, указанное с точностью до копеек, может быть нецелесообразно. Поэтому в качестве плановой цены указано округленное значение 35о. В соответствии с ним в блоке «План продаж» зафиксировано изменение цены на 12,5 % и соответствующий ему объ-
ем продаж в 137,5 единиц. Результатом установки такой плановой цены является то, что маржинальная прибыль от продаж основного товара вырастает на 3,13 %, т. е. больше, чем при «строго» оптимальной цене. Однако из-за того, что в данном случае будет продано меньше условных комплектов, выручка от продаж дополняющих товаров окажется меньше, чем в оптимальном варианте и общий прирост маржинальной прибыли от продаж условных комплектов также окажется чуть-чуть меньше, чем при оптимальных значениях цены и объема продаж (8,24 %). Понятно, что при практическом применении данной модели эта разница совершенно несущественна, поскольку расчет оптимума весьма условен и вряд ли может рассматриваться как идеально точный прогноз. В то же время, с помощью модели можно быстро оценить в каком направлении следует изменять цену основного товара и примерно на сколько.
1. URL: http://www.shurem.ru
2. URL: http://www.poschitaem.ru/dict
Поступила в редакцию 12.01.2012 г.
UDC 381.74
PLANNING OF SALES OF COMPLEMENTARY PRODUCTS AMONG SPREADSHEET
Aleksander Valeryevich TEN, Tambov State University named after G.R. Derzhavin, Tambov, Russian Federation, Candidate of Economics, e-mail: troveo@mail.ru
This article describes a computer model that implements the calculations for the joint optimization of prices and sales volumes of conditioned sets of complementary goods. The author shows the features of the calculations in the three blocks of the overall computer model for planning sales of complementary products: “Calculation of optimum sales”, “Sales plan”, “Sales of complementary products”. With this model, you can quickly see in which direction on how much you should change the price of basic goods.
Key words: primary goods; complementary goods; sales; optimum prices, variable costs, a computer model of the joint optimization of prices and sales volumes.
