Задачи по логистики. Решение задач с помощью ms excel 46

• Название: Решение задач с помощью ms excel 46
• Анкор: Задачи по логистики
• Дата: 14.05.2022
• Размер: 7.4 Mb.
• Имя файла: d0bcd0b5d182d0bed0b4d0b8d187d0bad0b0-d0b4d0bbd18f-d0bfd180d0bed0.docx
• Тип: Решение; #528805
• страница: 8 из 15

Решение задач с помощью 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, т.к. оборачиваемость рассчитана на месяц (февраль).

Какойиз двух методов применять, существенного зна­чения не имеет. Другое дело, если вам необходимо срав­нить две оборачиваемости, например свою и среднеот­раслевую. В этом случае важно, чтобы оборачиваемости были рассчитаны по одному методу.