Лабораторный практикум №1

Прогнозирование экономических показателей средствами MS Excel

Лабораторный практикум №1

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

Для прогнозирования в Ms Excel могут использоваться следующие функции:

- ПРЕДСКАЗ – прогнозирование значений по линейной зависимости;

- ТЕНДЕНЦИЯ – прогнозирование значений по линейной зависимости (аналогична функции ПРЕДСКАЗ);

- РОСТ – прогнозирование экспоненциальной зависимости;

- ЛИНЕЙН – построение линейного приближения;

- ЛГРФПРИБЛ – построение экспоненциального приближения.

Задачей данной работы является

1) применение функции ПРЕДСКАЗ для прогноза данных представленных в таблице №2,

2) построение точечной диаграммы и линии тренда по одному из показателей – объему реализованных товаров,

3) нахождение коэффициентов линейного уравнения и величины достоверности приближения (аппроксимации).

Функция ПРЕДСКАЗ вычисляет или предсказывает будущее значение по существующим значениям. Предсказываемое значение — это y-значение, соответствующее заданному x-значению. Известен набор существующих x- и y-значений; новое значение предсказывается с использованием линейной функции. Функцией ПРЕДСКАЗ можно воспользоваться для прогнозирования будущих продаж, потребностей в оборудовании или тенденций потребления.

Алгоритм выполнения:

a. Для прогнозирования данных, создайте электронную таблицу №2.1 по данным таблицы, представленной ниже. При построении соответствующей таблицы в Excel замените текстовые выражения месяцев (янв.-дек.) числовыми (1-12). (см. рис. 9)

Маркетинговые данные по цене реализации, цене приобретения и объему реализованных товаров за 2009 год.

№	Показатели	янв.	фев.	март	апр.	май	июнь	июль	авг.	сент.	окт.	нояб.	дек.
	Объем реал. товаров (шт.)
Цена реализации 1 шт. брутто (тыс. руб.)	18,47	18,53	18,62	18,73	19,43	19,44	19,44	19,49	19,54	19,54	19,60	19,72
Цена приобретения 1 шт. брутто тыс.руб.	15,72	15,82	16,14	16,19	16,23	16,23	16,31	16,32	16,32	16,36	16,40	16,44
	Объем реал. товаров (шт.)
Цена реализации 1 шт. брутто (тыс. руб.)	18,24	18,30	18,38	18,50	19,18	19,19	19,20	19,25	19,29	19,29	19,36	19,48
Цена приобретения 1 шт. брутто (тыс.руб.)	15,53	15,63	15,94	16,00	16,04	16,04	16,11	16,12	16,12	16,16	16,20	16,24
	Объем реал. товаров (шт.)
Цена реализации 1 шт. брутто (тыс. руб.)	18,39	18,45	18,54	18,65	19,34	19,35	19,36	19,40	19,45	19,45	19,52	19,64
Цена приобретения 1 шт. брутто (тыс.руб.)	16,43	16,53	16,87	16,92	16,97	16,97	17,04	17,05	17,05	17,10	17,14	17,18
	Объем реал. товаров (шт.)
Цена реализации 1 шт. брутто (тыс. руб.)	18,53	18,59	18,68	18,79	19,49	19,50	19,50	19,55	19,60	19,60	19,67	19,79
Цена приобретения 1 шт. брутто (тыс.руб.)	17,31	17,42	17,77	17,83	17,88	17,88	17,96	17,97	17,97	18,01	18,06	18,10

b. Создайте таблицу 2.2 «Прогноз на 2010 г.», оставив аналогичные показатели в первом и втором столбце и изменив числовое выражение месяцев на (13-24) (рис.9).

Рис. 9.

c. Для определения прогнозных значений необходимо рассмотреть аргументы функции ПРЕДСКАЗ:

- х – элемент данных, для которого предсказывается значение. В данном случае это будет месяц 2010 года;

- известные значения у – зависимый диапазон или массив данных (объем реализованных товаров, цена реализации или цена приобретения, в зависимости от того значение какого показателя прогнозируется);

- известные значения х – независимый диапазон или массив данных (месяцы 2009 года).

Для прогнозирования объема реализованного товара №1 во второй ячейки третьего столбца таблицы 2.2 (рис. 10) запишите формулу: =ПРЕДСКАЗ ( показатель месяца из таблицы 2.2; диапазон значений (с янв. по дек.) объема реализованных товаров из таблицы 2.1; диапазон значений месяцев из таблицы 2.1 ). При этом необходимо учесть, что при тиражировании формулы по горизонтали диапазоны (известные значения x и у) не должны смещаться в отличие от показателя месяца 2010 года, т.е. ссылки в формуле на диапазоны объема реализованных товаров и значений месяцев 2009 года необходимо сделать абсолютными. Для этого диапазоны x и y нужно окружить знаком «$», нажав на них клавишу F4. Таким образом, в ячейке С18 должна быть записана формула: =ПРЕДСКАЗ(C17;$C2:$N2;$C$1:$N$1). (Рис. 10).

Рис. 10.

Теперь растиражируйте эту формулу для остальных месяцев 2010 года (ячейки D18:N18).

Далее рассчитайте остальные показатели по товарам аналогично выше приведенному примеру расчета. При этом округлите значения объема реализованных товаров до целых значений, а цену приобретения (реализации) до двух знаков после запятой.

d. Постройте точечную диаграмму (рис. 12) с прямыми отрезками и маркерами по каждому товару: вкладка Вставка группа Диаграммы, используйте данные объема реализованных товаров по оси у и месяца по оси х из таблицы №2.1.

Рис. 12.

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

Рис. 13.

В MS Excel возможны следующие варианты функций (рис. 14):

Рис. 14.

- линейная у=aх+b, приминяется в простейших случаях, когда эксперементальные данные возростают или убывают с постоянной скоростью;

- полиномиальная у=а₀+а₁х+а₂х²+…+а_nхⁿ где до шестого порядка включительно (n≤6), a_i– константы. Используется для описания экспериментальных данных, попеременно возрастающих и убывающих. Степень полинома определяется количеством экстремумов (максимумов или минимумов) кривой. Полином второй степени можно описать только один максимум или минимум, полином третьей степени может иметь один или два экстремума, четвертой степени – не более трех экстремумов и т.д;

- логарифмическая – y=a·lnx+b, где a и b – константы, ln – функция натурального логарифма. Функция применяется для описания экспериментальных данных, которые вначале быстро растут или убывают, а затем постепенно стабилизируются;

- cтепенная – y=b·x^a, где a и b – константы. Аппроксимация степенной функцией используется для экспериментальных данных с постоянно увеличивающейся (или убывающей) скоростью роста. Данные не должны иметь нулевых или отрицательных значений;

- экспоненциальная – y=b·e_ax, a и b – константы, e – основание натурального логарифма. Применяется для описания экспериментальных данных, которые быстро растут или убывают, а затем постепенно стабилизируются. Часто ее использование вытекает из теоретических соображений.

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

Коэффициент детерминации (аппроксимации) R² (равен квадрату коэффициента корреляции r²) показывает, насколько изменения зависимого признака объясняются изменениями независимого (рис.15). Доля изменчивости у, определяемая выражением 1-R², оказывается необъясненной. Если две переменные функционально линейно зависимы (коэффициент детерминации равен 100%), то можно сказать, что изменение одной из них полностью объясняется изменением другой.

Рис.15.

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