Использование электронных таблиц для моделирования и прогнозирования экономических процессов

ИСПОЛЬЗОВАНИЕ ЭЛЕКТРОННЫХ ТАБЛИЦ ДЛЯ МОДЕЛИРОВАНИЯ И ПРОГНОЗИРОВАНИЯ ЭКОНОМИЧЕСКИХ ПРОЦЕССОВ

Задание.На основании статистических данных по объемам продаж сделать прогноз объёма продаж товаров для торгового пред­приятия на следующий месяц.

Методика выполнения задания

1.   Подготовьте исходные данные в табличной форме следующего вида

	A	B	C	D	E	F	G
1	Коэффициент а	Коэффициент в	Временной период (шаг)	Месяц	Объем продаж товаров факт, тыс. руб.	Объем продаж товаров теор., тыс. руб..	Отклонение, тыс. руб.
2			1	Январь	29,7
3			2	Февраль	28,4
4	\ —— ——		3	Март	30,90
5			4	Апрель	32,1
6			5	Май	30,80
7			6	Июнь	33,00
8			7	Июль	34,40
9			8	Август	31,40
10			9	Сентябрь	32,50
11			10	Октябрь	32,20
12			11	Ноябрь	34,70
13			12	Декабрь
14	Максимальная погрешность численного моделирования объёма продаж товаров, тыс. руб.
15	Максимальный объём продаж товаров факт, тыс. руб.
16	Минимальный объём продаж товаров, тыс. руб. ...

2. По данным таблицы построить график с маркерами типа «График X-У», где X - месяц, У - объём продаж товаров:

-  выделить диапазон D1:E12;

-  вызвать Мастер диаграмм;

-  назначить тип диаграммы – График, вид – График с маркерами, помечающими точки данных;

-  нажать кнопку Готово.

4.  Для аппроксимации полученного графика построить линию линейного тренда:

-  активизировать график одним щелчком мыши;

-  через опции системного меню Диаграмма – Добавить линию тренда  выбрать на закладке Тип окна Линия тренда тип линии тренда Линейная;

-  в окне Линия тренда перейти на закладку Параметры;

-  здесь установить: Прогноз вперёд на 1 периодов;

-  Показать уравнение на диаграмме;

-  Поместить на диаграмму величину достоверности аппроксимации (R²);

-  Нажать кнопку ОК.

4.    Переместить на графике уравнение линии тренда с поля  графика на  свободное место. Значения коэффициентов А и В из уравнения занести в таблицу..

5.   Вычислить значение теоретического объёма продаж товаров   по формуле, показанной на линии тренда:  =$A$2*C2 + $B$2 . Скопировать его в диапазонF2:F12.

6.   Вычислить  абсолютное значение отклонения  теоретического  и фактического объёма продаж товаров в столбце «Отклонение»:  = ABS(E2-F2). Скопировать его далее.

7.   Определить максимальную погрешность в столбце «Отклонение» при помощи функции МАКС Мастера функций (клетка G14).

8.    При помощи Мастера функций найти максимальный и минималь­ный объёмы продаж товаров.

9. 

Произвести минимизацию величины погрешности, используя сервисное средство «Поиск решения» (кнопка системного меню – Сервис). При этом в качестве целевой ячейки надо выбрать ту, в которой находится величина погрешности (G14). Изменять следует значение коэффициентов aи b( ячейки A2:В2).

10. Сделать прогноз  объёма продаж на декабрь, скопировав формулу из предыдущей строки.

11. Построить на диаграмме совмещенные графики объёма продаж теоретического и фактического.