Роздрукувати сторінку
Главная \ Методичні вказівки \ Методичні вказівки \ 932 Практична робота №5 на тему Розробка імітаційних моделей прийняття рішень у середовищі MS Excel із використанням надбудови Пакет аналізу, НУДПСУ, Національний університет державної податкової служби України

Практична робота №5 на тему Розробка імітаційних моделей прийняття рішень у середовищі MS Excel із використанням надбудови Пакет аналізу, НУДПСУ, Національний університет державної податкової служби України

« Назад

ПРАКТИЧНА РОБОТА №5
Тема: "Розробка імітаційних  моделей прийняття рішень у середовищі MS Excel із використанням надбудови Пакет аналізу”

Мета: набути практичних навичок розробки імітаційних моделей прийняття рішень у середовищі MS Excel із застосування надбудови Пакет аналізу.

ХІД РОБОТИ

Час виконання роботи – 2 години

Теоретичні відомості

ІМІТАЦІЙНЕ МОДЕЛЮВАННЯ

Основна ідея імітації – створити експериментальний пристрій, імітатор, який в основних рисах повторює (імітує) поведінку реальної системи, причому швидко і економно. Імітаційне ( комп’ютерне) моделювання сьогодні вважається одним з найдосконаліших методів кількісного аналізу багатьох економічних процесів. Імітація – це комп’ютерний експеримент, але не з реальною системою, а з її моделлю.

Імітаційне (комп’ютерне) моделювання економічних процесів найчастіше застосовують у двох випадках:

-  для управління складним бізнес – процесом, коли імітаційне модель керованого економічного об’єкта використовується в якості інструментального засобу у контурі адаптивної системи управління,  створеної на основі інформаційних (комп’ютерних технологій);

- при проведенні експериментів з дискретно – неперервними моделями складних економічних об’єктів для відслідковування динаміки в екстренних ситуаціях, пов’язаних з ризиками, натурне моделювання яких неможливе чи небажане.

Невеликі імітаційні моделі можна розробляти у табличному процесорі МS Excel. Для моделювання випадкових величин при проведенні імітаційного експерименту в цьому процесорі існують дві можливості :

- застосування вбудованих функцій СЛЧИС() або СЛУЧМЕЖДУ(),

- застосування  засобу Анализ данных=> Генерация случайных чисел, яке викликається з меню Сервис вікна програмиМS Excel,

за умови, що активізована надбудова МS Excel Пакет анализа.

Функція СЛЧИС() не має аргументіві повертає рівномірно розподілене  випадкове дійсне число, яке належить відрізку [0;1]. Шляхом нескладних математичних формул за допомогою цієї функції можна отримати формулу  МS Excel, яка буде обчислювати випадкове рівномірно розподілене дійсне число відрізку  [a;b], а саме:

=a + (ba)*СЛЧИС().

Функція СЛУЧМЕЖДУ(нижн_граница; верхн_граница) дозволяє обчислювати випадкове число із заданого в аргументах функції відрізку. Тип випадкового числа залежить від заданого типу аргументів (дійсне чи ціле).

Засіб Генерация случайных чиселвикористовують для автоматичної генерації множини даних заданого розміру, елементи якої характеризуються певним розподілом ймовірності з семи можливих: рівномірний, нормальний, Бернуллі, Пуассона, біноміальний, модельний та дискретний.

Для аналізу результатів імітаційного експерименту у багатьох випадках зручно застосовувати засоби надбудови Пакет аналізу Описательная статистика та Гистограмма.

У даній практичній роботі розглянемо побудову імітаційної моделі на прикладі ситуаційної задачі.

Задача. Деяке підприємство планує розпочати випуск нової моделі робототехнічного комплексу, яку планується продавати по ціні 32 000  0000 грн за одну одиницю. Початковий капітал становить 100 000 000 грн. Постійні витрати оцінюються 15 000 0000 грн. на рік. Змінні витрати становлять 73 % від доходу. Амортизаційні відрахування на нове обладнання становлять 10 000 000 грн. на рік. Залишкову вартість обладнання після 4 років експлуатації  вважають рівною нулю. Вартість капіталу для даного підприємства становить 10%, а податкова ставка – 32%. Попит на робототехнічний комплекс є випадковою величиною, яка на підставі проведених досліджень може набувати таких значень: 8, 9, 10, 11, 12 або 13 одиниць на рік. Потрібно розрахувати очікуване значення ЧПВ (чистої приведеної вартості) і ймовірність того, що ЧПВ не набуде від’ємного значення.

Завдання 1. Розробіть  модель фінансового планування в умовах випадкового попиту.

Створіть документ  Excel  з ім’ям Практ_5.xlsу своїй папці на сервері.    

1.1. Відкрийте документ Практ_5.xlsі створіть робочий аркуш Модель фінансового планування 2.

1.2. На вказаному робочому аркуші введіть початкові та розрахункові дані у відповідності до наведених на Рис. 5.1. Початкові дані задані у тис. грн.
Зверніть увагу на те, як моделюється попит: попит обчислюється за формулою, яка містить функцію СЛЧИСЛ() і математичну функцію ЦЕЛОЕ: =ЦЕЛОЕ(8+5*СЛЧИС()).

При обчисленні ЧПС прочитайте довідку про цю функцію.

1.3. Результати обчислень показано на Рис. 5.2.

Завдання 2. Проведення імітаційних експериментів.

2.1. У документі Практ_5.xls створіть робочий аркуш Імітація.

2.2.  Введіть  у комірку А1  текст Имітація №, а у комірки А2:А101 значення  від 1 до 100. Використайте засіб Прогресия.

2.3. У комірку В1 введіть текст ЧПВ.

2.4. У комірку В2 введіть формулу =’Модель фінансового планування 2’!$B$19.

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

2.5. Виділіть діапазон комірок А2:В101.

2.6. Виконайте команду Данные ® Таблица подстановки.

2.7. У діалоговому вікні Таблица подстановки введіть С1 у поле Подставлять значения по строкам (див. Рис. 5.3).

2.8. Щоб зафіксувати отримані значення ЧПС (щоб вони не змінювались при перерахунку робочого аркуша, оскільки попит є випадковою величиною),  виділіть діапазон комірок В2:В101, скопіюйте їх вміст у Буфер обміну, а потім виконайте команду Правка ® Специальная вставка ® Значения ®  ОК (Рис. 5.4).

Завдання 3. Провести статистичний аналіз отриманих значень ЧПС. Визначіти середнє значення ЧПВ.

3.1. Виконайте команду Сервис ® Анализ данных.

3.2. У списку Инструменты діалогового вікна Анализ данных (Рис. 5.5. ) виділіть Описательная статистика, потім клацніть на кнопці ОК.

3.3. У діалоговому вікні Описательная статистика встановіть перемикачі та введіть у текстові поля задані на Рис.  5.6 значення і клацніть на кноці ОК.

3.4. Порівняйте отримані у комірках F2:G19  результати статистичної обробки результатів іиітаційного експерименту з наведеними на Рис. 5.7. Яке середнє значення ЧПС? В яких межах може змінюватись ЧПС?

3.5. Побудуйте довірчий інтервал.

Завдання 4.  Знаходження розподілу ЧПС. Визначення ймовірності від’ємних значень  ЧПС та ймовірності найкращого значення ЧПС.

4.1. Отримаємо графічне представлення розподілу ймовірностей, функцію розподілу та таблицю частот, застосувавши  інструмент Гистограмма з пакету Анализ данных (див. Рис. 5.8).

4.2. У діалоговому вікні Гистограмма встановіть перемикачі та задайте значення у текстових полях відповідно до наведеного на Рис. 5.9.

4.3. Проаналізуйте отримані у комірках А1:С11 результати і порівняйте з наведеними на Рис.  5.10.

Скільки значень ЧПС мають від’ємні значення?

Яка ймовірність від’ємних значень?

В якому інтервалі знаходиться найбільше значень ЧПС?

Завдання 5. Захистіть виконану роботу.

Питання до захисту практичної роботи

  1. В чому полягає ідея імітації?

  2. В яких випадках застосовують імітаційне моделювання економічних процесів?

  3. Які інструменти існують в МS Excel для моделювання випадкових величин?

  4. Яка відмінність існує між функціями СЛЧИС та СЛУЧМЕЖДУ?

  5. В яких випадках використовують засіб Генерация случайных чиселнадбудови Пакет аналізу?

  6. Як обчислювався попит у моделі фінансового планування у даній практичній роботі?

  7. Яка вбудована функція MS Excel використовувалась для обчислення чистої приведеної вартості і який її синтаксис?

  8. Як організовано проведення і збереження результатів імітаційних експериментів у даній роботі?

  9. Чим зумовлені різні значення ЧПВ у різних прогонах імітаційної моделі?

  10.  Які результати статистичного аналізу результатів імітаційних експериментів можна отримати за допомогою засобу Описательная статистика?

  11. Які результати статистичного аналізу можна отримати при застосуванні засобу Гистограмма?

З повагою ІЦ “KURSOVIKS”!