Прогнозирование потребления электроэнергии жителями района (города, региона) на определенный период времени

Задача
Для прогнозирования потребления электроэнергии на определенный период времени в качестве примера выбрана республика Башкортостан. По нему получена информация, характеризующая зависимость потребления электроэнергии жителями региона по месяцам в 2020 гг. (Y, млрд кВт.ч.) от среднемесячной температуры (X1, в градусах), средней продолжительности дня по месяцам (X2, в часах), количеству ясных дней в месяце (X3, в днях).
Данные по потреблению электроэнергии взяты из сайта www.so-ups.ru «Системный оператор единой энергетической системы», по среднемесячной температуре и количеству ясных дней в месяце из сайта www.weatherarchive.ru «Прогноз и архив погоды», по средней продолжительности дня из сайта www.ru.365.wiki «Крупнейшие города мира».
Таблица 1
Месяц Потребление электроэнергии (Y, млрд кВт.ч.) Среднемесячная температура (X1, в градусах) Средняя продолжительность дня (X2, в часах) Количество ясных дней (X3, в днях)
Январь 2,55 -5,57 7,97 3
Февраль 2,42 -4,96 9,77 5
Март 2,45 0,91 11,92 9
Апрель 2,18 6,15 14,17 10
Май 1,88 14,87 16,15 12
Июнь 1,79 16,40 17,22 10
Июль 1,84 22,63 16,63 11
Август 1,8 17,67 14,83 11
Сентябрь 1,81 11,67 12,67 10
Октябрь 2,06 6,38 10,43 6
Ноябрь 2,23 -4,24 8,42 5
Декабрь 2,52 -12,63 7,33 5
Требуется:
Осуществить выбор факторных признаков для построения двухфакторной регрессионной модели.
Рассчитать параметры модели.
Для характеристики модели определить:
линейный коэффициент множественной корреляции,
коэффициент детерминации,
средние коэффициенты эластичности,
бетта-, дельта-коэффициенты.
Дать их интерпретацию.
Осуществить оценку надежности уравнения регрессии.
Оценить с помощью t-критерия Стьюдента статистическую значимость коэффициентов уравнения множественной регрессии.
Построить точечный и интервальный прогнозы результирующего показателя.
Отразить результаты расчетов на графике.
Выполнение задач отразить в аналитической записке, приложить компьютерные распечатки расчетов.
Решение
Построение системы показателей. Анализ матрицы коэффициентов парной корреляции. Выбор факторных признаков для построения двухфакторной регрессионной модели.
Таблица 2

В данном примере n=12, m=3.
а) Осуществим выбор факторных признаков для построения двухфакторной регрессионной модели с использованием инструмента корреляции в Excel (Приложение 1)
Для проведения корреляционного анализа необходимо выполнить следующие действия:
Данные для корреляционного анализа должны располагаться в смежных диапазонах ячеек.
Выбрать команду ДанныеАнализ данных.
В диалоговом окне Анализ данных выбрать инструмент Корреляция, а затем щелкнуть на кнопке ОК.
В диалоговом окне Корреляция в поле Входной интервал необходимо ввести диапазон Ячеек, содержащих исходные данные. Если выделены и заголовки столбцов, то установить флажок Метки в первой строке.
Выбрать параметры ввода. В данном примере Новый рабочий лист.
ОК.
Таблица 3
Результаты корреляционного анализа
  Потребление электроэнергии (Y, млрд кВт.ч.) Среднемесячная температура (X1, в градусах) Средняя продолжительность дня (X2, в часах) Количество ясных дней (X3, в днях)
Потребление электроэнергии (Y, млрд кВт.ч.) 1      
Среднемесячная температура (X1, в градусах) -0,924278101 1    
Средняя продолжительность дня (X2, в часах) -0,821462725 0,929146895 1  
Количество ясных дней (X3, в днях) -0,791947232 0,866473517 0,923978784 1
Анализ матрицы коэффициентов парной корреляции показывает, что на зависимую переменную, т.е. потребление электроэнергии, больше влияют среднемесячная температура (ryx1), и чуть в меньшей степени средняя продолжительность дня (ryx2) и количество ясных дней (ryx3).
Для построения двухфакторной регрессионной модели выбираем Х1, Х2 и Х3.
2. Построим линейную модель регрессии с использованием инструмента регрессия в Excel (приложение 2)
Для проведения регрессионного анализа выполняются следующие действия:
1. Выбрать команду Данные Анализ данных.
2. В диалоговом окне Анализ данных выбрать инструмент Регрессия, а затем щелкнуть на кнопке ОК.
3. В диалоговом окне Регрессия в поле Входной интервал Y необходимо ввести адрес одного диапазона ячеек, который представляет зависимую переменную. В поле Входной интервал X ввести адреса одного или нескольких диапазонов, которые содержат значения независимых переменных.
4. Если выделены и заголовки столбцов, то установить флажок Метки в первой строке.
5. Выбрать параметры ввода. В данном примере Новый рабочий лист.
6. В поле остатки поставить необходимые флажки.
ОК.
Таблица 4
  Коэффициенты
Y-пересечение 2,003193348
Среднемесячная температура (X1, в градусах) -0,031744195
Средняя продолжительность дня (X2, в часах) 0,035523744
Количество ясных дней (X3, в днях) -0,01597103
Оценка параметров регрессии осуществляется по методу наименьших квадратов по формуле a=XTX-1XTY, используя данные, приведенные в таблице 2.
XTX=mX1X2X3X1X12X1X2X1X3X2X1X2X22X2X3X3X1X3X2X3X32=1269,28147,59769,281763,091252,21884,55147,51252,211949,631301,8397884,551301,83887Теперь можно преступить к непосредственному расчету обратной матрицы (приложение 3).
Выделяем ячейку, которая должна стать верхней левой ячейкой обратной матрицы. Переходим в Мастер функций, кликнув по значку слева от строки формул.
В открывшемся списке выбираем функцию МОБР. Жмем на кнопку «OK».
В поле «Массив», открывшегося окна аргументов функции, устанавливаем курсор. Выделяем весь первичный диапазон. После появления его адреса в поле, жмем на кнопку «OK».
Как видим, появилось значение только в одной ячейке, в которой была формула. Но нам нужна полноценная обратная функция, поэтому следует скопировать формулу в другие ячейки. Выделяем диапазон, равнозначный по горизонтали и вертикали исходному массиву данных. Жмем на функциональную клавишу F2, а затем набираем комбинацию Ctrl+Shift+Enter. В выделенных ячейках получаем значения, округляем их до 4-х знаков после запятой:
Таблица 5
6,2418 0,1614 -0,6336 0,0864
0,1614 0,0054 -0,015 -0,0011
-0,6336 -0,015 0,0917 -0,0503
0,0864 -0,0011 -0,0503 0,0666
XTX-1=6,24180,1614-0,63360,08640,16140,0054-0,015-0,0011-0,6336-0,0150,0917-0,05030,0864-0,0011-0,05030,0666a=2,0032-0,0317441950,035523744-0,01597103Уравнение регрессии зависимости потребления электроэнергии жителями региона от среднемесячной температуры, средней продолжительности дня по месяцам, количеству ясных дней в месяце. можно записать в следующем виде:
Y=2,0032-0,0317X1+0,0355X2-0,016X3Оценим адекватность построенной модели.
Таблица 6

а) Проверку независимости остатков проведем с помощью d-критерия Дарбина – Уотсона:
d=Et-E(t-1)2Et2=0,2460,1338=1,839По таблице Дарбина-Уотсона при m=3 определим критические точки для уровня значимости 0,05 и числа наблюдений 12:
d1=0,66,
d2=1,86.
Так как расчетное значение больше d1, то свойство независимости выполняется.
Это является одним из подтверждений высокого качества модели.
б) Оценим нормальность распределения остаточной компоненты по RS-критерию с критическими уровнями 2,8-2,9.
SE=Et2n-m=0,13389=0,122.
RS=Emax-EminSE=0,2-(-0,14)0,122=2,787Гипотеза о нормальном распределении ряда остатков отвергается.
На основе полученной модели нельзя строить интервальный прогноз.
Таблица 7
Регрессионная статистика
Множественный R 0,931820242
R-квадрат 0,868288964
Нормированный R-квадрат 0,818897325
Стандартная ошибка 0,128684815
Наблюдения 12
Линейный коэффициент множественной корреляции R=0,932.
Коэффициент детерминации R2=0,8683, то есть 86,83% вариации зависимой переменной учтено в модели и обусловлено влиянием включенных факторов.
Вычислим средние коэффициенты эластичности по формуле:
Эj=aj∙XjYЭ1=-0,031744195*5,772,13=-0,086Э2=0,035523744*12,292,13=0,205Э2=-0,01597103*8,082,13=-0,061При увеличении среднемесячной температуры на 1% и неизменной средней продолжительности дня и количества ясных дней потребление электроэнергии населением уменьшится на 0,086%. При увеличении средней продолжительности дня на 1% и неизменной среднемесячной температуры и количества ясных дней потребление электроэнергии населением увеличится на 0,205%. При увеличении количества ясных дней на 1% и неизменной среднемесячной температуры и средней продолжительности дня потребление электроэнергии населением уменьшится на 0,061%.
Рассчитаем бетта-коэффициенты:
βj=aj∙SxjSy, где Sxj, Sy – стандартные отклонения множеств значений X и Y.
Таблица 8

Sy=Yi-Y2n=0,9912=0,287Sx1=Xi1-X12n=1363,1112=10,658Sx2=Xi2-X22n=136,3512=3,371Sx3=Xi3-X32n=102,9612=2,929β1=a1∙Sx1Sy=(-0,031744195)*10,6580,287=-1,1788β2=a2∙Sx2Sy=0,035523744*3,3710,287=0,4172β3=a3∙Sx3Sy=-0,01597103*2,9290,287=-0,163При неизменной средней продолжительности дня и количества ясных дней увеличение среднемесячной температуры на величину среднеквадратического отклонения уменьшит потребление электроэнергии на 1,179 ее среднеквадратического отклонения. При неизменной среднемесячной температуры и количества ясных дней увеличение средней продолжительности дня на величину среднеквадратического отклонения увеличит потребление электроэнергии на 0,417 ее среднеквадратического отклонения. При неизменной среднемесячной температуры и средней продолжительности дня увеличение количества ясных дней на величину среднеквадратического отклонения уменьшит потребление электроэнергии на 0,163 ее среднеквадратического отклонения.
Вычислим дельта-коэффициенты:
δ1=ryx1β1R2=-0,924278101*-1,17880,8683=1,2548δ2=ryx1β1R2=-0,821462725*0,41720,8683=-0,3947δ2=ryx1β1R2=-0,791947232*(-0,163)0,8683=0,14Указанные значения показывают, что на уменьшение потребления электроэнергии наибольшее влияние оказывают увеличение среднемесячной температуры (125%), затем увеличение количества ясных дней (14%), в то же время уменьшение средней продолжительности дня оказывает существенного влияния на уменьшение потребление электроэнергии (отрицательное значение -39%).
4. Осуществим оценку надежности уравнения регрессии на основе вычисления F-критерия Фишера:
F=R2(n-k-1)1-R2k=0,8683*81-0,8638*3=17, где k – число факторов в уравнении регрессии.
Табличное значение F-критерия Фишера при доверительной вероятности 0,95 при v1=k=3 и v2=n-k-1=8 составляет 4,07.
Так как F> Fтабл, то уравнение регрессии признается адекватным.
5. Оценим с помощью t-критерия Стьюдента статистическую значимость коэффициентов уравнения множественной регрессии.
Используем данные обратной матрицы XTX-1.
6,24180,1614-0,63360,08640,16140,0054-0,015-0,0011-0,6336-0,0150,0917-0,05030,0864-0,0011-0,05030,0666b11=6,2418b22=0,0054b33=0,0917b44=0,0666tai=aiSaita0=2,00320,321555704=6,2297ta1=-0,0317441950,00944=-3,3627ta2=0,0355237440,038970967=0,9115ta3=-0,015971030,033220523=-0,4808Табличное значение t-критерия при 5% уровне значимости:
tтабл=tα;n-m-1=t0,05;8=2,306, где m – количество переменных.
ta0>tтаблta1>tтаблta2<tтаблta3<tтаблЗначит, коэффициенты a0 и a1 являются статистически значимыми, а коэффициенты a2 и a3 не являются статистически значимыми.
6. Построим точечный и интервальный прогнозы на 2 шага вперед на основе приростов от фактически достигнутого уровня.
Средний абсолютный прирост Х1:
САПX1=X1(12)-X1(1)n-1=-12,63-(-5,57)12-1=-0,64X1р13=X112+САПX1∙1=-12,63+(-0,64)*1=-13,27X1р14=X112+САПX1∙2=-12,63+(-0,64)*2=-13,91Средний абсолютный прирост Х2:
САПX2=X2(12)-X2(1)n-1=7,33-7,9712-1=-0,06X2р13=X212+САПX2∙1=7,33+(-0,06)*1=7,27X2р14=X212+САПX2∙2=7,33+(-0,06)*2=7,21Средний абсолютный прирост Х3:
САПX3=X3(12)-X3(1)n-1=5-312-1=0,18X3р13=X312+САПX3∙1=5+0,18*1=5,18X3р14=X312+САПX3∙2=5+0,18*2=5,36Для получения прогнозных оценок прибыли по модели Y=2,0032-0,0317X1+0,0355X2-0,016X3 подставим в нее найденные прогнозные значения факторов:
Yр13=2,0032-0,0317*-13,27+0,0355*7,27-0,016*5,18=2,6Yр14=2,0032-0,0317*-13,91+0,0355*7,21-0,016*5,36=2,61Доверительный интервал прогноза будет иметь следующие границы:
Для X(13):
Верхняя граница прогноза: YрN+1+U(1)Нижняя граница прогноза: : YрN+1-U1U1=SetкрXпрTXTX-1XпрSE=0,129tкр=tα;n-m-1=t0,05;8=2,306XпрT=1;-13,27;7,27;5,18XTX-1=6,24180,1614-0,63360,08640,16140,0054-0,015-0,0011-0,6336-0,0150,0917-0,05030,0864-0,0011-0,05030,0666 Умножение матриц приведено в приложении 4.
U1=0,122*2,306*0,4823=0,195Для X(14):
Верхняя граница прогноза: YрN+1+U(2)Нижняя граница прогноза: : YрN+1-U2U1=SetкрXпрTXTX-1XпрSE=0,129tкр=tα;n-m-1=t0,05;8=2,306XпрT=1;-13,91;7,21;5,36XTX-1=6,24180,1614-0,63360,08640,16140,0054-0,015-0,0011-0,6336-0,0150,0917-0,05030,0864-0,0011-0,05030,0666U2=0,122*2,306*05515=0,209Таблица прогнозов
Таблица 9
Упреждение Прогноз Нижняя граница Верхняя граница
1 2,6 2,405 2,795
2 2,61 2,401 2,819
График 1
Прогноз значений потребления электроэнергии в январе и феврале 2021 года

Приложения по использованию инструментов Excel
Приложение 1 (Использование инструмента Корреляция).
Рисунок 1

Приложение 1 (Использование инструмента Корреляция).
Рисунок 2

Приложение 1 (Использование инструмента Корреляция)
Рисунок 3

Приложение 2 (Использование инструмента Регрессия)
Рисунок 4

Приложение 2 (Использование инструмента Регрессия)
Рисунок 5

Приложение 2 (Использование инструмента Регрессия)
Рисунок 6

Приложение 3 (Использование функции МОБР)
Рисунок 7

Приложение 3 (Использование функции МОБР)
Рисунок 8

Приложение 4 (Умножение матриц)
Рисунок 9

СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫВаснев С.А. Статистика.-М.-.МГУП, 2011.- 170 с.
Глинский В.В. Сборник задач по теории статистики. - М.: ИНФРА-М, 2012. -257 с.
Общая теория статистики. Ефимова М.Р., Петрова Е.В. – М.: ИНФРА, 2010. – 396 с.
Общая теория статистики: Статистическая методология в изучении коммерческой деятельности: Учебник / Под ред. О.Э. Башиной, А.А. Спирина. – 5-е изд., доп, и перераб. - М.: Финансы и статистика, 2011. – 440 с.: ил.
Т.Н Носкова. Практикум по статистике. Методические указания и задания к курсовой работе для студентов экономических специальностей. - М.:НГТУ, 2018-90с.
Шмойлова Р.А., Минашкин В.Г. и др. Теория статистики. - М. «Финансы и статистика», 2015. - 656 с.