Логистика закупок. Складская и транспортная логистика

Содержание ЛОГИСТИКА ЗАКУПОК 3 Наивный прогноз 4 Прогнозирование по средним значениям 4 Прогнозирование по скользящему среднему значению 5 Метод взвешенной скользящей средней 5 Регрессионный анализ 9 СКЛАДСКАЯ ЛОГИСТИКА 15 Определение месторасположения склада 15 Типовые задачи (примеры и задания для самостоятельного решения) 18 Производительность машин 26 ТРАНСПОРТНАЯ ЛОГИСТИКА 30 Транспортная задача 30 Выбор подвижного состава для перевозки продукции 39 Определение оптимального срока замены транспортного средства 43 Решение задач с помощью MS Excel 46 ОБОРАЧИВАЕМОСТЬ ТОВАРНО-МАТЕРИАЛЬНЫХ ЗАПАСОВ 46 ИЗЛИШКИ ТОВАРНЫХ ЗАПАСОВ И УСЛОВНОЕ ФОРМАТИРОВАНИЕ 55 ПЕРЕНОС ДАННЫХ ИЗ НЕСКОЛЬКИХ ТАБЛИЦ-ИСТОЧНИКОВ В ТАБЛИЦУ-ЦЕЛЬ 58 ИНДЕКС СЕЗОННОСТИ И ПРОГНОЗИРОВАНИЕ СБЫТА 61 КРАТКОСРОЧНОЕ ПРОГНОЗИРОВАНИЕ В MICROSOFT EXCEL 64 ОЦЕНКА ПОТРЕБНОСТИ В ЗАПАСАХ ТОВАРОВ НЕРЕГУЛЯРНОГО СПРОСА 68 ВЫЯВЛЕНИЕ КОМПЛЕМЕНТАРНЫХ ТОВАРОВ И ТОВАРОВ СУБСТИТУТОВ 70 Управление запасами с применением анализа АВС и XYZ 73 Решение транспортной задачи в Microsoft Excel с помощью Поиска решения 88 ЛОГИСТИКА ЗАКУПОК Логистика закупок — это функциональный блок логистики, свя­занный с управлением материальными потоками в процессе обеспече­ния предприятия всеми необходимыми ресурсами. Любое предприятие, как производственное, так и торговое, имеет службу, осуществляющую закупку, доставку и временное хранение сырья, полуфабрикатов и то­варов народного потребления. Появление термина «закупочная логистика» не случайно. За ру­бежом сфера деятельности по обеспечению компании (фирмы-произ­водителя или торговой компании) необходимыми видами материаль­ных ресурсов и готовой продукции традиционно называется purchasing (procurement) — закупки (управление закупками, снабжение). Эта же область производственной деятельности в отечественной практике до сих пор называется материально-техническим снабжением (обеспече­нием), на предприятиях оптовой торговли — товароснабжением. Од­нако в последние годы эту область стали определять как закупочную логистику, поскольку изменился характер материально-технического обеспечения предприятий: от жестко централизованного, фондируе­мого снабжения к свободной оптовой торговле ресурсами. Основными задачами, решаемыми закупочной логистикой, явля­ются: • определение предмета (структуры) закупок; • определение потребности в материальных ресурсах; • выбор поставщика; • определение объема закупок; • определение условий закупок; • подготовка и размещение заказа на покупку; • контроль выполнения заказа; • получение и проверка грузов; • обработка счета и оплата; • учет поступления материальных ресурсов. 1. ОПРЕДЕЛЕНИЕ ПОТРЕБНОСТИ В МАТЕРИАЛЬНЫХ РЕСУРСАХ Не вызывает сомнения тот факт, что прежде чем осуществить за­купку материальных ресурсов, необходимо определить потребность в последних на будущее. Определение потребности в материальных ресурсах основано на прогнозировании их расхода за прошедшие периоды времени. Суще­ствует порядка трехсот методов прогнозирования, которые можно раз­делить на три основные группы: 1) детерминированные; 2) стохастические (вероятностные); 3) эвристические. Прогнозирование обычно основано на использовании статисти­ческого материала (исторических данных) о расходовании материаль­ных ресурсов за определенный промежуток времени. Считается, что при наличии статистических данных за три периода можно сделать прогноз на полтора периода вперед. Следует помнить, что материальные ресурсы могут характеризо­ваться как относительно равномерным спросом, так и иметь резко вы­раженные сезонные «сгущения» или «разряжения» (например, потреб­ность в новогодних елках). Относительно равномерный спрос характерен для регулярно по­требляемых материальных ресурсов, не имеющих сезонных колебаний. Если потребность в материальных ресурсах характеризуется относи­тельно равномерными рядами отгрузок, можно использовать следу­ющие методы прогнозирования: • наивный прогноз; • группу методов прогнозирования по среднему значению (про­стой средней, скользящей средней, взвешенной скользящей средней); • метод регрессионного анализа. Наивный прогноз Наивный прогноз является самой простой методикой прогнози­рования. Он основывается на предположении о том, на сколько про­гнозируемое потребление будет соответствовать потреблению в пред­шествующем периоде. Достоинством наивного прогноза является отсутствие необходи­мости в накоплении статистических данных. Недостаток — вероятная низкая точность прогноза. Однако при прогнозировании спроса, на­пример, на хлеб или молочные продукты, он вполне оправдан. Так, специалист по закупкам должен быть осведомлен о датах начала-окон­чания православных постов и иных факторах, оказывающих влияние на изменение потребности в этих продуктах у населения. Прогнозирование по средним значениям В случае если временной ряд имеет интервал наблюдений в один месяц, повысить точность наивного прогноза позволяет метод прогно­зирования по простой средней величине потребления с учетом количе­ства рабочих дней в месяце. Среднее дневное потребление Рдн рассчитывается по формуле Рдн = Рмес : ДРмес, (1) где Рмес — объем потребления материального ресурса за предыдущий месяц, ед.; ДРмес – дни работы в предыдущем месяце. Прогноз потребления на следующий месяц рассчитывается по формуле Рj = Рдн : ДРмесj, (2) где Pj — прогнозируемый объем потребления материального ресурса в j-м месяце, ед.; ДРмесj – дни работы в j-м месяце. Прогнозирование по скользящему среднему значению Еще один метод прогнозирования по средним значениям — это прогноз на основе скользящего среднего значения потребления матери­ального ресурса. При составлении прогноза этим методом используется значение средней арифметической величины потребления за последние перио­ды наблюдений. Скользящая средняя дневного потребления рассчи­тывается по формуле (3) где — прогнозируемый объем дневного потребления материального ресурса в j-м периоде времени, ед.; i — индекс предыдущего периода времени; — объем дневного потребления в i-м предыдущем периоде времени; n — количество периодов, используемых в расчете скользящей средней. Прогноз потребления на месяц рассчитывается по формуле (4) где — прогнозируемый объем потребления материального ресурса в j-м месяце, ед.; - дни работы в j-м месяце. Для составления прогноза на основе скользящего среднего значения потребления материального ресурса требуется определить количество периодов наблюдений, которые будут использоваться при расчете. При этом необходимо учитывать особенности имеющегося временного ряда. Чем большее количество точек наблюдений берется в расчет, тем скользящая средняя менее чувствительна к изменениям значений по­требления в прошлые периоды. Преимущество метода прогнозирования по скользящей средней зак­лючается в его простоте. Основным же недостатком является то, что зна­чимость потребления в прошлые периоды при прогнозировании будущей потребности одинакова, что редко встречается на практике. Метод взвешенной скользящей средней Для учета важности отдельных периодов наблюдений использу­ют метод взвешенной скользящей средней. В этом методе каждому используемому в расчете скользящей сред­ней периоду присваивается коэффициент, отражающий значимость влияния этого периода на прогнозное значение потребления. Значи­мость более поздних периодов должна быть выше, чем значимость бо­лее ранних периодов. Например, из шести периодов расчета скользя­щей средней последнему может быть присвоен удельный вес 5, предыдущему — 4, далее 3; 2 и 1. В общем виде взвешенная скользя­щая средняя рассчитывается следующим образом: (5) где - прогнозируемый объем потребления материального ресурса в j-м перио­де времени, ед.; i - индекс предыдущего периода времени; - коэффициент значимости i-го периода; - объем потребления в i-м предыдущем периоде времени; n - количество периодов, используемых в расчете взвешенной скользящей средней. Пример 1. Прогнозирование потребности в материальных ресурсах. Исходные данные для определения потребности в материальных ресур­сах (MP), результаты наивного прогноза и результаты расчетов прогно­зирования на основе среднего потребления приведены в табл. 1. Прогнозирование по скользящей средней. В рассматриваемом примере ко­лебания потребления MP в течение первой половины года не длятся более двух месяцев. Во второй половине года имеются более длительные тенденции (до четырех месяцев в конце года). Игнорируя пока характер сезонных колебаний и тенденции рассматриваемого примера, выберем в качестве интервала расчета скользящей средней два месяца. Результат расчета прогноза по скользящей средней с учетом количества рабочих дней в месяце приведен в табл. 2. Таблица 1 Исходные данные для определения потребности в MP и результаты наивного прогноза и по средним значениям Месяц Факти­ческие отгрузки за месяц Наивный прогноз Коли­чество рабочих дней Среднее потреб­ление в день Прогноз по средним значениям потребления в день в месяц Январь 17 244 - 16 1 078 - - Февраль 57 187 17 244 20 2 860 1 078 21 560 Март 48 504 57 187 21 2 310 2 860 60 060 Апрель 58 647 48 504 21 2 793 2 310 48 510 Май 45 477 58 647 20 2 274 2 793 55 860 Июнь 23 833 45 477 22 1 084 2 274 50 028 Июль 21 730 23 833 20 1 087 1 084 21 680 Август 65 289 21 730 23 2 839 1 087 25 001 Сентябрь 46 663 65 289 22 2 122 2 839 62 458 Октябрь 45 344 46 663 21 2 160 2 122 44 562 Ноябрь 31 497 45 344 21 1 500 2 160 45 360 Декабрь 13 714 31 497 21 654 1 500 31 500 Прогнозирование по взвешенной скользящей средней. Для данных в табл. 2 выберем коэффициенты значимости прошлых периодов при прогнозировании потребности будущего периода. Для последнего пери­ода коэффициент значимости принимается равным 5, для предпослед­него — 1. Расчет взвешенной скользящей средней приведен в табл. 2. Для расчета прогноза среднедневного потребления MP (см. столбец 7 табл. 2), например в марте, требуется статистика фактических средне­дневных отгрузок (столбец 4 табл. 2) за январь и февраль: Округление потребления производится в большую сторону для га­рантии обеспечения материальным ресурсом. Для получения прогноза месячной потребности в MP в марте (столбец 8 табл. 2) необходимо прогноз среднесуточной потребности в мар­те умножить на количество рабочих дней в этом месяце. Таблица 2 Расчет прогнозного значения потребления MP по скользящей средней и по взвешенной скользящей средней Месяц Факти­ческие отгрузки за месяц Коли­чество рабочих дней Среднее потреб­ление в день Прогноз по скользящей средней Прогноз по взве­шенной скользящей средней в день в месяц в день в месяц 1 2 3 4 5 6 7 8 Январь 17 244 16 1 078 - - - - Февраль 57 187 20 2 860 - - - - Март 48 504 21 2 310 1 969 41 349 2 563 53 823 Апрель 58 647 21 2 793 2 585 54 285 2 402 50 442 Май 45 477 20 2 274 2 552 51 040 2 713 54 260 Июнь 23 833 22 1084 2 534 55 748 2 361 51942 Июль 21 730 20 1087 1 679 33 580 1283 25 660 Август 65 289 23 2 839 1 086 24 978 1087 25 001 Сентябрь 46 663 22 2 122 1 963 43 186 2 547 56 034 Октябрь 45 344 21 2 160 2 481 52 101 2 242 47 082 Ноябрь 31497 21 1500 2 141 44 961 2 154 45 234 Декабрь 13 714 21 654 1 830 38 430 1 610 33 810 Рис. 1. Результаты наивного прогноза потребности в MP Рис. 2. Результаты прогноза на основе среднего потребления Рис. 3. Результаты прогноза по скользящей средней Рис. 4. Результаты прогноза по взвешенной скользящей средней Как видно из примера, если потребление характеризуется колеба­ниями (сезонными или обусловленными какими-либо иными тенден­циями), то ни один из видов прогноза не является в достаточной сте­пени достоверным (рис. 1—4). В этом случае целесообразно провести научное исследование по­требления MP и, в частности, воспользоваться методикой регрессион­ного анализа. Регрессионный анализ Под регрессионным анализом понимают группу методов, позволяющих с достаточной степенью точности выявлять закономерности изменения случайных величин, устанавливать вид этих зависимостей. В данном случае под случайной величиной понимается расходование материальных ресурсов (фактические отгрузки), которое зависит от периодов потребления (года, месяца, дня и т.д.). Для прогнозирования материалопотока и товарооборота склада необходимо подобрать наиболее подходящее из известных математических уравнений функций (прямую, гиперболу, параболу и т. д.). Эти уравнения определяются на основании графиков, которые строятся по отчетным данным (динамическим рядам). Рассмотрим эти уравнения. Уравнение прямой имеет следующий вид: ух = а + b* х, (6) где ух — результативный признак; х — период времени; а и b — параметры прямой. Нахождение параметров а и b производится на основе выравни­вания по способу наименьших квадратов, которые приводят к сис­теме двух линейных уравнений с двумя неизвестными: (7) Решая это уравнение, находим: ; (7a) В целях облегчения нахождения параметров а и b систему мож­но упростить. Для этого отсчет времени следует вести так, чтобы сумма показателей времени ряда была равна нулю. Такая ус­ловность вполне допустима ввиду того, что начало выбирается произвольно. Чтобы равнялась нулю, в рядах с нечетным числом членов центральный член принимается за нуль, а члены, идущие от цент­ра (в столбце) вверх, получают номера от -1, -2, -3 — со знаком минус, а вниз — +1, +2, +3 — со знаком плюс. Например, ряд со­ставляет 7 членов . Если число членов ряда четное (на­пример, 6), рекомендуется занумеровать члены верхней половины ряда (от середины) числами -1, -3, -5 и т. д., члены нижней поло­вины (от середины) — +1, +3, +5 и т. д. В обоих случаях . Если члены динамического ряда получили такую нумерацию, что их сумма оказывается равной нулю, то система уравнений при­нимает вид Отсюда ; (8) Из приведенных формул видно, что для нахождения парамет­ров уравнения прямой необходимо знать величины , , . Если уровни динамического ряда обнаруживают тенденцию ро­ста по геометрической прогрессии, т. е. прирастают на одинаковое число процентов, выравнивание такого ряда следует проводить по показательной кривой: ух = abx. В этом уравнении х — рассматри­ваемый период, а — начальный уровень ряда (при х = 0), b — темп роста за единицу времени. Техника выравнивания по показательной кривой аналогична технике выравнивания по прямой. Кроме выравнивания и прогнозирования по прямой и пока­зательной кривой, на практике часто используются и другие функции. Например, уравнение параболы второго порядка: yx = a + bx + cx2, (9) где а, b, с — параметры, которые находятся из системы нормальных уравнений. Сама система уравнений, получаемая по способу наименьших квадратов, следующая: (10) Обозначим время таким образом, что . В этом случае нулю будет равно и (как всякая сумма нечетных степеней х). В силу сказанного система нормальных уравнений примет вид: . (11) Во втором уравнении: ; (12) ; . (13) В расчетах также динамический ряд может быть описан урав­нением гиперболы: yx = a + b / x. (14) Для гиперболической зависимости способ наименьших квадра­тов дает такую систему нормальных уравнений: . (15) Решая это уравнение способом определителей, находим: ; (16) Пример 2. За период с 2004 по 2010 г. известен динамический ряд товарооборота регионального склада (табл. 3). Сделайте прогноз товарооборота на 2013 г. Таблица 3 Товарооборот за период 2004—2010 гг. 2004 2005 2006 2007 2008 2009 2010 130 148 170 190 210 225 250 Рис. 5. Динамика изменения товарооборота за период 2004—2010 гг.: Решение. По данным табл. 3 строим график (рис. 5) динамики изменения товарооборота за период 2004—2010 гг. Из этого графи­ка видна тенденция изменения товарооборота. Она идет по прямой линии. Поэтому связь между указанными признаками может быть описана уравнением: ух = а + b* х, где ух — товарооборот регионального склада в условных единицах; х — рассматриваемый период; а и b — параметры (табл. 4). Найденные значения подставим в формулы (8), получим пара­метры а и b: ; . Уравнение нашей прямой будет: ух = 189 + 19,8х. Таблица 4 Расчет параметров уравнения прямой для прогнозирования товарооборота в 2013 г. Годы Товарооборот у, у .е. x х2 xy ух = 189 + 19,8x 1 2 3 4 5 6 2004 130 -3 9 -390 129,6 2005 148 -2 4 -296 149,4 2006 170 -1 1 -170 169,2 2007 190 189 2008 210 + 1 1 +210 208,8 2009 225 +2 4 +450 228,6 2010 250 +3 9 +750 248,4 1323 28 554 1324 2011 +4 268,2 2012 +5 288,0 2013 +6 307,8 Подсчитаем теоретические уровни ряда для каждого года (гр. 6, табл. 4). Сопоставленные графы 2 и 6 по каждому году показывают весь­ма незначительные отклонения расчетных уровней от фактичес­ких, что подтверждает правильность выбора математического уравнения. Для прогнозирования товарооборота необходимо продолжить графу 3 (рассматриваемый период) числами, следующими за ука­занным числом. В нашем случае это 3, далее рассматриваемый пе­риод будет 4, 5, 6 и т. д. На 2013 г. х = 6, тогда у2013 = 189 + 19,8 * 6 = 307,8. Пример 3. За период 2005—2010 гг. известен динамический ряд объема перевозок грузов с регионального склада (табл. 5). Сделайте прогноз перевозок в 2013 г. Исчислим параметры а, b, с по данным табл. 6 и формулам (12), (13). Таблица 5 Объем перевозок за период 2005—2010 гг. (тыс. т) 2005 2006 2007 2008 2009 2010 5398 5718 6132 6885 7647 8518 Рис. 6. Динамика изменения объема перевозок за период 2005—2010 гг.: 1 — фактические данные; 2 — расчетные данные Таблица 6 Расчет параметров уравнения параболы для выравнивания и прогнозирования объема перевозок с регионального склада Годы Объем перевозок (тыс. т) x х2 х4 хy х2y ух= 6500,3 +316,3х + +18,51x2 1 2 3 4 5 6 7 8 2005 5398 -5 25 625 -26990 134 950 5382 2006 5718 -3 9 81 -17154 51 462 5718 2007 6132 -1 1 1 -6132 6 132 6202 2008 6885 +1 1 1 6885 6 885 6835 2009 7647 +3 9 81 22 941 68 823 7616 2010 8518 +5 25 625 42 590 212 950 8545 40 298 70 1414 22 140 481 202 40 298 2011 +7 8611, 39 2012 +9 10 846,31 2013 + 11 12 219,31 Отсюда: а = (40 298 * 1,414 - 481 202 * 70)/(6 * 1414 - 702)  6500,3; b = 22 140/70  316,3; с = (6 * 481 202 – 70*40*40 298)/(6 * 1414 - 702)  18,51. Таким образом, уравнение параболы в нашем примере имеет вид: ух = 6500,3 + 316,3х + 18,51х2. Подставив в эту формулу конкретные значения х, находим зна­чения ух для всех членов динамического ряда (гр. 8, табл. 6). Сопоставленные графы 2 и 8 показывают незначительные от­клонения теоретических уровней от эмпирических, что свидетель­ствует о правильности выбора уравнения кривой. В 2013 г. объем перевозки грузов с регионального склада со­ставит: у2013 = 6500,3 + 316,3 * 11 + 18,51 * 11 = 12 219,31. Задачи для самостоятельного решения Задача 1. Сделайте прогноз товарооборота на 2011 г. (данные в табл. 7). Таблица 7 Исходные данные для расчета 2004 2005 2006 2007 2008 2009 2010 30 210 380 616 846 1000 1248 Задача 2. В таблице 8 приведены статистические данные расходования материального ресурса за последние 6 месяцев. Необходимо сделать прогноз расходования МР в 9 месяце. Таблица 8 Исходные данные для расчета Номер месяца (х) 1 2 3 4 5 6 Расход МР (у) 3000 3800 4400 4700 5000 5200 СКЛАДСКАЯ ЛОГИСТИКА Определение месторасположения склада Для решения одной из фундаментальных логистических задач — определения месторасположения распределительного склада в регионе — необходимо знать: • месторасположение (координаты хi, yi) фирм — производителей и потребителей данной продукции (клиентов); • объемы поставок продукции (Qi); • маршруты доставки (характеристику транспортной сети); • затраты (или тарифы) на транспортные услуги (Тi). В зависимости от выбранного критерия оптимизаций и учета расстояний между поставщиками, потребителями и складом можно выделить несколько типовых случаев. При этом основное внимание уделяется способу учета расстояния между объектами и количеству необходимых складов (табл. 1). Таблица 1 Систематизация методов определения месторасположения складов в распределительных логистических системах Вариант определения координат склада Количество складов Способ учета расстояний между объектами Описание метода 1. Месторас­положение склада выбирается на территории одного из объектов распредели-тельной сети Один Кратчайшее расстояние: где хi, yi - координаты поставщика, потребителя; хc, yc - координаты склада Минимизация, транспортной работы: На основе комбинаторики перебираются возможные варианты расположения координат склада «Манхэттенское расстояние»: Минимизация транспортной работы: Расстояния рассчитываются для конкретных объектов с координатами С(хi; yi) Несколько складов, количество которых известно; оптимальное количество складов Оба способа учета расстояния между объектами Минимизация грузооборота (суммарных логистических затрат) на основе транспортно производственной задачи 2. Расположение склада определяется с учетом координат размещения объектов складской распредели-тельной системы Один «Манхэттенское расстояние» 3. Месторасположение склада определяется с учетом выбранного критерия оптимизации (физического или экономичес-кого) Один Расстояние до объекта определяется от начала координат по оси X и Y Метод центра тяжести: Центр тяжести по тарифу: Кратчайшее расстояние Метод центра тяжести по расстоянию: Центр тяжести по грузобороту: Несколько складов, количество которых задано; оптимальное количество складов Кратчайшее расстояние. «Манхэттенcкое расстояние» Метод СПбГИЭУ* на основе транспортной задачи; прикладные пакеты программ * Метод СПбГИЭУ разработан на кафедре логистики и организации перевозок Санкт-Петербургского государственного инженерно-экономического университета. Задача определения места расположения распредели­тельного центра на обслуживаемой территории может фор­мулироваться как поиск оптимального решения или же как поиск субоптимального (близкого к оптимальному) реше­ния. Наукой и практикой выработаны разнообразные мето­ды решения задач обоих видов. Задача выбора оптимального места расположения ре­шается полным перебором и оценкой всех возможных вариан­тов размещения распределительных центров и выполняется на ЭВМ методами математического программирования. Одна­ко на практике в условиях разветвленных транспортных сетей метод может оказаться неприменим, так как число воз­можных вариантов по мере увеличения масштабов сети, а с ними и трудоемкость решения, растут по экспоненте. Гораздо менее трудоемки субоптимальные методы определения места размещения распределительных центров. Эти методы эффективны для решения больших практических задач. Они не обеспечивают отыскания оптимального решения, однако дают хорошие, близкие к оптимальным результаты при невысокой сложности вычислений. Типовые задачи (примеры и задания для самостоятельного решения) Задача 1. Методом определения центра тяжести грузопотоков найти ориентировочное место для расположения склада, снабжающего магазины. На территории района (рис.1) имеется 8 магазинов, торгующих продовольственными товарами. Рисунок 1. Карта района обслуживания Условные обозначения: - расстояние между обслуживаемыми магазинами — потребителями материального потока, км; 620 — № магазина и его грузооборот (например, магазин № 6, грузооборот — 20 т/мес); — автомобильные дороги. Методические указания В таблице 2 приведены координаты обслуживаемых магазинов (в прямоугольной системе координат), а также их месячный грузооборот. Таблица 2. Грузооборот и координаты обслуживаемых магазинов № магазина Координата X Координата У Грузооборот, т/мес. 1 10 10 15 2 23 41 10 3 48 59 20 4 36 27 5 5 60 34 10 6 67 20 20 7 81 29 45 8 106 45 30 Пользуясь приведенными в теоретических пояснениях к заданию формулами, необходимо найти координаты точ­ки (Хсклад, Усклад), в окрестностях которой рекомендуется организовать работу распределительного склада, а также указать эту точку на чертеже. Прежде чем приступить к расчетам, необходимо вы­полнить чертеж к заданию. Для этого, желательно на мил­лиметровой бумаге, следует нанести координатные оси, а затем точки, в которых размещены магазины. Рекомендуе­мый масштаб: одно миллиметровое деление — один кило­метр (рис.2.). Рисунок 2. Определение места расположения склада методом поиска центра тяжести грузовых потоков (рядом с номером магазина указан его месячый грузооборот) На рисунке рядом с номером магазина указан ме­сячный грузооборот. Координаты центра тяжести грузовых потоков (Хсклад,Ускяад), т.е. точки, в окрестностях которой может быть разме­щен распределительный склад, определяются по формулам: где Гi — грузооборот i-го потребителя; Хi, Уi — координаты i-го потребителя; n — число потребителей. Приведем в качестве примера расчет абсциссы центра тяжести грузовых потоков (Хсклад): Ординату центра тяжести грузовых потоков предлага­ется рассчитать самостоятельно. Точка территории, обеспечивающая минимум транспорт­ной работы по доставке, в общем случае не совпадает с найденным центром тяжести, но, как правило, находится где-то недалеко. Подобрать приемлемое место для склада позволит последующий анализ возможных мест размеще­ния в окрестностях найденного центра тяжести (в рамках данной работы не проводится). При этом необходимо оце­нить транспортную доступность местности, размер и кон­фигурацию возможного участка, а также планы местных властей в отношении намеченной территории. Применение описанного метода имеет ограничение. На модели расстояние от пункта потребления материального потока до места размещения распределительного центра учитывается по прямой. В связи с этим моделируемый рай­он должен иметь развитую сеть дорог, так как в противном случае будет нарушен основной принцип моделирования — принцип подобия модели и моделируемого объекта. Задача 2. Определить оптимальное месторасположение распределительного склада методом центра тяжести по тарифу Три молочных комбината — Царицынский молочный комбинат, Лианозовский молочный комбинат, Завод детских молочных про­дуктов — вместе обслуживают 5 магазинов, расположенных в Мос­ковской области. В табл. 3. приведены координаты молочных ком­бинатов и обслуживаемых магазинов. Комбинаты осуществляют среднюю партию поставки соответственно в размерах 250 т; 275 т; 185 т. Партии поставок при реализации клиентам соответственно равны: КА — 160 т; КВ — 110 т; КС — 170 т; KD — 150; КЕ — 120. Необходимо определить месторасположение распределитель­ного центра, который может обеспечить сбыт продукции молочных заводов среди потребителей, если известно, что тариф для постав­щиков на перевозку продукции составляет 1 руб./т.км, а тарифы для клиентов на перевозку продукции равны: для КА — 0,8 руб./т-км; КВ — 0,5 руб./т-км; КС — 0,6 руб./т-км; KD — 0,7 руб./т-км; КЕ — 0,5 руб./т.км. Таблица 3. Координаты расположения поставщиков и потребителей продукции Коорди­ната Поставщик Клиент Царицынский молочный комбинат Лианозовский молочный комбинат Завод детских молочных продуктов КА КВ КС KD КЕ X 20 50 70 55 15 35 40 46 Y 50 35 20 60 40 70 50 50 Решение рассматриваемой задачи основывает­ся на нахождении центра тяжести, который определяется по фор­муле где M — центр массы, км; Rni — расстояние от начала осей координат до точки, обозначающей месторасположение поставщика, км; Rki — расстояние от начала координат до точки, обозначающей мес­торасположение потребителя,км; Tni — транспортный тариф для поставщика на перевозку груза, руб./ткм; Тki — транспортный тариф для клиента на перевозку груза, руб./ткм; Qni — объем груза, поставляемый i-м поставщиком, т; Qki — объем груза, закупаемый i-м клиентом, т. Пример решения задачи 1. Рассчитаем координаты центра тяжести грузовых потоков по оси X: 2. Рассчитаем координаты центра тяжести грузовых потоков по оси У: Таким образом, оптимальное месторасположение склада нахо­дится в точке (43 км; 44 км). Задача 3. Для самостоятельного решения Торгово-посредническая компания «ТV» имеет 3 склада, которые обслуживаются 4 поставщиками. В табл. 4 приведены координаты месторасположения складов компании «ТV» и их поставщиков. Поставщики осуществляют среднюю партию поставки в разме­рах: ПA — 75 т; ПB — 45 т; ПC -50 т; ПD — 30. Объемы заказов по складам равны: СA — 70 т; СB — 50 т; СC — 80 т. Для минимизации транспортных расходов компания «ТV» при­няла решение о строительстве распределительного центра в райо­не сбыта продукции. Необходимо определить место расположения распределительного центра, если известно, что тариф для постав­щиков на перевозку продукции составляет ПA — 1,25 руб./ткм; ПB — 1 руб./ткм; ПC -1,75 руб./ткм; ПD — 1 руб/ткм, а тарифы для складов на перевозку продукции равны: для КA — 0,5 руб./ткм; КB — 0,6 руб./ткм; КC — 0,7 руб./ткм. Таблица 4. Координаты расположения поставщиков и складов торгово-посреднической компании «ТV» продукции Координата Клиент Поставщик СA СB СC ПA ПB ПC ПD X 20 50 70 55 15 35 10 Y 5 35 20 60 40 70 20 Задача 4. Определение формы собственности склада В связи с решением об увеличении объемов продаж перед тор­говой компанией «РИМ» встала проблема, что существующая собственная складская система не сможет поддерживать увеличе­ние объема продаж. Вследствие этого компания вынуждена вы­брать одну из двух альтернатив: приобрести склад в собственность или пользоваться услугами склада общего пользования. Таблица 5. Исходные данные задачи Суммарная величина грузопотока, проходящего через склад 7000 т/год Условно-постоянные затраты собственного склада 750 000 у.е./год Удельная стоимость грузопереработки на собственном складе 3,5 у.е./т Средняя цена закупки партии товара 4000 у.е./т Средняя торговая надбавка при оптовой продаже товаров 8% Коэффициент для расчета оплаты процентов за кредит 0,045 Тариф на услуги арендуемого склада 6 у.е./м2 Потребная площадь арендуемого склада 1211 м2 Пример решения задачи 1. Определим точку безубыточности деятельности склада, т. е. минимальный объем работы, ниже которого работа собственного склада компании «РИМ» становится убыточной при действующей системе расценок. Точка безубыточности (Тбу) — уровень грузооборота на складе, при котором доход от работы склада совпадает с общими издержками. Точка безубыточности определяется по формуле где Спост — условно-постоянные затраты, размер которых напрямую не связан с грузооборотом (Q) на складе (амортизация, заработная пла­та персонала, страховые выплаты, проценты по долгам, затраты на рекламу, затраты на содержание помещений и прочее); Д — прибыль на единицу груза от работы склада, рассчитывается по формуле где N — средняя торговая надбавка при оптовой продаже товаров; К — средняя цена закупки партии товара; Спер — переменные затраты, величина которых изменяется с измене­нием объема грузооборота складываются из процентов за кредит, взятого для оплаты партии товаров (Скр) и стоимости грузопереработки (Сгр): Спер = Скр + Сгр. Размер процентов за кредит на единицу груза определяется по формуле Скр = k * R, где k — коэффициент пропорциональности, зависящий от стоимости партии товаров и банковского процента. Стоимость грузопереработки определяется объемом работ на складе и удельной стоимостью выполнения этих работ (Суд). Следовательно, в развернутом виде формулу точки безубыточ­ности можно представить как Таким образом, Иными словами, склад может работать безубыточно при грузо­обороте 5495 т/год, в то время как расчетный грузооборот компа­нии составляет 7000 т/год. Таким образом, компания «РИМ» может рассматривать вариант строительства собственного склада. 2. Рассчитаем суммарные затраты при условии использования собственного склада компанией «РИМ». Зависимость затрат на грузопереработку на собственном скла­де от объема грузооборота рассчитывается исходя из того, что удельная стоимость грузопереработки на собственном складе со­ставит приблизительно 3,5 у.е. за 1 т/год. Тогда Ссс =3,5 * Т + Спост и Ссс = 3,5 * 70 000 + 750 000 = 774 500 у.е. 3. Рассчитаем суммарные затраты при условии использования услуг склада общего пользования. Затраты на хранение товаров на складе общего пользования определяются по следующей формуле: где — тариф на услуги арендуемого склада; — потребная площадь арендуемого склада, в нашем случае 1211 м2; 365 — число дней хранения на наемном складе за год. Сас = 6 * 1211 * 365 = 2 652 090 у.е. Таким образом, получаем, что при грузообороте склада 7000 т/год целесообразно иметь собственный склад, так как Ссс< Сас. Задача 5. Для самостоятельного решения В связи с решением об увеличении объема продаж перед торго­вой компанией встал вопрос: приобрести склад в собственность или пользоваться услугами склада общего пользования. На основе данных, приведенных в табл. 2, выберите и обоснуйте одну из альтернатив. Таблица 6. Исходные данные задачи Суммарная величина грузопотока, проходящего через, склад 9000 т/год Условно-постоянные затраты собственного склада 600 000 у.е./т Удельная стоимость грузопереработки на собственном складе 3 у.е./т Средняя цена закупки партии товара 2500 у.е./т Средняя торговая надбавка при оптовой продаже товаров 8% Коэффициент для расчета оплаты процентов за кредит 0,045 Тариф на услуги арендуемого склада 5,5 у.е./м2 Потребная площадь арендуемого склада 300 м2 Задача 6. Найти коэффициент неравномерности загрузки склада в отчет­ном и предыдущем году, а также темпы его изменения, используя сле­дующие данные: Показатели Месяцы года 1 2 3 4 5 6 7 8 9 10 11 12 Количество груза поступившего на склад, т 50 70 80 85 95 110 105 115 125 120 105 100 Решение: 1. Грузооборот склада - показатель, характеризующий мощность склада: где Г - грузооборот склада; Q - количество груза, поступившего на склад (или реализованного со склада) за период, т; T - продолжительность периода времени. 2. Коэффициент неравномерности загрузки склада (Кн): где Гмакс - грузооборот самого напряженного месяца; Гср - среднемесячный грузооборот склада. Гср = (50 + 70 + 80 + 85 + 95 +110+105+115+125+120+105+100) / 12 = 96,7т. Кн = 125 т/96,7 т =1,29. Задача 7. Для самостоятельного решения Найти коэффициент неравномерности загрузки склада в отчет­ном и предыдущем году, используя сле­дующие данные: Количество груза поступившего на склад, т Месяцы года 1 2 3 4 5 6 7 8 9 10 11 12 Отчетный год 200 205 220 225 215 205 210 195 185 180 170 150 Предыдущий год 145 160 175 190 190 205 210 215 220 210 205 200 Задача 8. Компания "Модус продукт", занимающаяся реализацией продук­тов питания, решила приобрести склад для расширения рынка сбыта на юго-востоке Москвы. Она предполагает, что годовой грузооборот склада должен составить 16 тыс. т при среднем сроке хранения груза 25 дней. Оп­ределить необходимую емкость склада. Решение: Вместимость (емкость) склада определяется по формуле: где Е — емкость склада, т; Тхр - средний срок хранения груза, дн.; t - число поступлений грузов в год; Q - годовой грузооборот, т. Е=16000*(365/25)=1067 Задача 9. Найти показатель "готовность к поставке" и общие затраты фир­мы на логистику в стоимостном выражении. Если известно, что фактиче­ский объем поставок продукции в год составил 1440 т при 16 поставках, что характеризует товарооборот склада фирмы за год, при этом планиро­валось - 14 поставок, фактический объем поставляемой партии составил 75 % от запланированного. Затраты фирмы на логистику составили 2,5 млн. руб. Стоимость 1 т груза - 40000 руб. Решение: 1. Готовность к поставке. Показатель используется как для оценки собст­венного уровня сервиса, так и для оценки уровня сервиса поставщика: где Гп - готовность к поставке; m - фактический объем поставок в количественном выражении; М - запланированный объем в количественном выражении. М = (1440/16)/75% * 100% * 14= 1680 т Гп = 1440/1680 * 100% = 85,7% 2. Затраты на логистику, приходящиеся на единицу товарооборота: где Дл - затраты на логистику, приходящиеся на единицу товарооборота; Сл - затраты на логистику за период; О - товарооборот за период. О = 1440 * 40000 = 57,6 млн. руб. Дл = 2,5 млн. руб./ 57,6 млн. руб. * 100 % = 4,34 % Задача 10. Для самостоятельного решения Найти показатель готовность к поставке и общие затраты фирмы на логистику в стоимостном выражении. Известно, что фактический объем поставок продукции в год составил 1680 т при 14 поставках, что характе­ризует товарооборот склада фирмы за год, при этом планировалось 12 поставок, фактический объем поставляемой партии составил 80 % от запла­нированного. Затраты фирмы на логистику составили 3 % от стоимости поставляемой партии. Стоимость 1 т груза - 35000 руб. Производительность машин Важнейший показатель погрузочно-разгрузочной машины - ее производительность (техническая и эксплуатационная). Под технической производительностью (Пт) понимают ко­личество груза (в тоннах, кубометрах, штуках или других еди­ницах), которое может переработать данная машина или уста­новка за 1 ч непрерывной работы в условиях максимальной загрузки и наиболее рациональной организации труда. • Техническая производительность машин циклического действия Техническая производительность погрузочно-разгрузочных машин циклического (периодического) действия Пт, т/ч, (козловые, мостовые, стреловые краны, авто-электропогруз­чики и др.): для штучных грузов: где Тц - продолжительность рабочего цикла машины, с; Qн - масса груза, перемещаемого рабочим органом машины за один цикл (номинальная грузоподъемность), т; Рабочим циклом (Тц ) погрузочно-разгрузочных машин на­зывают время, затрачиваемое на захват и перемещение одной порции груза, и возврат машины в исходное положение. Наибо­лее точно Тц можно определить хронометражным наблюдени­ем. В общем случае продолжительность одного цикла где время отдельных операций по застропке, подъему, перемещению, развороту, отстропке или высыпанию груза; коэффициент, учитывающий совмещение отдельных операций цикла Для вилочных авто- электропогрузчиков продолжитель­ность одного рабочего цикла Тц = j(t1 +t2 +t3 +t4 +t5 +t6 +t7 +t8 +t9 +t10 + t11) где j=0,85 - коэффициент, учитывающий совмещение опера­ций рейса во времени; t1 - время наклона рамы грузоподъемника вперед, заводки вил под груз, подъема груза на вилах и наклона рамы назад до от­каза (для средних условий работы можно принять t = 10—15 с); t2 - время разворота погрузчика (при развороте на 90° t2 = 6-8 с, а на 180° t2 = 10-15 с); t3 - продолжительность перемещения погрузчика с гру­зом; t4 - время установки рамы грузоподъемника в вертикаль­ное положение с грузом на вилах (от 2 до 3 с); t5 - время подъема груза на необходимую высоту; t6 - время укладки груза в штабель (5-8 с); t7 - время отклонения рамы грузоподъемника назад без груза (2-3 с); t8 - время опускания порожней каретки; t9 - время разворота погрузчика без груза (равно t2); t10 - время на обратный (холостой) ход погрузчика; t11 - суммарное время для переключения рычагов и сраба­тывания исполнительных цилиндров после включения (от 6 до 8 с). Время передвижения погрузчика с грузом или без него равно где L - путь передвижения погрузчика, м (выбирается из компоновки склада); Vп - скорость передвижения погрузчика, м/с; tрз - время на разгон и замедление (можно принять от 1 до 1,5 с). Продолжительность подъема или опускания груза опреде­лится по формуле где Н — высота подъема (опускания) груза, м (выбирается из компоновки склада); Vгр — скорость подъема (опускания) груза, м/с. Скорость подъема каретки без груза на 30% больше, чем с гру­зом, а скорость опускания с грузом на 50-70% больше подъема, а без груза — на 50% меньше, чем с грузом. Если автопогрузчик оборудован блочной или безблочной стрелой, время его цикла под­считывают тоже по формуле (3), однако время застропки груза t1 принимают равным 8-12 с, а время укладки груза в кузов автомо­биля или на железнодорожную платформу t6 = 5-8 с. • Эксплуатационная (сменная) производительность (Пэ ) Характеризует количество конкретно перегружаемого груза в течение одного часа (рабочей смены) при правильной органи­зации труда, передовые его методы и на определенном месте работы. Эксплуатационная производительность погрузочно-разгрузочной машины, т/ч (т/смену): Пэ =Пт*Кв*Кгр , т/ч; Пэ =Пт* Кв*Кгр*Тсм , т/смену, где Кв - коэффициент использования машины во времени в течение 1 ч (смены); Кгр - коэффициент использования машины по грузоподъ­емности (отношение массы груза, перемещаемой в среднем за один рабочий цикл, к номинальной грузоподъемности); Тсм - число рабочих часов в смене. По эксплуатационной производительности машин опреде­ляют плановые производственные задания на час, сутки, месяц, квартал и рассчитывают потребный парк машин для фронтов погрузки-выгрузки. Техническая характеристика универсальных электропогрузчиков Показатели ЭП-0601 ЭП- 0801 ЭП- 1201 ЭП-103 ЭП-202 ЭП-501 FB10-7 (Япония) FB15-7 (Япония) FB35-7 (Япония) FTB18-7 (Япония) Номер варианта 1 2 3 4 5 6 7 8 9 10 Грузоподъемность, т 0,63 0,8 1,25 1.0 2,0 5,0 1,0 1,5 3,5 1,75 Высота подъема груза, м 3,0 3,0 3,0 2,8 2,0 4,5 3,0 3,0 3,0 3,0 Скорость передвижения, км/ч: • с грузом 9,0 9,0 9,0 9,0 10,0 6,0 14,0 14,0 13,5 14,5 • без груза 10,0 10,0 10,0 10,0 12,0 7,0 16,0 16,0 15,0 16,0 Скорость подъема вил, м/с: • с грузом 0,20 0,16 0,16 0,15 0,17 0,10 0,39 0,35 0,24 0,40 • без груза 0,24 0,24 0,24 0,21 0,23 0,16 0,58 0,58 0,41 0,61 Скорость опускания вил, м/с: • с грузом 0,37 0,37 0,37 0,33 0,33 0,40 0,43 0,43 0,45 0,45 • без груза 0,13 0,13 0,13 0,13 0,13 0,14 0,56 0,56 0,55 0,55 Грузооборот склада, т/год 36000 36000 48000 48000 48000 60000 60000 60000 72000 72000 Коэффициент неравно-мерности загрузки склада 1,2 1,25 1,3 1,35 1,4 1,45 1,5 1,2 1,25 1,3 Количество рабочих дней в году 255 302 355 255 302 355 255 302 355 255 Средняя дальность перемещения грузов, сформированных пакетами, м 50 55 60 65 70 75 80 85 90 95 Техническая характеристика автопогрузчиков Показатели FHD15C3Z FHG15C3 FНD18C3Z FD20C3Z FG20C3 FHG25C3 FHG30C3 BULCAR CPQD25 FA BULCAR CPQD30FA FD 35 Номер варианта 11 12 13 14 15 16 17 18 19 20 Грузоподъемность, т 1,5 1,5 1,75 2,0 2,0 2,5 3,0 2,5 3,0 3,5 Высота подъема груза, м 3,0 3,0 3,0 3,0 3,0 3,0 3,0 3,0 3,0 3,0 Скорость передвижения, км/ч: • с грузом 18,5 18,5 18,5 17,5 18,5 18,5 19,0 20,0 20,0 16,0 • без груза 19,0 19,0 19,0 18,0 19,0 19,0 19,5 20,5 20,5 18,0 Скорость подъема вил, м/с: • с грузом 0,62 0,60 0,62 0,53 0,54 0,60 0,50 0,45 0,38 0,41 • без груза 0,66 0,68 0,66 0,57 0,58 0,62 0,52 0,48 0,4 0,54 Скорость опускания вил, м/с: • с грузом 0,45 0,45 0,45 0,45 0,45 0,45 0,45 0,40 0,40 0,45 • без груза 0,55 0,55 0,55 0,55 0,45 0,45 0,45 0,40 0,40 0,42 Грузооборот склада, т/год 72000 84000 84000 84000 96000 96000 96000 108000 108000 108000 Коэффициент неравно-мерности загрузки склада 1,35 1,4 1,45 1,5 1,2 1,25 1,3 1,35 1,4 1,45 Количество рабочих дней в году 302 355 255 302 355 255 302 355 255 302 Средняя дальность перемещения грузов, сформированных пакетами, м 100 105 110 115 120 125 130 135 140 145 ТРАНСПОРТНАЯ ЛОГИСТИКА Транспортная задача 1. Постановка задачи 2. Тип транспортной задачи 3. Модель транспортной задачи 4. Математическая формулировка задачи 5. Решение транспортной задачи 6. Решение транспортной задачи в Microsoft Excel с помощью Поиска решения 6.1.Технология работы 6.2.Решение открытой транспортной задачи 1. Постановка задачи Задача ставится следующим образом. Найти объем перевозок для каждой пары «поставщик-потребитель» так, чтобы: • мощности всех поставщиков были реализованы; • спросы всех потребителей были удовлетворены; • суммарные затраты на перевозку были минимальными. Рисунок 1 Таблица 1 Потребитель 1 Потребитель 2 Потребитель 3 Мощности поставщиков Поставщик 1 С11 С12 С13 a1 Поставщик 2 С21 С22 С23 a2 Поставщик 3 С31 С32 С33 a3 Поставщик 4 С41 С42 С43 a4 Потребности потребителей b1 b2 b3 аi – объем продукции, поставляемый i-ым производителем (поставщиком) bj – объем продукции, получаемый j-м потребителем cij – стоимость поставки единицы продукции от i-го производителя к j-му потребителю (удельная стоимость перевозок) xij – объем поставляемой продукции от i-го производителя к j-му потребителю (план перевозок) 2. Тип транспортной задачи • Транспортная задача называется закрытой, если суммарный объем отправляемых грузов равен суммарному объему потребности в этих грузах по пунктам назначения: • Если такого равенства нет (потребности выше запасов или наоборот), задачу называют открытой, то есть: 3. Модель транспортной задачи • Все грузы должны быть отправлены: • Все потребители должны быть обеспечены грузами в плановом объеме: • Суммарные объемы отправления должны равняться суммарным объемам назначения (уравнение баланса): • Должны выполняться условия неотрицательности переменных: • Целевая функция - транспортные издержки должны быть минимальны: 4. Математическая формулировка задачи • На множестве неотрицательных решений системы ограничений найти такое решение, при котором целевая функция принимает минимальное значение. 5. Решение транспортной задачи Транспортная задача решается методом потенциалов Этапы: 1. Разработка начального плана 2. Расчет потенциалов 3. Проверка плана на оптимальность 4. Поиск максимального звена неоптимальности (если условие третьего пункта не достигнуто) 5. Составление контура перераспределения ресурсов (цикла) 6. Определение минимального элемента в контуре перераспределения и перераспределение ресурсов по контуру 7. Получение нового плана. Описанная процедура повторяется несколько раз (итераций), пока не будет найдено оптимальное решение. Вычислительный алгоритм для каждой итерации не меняется. Пример. На 3 базах а1, а2, а3 имеется однородный товар количеством 200 т, 110 т и 90 т, соответственно. Этот товар нужно отгрузить магазинам b1, b2, b3 и b4, потребность которых составляет 50 т, 100 т, 150 т и 100 т, соответственно. Транспортные издержки cij – в тыс. руб. на тонну. Матрица тарифа Составить план отгрузки товара, чтобы транспортные расходы были минимальными. Решение: Начальный план можно составить несколькими методами. Рассмотрим метод «северо-западного угла», метод двойного предпочтения и метод аппроксимации (Фогеля). 1. Метод «северо-западного угла». Загрузка клеток начинается с верхней левой клетки и продолжается вниз и вправо. При этом клетки загружаются максимально возможным образом. По указанному правилу загружаем первую клетку из условия Таким образом, первый пункт назначения загружен, а первый пункт отправления имеет остатки груза , которые и распределяем на второй пункт назначения: Продолжая аналогичным образом, получаем: Результаты расчета начального плана представлены в табл.2. Таблица 2 bj аi 50 100 150 100 200 50 100 50 8 4 5 7 110 100 10 3 2 4 5 90 90 5 6 3 4 Недостатком данного метода является то, что он не учитывает значения элементов cij матрицы транспортных расходов, в результате чего полученное этим методом начальное распределение (начальный опорный план перевозок) может быть достаточно далеко от оптимального. 2. Метод двойного предпочтения. Отмечают клетки с наименьшими стоимостями перевозок сначала по каждой строке, а затем по каждому столбцу. Клетки, имеющие две отметки, заполняют в первую очередь, затем заполняют клетки с одной отметкой, а данные о нераспределенном грузе записывают в неотме­ченные клетки с наименьшими стоимостями. При этом из двух клеток с одинаковой стоимостью перевозок предпочтение отдается клетке, через которую осуществляется больший объем перевозок. Вычеркивание строк и столбцов при заполнении клеток проводится по описанным выше правилам. Пример начального распределения методом наименьших стоимостей для тех же исходных данных, что и ранее, представлен в табл. 3. Таблица 3.  bj аi 50 100 150 100 200 40 * 60 100 8 4 5 7 110 * 10 ** 100 3 2 4 5 90 ** 90 * 5 6 3 4 Этот результат лишь незначительно лучше начального плана, составленного методом «северо-западного угла». 3. Метод аппроксимации (Фогеля). При определении начального плана транспортной задачи методом аппроксимации Фогеля на каждой итерации по всем столбцам и по всем строкам находят разность между двумя записанными в них минимальными тарифами. Для решения внизу матрицы вводят дополнительную строку, в которую заносят разности между двумя минимальными значениями стоимости перевозок в каждом j-м столбце. С правой стороны матрицы вводят дополнительный столбец, в который записывают разности между двумя наименьшими значениями стоимости перевозок по каждой i-й строке. Таблица 4 40 150 100 100 150 100 bj аi 50 100 150 100 200 200 200 40 150 10 1 1 1 8 4 5 7 60 110 50 60 1 2 3 2 4 5 90 90 90 90 1 1 1 5 6 3 4 2 2 1 1 2 1 1 2 2 3 Среди всех разностей, вписанных в дополнительной строке и дополнительном столбце, выбирают наибольшую. В нашем примере два значения имеют одинаковую наибольшую разность и ; в одном случае необходимо загрузить клетку (2;1), в другом - (2;2). Поскольку оба варианта находятся на одной строке, выбирают тот из них, который с максимальным значением стоимости в своем столбце имеет наибольшее значение: Значит, надо остановиться на клетке (2;1). Загрузка осуществляется по максимуму. Столбец с номером j = 1 полностью загружен и из дальнейшего рассмотрения исключается (вычеркивается). По второй строке имеются остатки: Для оставшихся в рассмотрении вариантов вновь добавляем дополнительные строку и столбец, в которые выписываем разность между наименьшими стоимостями. На этот раз также два значения оказались одинаковыми, однако оба раза они получены с участием одного вычитаемого , что указывает на необходимость загрузки клетки (2;2): Проставляем загрузку в клетку (2;2), исключаем из рассмотрения строку с номером i = 2 и переходим к следующей итерации. Среди разностей наибольшая получена при вычитании , следовательно, загружаем клетку (3;4): Так как после итерации остался лишь один пункт отправления а1 = 200, то варианты распределения этого груза по пунктам назначения однозначны: Полученные значения проставляем в соответствующие клетки таблицы, и расчет закончен. Подсчитываем функцию цели: Расчет потенциалов выполняют по загруженным клеткам (), используя формулу Где - потенциал i-й строки - потенциал j-го столбца Возьмем для дальнейшего расчета начальный план, полученный методом «северо-западного угла». Пусть . Получим: Результаты расчетов потенциалов представлены в табл.5. Таблица 5 8 4 5 6 bj аi 50 100 150 100 200 - 50 100 50 8 4 5 + 7 -1 110 + 100 10 3 2 - 4 5 -2 90 90 5 6 3 4 Проверка плана на оптимальность. План считается оптимальным, если для всех незагруженных клеток выполняется условие . При этом количество загруженных клеток равно Где m – количество строк, n – количество столбцов. Если количество загруженных клеток меньше чем , то в этом случае вводятся условно загруженные клетки «значащие ноль». По табл.5 осуществляем проверку начального плана на оптимальность: 4 1 1 Поиск максимального звена неоптимальности. Оценивая разности , выберем ту из них, модуль которой будет наибольшим. В нашем примере это 4, следовательно, именно клетка (2;1) должна из незагруженной клетки перейти в загружаемую. В табл. 5 клетку (2;1) помечаем знаком (+) и пересчитаем ее по циклу. Составление контура перераспределения ресурсов (цикла). Циклом в таблице транспортной задачи называется замкнутый многоугольник, сторонами которого являются горизонтальные и вертикальные отрезки. Одна его вершина совпадает с загружаемой клеткой, а все остальные вершины находятся в загруженных клетках. При этом загружаемая клетка имеет знак (+), а остальные вершины имеют чередующиеся знаки. Определение минимального элемента в контуре перераспределения и перераспределение ресурсов по контуру. Из чисел, записанных в клетках цикла со знаком (-) выберем минимальное. В клетках с (-) вычитаем , а в клетках с (+) прибавляем В процессе перераспределения ресурсов может произойти полная разгрузка двух и более клеток. В этом случае следует считать незагруженной только одну из них, а остальные, проставив нулевой ресурс 0, считать условно загруженными. Таблица 6 4 4 5 6 bj аi 50 100 150 100 200 - 100 100 8 4 5 + 7 -1 110 50 + 50 10 3 2 4 - 5 -2 90 90 5 6 3 4 Получение нового плана (итерации) осуществляется в том же порядке, который был рассмотрен. До тех пор, пока не будет найдено оптимальное решение, удовлетворяющее неравенству: . По результатам первой итерации имеем: Ниже приведен план второй итерации. 1. Расчет потенциалов 2. Проверка плана на оптимальность 1 3. Поиск максимального звена неоптимальности (если условие третьего пункта не достигнуто) (клетка (2;2)) 4. Составление контура (цикла) перераспределения ресурсов 5. Определение минимального элемента в контуре перераспределения и перераспределение ресурсов по контуру Таблица 7 5 4 5 7 bj аi 50 100 150 100 200 50 150 8 4 5 7 -2 110 50 50 10 3 2 4 5 -3 90 90 5 6 3 4 В соответствии с перераспределением ресурсов по контуру (циклу) получаем табл.7, для которой вновь рассчитываем потенциалы и проверяем план на оптимальность. Таким образом, условие выполняется, и план, представленный в табл.7, является оптимальным. Транспортные издержки по оптимальному плану: При решении транспортных задач необходимо знать ряд особенностей. Важнейшим инструментом получения улучшенного решения является перераспределение ресурсов по контуру (циклу). Помимо простых контуров, которые встречались в рассмотренной задаче, на рис. 2 приведен пример более сложных контуров. Если при решении не выполняется условие , то вырожденность задачи можно устранить следующими приемами: • Поменять местами столбцы; • Ввести фиктивную клетку со «значащим» нулем (сделать условную загрузку клетки) Маршрутизация автомобильных перевозок Выбор подвижного состава для перевозки продукции Применение тягачей со сменными прицепами или полуприцепами по сравнению с бортовыми автомобилями Критерием выбора может стать равноценное расстояние lр, которое устанавливается при том условии, что часовая производительность автомобиля Qч.а. будет равна часовой производительности тягача Qч.тг., т.е.: где qa, qтг – грузоподъемность автомобиля и прицепных систем, соответственно буксируемых тягачом, т; tпр – время простоя автомобиля под погрузку и разгрузку, ч; tп.п – время перецепки прицепов, ч; β – коэффициент использования пробега; Vta ,Vtтг – техническая скорость автомобиля и тягача соответственно, км/ч; lр – равноценное расстояние, км. Полученное равноценное расстояние сравнивают с расстоянием перевозки: 1) если расстояние перевозки меньше равноценного, т.е. lе.г.lр, то применять автомобиль. Это связано с тем, что на коротких расстояниях перевозки время на перецепку прицепов меньше времени простоя бортовых автомобилей под погрузку и выгрузку; 2) при определении равноценного расстояния в знаменателе получена отрицательная величина – выбираем тягач, так как (qтг Vtтг) больше (qa Vta); при отрицательном значении числителя следует выбирать автомобиль. Пример 1. Определить целесообразность применения тягача или автомобиля, если грузоподъемность каждого из них – 5 т, техническая скорость автомобиля Vta= 25 км/ч, тягача Vtтг= 20 км/ч, коэффициент использования пробега β= 0,5, время простоя автомобиля под погрузку и выгрузку – 0,8 ч, а время перецепок – 0,1 ч. Расстояние перевозки lеr= 20 км. Решение. Определим равноценное расстояние: lр= 0,5*25*20(5*0,8-5*0,1)/(5*25-5*20)= 35 км. Так как расстояние перевозки меньше равноценного (20<35), то следует применять тягач. Пример 2. Определить выгодность применения 5-тонного авто­мобиля по сравнению с 4-тонным тягачом для работы на расстоя­нии 25 км, если техническая скорость автомобиля Vta= 25 км/ч, а тягача Vtтг =15 км/ч, время простоя автомобиля под погрузку и выгрузку — 0,5 ч, время на перецепку прицепов — 0,1 ч, коэффи­циент использования пробега β = 0,5. Решение. Равноценное расстояние (3.2): lр= 0,5*25*15(5*0,5-4*0,1)/(5*25-4*15)= 6 км. Поскольку расстояния перевозки больше равноценного (25 > 4,3), то следует применять автомобиль. Сравнение выгодности применения бортового автомобиля и самосвала Критерием выбора является равноценное расстояние lр. Оно оп­ределяется с использованием часовой производительности борто­вого автомобиля и самосвала. Графическое изменение часовой про­изводительности бортового автомобиля и самосвала в зависимости от длины груженой ездки, а также равноценное расстояние lр пока­заны на рис. 3.1. Равноценное расстояние lр определяется по фор­муле (3.2): , км, (3.3) где β — коэффициент использования пробега; Vt — техническая скорость самосвала и бортового автомобиля, км/ч; qб — грузоподъемность бортового автомобиля, т; ∆t — выигрыш во времени на разгрузку самосвала, ч; ∆q — потеря в грузоподъемности самосвала по сравнению с бортовым автомобилем, т; — время погрузки и выгрузки бортового автомобиля, ч. Определив равноценное расстояние, считают, что если расстоя­ние перевозки lеr будет меньше равноценного lр (lеr < lр), следует при­менять самосвал, если lеr > lр — бортовой автомобиль (рис. 3.2). Рис. 3.2. График равноценного расстояния: 1 — бортовой автомобиль; 2 — самосвал Пример 3. Какой автомобиль выгоднее применять (бортовой или самосвал), если расстояние груженой ездки — 20 км, грузоподъемность бортового автомобиля qб – 5 т, самосвала qс – 3,5 т, время под погрузку и выгрузку бортового автомобиля tпрб – 0,8 ч, самосвала tпрс – 0,3 ч? Коэффициент использования пробега β = 0,5, техническая скорость Vt=30 км/ч. Решение. Находим величину снижения грузоподъемности са­мосвала по сравнению с бортовым автомобилем: ∆q = qб - qс = 5 - 3,5 = 1,5 т. Определяем выигрыш во времени разгрузки самосвала: ∆t = tпрб - tпрс = 0,8 – 0,3 = 0,5 ч. Равноценное расстояние будет: lр = 0,5 * 30(5 * 0,5/1,5 - 0,8) = 13 км. Так как заданное расстояние 20 км и оно больше равноценного (20 > 12,5), то в данном случае следует применять бортовой автомобиль. Выбор типа автомобиля по себестоимости перевозок грузов Выбор типа автомобиля можно определить по себестоимости 1 т-км. Определяют себестоимость 1 т-км сравниваемых автомоби­лей и отдают предпочтение тому автомобилю, себестоимость тон­но-километра которого меньше. Себестоимость 1 т-км можно определить по формуле: , руб./1 т-км, (3.4) где Спер — сумма переменных расходов на 1 км, руб.; le — пробег автомобиля за одну ездку, км; Спост — сумма постоянных расходов на один автомобилечас, руб.; tе — время одной ездки, ч; Wе — транспортная работа за одну ездку, т-км; ЗПв — заработная плата водителя за одну ездку, руб. Так как le = ler /βе, tв = tдв + tпр = ler / βе * Vв + tпр, то формула примет следующий вид: . После преобразований получим: руб./1 т-км. Пример 4. Определить выгодность применения автомобиля грузоподъемностью 5,0 т по сравнению с автомобилем 4,0 т при следующих условиях: расстояние перевозки ler — 20 км, коэффи­циент использования пробега βе — 0,5, коэффициент использова­ния грузоподъемности γст — 0,8, техническая скорость 5-тонного автомобиля Vt = 25 км/ч, а 4-тонного — 20 км/ч, время простоя под погрузкой и разгрузкой за одну ездку для автомобиля 5т — 0,7 ч, а 4т — 0,5 ч. Затраты по каждой модели равны: Виды затрат 5 т 4 т Спер — переменные расходы, руб./1 т-км 6,0 5,0 Спост — сумма постоянных расходов на один автомобилечас, руб. 30,0 25,0 Заработная плата водителя за одну ездку, руб. 80,0 60,0 Решение. Себестоимость 1 т-км при выполнении перевозок авто­мобилем грузоподъемностью 4 т: Себестоимость 1 ткм при выполнении перевозок автомобилем грузоподъемностью 5 т: Себестоимость 1 т-км автомобиля грузоподъемностью 5 т получи­лась ниже, чем у автомобиля грузоподъемностью 4 т (4,86 < 5,03), поэтому для выполнения данной перевозки выбираем автомобиль грузоподъемностью 5 т. Задача. Какой автомобиль выгоднее применять (бортовой КАМАЗ - 63501 или самосвал КАМАЗ - 55111), если расстояние груженной ездки – 140 км, грузоподъемность бортового автомобиля qб – 14 т, самосвала qс – 13 т, время под погрузку и выгрузку бортового автомобиля tпрб – 1,6 ч, самосвала tпрс – 0,7 ч? Коэффициент использования пробега β = 0,5, техническая скорость Vt=30 км/ч. Определение оптимального срока замены транспортного средства Задача. Транспортные расходы, в том числе расходы на содер­жание транспортных средств, в структуре затрат на логистику занимают свыше 40%. Сократить эту статью расходов позволит своевременная замена транспортного средства. Решение данной задачи основано на понимании того, что всякое транспортное средство (автомобиль, погрузчик и т. д.), несмотря на массовый характер сборки, "болеет по-своему", т.е. в процессе эксплуатации имеет индивидуальные расходы на ремонт. Система учета затрат, направленных на поддержание работоспособности транспортного парка, должна обеспечивать выявление "слабой" техники, замену которой необходимо осуществлять в пер­вую очередь. Как свидетельствует практика, данная задача ставится и решается прежде всего на предприятиях, имеющих в своем составе службу логистики. Задание Определить срок замены транспортного средства методом минимума общих затрат. Автомобиль, купленный за 40000 у.е., эксплуатировался 6 лет, ежегодно проезжая по 20 тыс. км. Го­довые затраты на ремонт приведены в табл. 1. В ней же указана рыночная стоимость автомобиля (также условно) к концу каждого года эксплуатации. Методические указания Для определения точки (срока) замены необходимо определить две следующие зависимости: 1) f1(х) — зависимость расходов на ремонт, приходя­щихся на единицу выполненной автомобилем работы, от количества выполненной работы; 2) f2(х) — зависимость расхода капитала, приходяще­гося на единицу выполненной работы, от количества вы­полненной работы. Найденные зависимости f1(х) и f2(х) позволят опреде­лить функцию F(х) — зависимость суммарных затрат, т.е. расходов на ремонт и расхода капитала, от величины про­бега. Минимальное значение функции F(х) укажет срок замены транспортного средства. Таблица 1 Исходные данные для расчета точки минимума общих затрат (начальная стоимость автомобиля — 40000 у.е.) Год Пробег, нарастаю­щим итогом, км Годовые затраты на ремонт, у.е. Рыночная стоимость машины к концу периода, у.е. 1 20 000 300 34 000 2 40 000 800 29 600 3 60 000 1900 25 900 4 80 000 3000 22 800 5 100 000 4300 20 500 6 120 000 5900 18 400 Количество выполненной работы будем измерять пробе­гом автомобиля. Расчет точки замены рекомендуется выполнить по фор­ме, представленной в табл. 2. Таблица 2 Расчет точки минимума общих затрат Год Пробег нарастающим итогом, км Годовые затраты на ремонт, у.е. Затраты на ремонт нарастающим итогом, у.е. Стоимость ремонта на 1 км пробега к концу периода, у.е. (функция f1(х)) Рыночная стоимость машины к концу периода, у.е. Величина потребленного капитала к концу периода, у.е. Величина потребленного капитала на 1 км пробега, у.е. (функция f2(x)) Общие затраты на 1 км пробега, у.е. (функцияF(x)) 1 2 3 4 5 6 7 8 9 1-й 2-й 3-й 4-й 5-й 6-й Для определения f1(х) необходимо: 1. Определить затраты на ремонт нарастающим итогом к концу каждого года эксплуатации. По результатам расче­тов заполнить гр. 4 табл. 2. 2. Определить затраты на ремонт в расчете на 1 км про­бега автомобиля. Для этого затраты на ремонт к концу n-го периода, исчисленные нарастающим итогом, т.е. данные гр. 4 табл. 2 необходимо разделить на суммарный пробег автомобиля к концу этого же периода. Полученные результаты заносятся в гр. 5, данные которой в совокупности образуют табличную запись функции f1(х). Для определения f2(x) необходимо: 1. Определить величину потребленного капитала к кон­цу каждого периода эксплуатации. Эта величина рассчиты­вается как разница между первоначальной стоимостью ав­томобиля (40000 у.е.) и его стоимостью на рынке транс­портных средств, бывших в употреблении, к концу соот­ветствующего периода эксплуатации (данные гр. 6). Най­денные значения потребленного капитала вносятся в гр. 7 итоговой таблицы. 2. Определить величину потребленного капитала в расчете на 1 км пробега автомобиля. С этой целью значения гр. 7 необходимо разделить на соответствующие величины пробега (данные гр. 2). Результаты, образующие множество значений функции f2(х), заносятся в гр. 8. Для определения F(x) необходимо определить общие затраты в расчете на 1 км пробега. Для этого следует по­строчно сложить данные гр. 5 и 8, а результаты, также построчно, вписать в гр. 9. Данные гр. 9 образуют множе­ство значений целевой функции F(х), минимальное значе­ние которой указывает на точку замены автомобиля. Графы 2, 3 и 6 заполняются либо на основании исходных данных, приведенных в табл. 1, либо в соответствии с отдельным вариантом задания. В завершение данной темы можно рассчитывать потери, связанные с заменой транспортного средства в отлича­ющийся от оптимального срок. Для применения этого метода на предприятии служба логистики должна обеспечить точный учет расходов на ре­монт каждой единицы используемой в логистических про­цессах техники в привязке к количеству работы, выполненной данной единицей. В нашем примере количество ра­боты измерялось пробегом транспортного средства. Для погрузочно-разгрузочной техники, обеспечивающей выполне­ние большинства логистических операций, объем произве­денной работы измеряют количеством отработанного вре­мени, для чего на современных погрузчиках, штабелерах и т. п. устанавливают часовые механизмы, фиксирующие от­работанное время. Учет затрат на ремонт позволяет определить лишь одну из двух зависимостей, необходимых для принятия реше­ния о замене техники. Другая зависимость (f2(х)) определя­ется в результате проведения маркетинговых исследова­ний, включающих анализ состояния и прогноз развития рынка подержанной техники. Задачей службы маркетинга является также и реклама реализуемой предприятием тех­ники. Решение задач с помощью MS Excel Управление запасами — одна из наиболее сложных логи­стических задач. На практике преобладают два крайних подхода к управлению запасами. Первый — когда управ­ление запасами осуществляется инициативно, на основе личного опыта менеджера по закупкам. Второй — когда управление запасами «поручено» дорогому специализи­рованному программному обеспечению. Между тем, в большинстве случаев элементарное и довольно эффектив­ное управление запасами можно осуществлять с помощью Microsoft Excel. ОБОРАЧИВАЕМОСТЬ ТОВАРНО-МАТЕРИАЛЬНЫХ ЗАПАСОВ Базовые понятия и формулы. Для оценки эффективности управления товарно-материаль­ными запасами применяются два показателя: коэффици­ент оборачиваемости товарно-материальных запасов (inventory turnover (IT) ratio) и оборот товарно-материаль­ных запасов в днях (inventory turnover in days (ITD)). В финансовом менеджменте эти два параметра входят в группу коэффициентов деловой активности (activity ratios). Обычно в учебниках по финансовому менеджменту приводятся следующие формулы расчета указанных па­раметров: , где (1.1) СРТ— себестоимость реализованного товара; ЗсрТМЗ — среднее значение товарно-материальных запасов. (1.2), или , где (1.3) Коэффициент оборачиваемости товарно-материальных запасов (IТ) показывает количество превращений за оговорен­ный (отчетный) период товарно-матери­альных запасов в остатки на счетах или в дебиторскую задолженность. Иначе го­воря, коэффициент показывает, сколько раз за оговоренный период товар превра­тился в деньги. Кроме того, коэффици­ент оборачиваемости товарно-матери­альных запасов позволяет оценить товарно-материальные запасы с точки зрения их ликвидности и с точки зрения достаточности или недостаточности их запасов на складе. Оборот товарно-материальных запа­сов в днях (ITD) служит тем же целям, с тем лишь отличием, что показывает, не сколько раз товар превратился в деньги, а за какое количество дней товар превра­щается в деньги. В общем случае, чем больше пара­метр IТ и, соответственно, чем меньше параметр ITD, тем лучше. Рис. 1.1. Таблица расчета параметров оборачиваемости за год На рис. 1.1. представлена таблица объемов реализа­ции товарно-материальных запасов и их остатков на скла­де по месяцам. Обратите внимание, в ячейке «С14» ука­зан остаток товарно-материальных запасов на конец декабря. Это необходимо для более точного расчета сред­них остатков. Как построить таблицу примера. Чтобы построить таблицу данных, изображенную на рис. 1.1., осуществите следующие действия. В ячейке «А2» таб­лицы наберите янв, затем щелкните на ячейке левой кноп­кой мыши и подведите курсор мыши к маленькому чер­ному квадрату, расположенному в правом нижнем углу выделенной ячейки. Курсор примет такую форму: «+». Нажмите левую кнопку мыши и тяните рамку вниз до ячей­ки «А14». Отпустите кнопку мыши. Все захваченные рам­кой ячейки автоматически заполнятся сокращенными на­званиями месяцев: фев, мар, апр и т.д. (рис. 1.2). Рис. 1.2. Автоматическое заполнение ячеек В ячейки «В1» и «С1» внесите заголовки столбцов. Что­бы заголовки столбцов уместились в своих ячейках, не­обходимо изменить формат ячеек. Для этого выделите ячейки «В1» и «С1» и щелкните на них правой кнопкой мыши. Откроется контекстное меню (рис. 1.3), в котором щелк­ните на пункте «Формат яче­ек...». Откроется окно «Формат ячеек», в котором перейдите на закладку «Выравнивание» и ус­тановите флаг  «переносить по словам». Кроме того, в меню «Выравнивание по горизонта­ли:» и в меню «Выравнивание по вертикали:» установите тип вы­равнивания «по центру» (рис. 1.4). Чтобы сохранить измене­ния, щелкните на «ОК». Рис. 1.3. Контекстное меню панели «Стандартная» Рис. 1.4. Закладка «Выравнивание» окна «Формат ячеек» При таком формате ячейки текст, превышающий ее ширину, достигнув правого края ячейки, перенесется на новую строку внутри ячейки. Далее заполните и аналогичным образом отформати­руйте ячейки «А15»-«А23». Заголовки столбцов и итоговые результаты выделены на рис. 1.1 «жирным» шрифтом. Правильнее применять термин «полужирный» шрифт. Чтобы изменить начерта­ние шрифта на полужирное, выделите ячейки и щелкни­те на кнопке «Ж» («Полужирный») на панели «Формати­рование» окна Excel. Начертания можно комбинировать, как это сделано для названий месяцев. Выделите ячейки «А2»-«А14» и щелкните на кнопки «Ж» («Полужирный») и «К» («Кур­сив») на панели «Форматиро­вание» окна Excel. Чтобы содержимое ячеек с «длинным» текстом не пе­реносилось на слишком боль­шое количество строк внутри ячейки, необходимо расши­рить столбцы таблицы. Для этого поместите курсор на границу столбцов в строке за­головков. Курсор изменит свой вид с  на . Нажмите левую кнопку мыши и пере­тащите границы столбцов вправо, чтобы увеличить их ширину. Теперь заполните ячейки «В2»-«В13» и «С2»-«С14». Что-бы получить формат представ­ления чисел, как показано на рис. 1.1, выделите указанные выше ячейки и щелкните на них правой кнопкой мыши. От­кроется контекстное меню, в котором щелкните на пункте «Формат ячеек». Откроется од­ноименное окно. На закладке «Число» в области «Числовые форматы:» выберите пункт «Числовой» (рис. 1.5). Устано­вите параметр «число десятич­ных знаков» равным нулю и ус­тановите флаг  «Разделитель групп разрядов». После этого щелкните на кнопке «ОК», что­бы сохранить изменения. Рис. 1.5. Закладка «Число» окна «Формат ячеек» Внимание! Возможна ситуация, когда текст внутри ячейки переносится по словам, но высота ячейки не изменяется, т.е. текст целиком не виден. В этом случае следует попробовать дваж­ды щелкнуть на ячейке мышью, а затем щелкнуть на лю­бой другой ячейке. Для оформления границ таблицы воспользуйтесь кнопкой «Границы» панели «Главная». Для это­го сначала выделите ячейки таблицы, а затем щелкните на кнопке расположенной справа от кнопки «Грани­цы». Откроется меню типов границ (рис.1.6). Щелкните на кнопке «Все границы». Рис. 1.6. Меню типов границ Ячейка «В15» расчетная. В ней содержится сумма чи­сел ячеек «В2» - «В13». Для суммирования в ячейке «В15» наберите формулу =сумм(В2:В14). Внимание! Для быстрого набора формулы суммирования установите табличный курсор в ячейке «В15» и щелкните на кнопке «Автосумма» ∑ панели инструментов. В ячейке «В15» ото­бразится формула суммирования, а вокруг суммируемых яче­ек появится бегущая пунктирная линия. Для завершения ввода формулы суммирования нажмите клавишу Enter. Теперь у вас должна быть создана таблица как на рис. 1.1, незаполненными остались ячейки «С16»-«С17» и «В20»-«В23». Расчет параметров оборачиваемости за год. Для расчета параметров оборачиваемости товарно-мате­риальных запасов за год применяют два метода. Метод первый. Среднее значение товарно-материальных запасов рассчитывается по формуле: , где (1.4) Зср — среднее значение запасов; Онг — остатки на начало года; Окг — остатки на конец года. Для расчета по первому методу в ячейке «С16» набери­те формулу =(С2+С14)/2. Метод второй. Среднее значение товарно-материальных запасов рассчитывается по формуле: ,где (1.5) Онм — остатки на начало месяца. Окг — остатки на конец года. Для расчета по второму методу в ячейке «С16» набе­рите формулу =СРЗНАЧ(С2:С14). Данная формула эквивалентна формуле А =СУММ(С2:С14)/13. Внимание! Функция «СРЗНАЧ» игнорирует пустые ячейки. Иначе гово­ря, сумма значений ячеек делится на количество не пустых ячеек. Для расчета параметров оборачиваемости IТ и ITD набе­рите: в ячейке «В20» =В15/С16; в ячейке «В21» =В15/С17; в ячейке «В22» =365/В20; в ячейке «В23» =365/В21. Внимание! Чтобы в ячейках «В20» и «В21» отображались десятичные знаки, установите параметр «число десятичных знаков» для этих ячеек равным двум. Обратите внимание, при расчете по первому методу IТ=6,71; ITD = 54, а при расчете по второму — IТ=5,07; ITD = 72. То есть, первый метод менее точен, применять его следует только для экспресс оценки эффективности управления запасами (активами). Расчет параметров оборачиваемости за месяц. Средние товарно-материальные запасы за месяц могут быть определены как: • средние запасы на начало и на конец месяца; • сумма запасов на начало каждой недели месяца и на конец месяца, деленная на количество недель, +1; • сумма запасов на каждый день месяца, деленная на количество дней месяца. Следует отметить, что первый способ определения средних запасов наименее то­чен. Рассмотрим простой пример. Вы при­обрели в начале месяца товары на сумму 30000 рублей. На 25-й день вы распродали весь товар. На конец месяца остаток това­ра нулевой. Количество дней в месяце — 30. Используя формулу расчета ITD, полу­чаем: ITD = (30000 руб. + 0 руб.)/2*30 дней)/ 30000 руб. = 15 дней. Между тем, очевидно, что фактически ITD равен 25 дням. Для повышения точнос­ти расчета необходимо усреднять запасы по дням, в крайнем случае по неделям. Расчет среднего запаса по дням дает еще и управленческое преимущество. Ча­сто менеджеры для улучшения отчетнос­ти притормаживают снабжение в конце месяца, чтобы снизить складской запас и «улучшить» оборачиваемость. При усред­нении «по дням» эта хитрость не сраба­тывает. Расчет параметров оборачиваемости с усреднением запасов по дням имеет свои особенности. На рис. 1.7. приведена табли­ца реализации и складских остатков в фев­рале 2010 года. Как видно из таблицы, во­семь дней этого месяца были выходными и отгрузка товара не производилась. Соот­ветственно, параметры оборачиваемости, рассчитанные с учетом выходных дней и без их учета, будут различаться. Рис. 1.7. Таблица расчетов параметров оборачиваемости за месяц Внимание! Чтобы изменить формат представления даты, в окне «Фор­мат ячеек» на закладке «Число» выберите числовой фор­мат «Дата» (рис. 1.8), после чего в области «Тип:» дважды щелкните на требуемом формате представления. Рис. 1.8. Выбор формата представления даты Параметры оборачивае­мости рассчитаны в таблице двумя методами: 1) с усред­нением запасов по календар­ным дням; 2) с усреднением запасов только по рабочим дням. Вычисляемые ячейки таб­лицы содержат следующие функции Excel: ячейка «В30» =СУММ(В2:В29); ячейка «С31» =СРЗНАЧ(С2:С29); ячейка «С32» =СУММЕСЛИ(В2:В29; "<>"; С2:С29)/СЧЁТ(В2:В29). В ячейке «С32» применены две новые функции. Фун­кция СУММЕСЛИ — это функция суммирования по за­данному критерию. Чтобы рассчитать средние запасы с усреднением только по рабочим дням, первоначально необходимо вычислить сумму запасов, игнорируя при этом запасы выходных дней. Так как в выходные дни отгрузка товара не осуществлялась, пустые ячейки в столбце В можно рассматривать, как признак выходно­го дня. Суммируя только те ячейки в столбце С, которым соответствуют непустые ячейки в столбце В, вы получите сумму запасов рабочих дней. Для ввода функции СУММЕСЛИ щел­кните на кнопке «Вставка функ­ции» панели инструментов. Откроется окно «Мастер функций» (рис. 1.9). В этом окне в области «Категория:» щелкните на строке «Математические», после чего в области «Функция» дважды щелкните на функции СУММЕСЛИ. Откроется одноименное окно (рис. 1.10). Рис. 1.9. Окно «Мастер функций» Рис. 1.10. Окно функции «СУММЕСЛИ» В строку «Диапазон» необходимо внести диапазон яче­ек критерия выбора. В нашем случае это диапазон «В2»:«В29». Диапазон ячеек можно внести вручную, но удобнее щелкнуть на первой ячейке диапазона и, при нажатой левой кнопке мыши, протянуть бегущую пунктир­ную рамку до последней ячейки. В результате диапазон автоматически внесется в строку. В строку «Условие» необходимо внести условие, в слу­чае выполнения которого ячейки будут суммироваться. Наше условие суммирования — это непустые ячейки в столбце В. Задается оно знаком «не равно»: < >. В строку «Диапазон суммирования» необходимо вне­сти диапазон ячеек суммирования: «С2»:«С29». Проделав все описанные действия, щелкните на «ОК». Вторая функция — СЧЁТ — необходима для подсчета непустых ячеек в диапазоне «В2»:«В29». Сумма непус­тых ячеек, в нашем случае, — это количество рабочих дней. Внимание! Функция «СЧЁТ» возвращает количество именно непустых ячеек. Если в ячейку занесена цифра 0, такая ячейка будет участвовать в суммировании. Для расчета параметров оборачиваемости за месяц применяются те же формулы, что и в предыдущей задаче. Только в ячейках В36 и В37 вместо числа 365 ставится число 28, т.к. оборачиваемость рассчитана на месяц (февраль). Какой из двух методов применять, существенного зна­чения не имеет. Другое дело, если вам необходимо срав­нить две оборачиваемости, например свою и среднеот­раслевую. В этом случае важно, чтобы оборачиваемости были рассчитаны по одному методу. ИЗЛИШКИ ТОВАРНЫХ ЗАПАСОВ И УСЛОВНОЕ ФОРМАТИРОВАНИЕ Оборачиваемость товарных запасов в днях может служить индикатором излишних товар­ных запасов. Для лучшей ви­зуализации медленно оборачи­вающихся запасов применяют условное форматирование. На рис. 2.1 изображена таб­лица оборачиваемости в днях товарных запасов в разрезе номенклатуры. В столбце В указана фактическая обора­чиваемость каждого из това­ров в днях. В столбце С указа­ны плановые показатели. Рис. 2.1. Таблица оборачиваемости товаров Реальные таблицы с переч­нем товарных запасов содер­жат сотни, тысячи и порой де­сятки тысяч номенклатурных позиций. Ориентироваться в таких таблицах довольно сложно. Чтобы повысить эффек­тивность анализа, полезно визуально выделить те товар­ные позиции, оборачиваемость по которым превышает плановую. Для этих целей хорошо подходит условное фор­матирование. Создайте таблицу, аналогичную таблице на рис. 2.1, и поместите табличный курсор в ячейку «В3». Для Microsoft Office 2003 В меню «Формат» щелкните на пункте «Условное форма­тирование...» (рис. 2.2). Откроется одноименное окно (рис. 2.3), в котором в области «Условие 1» выберите зна­чение «формула». Затем перейдите в расположенную справа ячейку и наберите формулу проверки условия: =В3>С3. С помощью этой формулы вы проверите, пре­высила фактическая оборачиваемость плановую или нет. Рис. 2.2. Пункт «Условное форматирование меню «Формат». Рис. 2.3. Окно «Условное форматирование». Если вы набирали номера ячеек щелкая на них мы­шью, формула будет иметь вид: =$В$3>$С$3, т.е. ячей­ки «В3» и «С3» «заморожены». В дальнейшем это помеша­ет корректно скопировать формулу в расположенные ниже ячейки. Поэтому знаки $ лучше убрать. Набрав формулу условия, щелкните на кнопке «Фор­мат». Откроется окно «Формат ячеек». В нем вы можете настроить формат представления данных в ячей­ке и оформление ячейки в случае выполнения заданного условия. На закладке «Шрифт» вы можете настроить на­чертание шрифта ячейки, его цвет и наличие или отсут­ствие подчеркивания. В примере выбрано полу­жирное начертание и красный цвет. На закладке «Граница» вы можете настроить тип линии границы и ее цвет. На закладке «Вид» можно настроить цвет залив­ки ячейки. В примере выбран серый цвет. Установив все необходи­мые настройки, щелкните на «ОК», чтобы вернуться в окно «Условное форматирование». В нижней части окна отобра­зится внешний вид ячейки в случае выполнения условия. Если вас устраивает вид ячей­ки — щелкните на «ОК». Чтобы скопировать «услов­ное форматирование» ячейки в ячейки, расположенные под ней, поместите табличный курсор в нужную ячейку и щелкните на инструменте «Формат по образцу» панели «Стандартная» (рис. 2.4). Вокруг выбранной ячейки появится бегущая пунктирная линия. Наведите на ячейку курсор мыши, нажмите и, удерживая левую кнопку мыши, тяните курсор вниз до ячейки «В24». Отпустив левую кнопку мыши, вы завер­шите процесс копирования условного формата. Но слишком высокая оборачиваемость не всегда хоро­шо. Если оборачиваемость товарных запасов слишком высокая, у вас либо суще­ственный дефицит товаров, либо занижены планы. В лю­бом случае не помешает осу­ществить проверку по второ­му условию: =$В7<$С7/2, т.е. выделить ячейки, обора­чиваемость в которых лучше плановой в два раза. Для это­го вновь откройте окно «Ус­ловное форматирование» и щелкните на кнопке «А так­же >>». Добавится область «Условия 2», в которой вы мо­жете осуществить все необ­ходимые настройки точно так же, как и для «Условия 1». Рис. 2.4. Инструмент «Формат по образцу» панели «Стандартная». Внимание! В окне «Условное форматирование» контекстное меню не работает. Поэтому, чтобы скопировать формулу из «Усло­вия 1», выделите формулу и нажмите комбинацию клавиш СТRL+С. Для вставки скопированной формулы воспользуй­тесь комбинацией СТRL+V. Для Microsoft Office 2007 На панели «Главная» щелкните на пункте «Условное форма­тирование» (рис. 2.5). Появится меню. Выберите «Правила выделения ячеек» - «Больше», откроется одноименное окно (рис. 2.6). В поле «Форматировать ячейки, которые БОЛЬШЕ:» ввести ячейку С3. С помощью этой формулы вы проверите, пре­высила фактическая оборачиваемость плановую или нет. Рис. 2.5. Пункт «Условное форматирование» Рис. 2.6. Окно Условного форматирования «больше» Если вы набирали номера ячеек, щелкая на них мы­шью, формула будет иметь вид: =$С$3, т.е. ячей­ка «С3» «заморожена». В дальнейшем это помеша­ет корректно скопировать формулу в расположенные ниже ячейки. Поэтому знаки $ лучше убрать. Набрав формулу условия, щелкните на стрелочку в поле расположенном справа. Откроется список вариантов форматирования, если ни один из предложенных не подходит, выберите пункт «Пользовательский формат» (рис. 2.7). В нем вы можете настроить формат представления данных в ячей­ке и оформление ячейки в случае выполнения заданного условия. На закладке «Шрифт» вы можете настроить на­чертание шрифта ячейки, его цвет и наличие или отсут­ствие подчеркивания. В примере выбрано полу­жирное начертание и красный цвет. Рис. 2.7. Список вариантов форматирования На закладке «Граница» вы можете настроить тип линии границы и ее цвет. На закладке «Вид» можно настроить цвет залив­ки ячейки. В примере выбран серый цвет. Установив все необходи­мые настройки, щелкните на «ОК», чтобы вернуться в окно «Условное форматирование». В нижней части окна отобра­зится внешний вид ячейки в случае выполнения условия. Если вас устраивает вид ячей­ки — щелкните на «ОК». Чтобы скопировать «услов­ное форматирование» ячейки в ячейки, расположенные под ней, поместите табличный курсор в нужную ячейку и щелкните на инструменте «Формат по образцу» панели «Главная» (рис. 2.8). Вокруг выбранной ячейки появится бегущая пунктирная линия. Наведите на ячейку курсор мыши, нажмите и, удерживая левую кнопку мыши, тяните курсор вниз до ячейки «В24». Отпустив левую кнопку мыши, вы завер­шите процесс копирования условного формата. Рис. 2.8. Инструмент «Формат по образцу» панели «Стандартная». Но слишком высокая оборачиваемость не всегда хоро­шо. Если оборачиваемость товарных запасов слишком высокая, у вас либо суще­ственный дефицит товаров, либо занижены планы. В лю­бом случае не помешает осу­ществить проверку по второ­му условию: В7<С7/2, т.е. выделить ячейки, обора­чиваемость в которых лучше плановой в два раза. Для это­го вновь откройте окно «Ус­ловное форматирование» и щелкните по пункту «Меньше». Добавится область «Правило 2», в которой вы мо­жете осуществить все необ­ходимые настройки точно так же, как и для «Правило 1». ПЕРЕНОС ДАННЫХ ИЗ НЕСКОЛЬКИХ ТАБЛИЦ-ИСТОЧНИКОВ В ТАБЛИЦУ-ЦЕЛЬ При анализе товарных запасов часто возникает необходи­мость консолидации данных, содержащихся в несколь­ких таблицах-источниках. Например, ваша информаци­онная база данных может формировать два отчета: «складская справка» и «оборачиваемость». Вам же необ­ходима таблица с данными и по оборачиваемости и по те­кущим остаткам. Простая сортировка таблиц по алфави­ту с последующим копированием данных из одной таблицы в другую пригодна далеко не всегда. Часто в складской справке не отображаются товары с нулевым остатком, при этом в таблице оборачиваемостей эти товары могут присутствовать. Соответственно возникает задача объединения таблиц с разным количеством товарных позиций. Перенос данных из таблицы-источника в таблицу-цель. Для переноса данных из таблицы-источника в таблицу-цель удобно воспользоваться функци­ей ВПР. Рассмотрим применение функции на приме­ре. Пусть необходимо добавить из таблицы со складскими остатками (рис. 3.1) данные об оборачиваемостях товаров в таблицу на рис. 2.1. Таблица 3.1 в этом случае будет источником, а таблица 2.1 целью. Рис. 3.1. Таблица со складскими остатками Последовательность действий следующая. Установите табличный курсор в ячейку и щелкните на инстру­менте «Вставить функцию». Откроется окно «Мастер функций шаг 1 из 2». В разде­ле «Категория:» щелкните на категории «Ссылки и мас­сивы», а в разделе «Функция» дважды щелкните на функ­ции ВПР. Откроется окно функции ВПР (рис. 3.2). Рис. 3.2. Окно функции «ВПР» Первый параметр функции, который необходимо за­полнить, — это «Искомое_значение». Чтобы перенести данные из таблицы-источника в таблицу-цель, необходи­мо задать признак, по которому функция ВПР найдет ве­личину складского остатка в источнике и поместит в цель. В нашем случае таким признаком являются наименова­ния товаров, указанные в столбце «Товар». Щелкните на ячейке «А1» в таблице 2.1. Тем самым вы укажете, что в источнике необходимо искать ячейку, содержимое кото­рой совпадает с содержимом ячейки «А1». Следующий параметр функции — «Таблица». Здесь за­дается область поиска в таблице-источнике. Переместите курсор в область указанного параметра (первоначально она пустая), затем перейдите в файл или на лист с таблицей-источником и выделите область поиска. Чтобы выделить область поиска, щелкните на левой верхней ячейке облас­ти поиска и, удерживая левую кнопку мыши, тяните кур­сор до правой нижней ячейки области поиска. Обратите внимание, выделять область поиска следует, начиная со столбца, содержащего «Искомое_ значение». В нашем слу­чае — это столбец А. Если перед столбцом с «Исходным значением» имеются столбцы с данными (например с № п/п), в область поиска они не должны включаться. Третий параметр «Номер_столбца» — это номер столб­ца в «Таблица», из которого будут переноситься данные в таблицу-цель. Первый столбец массива всегда имеет но­мер 1. В нашем примере «Номер_столбца» равен 2. Обра­тите внимание, что речь идет не о номерах столбцов таб­лицы-источника, а о номерах столбцов выделенного в таблице массива. То есть номера столбцов таблицы и мас­сива совпадут только в частном случае, когда совпадают первый столбец таблицы и массива. Последний параметр «Интервальный_просмотр» — установите «ЛОЖЬ» и щелкните на «ОК». Теперь необходимо скопировать формулу в располо­женные ниже ячейки. Прежде чем это сделать, «заморозь­те» ячейки диапазона «Таблица», чтобы границы диапа­зона не сместились при копировании. Сейчас формула в ячейке «D1» имеет примерно такой вид: =ВПР(А1;'рис.3.1.'!А1:В27;2;ЛОЖЬ) исправьте ее на: =ВПР(А1;'рис.3.1.' !А$1:В$27;2;ЛОЖЬ) Внимание! Чтобы скопировать содержимое ячейки в смежные ячейки, выделите ячейку, затем подведите курсор мыши к маркеру, расположенному в правом нижнем углу выделенной ячей­ки. Курсор примет вид «». Нажмите левую кнопку мыши и тяните рамку в нужную сторону. Отпустите кнопку мыши. Во все захваченные рамкой ячейки скопируется содержи­мое исходной ячейки. Результирующая таблица представлена на рис. 3.3. Рис. 3.3. Объединенная таблица оборачиваемости и остатков товаров ИНДЕКС СЕЗОННОСТИ И ПРОГНОЗИРОВАНИЕ СБЫТА Индексы сезонности применяются для прогнозирования сбыта товаров сезонного спроса. Под сезонным спросом понимаются цикличные (повторяющиеся ежегодно) коле­бания объемов потребления товара. Эти колебания могут быть связаны со временем года, погодой или календар­ной датой (1 сентября, Новый год, 8 Марта). Для прогнозирования сезонных колебаний рассчиты­ваются индексы сезонности (Is). Индекс сезонности харак­теризует в процентах долю объема продаж товара относи­тельно его среднемесячного объема продаж за год. Расчет индексов сезонности в Excel. Рассчитать индексы сезонности можно двумя способами. Если в течении года присутствовала ярко выраженная тенденция (тренд) роста или спада объемов продаж, при­меняется формула: , где Ism—индекс сезонности месяца т; Ymn—значение объема реализации за месяц т, года n; Ycpn — среднемесячный объем реализации за год n; n —количество анализируемых лет. Иначе говоря, прогнозируемый на месяц т индекс се­зонности равен среднему индексу сезонности для этого месяца за n лет. Если в течение года ярко выраженной тенденции (трен­да) роста или спада объемов продаж нет, применяется формула: . Иначе говоря, прогнозируемый на месяц т индекс се­зонности равен отношению среднего значения объема продаж за несколько лет, за указанный месяц к среднеме­сячному объему продаж товара за n лет. На рис. 9.1 представлена таблица со статистикой про­даж товара за четыре года по месяцам и индексы сезон­ности. Is1 — индекс сезонности, рассчитанный первым способом, Is2 — индекс сезонности, рассчитанный вто­рым способом. Рис. 9.1. Таблица расчета индексов сезонности Расчет промежуточных результатов осуществлен с по­мощью следующих формул: • Столбец F («всего») — простое суммирование объе­мов реализации за указанный месяц по годам =СУММ(В4:Е4). • Столбец G («в среднем») — отношение объема реа­лизации за все годы к количеству лет =F4/СЧЁТ(В4:Е4). Для расчета индекса сезонности первым способом первоначально необходимо рассчитать индексы сезон­ности для каждого года раздельно. В таблице индексы сезонности по годам рассчитываются в столбцах Н-К. Расчетная формула имеет вид (для января 2006 года): =В4/($В$16/12)*100. И, наконец, индекс Is1 рас­считывается как среднее значение индексов по годам: =СРЗНАЧ(Н4:К4). Для расчета индекса сезонности вторым способом не­обходим средний объем продаж за месяц по годам. Он уже рассчитан в столбце G. Среднемесячный объем продаж за все годы рассчитывается в ячейке «G16» по формуле: =СУММ (G4:G15) /12. То есть сумма средних объемов продаж по каждому месяцу делится на количество меся­цев. И последний шаг — делим средний объем продаж за месяц m на среднемесячный объем продаж за четыре года: =G4/$G$16*100. Внимание! Прежде чем приступать к расчету индексов сезонности необходимо проанализировать имеющуюся статистику про­даж и убрать из нее аномально большие или аномально малые объемы продаж. Например, очень большой объем продаж мог быть вызван разовой, случайной, сделкой с крупным клиентом. Объем такой сделки необходимо убрать из статистики продаж. Прогнозирование объемов продаж с помощью индек­сов сезонности. Индексы сезонности можно использовать для прогнози­рования и планирования товарооборота на очередной год. Допустим, на очередной год вы планируете довести объем продаж вашего товара до 6000 штук. Рассчитав прогнозный среднемесячный объем продаж товара и ум­ножив его на соответствующие индексы сезонности, вы получите прогнозные объемы реализации по месяцам (рис. 9.2). Рис. 9.2. Таблица расчета плановых объемов продаж по месяцам. Для расчета планируемых объемов продаж в таблице применены следующие формулы: • =F15/12 — для расчета планового среднемесяч­ного объема продаж (в ячейке «F15» хранится плановый объем продаж за год); • =В2*$F$16/100 — для расчета планового объе­ма реализации товара за конкретный месяц. Применение индексов сезонности в ХYZ-анализе. Как уже было изложено выше, товары с ярко выражен­ной сезонностью скорее всего попадут в группу Z. Чтобы применить ХYZ-анализ к товарам сезонного характера, из статистики продаж необходимо изъять сезонную со­ставляющую. Убирается сезонная составляющая просто: фактические объемы продаж това­ра делятся на соответствующие ин­дексы сезонности, после чего прово­дится ХYZ-анализ. КРАТКОСРОЧНОЕ ПРОГНОЗИРОВАНИЕ В MICROSOFT EXCEL С помощью Excel можно осуществлять прогнозирование объемов сбыта регулярно потребляемых товаров. Целям прогнозирования служат статистические функции Excel: ПРЕДСКАЗ, ТЕНДЕНЦИЯ, РОСТ, ЛИНЕЙН, ЛГРФПРИБЛ. Для иллюстрации возможностей прогнозирования Excel воспользуемся в качестве модели функцией вида: Y = а + bX + сх. Пусть а = 3, b = 4, с = 1,4. Таким образом функция будет иметь вид: Y = 3 + 4Х + 1,4х. Таблица значений функции и ее график представлены на рис. 10.1. Рис. 10.1. Таблица значений и график модельной функции. ПРОГНОЗИРОВАНИЕ С ПОМОЩЬЮ ФУНКЦИЙ «ПРЕДСКАЗ» И «ТЕНДЕНЦИЯ» Функция ПРЕДСКАЗ, основываясь на известных значе­ниях величин X и Y, вычисляет будущее значение величи­ны Y для заданного значения X. Для вычисления будуще­го значения в функции ПРЕДСКАЗ применяется модель линейной регрессии: Y=аХ+b. Воспользуемся функцией ПРЕДСКАЗ, чтобы вычислить значения Y для значений X 11-15. Мастер функции имеет вид, как показано на рис. 11.1. Первый параметр — это значение X, для ко­торого будет вычисляться прогнозное значение Y. В на­шем примере — это ячейка «А12». Рис. 11.1. Мастер функции «ПРЕДСКАЗ». Параметр «Известные_значения_у» — это массив или диапазон известных значений Y. В примере — это диапа­зон «В2»:«В11». Параметр «Известные_значения_х» — это массив или диапазон значений X, для которых известны значения Y. В примере — это диапазон «А2»:«А11». Таким образом, функция будет иметь вид: ПРЕДСКАЗ(А12;$В$2:$В$11; $А$2:$А$11). Ячей­ки границ диапазонов заморожены, чтобы облегчить ко­пирование формулы. Результат прогноза и его графичес­кий вид представлены на рис. 11.2. Рис. 11.2. Таблица значений и график фактических значений и прогнозных. Такой же результат будет получен с помощью функ­ции ТЕНДЕНЦИЯ. Мастер функции имеет вид, как показано на рис. 11.3. Первый параметр функции — «Известные_ значения_у». В это поле следует внести диапазон извест­ных значений Y. Следующий параметр — «Известные_ значения_х». В поле этого параметра необходимо внести диапазон значений X, для которых известны значения Y. Значения X, для которых необходимо рассчитать прогноз­ные значения Y, указываются в поле «Новые_значения_х». В этом поле можно указать как одно значение X, так и ди­апазон значений. Последний параметр, «Константа», ука­зывает какого вида строить прямую аппроксимации. Если в этом поле указать значение «Истина» либо оставить его пустым, функция ТЕНДЕНЦИЯ вычислит значение по­стоянной «b». То есть прогнозная прямая будет иметь вид Y = аХ+b. Если в поле «Константа» указать значение «Ложь», функция ТЕНДЕНЦИЯ присвоит постоянной «b» значение «0», и прогнозная прямая будет иметь вид Y = аХ. В случае нашего примера функция будет иметь вид: =ТЕНДЕНЦИЯ($В$2:$В$11;$А$2:$А$11;А12;ИСТИНА). Рис. 11.3. Мастер функции «ТЕНДЕНЦИЯ». Однако правильнее будет занести в ячейку указанные функции как функции массива. В этом случае, если значе­ния X, для которых необходимо вычислить прогнозные значения Y, указать в виде диапазона, функции вернут со­ответствующий ряд значений прогнозных значений Y. Чтобы внести функцию как функцию массива необхо­димо проделать следующие действия: ■ Выделить в таблице диапазон ячеек для отображе­ния значений Y. Этот диапазон должен быть такой же длин­ны, как и соответствующий ему диапазон ячеек со значе­ниями X. В нашем примере это ячейки «С12»:«С16». ■ Внести в ячейку функцию. Функция вносится в этом случае вручную. То есть, не снимая выделение с ячеек, вам необходимо нажать клавишу =, затем набрать имя функции и ее параметры. После ввода функции кнопку Enter НЕ нажимать. ■ Завершить ввод функции, нажав на клавиатуре CTRL+SHIFT+ENTER. Выделенный диапазон ячеек бу­дет заполнен прогнозными значениями Y. Функция в ячей­ках будет заключена в фигурные скобки: =ТЕНДЕНЦИЯ(В2:B11;А2:A11;А12:А16;ИСТИНА). Внимание! При вводе параметров функции границы диапазонов сле­дует разделять знаком «двоеточие». Параметры функции разделяются знаком «точка с запятой». ПРОГНОЗИРОВАНИЕ С ПОМОЩЬЮ ФУНКЦИИ «РОСТ» Функция РОСТ вычисляет будущее значение величины Y, основываясь на предположении об их экспоненциальном росте. Для вычисления будущего значения функции РОСТ применяет модель вида: Y = dсх. Мастер функции РОСТ представлен на рис. 12.1. Па­раметры функции полностью идентичны параметрам функции ТЕНДЕНЦИЯ: • «Известные_значения_у» — поле для ввода диапазо­на известных значений Y. • «Известные_значения_х» — поле для ввода диапазо­на значений X, для которых известны значения У. • «Новые_значения_х» — поле для ввода диапазона зна­чений X, для которых необходимо рассчитать прогнозные значения Y. • «Конст» — поле, в котором указывается какого вида строить кривую аппроксимации. Если в этом поле указать значение «Истина», функция РОСТ вычислит значение по­стоянной «d». То есть прогнозная кривая будет иметь вид У = dсх. Если в поле «Конст» указать значение «Ложь» либо оста­вить его пустым, функция РОСТ присвоит постоянной «d» значение «1», и прогнозная прямая будет иметь вид У = сх. Рис. 12.1. Мастер функции «РОСТ». Функцию РОСТ, лучше вводить как формулу масси­ва. В нашем примере она имеет вид: {=РОСТ($В$2:$В$11;$А$2:$А$11;А12:А16;ИСТИНА)}. Таблица прогнозных значений Y, полученных с помо­щью функции РОСТ, и соответствующий им график пред­ставлены на рис. 12.2. Как видите, отклонения прогнозных значений от фактических довольно существенны. Точность прогноза можно повысить двумя способами. Во-первых, уменьшить горизонт прогнозирования, т.е. прогнозировать значения меньшего числа точек. Во-вторых, использовать для прогноза меньшее количество известных значений Y. Например, только три последние значения. Рис. 12.2. Таблица значений и график фактических значений и прогнозных. ОЦЕНКА ПОТРЕБНОСТИ В ЗАПАСАХ ТОВАРОВ НЕРЕГУЛЯРНОГО СПРОСА Простейший подход к оценке потребности в запасах това­ров нерегулярного спроса — это анализ количества сде­лок с такими товарами и размера сделок. Рассмотрим один из возможных вариантов такого анализа. На рис. 15.1 представлена таблица количества сделок с каким-то товаром и размеры сделок по месяцам. Несколь­ко слов о том, как вычислены значения: «всего» сделок, «всего» продано товара, «в среднем» сделок, «в среднем» размер сделки. Для подсчета общего количества сделок, ячейка «N2», применена функция суммирования =СУММ(В2:М2). Для подсчета среднего количества сде­лок, ячейка «O2», применена функция =СРЗНАЧ(В2:М2). Рис. 15.1. Таблица количества сделок с товаром и размеров сделок по месяцам. Для подсчета общего количества проданного товара, ячейка «N3», также применена функция суммирования =СУММ(В3:М9). Однако обратите внимания на указан­ный в ней диапазон суммирования. Он имеет вид двумерного массива. Границы диапазона задаются левой верх­ней ячейкой массива — «В3» и правой нижней — «М9». Аналогичным образом рассчитывается и среднее значе­ние размера сделки в ячейке «O3»: =СРЗНАЧ(ВЗ:М9). Рассчитаем, как часто совершается сделка с тем или иным количеством товара. Иными словами, построим таб­лицу частот размеров сделок. Для этого воспользуемся функцией ЧАСТОТА. Мастер функции имеет вид, как показано на рис. 15.2. Параметр «Массив_данных» — это набор данных или ди­апазон ячеек, для которых необходимо рассчитать часто­ты. Параметр «Массив_интервалов» — это набор интер­валов или диапазон ячеек с набором интервалов, в которых группируются значения из «Массива данных». Рис. 15.2. Мастер функции «ЧАСТОТА». Поясним на примере. Пусть имеется массив данных вида: 1,2,3,4,2,3,2. Если массив интервалов имеет вид: 1,2,3,4, функция вернет значения 1,3,2,1. Этот набор цифр означа­ет, что в массиве данных содержится одна «1», три «2», две «3», одна «4». Соответственно, если массив интервалов бу­дет иметь вид: 2,4, функция сосчитает количество значе­ний, попавших в интервалы 1-2 и 3-4, и вернет значения 4,3. Функция ЧАСТОТА должна вводиться как формула массива. Количество выделенных ячеек для ввода фун­кции должно быть на одну больше, чем количе­ство интервалов в «Двоичном массиве». В «лиш­нюю» ячейку будут попадать частоты для значений, выходящих за верхнюю границу интер­вала. Ячейки должны выделяться вертикально. На рис. 15.3. представлен результат работы функции ЧАСТОТА. Функция имеет вид {=ЧАСТОТА(рис.9.1.!В3:М9;А2:А11)}. Рис. 15.3. Таблица частот размеров сделок В качестве массива данных указан двумерный диапазон в таблице 15.1. Как видно из таблицы, наиболее часто встречаются сделки с объемом товара «2» и «4». В столбце С с помощью формулы вида =В2/$В$12 осуществлен расчет вероятности сделки с соответствующим количеством товара. В столбце D вычислены вероятности сделок нарастающим итогом. Предположим, нужно определить количество товара, которое необходимо положить на склад, чтобы осуще­ствить только одну сделку (однократно продать требуе­мое количество товара клиенту). Из таблицы на рис. 15.3 следует, что если на складе будет присутствовать 2 еди­ницы товара, сделка будет осуществлена в 35 % случаев. В 65 % случаев клиент останется неудовлетворенным. Если на складе будет присутствовать 4 единицы товара, т.е. количество товара, продаваемое в среднем за одну сдел­ку, сделка будет осуществлена в 73% случаев. 100% об­служивание клиента достигается только в случае нали­чия на складе 9 единиц товара. Уровень обслуживания клиента определяется менедж­ментом компании, на основе маркетингового анализа, фи­нансовых и логистических возможностей. Предположим, выбран уровень обслуживания 90%, т.е. 6 единиц товара на одну сделку. Умножив среднее количество сделок на 6 еди­ниц, получим объем запасов на один месяц. Как уже было отмечено выше, данный способ опреде­ления объема запаса не является единственным. Вместо среднего количества сделок для расчета запаса может быть применено наиболее вероятное количество сделок, прогнозное количество сделок с учетом сезонности, ли­нейного или экспоненциального тренда. Кроме функции ЧАСТОТА, полезным может оказать­ся применение функций МОДА, МЕДИАНА, МИН, МАКС. У всех перечисленных функций под скобками указывает­ся диапазон массива с данными для анализа. Можно ука­зать от одного до тридцати диапазонов, разделив их зна­ком «точка с запятой». Функция МОДА возвращает наиболее вероятное, наи­более часто встречающееся в массиве данных значение. Для таблицы на рис. 15.1 формула будет иметь вид =МОДА(В3:М9) и вернет значение «2». Функция МЕДИАНА возвращает значение медианы массива данных. Медиана — это число, которое делит область изменения значений в массиве данных на два ин­тервала, попадания в которые имеют равные вероятнос­ти. Для таблицы на рис. 15.1 формула будет иметь вид =МЕДИАНА(ВЗ:М9) и вернет значение «3». Функция МИН возвращает минимальное значение в мас­сиве данных, а функция МАКС — максимальное. Для таб­лицы на рис. 15.1 формула будут иметь вид =МИН(В3:М9) и =МАКС(ВЗ:М9). ВЫЯВЛЕНИЕ КОМПЛЕМЕНТАРНЫХ ТОВАРОВ И ТОВАРОВ СУБСТИТУТОВ Комплементарные товары — это взаимосвязанные, взаимодополняемые товары. Увеличение (уменьшение) спро­са на один из таких товаров вызывает увеличение (умень­шение) спроса на другой товар. Товары субституты — это взаимозаменяемые товары. Увеличение спроса на один из таких товаров вызывает уменьшение спроса на другой. Выявить такие товары поможет коэффициент корре­ляции. С другой стороны, если известно, что какие-то то­вары заведомо комплементарные, а коэффициент корре­ляции их объемов сбыта близок к нулю, следует срочно провести расследование. Возможно, один из товаров сто­ит слишком дорого или его качество не велико. Коэффи­циентом корреляции r(X,Y) двух случайных величин X и Y называется отношение их ковариации к произведению среднеквадратических от­клонений этих величин. Ковариацией двух случайных величин X и Y называется математическое ожида­ние произведений их отклонений от соот­ветствующих математических ожиданий. Свойства коэффициента корреляции: |r(X,Y)| <=1, т.е. -1≤ r(X,Y)≤+l. Если X и Y независимы, r(Х,Y)=0, но из равенства r(X,Y)=0 не следует независи­мость случайных величин X и Y. Если X и Y связаны линейной зависи­мостью Y=AX+B, где А и В постоянные, то |r(X,Y)|=l. Для вычисления коэффициента в Excel можно восполь­зоваться функцией КОРРЕЛ или ПИРСОН. Функции абсо­лютно идентичны. Мастер функций показан на рис. 16.1. Рис. 16.1. Мастер функции «КОРРЕЛ». Функции имеют два параметра «Массив1» и «Массив2». В поле параметра «Массив1» вносится диапазон ячеек со значениями объема продаж первого товара, а в поле параметра «Массив2» — второго. На рис.16.2 представлена таблица с результатами про­даж трех товаров по месяцам. Ниже приведены результаты расчета коэффициента кор­реляции объемов продаж первого и второго товаров, и первого и третьего. Формулы для расчета имеют вид: =КОРРЕЛ(В2:В13;С2:С13) и =КОРРЕЛ(В2:В13;D2:D13). Рис. 16.2. Пример расчета коэффициента корреляции. Для расчета коэффициентов корреляции можно вос­пользоваться надстройкой Excel «Пакет анализа». Эта надстройка служит для решения сложных статистичес­ких задач. Чтобы воспользовать­ся надстройкой «Пакет анализа»: Для Microsoft Office 2003 в пункте меню «Сервис» выбери­те пункт «Анализ данных» Для Microsoft Office 2007 вкладка «Данные» выберите пункт «Анализ данных» Откро­ется одноименное окно (рис. 16.3), в котором следует выбрать инст­румент анализа. Выберите инст­румент «Корреляция». Откроется окно «Корреляция» (рис. 16.4). В по­ле «Входной интервал» укажите интервал $B$2:$D$13 (в таблице 16.2). Переключатель • «Группирование:» оставьте в положении «по столбцам». В области «Параметры вывода» установите переклю­чатель • в положение «Выходной интервал:» и укажите диапазон ячеек для отображения результатов вычисле­ний. Для нашего примера необходим массив ячеек 4x4. Щелкните на кнопке «ОК». Результаты анализа представ­лены на рис. 16.5. Рис. 16.3. Окно «Анализ данных». Рис. 16.4. Окно «Корреляция». Рис. 16.5. Результаты корреляционного анализа с помощью надстройки «Пакет анализа». Внимание! Для Microsoft Office 2003 Если в меню «Сервис» отсутствует пункт «Анализ данных», вам необходимо установить «Пакет анализа». Для этого в меню «Сервис» выберите пункт «Надстройки». Откроется окно «Надстройки» (рис.16.6.). Установить флаг  «Пакет анализа» и щелкните на «ОК». Если в списке надстроек «Пакет анализа» отсутствует, воспользуйтесь программой Setup, чтобы установить недостающий компонент. Для Microsoft Office 2007 Кнопка “Office” → Параметры Excel → Надстройки → Управление: Надстройки Excel → Перейти →  «Пакет анализа» → ОК. Рис. 16.6. Окно «Надстройки» меню «Сервис». Управление запасами с применением анализа АВС и XYZ Задача. В целях укрепления позиции на рынке руководство оптовой фирмы приняло решение расширить торговый ас­сортимент. Свободных финансовых средств, необходимых для кредитования дополнительных товарных ресурсов, фирма не имеет. Свободных складских помещений также нет. Перед службой логистики была поставлена задача со­кращения общего объема товарных запасов с целью сокра­щения затрат на их содержание и высвобождения ресурсов для расширения ассортимента. Снижение запасов при этом не должно отразиться на уровне клиентского сервиса, т.е. на готовности компании к поставке товаров потребителям. Торговый ассортимент фирмы, годовые и квартальные объемы продаж по отдельным позициям представлены в табл. 1. Норма запаса, установленная в компании, составляет 20 дней: 3ср = 20 дн., т.е. средний запас рассчитан на 20 дней работы. Число ра­бочих дней в году составляет в компании 330 дней: N = 330 дн./год. Норма запаса одинакова по всем позициям ассортимента. Доля годовых затрат на хранение (М) в компании в среднем составляет 0,3 от стоимости среднего запаса: М = 0,3 1/год Выполняя задание руководства, служба логистики раз­делила весь торговый ассортимент на три группы А, В и С по признаку доли в реализации, а затем предложила но­вые дифференцированные нормативы среднего запаса: • для товаров группы А (лидеры в реализации) — со­кратить норму запаса и довести ее до 10 дней; • для товаров группы В (средняя доля в реализации) норму запаса оставить без изменения — 20 дней; • для товаров группы С (очень низкая реализация) уве­личить норму запаса, доведя ее до 30 дней. Анализ динамики продаж, выполненный по кварталам года (табл. 3.1), свидетельствует, что разные позиции ас­сортимента компании существенно отличаются друг от друга по показателю стабильности продаж. Позиции с высокой амплитудой колебаний спроса при прочих равных условиях требуют более тщательного контроля и более высоких стра­ховых запасов, чем позиции, потребляемые стабильно. В связи с этим служба логистики выполнила также и ана­лиз XYZ, разделив весь ассортимент на группы X, Y и Z по признаку стабильности спроса. Показателем при этом выб­ран коэффициент вариации квартального спроса. Резуль­таты анализа XYZ, соединенные с результатами анализа ABC, позволили рекомендовать для отдельных продуктов компании разные системы контроля управления запасами. Задание 1 Дифференцировать ассортимент по методу ABC. Порядок проведения анализа ABC: Формулирование цели анализа Идентификация объектов управления, анализируемых методом ABC Выделение признака, на основе которого будет осуществлена дифференциация объектов управления Оценка объектов управления по выделенному классификационному признаку Группировка объектов управления в порядке убывания значения признака Построение кривой АВС Разделение совокупности объектов управления на три группы: группа А, группа В и группа С Таблица 1 Исходные данные для проведения анализа ABC и анализа XYZ № позиции Наименование продукта Годовая реализа­ция про­дукта, тыс. руб. Реализация за квартал I II III IV 1 2 3 4 5 6 7 1 Батончик "Марс" 1788 380 475 400 533 2 Батончик "Милки Уэй" 648 120 185 220 123 3 Батончик "Несквик" 780 115 200 195 270 4 Батончик "Твикс" 2460 650 590 600 620 5 "Баунти" молочный 1524 400 335 415 374 6 Жевательная резинка "Бумер" 696 215 141 180 160 7 Жевательная резинка "Дирол" 3120 650 800 750 920 8 Жевательная резинка "Минтон" 348 80 70 95 103 9 Жевательная резинка "Стиморол" 1020 200 300 200 320 10 Жевательная резинка "Супер" 516 120 150 120 126 11 Изюм 12 1 4 3 4 12 Инжир 36 9 10 10 7 13 Кетчуп "Болгарский" 228 55 60 48 65 14 Кетчуп "Монарх" 96 20 15 31 30 15 Киндер-сюрприз 144 30 35 50 29 16 Кофе "Арабика" молотый 1140 280 270 275 315 17 Кофе растворимый "Нескафе Голд" 2052 530 520 500 502 18 Кофе растворимый "Нескафе Классик" 7536 1790 1900 1880 1966 19 Кукурузные хлопья с сахаром 180 50 39 45 46 20 Лапша "Доширак" 936 190 260 200 286 21 Миндаль в шоколаде 120 32 41 20 27 22 Мюсли 288 65 71 75 77 23 Рис длинный 852 230 220 220 182 24 Рис круглый 468 70 130 110 158 25 Сахар-песок фасованный 1308 348 330 310 320 26 Сникерс 3852 992 970 940 950 27 Суп-гуляш мгновенного приго­товления 24 4 7 6 7 28 Торт вафельный 60 18 21 11 10 29 Чай "Ахмад" 204 45 51 50 58 30 Чай индийский 48 10 14 12 12 31 Чупа Чупс 192 45 50 43 54 32 Шоколад "Аленка" 552 140 138 145 129 33 Шоколад "Альпен Голд" с ореха­ми и изюмом 240 45 72 69 54 34 Шоколад "Вдохновение" 132 30 35 31 36 35 Шоколад "Восторг" 108 26 20 32 30 36 Шоколад "Колокольня" пористый 12 4 2 4 2 37 Шоколад "Кофе с молоком" 168 40 35 50 43 38 Шоколад "Мишка косолапый" 264 79 70 70 45 39 Шоколад "Несквик" 84 18 21 22 23 40 Шоколад "Нестле Классик" 396 40 120 80 156 41 Шоколад "Путешествие" 60 12 10 23 15 42 Шоколад "Ретро" 312 72 68 79 93 43 Шоколад "Российский" 612 149 156 155 152 44 Шоколад "Рот-Фронт" 432 100 120 120 92 45 Шоколад "Сказки Пушкина" 144 30 40 39 35 46 Шоколад "Сударушка" 12 2 2 5 3 47 Шоколад "Воздушный" белый пористый 12 2 4 6 48 Шоколад с арахисом 36 6 12 14 4 49 Шоколад с кокосом 72 15 14 22 21 50 Шоколадный напиток "Несквик" 372 90 100 110 72 ИТОГО 36 696  ТЕХНОЛОГИЯ РАБОТЫ 1. Введите исходные данные в MS Excel и отсортируйте их по убыванию годовой реализации как это показано на рис.1. Рис.1 2. В столбце Н рассчитать долю отдельных пози­ций ассортимента в общей реализации. Не забудьте после выделения ячейки, содержащей итог годовой реализации нажать F4 и установить формат ячеек – процентный. Рис.2 3. Общее число позиций в нашем примере — 50. Следова­тельно, первая позиция упорядоченного списка составляет 2% от общего числа позиций. Две верхние позиции упоря­доченного списка составят 4% от общего числа позиций. На их долю в нашем примере приходится 31,03% всего оборо­та склада (20,54 + 10,50 = 31,03). Следуя данной логике, заполнить столбцы I и J, а затем по данным этих граф построить кривую ABC. Рис.3 4. Разделить анализируемый ассортимент на группы А, В и С. Среднестатистическое процентное соотношение групп А, В и С Группа Доля в ассортименте, % Доля в реализации, % А 20 80 В 30 15 С 50 5 Однако данное соотношение, подобно "средней темпе­ратуре по госпиталю", может не отражать специфики кон­кретного множества. Так, в нашем случае на долю 20% "ударного" ассортимента приходится лишь 70,31% реали­зации, а 80% реализации дают примерно 29% ассортимен­та. Как в этом случае поступить, включить в группу А 29% ассортимента или ограничить ее 20%? Ответ на этот вопрос получим, выполнив данную часть задания графическим ме­тодом определения границ групп А, В и С с помощью каса­тельной к кривой ABC. Краткое описание метода. На рис.4 представлена кривая ABC. Внимание! Кривая построена с целью пояснения метода на базе статистики, не имеющей отношения к нашему заданию. Соединим начало системы координат и конец графика прямой OD и затем проведем касательную к кривой ABC, параллельную линии OD. Абсцисса точки касания (точка М) покажет нам границу между группами А и В, а ордината укажет долю реализации продуктов группы А в общей реа­лизации. Соединим теперь точку М с концом кривой — точкой D, и проведем новую касательную к графику ABC, параллель­ную линии MD. Абсцисса точки касания (точка N) указывает границу между группами В и С, а ордината показывает сум­марную реализацию групп А и В в общей реализации. Процентное соотношение групп А, В и С, полученное методом касательной, представлено в табл. 2. Рис.4. Разделение ассортимента на группы А,В и С графическим способом Таблица 2. Процентное соотношение групп А, В и С ассортимента, представленного кривой на рис.4 Группа Доля в ассортименте, % Доля в реализации, % А 26 77 В 28 17 С 46 6 В ячейке К3 ввести формулу =ЕСЛИ(I3<=0,26;"A";ЕСЛИ(I3<=0,54;"B";"C")) либо воспользоваться функцией ЕСЛИ (рис. 5) и ввести данные. Рис. 5 Функция ЕСЛИ Скопировать эту формулу до конца таблицы. Рис. 6 В столбце К сделать условное форматирование. Как это сделать смотрите задачу «Излишки товарных запасов и условное форматирование» Задание 2 Определить величину снижения затрат на содержание запасов в результате использования дифференцированных нормативов среднего запаса. Методические указания Затраты на содержание запасов (Схран) определяются по формуле: Схран = Зср * Qоднодн * М, где Qоднодн — среднедневная реализация, определяемая как частное от деления годовой реализации на число рабочих дней в году, в нашем случае — 330 дн.; Зср — средний запас, дней, в нашем случае — 20 дн.; М — доля годовых затрат на хранение в стоимости сред­него запаса, в нашем случае — 0,3 1/год. Порядок проведения расчетов 1. Рассчитать размер затрат на содержание запаса в случае применения единых норм запасов для всего ассор­тимента. 2. Рассчитать размер затрат на содержание запаса в случае применения дифференцированных норм запасов для групп А, В и С. 2.1. Определить объем годового и среднедневного обо­рота по группам А, В и С. 2.2. Используя значения рекомендованных службой ло­гистики норм запасов (10, 20 и 30 дней для групп А, В и С соответственно), определить размеры запасов в сумме для продуктов групп А, В и С. 2.3. Определить общий размер запасов в сумме по все­му ассортименту компании в случае применения диффе­ренцированных норм запасов для групп А, В и С. 3. Определить, на какую величину снизятся затраты на содержание запасов в результате использования диффе­ренцированных нормативов среднего запаса. Задание 3 Дифференцировать ассортимент по методу XYZ. Анализ ABC позволил нам дифференцировать продук­ты на группы А, В и С по доли в реализации. XYZ-анализ выполним с целью разделения продуктов компании по признаку стабильности спроса. Отметим, что чем стабильнее спрос, тем меньше ошибки прогнозирова­ния, ниже потребность в страховых запасах, легче плани­рование движения продукта. Следовательно, методы управ­ления продуктами с разными показателями стабильности спроса могут иметь существенные различия.  ТЕХНОЛОГИЯ РАБОТЫ Признаком, на основе которого конкретную позицию ассортимента относят к группе X, Y или Z, является коэф­фициент вариации спроса (v) по этой позиции. Среди отно­сительных показателей вариации коэффициент вариации является наиболее часто применяемым показателем отно­сительной колеблемости. Рассчитать коэффициенты вариации спроса по от­дельным позициям ассортимента (v). где — значение спроса по оцениваемой позиции за i-й квартал; — среднеквартальное значение спроса по оценивае­мой позиции; n — число кварталов, за которые произведена оценка. 1. В столбце L рассчитать среднюю реализацию за квартал () Рис.7 2. В столбце М рассчитать коэффициент вариации . Для этого в ячейку М3 введите формулу =КОРЕНЬ(((D3-L3)^2+(E3-L3)^2+(F3-L3)^2+(G3-L3)^2)/4)/L3 Рис.8 3. Разделить анализируемый ассортимент на группы X, Y и Z. Таблица 3 Предлагаемый алгоритм разделения ассортимента на группы X, Y и Z Группа Интервал X Y Z В ячейке N3 ввести формулу =ЕСЛИ(M3<=0,1;"X";ЕСЛИ(M3<=0,25;"Y";"Z")) либо воспользоваться функцией ЕСЛИ и ввести данные. Скопировать ее до конца таблицы. Воспользоваться Условным форматированием. (Рис. 9) Рис.9 Для определения группы по двум признакам в ячейке О3 ввести функцию =СЦЕПИТЬ(K3;N3). Скопировать ее до конца таблицы. Воспользоваться Условным форматированием. Затем отсортировать по столбцу О в порядке возрастания. (Рис. 10) Рис. 10 Задание 4 Построить матрицу АВС-ХYZ-анализа, сделать пред­ложения по системам управления запасами для товарных позиций групп АХ, АY, AZ, а также группы В и группы С. Методические указания Матрица АВС-ХYZ составляется по форме табл. 4. В ячейки матрицы вписываются номера продуктов ( № позиции). Например, в ячейку АХ вписываются продукты, вошедшие в группу А при выполнении АВС-анализа и в группу X при выполне­нии ХYZ-анализа. Примечание. Предложения по системам управления запасами для товарных позиций формируются на базе изу­чения темы "Запасы в логистике", а также ряда других тем курса. Таблица 4 МАТРИЦА АВС-ХYZ АХ АY АZ ВХ ВY BZ СХ СY CZ Например, для товарных позиций, входящих в груп­пы АХ, АY и АZ, следует выработать индивидуальные технологии управления запасами. Для позиций, входящих в группу АХ, следует рассчитать оптимальный размер заказа. Позиции, входящие в группу АZ, следует контролиро­вать ежедневно. Очевидно, что в связи с большими колеба­ниями спроса здесь необходимо предусмотреть существен­ный страховой запас. Управление запасами по позициям, входящим в группы ВХ, ВY и ВZ, может осуществляться как по одинаковым, так и индивидуальным технологиям (как по срокам пла­нирования, так и по способам доставки). Планирование запасов по товарным позициям, входя­щим в группы СХ, СY и СZ, может осуществляться на более длительный период, например на квартал, с ежене­дельной (или ежемесячной) проверкой наличия запаса на складе. УПРАВЛЕНИЕ ЗАПАСАМИ В ЛОГИСТИКЕ Определение оптимального размера заказа Под оптимальным заказом понимается такой размер, при котором обеспечивается минимальная сумма затрат на оформление заказа и хранение запасов. Оптимальный размер заказа по критерию минимизации совокупных затрат на хранение запаса и повторение заказа рассчитывается по формуле Вилсона (Wilson): где Q* — оптимальный размер заказа, шт.; А — затраты на поставку единицы заказываемого продукта, руб.; S — потребность в заказываемом продукте за определенный пе­риод, шт.; I — затраты на хранение единицы запаса, руб./шт. План годового выпуска продукции производственного предприя­тия составляет 800 единиц, при этом на каждую единицу готовой продукции требуется 2 единицы комплектующего изделия КИ-1. Известно, что стоимость подачи одного заказа составляет 200 у.е., цена единицы комплектующего изделия — 480 у.е., а стоимость содержания комплектующего изделия на складе составляет 15% его цены. Требуется определить оптимальный размер заказа на комплек­тующее изделие КИ-1. Решение Используя формулу, определяем оптимальный размер зака­за по имеющимся исходным данным: Округление оптимального размера заказа в большую сторону помогает избежать дефицита комплектующего изделия. Таким образом, оптимальный размер заказа составляет 95 шт. Бензонасос № п/п Показатель Расчет 11 Потребность, шт. 10000 22 Оптимальный размер заказа, шт. 197 33 Время поставки, дн. 6 44 Возможное время задержки поставки, дн. 4 55 Ожидаемое дневное потребление шт./дн. 28 66 Срок расходования запасов, дн. 7 77 Ожидаемое потребление за время поставки 167 88 Максимальное потребление за время поставки, шт. 278 99 Страховой запас, шт. 111 110 Пороговый уровень запасов, шт. 278 111 Максимально желательный объем запасов, шт. 309 112 Срок расходования запасов до порогового уровня, дн. 1 Проведем графическое моделирование работы системы с фиксированным размером заказа: 1. При отсутствии сбоев в поставке 2. При однократном сбое в поставках 3. При неоднократных сбоях в поставках 1. Бензонасос № п/п Показатель Порядок расчета 1 Потребность, шт. 10000 2 Интервал поставки, дн. 7 3 Время поставки, дн. 6 4 Возможное время задержки поставки, дн. 4 5 Ожидаемое дневное потребление, шт./дн. 28 6 Ожидаемое потребление за время поставки, шт. 167 7 Максимальное потребление за время поставки, шт. 278 8 Страховой запас, шт. 111 9 Максимально желательный объем запасов, шт. 309 10 Размер заказа, шт. РЗ=МЖЗ-ТЗ+ОП Проведем графическое моделирование работы системы с фиксированным интервалом времени между заказами: 1. При отсутствии сбоев в поставке 2. При однократном сбое в поставках 3. При неоднократных сбоях в поставках Решение транспортной задачи в Microsoft Excel с помощью Поиска решения Проверить в Сервисе (во вкладке «Данные» в группе «Анализ») наличие функции Поиска решения. При отсутствии данной функции ее необходимо установить следующим образом: • для MS Excel 2003 Пункт меню Сервис → Надстройки → Поиск решения → ОК. • для MS Excel 2007 Кнопка “Office” → Параметры Excel → Надстройки → Управление: Надстройки Excel → Перейти → Поиск решения → ОК. Предположим, что имеется 4 поставщика и 3 потребителя. Производственные возможности поставщиков 1000, 1400, 1600 и 2000 единиц продукции, соответственно. Потребности потребителей в 1000, 3000 и 2000 единиц продукции, соответственно. Стоимость перевозки единицы продукции с фабрик в пункты распределения приведена на рисунке. Необходимо так спланировать перевозки, чтобы минимизировать суммарные транспортные расходы. Рисунок 2  ТЕХНОЛОГИЯ РАБОТЫ Для решения этой задачи с помощью средства поиска решений введем данные, как показано на рис. 3 В ячейки B3:D6 введены стоимости перевозок. Ячейки B10:D13 отведены под значения неизвестных (объемы перевозок). В ячейки F10:F13 введены объемы производства у поставщиков, а в ячейки B15:D15 введена потребность в продукции у потребителей. В ячейку B17 введена целевая функция =СУММПРОИЗВ(B3:D6; B10:D13) Рисунок 3 В ячейки B7:D7 введены формулы =СУММПРОИЗВ(B3:B6; B10:B13) =СУММПРОИЗВ(C3:C6; C10:C13) =СУММПРОИЗВ(D3:D6; D10:D13) В ячейки B14:D14 введены формулы =СУММ(B10:B13) =СУММ(C10:C13) =СУММ(D10:D13) определяющие объем продукции, ввозимой потребителям. В ячейки E10:E13 введены формулы =СУММ(B10:D10) =СУММ(B11:D11) =СУММ(B12:D12) =СУММ(B13:D13) вычисляющие объем продукции, вывозимой от поставщиков. Теперь выберем команду Сервис (вкладка Данные), Поиск решения и заполним открывшееся диалоговое окно Поиск решения, как показано на рис. 4. Не забудьте в диалоговом окне Параметры поиска решения установить флажок Линейная модель. После нажатия кнопки <Выполнить> средство поиска решений находит оптимальный план поставок продукции и соответствующие ему транспортные расходы (рис. 63). Рисунок 4 Рисунок 5 Решение открытой транспортной задачи • Уравнение баланса является обязательным условием решения транспортной задачи. Поэтому, когда в исходных условиях дана открытая задача, то ее необходимо привести к закрытой форме. ◦ В случае если потребности по пунктам назначения превышают запасы пунктов отправления, то вводится фиктивный поставщик с недостающим объемом потребления. ◦ Если запасы поставщиков превышают потребности потребителей, то вводится фиктивный потребитель с необходимым объемом потребления. • Варианты, связывающие фиктивные пункты с реальными, имеют нулевые оценки. • После введения фиктивных пунктов задача решается как закрытая. Примеры открытых транспортных задач • Суммарные потребности потребителей превышают суммарные мощности поставщиков • Суммарные мощности поставщиков превышают суммарные запросы потребителей Рисунок 6 Рисунок 7