Закупівельна логістика (Оптимізація закупівель засобами Microsoft Excel)

Лабораторна робота № 3

Тема: Закупівельна логістика (Оптимізація закупівель засобами Microsoft Excel).

Мета роботи: набуття практичних навичок щодо застосування надбудови Microsoft Excel Пошук рішення для вирішення питання оптимізації закупівель.

Завдання: вирішення завдання оптимізації закупівель з використанням надбудови Microsoft Excel Пошук рішення.

Методичні рекомендації:

Підприємство рано чи пізно постає перед дилемою – намагатися максимально догодити споживачам і тримати широкий асортимент і великий товарний запас або стежити за рентабельністю вкладень в асортимент і склад з тим, щоб можна було далі розвиватися. Існує достатня кількість методів, що дозволяють аналізувати асортимент і товарні запаси, найбільш відомими серед яких є ABC і XYZ-аналіз.

При виконанні ABC і XYZ-аналізу засобами Excel доцільно об'єднати в одній робочій книзі всі розрахунки, але для наочності можна розподілити їх на кількох аркушах. На рис. 1.1 показаний лист вихідних даних для проведення аналізу.

Надалі необхідно використовувати значення «частка в загальному запасі», тому можна окремо обчислити ці значення. В даному прикладі обчислення проводяться в комірках стовпчика C, наприклад, осередок C5 містить формулу «= (B5 / $ B $ 15) 100», в осередку B15, використовуваної в цій формулі, знаходиться загальна величина запасу, яка обчислюється за формулою «= СУММ (B5: B14)».

Рис. 1.1. Вихідні дані для аналізу

На рис. 1.2 показані результати подальших обчислень. дані, що знаходяться в осередках стовпців A, B і C, були скопійовані з попереднього 20 листа. Далі до осередків C5: C14 була застосована операція сортування (вкладка Головна → Сортування і фільтр → Сортування від максимального до мінімального). Після виконання сортування дані в осередках розміщуються в іншому порядку, тому потрібно додати нові номери товарів, отримані при сортуванні в порядку зменшення частки в загальному запасі (стовпець D).

У осередок E5 вводиться формула обчислення частки даного товару наростаючим підсумком = СУММ ($ C $ 5: $ C5). Далі ця формула копіюється в інші осередки стовпчика, наприклад, в осередку E6 при цьому буде знаходитися формула = СУММ ($ C $ 5: $ C6).

Для визначення групи товарів в осередку стовпця F вводяться складніші

формули. Наприклад, в комірці F5 використовується формула: «= ЕСЛИ (E5 <= 70;" A "; ЯКЩО (E5 <= 90;" B ";" C "))».

В даному прикладі критерії розподілу по групах (70 і 90%) вказуються у формулі в явному вигляді. При необхідності можна передбачити більш універсальний варіант, коли ці величини заносяться в якісь осередки разом з іншими вихідними даними, а у формулі замість конкретних числових значень використовуються адреси цих осередків.

Рис. 1.2. Результати ABC-аналізу

Результати обчислень можуть бути зображені в графічному вигляді. Для цього необхідно виділити відповідний стовпець таблиці (осередки E4: E14) і на вкладці Вставка в групі Діаграми натиснути кнопку Графік. Після того, як буде обраний один з варіантів графіка, він буде вставлений на лист Excel, і його можна буде перемістити в потрібне місце (рис. 1.3). При цьому, в якості назви графіка буде використовуватися найменування виділеного стовпця таблиці. Необхідно пам'ятати, що номери товарних позицій на цьому графіку відповідають новій нумерації товарів.

Рис. 1.3. Графічні результати ABC-аналізу

На рис. 1.4 представлені результати XYZ-аналізу. При його виконанні на

лист Excel були скопійовані вихідні дані (стовпці A, B, C, D, E). В комірках стовпчика F обчислюється середньоквартальна