ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ В МЕНЕДЖМЕНТЕ
Лабораторная работа 3. ПРОГНОЗ С ИСПОЛЬЗОВАНИЕМ ЛИНИИ ТРЕНДА
Цель работы: освоить возможности приложения Microsoft Excel для анализа развития предприятий.
Программа Microsoft Excel позволяет сделать приблизительный прогноз при наличии данных за определенный промежуток времени с помощью линии тренда [1].
Пример.
Задание. Используя данные о доходах бюджета г. Иркутска за 2007-2012 годы рассчитайте темп прироста доходов, общую сумму доходов за каждый год и структуру налоговых доходов по периодам. Спрогнозируйте итоговую сумму налогов на 2014 год (постройте линию тренда). Исходные данные смотрите в таблице на рисунке 15.
Рисунок 15 - Доходы бюджета г. Иркутска за 2007-2012 годы
Решение. Для того чтобы построить линию тренда необходимо сначала построить график, отражающий динамику итоговой суммы налогов за каждый год, но для начала рассчитайте строку ИТОГО ДОХОДОВ.
Для того, чтобы построить график необходимо на ленте главного меню выбрать вкладку ВСТАВКА, ГРАФИК (рисунок 2).
Следующий шаг предполагает определение диапазона построения графика. В данной задаче необходимо построить график по итоговой строке. Для этого выделите ячейки по строке ИТОГО ДОХОДОВ, содержащие итоговые значения бюджета по каждому году (B13;D13;F13;H13;J13;L13). Затем выделите ячейки «шапки» таблицы, содержащие названия периодов (B3;D3;F3;13;13;L3). Для этого нажмите клавишу CTRL и, не отпуская ее, выделите необходимые ячейки. Таким образом, получите диапазон для построения графика в виде двух выделенных строк, находящихся в разных частях таблицы. Далее на ленте главного меню выбрать вкладку ВСТАВКА, ГРАФИК. Появится график в виде восходящей кривой линии соединяющей шесть точек (рисунок 16)
Рисунок 16 - Линия тренда
Для того, чтобы построить линию тренда, наведите курсор на кривую и нажмите правую клавишу мыши, в появившемся контекстном меню выберите. Добавить линию тренда. Выберите Линейный тип. Откройте закладку ПАРАМЕТРЫ. Установите Прогноз вперед на 2 периода, поставьте галочки напротив условий: Поместить на диаграмму величину достоверности аппроксимации, Показать уравнение на диаграмме , нажмите Закрыть (рисунок 17).
Рисунок 17 - Параметры линии тренда
На графике появилась прямая линия, показывающая, что в 2014 году в бюджет поступит около 475 000 тыс. рублей. Коэффициент достоверности аппроксимации составляет 0,94. Это свидетельствует о том, что вероятность получения спрогнозированной суммы налогов составляет 94% (рисунок 16).
Ø Для чего используется линия тренда?
Ø Что показывает коэффициент достоверности аппроксимации?
Ø Что необходимо сделать, прежде чем строить линию тренда?
[1] Линии тренда позволяют графически отображать тенденции данных и прогнозировать их дальнейшие изменения. Подобный анализ называется также регрессионным анализом. Регрессионный анализ - форма статистического анализа, используемого для прогнозов. Регрессионный анализ позволяет оценить степень связи между переменными, предлагая механизм вычисления предполагаемого значения переменной из нескольких уже известных значений. Используя регрессионный анализ, можно продлить линию тренда в диаграмме за пределы реальных данных для предсказания будущих значений.
Настройки программы Microsoft Excel позволяют дополнить линию тренда значением квадрата смешанной корреляции – R2. Коэффициент R2 отражает близость значений линии тренда к фактическим данным и представляет собой значение в пределах от 0 до 1. Линия тренда в наибольшей степени приближается к представленной на диаграмме зависимости, если значение равно или близко к 1. При аппроксимации данных с помощью линии тренда значение R2 рассчитывается автоматически. Полученный результат можно вывести на диаграмме[1].
ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ: