Розробка автоматизованої інформаційної системи засобами табличного процесора 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 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. Срока, Н. В. Еремина н др. — К.: Техніка; Катовице: Экономическая академия им. Карола Адамецкого, 1 991 . —216с. 4. Ларичев О. Й . Наука и искусство принятия решений . — М.: Наука, 1979.— 200 с. 5. Лескин А. А., Ма. льцев В. Н. Системы поддержки управленческих и проектных решений. — Л.: Машиностроение. Ленингр. отд., 1990. — 167 с. 6. Нечеткие множества в моделях управлени я и искусственного интеллекта /Под ред. Д. А. Поспелова. —М.: Наука, 1986, 7. Си тник В. Ф. та ін. Си стеми підтримки прийняття рі шень. — К.: Техні к а,2005.—162с. 9. Макаров Й . М. н др. Теория выбора и принятия решений., — М.: Наука, 1982.—328 с. 10. Эддоус М. Стэнсфилд Г. Метод ы принятия решений: Пер. с англ. — М.: Аудит, ЮНИИТИ,