Рекомендації з використання засобу аналізу даних MS Excel “Диспетчер сценаріїв”.

Диспетчер сценаріїв – це засіб Excel, що може бути використаний для підтримки прийняття рішень у складних ситуаціях, які потребують аналізу типу “що…, якщо?”. Даний засіб дозволяє створювати кілька варіантів розв’язку однієї задачі та допускає використання до 32-х параметрів.

Вцілому, сценарій - це набір значень, що у програмі Microsoft Office Excel зберігаються і можуть автоматично підставлятися у робочий аркуш. Сценарії можна використовувати для прогнозу результатів моделей, виконаних розрахунків на аркуші. Існує можливість створити і зберегти на одному аркуші різні групи значень, а потім перемикатися на будь-який з цих нових сценаріїв, щоб проглядати різні результати.

Створення сценарію:

1. Виберіть команду Сценарии в меню Сервис.

2. Натисніть кнопку Добавить.

3. Введіть в поле Название сценария назву сценарію.

4. У полі Изменяемые ячейки введіть посилання на комірки, які потрібно змінити.

Примітка. Для збереження початкових значень змінних комірок створіть сценарій, який використовує початкові значення комірок, до створення сценарію, що змінює значення.

5. Встановите необхідні прапорці в наборі прапорців Защита.

6. Натисніть кнопку OK.

7. Введіть необхідні значення в діалоговому вікні Значения ячеек сценария.

8. Щоб створити сценарій, натисніть кнопку OK.

9. Якщо потрібно створити додаткові сценарії, знову натисніть кнопку Добавить, а потім повторіть цю процедуру. Після завершення створення сценаріїв натисніть кнопку OK, а потім - кнопку Закрыть в діалоговому вікні Диспетчер сценариев.

Правка сценарію.

Якщо після внесення змін до сценарію він буде збережений з первинним ім'ям, нові значення змінних осередків замінять значення в початковому сценарії.

1. Виберіть команду Сценарии в меню Сервис.

2. Виберіть назву змінного сценарію, а потім натисніть кнопку Изменить.

3. Внесіть необхідні зміни.

4. Введіть необхідні значення в діалоговому вікні Значения ячеек сценария.

5. Виконайте одну з наступних дій:

§ щоб зберегти зміни, натисніть кнопку OK;

§ щоб повернутися в діалогове вікно Диспетчер сценариев без зміни поточного сценарію, натисніть кнопку Отмена.

Завдання 2. За допомогою програми MS Excel розрахувати індекс припливу в країну прямих іноземних інвестицій. Із використанням засобу MS Excel “Диспетчер сценаріїв” виконати розрахунок індексу за кількома сценаріями. Оформити результати у вигляді звіту на робочому аркуші MS Excel та вибрати оптимальний варіант.

Дано (млрд. у. о.):

§ Валовий внутрішній продукт країни – 15,71;

§ Валовий світовий продукт – 12556,69;

§ Обсяги інвестицій у країну – 0,48;

§ Обсяги інвестицій по всім країнам – 364,28.

Додатково виконати розрахунок ще за 5 сценаріями, крім тих, що наведені у прикладі розв’язання.

Умова завдання подана у таблиці 3. Варіант вибирається згідно номера студента по списку в журналі.

Таблиця 3.

Варіанти завдань

№ варіанту	Валовий внутрішній продукт країни	Валовий світовий продукт	Обсяги інвестицій у країну	Обсяги інвестицій по всім країнам
	26,8	37689,23	0,97	245,35
	27,3	37928,45	1,03	256,47
	27,8	38167,67	1,09	267,59
	28,3	38406,89	1,15	278,71
	28,8	38646,11	1,21	289,83
	29,3	38885,33	1,27	300,95
	29,8	39124,55	1,33	312,07
	30,3	39363,77	1,39	323,19
	30,8	39602,99	1,45	334,31
	31,3	39842,21	1,51	345,43
	31,8	40081,43	1,57	356,55
	32,3	40320,65	1,63	367,67
	32,8	40559,87	1,69	378,79
	33,3	40799,09	1,75	389,91
	33,8	41038,31	1,81	401,03
	34,3	41277,53	1,87	412,15
	34,8	41516,75	1,93	423,27
	35,3	41755,97	1,99	434,39
	35,8	41995,19	2,05	445,51
	36,3	42234,41	2,11	456,63

 

Приклад розв’язання. Індекс припливу в країну прямих іноземних інвестицій розраховується за такою формулою (1):

INV = (I_i / I_w) / (G_i / G_w) (1)

Де G – сукупний світовий продукт країни (і) та всього світу (w); I – приплив інвестицій у країну (і) та в усі країни світу (w). INV – індекс припливу в країну прямих іноземних інвестицій.

Даний показник характеризує відносні успіхи країни в залученні інвестицій, однак не вимірює фактори, які є причинами цього.

Якщо індекс припливу в країну інвестицій більше за 1, це означає, що вона отримує прямих іноземних інвестицій більше ніж можна було передбачати на базі порівняння вищезгаданих коефіцієнтів. Така країна має додаткові інші переваги для транснаціональних компаній (ТНК), наприклад, більш ліберальний порівняно з іншими країнами, режим для іноземних інвесторів.

В MS Excel потрібно створити таблицю наступного вигляду (рис. 5):

Рис 5. Таблиця для розрахунків в MS Excel.

У комірку С7 вводимо формулу (I_i / I_w) / (G_i / G_w). Далі виділяємо комірки С3:С6 і виконуємо команду команду Сценарии в меню Сервис. У вікні Диспетчер сценариев натискаємо кнопку Добавить. У вікні Добавление сценария в полі Название сценария вводимо назву сценарію, наприклад “ Сценарій1 ” та натискаємо кнопку ОК. Діалогове вікно Значения ячеек сценария заповняємо як показано на рис 6:

Рис. 6. Заповнення вікна Значения ячеек сценария (“ Сценарій1 ”).

Після натискання кнопки ОК повертаємося у вікно Диспетчер сценариев. Аналогічно створюємо “ Сценарій2 ” та “ Сценарій3 ” (рис. 7, рис. 8):

 

Рис. 7. Заповнення вікна Значения ячеек сценария (“ Сценарій2 ”).

 

Рис. 8. Заповнення вікна Значения ячеек сценария (“ Сценарій3 ”).

Після виконання даних дій повертаємося у вікно Диспетчер сценариев та натискаємо кнопку Отчет. У вікні, що з’явилося, у полі Тип отчета вибираємо Сводная таблица, а у полі Ячейки результата записуємо C7:C8. Після натискання кнопки ОК у робочій книзі Excel створюється додатковий лист із назвою Сводная таблица по сценарию. На цьому аркуші розміщені в одну колонку назви виконаних сценаріїв, а в іншу – результати їх виконання, тобто отримуємо таблицю 4:

Таблиця.

Результат роботи засобу MS Excel “Диспетчер сценаріїв”

Сценарій1	5,781478401
Сценарій2	2,192068084
Сценарій3	3,067210106

Як видно із таблиці 4, Сценарій1 видає найбільше значення (5,78). Цей сценарій і є оптимальним.