andrey

Путь к Файлу: /Разное / Экономико-математическое прогнозирование при помощи MS Ecxel 1.doc

Ознакомиться или скачать весь учебный материал данного пользователя
Скачиваний:   0
Пользователь:   andrey
Добавлен:   24.01.2015
Размер:   384.0 КБ
СКАЧАТЬ

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ УКРАИНЫ

НАЦИОНАЛЬНЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ

«ХАРЬКОВСКИЙ ПОЛИТЕХНИЧЕСКИЙ ИНСТИТУТ»

 

 

 

 

 

КАФЕДРА ЭКОНОМИКИ И МАРКЕТИНГА

 

 

 

 

 

 

 

 

 

 

 

 

 

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

по выполнению лабораторной работы:

“Прогнозирование объемов продаж товара на рынке методом экстраполяции тренда при помощи MS Excel”

по дисциплине: “Информационные технологии маркетинга”

 

 

для специальности 7.050108 “Маркетинг”

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Харьков – 2004


 

 

Утверждено методическим советом кафедры “Экономики и маркетинга”, протокол №10 от 10 июня 2004г.

 

Методические указания по выполнению лабораторной работы: “Прогнозирование объемов продаж товара на рынке методом экстраполяции тренда при помощи MS Excel” по дисциплине: “Информационные технологии маркетинга” для студентов специальности 7.050108 “Маркетинг”/Сост. В.Н.Кобелев. – Харьков.: НТУ “ХПИ”, 2004. – 15с.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

© НТУ “ХПИ”, 2004


Общие положения

 

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

Трудоемкость расчетов обусловлена необходимостью переработки большого объёма информации, применения экономико-математических методов и вычислительной техники.

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

Цель работы – изучение экономико-математических методов прогнозирования. Лабораторная работа выполняется с использованием ЭВМ и электронных таблиц MS Excel из состава пакета прикладных программ MS Office.

 

Методические указания

 

Составной частью методики прогнозирования является экономико-математическая модель, т.е. математическое описание исследуемого экономического процесса или объекта. Только на базе такой модели, при наличии соответствующей информации и ЭВМ может быть получен обоснованный прогноз.

Наибольшее распространение в настоящее время получили модели статистического прогнозирования. Прогноз на перспективу устанавливается путем анализа фактических данных за прошедший период. На основе выявленной закономерности прогнозируется дальнейшее развитие исследуемого показателя.

При выполнении лабораторной работы студент получает у преподавателя данные об изменении исследуемых факторов в ретроспективном периоде. На ЭВМ осуществляется подбор экономико-математической модели, наиболее точно описывающей изменение исследуемых показателей. В заключении проводится прогнозирование по этим показателям на заданную перспективу.

 

Теоретические основы регрессионно-корелляционного анализа

 

Одними из наиболее эффективных методов, которые дают возможность анализировать значительные объёмы информации с целью исследования вероятной взаимосвязи между двумя и более переменными являются регрессионный и корреляционный анализ. В регрессионном анализе рассматривается связь между одной переменной, или признаком, и несколькими другими, которые называются независимыми переменными. Эта связь представляется при помощи математической модели, т.е. уравнения, которое связывает зависимую переменную (у) с независимыми (х) с учетом множества соответствующих допущений. Регрессионный анализ используется по двум причинам. Во-первых, потому что описание зависимости между переменными помогает установить существование возможной причинной связи. Во-вторых, получение аналитической зависимости между переменными дает возможность прогнозировать будущие значения зависимой переменной по значениям независимых переменных.

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

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

У=f(x)

Помимо функциональных существуют еще и корреляционные связи. В них изменение результативного признака у обусловлено влиянием х не полностью, а только частично:

У= Y(x) + e

Случайная часть e отображает либо свойственную результативному признаку изменчивость, либо влияние на неё факторов не учтенных этим соотношением, либо и то и другое вместе.

Основные этапы регрессионно-корелляционного анализа заключаются в следующем:

1) построение системы факторов, которые влияют на результативный признак;

2) разработка модели, которая отображает общий смысл изучаемых взаимосвязей и количественная оценка её параметров;

3) проверка качества модели;

4) оценка влияния отдельных факторов.

С целью упрощения первого этапа лабораторной работы в качестве независимого признака выбран фактор времени.

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

 

Экономико-математическое прогнозирование при помощи MS Ecxel 1

Рисунок 1 Диаграмма рассеяния

 

В случае парной корреляции эти функции записываются так:

линейная

Y = mx + b

логарифмическая

Y = m Ln (x) + b

степенная

Y = bxm

экспоненциальная

Y = bemx

полиномиальная

Y = m6x6 + m5x5 + m4x4 + m3x3 + m2x2 + m1x1 + b

 

Для оценки качества модели обычно используют коэффициент детерминированности R2. Его еще называют величиной достоверности аппроксимации или уровнем надежности. Он дает количественную оценку меры анализируемой связи. Чем ближе R2 к 1, тем в большей степени уравнение регрессии объясняет фактор, который изучается (при функциональной связи R2 равняется 1, а при отсутствии связи – 0). Если например, R2 равен 0,9, то можно считать, что 90% изменений (вариаций) функции обусловлены изменениями в учитываемых факторах и только 10% - за счет влияния других факторов.

Для облегчения выводов относительно практической значимости синтезированной модели показателю плотности связи r (коэффициент корреляции) в табл. 1 дается качественная оценка на основании шкалы Чеддока:

 

Таблица 1

Показатель плотности связи r

0.1-0,3

0,3-0,5

0.5-0,7

0.7-0,9

0,9 - в,99

Характеристика силы связи

слабая

воздержанная

заметная

значительная

весьма значительная


Пример проведения прогнозирования средствами MS Excel

 

Прогнозирование в лабораторной работе ведется при помощи регрессионно-корреляционного анализа методом экстраполяции тренда средствами электронных таблиц MS Excel.

Рассмотрим технологию прогнозирования на конкретном примере. Допустим, что нужно спрогнозировать уровень потребности в асинхронных электродвигателях мощностью до 100 кВт на 16 год (исходные данные представлены в табл. 2).

 

Таблица 2 – Потребность в электродвигателях по годам

Годы

Потребность в электродвигателях Пэt, тыс. шт.

1

510

2

560

3

540

4

600

5

634

6

623

7

650

8

690

9

680

10

730

11

746

12

739

 

Внесем исходные данные в рабочую книгу MS Excel (Рис. 2).

 

Экономико-математическое прогнозирование при помощи MS Ecxel 1

 

Рисунок 2 – Исходные данные


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

Процесс построения графика в MS Excel выглядит следующим образом.

Вначале выделим область исходных данных. Затем запускаем мастер диаграмм (или команда вставка – диаграмма) и следуя инструкциям мастера выполняем четыре шага (Рис.3 - 11).

На первом шаге необходимо выбрать тип диаграммы точечная (Рис 3).

 

Экономико-математическое прогнозирование при помощи MS Ecxel 1

Рисунок 3 – Шаг первый

 

На втором шаге необходимо выбрать источник данных диаграммы. Здесь необходимо ввести диапазон данных, а также отметить в строках или в столбцах находятся данные (Рис 4 -5).

Экономико-математическое прогнозирование при помощи MS Ecxel 1

Рисунок 4 – Шаг второй – Диапазон данных

 

На вкладке Ряд, при необходимости, необходимо откорректировать диапазон значений, а также имя ряда (Рис. 5).

 

Экономико-математическое прогнозирование при помощи MS Ecxel 1

Рисунок 5 – Шаг второй – Ряд


На третьем шаге мастера (Рис. 6 - 10) необходимо ввести следующие данные. На вкладке Заголовки необходимо указать название диаграммы, а также подписи по осям (Рис. 6).

 

Экономико-математическое прогнозирование при помощи MS Ecxel 1

Рисунок 6 – Шаг третий – Заголовки

 

На вкладке Оси указать, что нас интересуют ось Х и ось Y, отметив соответствующие чекбоксы (Рис. 7).

 

Экономико-математическое прогнозирование при помощи MS Ecxel 1

Рисунок 7 – Шаг третий – Оси

 

На вкладке Линии сетки указать, что нам нужна сетка по оси Х и оси Y, отметив соответствующие чекбоксы (Рис. 8).

 

Экономико-математическое прогнозирование при помощи MS Ecxel 1

Рисунок 8 – Шаг третий – Линии сетки

 

На вкладке Легенда указать, что нам не нужна легенда, отметив соответствующий чекбокс (при наличии только одной переменной легенда не нужна) (Рис. 9).

 

Экономико-математическое прогнозирование при помощи MS Ecxel 1

Рисунок 9 – Шаг третий – Легенда

 

На вкладке Подписи данных можно не включать подписи (Рис. 10).

 

Экономико-математическое прогнозирование при помощи MS Ecxel 1

Рисунок 10 – Шаг третий – Подписи данных

 

На четвертом шаге мастера необходимо указать на каком листе рабочей книги Excel поместить диаграмму – на отдельном или имеющемся (Рис. 11).

 

Экономико-математическое прогнозирование при помощи MS Ecxel 1

Рисунок 11 – Шаг четвертый

 

Нажав кнопку Готово получим график изображенный на рисунке 12

 

Экономико-математическое прогнозирование при помощи MS Ecxel 1

Рисунок 12


Сделаем график более удобным для дальнейшей работы. Нажав правую кнопку мыши на сером поле необходимо выбрать в контекстно-зависимом меню команду Формат области построения. Здесь нужно отметить радиокнопки Рамка - невидимая и Заливка – прозрачная, затем подтвердить выбор нажатием кнопки ОК (Рис. 13).

Экономико-математическое прогнозирование при помощи MS Ecxel 1

Рисунок 13

 

Диаграмма примет вид изображенный на рисунке 14

 

Экономико-математическое прогнозирование при помощи MS Ecxel 1

Рисунок 14

 

Применив соответствующее форматирование к осям (вкладки Вид, Шкала и др.) получим график пригодный для дальнейшей работы (рисунок 15)

 

Экономико-математическое прогнозирование при помощи MS Ecxel 1

Рисунок 15

 

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

 

Экономико-математическое прогнозирование при помощи MS Ecxel 1

Рисунок 16


Окно этой команды содержит две вкладки Тип и Параметры.

На вкладке Тип необходимо выбрать тип функции, которая в наибольшей степени соответствует поведению данных на графике. В нашем примере это Линейная функция.

На вкладке Параметры необходимо отметить два нижних чекбокса, чтобы уравнение и R2 появилось на диаграмме (Рис. 17).

 

Экономико-математическое прогнозирование при помощи MS Ecxel 1

Рисунок 17

 

После этого получим график показанный на рисунке 18.

 

Экономико-математическое прогнозирование при помощи MS Ecxel 1

Рисунок 18


Правильность выбора уравнения, кроме графического анализа, может быть проверена путем расчета коэффициента корреляции, который показывает, насколько зависимость между переменными близка к линейной функциональной. Так в примере получено значение R2 = 0,955, что свидетельствует о достаточно высокой практической значимости синтезированной модели.

Далее при помощи полученной модели дадим прогноз. Для этого создадим вторую таблицу, в которую введем уравнение полученной модели. Модель линейная y = 21,392x +502,79 или в более понятном выражении Пэt = 21,392t + 502,79.

После расчета формул получим результаты представленные в таблице 3 и на рисунке 19.

 

Таблица 3 – Результаты расчета

Годы

Потребность в электродвигателях Пэt, тыс. шт.

1

524,18

2

545,57

3

566,97

4

588,36

5

609,75

6

631,14

7

652,53

8

673,93

9

695,32

10

716,71

11

738,10

12

759,49

13

780,89

14

802,28

15

823,67

16

845,06

 

Экономико-математическое прогнозирование при помощи MS Ecxel 1

Рисунок 19

 

Таким образом, мы спрогнозировали потребность в асинхронных электродвигателях мощностью до 100 кВт на 16 год.

Сравнивая данные таблицы 2 и таблицы 3 видно, что прогноз дан с достаточно высокой степенью точности.

Наверх страницы

Внимание! Не забудьте ознакомиться с остальными документами данного пользователя!

Соседние файлы в текущем каталоге:

На сайте уже 21970 файлов общим размером 9.9 ГБ.

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

Не нашли нужный документ? Воспользуйтесь поиском по содержимому всех файлов сайта:



Каждый день, проснувшись по утру, заходи на obmendoc.ru

Товарищ, не ленись - делись файлами и новому учись!

Яндекс.Метрика