Використання MS Excel для обробки інформації з сезонних ризиків коливань цін на підприємствах агропрома

Размещено на http://www.allbest.ru/

Київський національний економічний університет імені Вадима Гетьмана

Академії соціальних відносин і туризму

Національний педагогічний університет імені М. П. Драгоманова

Використання MS Excel для обробки інформації з сезонних ризиків коливань цін на підприємствах агропрома

Кашина Ганна Сергіївна, доктор педагогічних наук, доцент, завідувач кафедри інтелектуальних систем і цифрових технологій, Косяк Інна Василівна, к. пед. .н., доцент кафедри промислової інженерії та сервісу, Громоздова Лариса Василівна, к. е. н., професор, професор кафедри інтелектуальних систем і цифрових технологій Бараник Зоя Павлівна, д. е. н, професор кафедри статистики, Макієвський Олексій Іванович, кандидат педагогічних наук, доцент кафедри інтелектуальних систем і цифрових технологій Тітов Владислав Вадимович, аспірант

м. Київ

Анотація

Метою статті є теоретико-методологічне обгрунтування доцільності прогнозування цін на сільськогосподарські культури на основі методу сезонних індексів за допомогою проведення розрахунків електронним інструментарієм MS Excel.

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

Наукова новизна отриманих результатів полягає в обґрунтуванні доцільності автоматизованої обробки даних та прикладного використання дата-аналітиками MS Excel для рішення задач прогнозування цін в умовах сезонних ризиків на сільськогосподарські культури. Показано , як за допомогою трендової моделі та програми Excel спрогнозовати ціни на сільськогосподарські культури.

Ключові слова: електронна таблиця, прогнозування, індекс сезонності, цінові ризики, адитивна модель, мультиплікативна модель, декомпозицій- ний ряд.

Abstract

Anna Sergeevna Kashina,

Doctor of Pedagogical Sciences, Head of the Department of Intelligent Systems and Digital Technologies, Academy of Labor, Social Relations and Tourism, Kyiv

Inna Vasilievna Kosyak,

Сandidate pedagogical sciences, associate professor, associate professor department of engineering and production technologies Ukrainian State University named Mykhailo Drahomanov associate professor of the department professional education in the field of technology and design Kyiv National University of Technology and Design

Larisa Vasilievna Hromozdova,

Candidate of Economics, PhD, Professor of the Department of Intelligent Systems and Digital Technologies, Academy of Labor, Social Relations and Tourism, Kyiv,

Zoya Pavlovna Baranik,

Doctor of Economics, Professor of the Department of Statistics, Kiev National Economic University named after Vadim Getman, Kiev.

Oleksiy Ivanovich Makievsky,

Candidate of Pedagogical Sciences, Associate Professor of the Department of Intelligent Systems and Digital Technologies.

Vladislav Vadimovich Titov,

postgraduate student, Kiev National Economic University named after Vadim Getman, Kyiv.

Application of MS Excel for information processing according to seasonal fluctuation risks prices at agricultural enterprises

The purpose of the article is theoretical and methodological substantiation of the expediency of forecasting prices for agricultural crops based on the method of seasonal indices using the MS Excel electronic toolkit.

The research methodology is based on the use of induction and deduction techniques, which made it possible to determine algorithms and electronic tools for processing information on seasonal risks of price fluctuations at agro-industry enterprises; as well as methods: analysis and synthesis, observation, comparison and analogy, economic-mathematical modeling, graphic interpretation, logic, which contributed to the study of the economic problem of seasonal risks of price fluctuations at agro-industry enterprises.

The scientific novelty of the obtained results lies in the substantiation of the feasibility of automated data processing and the applied use of MS Excel by data analysts to solve the problems of price forecasting in terms of seasonal risks for agricultural crops. It is shown how to forecast the prices of agricultural crops using the trend model and the Excel program.

Keywords: spreadsheet, forecasting, seasonality index, price risks, additive model, multiplicative model, decomposition series.

Аннотация

Анна Сергеевна Кашина, доктор педагогических наук, заведующая кафедри интелектуальних систем ы цифрових технологий, Академия труда, социальных отношений и туризма, г. Киев.

Инна Васильевна Косяк, кандидат педагогических наук, доцент, доцент кафедры инженерии и технологий производства Украинского государственного университета имени Михаила Драгоманова, доцент кафедры профессионального образования в области технологий та дизайна Киевского национального университета технологий и дизайна

Лариса Васильевна Громоздова, кандидат экономических наук, PhD, професор кафедры интеллектуальных систем и цифровых технологий. Академия труда, социальных отношений и туризма ,г. Киев

Зоя Павловна Бараник, доктор экономических наук, профессор кафедры статистики ,Киевский национальный экономический университет имени Вадима Гетьмана, г. Киев.

Макиевский Алексей Иванович, кандидат педагогических наук, доцент кафедры интеллектуальных систем и цифровых технологий, Академия труда, социальных отношений и туризма, г. Киев

Владислав Вадимович Титов, аспирант, Киевский национальный экономический университет имени Вадима Гетьмана, г. Киев.

Применение MS Excel для обработки информации по сезонным рискам колебания цен на предприятиях агропрома

Целью статьи является теоретико-методологическое обоснование целесообразности прогнозирования цен на сельскохозяйственные культуры на основе метода сезонных индексов посредством расчетов электронным инструментарием MS Excel.

Методология исследования базируется на использовании приемов индукции и дедукции, позволяющих определить алгоритмы и электронные инструментарии обработки информации по сезонным рискам колебания цен на предприятиях агропрома; а также методов: анализа и синтеза, наблюдения, сравнения и аналогии, экономико-математического моделирования, графической интерпретации, логики,- что способствовали проведению исследования экономической проблемы по сезонным рискам колебания цен на предприятиях агропрома

Научная новизна полученных результатов заключается в обосновании целесообразности автоматизированной обработки данных и прикладного использования дата-аналитиками MS Excel для решения задач прогнозирования цен при сезонных рисках на сельскохозяйственные культуры. Показано, как с помощью трендовой модели и программы Excel спрогнозировать цены на сельскохозяйственные культуры.

Ключевые слова: электронная таблица, прогнозирование, индекс сезонности, ценовые риски, аддитивная модель, мультипликативная модель, декомпозиционный ряд. електронний ризик ціна

Постановка проблеми

У сучасних умовах ринкової економіки рентабельність агропромислових підприємств багато в чому залежить від закупівельних цін на сільськогосподарські культури. При формуванні закупівельних цін необхідно враховувати витрати на виробництво сільськогосподарських культур та вплив політичних, економічних і соціальних факторів. Тому важливим завданням стратегічного управління агропромисловим комплексом, на нашу думку, є прогнозування закупівельних цін на сільськогосподарські культури, оскільки ефективний і конкурентоспроможний розвиток будь-якого напряму бізнесу неможливо уявити без механізму прогнозування і планування. Все це -- різновид робіт з великим обсягом інформації. На даний час найпопулярнішим в практиці і ефективним електронним інструментарієм, що забезпечує проведення усіх необхідних розрахунків, вважається програма Excel.

Аналіз актуальних досліджень

Проблемам економічного прогнозування присвячено чимало наукових досліджень О. М. Азаряна, М. М. Ад- жавенко, С. В. Глівенко, Б. Є. Грабовецького, В. П. Гудкова, Н. О. Дзюбенко, О. В. Лозової, М. В. Макаренко, М. Т. Пашути, М. М. Петрушенко, О. Р. Приймук, О. С. Сенишина, В. Є. Снитюка, В. І. Творонович, Р. В. Фещур та інших.

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

Виклад основного матеріалу

Економічне прогнозування посідає важливе місце в процесі підготовки й прийняття господарських рішень, є невід'ємною частиною системи управління економічною діяльністю та забезпечує науковість і обґрунтованість прийняття рішень при плануванні економічних процесів на підприємствах [2, с. 4]. Зазвичай, для розрахунку завдань прогнозування на підприємствах агропрому використовується програма Excel. Це найдоступніший і вивчений інструментарій обробки інформації у вирішенні завдань з прогнозування сезонного коливання цін.

Продовольчий ринок функціонує на основі таких загальних принципів ринкового господарства, як: свобода підприємництва, орієнтація на споживача, свобода споживчого вибору, конкуренції та приватної власності. Проте ринок продовольчих товарів має низку специфічних особливостей, що відрізняють його від інших ринків економічної системи [1, с. 25]. Невід'ємною частиною, зокрема, є сезонність виробництва і використання сировини для виробництва продовольчої продукції, що потребує проведення робіт з великими обсягами інформації для прорахунку прогнозів у майбутніх цінах на продовольчому ринку. Ці чинники впливають на збутову діяльність агропідпрємства та продовольчу безпеку в регіоні й в країні. Ефективними на даний час є електронні таблиці, що створюються за допомого. MS Excel.

Сезонність -- явище, що досить часто зустрічається в економічних часових рядах. Сезонні явища мають в своєму розвитку певні закономірності, що більш-менш регулярно повторюються з місяця в місяць, з кварталу в квартал [2, с. 39].

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

Варто зауважити, що статистичне дослідження сезонності висуває два основних завдання, в яких має бути враховано: прогнозування стану досліджуваного процесу, що містить сезонні коливання, та прогнозування довгострокової тенденції розвитку процесу, коли вихідні дані необхідно очистити від сезонних коливань. В сучасних інформаційних системах проведення таких досліджень та вирішення цих завдань можливо тільки з використанням сучасних електронних засобів, де дуже популярною є програма MS Excel. В електронній таблиці ці завдання вирішуються поділом часового ряду на окремі компоненти (декомпозиції часового ряду) [3, с. 57]. Інформаційні масиви, що є вхідними даними, наповнюються цифровим матеріалом сезонної та циклічної статистики, що відображають необхідні для проведення розрахунку характеристики. В цілому серед чинників, що визначають регулярні коливання ряду, розрізняють тренд, сезонні та циклічні чинники. Вся ця інформація підлягає цифровізації.

У практичної роботі з MS Excel дуже важливим є формалізація опису інформації -- математичне моделювання.

У процедурі сезонної декомпозиції реалізовані дві альтернативні моделі комбінування сезонної та несезонних компонент -- мультиплікативна та адитивна. При застосуванні першої моделі сезонна компонента визначається як фактор (індекс сезонності), на який необхідно помножити сезонно скориговане значення ціни динамічного ряду для отримання відповідного реального (нескоригованого) значення показника. Друга, адитивна, модель визначає сезонну компоненту ціни як фактор, який необхідно додати до скоригованого значення елемента ряду для відновлення його реального значення. Мультиплікативну модель треба використовувати для рядів, в яких амплітуда коливань пропорційна рівню ряду. Якщо такої залежності не спостерігається, має застосовуватися адитивна модель [5, с. 127]. Отже, принцип декомпозиції часового ряду полягає в тому, що в кожний момент часу t досліджуваний ряд Yt, складається з таких компонент:

тренд Tp тобто стійка довгострокова тенденція;

сезонна компонента It;

циклічна компонента Ct;

випадкова компонента Et.

Ці компоненти найчастіше комбінують як суму (адитивна декомпози- ція):

Y t = Tt + It + Ct + Et,(2.1),

або як добуток (мультиплікативнаидекомпозиція):

Y't = Tt х It х Ct х Et, (2.2).

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

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

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

Після декомпозиції розпочинаємо роботу з сезонністю. Вважається, що часовий ряд доцільно досліджувати на сезонність, якщо інтервал, за який беруть дані, менший, ніж рік (наприклад, якщо дані щомісячні, щоквартальні тощо) [4, с. 57]. Очевидно, сезонності в щорічних даних немає, навіть якщо вона наявна, скажімо, в щомісячних даних того ж процесу.

Аналіз сезонності складається з таких етапів:

1) визначення величини сезонної хвилі (сезонного періоду), тобто період, в який ряд має певну динаміку, відтворюється в аналогічних інтервалах часу;

2) визначення сезонної компоненти для кожного інтервалу часу в середині сезонного періоду.

Окремо може бути проаналізовано визначення періодів сезонної хвилі, тобто її поділ на частки, на яких динаміка ряду не змінюється, аналіз амплітуди сезонної хвилі тощо.

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

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

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

Варто враховувати, що для надійних розрахунків сезонності треба мати дані за кілька сезонних періодів. Маючи щомісячні дані за один рік, неможливо провести розрахунки прогнозу з урахуванням сезонності на наступний період, якщо сезонний період цього процесу становить теж рік.

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

Метод декомпозиції ряду за допомогою сезонних індексів (Season- alin- dexes).

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

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

Етап 1. Декомпозиція ряду. Визначається, який тип декомпозиції ряду (адитивну чи мультиплікативну) доцільно провести, і здійснюється розрахунок окремих компонент ряду.

Крок 1.1. Визначення сезонних індексів. Сезонна хвиля для кожного інтервалу часу, що входить до сезонного інтервалу, розкладається на сезонні індекси. У найпростішому випадку вони розраховуються як усереднені значення кожного інтервалу в середині сезонної хвилі до середнього значення -- досліджуваного ряду за весь сезонний період, наприклад, середнє значення обсягу продажу у квітні до середнього значення продажу за рік. Найчастіше -- застосовуються методики розрахунку середніх значень за допомогою ковзних середніх, наприклад, центрованої ковзної середньої.

Залежно від того, яка декомпозиція планується, сезонні індекси розраховуються визначенням різниці (чи відношення) до центрованої ковзної середньої сезонного індексу для кожної частини сезонної хвилі.

Використання MS EXCEL для обробки інформації з сезонних 23 ризиків коливань цін на підприємствах агропрому декси сезонності (сезонну хвилю). Результати розрахунків наведені в Excel-таблиці 1.

Таблиця 1. Розрахунок індексу сезонності цін на сільськогосподарські культури

Крок 1.2. Десезоналізація даних. На цьому етапі сезонна компонента виключається з ряду Yt. Десезоналізація для адитивної декомпозиції здійснюється відніманням від кожного фактичного рівня ряду відповідного індексу сезонності (діленням значення ряду на індекс у випадку мульти- плікативної декомпозиції). Наприклад, у випадку мультиплікативної де- композиції ряд Yt трансформується в десезоналізований ряд Dt за допомогою перетворення

де It, є {h,b,b,...,Ii}; L -- довжина сезонного періоду (L = 12 для щомісячних даних, L=4 для щоквартальних значень тощо).

Крок 1.3. Визначення тренду. «Очищений» від сезонності ряд містить тренд Dt, циклічну та випадкову компоненти. Якщо циклічна компонента не дуже заражена, вона не вплине суттєво на визначення тренда. Оцінити тренд можна за допомогою МНК. Пояснювальною змінною є час. Форму тренда доцільно дібрати після візуального аналізу графіка ряду Dt.

Крок 1.4. Визначення циклічної компоненти. Циклічна компо- нента Ct визначається вирахуванням з ряду DIt, тренда. Для мультиплікативної моделі:

Таблиця 2 Виділення циклічної компоненти

Останній крок є опційним, тобто в більшості випадків аналізу сезонності не розраховується. Аналіз ряду Ct дозволить зробити висновок, чи треба враховувати його динаміку під час побудови прогнозу ряду. Результати розрахунків наведені в Excel-таблиці 2.

Етап 2. Побудова прогнозу.

Крок 2.1. Розраховується тренд на прогнозний період. Використовується регресійна модель, отримана на Кроці 1.3.

Результати розрахунків наведені в Excel-таблиці 3.

Таблиця 3. Розрахунок компоненти часового ряду

Крок 2.2. До тренда на прогнозні періоди додаються (для моделі адитивної декомпозиції) чи домножуються (для моделі мультиплікативної де- композиції) відповідні коефіцієнти сезонності.

Крок 2.3. Якщо вплив циклічності на ряд Yt можна вважати суттєвим (крок 1.4.), то прогноз коригується на прогнозовану величину циклічності ряду.

Завдяки застосуванню цього методу та можливості графічної візуалі- зації інформації програмою Excel, отримуємо графічну інтерпретацію прогнозу цін на сільськогосподарські культури (мал. 1):

Мал. 1. Графічна інтерпретація прогнозу цін на сільськогосподарські культури.

MSE = 0,110791646 MAPE = 0,067077269

Міра якості прогнозу:

MAPE = 0,067077269 Ч 100%=6,7 %.

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

Таблиця 4.

Слід зауважити, що сучасні розробники створили інтелектуальні програми для бездоганної конвертації файлів Excel у PDF, у JPG, у HTML, що дуже підвищує подальшу ефективну обробку інформації, її аналіз на усіх рівнях прийняття рішень.

Висновок

Вищенаведений метод сезонних індексів дозволяє прогнозувати ціну, за якою реалізуватиметься майбутня сільськогосподарська продукція, та застрахуватися від сезонних страхових ризиків. На жаль, прогнози не можуть бути надто точними, тому що є безліч факторів, через які виникає невідповідність між реальністю та прогнозом. Тому під час прийняття управлінських рішень менеджери мають добре ознайомитися з методами прогнозування і віднайти найефективніші рішення. Прийняттю цього рішення передує великий обсяг обробки і аналізу інформації, що в сучасних умовах досягається на підприємствах агропрома використанням табличного процесора MS Excel. Excel визнана дата-аналітиками як провідна потужна програма, що дозволяє в матричному режимі обробляти великі електронні таблиці та відображати інформацію у вигляді графіків і діаграм, що має можливості використовувати макроси. Microsoft Excel інтегрує мову Python в Excel, що розширює можливості цього програмного продукту при використанні потужних розрахунків та проведенні аналітики фахівцями з ціноутворення на підприємствах агропрома.

Перспективним напрямом подальших досліджень є обґрунтування системних підходів до процесу прогнозування цін на продукцію агропрому та вдосконалення електронних засобів обробки інформації.

Список використаних джерел

1. Дзюбенко Н. О. Соціально-економічне прогнозування розвитку продовольчого ринку в Україні / Н. О. Дзюбенко // Дисертація на здобуття наукового ступеня кандидата економічних наук / за ред. Н. О. Дзюбенко. -- Львів: Львівський національний університет імені Івана Франка, 2014. -- 259 с.

2. Економічне прогнозування. Навчальний посібник. М. В. Макаренко, В. П. Гудкова, М. М. Аджавенко, О. Р. Приймук, В. І. Творонович. -- К.: Видавництво ДЕТУТ, 2014. -- 161 с.

3. Кальдишев Г. С. Анализ временных рядов и прогнозирование [Текст] Г. С. Кальдишев, А. А. Франкель. -- М.: Статистика, 1973. -- 103 с.

4. Творонович В. І. Економічне прогнозування. Навчальний посібник. -- К.: Наша справа, 2001. -- 98 с.

5. Шуліков А. Е., Голованова М. А. Статистичне дослідження часових рядів цін на ринку / А. Е. Шуліков, М. А. Голованова // Економіка та управління підприємствами машинобудівної галузі: проблеми теорії та практики, 2011, № 2(14). -- С. 125--136.

References

1. Dziubenko N. O. Sotsialno-ekonomichne prohnozuvannia rozvytku prodo- volchoho rynku v Ukraini/ N. O. Dziubenko // Dysertatsiia na zdobuttia nauko- voho stupenia kandydata ekonomichnykh nauk / za red. N. O. Dziubenko -- Lviv.: Lvivskyi natsionalnyi universytet imeni Ivana Franka, 2014. -- 259 s.

2. Ekonomichne prohnozuvannia: Navchalnyi posibnyk. M. V. Makarenko, V. P. Hudkova, M. M. Adzhavenko, O. R. Pryimuk, V. I. Tvoronovych. -- K.: Vydavnytstvo DETUT, 2014. -- 161 s.

3. Kaldishev G. S. Analiz vremennyih ryadov i prognozirovanie [Tekst]

4. G. S. Kaldishev, A. A. Frankel. -- M.: Statistika, 1973. -- 103 s.

5. Tvoronovych V. I. Ekonomichne prohnozuvannia: Navchalnyi posibnyk. -- K.: Nasha sprava, 2001. -- 98 s.

6. Shulikov A. E., Holovanova M. A. Statystychne doslidzhennia chasovykh riadiv tsin na v2v-rynku / A. E. Shulikov, M. A. Holovanova // Ekonomika ta upravlinnia pidpryiemstvamy mashynobudivnoi haluzi: problemy teorii ta prak- tyky, 2011, № 2(14) S. 125--136.

Размещено на Allbest.ru