Задание 2 Регрессионный анализ данных
Министерство образования и науки
Российской Федерации
Федеральное государственное бюджетное образовательное учреждение
высшего профессионального образования
"Кубанский государственный
технологический университет"
Кафедра информатики и вычислительной техники
О.С. Безнос
Методические указания
По выполнению контрольной работы
Для студентов направления 39.03.01 - Социология
Заочной формы обучения СГФ
Краснодар 20 17
Оглавление
Контрольная работа №1. 2
Общие положения. 2
1 Задания контрольной работы.. 3
Задание 1. Построение графиков и диаграмм.. 3
Варианты задания 1. 3
Задание 2 Регрессионный анализ данных. 9
Варианты задания 2. 9
Задание 3. Решение задач оптимизации методом.. 12
линейного программирования. 12
Варианты задания 3. 12
Задание 4. Технология работы с финансовыми функциями MS Excel. 18
Анализ «Что-если». 18
2 Методические указания. 24
2.1 Методические указания к выполнению задания 1. 24
2.2 Методические указания к выполнению задания 2. 26
2.3 Методические указания к выполнению задания 3. 32
2.4 Методические указания к выполнению задания 4. 42
Список литературы.. 52
Образец титульного листа: 46
Контрольная работа №1
Общие положения
Контрольная работа (КР) предназначена для освоения студентами информационных технологий работы в табличном процессоре MS Excel, которые используются в управлении организациями, производственном, стратегическом и финансовом менеджменте.
|
|
|
Номер варианта для заданий 1,3 и 4 КР (20 вариантов) определяется по последним двум цифрам номера зачётной книжки согласно следующему правилу:
· если предпоследняя цифра чётная, то она принимается равной нулю (0), если нечётная, то единице (1);
· последняя цифра используется без изменения.
Примеры: № зачётной книжки 11-ЗГБ-СЦ-207 - вариант 7, № зачётной книжки 11-ЗГБ-СЦ-20 - вариант 0, № 11-ЗГБ-СЦ-251 - вариант 11.
Для задания 2 номер варианта определяется по последней цифре номера зачётной книжки.
КР сдаётся на кафедру "Информатика" по адресу: г. Краснодар, ул. Красная, 135, ауд. К-205(123) за 1 месяц до начала сессии. Если работа не принята преподавателем, она возвращается студенту на доработку и снова сдаётся на кафедру. Защита контрольных работ производится на первом лабораторном занятии сессии.
Контрольная работа сдаётся в двух видах:
1. Компакт – диск с выполненными заданиями в электронном виде (следует использовать перезаписываемый CD-RW, т.к. возможен возврат работы для исправления ошибок).
2. Распечатка заданий КР в бумажном виде. Структура бумажной версии:
|
|
|
· Титульный лист согласно Приложению 1
· Текст задания 1 из данного документа и его выполнение.
· Аналогично - тексты заданий 2-4 из данного документа и их выполнение (с использованием скриншотов выполнения заданий в MS Excel)
· Список использованной литературы (см. с. 52)
1 Задания контрольной работы
Контрольная работа состоит из 4 заданий:
Задание 1. Построение графиков и диаграмм
1 Ввести в табличном процессоре Excel таблицу согласно варианту. Заполнить таблицу примерными данными (придумать самостоятельно), в вычисляемые ячейки (среднее, процент, остаток, сумма и т.п.) ввести соответствующую формулу)
2 Построить по заполненной таблице 4 двумерные и 4 трехмерные диаграммы любых типов (по выбору студента), содержащих не менее 3 рядов данных (функций) для 5-6 значений категорий (аргумента функций).
3 Построить в Excel график функции:
y= n ·sin(2x), -5 <= х <=5, шаг Δх = 1,
где n – номер варианта студента.
4 Построить график изменения цены товара во времени согласно табл.1.
Таблица 1 Изменение цены товара
| Интервал времени | Январь | Февраль | Март | Апрель |
| Стоимость, руб. | 880 | 825 | 850 | 875 |
| Стоимость, доллар. | 350 | 320 | 335 | 342 |
В графике использовать две оси ординат, на одной цена товара должна быть приведена в рублях, на другой – в долларах (с помощью диаграммы График(2 оси).
|
|
|
5 Для графика п. 4 изменить цвет осей, шрифт названий графика и осей, ввести сетку.
6 Дополнить таблицу ещё тремя значениями функции для трёх последующих месяцев и изменить согласно новым данным график функции (с помощью команд меню Диаграмма- Добавить данные...).
Варианты задания 1
Вариант 1 Сравнительная таблица розничных цен на продовольственные товары по городам Северного Кавказа (руб. за 1 кг)
| Товар | Краснодар | Ростов | Ставрополь | Майкоп | Нальчик | Средняя цена |
| Говядина | ||||||
| Свинина | ||||||
| Птица | ||||||
| Рыба |
В ячейки столбца «Средняя цена» ввести любым способом формулы для вычисления среднего значения содержимого ячеек соответствующей строки .
Вариант 2 Данные о продаже автомобилей в 200__г.
| Марка | 1 квартал | % | 2 квартал | % | 3 квартал | % | 4 квартал | % |
| БМВ | ||||||||
| Форд | ||||||||
| Ауди | ||||||||
| Рено | ||||||||
| Всего |
В ячейках столбцов «%» должны быть записаны формулы, вычисляющие для данной марки автомобиля процент от общего числа
|
|
|
проданных в данном квартале машин из ячеек строки «Всего» .
Вариант 3 Температура воздуха в городах мира с 1 по7 января 200__г.
| Дата | Москва | Рим | Париж | Лондон | Мадрид |
| 01 янв 2000 | |||||
| 02.01.2000 | |||||
| 03.1.2000 | |||||
| 04.01.2000 | |||||
| 5 янв | |||||
| 6.01 | |||||
| 07.01.2000 13:30 | |||||
| Средн. темп -ра | |||||
| Общая средняя. по всем городам |
| ||||
В ячейке строки «Средняя температура» ввести формулу для вычисления среднего значения температуры в столбце, в строку «Общая средняя по всем городам» – формулу для вычисления среднего от содержимого ячеек строки «Средняя температура».
Вариант 4 Перечень доходов и расходов за 1-е полугодие.
| Статья \ Месяц | Янв. | Февр. | Март | Апр. | Май | Июнь |
| Доходы | ||||||
| Оклад | ||||||
| Премия | ||||||
| Совместительство | ||||||
| Всего доходов | ||||||
| Расходы | ||||||
| Жилье | ||||||
| Кредит | ||||||
| Питание | ||||||
| Налоги | ||||||
| Другие расходы | ||||||
| Всего расходов | ||||||
| Остаток | ||||||
| Остаток за полугодие | ||||||
В ячейки строки «Всего доходов» и «Всего расходов» ввести формулы, вычисляющие сумму значений ячеек соответствующего столбца, в ячейках строки «Остаток» должны быть формулы, вычисляющие разность содержимого ячеек строк «Всего доходов» и «Всего расходов», в строке
«Остаток за полугодие» – сумма содержимого ячеек строки «Остаток».
Вариант 5.Выручка от продажи книжной продукции в 200_ г.
| Название книги | 1 кв. | 2 кв. | 3 кв. | 4 кв. | Процент за год |
| 1. | |||||
| 2. | |||||
| 3. | |||||
| 4. | |||||
| 5. | |||||
| 6. | |||||
| Всего за год |
В ячейках столбца «Процент за год» и строки «Всего за год» должны быть записаны соответствующие формулы.
Вариант 6 Итоговые экспертные оценки (от 0 до 10 баллов) кандидатов на должность генерального директора фирмы «Аква».
| Номер кандидата | Эксперты | Сумма баллов | Процент | |||
| 1 | 2 | 3 | 4 | |||
| №1 | ||||||
| №2 | ||||||
| №3 | ||||||
| №4 | ||||||
| №5 | ||||||
| Всего: |
| |||||
В столбце «Процент» вычисляется процентное отношение ячеек столбца «Сумма баллов» от значения ячейки строки «Всего».
Вариант 7 Прайс-лист фирмы «Трианон» на 22 декабря 200_ г.
| Наименование товара | Рознич. цена, руб. | От 10 шт. | От 100 шт. | Свыше 1000 шт. | Диллеры |
| 1. | |||||
| 2. | |||||
| 3. | |||||
| 4. | |||||
| 5. | |||||
| 6. |
Формулы в ячейках столбцов «От 10 шт. ... Диллеры» должны учитывать, что цена уменьшается на 1 процент по сравнению с предыдущей
колонкой.
Вариант 8 Результаты зимней сессии студентов группы 200_-Эк- 52
| Фамилия И.О. | Математика | Информатика | Иностр. язык | Маркетинг | Сумма | Средн. балл |
| 1. | ||||||
| 2. | ||||||
| 3. | ||||||
| 4. | ||||||
| 5. | ||||||
| 6. |
В столбцах «Сумма» и «Средний балл» должны быть введены соответствующие формулы.
Вариант 9 Анализ цен на товары в конкурирующих фирмах.
| Наименование товара | Название фирмы | ||
| ВЛАДОС | ОК | Контраст | |
| 1. | |||
| 2. | |||
| 3. | |||
| 4. | |||
| 5. | |||
| Сумма, руб. | |||
Вариант 10 Изменение курса валют за период 1 по 5 марта 201_г.
| Дата | Доллар | Рубль | Доллар/Рубль |
| 1 мар 2010 | |||
| 2 мар 2010 | |||
| 3 мар 2010 | |||
| 4 мар 2010 | |||
| 5 мар 2010 | |||
| Средний за период | |||
| Процент роста |
В столбце «Доллар/ Рубль» должна быть формула для вычисления отношения курсов доллара и марки в соответствующей строке. В строку «Средний за период» вводятся формулы для вычисления среднего
значения в столбце, в строку «Процент роста» - формулы для определения процентного прироста курса на5 марта относительно курса на 1 марта 200_г.
Вариант 11 Сравнительная таблица розничных цен
| Товар | Краснодар | Ростов | Ставрополь | Майкоп | Нальчик | Средняя цена |
| Цемент | ||||||
| Алебастр | ||||||
| Толь | ||||||
| Битум |
В ячейки столбца «Средняя цена» ввести любым способом формулы для вычисления среднего значения содержимого ячеек соответствующей строки .
Вариант 12 Данные о продаже автомобилей в 201__г.
| Марка | 1 кварт | % | 2 кварт | % | 3 кварт | % | 4 кварт | % |
| Волга | ||||||||
| Москвич | ||||||||
| Жигули | ||||||||
| Газель | ||||||||
| Бычок | ||||||||
| Всего |
В ячейках столбцов «%» должны быть записаны формулы, вычисляющие для данной марки автомобиля процент от общего числа проданных в данном квартале машин из ячеек строки «Всего» .
Вариант 13 Температура воздуха в городах мира с 1 по 5 января 201__г.
| Дата | Вена | Рим | Париж | Лондон | Осло |
| 01.01. 200… | |||||
| 02.01.200… | |||||
| 03.1.200… | |||||
| 04.01.200… | |||||
| 05.01.200… | |||||
| Средн. темп -ра | |||||
| Общая средняя. по всем городам |
| ||||
В ячейки строки «Средняя температура» ввести формулу для вычисления среднего значения температуры в столбце, в строку «Общая средняя по всем городам» – формулу для вычисления среднего от содержимого ячеек строки «Средняя температура».
Вариант 14 Перечень доходов и расходов за 1-е полугодие.
| Статья \ Месяц | Янв. | Февр. | Март | Апр. | Май | Июнь |
| Доходы | ||||||
| Оклад | ||||||
| Премия | ||||||
| Прибыль | ||||||
| Всего доходов | ||||||
| Расходы | ||||||
| Жилье | ||||||
| Кредит | ||||||
| Питание | ||||||
| Налоги | ||||||
| Всего расходов | ||||||
| Остаток | ||||||
| Остаток за полугодие | ||||||
В ячейки строки «Всего доходов» и «Всего расходов» ввести формулы, вычисляющие сумму значений ячеек соответствующего столбца, в ячейках строки «Остаток» должны быть формулы, вычисляющие разность содержимого ячеек строк «Всего доходов» и «Всего расходов», в строке «Остаток за полугодие» – сумма содержимого ячеек строки «Остаток».
Вариант 15.Выручка от продажи книжной продукции в 200_ г.
| Название книги | 1 кв. | 2 кв. | 3 кв. | 4 кв. | % за год |
| 1. | |||||
| 2. | |||||
| 3. | |||||
| 4. | |||||
| 5. | |||||
| 6. | |||||
| Всего за год |
В ячейках столбца «Процент за год» и строки «Всего за год» должны быть записаны соответствующие формулы.
Вариант 16 Итоговые экспертные оценки (от 0 до 10 баллов) кандидатов на должность генерального директора фирмы «Аква».
| Номер кандидата | Эксперты | Сумма баллов | Процент | |||
| 1 | 2 | 3 | 4 | |||
| №1 | ||||||
| №2 | ||||||
| №3 | ||||||
| №4 | ||||||
| №5 | ||||||
| Всего: |
| |||||
В столбце «Процент» вычисляется процентное отношение ячеек столбца «Сумма баллов» от значения ячейки строки «Всего».
Вариант 17 Прайс-лист фирмы «Трианон» на 22 декабря 201_ г.
| Наименование товара | Рознич. цена, руб. | От 10 шт. | От 100 шт. | Свыше 1000 шт. | Диллеры |
| 1. Шторы | |||||
| 2. Занавес | |||||
| 3. Карнизы | |||||
| 4.Ролл-ставни | |||||
| 5. | |||||
| 6. |
Формулы в ячейках столбцов «От 10 шт. ... Диллеры» должны учитывать, что цена уменьшается на 1 процент по сравнению с предыдущей колонкой.
Вариант 18 Результаты зимней сессии студентов группы 0_-Эк- 52
| Фамилия И.О. | Математика | Информатика | Иност.. язык | Маркетинг | Сумма | Средн. балл |
| 1. | ||||||
| 2. | ||||||
| 3. | ||||||
| 4. | ||||||
| 5. | ||||||
| 6. |
В столбцах «Сумма» и «Средний балл» должны быть введены соответствующие формулы.
Вариант 19 Анализ цен на товары в конкурирующих фирмах.
| Наименование товара | Название фирмы | ||
| Санрайз | ОК | Иманго | |
| 1. | |||
| 2. | |||
| 3. | |||
| 4. | |||
| 5. | |||
| Сумма, руб. | |||
Вариант 20 Изменение курса валют за период 1 по 5 марта 200_г.
| Дата | Доллар | Евро | Доллар/Евро |
| 1 мар 2000 | |||
| 2 мар 2000 | |||
| 3 мар 2000 | |||
| 4 мар 2000 | |||
| 5 мар 2000 | |||
| Средний за период | |||
| Процент роста |
В столбце «Доллар/Евро» должна быть формула для вычисления отношения курсов доллара и евро в соответствующей строке. В строку «Средний за период» вводятся формулы для вычисления среднего значения в столбце, в строку «Процент роста» - формулы для определения процентного прироста курса на 5 марта относительно курса на 1 марта 201_г.
Задание 2 Регрессионный анализ данных
Согласно варианту задания необходимо в табличном процессоре MS Excel составить, заполнить и определить:
1. Таблицу исходных данных, а также значений, полученных методами линейной и экспоненциальной регрессии.
2. Коэффициенты в уравнениях прямой и экспоненциальной кривой (функции ЛИНЕЙН и ЛГРФПРИБЛ).
Запишите уравнения прямой и экспоненциальной кривой для одномерной регрессии..
3. Погрешности (ошибки) прямой и экспоненциальной кривой. вычислений для коэффициентов и функций, коэффициенты детерминированности. Оценить, какой тип регрессии наилучшим образом подходит для вашего варианта задания.
4. Прогноз изменения данных, выполненный с использованием линейной и экспоненциальной регрессии (функции ТЕНДЕНЦИЯ, РОСТ).
5. Построить гистограмму (или график) исходных данных для задачи 1 (одномерная регрессия), отобразить на ней линию тренда, а также соответствующее ей уравнение и коэффициент детерминированности.
Варианты задания 2
(номер варианта соответствует последней цифре номера зачетной книжки)
Вариант 1
На рынке наблюдается стойкое снижение цен на компьютеры. Сделать прогноз, сколько необходимо будет снизить цену на компьютеры в следующем месяце в Вашей фирме, чтобы как минимум сравнять ее с ценой на аналогичные компьютеры в конкурирующей фирме, если известна динамика изменения цен на них в конкурирующей фирме за последние 12 месяцев.
Для выполнения задания нужно ввести ряд из 12 ячеек с ценами конкурирующей фирмы , сделать прогноз цены на следующий месяц и др.(см. Задание).
Вариант 2
Имеются данные о динамике продаж в расчете на душу населения по хлебобулочным продуктам и молочным изделиям, а также динамика изменения среднедушевого годового дохода за последние 10 лет. Для каждой группы товаров построить регрессионные модели, описывающие зависимость объемов продаж от размера доходов. Сделать прогноз об объемах продаж и размерах доходов на следующий год.
Для выполнение задания нужно составить таблицу вида:
| Годы | 1 | 2 | . . . | 10 | 11 |
| х1 – хлеб, кг | 0,5 | 26,7 | . . . | 42,8 | |
| х2 –молоко, л | 0,45 | 22 | . . . | 39,5 | |
| у – доход, р. | 6600 | 7200 | . . . | 18250 |
и получить два уравнения – у =f (x1) и у= f(х2) , сделать прогноз на следующий год для рядов х1, х2, у и др. .(см. Задание).
Вариант 3
Автомобильный салон имеет данные о количестве проданных автомобилей "Мерседес" и "БМВ" за последние 4 квартала. Учитывая тенденцию изменения объема продаж, определить, каких автомобилей необходимо закупить больше ( "Мерседес" или "БМВ" ) в следующем квартале?
Для выполнения задания нужно составить и заполнить таблицу вида
| 1 квартал | 2 квартал | 3 квартал | 4 квартал | Новый квартал | |
| Мерседес | 10 | 12 | 15 | 18 | |
| БМВ | 9 | 10 | 14 | 17 |
сделать прогноз продаж на новый квартал и выполнить другие пункты Задания.
Вариант 4
Определить минимально необходимый тираж ежемесячного журнала " Speed-Info " и возможный доход от размещения в нем рекламы в следующем месяце, если известны данные об объемах продаж этого журнала и доходах от размещения рекламы за прошедшие 12 месяцев (считать, что расценки на рекламу не менялись ).
Для выполнения задания нужно составить таблицу вида
| Месяц | 1 | 2 | . . . | 12 | 13 |
| Тираж | 100000 | 120000 | . . . | 145000 | |
| Доход | 128000 | 135000 | . . . | 172000 |
и заполнить ячейки за 12 месяцев условными данными. По этим данным нужно сделать линейный и экспоненциальный прогноз и др. (см. Задание).
Вариант 5
На основании данных о курсе американского доллара и немецкой марки в первом полугодии сделать прогноз о соотношении данных валют на второе полугодие. Во что будет выгоднее вкладывать деньги в конце года?
Для выполнения задания нужно составить таблицу вида
| Месяц | 1 | 2 | 3 | 4 | 5 | 6 | 7 | . . . | 12 |
| Доллар | 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 месяцев и др.(см. Задание).
Вариант 6
Для некоторого региона известен среднегодовой доход населения, а также данные о расходах ( тыс. руб. в год ) за последние 5 лет На основании известных данных провести анализ потребительского Дохода и расхода в следующем году.
Для выполнения задания нужно составить таблицу вида
| Годы | Доход | Расход |
| 1 | 21,4 | 3,1 |
| . . . | ... | ... |
| 5 | 26,2 | 1,5 |
| 6 |
Для столбцов Доход и расход нужно выполнить регрессионный прогноз на следующий год и другие пункты Задания.
Вариант 7
Определить возможный прирост населения (кол-во человек на 1000 населения ) в 2010 году, если известны данные о количестве родившихся и умерших на 1000 населения в 2001-2009 годах.
| Год | 2001 | 2002 | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 |
| Родилось | 120 | 110 | 108 | 112 | 98 | 102 | 126 | 100 | 118 | |
| Умерло | 75 | 50 | 65 | 88 | 69 | 98 | 102 | 14 | 107 |
Вариант 8
Построить прогноз развития спроса населения региона на телевизоры, если известна динамика продаж телевизоров (тыс. шт. ) и динамика численности населения данного региона ( тыс. чел. ) за последние 10 лет.
Для выполнения задания нужно составить таблицу из двух рядов (продаж телевизоров и численности населения по годам и сделать прогноз по обоим рядам на следующий год. Выполнить другие пункты Задания.
Вариант 9. 1 Размещая свою рекламу в двух печатных изданиях одновременно, фирма собрала сведения о количестве поступивших звонков и количестве совершенных сделок по объявлениям в каждом из указанных изданий за последние 12 месяцев. Определить, в каком из изданий и насколько эффективность размещения рекламы в следующем месяце будет больше?
Для выполнения задания нужно составить таблицу вида:
| Издание 1 | Издание 2 | |||
| Месяцы | Звонки | Сделки | Звонки | Сделки |
| 1 | 98 | 66 | 112 | 79 |
| 2 | 105 | 72 | 143 | 85 |
| . . . | . . . | . . . | . . . | . . . |
| 12 | 139 | 88 | 155 | 97 |
| 13 лин. | ||||
| 13-эксп. | ||||
Эффективность определяется как сделки /звонки. Сделать линейный и экспоненциальный прогнозы по обоим изданиям, выполнить другие пункты Задания.
Вариант 10
1Для двух радиостанций известны данные об изменении объема аудитории и динамике роста цен за 1 минуту эфирного времени за последние 12 месяцев. Определить, для какой радиостанции стоимость одного контакта со слушателем в следующем месяце будет меньше?
Для выполнения задания нужно составить и заполнить таблицу вида
| A | B | C | D | E | |
| 1 | Радиостанция 1 | Радиостанция 2 | |||
| 2 | Месяц | Аудитория | Цена 1 мин. | Аудитория | Цена 1 мин. |
| 3 | 1 | 250000 | 8000 | 300000 | 7560 |
| 4 | 2 | 540000 | 6500 | 450000 | 6340 |
| ... | ... | ... | ... | ... | ... |
| 14 | 12 | 1108000 | 4700 | 1025000 | 4540 |
| 15 | Прогноз | ||||
| 16 | Контакт | ||||
В строке «Контакт» в ячейках С8 и D8 должны быть записаны формулы =С7/B7 и =E7/D7 соответственно, вычисляющие стоимость 1 мин. эфира для одного слушателя в прогнозируемом месяце. Прогноз нужно выполнить для линейного и экспоненциального приближений и выбрать более достоверный, а также сделать другие пункты Задания.
Дата добавления: 2019-09-13; просмотров: 599; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!