Анализ логистики и управления цепочками поставок в Excel

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

Пример бизнес-идеи для анализа управления логистикой в Excel

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

Описание бизнес-идеи как исходные данные к презентации

В Министерстве Сельского Хозяйства США разработали новую технологию для производства искусственных дров из травы. Согласно технологическому составу рецепта в сырьевую смесь должны добавляться щепки древесины до 40% по отношению к траве 60%. В процессе прессования предварительно подготовленного сырья: правильно высушенной измельченной травы и щепок из древесины под давлением выделяется древесный клей, который служит натуральным связующим веществом. В результате на выходе пресса экструдера получается брикет экологичных искусственных дров из травы.

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

Два базовых компонента для прибыльного производства био-дров:

1. Основной компонент – сено 60%. Повсюду много скошенной травы. Ее тысячами тон по всем штатам США ежегодно собирают с газонов приусадебных участков и школ, парков. После чего закапывают в грунт для гумуса.

2. Второй компонент – древесная щепка 40%. Его можно получать из быстрорастущего тополя Quaking Aspen. Этот осинообразный тополь является самым популярным деревом на всей территории Северной Америки. Высота Quaking Aspen достигает до 35 м, а скорость роста около 2,7 метра за год. Если потребуются большие объемы древесной щепы, то Quaking Aspen можно быстро выращивать в частных питомниках. Древесина из дерева Quaking Aspen непригодна для мебели, досок и не подходит даже на дрова. Но идеально подходит как связующий компонент для переработки сухой травы в энергетический продукт с повышенным спросом у потребителей.

Интересный факт! Quaking Aspen – признано самым старым растением, которое сохранилось до сегодняшних дней на нашей планете. Самая большая колониальная структура корня этого дерева называется лесом Пандо, он расположен в штате Юта (США). Этот лес состоит из огромной рощи деревьев Quaking Aspen и покрывает площадь 43 гектара. Но что особенно интересно, лес Пандо состоит только из одного вида дерева Quaking Aspen и обладает единым корнем. На сегодняшний день Пандо является самым большим живым организмом на нашей планете с весом 6 000 тонн.

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

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

Особенно популярные искусственные дрова с добавками ароматов: мяты, лаванды и эвкалипта.

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

Интересный факт! В период 2030-2040 года наступает цикл снижения солнечной активности в нашей галактике. Такое бывает раз на 400 лет. В этот цикл нас ждет мини ледниковый период. Средняя температура на планете снизится.

До 2030-го года важно успеть наладить логистику доставки дров во всем мире.

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

ТЗ для разработки визуализации данных логистики в Excel

Все аспекты вышеописанной бизнес-идеи включаются в техническое задание для разработчика визуализации данных в Excel.

Топ-3 перевозимых грузов:

1. Закупка сырья – сено скошенной травы с газонов и бревна тополя Quaking Aspen.
2. Главный реализуемый продукт – искусственные дрова для каминов с аромамаслами и натуральными добавками.
3. Дополнительный товар для расширения ассортимента с целью увеличения продаж – пеллеты для заправки котлов длительного автоматного горения с функцией автоматической подачи твердого топлива.

Доставку грузов осуществляют три водителя. По 5-ти маршрутам. У первых двоих водителей по 2 маршрута и третьего только 1.

Основные производственные мощности расположены в городе Нью-Йорк. Поэтому доставка производимой продукции по США будет реализована в направлении из Нью-Йорка. А поставки сырья в обратном направлении – соответственно. Фирма на этапе развития. Ее сеть реализации товаров налажена только по 8-ми городам в разных штатах США:

1. Нью-Йорк – штат Нью-Йорк.
2. Нэшвилл – штат Теннесси.
3. Чикаго – штат Иллинойс.
4. Даллас – штат Техас.
5. Денвер – штат Колорадо.
6. Лос-Анджелес – штат Калифорния.
7. Сан-Франциско – штат Калифорния.
8. Сиэтл – Вашингтон.

У каждого маршрута свое расстояние в милях, что влияет на различные логистические факторы. Все параметры дашборда заполняются исходными значениями на листе «Input Value»:

1. Расход топлива автомобиля в галлонах на 100 миль.
2. Амортизация за каждое пройденные 100 миль на грузовике.
3. Зарплата для каждого водителя.
4. Лимиты командировочных расходов на 100 миль.
5. План KPI – только при превышении выполнения плана будут выплачены соответственные премиальные бонусы.
6. Размер бонусных вознаграждений за перевыполнение плана KPI отдельно по каждому водителю.
7. Стоимость одной упаковки груза.
8. Вес упаковки грузов в фунтах.
9. Стоимость 1-го галлона топлива.
10. Годовой бюджет выделенный на топливо для заправки грузовиков.
11. Расстояние продолжительности каждого маршрута в милях.

Можно изменять только окрашенные ячейки в синий и зеленый цвет.

Внимание! После изменения значений параметров на листе «Input Value» не забудьте обновить сводные таблицы Excel. Для этого перейдите на лист «Control» и поместите курсор Excel в ячейку любой сводной таблицы. Затем выберите опцию: PivotTable Analyze → Data → Refresh → Refresh All.

Исходные данные статистики функционирования логистики заполняются на листе «Data» в соответственных столбцах без формул:

Структура дашборда для оценки эффективности логистики в Excel

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

Слева находится панель управления дашбордом которая состоит из 4-х блоков кнопок:

Первые две кнопки предназначены для переключения между двумя режимами:

1. Визуальный анализ общей картины эффективности логистики на дашборде.
2. Презентация показателей маршрутов транспортировки грузов по городам США.

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

Далее идет блок из 5-ти кнопок для сегментирования показателей по маршрутам.

На четвертом блоке панели управления расположены кнопки выборки данных по месяцам. Структура кнопок в формате ¾ чтобы удобно было выбирать как по месяцам, так и по кварталам.

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

При выборе одного маршрута или целой группы, схема логистической цепочки на карте США изменяется соответственно.

Ниже расположены 3 блока первого плана с итоговыми ключевыми значениями:

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

Ниже под блоками находиться Bar Chart суммарной стоимости перевозимых грузов по месяцам:

Даннвй Bar Chart умеет экспонировать выбранные учетные периоды на панели управления дашбордом. Так подсвечены показатели за второй квартал.

С правой стороны блок графиков и показателей расходов:

Второстепенные логистические расходы на оплату:

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

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

График также умеет экспонировать выбранные периоды с помощью маркеров. На картинке показано как выглядит выбранный период – первое полугодие.

Слева находятся сразу 3 блока показателей пробега:

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

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

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

Ниже представлен Pie Chart разделен на сегменты категорий оценки пройденной дистанции:

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

• долгий маршрут;
• средний;
• короткий.

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

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

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

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

Из материалов сырья были произведены дрова и пеллеты и все они перевозились по нашей логистической цепочке в процессах закупки и реализации на существенно покрытой территории США.

Общая картина дашборда для визуального анализа выглядит так:

Скачать анализ логистики и управления цепочками поставок в Excel

В следующей статье мы разберем второй экран дашборда для анализа управления маршрутами. Там вас ждет более интересное решение в Map Chart для презентации схем маршрутов через ключевые города США и многое другое:

Как сделать Map Chart для управления логистикой в Excel

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

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