Уравнение для прямой линии в случае множественной линейной регрессии имеет следующий вид:
y = m1x1 + m2x2 +... + b,
где зависимое значение y является функцией независимых значений x1, x2, … xn. Значения m - это коэффициенты, соответствующие каждой независимой переменной x, а b - это постоянная.
Функция ЛИНЕЙН возвращает массив { mn;mn-1;...;m1;b}. ЛИНЕЙН может также возвращать дополнительную регрессионную статистику. В приведенной ниже таблице 2 показано, в каком порядке возвращается дополнительная регрессионная статистика.
Таблица 2. Результат функции ЛИНЕЙН.
| mn | mn-1 | … | m2 | m1 | b |
| sen | sen-1 | … | se2 | se1 | seb |
| R2 | sev | ||||
| F | df | ||||
| ssreq | ssresid |
Размерность массива, возвращаемого функцией ЛИНЕЙН определяется следующим образом: (n + 1) х 5. Наибольший интерес представляет первая строка массива: здесь мы видим все коэффициенты m и свободный член b. Обратите внимание, что коэффициенты m возвращаются в обратном порядке (от mn до m1).
Таблица 3. Подробное описание результата функции ЛИНЕЙН.
| Величина | Описание |
| se1,se2,...,sen | Стандартные значения ошибок для коэффициентов m1,m2,...,mn. |
| seb | Стандартное значение ошибки для постоянной b (seb = #Н/Д, если константа имеет значение ЛОЖЬ). |
| R2 | Коэффициент детерминированности, нормированный от 0 до 1. Если он равен 1, то имеет место полная корреляция с моделью, если коэффициент детерминированности равен 0, то уравнение регрессии неудачно для предсказания значений y. |
| sey | Стандартная ошибка для оценки y. |
| F | F -статистика, или F -наблюдаемое значение. F -статистика используется для определения того, является ли наблюдаемая взаимосвязь между зависимой и независимой переменными случайной или нет. |
| df | Степени свободы. Степени свободы полезны для нахождения F -критических значений в статистической таблице. Для определения уровня надежности модели нужно сравнить значения в таблице с F -статистикой, возвращаемой функцией ЛИНЕЙН. |
| ssreg | Регрессионная сумма квадратов. |
| ssresid | Остаточная сумма квадратов. |
Задания.
Задача 1. Определить курс USD через шесть месяцев, если известен курс за предшествующие полгода: 1740р., 1760р., 1820р., 1840р., 1870р., 1890р. Расчет произвести различными статистическими функциями и сравнить полученные результаты. Какая из функций наиболее точно описывает данную зависимость? Для расчетов воспользуйтесь функциями, указанными в таблице 4.
Таблица 4.
| Месяцы (x) | Исход. данные (известные y) | =ЛИНЕЙН y=mx+b | =РОСТ | =ТЕНДЕНЦИЯ | =ЛГРФПРИБЛ y=bm^x | ПРЕДСКАЗ | |||||
| Расчетные данные | Коэффициент корреляции | Расчетные данные | Коэффициент корреляции | Расчетные данные | Коэффициент корреляции | Расчетные данные | Коэффициент корреляции | Расчетное значение | |||
| m | b | m | b | ||||||||
Задача 2. Определить доход фирмы, занимающейся продажей автомобилей в 9-ом месяце, если ее доходы за предшествующие шесть месяцев составил $1200, $1260, $1450, $1780, $1900, $2400. Задачу решить графическим методом аппроксимации (способ 4), используя различные виды трендов (линейный, логарифмический, экспоненциальный, степенной). Какая из функций наиболее точно описывает данную зависимость?
Задача 3. Используя множественный регрессионный анализ, определить оценочную стоимость 3-х комнатной квартиры в центре города, жилой площадью 56 кв. м, кухня-12 кв. м, на 3-ем этаже, квартира с балконом и телефоном.
Рассчитать статистику и коэффициент корреляции.
Таблица 5.
| Улица | Цена USD | Телефон | Площадь | Этаж | Балкон Лоджия | |
| жилая | кухня | |||||
| Машерова | 40 000 | + | 40,0 | 8,0 | Л | |
| Кропоткина | 44 000 | + | 54,0 | 8,0 | Б | |
| Свердлова | 44 000 | + | 60,1 | 12,5 | - | |
| В.Хоружей | 39 000 | + | 47,5 | 7,0 | Л | |
| Берестянская | 45 000 | + | 48,5 | 7,5 | Б | |
| Ф.Скорины | 47 000 | + | 46,8 | 8,0 | Л | |
| Варвашени | 48 000 | - | 53,0 | 7,7 | - | |
| Богдановича | 50 000 | + | 52,1 | 8,1 | Б | |
| Захарова | + | Б |
Примечание! Для расчетов необходимо нечисловую информацию, указанную в таблице, представить в виде чисел, например, наличие телефона вместо «+» указать «1», а отсутствие - «0», наличие лоджии - «2», балкона - «1».
Можно ли считать расчет достоверным? Проанализируйте, как влияет каждый параметр на стоимость.
Задача 4. Исследовать зависимость производительности труда от текучести кадров и уровня механизации работ с помощью функций =ЛИНЕЙН() и =ЛГРФПРИБЛ(). Данные для расчета приведены в Таблице 6. Как повлияет увеличение текучести кадров на 1% на производительность труда?
Таблица 6.
| Предприятие | Текучесть рабочей силы Х1, % | Уровень механизации работ Х2, % | Производительность труда Y,% | Расчет производительности ЛИНЕЙН | Расчет производительности ЛГРФПРИБЛ | Производительность при текучести +1% ЛИНЕЙН | Производительность при текучести+1% ЛГРФПРИБЛ | Изменение производительности (7)-(5) | Относительное изменение производительности (8)/(6) |
| (1) | (2) | (3) | (4) | (5) | (6) | (7) | (8) | (9) | (10) |
| Автопромсборка НПП | 0,15 | 10,3 | |||||||
| Барановичский завод автоматических линий | 0,18 | 9,6 | |||||||
| БЕЛМАШПРИБОР Концерн | 0,19 | 8,9 | |||||||
| БЕЛПОДШИПНИК ЗАО | 0,44 | 4,7 | |||||||
| БЕЛРОБОТ ЗАО | 0,35 | 6,3 | |||||||
| БелТОПАЗ Гродненский завод токарных патронов | 0,28 | 5,4 | |||||||
| Вистан Витебский станкостроительный завод | 0,23 | 6,5 | |||||||
| Гомельский станкостроительный завод им.Кирова ГП | 0,36 | 5,1 | |||||||
| Дзержинский экспериментально-механический завод | 0,42 | 6,2 | |||||||
| Завод станкопринадлежностей АО | 0,26 | 5,3 | |||||||
| Минский завод автоматических линий им.Машерова | 0,23 | 5,8 | |||||||
| Молодечненский станкостроительный завод | 0,37 | ||||||||
| Пинский опытно-механический завод ОАО | 0,57 | 5,1 | |||||||
| Святовит СП | 0,37 | 4,3 | |||||||
| Станколиния МП | 0,28 | 4,6 | |||||||
| Станкостроительный завод им Кирова | 0,24 | 6,3 | |||||||
| Автогидроусилитель Борисовский завод | 0,28 | 7,7 | |||||||
| Коэффициент аппроксимации R2 |
Лабораторная работа №2. Использование надстройки "Поиск решения".
Цель работы.
Научиться использовать надстройку MS Excel «Поиск решения» для решения финансовых задач.