Ляшенко О. С. Засоби табличного процесу Microsoft Excel для прогнозування економічних процесів // Міжнародний науковий журнал "Інтернаука". — 2019. — №8.
Інше
УДК 330.43
Ляшенко Ольга Сергіївна
асистент кафедри вищої математики та інформаційних систем
Донецький національний університет економіки і торгівлі
імені Михайла Туган-Барановського
Ляшенко Ольга Сергеевна
Ассистент кафедры высшей математики и информационных систем
Донецкий национальный университет экономики и торговли
имени Михаила Туган-Барановського
Liashenko Olga
Assistant of the Department of Mathematics and Information Systems
Donetsk National University of Economics and Trade
named after Mykhailo Tugan-Baranovsky
ЗАСОБИ ТАБЛИЧНОГО ПРОЦЕСУ MICROSOFT EXCEL ДЛЯ ПРОГНОЗУВАННЯ ЕКОНОМІЧНИХ ПРОЦЕСІВ
СРЕДСТВА ТАБЛИЧНОГО ПРОЦЕССА MICROSOFT EXCEL ДЛЯ ПРОГНОЗИРОВАНИЯ ЭКОНОМИЧЕСКИХ ПРОЦЕССОВ
MEANS OF MICROSOFT EXCEL TABLE PROCESS FOR FORECASTING ECONOMIC PROCESSES
Анотація. У статті досліджуються ефективність застосування засобів табличного процесору Microsoft Excel для прогнозування економічних процесів. Розглядається побудова стохастичної моделі аналізу економічного процесу. В процесі моделювання побудовані діаграми та створена економіко-математична модель, складено прогнозований прибуток для планованого обсягу продаж. Розрахований прогноз та графічно представлена трендова модель всіх показників. Наведено приклади розрахунку для одного виду продукції та виконана перевірка адекватності, значимості та точності моделі.
Ключові слова: табличний процесор, кореляційно-регресійний аналіз, економіко-математична модель, прогнозування.
Аннотация. В статье исследовано эффективность использования средства процессора Microsoft Excel для прогнозирования экономических процессов. Рассмотрено построение стохастической модели анализа экономического процесса. В ходе моделирования построено диаграммы и создано экономико-математические модели, рассчитан прогноз прибыли продаж и графически представлена трендовая модель всех показателей. Приведено примеры расчета для определенного вида продукции и выполнена проверка адекватности, значимости и точности модели.
Ключевые слова: табличный процессор, корреляционно - регрессионный анализ, экономико-математическая модель, прогнозирование.
Summary. This article is a study of the efficiency of using Microsoft Excel spreadsheet processors to predict economic processes. The stochastic model is based on the analysis of the economic process. Diagrams are constructed in the process of modeling and the econometric-mathematical model is created, forecast profit is compiled for the planned sales volume. The forecast is calculated and the trend model is graphically represented by all indicators. Examples of calculation are given for one type of product and the model is tested for adequacy, significance and accuracy.
Key words: table processor, correlation-regression analysis, economic-mathematical model, forecasting.
Текст статті. Ефективне управління сучасним підприємством не можливо без використання засобів табличного процесору Microsoft Excel, а саме аналізу даних за допомогою статистичних функцій і вбудованого спеціалізованого пакету та економіко-статистичного аналізу.
Економічні явища досить різноманітні та характеризуються рядом ознак, що відображають певні властивості цих процесів і явищ та піддаються взаємозумовленим змінам. В одних випадках залежність між ознаками виявляється досить тісною (наприклад, годинне відпрацювання робітника та його заробітна плата), а в інших – їх зв’язок ледь помітний (об’єм перевезень та календарний місяць).
Для планування економічного розвитку підприємства використовують кореляційно - регресійний аналіз, що являється методом для дослідження форм зв’язку, які встановленні кількісним співвідношенням між випадковими величинами процесу, що досліджується. Його застосування дозволяє провести аналіз досліджуваних статистичних показників та на основі цього аналізу та отриманих даних побудувати економіко-математичну модель.
Кореляційно-регресійний аналіз класичний метод стохастичного моделювання та метод кількісної оцінки взаємозалежностей між статистичними ознаками, що характеризує окремі суспільно-економічні явища та процеси економічної діяльності.
Кореляційний аналіз – кількісний метод, що дозволяє виявити залежність між декількома випадковими величинами, а його параметри використовуються як знаряддя цілеспрямованого регулювання рівнів результативних ознак.
Регресійний аналіз – це кількісний метод визначення виду математичної функції в причино-наслідковій залежності між змінними величинами.
Основна задача кореляційно-регресійного аналізу полягає в кількісному визначенні тісноти зв’язку між двома ознаками при парному зв’язку і між результативними та декількома факторними ознаками при багатофакторному зв’язку і статистичній оцінці надійності встановленого зв’язку.
При застосування на практиці кореляційно-регресійного аналізу виділяють наступні етапи:
Перший етап: постановка задачі – відбувається визначення показників, залежність яких буде оцінюватися, здійснюється формулювання економічно осмисленої і прийнятної гіпотези про залежність між показниками з зазначеним рівнем довіри.
Другий етап: формування переліку чинників, їх логічний аналіз. На цьому етапі відбувається відбір оптимального числа найбільш істотних змінних факторів, які мають вплив на залежний показник, після чого починається їх обробка.
Третій етап: побудова моделі, її перевірка на адекватність модель, правдоподібність та значущість. На цьому етапі відбувається визначення числових значення параметрів регресії, обчислення ряду показників, що характеризують точність проведеного аналізу. Основна задача цього етапу – відшукати найбільш точну тісноту зв’язку, для того щоб можна було прогнозувати, передбачати значення залежної величини, при відомих значеннях незалежних величин.
Для оцінки адекватності моделі необхідно дослідити залишки. Залишки – це різниця між вихідними, тобто досліджуваними, значеннями залежної змінної і передбачуваними даними.
Четвертий етап: оцінка або точність моделі. Суть етапу полягає в визначенні настільки теоретичні дані відрізняються від фактичних. Відбувається визначення числових параметрів апроксимації та варіації, пошук точкової та інтервальної оцінки.
П’ятий етап: економічна представлення. На цьому етапі проводять порівняння результатів аналізу з гіпотезами, що припущені на першому етапі, відбувається оцінка їх правдоподібність з економічної точки зору та підводяться аналітичні висновки.
Завданням кореляційно-регресійного аналізу є встановити між факторними і результативними ознаками теоретичну форму зв’язку (регресійний аналіз) та визначити тісноту цього зв’язку (кореляційний аналіз).
Зв’язки за формою поділяються на кореляційні прямі й обернені, лінійні й криволінійні (нелінійні), одно – й багатофакторні. Прямі й обернені зв’язки – це зв’язки, що розрізняють залежно від напряму зміни результативної ознаки. Зміна ознаки в тому самому напрямі, що і факторна (тобто, із збільшенням результативної ознаки збільшується, а із зменшенням – зменшується), то це є прямий зв’язок, в іншому випадку, – зв’язок обернений.
Форма зв’язку результативної ознаки у з факторами х одержала назву рівняння регресії.
В залежності від числа взаємопов’язаних ознак існує парна та множинна регресія. Якщо досліджується зв'язок між двома ознаками, то регресія є парною, якщо між трьома та більше – множинною (багатофакторною).
Найбільш розповсюдженою з простих моделей є парна лінійна регресія, при застосуванні якої використовується співвідношення результативної ознаки та одного факторної ознаки.
Розглянемо приклади застосування кореляційно-регресійного аналізу для прогнозування економічних процесів.
Приклад 1.
Фірма «Логістик-транс» здійснює перевезення промислової продукції. Маємо дані про об’єми перевезень за останні 9 місяців (табл.1)
Таблиця 1
Об’єми перевезень за останні 9 місяців
|
Місяць |
Об’єм перевезень (тис. тон) |
|
1 |
1750 |
|
2 |
1755 |
|
3 |
1767 |
|
4 |
1760 |
|
5 |
1770 |
|
6 |
1790 |
|
7 |
1810 |
|
8 |
1840 |
|
9 |
1854 |
Джерело: [8]
Визначити прогнозований об’єм перевезень на 10-й місяць.
Встановлюємо рівень довіри γ=95% або рівень значущості α=1-γ=5%.
Аналіз за допомогою лінії тренду. Будуємо графік функції обсягу перевезень у залежності від календарного місяця (рис. 1).
Рис. 1. Графік функції обсягу перевезень
Джерело: побудова діаграми за допомогою табличного процесору Microsoft Excel
Рівень вірогідності застосовуваної лінійної апроксимації дорівнює 0.8877. Намагаємося підвищити його, застосувавши поліноміальну апроксимацію (рис.2).
Рис. 2. Діаграма обсягу перевезень з поліноміальною апроксимацією
Джерело: побудова діаграми та лінії тренду з параметрами за допомогою табличного процесору Microsoft Excel
Рівень вірогідності застосовуваної поліноміальної апроксимації близький до 1 (дорівнює 0,98), тобто мається тісна кореляційна залежність між розглянутими змінними. Для аналізу адекватності, значимості та точності моделі необхідно використовувати значення статистичних характеристик. Після чого для одержання шуканого прогнозного значення переходимо в режим редагування лінії тренда і на вкладці Параметри встановлюємо Прогноз уперед на 1 одиницю.
Результат: прогнозований обсяг перевезень на 10-й місяць складає »1882 тисяч тонн.
Аналіз математичної моделі та розрахунок прогнозних значень
Для визначення коефіцієнтів функції, яка використовується при обчисленнях лінії регресії й розрахунку статистичних характеристик застосуємо засіб Регресія пакету Аналіз даних. Результат виконання: математична модель: об’єм = 1,851*місяць^2 -5,401*місяць + 1756,81. Коефіцієнт детермінації R2≈0,98>γ=0,95, df=6, R2нормированный≈0,97>γ=0,95, Значимість F ≈ 0,0001 < 0,05. Модель відповідає економічному процесу.
Р-Значення для t1≈0,002<0,05, t2≈0,2>0,05 і t3≈0,006<0,05. Тобто другий коефіцієнт не значимий (може дорівнювати 0). Це ж саме можна встановити за допомогою значень критерію Стьюдента |t1|≈5,041, |t2|≈1,434, |t3|≈214,194, tкрит≈2,346.
Визначити коефіцієнти функції, за допомогою функції ЛИНЕЙН наведені в таблиці 2.
Таблиця 2
Результати обчислення статистичних характеристик
|
1,851732 |
-5,40065 |
1756,81 |
Коефіцієнти a1, a2, b неявної лінійно регресії (y=1,851x2 -5,4001x+1756,81) |
|
0,367292 |
3,76601 |
8,201938 |
Стандартні значення помилок для коефіцієнтів a й b |
|
0,978561 |
6,445946 |
|
R2; стандартна помилка для оцінки y |
|
136,9297 |
6 |
|
F-статистика; ступені свободи |
|
11378,92 |
249,3013 |
|
Регресійна сума квадратів; залишкова сума квадратів |
Регресійна модель: Об’єм перевезень = 1,851* Місяць^2+ 1756,81
R2=0,97; R2нормований=0,97; Похибка=0,42%< α, df=6.
F =177,58>Fкрит=5,59; tа=15,36; tb=485,85; tкр=2,36;
А=0,29%< α; V= 0,39%< α.
Висновок. Регресійна модель правдоподібна з рівнем довіри γ=95%.
За отриманою моделлю розраховуємо прогнозне значення обсягу перевезень на 10-й місяць (таблиця 3).
Середнє значення прогнозу об’єму перевезень (точкова оцінка) на 10-й місяць становить 1879,88 тис. тон.
Точність довірчого інтервалу δ=21,15.
Довірчий інтервал: 1858,73<Yпр<1901,02.
Приклад 2.
ТОВ «ДОН» протягом 8-ми місяців реалізує продаж продовольчих товарів. Дані про реалізацію товарів наведенні в таблиці 4. Скласти прогнозований прибуток для обсягу продаж 39000 грн.
Таблиця 3
Прогнозне значення обсягу перевезень на 10-й місяць
|
Місяць |
Місяць^2 |
Об’єм перевезень |
Розрахункове значення Y |
Залишки |
A |
(X-Xср)2 |
|
1 |
1 |
1750 |
1747,41133 |
2,5886705 |
0,001479 |
1406,25 |
|
2 |
4 |
1755 |
1751,425439 |
3,5745614 |
0,002037 |
1190,25 |
|
3 |
9 |
1767 |
1758,11562 |
8,8843796 |
0,005028 |
870,25 |
|
4 |
16 |
1760 |
1767,481875 |
-7,481875 |
0,004251 |
506,25 |
|
5 |
25 |
1770 |
1779,524202 |
-9,524202 |
0,005381 |
182,25 |
|
6 |
36 |
1790 |
1794,242602 |
-4,242602 |
0,00237 |
6,25 |
|
7 |
49 |
1810 |
1811,637075 |
-1,637075 |
0,000904 |
110,25 |
|
8 |
64 |
1840 |
1831,70762 |
8,29238 |
0,004507 |
650,25 |
|
9 |
81 |
1854 |
1854,454238 |
-0,454238 |
0,000245 |
1806,25 |
|
10 |
100 |
1879,876929 |
1879,876929 |
|
|
6728,25 |
|
|
|
|
|
6,821E-13 |
0,29% |
|
Джерело: розрахунки автора за допомогою функцій табличного процесору Microsoft Excel
Таблиця 4
Реалізація товарів ТОВ «ДОН»
|
№ п/п |
Прибуток |
Обсяг продаж |
|
1 |
9800 |
80000 |
|
2 |
10000 |
100000 |
|
3 |
12000 |
160000 |
|
4 |
30000 |
220000 |
|
5 |
35000 |
280000 |
|
6 |
41000 |
340000 |
|
7 |
43000 |
360000 |
|
8 |
50000 |
380000 |
Джерело: розробка автора
Робота по знаходженню прогнозованого прибутку та графік реалізації товарів представлено в таблиці 5 та на рисунку 3.
Рис. 3. Графік реалізації товарів з лінією тренду
Джерело: побудова лінії тренду за допомогою табличного процесору Microsoft Excel
Таблиця 5
Прогнозний прибуток 9-й місяць
|
№ п/п |
Прибуток |
Обсяг продаж |
|
Х |
Y |
|
|
1 |
9800 |
80000 |
|
2 |
10000 |
100000 |
|
3 |
12000 |
160000 |
|
4 |
30000 |
220000 |
|
5 |
35000 |
280000 |
|
6 |
41000 |
340000 |
|
7 |
43000 |
360000 |
|
8 |
50000 |
380000 |
|
9 |
49826,9266 |
390000 |
Джерело: розрахунки автора за допомогою функцій табличного процесору Microsoft Excel
Таким чином, за умови лінійної моделі = 7,1507*прибуток +33702 та при R2=0,96 прибуток буде рівним 49826,93 грн. при обсязі продаж рівному 390000 одиниць товару.
Одержана модель не є правдоподібною за показниками середньої похибки апроксимації (10,81%) та за коефіцієнтом варіації (10,34%) із рівнем довіри 4%. Але модель є адекватною за критерієм Фішера (4,65), значущою за критерієм Стьюдента (2,61) при заданому рівні довіри 4%.
Отже, використання табличного процесору, як засобу для застосування кореляційно-регресійного аналізу дає змогу спрогнозувати не лише темпи зростання економічного процесу на короткотривалий період, а й спрогнозувати прибуток при відомому значенні обсягу реалізації продукції.
Таким чином, хоча використання табличного процесору Microsoft Excel дає змогу створити не лише кореляційно-регресійну модель, а й спрогнозувати загальні показники економічного процесу та визначити розвиток підприємства у майбутньому. Однак, він не пристосований для розв’язання більш складних задач такого плану. Існує ряд незручностей при моделюванні. 1. Визначення лінії тренду ускладнене. 2. Оцінка правдоподібності моделі та розрахунок інтервальних оцінок прогнозованих значень не автоматизовано. Для більш складних економічних процесів, в тому числі багатофакторних моделей, доцільно використовувати пакет Статистика або СПСС.
Література
