Пример 4. Интеллектуальные информационные технологии в поддержке принятия решений. Прогнозирование по временным рядам.

Найти наилучшую трендовую модель и сделать прогноз на один период вперед для трех показателей ВВП на душу населения России, США и Китая.

Россия
США
Китай

Решение: Для нахождения тренда необходимо построить диаграмму динамики с помощью средств Microsoft Excel. Для построения диаграмм динамики необходимо воспользоваться графическими возможностями ППП Excel. Для этого надо ввести значения динамического ряда в ячейки Книги Excel, выделить диапазон данных мышкой вместе с названием и выбрать команду ВСТАВКА/ График.

Результат построения диаграммы динамики представлен на рисунке

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

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

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

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

Чтобы получить функциональное описание тренда необходимо перейти на вкладку Параметры и установить опцию Показать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации R^2.

Величина достоверности аппроксимации R^2 определяется как квадрат коэффициента корреляции между уровнем динамического ряда и временем и характеризует долю вариации уровня динамического ряда, объясненную влиянием времени. Чем ближе R^2 к 1, тем более значимо уравнение тренда. По величине достоверности аппроксимации определяют выбор вида тренда, за лучшее приближение модели к фактическому процессу принимают ту функциональную зависимость, для которой коэффициент достоверности аппроксимации ближе к 1. (Но, если коэффициент достоверности аппроксимации меньше 0,7, то трендовая модель временного ряда не существует.)

Если какой-либо тренд дал достаточно высокую величину R^2, то навкладке Параметры можно задать Прогноз на 1 период вперед.

На каждом из 6 графиков необходимо построить разные модели трендов: линейный, логарифмический, полиномиальный (степень 2 и степень 3), степенной, экспоненциальный. Из построенных трендовых моделей необходимо отобрать наилучший по наибольшему значению коэффициента достоверности аппроксимации.

Так, например, для ВВП на душу населения США наилучшей моделью тренда является линейная. Линейный тренд уровня динамики ВВП на душу населения для США представлен на рисунке. Анализ данного графика показывает, что модель динамического ряда, выбранная в виде линейного тренда, в принципе адекватно описывает реальный процесс, коэффициент достоверности аппроксимации 0,996, что очень близко к 1. Прогнозирование в такой модели возможно и мы видим, что точечный прогноз, на первый взгляд, достоверен.

Линейный тренд динамического ряда для США

В случае динамического ряда ВВП на душу населения России наиболее высокая величина достоверности аппроксимации 0,973 у полиномиального тренда второй степени, но она не достаточна для адекватного описания реального процесса, поэтому и прогнозирование в данной модели скорей всего не достоверно.

Полиномиальный тренд динамического ряда для России.

ЗАДАНИЯ КОНТРОЛЬНОЙ РАБОТЫ

Задание 1. Работа в генераторе СППР. Построение шаблонов.

1. Создать шаблон в Excel для расчета выручки, прибыли и рентабельности продукции, если дано:

- Количество реализованной продукции q;

- Цена единицы продукции p;

- Себестоимость единицы продукции z.

Найти:

- Стоимость реализованной продукции ;

- Прибыль от реализации ;

- Рентабельность продукции .

С помощью шаблона рассчитать: Рентабельность продукции, если реализовано 2500 ед. товара по цене 2000 руб. за ед., себестоимость составила 1800 руб. на ед.

На сколько надо повысить цену, чтобы рентабельность продукции увеличилась до 15%?

2. Создать шаблон в Excel для расчета наращенной суммы сложных процентов, если известна современная стоимость Р, срок сделки n, процентная ставка i. Необходимо определить наращенную сумму .

Определите наращенную сумму через 5 лет на депозите при процентной ставке 20% и первоначальной стоимости 100000 руб.

Рассчитайте, какую сумму надо вложить на счет в банке сроком на 3 года под 25%, чтобы получить сумму равную 250 000 руб.

3. Создать шаблон в Excel для расчета современной суммы постоянной ренты, если известна сумма периодического платежа R, срок сделки n, процентная ставка i. Необходимо определить современную стоимость .

Какие инвестиционные вложения необходимо произвести в производство, чтобы получать ежегодный доход в размере 100000 руб. в течение 12 лет, если рентабельность проекта оценивается в 16%.

Рассчитайте период окупаемости инвестиционного проекта, который требует инвестиционных вложений 10 млн. руб., а ожидаемые ежегодные доходы составят 2,5 млн. руб., ставка сравнения 12%.

4. Создать шаблон в Excel для расчета наращенной суммы постоянной ренты, если известна сумма периодического платежа R, срок сделки n, процентная ставка i. Необходимо определить наращенную сумму .

Какая сумма будет на пенсионном счете через 20 лет, если ежегодно делать вклад в размере 2000 руб., процентная ставка 10%.

Рассчитайте периодический платеж, который необходимо делать ежегодно, что бы через 5 лет получить сумму равную 250 000 руб. при ставке 32%.

5. Создать шаблон в Excel для расчета современной стоимости векселя P, если известна сумма, указанная на векселе S, дата погашения векселя , дата учета векселя и учетная процентная ставка банка d. Современная стоимость рассчитывается по формуле . (Для t задать формат даты).

Рассчитать какую сумму получит держатель векселя на сумму 20000 руб., срок погашения по которому наступает 25 декабря 2003 года, если он учтет его в банке 27 ноября 2003 года по учетной ставке 24%?

Если известно, что сумма, которую выплатит банк за вексель стоимостью 10000 руб. (срок погашения 12 декабря 2003 года), равна 9000 руб. 25 ноября 2003 года, то чему равна учетная ставка банка?

6. Создать шаблон в Excel для расчета оптимального размера партии заказа, если известны спрос на товар r, затраты на заказ товара - , затраты на хранение единицы товара в единицу времени - . Оптимальный размер заказа рассчитывается по формуле .

Рассчитать оптимальный размер заказа, если известно, что затраты на заказ партии товара составляют 30 руб., спрос на товар 8000 шт. в год, затраты на хранение единицы товара в год равны 3 руб./шт.

Поставщик может поставить 300 ед. товара в год. Какой объем спроса останется неудовлетворенным?

7. Создать шаблон в Excel для расчета средней длины очереди в супермаркете, если известны коэффициент загрузки системы - , число кассовых аппаратов – n, и вероятность того, что все каналы обслуживания (кассовые аппараты) будут заняты - . Средняя длина очереди рассчитывается по формуле .

Супермаркет имеет 5 кассовых аппаратов, коэффициент загрузки системы равен 4, вероятность того, что все обслуживающие кассы будут заняты равна 0,55. Рассчитайте среднюю длину очереди.

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

8. Создать шаблон в Excel для расчета наращенной суммы простых процентов, если известна современная стоимость Р, дата заключения сделки t₀ и дата итоговых выплат t₁, процентная ставка i. Необходимо определить срок операции t=t₁-t₀, проценты и наращенную сумму .

На депозит 22.08 поместили сумму 20000 рублей под 12% годовых. Какую сумму снимет вкладчик через полгода.

Рассчитайте, какую сумму надо вложить на счет в банке сроком 140 дней под 25%, чтобы получить сумму равную 250 000 руб.

9. По данным 1960-1995 гг. производственная функция валового внутреннего продукта США имеет вид: , где Q - ВВП, K - основные производственные фонды, L - трудовые ресурсы. Создайте шаблон в Excel для прогноза валового внутреннего продукта США, если известны производственные фонды (К) и трудовые ресурсы (L).

Известно, что основные фонды в 1996 году составят 288 условных единиц, а трудовые ресурсы – 193 у.е. Рассчитайте прогнозное значение ВВП на 1996 г.

Для уровня ВВП в 1800 условных единиц рассчитайте какова должна быть стоимость основных фондов, если трудовые ресурсы равны 214 условных единиц.

10. Создать шаблон в Excel для расчета ежегодной выплаты по кредиту, если известна сумма долга D, срок выплат n, кредитная процентная ставка i. Ежегодный платеж рассчитывается по формуле: .

Рассчитать ежегодный платеж, который должна производить фирма, если сумма кредита, предоставленного банком 1 млн. руб., срок погашения кредита 3 года, кредитная ставка 21,8%.

На сколько необходимо увеличить срок выплат, если известно, что фирма может выплачивать в год не более 400 тыс. руб.?

Задание 2. Работа в генераторе СППР. Факторный анализ методами «What-if».

1. Порог рентабельности производства q рассчитывается, исходя из постоянных и переменных издержек на производство товара и его цены, по формуле: , где FC – постоянные издержки; р – цена единицы товара; v – переменные издержки единицы товара.

Требуется построить шкалу для порога рентабельности, если цена на товар меняется от 950 до 1100 руб. (шаг 50 руб.), переменные издержки на единицу продукции – от 540 до 610 руб. (шаг 10 руб.), а постоянные издержки не меняются и равны 120000 руб.

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

Требуется построить таблицу рентабельности для процессора, если его цена меняется от 4,8 до 5,4 тыс. руб. (шаг 150 руб.), а себестоимость – от 4,2 до 4,6 тыс. руб. (шаг 100 руб.).

3. В модели дисконтирования дивидендов внутренняя стоимость акции (V) оценивается, исходя из суммы дивидендов, выплаченных в предшествующий период (), темпа прироста дивидендов (q) и рыночной кредитной ставки (ставки сравнения i), по формуле: .

Требуется построить таблицу для внутренней стоимости акции при изменении рыночной кредитной ставки от 22% до 18% (шаг 1%) и темпа прироста дивидендов от 3,5% до 6% (шаг 0,5%), если известно, что дивиденды предшествующего периода составили 20 руб. на акцию.

4. В линейной модели сумма амортизации определяется, исходя из первоначальной стоимости (Р), ликвидационной стоимости (L) и срока эксплуатации оборудования (n), по формуле: .

Требуется построить шкалу для суммы амортизации при изменении первоначальной стоимости от 120 до 140 тыс. руб. (шаг 5 тыс. руб.), и срока эксплуатации оборудования от 5 до 8 лет (шаг 1 год), если ликвидационная стоимость составляет 15% от первоначальной.

5. Коэффициент укомплектованности кадрами предприятия рассчитывается, исходя из фактически работающих специалистов с высшим (), средне-специальным () образованием и практиков (П), работающих на должностях специалистов, и специалистов, требуемых по штату (, ), по формуле: .

Рассчитайте шкалу для коэффициента укомплектованности кадрами предприятия, если по штату специалистов с высшим образованием требуется 120 человек, с средне-специальным – 50 человек, а фактически работает – специалистов с высшим образованием от 110 до 115 человек (шаг 1 чел.), с средне-специальным – 40-47 человек (шаг 1 чел.), практиков 5 человек.

6. Коэффициент приведения для ежегодных рентных выплат рассчитывается, исходя из значения процентной ставки (i) и срока выплат (n), по формуле: .

Постройте таблицу для расчета коэффициента приведения при варьировании процентной ставки от 5 до 27% (шаг 2%) и срока выплат от 1 до 10 лет (шаг 1 год).

7. Множитель наращения для определения наращенных сумм на депозитных счетах рассчитывается, исходя из значения процентной ставки (i) и срока выплат (n), по формуле: .

Постройте таблицу для расчета множителя наращения при варьировании процентной ставки от 9 до 33% (шаг 2%) и срока выплат от 1 до 12 месяцев (шаг 1 месяц).

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

Постройте таблицу для расчета оптимального размера заказа при изменении затрат на заказ товара от 25 до 40 руб. (шаг 3 руб.), затрат на хранение – от 3 до 8 руб. (шаг 1 руб.), если известно, что спрос на товар равен 10000 шт.

9. Средняя длина очереди рассчитывается, исходя коэффициента загрузки системы - , числа кассовых аппаратов – n, и вероятности того, что все каналы обслуживания (кассовые аппараты) будут заняты - , по формуле .

Постройте таблицу расчета длины очереди, если коэффициент загрузки меняется от 2,5 до 3,5 (шаг 0,2), вероятность того, что все обслуживающие кассы будут заняты – от 0,45 до 0,8 (шаг 0,05), если фирма имеет 4 кассовых аппаратов.

10. Перспективная численность населения на n лет вперед рассчитывается, исходя из общего прироста населения на 1000 человек () и численности населения предшествующего периода (), по формуле: .

Постройте таблицу перспективной численности населения на 10 лет вперед (шаг 1 год) при общем приросте населения на 1000 человек варьирующемся от -2 до 8 человек на 1000 населения, если численность населения на настоящий момент времени равна 1188 тыс. человек.

Задание 3. Интеллектуальные информационные технологии в поддержке принятия решений. Построение уравнения регрессии.

Имеются сведения о фонде оплаты труда (ФОТ, млн. руб.) и численности работающих (S, тыс. чел.) в бюджетных организациях города:

Вариант	Показатели	I	II	III	IV	V	VI	VII	VII	IX	X
	ФОТ	10,1	11,2	13,4	14,2	14,8	14,6	15,2	15,6	15,8	16,5
	S	5,2	6,1	6,4	7,1	7,3	7,1	7,5	7,6	7,4	8,2
	ФОТ	4,2	4,8	4,3	4,6	5,7	6,2	4,1	7,2	7,4	7,5
	S	2,0	2,3	2,1	2,3	2,8	3,0	1,9	3,5	3,6	3,6
	ФОТ	8,2	8,6	7,9	6,8	6,4	5,8	5,2	5,4	4,9	5,3
	S	2,1	2,2	1,9	1,6	1,5	1,4	1,3	1,3	1,2	1,3
	ФОТ	12,5	12,7	13,6	14,5	15,6	15,9	16,7	15,2	14,5	13,2
	S	3,1	3,2	3,6	3,7	3,9	3,9	4,1	3,8	3,6	3,5
	ФОТ	20,4	21,5	23,1	22,6	23,4	25,6	28,1	27,5	28,6	27,4
	S	5,1	5,4	5,8	5,6	5,8	6,4	6,5	6,4	6,6	6,3
	ФОТ	30,1	35,4	36,4	38,5	40,5	45,6	40,7	42,8	44,6	45,0
	S	3,0	3,4	3,6	3,7	4,1	4,5	4,1	4,3	4,5	4,6
	ФОТ	50,6	54,1	58,2	56,7	60,4	62,5	59,2	57,4	52,8	55,8
	S	5,1	5,5	5,9	5,7	6,1	6,2	6,0	5,7	5,3	5,6
	ФОТ	10,5	10,8	12,4	13,7	13,9	14,8	15,9	16,4	16,7	15,2
	S	1,1	1,1	1,2	1,4	1,4	1,5	1,6	1,6	1,7	1,5
	ФОТ
	S	10,2		10,4	10,9	11,1	12,1	13,4	12,4	12,7	11,4
	ФОТ
	S	11,9	14,7	13,9	16,1	13,7	12,9	14,8	15,8	16,4	12,3

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

Отобразить на графиках фактические данные и результаты расчета. Сделать выводы.

Дата добавления: 2014-11-18