Статьи по рубрикам

Полезные инструменты Excel для управления ценообразованием

«Планово-экономический отдел» №5 2018 / Руководство к действию

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

ОЦЕНИВАЕМ РЕНТАБЕЛЬНОСТЬ

Таблицы данных (Данные → Анализ «что если» → Таблица данных) позволяют одновременно просматривать результаты ввода нескольких возможных значений.

ПРИМЕР 1

В компании «Альфа» себестоимость Продукции А за отчетный период составила 25 400 руб. При цене реализации в 32 000 руб. рентабельность — 26 % (табл. 1). Это благоприятная ситуация. В компании утверждена плановая рентабельность в 10 %.

Таблица 1. Фактические показатели Продукции А
Показатель	Значение
Цена реализации, руб.	32 000
Себестоимость продукции, руб.	25 400
Плановая рентабельность, %	10,00
Фактическая рентабельность, %	25,98
Отклонение, %	15,98

В связи с нестабильностью цен на закупаемое сырье и материалы себестоимость продукции колеблется в интервале от 18 до 40 тыс. руб. Нужно оценить рентабельность Продукции А при утвержденной цене реализации в 32 тыс. руб.

Создаем табл. 2. Значения первой строки табл. 2 равны показателям табл. 1 (рис. 1):

себестоимость продукции (F3=C4) — 25 400 руб.;
фактическая рентабельность (G3=C6) — 25,98 %;
отклонение от плановой рентабельности (H3=C7) — 15,98 %.

Далее заполняем столбец «Себестоимость продукции» результатами калькуляций при разной закупочной цене материалов.

Для автоматического заполнения оставшихся полей табл. 2 выделяем диапазон F3:H19 (таблица без шапки). Нажимаем Данные → Анализ «что если» → Таблица данных. В появившемся поле «Подставлять значения по строке в» вводим С4 (фактическая себестоимость, табл. 1) (рис. 2).

Таблица 2. Рентабельность Продукции А
Вариант 1. Цена реализации — 32 000 руб.			Вариант 2. Цена реализации — 29 000 руб.
Себестоимость продукции, руб.	Фактическая рентабельность, %	Отклонение, %	Себестоимость продукции, руб.	Фактическая рентабельность, %	Отклонение, %
25 400	25,98	15,98	25 400	14,17	4,17
18 000	77,78	67,78	18 000	61,11	51,11
19 300	65,80	55,80	19 300	50,26	40,26
21 500	48,84	38,84	21 500	34,88	24,88
22 900	39,74	29,74	22 900	26,64	16,64
24 000	33,33	23,33	24 000	20,83	10,83
25 400	25,98	15,98	25 400	14,17	4,17
26 300	21,67	11,67	26 300	10,27	0,27
27 800	15,11	5,11	27 800	4,32	–5,68
28 900	10,73	0,73	28 900	0,35	–9,65
31 000	3,23	–6,77	31 000	–6,45	–16,45
32 000	0,00	–10,00	32 000	–9,38	–19,38
33 000	–3,03	–13,03	33 000	–12,12	–22,12
34 000	–5,88	–15,88	34 000	–14,71	–24,71
35 000	–8,57	–18,57	35 000	–17,14	–27,14
36 000	–11,11	–21,11	36 000	–19,44	–29,44
40 000	–20,00	–30,00	40 000	–27,50	–37,50

Таблица 2 (вариант 1) заполнилась правильно. Для перепроверки ввели фактическую себестоимость в поле значений. Excel рассчитал аналогичную рентабельность — 26 %.

Согласно табл. 2 плановый уровень рентабельности Продукции А (10 %) при цене реализации 32 тыс. руб. будет достигнут при себестоимости до 28 900 руб.

Вывод: допустимым является увеличение себестоимости продукции из-за роста цен на сырье до 28 900 руб. При неблагоприятных факторах, если компания закупит сырье по завышенным ценам и себестоимость достигнет 40 тыс. руб., убыточность компании составит 20 %. От закупки такого сырья следует отказаться.

Разработанным расчетным шаблоном можно воспользоваться и в следующем случае. Руководство компании рассматривает вариант снижения цены реализации с 32 до 29 тыс. руб. (табл. 2, вариант 2). Тогда фактическая рентабельность при себестоимости 25,4 тыс. руб. составит 14,17 %. Для достижения плановой прибыли себестоимость не должна превышать 26 300 руб. Коммерческий директор может просмотреть все возможные варианты. Таблицы данных (Данные → Анализ «что если» → Таблица данных) можно использовать, когда:

есть одна переменная, на которую ссылаются несколько формул (пример 1, табл. 2);
имеются две переменные, на которые ссылается одна формула (пример 2, табл. 4).

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

Рассмотрим Таблицу данных с двумя переменными.

Е. С. Панченко, бизнес-консультант

Материал публикуется частично. Полностью его можно прочитать в журнале «Планово-экономический отдел» № 5, 2018.

Скачать ознакомительный номер

Новости Подписаться

Открыть все новости