Розробка автоматизованої інформаційної системи засобами табличного процесора EXCEL
1. Практичне завдання
Засобами табличного процесора EXCEL розробити автоматизовану інформаційну систему, що дозволяє визначити
найкращу стратегію покупки (х*) розглянутими в роботі методами при відомих і невідомих
можливостях ринкової кон'юнктури для розглянутого приклада.
Макет таблиці 1 „Анкета експертного опитування”, що
розроблена в EXCEL наведена нижче:
|
Анкета
експертного опитування
|
|
|
|
|
|
|
|
|
|
Таблиця 1
|
|
Базова таблиця
|
|
|
|
|
|
|
|
|
|
|
Експерт
|
Оцінки
значимості критеріїв експертами
|
Сума
|
|
f1 (якість)
|
f2 (ціна)
|
f3 (прибуток)
|
|
|
1
|
1
|
1
|
1
|
3
|
|
2
|
1
|
1
|
1
|
3
|
|
3
|
1
|
1
|
1
|
3
|
|
4
|
1
|
1
|
1
|
3
|
|
коеф. ваги αi
|
1,00
|
1,00
|
1,00
|
|
|
станд. відхил.
|
0,00
|
0,00
|
0,00
|
|
|
дисперсія
|
0,00
|
0,00
|
0,00
|
|
|
коеф. варіації
|
0,00
|
0,00
|
0,00
|
|
Так як діапазон, що виділений
у сірий колір являє собою відношення оцінки відповідного експерту до суми всіх
3-х оцінок відповідного ж експерту, то доповнимо (розширимо) таблицю 1, як це
зображено на рис.1 на деякому прикладі.
Рис.1
Тобто, саме оцінки експертів
за 10-ти бальною шкалою проставлюються у діапазоні F8:H11.
Макет таблиці 1 „Анкета експертного опитування”, що
розроблена в EXCEL в режимі формул наведена
нижче (рис.2):
Рис.2.
Макет таблиці 2 „Техніко-економічні показники
проектів”, що
розроблена в EXCEL наведена нижче:
|
|
|
Таблиця 2
|
|
Техніко-економічні
показники проектів
|
|
Номера
проектів та його характеристики
|
Критерії
оцінки проектів
|
|
f1 (якість)
|
f2 (ціна)
|
f3 (прибуток)
|
|
Проект №1
|
|
|
|
|
Проект №2
|
|
|
|
|
Відносний
коефіцієнт значимості критеріїв (αi) – з таблиці 1
|
0,00
|
0,00
|
0,00
|
|
Мінімально
припустиме значення критерію
|
0
|
0
|
|
Максимально
припустиме значення критерію
|
0
|
0
|
0
|
Макет таблиці 2 „Техніко-економічні показники
проектів”, що
розроблена в EXCEL в режимі формул наведена
нижче:
|
|
|
Таблиця 2
|
|
Техніко-економічні
показники проектів
|
|
Номера
проектів та його характеристики
|
Критерії
оцінки проектів
|
|
f1 (якість)
|
f2 (ціна)
|
f3 (прибуток)
|
|
Проект №1
|
|
|
|
|
Проект №2
|
|
|
|
|
Відносний
коефіцієнт значимості критеріїв (αi) – з таблиці 1
|
=B12
|
=C12
|
=D12
|
|
Мінімально
припустиме значення критерію
|
=МИН(B21:B22)
|
=МИН(C21:C22)
|
=МИН(D21:D22)
|
|
Максимально
припустиме значення критерію
|
=МАКС(B21:B22)
|
=МАКС(C21:C22)
|
=МАКС(D21:D22)
|
Але також доповнюємо макет
таблиці 2, для більш детального розрахунку загальних ваг проектів за усіма
крітеріями (рис.3).
Рис.3.
Макет таблиці 3 „Техніко-економічні показники
проектів”, що
розроблена в EXCEL наведена нижче:
|
|
|
Таблиця 3
|
|
Матриця
прибутків
|
|
Перелік
проектів та їх характеристика
|
Варіанти
ринкової кон’юнктури
|
|
Низький рівень
попиту
|
Середній
рівень попиту
|
Високий рівень
попиту
|
|
Проект 1
|
Значення
прибутку по 1 проекту в умовах низької кон’юнктури ринку
|
Значення
прибутку по 1 проекту в умовах середньої кон’юнктури ринку
|
Значення
прибутку по 1 проекту в умовах високій кон’юнктури ринку
|
|
Проект 2
|
Значення
прибутку по 2 проекту в умовах низької кон’юнктури ринку
|
Значення
прибутку по 2 проекту в умовах середньої кон’юнктури ринку
|
Значення
прибутку по 2 проекту в умовах високій кон’юнктури ринку
|
|
Вірогідністьь
настання i-ого варіанту кон’юнктури ринку
|
0,6
|
0,8
|
0,4
|
|
α*
|
0,28
|
* згідно варіанту № 19.
Але також доповнюємо макет
таблиці 3, для більш детального розрахунку загальних ваг проектів за усіма
крітеріями прибутків (рис.4).
Рис.4.
2. Індивідуальне завдання на контрольну
роботу з дисципліни “Методи
і моделі прийняття рішень в аналізі і аудиті”
Мета: Провести контрольні
розрахунки для рішення задач добору кращого варіанта інвестиційного проекту
(ІП).
Склад задач:
1. Проведемо розрахунки для отримання
значень відносного коефіцієнту значимості критеріїв оцінки проектів (αi) за допомогою методу Дельфи. Приклад анкети експертного
опитування наведений у таблиці 1.
Таблиця 1 – Анкета експертного опитування.
|
№ експерта
|
Оцінки
значимості критеріїв експертами
|
|
f1
|
f2
|
…
|
fi
|
|
1
|
|
|
|
|
|
2
|
|
|
|
|
|
n
|
|
|
|
|
|
αi –
показник, який необхідно розрахувати
|
|
|
|
|
Кількість експертів - 3. Оцінювати проекти будемо за десятибальною
шкалою. Вихідні дані таблиці 1 отримаємо за результатами власного експертного
опитування за крітеріями: якість, ціна, прибуток (діапазон F8:H11) реалізації
деякого товару, наприклад, монтажного інструменту (рис.5).
|
Безпосередній
розрахунок коефіціентів ваги αi
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Таблиця 1
|
|
|
|
|
Базова таблиця
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Експерт
|
Оцінки
значимості критеріїв експертами
|
Сума
|
Оцінки
значимості критеріїв експертами
|
Сума
|
|
f1
(якість)
|
f2 (ціна)
|
f3 (прибуток)
|
|
f1
(якість)
|
f2 (ціна)
|
f3 (прибуток)
|
|
|
1
|
0,32
|
0,41
|
0,27
|
1
|
7
|
9
|
6
|
22
|
|
2
|
0,32
|
0,42
|
0,26
|
1
|
6
|
8
|
5
|
19
|
|
3
|
0,33
|
0,38
|
0,29
|
1
|
8
|
9
|
7
|
24
|
|
4
|
0,31
|
0,44
|
0,25
|
1
|
5
|
7
|
4
|
16
|
|
коеф. ваги
αi
|
0,32
|
0,41
|
0,27
|
х
|
х
|
х
|
х
|
х
|
|
станд. відхил.
|
0,01
|
0,03
|
0,02
|
х
|
х
|
х
|
х
|
|
дисперсія
|
0,00009
|
0,00070
|
0,00031
|
х
|
х
|
х
|
х
|
х
|
|
коеф. варіації
|
0,03
|
0,06
|
0,07
|
х
|
х
|
х
|
х
|
х
|
Як бачимо з
розрахунків таблиці 1, найбільше вагомий крітерій – ціна αi = 0,41, найменше вагомий крітерій –
прибуток αi = 0,27.
Отримані значення
αi далі використовуємо при рішенні другої
задачі завдання, та занесемо їх у відповідний рядок B23:D23 таблиці 2.
2. Проведемо розрахунки оцінки техніко-економічних
показників (ТЕП) проектів за декількома критеріями.
Для оцінки проектів будемо використовувати методи багатокритеріального
аналізу. Значення критеріїв оцінки ТЕП проектів було визначино у таблиці 1.
Розрахунки оцінки техніко-економічних показників (ТЕП) проектів за
декількома критеріями приведено на наступному фрагменті:
|
|
|
Таблиця 2
|
|
|
|
|
|
Техніко-економічні
показники проектів
|
|
|
|
|
|
Номера
проектів та його характеристики
|
Критерії
оцінки проектів
|
Складальні
критеріїв
|
Загальний
крітерій
|
|
f1
(якість)
|
f2 (ціна)
|
f3 (прибуток)
|
f1
(якість)
|
f2 (ціна)
|
f3
(прибуток)
|
|
Проект №1
|
60
|
8
|
50
|
0,23996
|
0,410660885
|
0,19241997
|
0,458204282
|
|
Проект №2
|
80
|
9
|
70
|
0,31995
|
0,461993496
|
0,269387959
|
0,512556694
|
|
Відносний
коефіцієнт значимості критеріїв (αi)
|
0,32
|
0,41
|
0,27
|
х
|
х
|
х
|
х
|
|
Мінімально
припустиме значення критерію
|
60
|
8
|
50
|
х
|
х
|
х
|
х
|
|
Максимально
припустиме значення критерію
|
80
|
9
|
х
|
х
|
х
|
х
|
З таблиці 2 бачимо, що найбільше значимим крітеріїм експерти визнали Проект
2 (коефіцієнт загального крітерію 0,513), найменше значимим крітеріїм експерти
визнали Проект 2 (коефіцієнт загального крітерію 0,458).
Значення кількості проектів (2) і кількості показників (3) взято згідно
нашого варіанту 19.
3. Проведемо
розрахунки, що необхідні для добору кращого варіанту проекту за допомогою матриці
прибутків в залежності від обсягів реалізації продукції з використанням методів
прийняття рішень в умовах ризику і невизначеності.
Приклад оформлення матриці прибутків наведений у таблиці 3.
Таблиця 3 – Матриця прибутків (витрат)
|
Перелік
проектів та їх характеристика
|
Варіанти
ринкової кон’юнктури
|
|
Низький рівень попиту
|
Середній
рівень попиту
|
Високий
рівень попиту
|
|
Проект 1
|
Значення
прибутку (витрат) по 1 проекту в умовах низької кон’юнктури ринку
|
|
|
|
Проект 2
|
|
|
|
|
...
|
|
|
|
|
Проект n
|
|
|
|
|
Вірогідність настання i-ого варіанту кон’юнктури ринку
|
|
|
|
|
α
|
|
Значення α залежить від варіанта 19, тобто 0,28.
В таблиці 3
середній рівень попиту за проектами відповідає значенням прибутків за проектами
з таблиці 2, низький рівень попиту за проектами відповідає значенням прибутків
за проектами, зменшеними на 15%, високий рівень попиту за проектами відповідає
значенням прибутків за проектами збільшеними на 15%. Вірогідність настання i-ого варіанту кон’юнктури ринку
визначена емпірічним шляхом в результаті маркетингових досліджень (0,6; 0,8;
0,4 відповідно).
|
|
|
Таблиця 3
|
|
Матриця
прибутків
|
|
Перелік
проектів та їх характеристика
|
Варіанти
ринкової кон’юнктури
|
|
Низький рівень
попиту
|
Середній
рівень попиту
|
Високий рівень
попиту
|
|
Проект 1
|
42,5
|
50
|
57,5
|
|
Проект 2
|
59,5
|
70
|
80,5
|
|
Вірогідність
настання i-ого варіанту кон’юнктури ринку
|
0,6
|
0,8
|
0,4
|
|
α
|
0,28
|
|
Загальний
крітерій 1
|
7,14
|
11,2
|
6,44
|
|
Загальний
крітерій 2
|
9,996
|
15,68
|
9,016
|
Як бачимо з
розрахунків таблиці 3 найбілше вірогідний середній рівень попиту за проектами,
на другому місті низький рівень попиту, на третьому - високий рівень попиту за
прибутковістю. Але кращим все одне є проект 2 - більше вагома сума загальних
крітеріїв.
4. Отримаємо узагальнену оцінку проектів
за допомогою даних, отриманих за результатами проведених розрахунків.
Таким чином, як
бачимо з розрахунків таблиці 1, найбільше вагомий крітерій – ціна виробу αi = 0,41, найменше вагомий крітерій –
прибуток від виробу αi = 0,27.
З таблиці 2 бачимо, що
найбільше значимим крітеріїм експерти визнали Проект 2 (коефіцієнт загального
крітерію 0,513), найменше значимим крітеріїм експерти визнали Проект 2
(коефіцієнт загального крітерію 0,458).
Та, як бачимо з
розрахунків таблиці 3 найбілше вірогідний середній рівень попиту за проектами,
на другому місті низький рівень попиту, на третьому - високий рівень попиту за
прибутковістю. Але кращим все одне є проект 2 - більше вагома сума загальних
крітеріїв.
Висновки
Таким чином, з
розрахунків таблиці 1 можна визначити, що найбільше вагомий крітерій – ціна
виробу αi = 0,41, найменше вагомий крітерій –
прибуток від виробу αi = 0,27.
З розрахунків
таблиці 2 можна визначити, що найбільше значимим крітеріїм експерти визнали
Проект 2 (коефіцієнт загального крітерію 0,513), найменше значимим крітеріїм
експерти визнали Проект 2 (коефіцієнт загального крітерію 0,458).
Та, з розрахунків
таблиці 3 можна визначити, що найбільше вірогідний середній рівень попиту за
проектами, на другому місті низький рівень попиту, на третьому - високий рівень
попиту за прибутковістю. Але кращим все одне є проект 2 - більше вагома сума
загальних крітеріїв.
Література
1. Евланов Л. Г. Теория и
практика принятия решений. — М.: Экономика,
1984. — 176 с.
2. Кини
Р. Л., Райфа X. Принятие решений при многих
критериях: предпочтения и замещения.
— М.: Радио и связь,
1981.
3. Компьютеризация информационных
процессов на промышленных
предприятиях / В. Ф. Сьітник, X. Срока, Н. В. Еремина н др. — К.: Техніка; Катовице: Экономическая академия им. Карола Адамецкого, 1991. —216с.
4. Ларичев О. Й. Наука и искусство принятия
решений. — М.: Наука, 1979.—200
с.
5. Лескин А. А., Ма.льцев В. Н. Системы поддержки
управленческих и проектных
решений. — Л.: Машиностроение.
Ленингр. отд., 1990. —
167 с.
6. Нечеткие множества в
моделях управления и искусственного интеллекта /Под ред. Д. А. Поспелова.
—М.: Наука, 1986,
7. Ситник В. Ф. та ін. Системи підтримки прийняття рішень. — К.: Техніка,2005.—162с.
9. Макаров
Й. М. н др. Теория выбора и принятия решений.,— М.: Наука, 1982.—328 с.