Пример 3. 4. 5 оценка параметров модели с помощью надстройки EXCEL Анализ данных

Финансовый директор АО «Веста» рассматривает целесообразность ежемесячного финансирования инвестиционного проекта со следующими объемами нетто-платежей, тыс. руб.:

45 40 43 48 42 47 51 55 50 57 60 62.

Требуется определить:

1) Линейную модель зависимости объемов платежей от сроков (времени).

2) Оценить адекватность и точность построенной модели на основе исследования:

• случайности остаточной компоненты по критерию пиков;
• независимости уровней ряда остатков по d - критерию (в качестве критических значений следует использовать уровни d ₁ = 1,08 и d ₂ = 1,36) и по первому коэффициенту автокорреляции, критический уровень которого r (1) = 0,36;
• нормальности распределения остаточной компоненты по RS - критерию с критическими уровнями 2,7 – 3,7;
• для оценки точности модели используйте среднеквадратическое отклонение и среднюю по модулю относительную ошибку;

3) Определить размеры платежей на 3 последующих месяца (построить точечный и интервальный прогнозы на два шага вперед (для вероятности
Р= 90% используйте коэффициент = 1,812) отобразить на графике фактические данные, результаты расчетов и прогнозирования). Оценить целесообразность финансирования этого проекта, если в следующем квартале на эти цели фирма может выделить только 120 тыс.руб.

Решение

1) оценка параметров модели.

Оценка параметров модели с помощью надстройки EXCEL Анализ данных.

Построим линейную модель регрессии Y от t. Для проведения регрессионного анализа выполните следующие действия:

· Выберите команду Сервис Þ Анализ данных.

· В диалоговом окне Анализ данных выберите инструмент Регрессия, а затем щелкните на кнопке ОК.

· В диалоговом окне Регрессия в поле Входной интервал Y введите адрес одного диапазона ячеек, который представляет зависимую переменную. В поле Входной интервал Х введите адрес диапазона, который содержат значения независимой переменной t Если выделены и заголовки столбцов, то установить флажок Метки в первой строке.

· Выберите параметры вывода. В данном примере Новая рабочая книга.

· В поле График подбора поставьте флажок.

· В поле Остатки поставьте необходимые флажки и нажмите кнопку ОК.

Результат регрессионного анализа содержится в нижеприведенных таблицах (табл. 4.3.13 и 4.3.14)

Таблица 4.3.13

Таблица 4.3.14. ВЫВОД ОСТАТКА

Во втором столбце табл. 4.3.13 содержатся коэффициенты уравнения регрессии a ₀, a ₁, в третьем столбце – стандартные ошибки коэффициентов уравнения регрессии, а в четвертом – t-статистика, используемая для проверки значимости коэффициентов уравнения регрессии.

Уравнение регрессии зависимости объемов платежей от сроков (времени) имеет вид:

.