ВСЕРОССИЙСКИЙ ЗАОЧНЫЙ ФИНАНСОВО-ЭКОНОМИЧЕСКИЙ ИНСТИТУТ

КАФЕДРА СТАТИСТИКИ

Лабораторная работа №3

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

Вариант № 6

                                       Исполнитель: Ржавичева Татьяна Владимировна

                                            Специальность:  финансы и кредит 

                                            Группа: дневная III курс

                        № зачетной книжки: 08ФФД40351

                                                       Руководитель: Леонова Светлана Николаевна

Орел 2009г

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

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

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

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

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

2.     Enter;

3.     Установить курсор в правом нижнем углу ячейки (С26) с формулой – образцом (курсор примет форму черного крестика) и, удерживая левую клавишу мыши в нажатом состоянии, переместить курсор до нижней клетки графы. Отпустить клавишу мыши (формула – образец размножилась на всю графу).

4.     Выполнить действия 1–2 поочередно для всех аналитических показателей ряда динамики табл.3.2:

абсолютного прироста базисного;

темпа роста цепного и базисного;

темпа прироста цепного и базисного;

абсолютного значения 1% прироста.

Результат работы алгоритма 1.1 в табл. 3.2

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

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

1.     В ячейке, выделенной для значения средний уровень ряда динамики (E34), перед формулой поставить знак равенства «=»;

2.     Enter;

3.     Выполнить действия 1–2 поочередно для всех средних показателей ряда динамики табл.3.3:

среднего абсолютного прироста;

среднего темпа роста;

среднего темпа прироста.

Результат работы алгоритма 1.2 в табл. 3.3

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

Алгоритмы выполнения Задания 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.     Флажок Поместить на диаграмму величину достоверности аппроксимации R2 – Активизировать;

7.     ОК;

8.     Установить курсор на линию тренда, щелкнуть правой клавишей мыши и выбрать меню Формат линии тренда;

9.     В появившемся диалоговом окне Формат линии тренда выбрать вкладку Вид и задать по своему усмотрению тип, цвет и толщину линии;

10.     ОК;

11.     Выделить уравнение линии тренда и индекс детерминации R2  и с помощью приема "захват мышью" вынести их за корреляционное поле. При необходимости уменьшить размер шрифта.

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

парабола;

полином 3-го порядка.

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

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

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

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

Алгоритм 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.     Отформатировать таблицу по образцу с помощью инструмента .

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

График сглаживания ряда динамики выпуска продукции методом скользящей средней представлен на рис. 3.3.

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

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

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

1.     Выделить мышью столбец данных в диапазоне B47:В58 (табл. 3.5);

2.     Вставка=>Диаграмма=>Точечная;

3.     В появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4) выбрать вид точечной диаграммы, на которой значения соединены отрезками;

4.     Далее;

5.     В появившемся диалоговом окне Мастер диаграмм (шаг 2 из 4) выбрать вкладку Ряд и ввести имя ряда «Исходные данные»;

6.     Далее;

7.     В появившемся диалоговом окне Мастер диаграмм (шаг 3 из 4) выбрать вкладку Заголовки и задать названия диаграммы («Выпуск продукции за 6-ой год по месяцам») и осей Х («Месяцы») и У («Выпуск продукции. млн. руб.»;

8.     Готово.

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

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

1.     Выделить мышью диаграмму «Выпуск продукции за 6-ой год по месяцам», расположенную в конце Рабочего файла;

2.     Диаграмма => Добавить линию тренда;

3.     В появившемся диалоговом окне Линия тренда выбрать вкладку Тип и задать вид линии тренда – линейная;

4.     Выбрать вкладку Параметры и выполнить действия:

1.           Переключатель Название аппроксимирующей кривой: автоматическое/другое – установить в положение другое и ввести имя тренда - Прямая;

2.           Поле Прогноз вперед на – НЕ активизировать;

3.           Поле Прогноз назад на – НЕ активизировать;

4.           Флажок   Пересечение   кривой   с   осью   Y   в   точке   –   НЕ активизировать;

5.           Флажок Показывать уравнение на диаграмме – Активизировать;

6.           Флажок Поместить на диаграмму величину достоверности аппроксимации R2 – Активизировать;

7.           ОК;

8.           Установить курсор на линию тренда, щелкнуть правой клавишей мыши и выбрать меню Формат линии тренда;

9.           В появившемся диалоговом окне Формат линии тренда выбрать вкладку Вид и задать по своему усмотрению тип, цвет и толщину линии;

10.      ОК;

11.      Выделить уравнение линии тренда и индекс детерминации R2  и с помощью приема "захват мышью" вынести их за корреляционное поле. При необходимости уменьшить размер шрифта.

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

По окончании работы алгоритма 2.4 выполнить следующие действия:

1.     Снять заливку области построения;

2.     При необходимости изменить масштаб шкалы осей диаграммы.

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

                 Рис. 3.4. График сглаживания по прямой и параболе ряда динамики выпуска продукции за 6-ой год

ПРИЛОЖЕНИЕ

        Таблица 3.1