Початковий запас матеріалів і кількість матеріалів, придбаних

протягом звітного періоду

Назва запасу	Код запасу	Запас на 01.10.ХХ (кількість)	Придбано запасів (кількість)	Дата придбання	Вартість одиниці придбаного запасу, грн.
1	2	3	4	5	6

Таблиця 8

Відпуск матеріалів у виробництво протягом звітного періоду

Назва запасу	Код запасу	Відпущено у виробництво (кількість)	Вартість одиниці запасу, грн.
1	2	3	4

Таблиця 9

Вартість матеріалів, відпущених у виробництво і їх залишок у кінці

звітного періоду

Назва запасу	Код запасу	Вар- тість одиниці запасу	Запас на 01.10.ХХ	Кіль-кість придба-них запасів	Вартість товарів, що відпускати­муться у виробництво протягом місяця, грн.	Відпу-щено у вироб-ництво (кіль-кість)	Кінцевий запас (кіль-кість)	Вартість відпущених у вироб-ництво запасів, грн.
1	2	3	4	5	6	7	8	9

Для визначення кількості матеріалів, що відправлені у виробництво протягом місяця слід скористатися формулою масиву, що виконує декілька обчислень і повертає декілька значень. Формули масиву зазвичай обробляють один і декілька наборів аргументів, котрі називаються аргументами масиву. При цьому всі аргументи повинні бути прямокутними і мати однакову кількість рядків та стовпців. Щоб вернути декілька значень, формулу необхідно ввести в декілька комірок.

При вводі формули масиву Microsoft Excel автоматично помістить її в фігурні дужки ({}).

Щоб ввести формулу масиву необхідно:

 якщо формула масиву повертає одне значення, слід вказати комірку, в котру необхідно ввести формулу;

 якщо формула масиву повертає декілька значень, необхідно виділити діапазон комірок, в котрі необхідно ввести формулу;

 набрати формулу;

 натиснути клавіші CTRL+SHIFT+ENTER.

Щоб змінити формулу масиву необхідно:

 вказати будь-яку комірку масиву, що містить формулу;

 вказати рядок формул. При переході в рядок формул фігурні дужки ({}) щезнуть;

 змінити формулу;

 натиснути клавіші CTRL+SHIFT+ENTER.

Для полегшення розуміння формули, яка використовуватиметься для визначення кіль­кості матеріалів, що відправлені у виробництво протягом місяця, можна скористатися поімено­ваними комірками та діапазонами. Щоб присвоїти ім’я комірці чи діапазону необхідно виділи­ти її в робочому листі, а далі виконати команду Вставка  Имя  Присвоить і ввести ім’я в поле Имя.

Зважаючи на все наведене вище, для отримання результатів з вартості відпущених матеріалів у виробництво за методом ідентифікаційної собівартості відповідної одиниці запасів потрібно скористатися такими порадами:

1.1. Всі три таблиці, необхідні для виконання завдання, потрібно побудувати і розмістити на різних листах книги Excel;

1.2. Табл. 7, табл. 8 і стовпці 1 – 5 табл. 9 заповнюються вручну, враховуючи дані задачі та індивідуальний варіант;

1.3. “Вартість товарів, що відпускатимуться у виробництво протягом місяця, грн.” (стов­пець 6 табл. 9) являє собою добуток суми вхідного залишку запасів і кількості придбаних запасів протягом місяця на вартість одиниці запасу;

1.4. “Відпущено у виробництво (кількість)” (стовпець 7 табл. 9) являє собою формулу масиву, яка підсумовує кількість відпущених у виробництво запасів з табл. 8 по коду і вартості одиниці запасу і переносить її у відповідну комірку табл. 9.

Тобто, формула масиву матиме вигляд

{=СУММ(([Код запасу з таблиці 9]=[Діапазон, що містить коди

запасу в таблиці 8])*([Вартість одиниці запасу з таблиці 9]=

[Діапазон, що містить вартість одиниці запасу з таблиці 8])*

Відпущено у виробництво (кількість)} (14)

1.5. “Кінцевий запас (кількість)” (стовпець 8 табл. 9) є різницею між сумою початкового залишку і кількістю придбаних запасів та кількістю запасів, відпущених у виробництво;

1.6. “Вартість відпущених у виробництво запасів, грн.” (стовпець 9 табл. 9) є добутком вартості одиниці запасів на кількість запасів, відпущених у виробництво.

2. Метод середньозваженої собівартості (середньої вартості).

Якщо компанія, котра має деякий товарно-матеріальний запас, одиниці котрого можна придбати за різною ціною, немає ніякої можливості визначити, котрі саме одиниці були відправлені у виробництво або процес даного визначення є достатньо трудомісткий і пов’яза­ний з серйозними витратами часу та коштів, вона (компанія) може використати метод списання запасів за середньозваженою собівартістю (середньою вартістю).

Цей метод припускає, що середньозважене вартостей всіх одиниць одного виду запасу в початковому товарно-матеріальному запасі – це і є вартість відпущеного у виробництво матеріалу.

Рекомендації для визначення вартості списаних у виробництво запасів протягом звітного періоду є такі:

2.1. Перейменуйте Лист 1 створеної Вами книги на ‘Вересень 20ХХ’, а Лист 2 на ‘Жовтень 20ХХ’. На цих листах будуть міститися дані про наявність та рух виробничих запасів по складу підприємства за місяць;

2.2. Створіть на листі ‘Вересень 20ХХ’ таблицю “Відомість наявності виробничих запасів на складі” за вересень 20ХХ р. за формою, зображеною у вигляді табл. 10;

2.3. Створіть на листі ‘Вересень 20ХХ’ таблицю “Рух виробничих запасів по складу” за формою, зображеною у вигляді табл. 11. Дану таблицю слід розмістити праворуч від таблиці “Відомість наявності виробничих запасів на складі”;

2.4. Таблиця “Рух виробничих запасів по складу” заповнюється, враховуючи умови завдання та варіанта студента, зважаючи на те, що у вересні 20ХХ р. відпуску виробничих запасів у виробництво не було;

Таблиця 10

Відомість наявності виробничих запасів на складі

№ з/п	Назва запа-су	Код запа-су	Оцінка собівар-тості	Запас на 01.09.ХХ		Придбано за вересень 20ХХ р.		Відпущено за вересень 20ХХ р.			Залишок на 30.10.ХХ
				Кількість	Вартість	Кількість	Вартість	Кількість	Вартість одиниці	Вартість всього	Кількість	Вартість
1	2	3	4	5	6	7	8	9	10	11	12	13

Таблиця 11

Рух виробничих запасів по складу

Придбано <дата придбання>		Вартість відпущених у виробництво запасів, грн.	Залишок
Кількість	Вартість, гн.		Кількість	Вартість, грн.
1	2	3	4	5

2.5. Таблицю “Відомість наявності виробничих запасів на складі” за вересень 20ХХ р. слід заповнювати, враховуючи умови завдання та варіанта студента, використовуючи дані таблиці “Рух виробничих запасів по складу”, зважаючи на те, що вхідного залишку виробничих запасів на 01.09.20ХХ р. не було. У стовпці 4 таблиці вказується метод списання запасів введенням символів, що однозначно ідентифікує певний метод (СЗС – середньозваженої собі­вартості, ФІФО, ЛІФО);

2.6. Створіть на листі ‘Жовтень 20ХХ’ таблицю “Відомість наявності виробничих запасів на складі” за жовтень 20ХХ р. за формою, аналогічною до табл. 10, лише зі зміною звітного періоду на жовтень 20ХХ року;

2.7. Стовпці 2, 3, 9 (назва товару, код товару, кількість виробничих запасів відпущених) таблиці “Відомість наявності виробничих запасів на складі” за жовтень 20ХХ р. заповнюються вручну, враховуючи умови завдання та варіанта;

2.8. Створіть на листі ‘Жовтень 20ХХ’ таблиці “Рух виробничих запасів по складу” за жовтень 20ХХ р. за формою, зображеною у вигляді табл. 11. Таких таблиць треба створити стільки, скільки відбулося надходжень запасів протягом місяця жовтня 20ХХ р. Дані таблиці складатимуться з однотипних блоків, кожен з яких має містити інформацію про виробничі запаси, придбані протягом одного дня (стовпці 1 та 2);

2.9. У комірки графи “Кількість” (стовпець 5) таблиці “Відомість наявності виробничих запасів на складі” за жовтень 20ХХ р. треба ввести формулу, за допомогою якої б автоматично записувалася кількість одиниць виробничих запасів, що є на складі, на перше число звітного місяця (автоматичне формування вхідних залишків).

Рекомендації до створення формули. Необхідно використати формулу масиву, яка б вибирала в рядку, що містить дати придбання виробничих запасів комірки, в яких записана дата, менша 01.10.ХХ р. (тобто день останнього оприбуткування виробничих запасів, поперед­нього перед звітним, періоду), після чого вибирала числове дане, що стосується кількості придбаних виробничих запасів (при цьому як ще один аргумент у формулі можна використати або число 1 або слово ‘Кількість’) і копіювала його у відповідну комірку таблиці “Відомість наявності виробничих запасів на складі” за жовтень 20ХХ р.

Тобто загальний вигляд формули є такий:

{=СУММ(([Діапазон комірок, що містять дати придбання

виробничих запасів] < [Комірка, що містить дату внесення

вхідного залишку])*([Діапазон комірок, що містять номери

стовпців таблиць]=1)*[Діапазон комірок, що містить числові

дані таблиць])} (15)

2.10. У комірки графи “Вартість” (стовпець 6) таблиці “Відомість наявності виробничих запасів на складі” за жовтень 20ХХ р. слід ввести формулу, за допомогою якої б автоматично записувалася вартість одиниць виробничих запасів, що є на складі, на перше число звітного місяця (автоматичне формування вхідних залишків).

Рекомендації до створення формули. Процедура створення аналогічна запропонованій у пункті 2.9. Необхідно знову використати формулу масиву, яка б вибирала в рядку, що містить дати придбання виробничих запасів комірки, в яких записана дата, менша 01.10.ХХ р. (тобто день останнього оприбуткування виробничих запасів, попереднього перед звітним, періоду), після чого вибирала числове дане, в даному випадку те, що стосується вартості придбаних виробничих запасів (при цьому як ще один аргумент в формулі можна використати або число 2 або слово ‘Вартість’) і копіювала його у відповідну комірку таблиці “Відомість наявності виробничих запасів на складі” за жовтень 20ХХ р.

Загальний вигляд формули такий:

{=СУММ(([Діапазон комірок, що містять дати придбання

виробничих запасів] < [Комірка, що містить дату внесення

вхідного залишку])*([Діапазон комірок, що містять номери

стовпців таблиць]=2)*[Діапазон комірок, що містить числові

дані таблиць])} (16)

2.11. У комірки графи “Кількість” (стовпець 7) таблиці “Відомість наявності виробничих запасів на складі” за жовтень 20ХХ р. треба ввести формулу, за допомогою якої б автоматично записувалася кількість одиниць виробничих запасів, придбаних за звітний місяць.

Рекомендації до створення формули. Використаємо формулу масиву, яка б вибирала в рядку, що містить дати придбання виробничих запасів комірки, в яких записана дата, більша або яка дорівнює 01.10.ХХ р. (тобто дні оприбуткування виробничих запасів звітного періоду), після чого вибирала числові дані, що стосуються кількості придбаних виробничих запасів (при цьому як ще один аргумент у формулі можна використати або число 1 або слово ‘Кількість’), підсумовувала їх і копіювала дану суму у відповідну комірку таблиці “Відомість наявності виробничих запасів на складі” за жовтень 20ХХ р.

{=СУММ(([Діапазон комірок, що містять дати придбання

виробничих запасів] >= [Комірка, що містить дату внесення

вхідного залишку])*([Діапазон комірок, що містять номери

стовпців таблиць]=1)*[Діапазон комірок, що містить числові

дані таблиць])} (17)

2.12. У комірки графи “Вартість” (стовпець 8) таблиці “Відомість наявності виробничих запасів на складі” за жовтень 20ХХ р. слід ввести формулу, за допомогою якої б автоматично записувалася вартість (загальна) одиниць виробничих запасів, придбаних за звітний місяць.

Рекомендації до створення формули. Процедура створення аналогічна запропонованій у пункті 2.11. Необхідно знову використати формулу масиву, яка б вибирала в рядку, що містить дати придбання виробничих запасів комірки, в яких записана дата, більша або яка дорівнює 01.10.ХХ р. (тобто дні оприбуткування виробничих запасів звітного періоду), після чого вибирала числові дані, що стосуються вартості придбаних виробничих запасів (при цьому як ще один аргумент в формулі можна використати або число 2 або слово ‘Вартість’), підсумо­вувала їх і копіювала дану суму у відповідну комірку таблиці “Відомість наявності виробничих запасів на складі” за жовтень 20ХХ р.

{=СУММ(([Діапазон комірок, що містять дати придбання

виробничих запасів] >= [Комірка, що містить дату внесення

вхідного залишку])*([Діапазон комірок, що містять

номери стовпців таблиць]=2)*[Діапазон комірок, що

містить числові дані таблиць])} (18)

2.13. У комірки графи “Вартість одиниці” (стовпець 10) таблиці “Відомість наявності виробничих запасів на складі” за жовтень 20ХХ р. треба ввести формулу, за допомогою якої б автоматично розраховувалася середня вартість відпущених у виробництво виробничих запасів. У чисельнику даної формули повинна міститися сума залишку вартості виробничих запасів на 01.10.ХХ р. і вартість придбаних запасів протягом звітного періоду, а в знаменнику – сума залишку по кількості виробничих запасів та кількість придбаних запасів протягом звітного місяця. Дана комірка заповнюється лише при використанні методу середньозваженої собівар­тості (середньої вартості).

2.14. У комірки графи “Вартість всього” (стовпець 11) таблиці “Відомість наявності виробничих запасів на складі” за жовтень 20ХХ р. треба ввести формулу, за допомогою якої б автоматично розраховувалася загальна вартість відпущених у виробництво запасів протягом місяця. Дані в комірках графи – це результат від множення стовпця 9 на стовпець 10 (тобто добуток кількості та вартості одиниці).

2.15. У комірки графи “Кількість” (стовпець 12) таблиці “Відомість наявності виробничих запасів на складі” за жовтень 20ХХ р. необхідно ввести формулу для визначення залишку виробничих запасів на останній день місяця з урахуванням надходжень та відпуску у вироб­ництво. Тобто ця графа являє собою різницю між сумою кількісного надходження певного виду запасу і залишку цього ж запасу на початок місяця (стовпець 7 + стовпець 5) та відпуском його у виробництво (стовпець 9);

2.16. У комірки графи “Вартість” (стовпець 13) таблиці “Відомість наявності виробничих запасів на складі” за жовтень 20ХХ р. необхідно ввести формулу для визначення залишку виробничих запасів на останній день місяця з урахуванням надходжень та відпуску у вироб­ництво у вартісних показниках. Тобто ця графа являє собою різницю між сумою вартісного надходження певного виду запасу і залишку цього ж запасу на початок місяця (стовпець 8 + стовпець 6) та відпуском його у виробництво (стовпець 10);

2.17. У комірку для введення дати таблиці “Рух виробничих запасів по складу” за жовтень 20ХХ р. треба записати формулу, що забезпечувала б перенесення даних з попереднього місяця. Дана формула повинна мати такий вигляд:

=`Вересень20ХХ`! [Дата останнього оприбуткування

виробничих запасів у попередньому

звітному періоді] (18)

2.18. У наступну комірку, що знаходиться поряд (праворуч) з коміркою введення дати “Рух виробничих запасів по складу” за жовтень 20ХХ р. треба записати формулу копіювання дати останнього оприбуткування виробничих запасів у попередньому звітному періоді. Дублю­вання дат у двох суміжних клітинках є обов’язковою умовою для автоматизації підрахунку загальної кількості і вартості виробничих запасів;

2.19. У комірки графи “Кількість” (стовпець 1) таблиці “Рух виробничих запасів по складу” за жовтень 20ХХ р. треба записати формулу, що забезпечувала б перенесення даних про кількість залишку виробничих запасів на кінець попереднього звітного періоду. Ця форму­ла повинна мати такий вигляд:

=`Вересень 20ХХ`![Кількість виробничих запасів на

складі станом на останнє число попереднього перед

звітним місяця] (19)

2.20. У комірки графи “Вартість” (стовпець 2) таблиці “Рух виробничих запасів по складу” за жовтень 20ХХ р. треба записати формулу, що забезпечувала б перенесення даних про вартість залишку виробничих запасів на кінець попереднього звітного періоду. Ця формула повинна мати такий вигляд:

=`Вересень 20ХХ`![Вартість виробничих запасів на

складі станом на останнє число попереднього

перед звітним місяця] (20)

2.21. При використанні методу середньозваженої собівартості при списанні запасів стовпці 3, 4, 5 (вартість проданих запасів, кількість, вартість) блоків таблиці “Рух виробничих запасів по складу” за жовтень 20ХХ р. заповнювати не потрібно, дані графи використовуються для методів ЛІФО та ФІФО.

3. Методи ЛІФО та ФІФО.

FIFO (“first-in, first-out” – “першим прибув, першим вибув”) – це метод оцінки товарно-матеріального запасу, котрий, подібно до методу точного визначення (ідентифікаційної собівартості відповідної одиниці запасів), працює з фактичною вартістю одиниці матеріального запасу. На відміну від методу середньозваженої собівартості (середньої вартості), метод ФІФО оперує вартістю запасу, по котрій його було відпущено у виробництво.

Метод ФІФО припускає, що перша одиниця запасу, відпущена у виробництво або реалі­зована протягом звітного місяця, має вартість, яка дорівнює вартості першої одиниці запасу, що придбана на склад підприємства. Отже, матеріал, що першим надійшов на підприємство, першим йде на виробництво чи продаж.

У методі LIFO (“last-in, first-out” – “останнім прибув, першим спожитий”) загалом меха­нізм такий же, як і в методі FIFO. Різниця в тому, що вартість відпущених у виробництво або реалізованих запасів припускається такою, що дорівнює вартості одиниць запасу, котрі надійш­ли в початковий товарно-матеріальний запас останніми.

Для визначення вартості запасів, що відпускаються у виробництво, за даними двома методами необхідно скористатися такими рекомендаціями:

3.1. Перша частина таблиці “Відомість наявності виробничих запасів на складі” за жов­тень 2003 р. при використанні методів ЛІФО та ФІФО (стовпці 1 – 9) заповнюється аналогічно як при використанні методу середньозваженої собівартості.

3.2. Комірки графи “Вартість одиниці” таблиці “Відомість наявності виробничих запасів на складі” за жовтень 20ХХ р. при використанні методів ЛІФО та ФІФО не заповнюються;

3.3. У комірках графи “Вартість всього” (стовпець 11) таблиці “Відомість наявності виробничих запасів на складі” за жовтень 20ХХ р. вводиться формула масиву, яка повертала б суму всіх відпущених у виробництво запасів у вартісному виразі протягом звітного місяця.

Рекомендації до створення формули. Використаємо формулу масиву, яка б вибирала в рядку, що містить номери стовпців таблиці “Рух виробничих запасів по складу” за жовтень 20ХХ р. число 3 (тобто стовпець “Вартість відпущених у виробництво запасів”) і підсумову­вала числові дані вартості відпущених у виробництво запасів протягом звітного місяця.

Загалом формула повинна мати такий вигляд:

{СУММ(ЕСЛИ([Діапазон комірок, що містять номери

стовпців таблиць]=3; [Діапазон комірок, що містить

числові дані таблиць]))} (21)

3.4. У комірках графи “Кількість” (стовпець 12) таблиці “Відомість наявності виробничих запасів на складі” за жовтень 20ХХ р. вводиться формула масиву, яка повертала б суму всіх залишків виробничих запасів у кількісному виразі протягом звітного місяця.

Рекомендації до створення формули. Використаємо формулу масиву, яка б вибирала в рядку, що містить номери стовпців таблиці “Рух виробничих запасів по складу” за жовтень 20ХХ р. число 4 (тобто стовпець “Кількість” (по залишках)) і підсумовувала числові дані кількості залишків виробничих запасів протягом звітного місяця.

Загалом формула повинна мати такий вигляд:

{СУММ(ЕСЛИ([Діапазон комірок, що містять

номери стовпців таблиць]=4; [Діапазон комірок,

що містить числові дані таблиць]))} (22)

3.5. У комірках графи “Вартість” (стовпець 13) таблиці “Відомість наявності виробничих запасів на складі” за жовтень 20ХХ р. вводиться формула масиву, яка повертала б суму всіх залишків виробничих запасів у вартісному виразі протягом звітного місяця.

Рекомендації до створення формули. Процедура створення формули аналогічна запропо­нованій у двох попередніх пунктах. Знову ж таки використаємо формулу масиву, яка б виби­рала в рядку, що містить номери стовпців таблиці “Рух виробничих запасів по складу” за жовтень 20ХХ р. число 5 (тобто стовпець “Вартість” (по залишках)) і підсумовувала числові дані вартості залишків виробничих запасів протягом звітного місяця.

Загалом формула повинна мати такий вигляд:

{СУММ(ЕСЛИ([Діапазон комірок, що містять

номери стовпців таблиць]=5; [Діапазон комірок,

що містить числові дані таблиць]))} (23)

3.6. Комірки перших двох стовпців кожного з блоків таблиці “Рух виробничих запасів по складу” за жовтень 20ХХ р. заповнюються аналогічно, як і при використанні методу середньо­зваженої собівартості (середньої вартості).

3.7. У комірки графи “Вартість відпущених у виробництво запасів” треба ввести формулу, яка б визначала вартість запасів, відпущених у виробництво множенням кількості таких запасів на вартість їх одиниці.

3.8. У комірки графи “Кількість” необхідно ввести формулу для визначення залишку виробничих запасів після відпущення певної їх кількості у виробництво. Дана формула буде різна залежно від методу списання запасів.

Рекомендації до створення формул:

 при використанні методу ФІФО:

Використаємо формулу масиву, яка б повертала значення кількості перших за датою оприбуткування запасів як списаних у виробництво.

Формула матиме такий вигляд:

{=ЕСЛИ(([Кількість відпущених у виробництво запасів] СУММ(([Діапазон комірок, що містять номери стовпців таблиць]=1)*[Діапазон комірок, що містить числові дані таблиць])); 0;ЕСЛИ([Кількість відпущених у виробництво запасів]+[Кількість останніх придбаних запасів у минулому звітному періоді]>СУММ([Діапазон комірок, що містять номери стовпців таблиць]=1)*[Діапазон комірок, що містить числові дані таблиць]); СУММ(([Діапазон комірок, що містять номери стовпців таблиць]=1)*[Діапазон комірок, що містить числові дані таблиць])- [Кількість відпущених у виробництво запасів]; [Кількість останніх придбаних запасів у минулому звітному періоді]))} (24)

 при використанні методу ЛІФО:

Використаємо формулу масиву, яка б повертала значення кількості останніх за датою оприбуткування запасів як списаних у виробництво.

Формула матиме такий вигляд:

{=ЕСЛИ(([Кількість відпущених у виробництво запасів]-[Кількість останніх придбаних запасів у минулому звітному періоді]>СУММ(([Діапазон комірок, що містять номери стовпців таблиць, починаючи з першого придбання звітного місяця]=1)*[Діапазон комірок, що містить числові дані таблиць, починаючи з першого придбання звітного місяця]));0;ЕСЛИ([Кількість відпущених у виробництво запасів]>СУММ(([Діапазон комірок, що містять номери стовпців таблиць, починаючи з першого придбання звітного місяця]=1)*[Діапазон комірок, що містить числові дані таблиць, починаючи з першого придбання звітного місяця])); [Кількість останніх придбаних запасів у минулому звітному періоді] +СУММ (([Діапазон комірок, що містять номери стовпців таблиць, починаючи з першого придбання звітного місяця]=1)*[Діапазон комірок, що містить числові дані таблиць, починаючи з першого придбання звітного місяця])-[Кількість відпущених у виробництво запасів];[Кількість останніх придбаних запасів у минулому звітному періоді]))} (25)

3.9. У комірки графи “Вартість” (стовпець 5) таблиці “Відомість наявності виробничих запасів на складі” за жовтень 20ХХ р. вводиться формула, що визначає різницю між вартістю придбаних запасів і вартістю запасів, відпущених у виробництво.