Пример 4. Предположим, что коммерческий агент рассматривает возможность закупки небольших зданий под офисы в традиционном деловом районе. Агент может использовать множественный регрессионный анализ для оценки цены здания под офис на основе следующих переменных:
y -оценочная цена здания под офис;
x1 -общая площадь в квадратных метрах;
x2 -количество офисов;
x3 -количество входов;
x4 -время эксплуатации здания в годах.
Агент наугад выбирает 11 зданий из имеющихся 1500 и получает следующие данные:
| A | B | C | D | E | |
| х1 -площадь, м2 | х2 -офисы | х3 - входы | х4- срок, лет | Цена, у.е. | |
| 1.5 | |||||
| 1.5 | |||||
"Пол-входа" означает вход только для доставки корреспонденции.
В этом примере предполагается, что существует линейная зависимость между каждой независимой переменной (x1,x2,x3,x4) и зависимой переменной (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=ml ∙ xl+m2 ∙ x2+m3 ∙ x3+m4 ∙ x4+b теперь может быть получено из строки 14:
y=27,64 ∙ x1+12530 ∙ x2+2553 ∙ x3-234,24 ∙ x4+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 (x1) и у= f(х2), сделать прогноз на следующий год для рядов х1, х2, у и др..(см. Задание).
2 Руководство фирмы провело оценку качеств пяти рекламных агентов по следующим признакам: х1- эрудиция, х2- энергичность, х3- умение работать с
людьми, х4 - внешность, х 5- знание предметной области. Полученные средние оценки, нормированные от 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 недавно проданных подержанных автомобилях: х1 - стоимость продажи, х2 - стоимость аналогичного нового автомобиля, х3 - год выпуска, х4 - пробег, х5- кол-во капитальных ремонтов, х6- экспертные заключения о состоянии кузова и техническом состоянии автомобилей (по 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 месяцев о том, сколько раз выходила реклама фирмы, занимающейся недвижимостью, на телевидении – х1, радио – х2, в газетах и журналах – х3, а также количество звонков – у1 и количество совершенных сделок у2. Какое соотношение количества совершенных сделок к количеству звонков у (в %) можно ожидать в следующем месяце, если известно, сколько раз выйдет реклама в каждом из перечисленных средств массовой информации.
Для выполнения задания нужно составить и заполнить таблицу вида
| A | B | C | D | E | |
| х1 | х2 | х3 | y = y2/y1·100% | ||
| Январь | 78% | ||||
| .. | ... | ... | ... | ... | ... |
| Июнь | 89% | ||||
| Июль |
и выполнить применительно к таблице пункты Задания.
Вариант 6
1 Для некоторого региона известен среднегодовой доход населения, а также данные о структуре расходов (тыс. руб. в год) за последние 5 лет по следующим статьям: питание – х1, жилье – х2, одежда – х3, здоровье – х4, транспорт –х5, отдых – х6, образование – х7. На основании известных данных провести анализ потребительского кредита (или накопления) в следующем году.
Для выполнения задания нужно составить таблицу вида
| Годы | х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 должны быть записаны формулы, вычисляющие суммы всех расходов х1 + х2+...+ х7 в каждом году, в ячейках столбца Доход – Доход соответствующие среднегодовые доходы, в ячейках столбца Кредит – формулы разности содержимого ячеек с ежегодными доходами и затратами, т.е. Кредит = Доход - Σхi .. Затем для столбца Кредит нужно выполнить регрессионный прогноз на следующий год и другие пункты Задания.
2 Для 10 однокомнатных квартир, расположенных в одном районе, известны следующие данные: общая площадь - х1, жилая площадь – х2, площадь кухни – х3, наличие балкона – х4, телефона – х5, этаж – х6, а также стоимость – х7. Определить, сколько может стоить однокомнатная квартира в этом районе без балкона, без телефона, расположенная на 1-ом этаже, общей площадью 28 кв. м, жилой- 16 кв. м, с кухней 6 кв. м.
Вариант 7
1 Определить возможный прирост населения (кол-во человек на 1000 населения) в 2005 году, если известны данные о кол-ве родившихся и умерших на 1000 населения в 1991-2000 годах.
2 После некоторого спада наметился рост объемов продаж матричных
принтеров. Используя данные об объемах продаж, ценах на матричные, струйные и лазерные принтеры, а также на их расходные материалы за последние 6 месяцев, определить возможный спрос на матричные принтеры в следующем месяце. Проанализируйте, связано ли увеличение спроса на матричные принтеры с уменьшением спроса на струйные и лазерные.
Для выполнения задания нужно составить и заполнить таблицу вида
| Матричные принтеры | Струйные принтеры | Лазерные принтеры | |||||||
| Спрос, у1 | Цена, x1 | Р.мат z1 | Спрос, y2 | Цена, x2 | Р.мат. z2 | Спрос, y3 | Цена x3 | Р.мат. z3 | |
| .. | ... | ... | ... | ... | ... | ... | ... | ... | ... |
сделать прогноз на седьмой месяц по уравнению у1= f(x1,z1), получить уравнение у1 = f(y2, x2, z2, y3, x3, z3) и проанализировать его. Если слагаемые y2 и y3 входят в регрессионное уравнение со знаком “-“, то уменьшение спросов y2 и y3 ведёт к увеличению спроса у1. Выполнить другие пункты Задания.
Вариант 8
1 Построить прогноз развития спроса населения региона на телевизоры, если известна динамика продаж телевизоров (тыс. шт.) и динамика численности населения данного региона (тыс. чел.) за последние 10 лет.
Для выполнения задания нужно составить таблицу из двух рядов (продаж телевизоров и численности населения по годам и сделать прогноз по обоим рядам на следующий год. Выполнить другие пункты Задания.
2 Размещая рекламу в 4-х изданиях, фирма собрала сведения о поступивших на нее откликов - у и сопоставила их с данными об изданиях: х1- стоимость издания, х2 - стоимость одного блока рекламы, х3 - тираж, х4 - объем аудитории, х5- периодичность, х6- наличие телепрограммы. Какое количество откликов можно ожидать на рекламу в издании со следующими характеристиками: 15000 руб., 10$, 1000 экз., 25000 чел., 4 раза в месяц, без телепрограммы.
Для выполнения задания нужно составить и заполнить таблицу вида
| Данные | Отклики-у | х1 | х2 | х3 | х4 | х5 | х6 |
| Издание 1 | |||||||
| ... | ... | ... | ... | ... | ... | ... | ... |
| Издание 4 | |||||||
| Прогноз |
сделать прогноз при заданных характеристиках и выполнить другие пункты Задания.
Вариант 9. 1 Размещая свою рекламу в двух печатных изданиях одновременно, фирма собрала сведения о количестве поступивших звонков и количестве совершенных сделок по объявлениям в каждом из указанных изданий за последние 12 месяцев. Определить, в каком из изданий и насколько эффективность размещения рекламы в следующем месяце будет больше?
Для выполнения задания нужно составить таблицу вида:
| Издание 1 | Издание 2 | |||
| Месяцы | Звонки | Сделки | Звонки | Сделки |
| ... | ... | ... | ... | ... |
| 13 лин. | ||||
| 13-эксп. |
Эффективность определяется как сделки /звонки. Сделать линейный и экспоненциальный прогнозы по обоим изданиям, выполнить другие пункты Задания.
2 Пусть комплект мягкой мебели (диван + 2 кресла) характеризуется следующими признаками (1- есть, 0- нет): х1- деревянные подлокотники, х2- велюровое покрытие, х3 - кресло-кровать, х4 - угловой диван, х5- раскладывающийся диван, х6 - место для хранения белья. На основании данных о стоимости 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 месяцев в следующих сферах:
а) менеджмент и технология – х1;
б) менеджеры и персонал – х2;
в) культура банковского обслуживания – х3;
г) имидж банка на рынке финансовых услуг – х4;
д) реклама банка – х5.
Определить возможное изменение количества вкладчиков данного банка в следующем месяце, если известны значения сфер рейтинга и количество вкладчиков в каждом из рассматриваемых 6 месяцев.
Для выполнения задания нужно составить и заполнить таблицу
| A | B | C | D | E | F | G | |
| Месяц | х1 | х2 | х3 | х4 | х5 | Кол-во вкладчиков | |
| .. | ... | ... | ... | ... | ... | ... | ... |
| Прогноз |
и выполнить другие пункты Задания.
Содержание отчёта
1Название, цель, содержание работы
2 Задание своего варианта
3 Письменные ответы на контрольные вопросы
4 Выводы по работе
На дискете должны быть сохранены результаты работы
В отчете по лабораторной работе должны быть записаны все выполненные пункты Задания (таблицы, уравнения, прогнозируемые значения, стандартные ошибки, коэффициенты детерминированности, графики или гистограммы с приближающими кривыми и линиями тренда и др.
Лабораторная работа №5
