Разработка системы учета и прогнозирования ежедневных поступлений страховых взносов на обязательное пенсионное страхование (стр. 5 из 11)
Таблица 1.3 - Преобразованные данные
| A | B | C | D | E | F | G | H | I | J | |
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 |
| 1 | y | x | x2 | xy | x3 | x4 | x2y | ln(y) | x*ln(y) | |
| 2 | 1 | 3,83E+07 | 2,68E+07 | 1,47E+15 | 1,03E+15 | 5,62E+22 | 2,15E+30 | 5,62E+22 | 17,46 | 6,69E+08 |
| 3 | 2 | 2,79E+07 | 1,57E+07 | 7,78E+14 | 4,37E+14 | 2,17E+22 | 6,06E+29 | 2,17E+22 | 17,14 | 4,78E+08 |
| 4 | 3 | 2,69E+07 | 2,66E+07 | 7,25E+14 | 7,16E+14 | 1,95E+22 | 5,26E+29 | 1,95E+22 | 17,11 | 4,61E+08 |
| 5 | 4 | 4,79E+07 | 7,70E+07 | 2,29E+15 | 3,68E+15 | 1,10E+23 | 5,24E+30 | 1,10E+23 | 17,68 | 8,46E+08 |
| 6 | 5 | 6,12E+07 | 5,40E+07 | 3,75E+15 | 3,31E+15 | 2,30E+23 | 1,41E+31 | 2,30E+23 | 17,93 | 1,10E+09 |
| 7 | 6 | 6,40E+07 | 4,41E+07 | 4,09E+15 | 2,82E+15 | 2,62E+23 | 1,68E+31 | 2,62E+23 | 17,97 | 1,15E+09 |
| 8 | 7 | 4,25E+07 | 3,47E+07 | 1,81E+15 | 1,48E+15 | 7,68E+22 | 3,27E+30 | 7,68E+22 | 17,57 | 7,47E+08 |
| 9 | 8 | 8,09E+07 | 4,89E+07 | 6,55E+15 | 3,96E+15 | 5,30E+23 | 4,29E+31 | 5,30E+23 | 18,21 | 1,47E+09 |
| 10 | 9 | 6,60E+07 | 5,41E+07 | 4,36E+15 | 3,57E+15 | 2,88E+23 | 1,90E+31 | 2,88E+23 | 18,01 | 1,19E+09 |
| 11 | 10 | 9,71E+07 | 7,22E+07 | 9,44E+15 | 7,01E+15 | 9,17E+23 | 8,91E+31 | 9,17E+23 | 18,39 | 1,79E+09 |
| 12 | 11 | 2,10E+08 | 1,34E+08 | 4,41E+16 | 2,81E+16 | 9,27E+24 | 1,95E+33 | 9,27E+24 | 19,16 | 4,03E+09 |
| 13 | 12 | 4,05E+07 | 6,14E+07 | 1,64E+15 | 2,49E+15 | 6,65E+22 | 2,70E+30 | 6,65E+22 | 17,52 | 7,10E+08 |
| 14 | 13 | 1,35E+07 | 2,42E+05 | 1,84E+14 | 3,27E+12 | 2,49E+21 | 3,37E+28 | 2,49E+21 | 16,42 | 2,22E+08 |
| 15 | 14 | 1,07E+07 | 2,82E+07 | 1,15E+14 | 3,03E+14 | 1,24E+21 | 1,33E+28 | 1,24E+21 | 16,19 | 1,74E+08 |
| 16 | 15 | 1,09E+07 | 1,07E+07 | 1,19E+14 | 1,17E+14 | 1,30E+21 | 1,42E+28 | 1,30E+21 | 16,21 | 1,77E+08 |
| 17 | 16 | 1,07E+07 | 1,24E+07 | 1,15E+14 | 1,33E+14 | 1,24E+21 | 1,33E+28 | 1,24E+21 | 16,19 | 1,74E+08 |
| 18 | 17 | 9,09E+06 | 7,19E+06 | 8,26E+13 | 6,54E+13 | 7,51E+20 | 6,83E+27 | 7,51E+20 | 16,02 | 1,46E+08 |
| 19 | 18 | 9,20E+06 | 6,53E+06 | 8,47E+13 | 6,01E+13 | 7,80E+20 | 7,18E+27 | 7,80E+20 | 16,04 | 1,48E+08 |
| 20 | 19 | 1,36E+07 | 8,11E+06 | 1,86E+14 | 1,11E+14 | 2,54E+21 | 3,46E+28 | 2,54E+21 | 16,43 | 2,24E+08 |
| 21 | 20 | 2,21E+07 | 1,11E+07 | 4,87E+14 | 2,45E+14 | 1,07E+22 | 2,37E+29 | 1,07E+22 | 16,91 | 3,73E+08 |
| 22 | 21 | 1,43E+07 | 2,60E+05 | 2,05E+14 | 3,72E+12 | 2,93E+21 | 4,19E+28 | 2,93E+21 | 16,48 | 2,36E+08 |
| 23 | 22 | 2,71E+07 | 5,08E+07 | 7,36E+14 | 1,38E+15 | 2,00E+22 | 5,42E+29 | 2,00E+22 | 17,12 | 4,64E+08 |
| 24 | Сумма | 9,45E+08 | 9,45E+08 | 8,33E+16 | 6,10E+16 | 1,19E+25 | 2,14E+33 | 1,19E+25 | 3,78E+02 | 1,70E+10 |
где
у – ежедневные платежи марта 2009 года;
х – ежедневные платежи марта 2008 года.
Шаг 2
Аппроксимируем функцию 
линейной функцией 
. Для определения коэффициентов 
и 
воспользуемся системой
Система 4
Используя итоговые суммы таблицы 3, расположенные в ячейках B24, C24, D24, E24, запишем систему в виде
Система 5
решив которую, получаем: а1 = 22415273,4 и а2 = 0,478067637. Решение системы производили средствами MS Excel. Результаты представлены в таблице 4
Таблица 1.4 - Решение системы уравнений в MS Excel
| A | B | С | D | |
| 1 | 22 | 9,45E+08 | 9,45E+08 | |
| 2 | 9,45E+08 | 8,33E+16 | 6,1E+16 | |
| 3 | ||||
| 4 | 0,088583 | -1E-09 | 22415273 | а1 |
| 5 | -1E-09 | 2,34E-17 | 0,478068 | а2 |
В таблице 4 в ячейках А4:В5 записана формула {=МОБР(A1:B2)}.
В ячейках C4:C5 записана формула {=МУМНОЖ(A4:B5;C1:C2)}.
Шаг 3
Далее аппроксимируем функцию 
квадратичной функцией 
. Для определения коэффициентов , и воспользуемся системой
Система 6
Используя итоговые суммы таблицы 3, расположенные в ячейках B24, C24, D24, E24, F24, G24, H24, запишем систему в виде
Система 7
решив которую, получаем а1 = 114949314, а2 = -3,46784603, а3 = 2,0307E-08.
Таким образом, квадратичная аппроксимация имеет вид
у = 114949314 - 3,46784603*х + 2,0307E-08*х2
Решение системы производили средствами MS Excel. Результаты представлены в таблице 5.
Таблица 1.5 - Решение системы уравнений в случае квадратичной аппроксимации
| A | B | C | D | E | |
| 1 | 22 | 9,45E+08 | 8,33E+16 | 9,45E+08 | |
| 2 | 9,45E+08 | 8,33E+16 | 1,19E+25 | 6,1E+16 | |
| 3 | 8,33E+16 | 1,19E+25 | 2,14E+33 | 1,19E+25 | |
| 4 | |||||
| 5 | 0,185801 | -5,1E-09 | 2,13E-17 | 1,15E+08 | а1 |
| 6 | -5,1E-09 | 2E-16 | -9,1E-25 | -3,46785 | а2 |
| 7 | 2,13E-17 | -9,1E-25 | 4,68E-33 | 2,03E-08 | а3 |
Шаг 4
Теперь аппроксимируем функцию экспоненциальной функцией . Для определения коэффициентов и прологарифмируем значения и используя итоговые суммы таблицы 3, расположенные в ячейках B24, D24, I24, J24 получим систему
Система 8
где .
Решив систему, найдем с = 16,45413618, а2 = 1,71028E-08.
После потенцирования получим а1 = 13993957,34.
Таким образом, экспоненциальная аппроксимация имеет вид
.
Решение системы проводили, пользуясь средствами Microsoft Excel. Результаты представлены в таблице 6.
Таблица 1.6 - Решение системы уравнений при экспоненциальной аппроксимации
| A | B | C | D | |
| 1 | 22 | 9,45E+08 | 378,1502 | |
| 2 | 9,45E+08 | 8,33E+16 | 1,7E+10 | |
| 3 | ||||
| 4 | 0,088583 | -1E-09 | 16,45414 | с |
| 5 | -1E-09 | 2,34E-17 | 1,71E-08 | а2 |
| 6 | 13993957 | а1 |
В ячейках А4:В5 записана формула {=МОБР(А1:В2)}.
В ячейках С4:С5 записана формула {=МУМНОЖ(А4:В5;С1:С2)}.
В ячейке С6 записана формула =EXP(C4).
Вычислим среднее арифметическое значение показателей х и у в таблице 1.7:
Таблица 1.7 - Среднее арифметическое показателей
| В | С | |
| 49 | Xcp | 35 673 235,85 |
| 50 | Ycp | 42 946 701,47 |
Шаг 5
Для того, чтобы рассчитать коэффициент корреляции и коэффициент детерминации, данные целесообразно расположить в виде таблицы 7, которая является продолжением таблицы 3.
Таблица 1.8 - Расчет коэффициентов корреляции и детерминации
| K | L | M | N | O | P | Q | R | |
| 1 | (Х-Хср) | (У-Уср) | произв | (Х-Хср)^2 | (У-Уср)^2 | линейная | квадратичная | экспон |
| 2 | 2,62E+06 | -4,65E+06 | -1,22E+13 | 6,88E+12 | 2,16E+13 | 5,89E+12 | 6,95E+14 | 1,29E+14 |
| 3 | -7,78E+06 | -1,51E+07 | 1,17E+14 | 6,05E+13 | 2,27E+14 | 6,17E+13 | 3,74E+13 | 2,86E+13 |
| 4 | -8,74E+06 | -1,60E+07 | 1,40E+14 | 7,64E+13 | 2,56E+14 | 6,98E+13 | 8,73E+13 | 2,26E+13 |
| 5 | 1,22E+07 | 4,91E+06 | 5,98E+13 | 1,48E+14 | 2,41E+13 | 6,56E+12 | 2,74E+15 | 2,60E+14 |
| 6 | 2,56E+07 | 1,83E+07 | 4,68E+14 | 6,54E+14 | 3,35E+14 | 9,13E+13 | 6,81E+15 | 4,56E+14 |
| 7 | 2,83E+07 | 2,10E+07 | 5,96E+14 | 8,02E+14 | 4,43E+14 | 1,21E+14 | 7,71E+15 | 4,92E+14 |
| 8 | 6,84E+06 | -4,33E+05 | -2,96E+12 | 4,68E+13 | 1,88E+11 | 5,12E+10 | 1,47E+15 | 1,84E+14 |
| 9 | 4,53E+07 | 3,80E+07 | 1,72E+15 | 2,05E+15 | 1,44E+15 | 3,93E+14 | 1,29E+16 | 6,29E+14 |
| 10 | 3,04E+07 | 2,31E+07 | 7,02E+14 | 9,23E+14 | 5,34E+14 | 1,45E+14 | 8,38E+15 | 5,17E+14 |
| 11 | 6,15E+07 | 5,42E+07 | 3,33E+15 | 3,78E+15 | 2,94E+15 | 8,00E+14 | 1,62E+16 | 5,49E+14 |
| 12 | 1,74E+08 | 1,67E+08 | 2,91E+16 | 3,04E+16 | 2,79E+16 | 7,61E+15 | 5,25E+15 | 8,90E+16 |
| 13 | 4,85E+06 | -2,43E+06 | -1,18E+13 | 2,35E+13 | 5,90E+12 | 1,61E+12 | 1,07E+15 | 1,57E+14 |
| 14 | -2,21E+07 | -2,94E+07 | 6,50E+14 | 4,90E+14 | 8,64E+14 | 2,35E+14 | 3,38E+15 | 1,68E+13 |
| 15 | -2,49E+07 | -3,22E+07 | 8,03E+14 | 6,22E+14 | 1,04E+15 | 2,83E+14 | 4,80E+15 | 3,69E+13 |
| 16 | -2,47E+07 | -3,20E+07 | 7,93E+14 | 6,13E+14 | 1,03E+15 | 2,79E+14 | 4,70E+15 | 3,53E+13 |
| 17 | -2,49E+07 | -3,22E+07 | 8,03E+14 | 6,21E+14 | 1,04E+15 | 2,82E+14 | 4,80E+15 | 3,69E+13 |
| 18 | -2,66E+07 | -3,39E+07 | 9,00E+14 | 7,07E+14 | 1,15E+15 | 3,12E+14 | 5,78E+15 | 5,27E+13 |
| 19 | -2,65E+07 | -3,37E+07 | 8,93E+14 | 7,01E+14 | 1,14E+15 | 3,10E+14 | 5,71E+15 | 5,15E+13 |
| 20 | -2,20E+07 | -2,93E+07 | 6,46E+14 | 4,85E+14 | 8,59E+14 | 2,34E+14 | 3,34E+15 | 1,62E+13 |
| 21 | -1,36E+07 | -2,09E+07 | 2,84E+14 | 1,85E+14 | 4,36E+14 | 1,19E+14 | 6,91E+14 | 2,72E+12 |
| 22 | -2,14E+07 | -2,86E+07 | 6,12E+14 | 4,57E+14 | 8,20E+14 | 2,23E+14 | 3,05E+15 | 1,27E+13 |
| 23 | -8,54E+06 | -1,58E+07 | 1,35E+14 | 7,30E+13 | 2,50E+14 | 6,81E+13 | 7,53E+13 | 2,38E+13 |
| 24 | 1,60E+08 | 3,73E-08 | 4,28E+16 | 4,39E+16 | 4,28E+16 | 1,16E+16 | 9,97E+16 | 9,27E+16 |
В ячейках К2 записана формула =(С2-$C$49). Затем она скопирована в ячейки К3:К23. Аналогично рассчитываются отклонения показателя у от его среднего значения уср, записанные в ячейках L2:L23.