Розробка автоматизованої інформаційної системи засобами табличного процесора EXCEL
1
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 | 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 | 70 | х | х | х | х | |
|
З таблиці 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 с.
10. Эддоус М. Стэнсфилд Г. Методы принятия решений: Пер. с англ. -- М.: Аудит, ЮНИИТИ, 1997. - -590 с.