Тенденция(е2:е7;a2:d12;{2500;3;2;25})
При интерполяции с помощью функции
=ЛГРФПРИБЛ(Е2:Е7;A2:D12;ИСТИНА;ИСТИНА)
для получения уравнения множественной экспоненциальной регрессии выводится результат:
| 0,99835752 | 1,0173792 | 1,0830186 | 1,0001704 | 81510,335 |
| 0,00014837 | 0,0065041 | 0,0048724 | 6,033Е-05 | 0,1365601 |
| 0,99158875 | 0,0105158 | #Н/Д | #Н/Д | #Н/Д |
| 176,832548 | #Н/Д | #Н/Д | #Н/Д | |
| 0,07821851 | 0,0006635 | #Н/Д | #Н/Д | #Н/Д |
| #Н/Д | #Н/Д | #Н/Д | #Н/Д | #Н/Д |
Коэффициент детерминированности здесь составляет 0,992(99,2%), т.е. меньше, чем при линейной интерполяции, поэтому в качестве основного следует оставить уравнение множественной регрессии (14).
Таким образом, функции ЛИНЕЙН, ЛГРФПРИБЛ, НАКЛОН определяют коэффициенты, свободные члены и статистические параметры для уравнений одномерной и множественной регрессии, а функции ТЕНДЕНЦИЯ, ПРЕДСКАЗ, РОСТ позволяют получить прогноз новых значений без составления уравнения регрессии по значениям тренда.
Задание
Вариант задания к данной лабораторной работе включает две задачи. Для каждой из них необходимо составить и определить:
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 | 27,9 | 30,1 | 31,5 | 35,7 | 38,3 | 40,1 | 41,5 | 42,8 | |
| Х2-молоко, л | 0,45 | 23,8 | 25,9 | 27,4 | 33,5 | 36,8 | 38,1 | 39,5 | |||
| У-доход, р. |
и получить два уравнения – у=f(x1) и у=f(x2), сделать прогноз на следующий год для рядов х1, х2, у и др.(см. Задание).
2. Руководство фирмы провело оценку качеств пяти рекламных агентов по следующим признакам: х1- эрудиция, х2- знание предметной области. Полученные средние оценки, нормированные от 0 до 1, были сопоставлены с оценками эффективности деятельности агентов (% успешных сделок от количества возможных). Определить эффективность для агента с усреднёнными качествами. Сравнить её со среднеё эффективностью упомянутых 5 агентов.
Исходные данные нужно ввести в таблицу вида:
| А | В | С | D | E | F | G | |
| Х1-эруд. | Х2-энер | Х3-люди | Х4-вн. | Х5-зн. | Эф-ть | ||
| Агент 1 | 0,8 | 0,2 | 0,4 | 0,6 | 1,0 | 76% | |
| Агент 2 | 0,74 | 0,3 | 0,39 | 0,58 | 0,95 | 78% | |
| Агент 3 | 0,67 | 0,41 | 0,35 | 0,5 | 0,83 | 79% | |
| Агент 6 | 0,59 | 0,59 | 0,33 | 0,47 | 0,8 | 80% | |
| Агент 5 | 0,5 | 0,7 | 0,3 | 0,4 | 0,74 | 81% | |
| Средняя эффективность пяти агентов | |||||||
| Ср. агент | 0,5 | 0,5 | 0,5 | 0,5 | 0,5 |
Массив ячеек В2-F6 заполняется произвольными числами от 0 до 1, столбец G2-G6 – процентами удачных сделок по принципу «Чем выше уровень качеств агента, тем выше эффективность его работы», в ячейке G7 должна быть формула для вычисления среднего значения ячеек G2:G6, в ячейке G8 нужно вычислить значение эффективности для среднего агента по формуле, полученной в результате множественного регрессионного анализа работы пяти агентов. Остальные пункты – см Задание.
Вариант 3.
1.Автосалон имеет данные о количестве проданных автомобилей «Мерседес» и «БМВ» за последние 4 квартала. Учитывая тенденцию изменения объёма продаж, определить, каких автомобилей нужно закупить больше («Мерседес» или «БМВ») в следующем квартале?
Для выполнения задания нужно составить и заполнить таблицу вида:
| Х | |||||
| Мерседес (Y1) | |||||
| БМВ (Y2) |
сделать прогноз продаж на новый квартал и выполнить другие пункты задания.
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 месяцев сделать прогноз о том, возрастает ли объём продаж у данной фирмы при очередном снижении цен в следующем месяце, если предположить, что цены и объёмы у конкурентов в следующем месяце будут средние за рассматриваемый период.
Для выполнения задания нужно составить таблицу вида:
| Мес. | Фирма | Конкурент 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 | 29,3 | 29,7 | 30,5 | 30,9 | 31,8 | |
| Марка | 72,1 | 76,3 | 79,6 | 85,3 | 89,7 | 90,9 | 93,2 | 96,4 | 100,2 | 101,6 | 104,9 |
и сделать линейный прогнозы на следующие 6 месяцев и др. (см. Задание).
2. Известны данные за последние 6 месяцев о том, сколько раз выходила реклама фирмы, занимающейся недвижимостью, на телевидении-х1, радио-х2, в газетах и журналах-х3, а также количество звонков-у1 и количество совершённых сделок-у2. Какое соотношение количества совершённых сделок к количеству звонков у (в %) можно ожидать в следующем месяце, если известно, сколько раз выйдет реклама в каждом из перечисленных средств массовой информации.
Для выполнения задания нужно составить и заполнить таблицу вида:
| A | B | C | D | E | |
| Х1 | Х2 | Х3 | y=у2/у1*100% | ||
| 78% | |||||
| 80% | |||||
| 81% | |||||
| 84% | |||||
| 85% | |||||
| 89% | |||||
и выполнить применительно к таблице пункты Задания.
Вариант 6.
1. Для некоторого региона известен среднегодовой доход населения, а также данные о структуре расходов (тыс. руб. в год) за последние 5 лет по следующим статьям: питание-х1, жильё-х2, одежда-х3, здоровье-х4, транспорт-х5, отдых-х6, образование-х7. На основании известных данных провести анализ потребительского кредита (или накопления) в следующем 6 году.
Для выполнения задания нужно составить и заполнить таблицу вида:
| Годы | Х1 | Х2 | Х3 | Х4 | Х5 | Х6 | Х7 | Расход (∑xi) | Доход | Кредит(Y) |
| 1,3 | 0,3 | 18,6 | 21,4 | 3,1 | ||||||
| 5,2 | 2,2 | 1,2 | 1,2 | 0,4 | 4,8 | 4,5 | 19,5 | 2,5 | ||
| 5,5 | 2,5 | 1,1 | 1,4 | 0,6 | 4,6 | 4,9 | 20,6 | 23,4 | 2,8 | |
| 5,8 | 2,7 | 0,9 | 1,6 | 4,2 | 5,6 | 21,8 | 25,8 | |||
| 0,8 | 1,2 | 6,5 | 24,7 | 26,2 | 1,5 | |||||
| 7,5 | 3,3 | 0,7 | 2,2 | 1,5 | 3,8 | 26,5 | 27,5 |
В ячейках столбца ∑хi должны быть записаны формулы, вычисляющие суммы всех расходов х1+х2+…+х7 в каждом году, в ячейках столбца Доход- соответствующие среднегодовые доходы, в ячейках столбца Кредит – формулы разности содержимых ячеек с ежегодными доходами и затратами, т.е. Кредит=Доход-∑хi . Затем для столбца Кредит нужно выполнить регрессионный прогноз на следующий год и другие пункты задания.
2. Для 10 однокомнатных квартир, расположенных в одном районе, известны следующие данные: общая площадь – х1, жилая площадь – х2, площадь кухни – х3, наличие балкона – х4, телефона – х5, этаж – х6, а также стоимость – y. Определить, сколько может стоить однокомнатная квартира в этом районе без балкона, без телефона, расположенная на 1-ом этаже, общей площадью 28 кв.м., жилой-16 кв.м., с кухней 6 кв.м.
| Квартиры | X1 | X2 | X3 | X4 | X5 | Стоимость (y) |
| 7,7 | ||||||
| 46,3 | ||||||
| 9,5 | ||||||
| 14,5 | ||||||
Вариант 7.
1. Определить возможный прирост населения (кол-во человек на 1000 населения) в 2005 году, если известны данные о кол-ве родившихся и умерших на 1000 населения в 1991-2000 годах.
| Годы | |||||||||||
| Родились | |||||||||||
| Умерли |
2. После некоторого спада наметился рост объёмов продаж матричных принтеров. Используя данные об объёмах продаж, ценах на матричные, струйные и лазерные принтеры, а также на их расходные материалы за последние 6 месяцев, определить возможный спрос на матричные принтеры в следующем месяце. Проанализируйте, связано ли увеличение спроса на матричные принтеры с уменьшением спроса на струйные и лазерные.
| Матричные принтеры | Струйные принтеры | Лазерные принтеры | |||||||
| Спрос у1 | Цена х1 | Р.мат z1 | Спрос у2 | Цена х2 | Р.мат z2 | Спрос у3 | Цена х3 | Р.мат. z3 | |
Необходимо сделать прогноз на седьмой месяц по уравнению у1=f(x1,z1), получить уравнение y=(y2,x2,z2,y3x3z3) и проанализировать его. Если слагаемые у2 и у3 входят в регрессионное уравнение со знаком «-«, то уменьшение спросов у2 и у3 ведёт к увеличению спроса у1.
Вариант 8.
1. Построить прогноз развития спроса населения на телевизоры, если известна динамика продаж телевизоров (тыс. шт.) и динамика численности населения (тыс. чел.) за 10 лет. По данным таблицы сделать прогноз по обоим рядам на следующий год. Выполнить другие пункты задания.
| Годы | |||||||||||
| Динамика нас. (тыс. чел) | 21,5 | 26,1 | 31,5 | 34,9 | 45,1 | 50,8 | 59,4 | 63,9 | 67,1 | ||
| Динамика продаж(тыс.шт) | 2,5 | 2,9 | 3,4 | 3,9 | 4,1 | 4,8 | 5,6 | 5,9 | 6,2 |
2. Размещая рекламу в 4-х изданиях, фирма собрала сведения о поступивших на нее откликов – у и сопоставила их с данными об изданиях: х1 – стоимость издания, х2 – стоимость одного блока рекламы, х3 – тираж, х4 – объём аудитории, х5 – периодичность, х6 – наличие телепрограммы. Какое количество откликов можно ожидать на рекламу в издании со следующими характеристиками: 15000 руб., 10$, 1000 экз., 25000 чел., 4 раза в месяц, без телепрограммы.
Пользуясь данными таблицы
| Издания | Х1 | Х2 | Х3 | Х4 | Х5 | Х6 | Отклики у |
| 11,8 | |||||||
необходимо сделать прогноз при заданных характеристиках.
Вариант 9. 1. Размещая свою рекламу в 2-х печатных изданиях одновременно, фирма собрала сведения о количестве поступивших звонков и количестве поступивших сделок по объявлениям в каждом из указанных изданий за последние 12 месяцев. Определить, в каком из изданий и насколько эффективность размещения рекламы в следующем месяце будет больше?
| Месяцы | Издание 1 | Издание 2 | ||
| Звонки | Сделки | Звонки | Сделки | |
Эффективность определяется как сделки/звонки. Сделать линейный и экспоненциальный прогнозы по обоим изданиям.
2. Пусть комплект мягкой мебели (диван+2 кресла) характеризуется стоимостью комплектующих: х1- деревянные подлокотники, х2 – велюровое покрытие, х3 – кресло-кровать, х4 – угловой диван, х5 – раскладывающийся диван, х6 – место для хранения белья. По данным о стоимости 5 комплектов сделать вывод о возможной стоимости комплекта с обычным раскладывающимся диваном, с местом для белья, без деревянных подлокотников и велюрового покрытия, с креслом кроватью.
Пользуясь данными таблицы
| Признаки | Х1 | Х2 | Х3 | Х4 | Х5 | Х6 | У1-стоимость |
| Комплект 1 | 13850р. | ||||||
| Комплект 2 | 15770р. | ||||||
| Комплект 3 | 16730р. | ||||||
| Комплект 4 | 24350р. | ||||||
| Комплект 5 | 42150р. | ||||||
| Комплект 6 |
сделать прогноз и выполнить другие пункты задания.
Вариант 10.
1. Для 2-х радиостанций известны данные об изменении объёма аудитории и динамике роста цен за 1 минуту эфирного времени за последние 12 месяцев. Определить, для какой радиостанции стоимость одного контакта со слушателем будет меньше?
| Месяц | Радиостанция 1 | Радиостанция 2 | ||
| Аудитория | Цена 1мин. | Аудитория | Цена 1 мин | |
| Контакт |
В строке «Контакт» в ячейках С8 и D8 должны быть записаны формулы = С7/В7 и =Е7/D7 соответственно, вычисляющие стоимость 1 мин. Эфира для одного слушателя в прогнозируемом месяце. Прогноз нужно выполнить для линейного и экспоненциального приближений и выбрать более достоверный, а также сделать другие пункты Задания .
2. На основании данных ежемесячных исследований известна динамика рейтинга банка (в условных единицах) за последние 6 месяцев в следующих сферах:
а) менеджмент и технология – х1
б) менеджеры и персонал – х2
в) культура банковского обслуживания – х3
г) имидж банка на рынке финансовых услуг – х4
д) реклама банка – х5
Определить возможное изменение количества вкладчиков данного банка в следующем месяце, если известны значения сфер рейтинга и количество вкладчиков в каждом из рассматриваемых 6 месяцев.
| Месяц | х1 | х2 | х3 | х4 | х5 | Кол-во вкладчиков |
Выполнить другие пункты Задания.
Контрольные вопросы
1. Сущность регрессионного анализа, его использование для прогнозирования функций.
2. Как получить уравнения одномерной линейной регрессии, каков синтаксис функции линейного приближения?
3. Как получить уравнение многомерной линейной регрессии, каков синтаксис функции?
4. Как получить уравнение одномерной экспоненциальной регрессии, каков синтаксис функции экспоненциального приближения?
5. Как получить уравнение многомерной экспоненциальной регрессии, каков синтаксис функции экспоненциального приближения?
6. Каковы правила ввода и использования табличных формул?
7. Как на гистограмме исходных данных добавить линию тренда?
8. Как с помощью линии тренда отобразить прогнозируемые величины?