Исследовать совместное влияние затрат на рекламу и цены товара на объем продаж, оценка параметров двухфакторной модели.
Цель работы - на основе заданных статистических данных линейной регрессией оценить параметры двухфакторной модели зависимости объемов продаж от затрат на рекламу и цены товара.
Методика выполнения задания
Параметры линейной многофакторной модели оцениваются в Excel с помощью статистической функции ЛИНЕЙН. Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов, вычисляя прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные.
Общий синтаксис вызова функции ЛИНЕЙН имеет следующий вид:
ЛИНЕЙН(известные_значения_у; известные_значения_х; конст; статистика),
где известные_значения_у – это множество значений у, которыеуже известны для соотношения у=а1х+ а0. В нашем примере Y задается диапазоном продаж С2:С19;
известные_значения_х– это множество известных значений х. В нашем примере статистические данные факторов рекламы и цены задаются матрицей А2:В19;
Конст- это логическое значение, которое указывает, требуется ли, чтобы константа а0 была равна нулю. Если конст имеет значение ИСТИНА или опущено, то а0 вычисляется обычным образом. Если конст имеет значение ЛОЖЬ, то а0 полагается равным нулю и значения а1 подбираются так, чтобы выполнялось соотношение у=а1х;
Статистика- это логическое значение, которое указывает, требуется ли отобразить в массиве результатов дополнительную статистику по регрессии. Если статистика имеет значение ИСТИНА, то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику.
Если статистика имеет значение ЛОЖЬ или опущена, то функция ЛИНЕЙН возвращает только коэффициент a1 и константу а0.
Дополнительная регрессионная статистика включает: стандартные значения ошибок для параметров, коэффициент детерминированности, стандартную ошибку для оценки Y, F-статистики и др.
Неприятная особенность функции, что она принимает последовательность независимых переменных в порядке увеличения их номера (столбца), а параметры выводит в обратном порядке.
Порядок выполнения задания
Загрузить Excel, открыть третий лист вашей книги и ввести в него исходные данные в соответствии с рис. 4.5.
Рис.4.5. Таблица с исходными данными и применением функции
ЛИНЕЙН для оценки параметров модели зависимости
объемов продаж от затрат на рекламу и цены товара
Слева в таблице расположены исходные статистические данные. Вверху колонок - названия показателей, внизу - обозначения факторов, XI -затраты на рекламу, Х2 - цена товара и функции Y - объем продаж.
Справа в колонках E:G показан синтаксис функции ЛИНЕЙН. Ниже даны обозначения параметров модели а2, al, a0 и их вычисленные значения. Затем представлено итоговое уравнение модели.
Функция ЛИНЕЙН рассчитывает статистику для ряда данных с применением метода наименьших квадратов. Функция вычисляет массив параметров и задается в виде формулы массива. Вы должны выделить диапазон, в котором желаете отобразить результаты, ввести функцию и нажать Ctrl + Shift + Enter. Эту формулу вы видите на рисунке 1.4 в строке формул. ЛИНЕЙН может также отображать дополнительную регрессионную статистику. Выделенный диапазон для отображения результатов должен содержать количество клеток в первой строке не менее количества определяемых параметров. Если вы желаете получить дополнительные статистические оценки регрессии, то диапазон массива должен содержать не менее пяти строк.
Анализ результатов
В результате обработки статистических данных функцией ЛИНЕЙН методами и алгоритмом наименьших квадратов получены числовые значения параметров и оценка их достоверности (рис. 4.6). Получено уравнение зависимости объема продаж от цены товара и затрат на рекламу.
Рис. 4.6. Таблица с исходными данными и применением функции
ЛИНЕЙН для оценки параметров модели зависимости
объемов продаж от затрат на рекламу и цены товара
С увеличением цены объемы продаж уменьшаются, сокращается платежеспособный спрос, а с увеличением затрат на рекламу продажи увеличиваются.
Модель готова для применения в прогнозировании, планировании и исследовании рыночного равновесия.
Задания для самостоятельного решения
Задание 1.
Построить и исследовать динамику роста производства продукции, используя данные:
| Годы | Уровень производства |
| 16,9 | |
| 17,1 | |
| 18,9 | |
| 19,7 | |
| 19,8 | |
| 19,9 |
Определить уровень производства в 2017 году. Обосновать выбор уравнения регрессии для расчета прогноза
Задание 2.
Построить функции, наилучшим образом аппроксимирующие зависимости:
| х | 1,0 | 1,5 | 3,0 | 4,5 | 5,5 |
| у | 1,25 | 1,4 | 1,5 | 1,75 | 2,25 |
Обосновать выбор уравнения регрессии для расчета прогноза
Задание 3
В таблице приведены данные численности студентов ВУЗа за период с 2011 по 2016 годы.
| Годы | ||||||
| Фактическая численность, чел. |
Рассчитать численность студентов на следующие три года.
Обосновать выбор уравнения регрессии для расчета прогноза.
Задание 4
Вложенные в производство средства дают прибыль:
| Средства | ||||||
| Прибыль |
Определить зависимость прибыли от вложенных средств и вычислить прибыль для вложений, равных 10000 руб.
Задание 5
В таблице приведены данные численности персонала организации за период с 2011 по 2016 годы:
| Годы | |||||||||
| Фактическая численность, чел. |
Рассчитать численность персонала на следующие три года.
Обосновать выбор уравнения регрессии для расчета прогноза
Задание 6
В таблице приведены статистические данные опроса населения по их затратам на приобретение продуктов питания
| Доход на члена семьи | ||||||||||
| Затраты на питание |
Рассчитать, какие будут затраты на питание, если доход на члена семьи будет равняться 5000 рублей.
Обосновать выбор уравнения регрессии для расчета прогноза.
Задание 7
В таблице приведены данные по продаже электробытовых товаров (холодильники, пылесосы, обогреватели и др.) 2009 – 2016 гг., полученные на основе опроса населения
| Годы | ||||||||
| Товарооборот | 1,8 | 2,27 | 4,3 | 6,4 | 5,7 | 7,2 | 8,2 | 8,4 |
Найти уравнение регрессии наилучшим образом описывающее данные опроса, на 2017 и 2018 годы полагая, что процессы определяющие спрос населения на электробытовые товары остается неизменным в течение всего последующего времени.
Контрольные вопросы для допуска и защиты работы
1. Что такое «линия тренда»?
2. Как построить линию тренда для заданного ряда наблюдений?
3. Какие типы линии тренда позволяет использовать Excel?
4. Как показать на диаграмме (графике) уравнение линии тренда?
5. Что характеризует статистика R2? В каких пределах она может изменяться?
6. Вы получили два уравнения регрессии. Одно из них имеет R2 = 0,45, а другое 0,95. Какое из уравнений Вы используете для прогноза?
Лабораторная работа №5. «Решение транспортной задачи. Нахождение Оптимального плана перевозки грузов с целью Получения минимальных издержек»