2. 4 Научный инструментарий системного анализа Вопросы для самотестирования (стр. 11 из 13)
Множественный R 0,916797429
R-квадрат___________ 0,840517526
Нормированный R-ква 0,805076976
| 58,11124159 12 |
Стандартная ошибка На бл ю д ен ия
| Дисперсионный анализ | |||||
| <# | 88 | М8 | Р | Значимость F | |
| Регрессия | 2 | 160175,6991 | 80087,84954 | 23,71626658 | 0,00025835 |
| Остаток | 9 | 30392,24759 | 3376,916399 | ||
| Итого | 11 | 190567,9467 | |||
| Коэффициент Стандартная ошибка t-статистика P-Значение Нижние 95% | |||||
| Y-пересечение | -238,850358 | 179,5375101 | -1,330364657 | 0,216121449 | -644,9927319 |
| Переменная X 1 | 1,024703982 | 0,225040296 | 4,553424439 | 0,001379169 | 0,515627077 |
| Переменная X 2 | 4,271277911 | 2,287451269 | 1,867265095 | 0,094710635 | -0,903300306 |
Рисунок 18 - Результаты расчета
Уравнение регрессии, полученное с помощью инструмента Регрессия , практически не отличается от полученного с помощью функции ЛИНЕЙН, однако приведенный сопутствующий статистический материал гораздо удобнее и полнее описывает рассматриваемый пример и раскрывает его физическую сущность.
47
Так в частности, приведенный в первой таблице рисунка 18 параметр множественный R определяемый как корень квадратный из R - квадрат, является коэффициентом корреляции и определяет корреляцию между общими расходами населения и полученной комбинацией расходов, связанных с покупкой товаров и оплатой услуг.
Нормированный R - квадрат позволяет произвести оценку объема исследуемой выборки. Так в частности, при увеличении объема выборки, численное значение Нормированного R - квадрат приближалось бы к фактическому значению R - квадрат.
Вторая таблица рисунка 18 показывает результаты дисперсионного анализа.
В третьей таблице помимо коэффициентов регрессии и их стандартных погрешностей (ошибок) приведены расчетные t - статистики для каждого параметра уравнения регрессии, а также величины их доверительных интервалов. Кроме того, в таблице приводится р - значение, называемое так же р - уровень. Этот показатель находится в убывающей зависимости от уровня надежности формируемых статистических данных. Он показывает вероятность появления ошибки, связанной с распространением полученного результата на всю статистическую совокупность в целом. Например, р - уровень = 0,05 (1/20) показывает, что имеется 5% вероятность, что найденная в выборке связь между переменными является лишь случайной особенностью данной выборки. С помощью параметр р -значение имеется возможность предварительной оценки, без использования специальных статистических таблиц, значимости коэффициентов уравнения регрессии. Например, из третьей таблицы рисунка 18 видно, что р - значение второй переменной большее 0,05, что может служить основанием вывода о не значимости второй переменной. Этот вывод нами установлен ранее путем сравнения расчетного и критического значений t - статистик.
Учитывая, что уравнение регрессии адекватно описывает изучаемое явления, а также то, что t -статистика и р - значение для переменной х2не значительно отличаются от критических значений уровня надежности, принимаем решение оставить переменную х2в уравнении регрессии.
Вывод. В целом, выполненные исследования позволяют сделать вывод о том, что около 92% всех денежных расходов населения Пензенской области тратится им на покупку товаров и оплату услуг, причем около 80% из них расходуется на покупку товаров и лишь примерно 12 % на оплату услуг. Другие виды расходов, например покупка валюты, ценных бумаг и т.д. можно считать статистически не значимыми. Полученный вывод может быть использован при принятии управленческого решения по формированию потребительской корзины, расчету прожиточного минимума, оценки перспектив деятельности банковских структур и рынка ценных бумаг.
Полученное регрессионное уравнение может быть использовано и для прогнозирования расходов населения. Наиболее простой способ это подстановка в уравнение регрессии прогнозных значений переменных. Однако, MS Excel представляет более простой и надежный способ прогнозирования с ис-
48
пользованием функции ТЕНДЕНЦИЯ. Данная функция вычисляет уравнение регрессии так же как это делает функция ЛИНЕЙН, и при необходимости мо-жет применяться для новых прогнозных значений переменных.
На рисунке 19 в нижней его части показаны результаты прогнозирования с использованием функции ТЕНДЕНЦИЯ
| ЩПотрвб. расходы | ||||
| А | В С | о | ||
| Э | 409,1 | Э4.Э | 595,5 | |
| 10 | 387,7 | 84,3 | 549/ | |
| 11 | 372,9 | 79,8 | 534,5 | |
| 12 | 366,3 | 84,3 | 509,6 | |
| 13 | 377,5 | 87,2 | 546,3 | |
| и- | 404,5 | 85,2 | 476,6 | |
| 15 | 542,3 | 90,5 | 606,2 | |
| 16 | 524,7 | 100,6 | 690,5 | |
| 17 | 544,5 | 97/ | 729,9 | |
| 18 19 | 629,9 | 107,5 | 959,0 | |
| 500,0 | 200,0 | {=ТЕНЦЕНЦИЯ(С7:С18;А7:В18;А19:В23Н| | ||
| 20 400,0 | 290,0 | 1238,9 | ||
| 21 450 | 450 | 2144,341494 | ||
| 22 ЕОО | ЗОО | 1657,355404 | ||
| СП | 5501 270 | 1477,931868 | ||
Рисунок 19 - Результаты прогнозирования
Изменяя соотношения переменных х 1 (ячейка А ) и х2 (ячейка В ), результат прогноза денежных расходов населения отображается в ячейках С19:С23. Приведенная в этих ячейках сумма прогнозных расходов также может служить исходной информацией для принятия соответствующего управленческого решения.
4.3 Технология решения оптимизационных задач
Дальнейшим шагом разработки управленческого решения является учет оптимальных соотношений переменных, формирующих модель исследуемого социально - экономического явления. Так в частности, задача оптимизации для условий рассматриваемого примера может быть сформулирована в следующей постановке:
Определить оптимальное соотношение между расходами всего на-ч селения Пензенской области, затраченными на покупку товаров (х!) и рас-1 ходами, затраченными на оплату услуг (х2) таким образом, чтобы суммарные расходы населения (у) не превышали заданного (установленного). значения.
Для рассматриваемого примера, в качестве заданного (установленного) значения расходов принимаем 1000 млн. рублей.
>
49
Поставленная задача оптимизации может быть решена с помощью инструмента MS Excel Поиск решения. Для этого необходимо в меню Сервис установить инструмент Поиск решения. В результате открывается диалоговое окно, показанное на рисунке 20.
Рисунок 20 - Диалоговое окно Поиск решения
Элементами диалогового окна Поиск решения являются. 1. Поле Установить целевую ячейку необходимо для ссылки на ячейку с функцией, для которой ищется минимальное (максимальное), либо заданное значение. Для рассматриваемого примера в качестве функции используется полученное ранее регрессионное уравнение (3). Тип взаимосвязи между решением и целевой функцией задается путем установки переключателя в группе Равной. Для нахождения максимального или минимального значения целевой функции этот переключатель необходимо установить в положение Максимальному значению или Минимальному значению, соответственною. Для нахождения заданного значения целевой функции переключатель необходимо установить в положение Значению и ввести заданное значение целевой функции. Последнее соглашение является условием поставленной задачи оптимизации рассматриваемого примера, поэтому необходимо ввести значение 1000.
2. Поле Изменяя ячейки предназначено для ссылки на ячейки, являющиеся оптимизируемыми переменными Для рассматриваемого примера это параметры х 1 и х2 .
3. В поле Ограничения необходимо указать ссылки на ячейки, в которых указаны ограничения, накладываемые на оптимизируемые переменные. Ограничения добавляются по одному при нажатии на кнопку Добавить и могут быть как в виде равенств, неравенств, так и в виде целочисленных переменных. Диалоговое окно Добавление ограничений показано на рисунке21.
50
 |
| Добавление ограничения |
| Ссылка на ячейку |
Рисунок 21 - Диалоговое окно Добавление ограничений.