Спрогнозируем ежеквартальный объем таможенных платежей на ближайший год на основе имеющихся данных об объемах таможенных платежей за предыдущие годы. Исходные данные для прогнозирования представлены в таблице 5.1. (Примечание: ввод исходных данных начинать с ячейки А1 - Ежеквартальный объем таможенных платежей).

Таблица 5.1 Исходные данные для экстраполяции ежеквартального объема таможенных платежей

    Выполнить обыкновенную линейную экстраполяцию проще всего с помощью выделения экстраполируемого ряда данных, а затем буксировки мышью маркера заполнения. (Выделите мышью интервал В3:В14, установите указатель мыши на маркер заполнения (квадратик в правом нижнем углу выделения) и, удерживая нажатой левую кнопку мыши, протащите его на четыре ячейки вниз.

    Подобный образом можно выполнить не только линейную экстраполяцию, но и экспоненциальную экстраполяцию. Для этого нужно выполнить протаскивание маркера заполнения с помощью правой кнопки мыши. В этом случае будет раскрыто контекстное меню, в котором следует выбрать команду "Экспоненциальное приближение".

    Таким образом можно легко получить интересующие численные значения. Однако, если потребуется вносить изменения в исходные данные, то для того, чтобы получить новые значения экстраполяции, потребуется заново повторить все описанные выше действия, ведь ячейки заполняются соответствующими числовыми значениями, а не формулами, связанными с исходными данными.

    Если необходимо получить экстраполяционные значения, которые бы автоматически изменялись при изменении соответствующих исходных данных, необходимо использовать функции рабочего листа ТЕНДЕНЦИЯ для линейной экстраполяции или РОСТ для экспоненциальной экстраполяции.

    Порядок выполнения задания:

1 заполнить ячейки С3:С18 элементами арифметической прогрессии (начальный член - 1 и шаг - 1), используя команды: Правка - Заполнить - Прогрессия;

2 выделить блок ячеек D3:D14, нажать кнопку мастера функций на панели инструментов, выбрать категорию "Статистические", имя функции "ТЕНДЕНЦИЯ";

3 в поле "изв_знач_у" ввести В3:В14. Нажать "Готово";

4 нажать <F2>, затем одновременно <Ctrl> + <Shift> + <Enter>;

5 выделить блок ячеек D15: D18, нажать кнопку мастера функций на панели инструментов, выбрать категорию "Статистические", имя функции "ТЕНДЕНЦИЯ";

6 в поле "изв_знач_у" ввести В3:В14, "изв_знач_х" ввести С3:С14; "нов_знач_х" ввести С15:С18. Нажать "Готово";

7 нажать <F2>, затем одновременно <Ctrl> + <Shift> + <Enter>;

8. Используя функцию РОСТ, аналогично спрогнозируйте ряд чисел в столбце Е, используя те же данные, что и для функции ТЕНДЕНЦИЯ.

    А теперь в качестве иллюстрации вставим диаграмму с линией тренда.

Порядок выполнения задания:

1 вставка - Диаграмма - На этом листе;

2 исходный диапазон данных В3:В18;

3 тип диаграммы - график;

4 добавить легенду? - нет;

5 название диаграммы - линейный тренд;

6 войти в режим редактирования диаграммы, выделить щелчком вертикальную ось и отформатировать ее: Формат оси - Шкала - минимум:=200000;

7 добавить линию тренда. Для этого в области графика выделить одиночным щелчком ряд данных, затем выполнить следующие действия: Вставка - Линия тренда - тип: Линейная - параметры: Показывать уравнение на диаграмме.

    Окончательный вид рабочего листа с результатами прогнозирования представлен на рис. 5.1.

Рис. 5. 1 Окончательный вид рабочего листа с результатами  

       прогнозирования

Дополнительное задание:

Сделайте прогноз на следующие 5 периодов для исходных данных, используя экспоненциальное распределение:

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

Зачетное задание

1. В соответствии с заданием варианта выбрать из исходных данных (таблицы 5.1 - 5.3)   значения соответствующих показателей за 7 периодов. Перенести в Excel.

2. Сформировать на новом листе Excel   таблицу на основе выбранных данных (путем копирования и вставки транспонированием) так, чтобы значения показателей из строк преобразовались в столбцы.

3. Установить пакет анализа (сервис – надстройки – пакет анализа – установить) и режим ручных вычислений (сервис – параметры – вычисления вручную – установить)

4. Используя Сервис-Анализ данных – корреляция построить корреляционную таблицу, характеризующую взаимосвязи между четырьмя показателями.  

5. Используя Сервис – Анализ данных – Описательная статистика построить таблицу описательной статистики

8. Используя Сервис – Анализ данных – Генерация случайных чисел получить еще по двадцать значений каждого показателя. Снова построить корреляционную таблицу, сравнить с предыдущей.

6. Провести прогноз еще 10 значений каждого показателя. Построить диаграммы с линией тренда.

6. Подготовить отчет, в котором отразить все созданные таблицы, прокомментировать результаты статистических исследований выбранных данных.

Варианты заданий:

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

1. Исходные данные для исследования: внеоборотные активы, долгосрочные финансовые вложения, запасы, краткосрочные финансовые вложения (таблица 5.1)

2. Исходные данные для исследования: основные средства, долгосрочные финансовые вложения, оборотные активы, незавершенное производство (таблица 5.1)

3. Исходные данные для исследования: внеоборотные активы, запасы, долгосрочные дебиторы, денежные средства (таблица 5.1)

4. Исходные данные для исследования: основные средства, долгосрочные финансовые вложения, краткосрочные финансовые вложения, денежные средства (таблица 5.1)

5. Исходные данные для исследования: собственный капитал, добавочный капитал, краткосрочные кредиты и займы, краткосрочная кредиторская задолженность перед поставщиками и подрядчиками (таблица 5.2)

6. Исходные данные для исследования: прибыль, краткосрочные обязательства, задолженность участникам (учредителям), собственный капитал (таблица 5.2)

7. Исходные данные для исследования:  оборотные активы (таблица 5.1), прибыль , краткосрочные кредиты и займы, краткосрочная задолженность перед поставщиками и подрядчиками (таблица 5.2)

8. Исходные данные для исследования: чистая выручка, полная себестоимость, валовая прибыль, чистая прибыль (таблица 5.3)

9. Исходные данные для исследования: оборотные активы (таблица 5.1), собственный капитал (таблица 5.2), себестоимость, валовая прибыль (таблица 5.3)

10.  Исходные данные для исследования: денежные средства (таблица 5.1), краткосрочные обязательства (таблица 5.2), себестоимость, чистая прибыль (таблица 5.3)

Таблица 5.1 - Активы аналитического баланса

Продолжение таблицы 5.1

Таблица 5.2 – Пассивы аналитического баланса

Таблица 5.3 – Отчет о прибылях и убытках

Список литературы

1. Гарнаев А.Ю. Excel, VBA, Internet в экономике и финансах. – СПб.: БХВ-Петербург, 2003. – 816 с.

2. Емельянов А.А. Имитационное моделирование в управлении рисками – СПб: СПбГИЭА, 2000. – 376 с.

3. Петров С В. Методы научных исследований. Методические указания по выполнению лабораторных работ. Калининград: изд-во КГТУ, 2013 – 93 с.

4. Г.Г. Арунянц. Моделирование экономических процессов. Практикум – Калининград: БИЭФ, 2009. – 223 с.