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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

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

- здесь установить: Прогноз вперёд на 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. Построить на диаграмме совмещенные графики объёма продаж теоретического и фактического.

Указанные действия выполнить для различных видов математи­ческих моделей, для чего при построении линии тренда следует использовать следующие функции: линейную (Y= A*X+B), степенную (Y=A*X^B), экспонен­циальную (Y=A*EXP(B*X). Определить, какая математическая модель даёт минималь­ную погрешность решения.

 

Результаты проведенного моделирования при помощи трёх раз­личных моделей представить на трёх листах рабочей книги EXCEL к файле «Моделирование», помещённом в личной папке студента. Каждому листу рабочей книги присвоить название.

 

12. В соответствии с выбранным преподавателем вариантом из нижней таблицы проделать самостоятельную работу.