СодержаниеСодержание
Задание 1. Прогнозирование с помощью MS Excel 3
1.1. Применение метода скользящего среднего 3
1.2. Применение функций регрессии Excel 4
1.3. Использование функции экспоненциального сглаживания 7
1.4. Использование множественной регрессии 9
Задание 2. Практические задачи в MS Excel 11
2.1. Создание формы финансового отчета 11
2.1.1. Постановка задачи 11
2.1.2. Ввод исходных данных 11
2.1.3. Проведение расчетов 12
2.2. Нахождение уравнения корреляционной связи 14
2.2.1. Постановка задачи 14
2.2.2. Ввод исходных данных 14
2.2.3. Выполнение расчетов 14
2.2.4. Вид таблицы после проведения расчетов 16
2.2.5. Построение графика зависимости 16
Литература 19ВведениеЗадание 1. Прогнозирование с помощью MS Excel
Любое предприятие в процессе планирования своей деятельности сталкивается с необходимостью определить тенденции изменения основных экономических показателей. Определение будущих значений необходимых показателей для построения смет и бюджетов возможно с применением способов прогнозирования. Некоторые из методов прогнозирования реализованы в составе инструментов широко известного программного приложения - Microsoft Excel. Остановимся на основных методах, очень полезных в процессе прогнозирования хозяйственной деятельности.
1.1. Применение метода скользящего среднего
При использовании этого метода прогноз любого периода представляет собой получение среднего показателя нескольких результатов наблюдений временного ряда. Хотя этот метод слишком прост для создания точного прогноза, иногда он может быть более эффективен, чем методы, основанные на долговременных наблюдениях.
Например, если за последние несколько месяцев в изменении объема продаж наметилась тенденция отличная от той, которая наблюдалась в предыдущие два года, то, используя данные последних месяцев для составления прогноза на следующий месяц, будет получен более точный результат, чем при использовании данных за два года.
Одним из способов создания скользящего среднего в Excel является прямое введение формулы.
Ввести формулу в ячейку можно простым набором на клавиатуре после знака «=» стандартной функции: =СРЗНАЧ(СI:СJ), где С – буквенное обозначение столбца, содержащего необходимые ячейки; I – номер первой ячейки диапазона данных; J – номер последней ячейки диапазона.
Можно ввести формулу в ячейку, используя встроенный инструмент «Мастер функций». В меню «Вставка» выберите «Функция». В открывшемся диалоговом окне, в разделе «Категория» выберите – «Статистические», в разделе «Функция» - СРЗНАЧ. После этого выделите диапазон данных и нажмите «ОК».
Другим способом создания скользящего среднего является использование надстройки «Пакет анализа».
В меню «Сервис» выберите «Анализ данных». Появится диалоговое окно «Анализ данных». Из списка выберите инструмент анализа «Скользящее среднее» и нажмите «ОК». Появится диалоговое окно «Скользящее среднее». В поле «Входной интервал» укажите диапазон в рабочем листе. В поле «Интервал» введите количество периодов, которые хотите включить в подсчет скользящего среднего.
В поле «Выходной интервал» выделите ячейку, с которой хотите начать вывод расчетных значений. Нажмите «ОК» .
С помощью инструментов Excel возможно графическое представление данных скользящего среднего. Для этого необходимо создать график, содержащий значения базовых данных, с помощью инструмента «Мастер диаграмм». Этот инструмент можно вызвать в меню «Вставка» - «Диаграмма», либо на стандартной панели инструментов. После построения графика щелкните на линии правой кнопкой мыши. В появившемся контекстном меню выберите «Добавить линию тренда». В появившемся диалоговом окне щелкните на вкладке «Тип». Выберите линию тренда «Линейная фильтрация», а затем – необходимое для расчета скользящего среднего количество периодов с помощью счетчика «Точки». Нажмите «ОК». Вы увидите на графике линию тренда скользящего среднего.
1.2. Применение функций регрессии Excel
Для применения метода регрессии в процессе прогнозирования показателей можно использовать стандартные функции Excel: ТЕНДЕНЦИЯ и РОСТ. Методы регрессии оценивают взаимосвязь между фактическими данными наблюдений и другими параметрами, например, порядковыми номерами периодов наблюдений или датами наблюдений.
Рассмотрим применение функции ТЕНДЕНЦИЯ.
Предположим, данные наблюдений конкретного показателя внесены в рабочем листе в столбце А с 1-ой по I-ю ячейки. Значения порядковых номеров наблюдений внесены в столбце В, рядом со значениями соответствующих показателей. На основании имеющихся данных, вы можете построить прогноз исследуемого показателя на последующие периоды временного ряда, по которым еще нет результатов наблюдений. Например, у вас имеются результаты десяти наблюдений, и вы хотите определить прогнозные показатели на следующие десять аналогичных периодов времени. В ячейках А1:А10 внесены значения показателя, а в ячейках В1:В10 - порядковые номера наблюдений 1, 2, 3 … 10.
Чтобы определить прогнозное значение одного первого периода, в ячейку В11 внесите порядковый номер этого периода – 11. Далее, в ячейку С11 вставьте формулу функции ТЕНДЕНЦИЯ следующим образом. Выделите ячейку С11. В меню «Вставка» выберите «Функция». В появившемся диалоговом окне в разделе «Категория» выберите «Статистические», а в разделе «Функция» - ТЕНДЕНЦИЯ. Нажмите «ОК».
Появится диалоговое окно функции ТЕНДЕНЦИЯ. В строке «Изв_знач_у» выделите диапазон ячеек А1:А10. В строке «Изв_знач_х» выделите диапазон ячеек В1:В10. В строке «Нов_знач_х» выделите ячейку В11.Нажмите «ОК». В ячейке С11 появится прогнозное значение показателя на одиннадцатый период.
Для того чтобы определить прогнозные значения сразу на десять будущих периодов, нужно формулу функции ТЕНДЕНЦИЯ ввести в диапазон ячеек С11:С20. Для этого внесите в ячейки В11:В20 порядковые номера прогнозируемых периодов – 11, 12, 13 … 20.
Выделите диапазон ячеек С11:С20. Нажмите комбинацию клавиш «Ctrl+Shift+Enter» (с помощью этой комбинации вводятся формулы массива) и, не отпуская клавиши, проделайте аналогичные действия, которые описывались выше. С одним изменением. В строке «Нов_знач_х» диалогового окна функции «ТЕНДЕНЦИЯ» выделите диапазон ячеек В11:В20.
В ячейках С11:С20 появятся прогнозные значения для десяти будущих периодов.
Функция ТЕНДЕНЦИЯ вычисляет прогнозы, основанные на линейной связи между результатом наблюдений и временем наблюдений. Если же взаимосвязь показателей носит нелинейный характер, более точный прогноз вы можете построить, используя функцию Excel РОСТ.
Используя тот же пример, в ячейках А1:А10 внесены данные наблюдений показателя, в ячейках В1:В10 – порядковые номера наблюдений. Так же как и для функции ТЕНДЕНЦИЯ, введите в ячейки В11:В20 номера прогнозных периодов. Далее проделайте аналогичные действия, только в разделе «Функция» диалогового окна «Мастер функций» выберите РОСТ. В ячейках С11:С20 вы получите прогнозные значения показателя, рассчитанные с использованием функции РОСТ .
Если у вас возникла необходимость провести регрессионный анализ на графике, который вы построили по данным наблюдений, можно не вводить в рабочий лист значения номеров прогнозных периодов. Прогноз можно сделать с помощью графической линии тренда.
Щелкните правой кнопкой мыши на линии построенного графика. В появившемся контекстном меню выберите «Добавить линию тренда». Выберите тип линии тренда «Линейная». Щелкните на вкладке «Параметры». В поле «Вперед на» введите количество желаемых периодов для прогноза. При желании, можете установить флажок «показывать уравнение на диаграмме». Нажмите «ОК». В результате на графике будет построена линия тренда вашего временного ряда, продленная вперед на то количество периодов, которое вы выбрали.ЛитератураЛитература
1. Додж М., Стинсон К. Эффективная работа Excel 2002. Издательство «Питер», 2003, 992 с.
2. Ефимова О.В. Microsoft Office Excel 2003. Электронные таблицы. Издательство «Интеллект-Центр», 2006, 112 с.
3. Курбатова Microsoft Excel 2003. Самоучитель. Диалектика, 2005. – 219 с.
4. Марк Дж., Крейг С. Эффективная работа с Excel 2003. Издательство «Питер», 2003, 992 с.
5. Новиков Ф., Яценко А. Microsoft Office XP в целом Издано: 2002, СПб., "БХВ-Петербург", 928 стр.
6. Правин О.В. Правильный самоучитель работы на компьютере. Издательство «БХВ-Петербург», 2003, 480 с.
7. Уокенбах. Excel 2002 Библия пользователя. Диалектика, 2002. – 316 с.
|
|
