Автоматизированный анализ динамики социально-экономических явлений в среде MS Excel

 

Федеральное государственное образовательное  бюджетное учреждение

высшего профессионального  образования

ФИНАНСОВЫЙ  УНИВЕРСИТЕТ ПРИ ПРАВИТЕЛЬСТВЕ  РОССИЙСКОЙ ФЕДЕРАЦИИ (ФИНУНИВЕРСИТЕТ)

FINANCIAL UNIVERSITY AT GOVERNMENT OF THE RUSSIAN FEDERATION (FINUNIVERSITY)

Уфимский  филиал Финуниверситета

КАФЕДРА БУХГАЛТЕРСКОГО УЧЕТА, АУДИТА И СТАТИСТИКИ

 

 

 

 

 

 

О Т Ч Е  Т 

о результатах выполнения

компьютерной лабораторной работы №3

 

Автоматизированный  анализ динамики социально-экономических  явлений в среде MS Excel

 

 

Вариант № 73

 

 

 

 

 

Исполнитель:	Григорьева Р.Ю.
Специальность	Менеджмент организации
группа	1СП(БМ)
Руководитель:

 

 

 

 

Уфа 2013 г.

1. Постановка  задачи

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

Полученные два ряда динамики представлены в формате  электронных таблиц процессора Excel, годовые данные -  в диапазоне  ячеек A6:B12, а данные за 6-ой год по месяцам - в диапазоне D6:E19. Для демонстрационного примера (ДП) данные о выпуске продукции приведены в Таблице 3.1.

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

Задание 1. Расчёт и анализ показателей ряда динамики выпуска продукции за шестилетний период.

Задание 2. Прогноз показателя выпуска продукции на 7-ой год методом экстраполяции.

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

2. Результат  выполнения лабораторной работы

Задание 1.

Расчёт и  анализ показателей ряда динамики выпуска  продукции за шестилетний период

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

Задача 1. Расчет цепных и базисных показателей динамики: абсолютный прирост (сокращение); темп роста (снижения); темп прироста (сокращения) и абсолютное значение 1 % прироста.

Задача 2. Расчет средних показателей ряда динамики: средний уровень ряда динамики; средний абсолютный прирост; средний темп роста и средний темп прироста.

 

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

1. В ячейке, выделенной для значения абсолютного прироста цепного за первый год (С26), перед формулой поставить знак равенства «=»;
2. Enter;
3. Размножить формулу на всю графу.
4. Выполнить действия 1–2 поочередно для всех аналитических показателей ряда динамики табл.3.2: абсолютного прироста базисного; темпа роста цепного и базисного; темпа прироста цепного и базисного; абсолютного значения 1% прироста.

 

 

Задача 1.2. Расчёт средних показателей ряда динамики

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Задание 2.

Прогноз показателя выпуска продукции на 7-ой год  методом экстраполяции.

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

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

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

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

 

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

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

 

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

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

 

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

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

Действия 1 – 4 (в п.4  шаги 1–8) выполнить для линии тренда: степенная; полиномиальная 3-го порядка.

Рис. 2.3. График сглаживания по линейной, степенной и полиномиальной 3-го порядка ряда динамики

 

Задание 3.

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

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

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

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

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

 

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

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

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

9. ОК.

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

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

 

 

Рис.3.1. Скользящее среднее

 

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

1. Выделить мышью столбец данных в диапазоне B47:В58 (табл. 3.5);
2. Вставка=>Диаграмма=>Точечная;
3. В появившемся диалоговом окне Мастер диаграмм выбрать вид точечной диаграммы, на которой значения соединены отрезками;
4. Далее;
5. В появившемся диалоговом окне Мастер диаграмм выбрать вкладку Ряд и ввести имя ряда «Исходные данные»;
6. Далее;
7. В появившемся диалоговом окне Мастер диаграмм выбрать вкладку Заголовки и задать названия диаграммы («Выпуск продукции за 6-ой год по месяцам») и осей Х («Месяцы») и У («Выпуск продукции, млн. руб.»;
8. Готово.

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

 

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

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

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

Результат работы представлен  на рис.3.3.

Рис. 3.3. График сглаживания по линейной и степенной ряда динамики

ПРИЛОЖЕНИЕ

Результативные  таблицы