Задачи по логистики. Решение задач с помощью ms excel 46
 Скачать 7.4 Mb.
|
Управление запасами с применением анализа АВС и 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
ТЕХНОЛОГИЯ РАБОТЫ 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% "ударного" ассортимента приходится лишь 70,31% реализации, а 80% реализации дают примерно 29% ассортимента. Как в этом случае поступить, включить в группу А 29% ассортимента или ограничить ее 20%? Ответ на этот вопрос получим, выполнив данную часть задания графическим методом определения границ групп А, В и С с помощью касательной к кривой ABC. Краткое описание метода. На рис.4 представлена кривая ABC. Внимание! Кривая построена с целью пояснения метода на базе статистики, не имеющей отношения к нашему заданию. Соединим начало системы координат и конец графика прямой OD и затем проведем касательную к кривой ABC, параллельную линии OD. Абсцисса точки касания (точка М) покажет нам границу между группами А и В, а ордината укажет долю реализации продуктов группы А в общей реализации. Соединим теперь точку М с концом кривой — точкой D, и проведем новую касательную к графику ABC, параллельную линии MD. Абсцисса точки касания (точка N) указывает границу между группами В и С, а ордината показывает суммарную реализацию групп А и В в общей реализации. Процентное соотношение групп А, В и С, полученное методом касательной, представлено в табл. 2.   Рис.4. Разделение ассортимента на группы А,В и С графическим способом Таблица 2. Процентное соотношение групп А, В и С ассортимента, представленного кривой на рис.4
В ячейке К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
В ячейке 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, следует выработать индивидуальные технологии управления запасами. Для позиций, входящих в группу АХ, следует рассчитать оптимальный размер заказа. Позиции, входящие в группу АZ, следует контролировать ежедневно. Очевидно, что в связи с большими колебаниями спроса здесь необходимо предусмотреть существенный страховой запас. Управление запасами по позициям, входящим в группы ВХ, ВY и ВZ, может осуществляться как по одинаковым, так и индивидуальным технологиям (как по срокам планирования, так и по способам доставки). Планирование запасов по товарным позициям, входящим в группы СХ, СY и СZ, может осуществляться на более длительный период, например на квартал, с еженедельной (или ежемесячной) проверкой наличия запаса на складе. УПРАВЛЕНИЕ ЗАПАСАМИ В ЛОГИСТИКЕ Определение оптимального размера заказа Под оптимальным заказом понимается такой размер, при котором обеспечивается минимальная сумма затрат на оформление заказа и хранение запасов.  Оптимальный размер заказа по критерию минимизации совокупных затрат на хранение запаса и повторение заказа рассчитывается по формуле Вилсона (Wilson):  где Q* — оптимальный размер заказа, шт.; А — затраты на поставку единицы заказываемого продукта, руб.; S — потребность в заказываемом продукте за определенный период, шт.; I— затраты на хранение единицы запаса, руб./шт. План годового выпуска продукции производственного предприятия составляет 800 единиц, при этом на каждую единицу готовой продукции требуется 2 единицы комплектующего изделия КИ-1. Известно, что стоимость подачи одного заказа составляет 200 у.е., цена единицы комплектующего изделия — 480 у.е., а стоимость содержания комплектующего изделия на складе составляет 15% его цены. Требуется определить оптимальный размер заказа на комплектующее изделие КИ-1. Решение Используя формулу, определяем оптимальный размер заказа по имеющимся исходным данным:  Округление оптимального размера заказа в большую сторону помогает избежать дефицита комплектующего изделия. Таким образом, оптимальный размер заказа составляет 95 шт. Бензонасос
Проведем графическое моделирование работы системы с фиксированным размером заказа: 1. При отсутствии сбоев в поставке  2. При однократном сбое в поставках  3. При неоднократных сбоях в поставках  Бензонасос
Проведем графическое моделирование работы системы с фиксированным интервалом времени между заказами: 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 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
