Моделирование экономического процесса

 

1. Запустите MS EXCEL

2. Создайте таблицу по образцу (Рис. 21).

3. По исходным данным постройте график для визуального отображения объема продаж. Для этого:

· выделите диапазон ячеек D1: E12;

· выполните команду Вставка - Диаграмма;

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

· нажмите кнопку Далее;

· в следующем окне будет приведен вид получаемого графика, ничего не меняя, нажмите кнопку Далее;

· в следующем окне введите название диаграммы и название осей:

- название: Объем продаж;

- ось Х: Месяцы;

- ось Y: Тыс. руб.;

· нажмите кнопку Далее;

 

 

Рис. 21. Исходная таблица в MS EXCEL

 

4. в последнем окне нажмите кнопку Готово, и у вас получится график (Рис. 22).

 

Рис. 22. Построение графика

 

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

· выделите линию графика одним щелчком мыши;

· выполните команду Диаграмма – Добавить линию тренда;

· в открывшемся окне выберите закладку Тип;

· выделите тип линии тренда - Линейная;

· откройте закладку Параметры;

· установите следующие параметры:

- прогноз вперёд на 1 периодов;

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

· нажмите кнопку ОК.

 

В результате на графике появится уравнение линии тренда. Это уравнение и будет являться основой для математической модели. Переместите на графике уравнение линии тренда с поля графика на свободное место. У вас должен получиться подобный график (Рис. 23).

 

Рис. 23. Построение линии тренда

 

6. Общий вид уравнения для линейной модели y=ax+b. Сравнив полученное уравнение на диаграмме с данным мы можем определить коэффициенты А и В. Коэффициент А=0, 4291 и В=29, 253. В данной формуле в качестве значений Х используются значения временного периода, т.е. значения столбца С.

Внесите значения коэффициентов А и В в ячейки таблицы А2 и В2 соответственно.

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

8. Скопируйте формулу из ячейки F2 в диапазон F2: F12.

9. Вычислите абсолютное значение отклонения теоретического и фактического объёма продаж товаров в столбце «Отклонение», т.е. найдите модуль отклонения. Для этого выполните команду Вставка – Функция.

10. В открывшемся окне выберите категорию – Математические.

11. Найдите и выделите функцию ABS. После этого нажмите ОК.

12. В новом окне введите необходимое действие E2-F2 (Рис. 24).

 

 

Рис. 24. Окно функции ABS

 

13. Нажмите кнопку ОК. Полученную формулу скопируйте в остальные ячейки (до ячейки F12).

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

15. Сделайте прогноз объёма продаж на декабрь. Для этого скопируйте формулу из ячейки F12 в ячейку F13.

В результате у вас должна получиться таблица следующего вида (Рис. 25).

 

Рис. 25. Таблица с выполненными расчетами