Оптимизация ассортимента товаров на примере таблицы Точка безубыточности.
Главная  ·  Карта сайта  ·  Демо-версии  ·  Список таблиц  ·  Цены  ·  Отзывы  ·  Контакты
Финансовые экономические расчеты в Excel
Комплект для фин.-эконом. расчетов в Excel
Расчеты, диаграммы, пояснения — на 75 листах
 Платежный календарь
 Себестоимость и доходность. Мультивалютная версия
 Окупаемость инвестиционных проектов
 Финансовый анализ в форматах МСФО или РСБУ
 Точка безубыточности и расчет доходности при большом ассортименте
 Анализ ассортимента
 Подробнее…

Оптимизация ассортимента товаров в таблице
«Точка безубыточности. Анализ и оптимизация доходности ассортимента»

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

Алгоритм действий следующий:

  1. Находим наименее прибыльный товар
  2. «Оптимизируем» его
  3. Сравниваем показатели ДО и ПОСЛЕ
  4. Находим следующий наименее прибыльный товар
  5. «Оптимизируем» его
  6. Сравниваем показатели ДО и ПОСЛЕ
  7. И т.д.

Способы оптимизации могут быть разные:

  1. Ничего не предпринимаем, данный товар привлекает покупателей, а прибыль делается на других товарах.
  2. Уменьшаем долю товара в закупках. Как частный случай — уменьшаем до нуля, т.е. убираем товар из ассортимента.
  3. Увеличиваем наценку.

Вот как это можно сделать в таблице «Точка безубыточности. Анализ и оптимизация доходности ассортимента».

Пусть имеется следующий ассортимент товаров. Голубоватым фоном отмечены ячейки для ввода. Часть столбцов скрыта.

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

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

Шаг 1.

Записываем показатели, имеющиеся до оптимизации: Отчет о прибылях и убытках, показатели рентабельности, точку безубыточности и др. Нажимаем кнопку «Сохранить показатели».

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



Шаг 2.

Сортируем список товаров по столбцу «% Марж. прибыли в выручке»

Видим, что наименее прибыльная группа «Розетки/Выкл.», уменьшим ее сумму закупки с 100 тыс. до 50 тыс.

Освободившиеся 50 тыс. перераспределим на закупку телевизоров.

Сумма по столбцу «Стоимость в закупочных СЗП» равная 0, показывает что распределены все освободившиеся суммы.

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

Чтобы нам не приходилось подгонять сумму закупки под целое число единиц товара, таблица делает округление вниз и вверх, предлагая нам соответстующие суммы закупки. Так, в нашем случае, таблица предлагает 48000 (это 6 телевизоров) или 56000 (это 7 телевизоров).

Выбираем 48000, оставшиеся 2000 перенаправляем на электроинструмент.

Что получается в результате:

Выручка увеличилась на 110 тыс.

Теперь посмотрим как увеличить наценку.

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

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

 

Теперь самое интересное — результат оптимизации. Сравнение ОПУ до оптимизации и после. Двигайте ползунки во фрейме (окошке) ниже.

 

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

Новый вариант таблицы с описанными дополнениями вышел 19 декабря 2019 г. Пока вариант только для Windows.

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

Описание таблицы «Точка безубыточности. Анализ и оптимизация доходности ассортимента»

Купить

Избавьтесь от утомительных
расчетов с помощью
этих Excel-таблиц >>>

Комплект со скидкой 30%

Финансовые расчеты в Excel Шесть таблиц:
  • 1. Платежный календарь
  • 2. Себестоимость
  • 3. Расчет инвестиционных проектов
  • 4. Финансовый анализ
  • 5. Точка безубыточности
  • 6. Анализ ассортимента
  • Подробнее…

Таблицы отдельно

Платежный календарь. График и прогноз платежей и поступлений в Excel Платежный календарь
  • Платежные Календари на месяц, 3 месяца и год
  • Деб.и Кред. задолженность.
  • Отсрочка, просроченные, с наступающим сроком
  • Контроль оплаты
  • Расчет ожидаемого остатка
  • Кассовый разрыв Подробнее…
Расчет себестоимости и доходности в Excel Расчет себестоимости
  • Себестоимость
  • Доходность
  • Маржинальный анализ
  • Расходы в 10 валютах одновременно Подробнее…
Оценка экономической эффективности инвестиций в Excel Расчет инвестиционных проектов
  • Дисконтир. потоки
  • WACC, NPV, IRR, ROI, PI
  • Срок окупаемости
  • Устойчивость проекта
  • Расчет и Сравнение семи проектов Подробнее…
Финансовый анализ МСФО в Excel Финансовый анализ МСФО
  • Вертикальный и горизонтальный Анализ баланса и P&L
  • 36 коэффициентов
  • Динамика за 5 периодов
  • Риск банкротства
  • ДДС прямым и косвенным методом
  • Отчет об источниках и использовании денежных средств Подробнее…
Финансовый анализ РСБУ в Excel Финансовый анализ РСБУ
  • Вертикальный и горизонтальный Анализ баланса и ОПУ
  • 70 коэффициентов
  • Динамика за 8 периодов
  • Риск банкротства Подробнее…
Расчет Точки безубыточности в Excel Точка безубыточности
Анализ доходности
  • Расчет доходности при большом ассортименте
  • Минимальная наценка
  • Маржинальный анализ
  • Графики точки безубыт., фин. прочности, оп. рычага Подробнее…
Анализ ассортимента в Excel Анализ ассортимента
  • ABC-анализ
  • Разбиение на группы по объему
  • Сравнительный анализ двух доп. показателей
  • 17 графиков Подробнее…
Планирование личного времени и управление задачами в Excel Личное Планирование
  • Цели
  • Матрица Эйзенхауэра
  • Метод Стивена Кови
  • Рутинные задачи
  • Неделя и Очередь задач
  • Исполнители и Контексты Подробнее…
Ведение семейного бюджета в Excel Учет доходов и расходов
  • На одном листе
  • Просто и наглядно
  • Учет
  • Планирование
  • Выборка
    Подробнее…

Посмотрите полный список таблиц >>>

Как получить демо-версии


ВКонтакте: vk.com/vvprokhorov

Разработка Excel-таблиц
экономической и управленческой
тематики. Условия тут >>>

Опубликовано 13.12.1018, исправлено 04.03.2019    0017