Введение

ЗАДАНИЕ

Краткая характеристика проблемы

Статистические функции, прогнозирование динамических рядов

Использование MS Excel для решения финансовых и экономических задач

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

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

Если в регрессии можно считать время в качестве независимой переменной, то ход развития явления, процесса связывается не с какими-либо конкретными факторами, а с течением времени (а точнее с временным интервалом: сек., мин., час, сут., неделя, мес., квартал, год,). В таком случае терминология "Регрессионный анализ" заменяется на – "Анализ временных рядов". Например, в торгах на Международных валютных биржах динамика изменения цен в реальном режиме определяется секундами.

Имеется следующая информация предприятияю торговли о продолжительности эксплуатации типового оборудования и затратах на его ремонт(табл. 1).

В целях нормирования расхода средств на ремонт оборудования найти зависимость затрат на ремонт оборудования от срока эксплуатации оборудования.

Таблица 1

При статистическом изучении связи показателей выберем прямолинейную форму зависимости (аппроксимации) между признаками Х и Уприменением формулы У =А₀ + A₁ * X.

1. Построить линейный тренд для данных таблицы 3.

Действия:

· Ввести значения Х и У по образцу(рис. 1).

· Щелкнуть на пиктограммеМастера диаграмм . Выбрать тип диаграммыЛинейная (Excel 95) илиТочечная (Excel 97).

· Ввести названия диаграммы и осей по образцу .

· Закончить построение диаграммы;

· Выбрать команду главного менюДиаграмма, Добавить линию тренда (Excel 97) или –Вставка, Линия тренда. Выбрать тип тренда –линейный;

· Раскрыть вкладкуПараметры и установить опцию –Прогноз на 1 период вперед, затем установить флажок –показывать уравнение на диаграмме, и флажок –поместить на диаграмму величину достоверности аппроксимации R²). Щелкнуть на кнопкеОК.

Рис. 1. Вид таблицы данных и линейного тренда

2. Для числовых значений диапазона ячеекB2:B11 вычислить среднее значение и результат поместить в ячейкуB12.

Действия:

· Установить курсор в ячейкуB12, активизироватьМастер функций,щелкнув на пиктограмме f_x.

· В диалоговом окне Мастер функций [шаг 1 из 2] выбрать Категория – Статистические, Функция –СРЗНАЧ. В полеЧисло окна функции СРЗНАЧ щелкнуть на кнопке с красной стрелкой, затем выделить диапазон ячеекB2:B11 и снова щелкнуть на кнопке с красной стрелкой в поле функции, затем щелкнуть на кнопке ОК. В ячейкеС16 появится среднее значение – 19,1.

3. Для числового ряда из диапазона ячеекB2:B11 аналогично вычислитьмаксимальное и минимальное значения

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

Очевидный ответ будет таким: как можно больше телевизоров и как можно больше магнитофонов. Реальные возможности магазина ограничены. В день можно продать не более 70 магнитофонов и не более 50 телевизоров. Значит нужно продавать именно это количество товара. Максимальная выручка составит:

500*70+2000*50=13500 руб.

Теперь перейдем к математической постановке задачи. Определим выручку В как линейную функцию двух переменных.

В=500*m+2000*t,

где переменные m и t обозначают количество магнитофонов и телевизоров.

Если в плоскости МТ для каждого значения m и t построим перпен­дикуляр с высотой, определяемой этой функцией, то получим плоскость Q, показанную на Рис. 1.

Рис.1

Оптимальна точка должна находиться в этой плоскости. Но плос­кость не имеет границ, и оптимальное решение найти нельзя. Вспомним о реальных возможностях магазина (70 магнитофонов м 50 телевизоров) и добавим, что количество проданных телевизоров и магнитофонов не мо­жет быть отрицательным. Этими 4-мя условиями (неравенствами) и опре­деляются ограничения, которые в плоскости МТ образуют заштрихован­ный прямоугольник. Значит, оптимальная выручка на приведенном рисунке определяется точкой на плоскости Q над этим прямоугольником. Теперь достаточно "пройти" по точкам вдоль проекции прямоугольника на плос­кость, которая показана на рисунке жирной линией, и найти самую высо­кую точку. Она и будет оптимальной.

Рассмотренный пример относится к области линейного программи­рования. Большое количество экономических задач сводятся к линейному программированию. Задачи линейного программирования можно решать разными методами, например графическим или симплекс-методом, а мож­но использовать прикладные программы.

Далее на примерах рассматривается использование для этой цели табличного процессора Ехсеl, вместе с его инструментальным средством Solver (Решатель). Первые 4 примера рассмотрены детально. Приводится подробная информация о средствах интерфейса и параметрах. Остальные 3 примера следует решить самостоятельно.