Автоматизированный априорный анализ статистической совокупности в среде MS Excel

Задача 2. Прогнозирование выпуска продукции предприятием на год вперёд с использованием аналитического выравнивания ряда динамики по прямой, параболе и полиному 3-го порядка.

Алгоритмы выполнения Задания  2

Задача 1. Прогнозирование выпуска продукции предприятием на год вперёд с использованием среднего абсолютного прироста и среднего темпа роста.

Алгоритм  2.1. Расчёт выпуска продукции на год вперёд с использованием среднего абсолютного прироста и среднего темпа роста

1. В ячейке, выделенной для значений прогнозируемого выпуска продукции по среднему абсолютному приросту (Е41), перед формулой поставить знак равенства «=».
2. Enter;
3. Выполнить действия 1–2 для прогнозируемого выпуска продукции по значению среднего темпа роста (табл.3.4).

     Результат работы алгоритма 2.1 для демонстрационного примера приведен в табл. 3.4 – ДП.

Задача 2. Прогнозирование выпуска продукции предприятием на год вперёд с использованием аналитического выравнивания ряда динамики по прямой, параболе и полиному 3-го порядка.

Алгоритм  2.2. Построение графика динамики выпуска продукции за 6 лет с использованием средств инструмента МАСТЕР ДИАГРАММ

1. Выделить мышью ячейки, содержащие выпуск продукции за 6 лет (диапазон ячеек B7:B12);
2. Вставка=>Диаграмма=>Точечная;
3. В появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4) выбрать вид точечной диаграммы, на которой значения соединены отрезками;
4. Далее;
5. В появившемся диалоговом окне Мастер диаграмм (шаг 2 из 4) выбрать вкладку Ряд и задать имя ряда 1 – «Исходные данные»;
6. Далее;
7. В появившемся диалоговом окне Мастер диаграмм (шаг 3 из 4) выбрать вкладку Заголовки и задать названия диаграммы («Прогнозирование выпуска продукции на 7-ой год») и осей Х («Годы») и У («Выпуск продукции. млн. руб.»;;
8. Готово;
9. Выделить на полученной диаграмме ось Y (подвести курсор к требуемой оси и щелкнуть левой клавишей мыши);
10. Формат => Выделенная ось;
11. В появившемся диалоговом окне Формат оси выбрать вкладку Шкала;
12. В поле Минимальное значение – ввести минимальное (или несколько ниже) значение признака «Выпуск продукции»;
13. ОК.

     Выделить диаграмму и с помощью приема "захват мышью" переместить ее в конец Рабочего файла.

Алгоритм  2.3. Нахождение тренда ряда динамики выпуска продукции методом аналитического выравнивания и прогнозирование его на год вперед с помощью инструмента Мастер диаграмм

1. Выделить мышью диаграмму «Прогнозирование выпуска продукции на 7-ой год», расположенную в конце Рабочего файла;
2. Диаграмма => Добавить линию тренда;
3. В появившемся диалоговом окне Линия тренда выбрать вкладку Тип и задать вид линии тренда – линейная;
4. Выбрать вкладку Параметры и выполнить действия:
1. Переключатель Название аппроксимирующей кривой: автоматическое/другое – установить в положение другое и ввести имя тренда– Прямая;
2. В поле Прогноз вперед на…едициц ввести значение «1»;
3. Поле Прогноз назад на…единиц – НЕ активизировать;
4. Флажок   Пересечение   кривой   с   осью   Y   в   точке   –   НЕ активизировать;
5. Флажок Показывать уравнение на диаграмме – Активизировать;
6. Флажок Поместить на диаграмму величину достоверности аппроксимации R² – Активизировать;
7. ОК;
8. Установить курсор на линию тренда, щелкнуть правой клавишей мыши и выбрать меню Формат линии тренда;
9. В появившемся диалоговом окне Формат линии тренда выбрать вкладку Вид и задать по своему усмотрению тип, цвет и толщину линии;
10. ОК;
11. Выделить уравнение линии тренда и индекс детерминации R² и с помощью приема "захват мышью" вынести их за корреляционное поле. При необходимости уменьшить размер шрифта.

5. Действия 1 – 4 (в п.4  шаги 1–11) выполнить для линии тренда:

Парабола (полином 2-го порядка);

Степенная.

      Результат работы алгоритмов 2.2 – 2.3 для демонстрационного примера представлен на рис.3.1.

      

 

                                   Рис.3.1. График динамики выпуска продукции за 6 лет и прогноз выпуска на год вперед

 

Задание 3.

Выявление тенденции развития изучаемого явления (тренда) по данным о выпуске продукции по месяцам за 6-ой год методами скользящей средней и аналитического выравнивания.

      Выполнение  Задания 3 заключается в решении двух задач:

Задача  1. Расчет скользящей средней, полученной на основе трёхчленной скользящей суммы.

Задача  2. Аналитическое выравнивание по прямой и параболе.

Алгоритмы выполнения Задания 3

Задача 1. Расчет скользящей средней, полученной на основе трёхчленной скользящей суммы

Алгоритм  3.1. Нахождение значений скользящей средней с помощью инструмента СКОЛЬЗЯЩЕЕ СРЕДНЕЕ надстройки ПАКЕТ АНАЛИЗА

1. Сервис => Анализ данных => Скользящее среднее => ОК;
2. Входной интервал <= диапазон ячеек табл. 3.5 со значениями признака – Выпуск продукции (В47:В58);
3. Метки в первой строке– НЕ активизировать;
4. Интервал – НЕ активизировать;
5. Выходной интервал <= адрес первой ячейки третьего столбца выходной результативной таблицы (С47);
6. Новый рабочий лист и Новая рабочая книга – НЕ активизировать;
7. Вывод графика –Активизировать;
8. Стандартные погрешности – НЕ активизировать;

9. ОК.

     Выделить  диаграмму и с помощью приема "захват мышью" переместить ее в конец Рабочего файла.

      В результате указанных действий осуществляется вывод значений скользящей средней, начиная с ячейки, указанной в  поле Выходной интервал диалогового окна инструмента Скользящее среднее, и графика. Для демонстрационного примера они представлены в табл.3.5 и на рис.3.2.

 
 
 

Рис.3.2. График сглаживания ряда динамики выпуска продукции за 6-ой год, сгенерированный в режиме «скользящее среднее» Пакета анализа

Алгоритм  3.2. Приведение выходной таблицы к виду, принятому в статистике

1. Ячейку С47, содержащую термин " #Н/Д", выделить мышью и очистить, нажав клавишу [Delete];
2. Ячейки результативной таблицы (С49:С58), содержащие значения «Скользящее среднее», вырезать с помощью инструмента ;
3. Вставить в табл. 3.5, начиная с ячейки С48, с помощью инструмента ;
4. Отформатировать таблицу по образцу с помощью инструмента .

      Для демонстрационного примера полученная результативная таблица выглядит следующим  образом.