регистрация / вход

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

ОТКРЫТЬ САМ ДОКУМЕНТ В НОВОМ ОКНЕ

ДОБАВИТЬ КОММЕНТАРИЙ [можно без регистрации]

Ваше имя:

Комментарий