Экспоненциальная регрессия

Пример 3. а) Функция ЛГРФПРИБЛ.

Условие примера 2.

Поскольку функция в табл. 2 носит явно нелинейный характер, целесообразно искать её приближение в виде не прямой линии, как в примере 2, а в виде нелинейной кривой. Из всех видов нелинейности (гипербола, парабола и др.) Excel реализует только экспоненциальное приближение вида у = b∙m^x c помощью функции ЛГРФПРИБЛ, которая рассчитывает для этого уравнения значения b и m.

Выделим для результата блок ячеек F8:G12, введём в строку формул

Функцию =ЛГРФПРИБЛ(E2:E7;D2:D7;1;1), нажмём клавиши Ctrl+Shift+ Enter, в выделенных ячейках появится результат:

1,56628015	1,196513
0,02038299	0,07938
0,99181334	0,085268
484,599687
3,52335921	0,029083

 

Таким образом, коэффициент m = 1,556, а b = 1,197, т.е. уравнение приближающей кривой имеет вид:

 

у = 1,197∙(1,556 ^х) (13)

со стандартными ошибками для m, b и y равными 0,02, 0,07 и 0,08 соответ-ственно. Коэффициент детерминированности r² = 0,992, т.е.

полученное уравнение даёт совпадение с табличными данными с вероятностью 99,2%.

Поскольку интерполяция табл. 2 экспоненциальной кривой даёт более точное приближение (99,2%) и с меньшими стандартными ошибками для m,b и y, в качестве приближающего уравнения принимаем уравнение (13).

При х = 8 получим у = 1,197 ∙ 34,363 = 41,131 град.

б) Функция РОСТ вычисляет прогнозируемое по экспоненциальному приближению значения у для новых значений х, имеет формат:

=РОСТ (изв_знач_ у;изв_знач_ х;нов_знач_ х; константа).

Выделим блок ячеек F14:F17, введём формулу

=РОСТ(E2:E7;D2:D7;G2:G5;ИСТИНА), в выделенных ячейках появится массив чисел {27,6696434; 43,3384133; 67,8800967; 106,319248}, т.е. при х=8 значение функции у = 43,34 град. Это значение немного отличается от вычисленного в п. а), поскольку функция РОСТ использует для расчетов линию экспоненциального тренда.

Примечание. При выборе экспоненциальной приближающей кривой следует учитывать, что интерполировать ею можно только участки, где функция монотонно возрастает или убывает (при отрицательном аргументе х), т.е. функцию, имеющую точки перегиба (например, параболу, синусоиду, кривую рис. 2 – т..А и др.) следует разбить на участки монотонного изменения от одной точки перегиба до другой и каждый участок интерполировать отдельно. Для рис. 2 функцию нужно разбить на 2 участка – от начала до т..А и от т.А до конца кривой.

 

Пример 4. Линейный многомерный регрессионный анализ Предположим, что коммерческий агент рассматривает возможность закупки небольших зданий под офисы в традиционном деловом районе. Агент может ис­пользовать множественный регрессионный анализ для оценки цены здания под офис на основе следующих переменных:

y -оценочная цена здания под офис;

x₁ -общая площадь в квадратных метрах;

x₂ -количество офисов;

x₃ -количество входов;

x₄ -время эксплуатации здания в годах.

Агент наугад выбирает 11 зданий из имеющихся 1500 и получает сле­дующие данные:

	A	B	C	D	E
	х1 -площадь, м2	х2 -офисы	х3 - входы	х4- срок, лет	Цена, у.е.
			1.5
			1.5

 

"Пол-входа" означает вход только для доставки корреспонденции.

В этом примере предполагается, что существует линейная зависимость между каждой независимой переменной (x₁,x₂,x₃,x₄) и зависимой переменной (y),т.е. ценой здания под офис в данном рай­оне.

§ выделим блок ячеек А14:Е18 (в соответствии с табл. 1),

§ введем формулу =ЛИНЕЙН (E2:E12;A2:D12;ИСТИНА;ИСТИНА),

§ нажмём клавиши Ctrl+Shift+ Enter,

§ в выделенных ячейках появится результат:

 

	A	B	C	D	E
	-234.237	2553.210	12529.7682	27.6413	52317.830
	13.2680	530.66915	400.066838	5.42937	12237.361
	0.99674	970.57846	#H/Д	#H/Д	#H/Д
	459.753		#H/Д	#H/Д	#H/Д
		5652135.3	#H/Д	#H/Д	#H/Д

 

Уравнение множественной регрессии y=m_l ∙ x_l+m₂ ∙ x₂+m₃ ∙ x₃+m₄ ∙ x₄+b теперь может быть получено из строки 14:

y=27,64 ∙ x₁+12530 ∙ x₂+2553 ∙ x₃-234,24 ∙ x₄+52318 (14)

Теперь агент может определить оценочную стоимость здания под офис

в том же районе, которое имеет площадь 2500 кв. м, три офиса, два входа, зда­нию 25 лет, используя следующее уравнение:

у=27,64 ∙ 2500+12530 ∙ 3+2553 ∙ 2-234,24 ∙ 25+52318=158261 у.е.

Это значение может быть вычислено с помощью функции ТЕНДЕНЦИЯ:

=ТЕНДЕНЦИЯ (Е2:Е12; A2:D12; {2500;3;2;25}).

Если вместо функции ЛИНЕЙН провести интерполирование таблицы с помощью функции

=ЛГРФПРИБЛ(E2:E12;A2:D12;ИСТИНА;ИСТИНА), то

для получения уравнения множественной экспоненциальной регрессии будет выведен результат:

 

0,99835752	1,0173792	1,0830186	1,0001704	81510,335
0,00014837	0,0065041	0,0048724	6,033E-05	0,1365601
0,99158875	0,0105158	#Н/Д	#Н/Д	#Н/Д
176,832548		#Н/Д	#Н/Д	#Н/Д
0,07821851	0,0006635	#Н/Д	#Н/Д	#Н/Д
#Н/Д	#Н/Д	#Н/Д	#Н/Д	#Н/Д

 

Коэффициент детерминированности здесь составляет 0,992 (99,2%), т.е. меньше, чем при линейной интерполяции, поэтому в качестве основного следует оставить уравнение множественной регрессии (14).

Таким образом, функции ЛИНЕЙН, ЛГРФПРИБЛ, НАКЛОН определяют коэффициенты, свободные члены и статистические параметры для уравнений одномерной и множественной регрессии, а функции ТЕНДЕНЦИЯ, ПРЕДСКАЗ, РОСТ позволяют получить прогноз новых значений без составления уравнения регрессии по значениям тренда.

Контрольные вопросы

1 Сущность регрессионного анализа, его использование для прогнозирования функций.

2 Как получить уравнение одномерной линейной регрессии, каков синтаксис функций линейного приближения?

3 Как получить уравнение многомерной линейной регрессии, каков синтаксис функции?

4 Как получить уравнение одномерной экспоненциальной регрессии, каков синтаксис функции экспоненциального приближения?

5 Как получить уравнение многомерной экспоненциальной регрессии, каков синтаксис функции экспоненциального приближения?

6 Что выполняют функции ЛИНЕЙН, ТЕНДЕНЦИЯ, РОСТ, ЛГРФПРИБЛ, ПРЕДСКАЗ?

7 Каковы правила ввода и использования табличных формул?

8 Как на гистограмме исходных данных добавить линию тренда?

9 Как с помощью линии тренда отобразить прогнозируемые величины?

Задание

Вариант задания к данной лабораторной работе включает две задачи. Для каждой из них необходимо составить и определить:

1. Таблицу исходных данных, а также значений, полученных методами линейной и экспоненциальной регрессии.

2. Коэффициенты в уравнениях прямой и экспоненциальной кривой (функции ЛИНЕЙН и ЛГРФПРИБЛ), напишите уравнения прямой и экспоненциальной кривой для простой и множественной регрессии..

3. Погрешности (ошибки) прямой и экспоненциальной кривой. вычислений для коэффициентов и функций, коэффициенты детерминированности. Оценить, какой тип регрессии наилучшим образом подходит для вашего варианта задания.

4. Прогноз изменения данных, выполненный с использованием линей­ной и экспоненциальной регрессии (функции ТЕНДЕНЦИЯ, ПРЕДСКАЗ, РОСТ).

5. Построить гистограмму (или график) исходных данных для задачи 1 (одномерная регрессия), отобра­зить на ней линию тренда, а также соответствующее ей уравнение и коэффициент детерминированности.

Варианты заданий.

(номер варианта определяет преподаватель)

Вариант 1

1 На рынке наблюдается стойкое снижение цен на компьютеры. Сделать прогноз, сколько необходимо будет снизить цену на компьютеры в следующем месяце в Вашей фирме, чтобы как минимум сравнять ее с ценой на ана­логичные компьютеры в конкурирующей фирме, если известна динамика изменения цен на них в конкурирующей фирме за последние 12 месяцев.

Для выполнения задания нужно ввести ряд из 12 ячеек с ценами конкурирующей фирмы, сделать прогноз цены на следующий месяц и др.(см. Задание).

2 Известна структура расходов фирмы на рекламу в газетах, на радио, в журналах, на телевидении, на наружную рекламу (в процентах от общей суммы), а также оборот фирмы в каждом за последние 6 месяцев. Какой оборот можно ожидать в следующем месяце, если предполагается следующая структура расходов на рекламу: газеты-40%, журналы-40%, радио-5%, телевидение-14%, наружная реклама-1%.

 

Для выполнения задания нужно составить таблицу со столбцами вида:

Месяц	Х1 – газеты, %	Х2 –журналы, %	Х3 – радио, %	Х4 – телевид.,%	Х5 – наружн. рекл.%	Оборот, $.
Январь
...	...	...	...	...	...	...
Июнь

и сделать множественный регрессионный прогноз.(см. Задание).

Вариант 2

1 Имеются данные о динамике продаж в расчете на душу населения по хлебобулочным продуктам и молочным изделиям, а также динамика изменения среднедушевого годового дохода за последние 10 лет. Для каждой группы товаров построить регрессионные модели, описывающие зависимость объемов продаж от размера доходов. Сделать прогноз об объемах продаж и размерах доходов на следующий год.

Для выполнение задания нужно составить таблицу вида:

Годы			...
х1 – хлеб, кг	0,5	26,7	...	42,8
х2 –молоко, л	0,45		...	39,5
у – доход, р.			...

и получить два уравнения – у =f (x₁) и у= f(х₂), сделать прогноз на следующий год для рядов х₁, х₂, у и др..(см. Задание).

2 Руководство фирмы провело оценку качеств пяти рекламных агентов по следующим признакам: х₁- эрудиция, х₂- энергичность, х₃- умение работать с

людьми, х₄ - внешность, х ₅- знание предметной области. Полученные средние оценки, нормированные от 0 до 1, были сопоставлены с оценками эффективности деятельности агентов (% успешных сделок от количества возможных). Определить, какую эффективность можно ожидать от рекламного агента, обладающего усредненными качествами. Сравнить ее со средней эффективностью упомянутых 5 агентов.

Исходные данные нужно ввести в таблицу вида:

	A	B	C	D	E	F	G
		х1-Эруд.	х2 -Энер	х3-Люди	х4-Вн.	х 5-Зн.	Эф-ть
	Агент 1	0,8	0,2	0,4	0,6	1,0	76%
.	...	...	...	...	...	...	...
	Агент 5	0,5	0,7	0,3	0,4	0,74	81%
	Средняя эффективность пяти агентов
	Ср. агент	0,5	0,5	0,5	0,5	0,5

Массив ячеек B2-F6 заполняется произвольными числами от 0 до 1,

столбец G2-G6 – процентами удачных сделок по принципу «Чем выше уровень качеств агента, тем выше эффективность его работы», в ячейке G7 должна быть формула для вычисления среднего значения содержимого ячеек G2-G6, в ячейке G8 нужно вычислить значение эффективности для среднего агента по формуле, полученной в результате множественного регрессионного анализа работы пяти агентов. Остальные пункты – см. Задание.

Вариант 3

1 Автомобильный салон имеет данные о количестве проданных автомобилей "Мерседес" и "БМВ" за последние 4 квартала. Учитывая тенденцию изменения объема продаж, определить, каких автомобилей необходимо закупить больше ("Мерседес" или "БМВ") в следующем квартале?

Для выполнения задания нужно составить и заполнить таблицу вида

	квартал	квартал	квартал	квартал	Новый квартал
Мерседес
БМВ

сделать прогноз продаж на новый квартал и выполнить другие пункты Задания.

2 Известны следующие данные о 5 недавно проданных подержанных автомобилях: х₁ - стоимость продажи, х₂ - стоимость аналогичного нового автомобиля, х₃ - год выпуска, х₄ - пробег, х₅- кол-во капитальных ремонтов, х₆- экспертные заключения о состоянии кузова и техническом состоянии автомобилей (по 10-бальной шкале). Определить, сколько может стоить автомобиль с соответствующими характеристиками: 20 000 руб., 34 000 руб., 1990 г., 140000 км., 0, 6 – см. пример 4.

Вариант 4 1 Определить минимально необходимый тираж ежемесячного журнала " Speed-Info " и возможный доход от размещения в нем рекламы в следующем месяце, если известны данные об объемах продаж этого журнала и доходах от размещения рекламы за прошедшие 12 месяцев (считать, что расценки на рекламу не менялись).

Для выполнения задания нужно составить таблицу вида

Месяц			...
Тираж			...
Доход			...

и заполнить ячейки за 12 месяцев условными данными. По этим данным нужно сделать линейный и экспоненциальный прогноз и др. (см. Задание).

2 В целях привлечения покупателей и увеличения оборота фирма проводит стратегию ежемесячного снижения цен на свой товар. На основании

данных о динамике изменения цен, объемов продаж в данной фирме и еще в 3 конкурирующих фирмах за последние 12 месяцев сделать прогноз о том, возрастет ли объем продаж у данной фирмы при очередном снижении цен в следующем месяце, если предположить, что цены и объемы у конкурентов в следующем месяце будут средние за рассматриваемый период.

Для выполнения задания нужно составить таблицу вида

 

	A	B	C	D	T	F	G	H	I
	мес	Фирма	Конкурент 1	Конкурент 2	Конкурент 3
		У1- объём	Х1- цена	Х2- объём	Х3- цена	Х4- объём	Х5 -цена	Х6 -объём	Х7 -цена
..	...	...	...	...	...	...	...	...	...

 

Вариант 5

1 На основании данных о курсе американского доллара и немецкой марки в первом полугодии сделать прогноз о соотношении данных валют на второе полугодие. Во что будет выгоднее вкладывать деньги в конце года?

Для выполнения задания нужно составить таблицу вида

 

Месяц								...
Доллар	24,.5	24,9	25,7	26,9	28,0	28,8
Марка	72,1	76,3	79,6	85,3	89,7	90,9

 

и сделать линейный прогнозы на следующие 6 месяцев и др.(см. Задание).

2 Известны данные за последние 6 месяцев о том, сколько раз выходила реклама фирмы, занимающейся недвижимостью, на телевидении – х₁, радио – х₂, в газетах и журналах – х₃, а также количество звонков – у₁ и количество совершенных сделок у₂. Какое соотношение количества совершенных сделок к количеству звонков у (в %) можно ожидать в следующем месяце, если известно, сколько раз выйдет реклама в каждом из перечисленных средств массовой информации.

Для выполнения задания нужно составить и заполнить таблицу вида

 

	A	B	C	D	E
		х1	х2	х3	y = y2/y1·100%
	Январь				78%
..	...	...	...	...	...
	Июнь				89%
	Июль

 

и выполнить применительно к таблице пункты Задания.

Вариант 6

1 Для некоторого региона известен среднегодовой доход населения, а также данные о структуре расходов (тыс. руб. в год) за последние 5 лет по следующим статьям: питание – х₁, жилье – х₂, одежда – х₃, здоровье – х₄, транспорт –х₅, отдых – х₆, образование – х₇. На основании известных данных провести анализ потребительского кредита (или накопления) в следующем году.

Для выполнения задания нужно составить таблицу вида

 

Годы	х1	х2	х3	х4	х5	х6	х7	Σхi	Доход	Кредит
					0,3			18,3	21,4	3,1
...	..	..	..	..	...	..	...	...	...	...
					1,2		6,5	24,7	26,2	1,5

 

В ячейках столбца Σх_i должны быть записаны формулы, вычисляющие суммы всех расходов х₁ + х₂+...+ х₇ в каждом году, в ячейках столбца Доход – Доход соответствующие среднегодовые доходы, в ячейках столбца Кредит – формулы разности содержимого ячеек с ежегодными доходами и затратами, т.е. Кредит = Доход - Σх_{i .}. Затем для столбца Кредит нужно выполнить регрессионный прогноз на следующий год и другие пункты Задания.

2 Для 10 однокомнатных квартир, расположенных в одном районе, известны следующие данные: общая площадь - х₁, жилая площадь – х₂, площадь кухни – х₃, наличие балкона – х₄, телефона – х₅, этаж – х₆, а также стоимость – х₇. Определить, сколько может стоить однокомнатная квартира в этом районе без балкона, без телефона, расположенная на 1-ом этаже, общей площадью 28 кв. м, жилой- 16 кв. м, с кухней 6 кв. м.

Вариант 7

1 Определить возможный прирост населения (кол-во человек на 1000 населения) в 2005 году, если известны данные о кол-ве родившихся и умерших на 1000 населения в 1991-2000 годах.

2 После некоторого спада наметился рост объемов продаж матричных

принтеров. Используя данные об объемах продаж, ценах на матричные, струйные и лазерные принтеры, а также на их расходные материалы за последние 6 месяцев, определить возможный спрос на матричные принтеры в следующем месяце. Проанализируйте, связано ли увеличение спроса на матричные принтеры с уменьшением спроса на струйные и лазерные.

Для выполнения задания нужно составить и заполнить таблицу вида:

 

	Матричные принтеры	Струйные принтеры	Лазерные принтеры
	Спрос, у1	Цена, x1	Р.мат z1	Спрос, y2	Цена, x2	Р.мат. z2	Спрос, y3	Цена x3	Р.мат. z3
..	...	...	...	...	...	...	...	...	...

 

сделать прогноз на седьмой месяц по уравнению у₁= f(x_1,z₁), получить уравнение у₁ = f(y₂, x₂, z₂, y₃, x₃, z₃) и проанализировать его. Если слагаемые y₂ и y₃ входят в регрессионное уравнение со знаком “-“, то уменьшение спросов y₂ и y₃ ведёт к увеличению спроса у₁. Выполнить другие пункты Задания.

Вариант 8

1 Построить прогноз развития спроса населения региона на телевизоры, если известна динамика продаж телевизоров (тыс. шт.) и динамика численности населения данного региона (тыс. чел.) за последние 10 лет.

Для выполнения задания нужно составить таблицу из двух рядов (продаж телевизоров и численности населения по годам и сделать прогноз по обоим рядам на следующий год. Выполнить другие пункты Задания.

2 Размещая рекламу в 4-х изданиях, фирма собрала сведения о поступивших на нее откликов - у и сопоставила их с данными об изданиях: х₁- стоимость издания, х₂ - стоимость одного блока рекламы, х₃ - тираж, х₄ - объем аудитории, х₅- периодичность, х₆- наличие телепрограммы. Какое количество откликов можно ожидать на рекламу в издании со следующими характеристиками: 15000 руб., 10$, 1000 экз., 25000 чел., 4 раза в месяц, без телепрограммы.

Для выполнения задания нужно составить и заполнить таблицу вида

 

Данные	Отклики-у	х1	х2	х3	х4	х5	х6
Издание 1
...	...	...	...	...	...	...	...
Издание 4
Прогноз

 

сделать прогноз при заданных характеристиках и выполнить другие пункты Задания.

Вариант 9. 1 Размещая свою рекламу в двух печатных изданиях одновременно, фирма собрала сведения о количестве поступивших звонков и количестве совершенных сделок по объявлениям в каждом из указанных изданий за последние 12 месяцев. Определить, в каком из изданий и насколько эффективность размещения рекламы в следующем месяце будет больше?

Для выполнения задания нужно составить таблицу вида:

 

	Издание 1	Издание 2
Месяцы	Звонки	Сделки	Звонки	Сделки
...	...	...	...	...
13 лин.
13-эксп.

 

Эффективность определяется как сделки /звонки. Сделать линейный и экспоненциальный прогнозы по обоим изданиям, выполнить другие пункты Задания.

2 Пусть комплект мягкой мебели (диван + 2 кресла) характеризуется следующими признаками (1- есть, 0- нет): х₁- деревянные подлокотники, х₂- велюровое покрытие, х₃ - кресло-кровать, х₄ - угловой диван, х₅- раскладывающийся диван, х₆ - место для хранения белья. На основании данных о стоимости 5 комплектов мягкой мебели, для которых известны перечисленные признаки, сделать вывод о возможной стоимости комплекта с обычным раскладывающимся диваном, с местом для белья, без деревянных подлокотников, с обычными креслами.

Для выполнения задания нужно составить таблицу

Признаки	х1	х2	х3	х4	х5	х6	у1 - стоимость
Комплект 1							12560 р.
...	...	...	...	...	...	...	...
Комплект 5							10980 р.
Прогноз

сделать прогноз и выполнить другие пункты Задания.

Вариант 10

1Для двух радиостанций известны данные об изменении объема аудитории и динамике роста цен за 1 минуту эфирного времени за последние 12 месяцев. Определить, для какой радиостанции стоимость одного контакта со слушателем в следующем месяце будет меньше?

Для выполнения задания нужно составить и заполнить таблицу вида

 

	A	B	C	D	E
		Радиостанция 1	Радиостанция 2
	Месяц	Аудитория	Цена 1 мин.	Аудитория	Цена 1 мин.
...	...	...	...	...	...
	Прогноз
	Контакт

 

В строке «Контакт» в ячейках С8 и D8 должны быть записаны формулы =С7/B7 и =E7/D7 соответственно, вычисляющие стоимость 1 мин. эфира для одного слушателя в прогнозируемом месяце. Прогноз нужно выполнить для линейного и экспоненциального приближений и выбрать более достоверный, а также сделать другие пункты Задания.

2 На основании данных ежемесячных исследований известна динамика рейтинга банка (в условных единицах) за последние 6 месяцев в следующих сферах:

а) менеджмент и технология – х₁;

б) менеджеры и персонал – х₂;

в) культура банковского обслуживания – х₃;

г) имидж банка на рынке финансовых услуг – х₄;

д) реклама банка – х₅.

Определить возможное изменение количества вкладчиков данного банка в следующем месяце, если известны значения сфер рейтинга и количество вкладчиков в каждом из рассматриваемых 6 месяцев.

Для выполнения задания нужно составить и заполнить таблицу

 

	A	B	C	D	E	F	G
	Месяц	х1	х2	х3	х4	х5	Кол-во вкладчиков
..	...	...	...	...	...	...	...

и выполнить другие пункты Задания.

 

Содержание отчёта

1Название, цель, содержание работы

2 Задание своего варианта

3 Письменные ответы на контрольные вопросы

4 Выводы по работе

 

На дискете должны быть сохранены результаты работы

 

В отчете по лабораторной работе должны быть записаны все выполненные пункты Задания (таблицы, уравнения, прогнозируемые значения, стандартные ошибки, коэффициенты детерминированности, графики или гистограммы с приближающими кривыми и линиями тренда и др.

 

⇐ Предыдущая12345

Поделиться:

Читайте также:

Занятие 8. Парная корреляция и парная линейная регрессия.
Простая линейная регрессия

Воспользуйтесь поиском по сайту: