Технології параметричного аналізу методами «що - якщо»

Одне з типових застосувань електронних таблиць – виконання аналізу «що–якщо». Суть аналізу «що–якщо» полягає в отриманні відповідей на питання: як вестиме себе досліджуваний об’єкт у різних ситуаціях, кожна з яких визначається певними значеннями вхідних даних? Так, за оцінювання конкретної маркетингової ситуації часто потрібно отримати відповіді на питання такого типу: що буде, якщо збільшити різницю між собівартістю і продажною ціною або збільшити заробітну плату співробітникам на 1 %? Яке зростання необхідне, якщо до початку наступного року треба досягти збільшення обсягів продажу на певну суму? Як зміниться прибуток, якщо змінити різницю між собівартістю та ціною продажу? Як відіб’ється на собівартості продукції підвищення цін на певні матеріали або на електроенергію?

Для спрощення аналізу «що − якщо» в Excel є декілька інструментів, до яких належать: Добір параметра. Таблиця підстановки даних. Диспетчер сценаріїв.

Добір параметра. Цей інструмент дає змогу швидко й легко отримати від­повідь на питання: яке значення має бути на вході моделі (у найпростішому випадку модель може складатися лише з однієї формули), щоб отримати деяке конкретне значення на її виході? За відсутності такого інструмента для відповіді на це питання необхідно або вручну добирати вхідне значення, або створювати нову модель.

Пошук значення на вході моделі виконується так:

1. У меню Сервіс вибирається команда Добір параметра.

1. У поле Встановити в комірці вводиться посилання на клітину, що містить формулу, результат обчислення якої повинен мати потрібне значення.

2. У поле Значення вводиться значення, яке має бути отримане на виході моделі, тобто в клітині, посилання на яку було введене на попередньому кроці.

3. У поле Змінювані значення в комірці вводиться посилання на клітину на вході моделі, значення якої потрібно добрати. Значення цієї клітини має безпосередньо або через значення інших клітин впливати на результат формули, вказаної на кроці 2.

Таблиця підстановки даних. Цей інструмент дає змогу легко проаналізувати, як зміна певних значень у формулах впливає на результати, обчислюванні за цими формулами. Він є засобом швидкого обчислення декіль­кох варіантів, а також засобом перегляду і порівняння резуль­татів різних варіантів на одному робочому аркуші. Зазвичай таку таблицю використовують як частину деякої моделі. Існує два типи таблиць підстановки даних – з однією і з двома змінними.

Таблиця підстановки з однією змінною складається з трьох частин:

• вектора-колонки (або вектора-рядка), значення елементів якого підставляються в указану клітину (клітину введення даних). Кількість елементів цього вектора залежить від кількості значень, що підставляються у клітину введення даних;

• вектора-рядка (або вектора-колонки), елементи якого містять формули, що прямо або опосередковано посилаються на клітину введення даних. Цей вектор може мати й один елемент (складатися лише з однієї формули);

• векторів-колонок (або векторів-рядків) даних. Елементи кожного з цих векторів обчислюються за однією з формул, яка знаходиться над цим вектором-колонкою (або перед цим вектором-рядком). Якщо використовується лише одна формула, то буде лише один вектор з обчисленими даними.

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

Таблиця підстановки з однією змінною створюється у такій послідовності:

1. В окрему колонку аркуша або в окремий рядок уводиться список значень, які потрібно підставляти у клітину введення даних.

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

2. Виділяється прямокутник клітин, що містить формули та значення для підстановки в ці формули.

3. У меню Дані вибирається команда Таблиця підстановки.

4. Якщо значення для підстановки розміщено у колонку (а фор­мули – у рядок), то посилання на клітину введення вказується в полі Підставляти значення по рядках. Якщо ж значення для підстановки розміщено в рядок (а формули – у колонку), то посилання на клітину введення даних указується в полі Підставляти значення по колонках.

Таблиця підстановки з двома змінними створюється таким чином:

1. У клітину аркуша вводиться формула, результат обчислення якої залежить від двох клітин уведення даних.

1. У ту саму колонку нижче формули вводяться значення підстановки для першої змінної. Значення підстановки для другої змінної вводяться в рядок за формулою.

2. Виділяється прямокутник клітин, що містить формулу й обидва набори даних підстановки.

3. У меню Дані обирається команда Таблиця.

4. У поле Підставляти значення по колонках уводиться посилання на одну з клітин уведення даних.

5. У поле Підставляти значення по рядках уводиться посилання на другу клітину введення даних.

Слід мати на увазі, що таблиці підстановки перераховуються кожен раз при перерахунку аркуша навіть у тому разі, коли в них не було внесено ніяких змін. Тому у випадку, коли таблиця підстановки містить значну кількість елементів, для прискорен­ня процесу перерахунку аркуша слід так змінити параметр Обчислення на вкладці Обчислення (меню Сервіс команда Пара­метри), щоб автоматично перераховувався аркуш, але не табли­ці. Коли ж виникає потреба у перерахунку таблиці, натис­кується клавіша F9.

Слід також звернути увагу на те, що діапазон клітин з даними, що обчислюються за допомогою таблиці підстановки (з одною або двома змінними), являє собою масив, окремі частини якого не можна відредагувати і не можна вилучити. Можна лише очистити весь діапазон клітин цього масиву разом.

Диспетчер сценаріїв. Цей інструмент аналізу «що–якщо» дає змогу створювати і зберігати на робочому аркуші різні набори значень (сценарії) вхідних даних, а потім переключатися на будь-який з них для перегляду результатів, щоб порівняти поведінку модельованого об’єкта в різних ситуаціях. Для того, щоб задати умови, які характеризують певну ситуацію, потрібно, як правило, змінити значення декількох клітин, які містять вхідні дані. Тому таблиці даних, що дають змогу варіювати не більше двох змінних, для цього не завжди підходять. Саме в таких випадках застосовуються сценарії. З їх допомогою можна проводити багатофакторний параметричний аналіз, маніпулюючи одночасно 32 змінними.

Диспетчер сценаріїв дає змогу:

– створювати сценарії, вибираючи для кожного з них до 32 клітин із вхідними змінними;

– задавати значення кожної клітини, що містить вхідну змінну;

– присвоювати імена, зберігати і переглядати створені сценарії;

– створювати звіти для огляду і порівняння значень вхідних змінних і результатів сценаріїв;

– об’єднувати декілька сценаріїв в єдину модель;

– захищати сценарії від несанкціонованих змін;

– захищати сценарії від несанкціонованого перегляду.

Створення (додавання) нового сценарію. Сценарії можна створювати на кожному аркуші робочої книги, але слід мати на увазі, що вони можуть використовувати клітини лише того аркуша, на якому створюються.

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

Для визначення нового сценарію виконуються такі дії:

1. Вибирається команда Сервіс/Сценарії і в діалоговому вікні Диспетчер сценаріїв натискується кнопка Додати.

1. У діалоговому вікні Додавання сценарію у текстовому полі Назва сценарію набирається ім’я сценарію. На відміну від імен клітин назва сценарію може починатися з цифри, містити пропуски і мати довжину до 255 символів.

2. Якщо перед додаванням сценарію були виділені клітини робочого аркуша, то в текстовому полі Комірки, що змінюються з’явиться адреса цього діапазону клітин. У разі потреби в полі Комірки, що змінюються можна набрати посилання на потрібні клітини або виділити з допомогою миші. Якщо треба виділити несуміжні діапазони, то тримається натиснутою клавіша Ctrl. Виділятися можуть лише клітини з даними, а не ті, що містять формули.

3. За умовчання в текстовому полі Коментар з’явиться ім’я користувача і поточна дата. Можна вилучити або відредагувати цю інформацію.

4. Режим Заборонити зміни вибирається за умовчання, а режим Сховати – ні. Активізуються встановлені режими лише у разі включення захисту робочого аркуша командою Сервіс/Захист/Захистити аркуш.

5. За натискання кнопки ОК з’явиться діалогове вікно Значення комірок сценарію. Якщо клітинам, адреси яких визначалися в пункті 3, були надані імена, то вони з’являться в цьому діалоговому вікні. Тільки п’ять імен (або адрес клітин) будуть видимі. Щоб побачити інші, використовується смуга прокручування.

6. У разі потреби на даному кроці можна змінити значення. Якщо це перший сценарій на цьому аркуші, то можна зберегти його з величинами, заведеними за умовчання. Після введення потрібних значень можна або натиснути кнопку Додати і повернутись у діалогове вікно Додавання сценарію, або натиснути кнопку ОК і повернутись у вікно Диспетчер сценаріїв.

Використання сценаріїв. Найпростіший спосіб перегляду значень, що відповідають певному сценарію, – це використання діалогового вікна Диспетчер сценаріїв. Спочатку за допомогою смуг прокручування робочого аркуша в поле зору виводиться та частина робочого аркуша, де знаходяться відповідні вихідні значення. Після виконання команди Сервіс/Сценарії у текстовому полі Сценарії діалогового вікна Диспетчер сценаріїв вибирається назва потрібного сценарію і натискується кнопка Вивести. Це веде до заміни вхідних даних моделі, її перерахунку та виведенню значень відповідно до вибраного сценарію (попередні вхідні дані, якщо вони не були збережені у вигляді сценарію, будуть загублені).

Але такий спосіб аналізу варіантів розвитку подій придатний лише для відносно простих моделей. Для складніших використовуються підсумкові звіти для різних сценаріїв на додаткових робочих аркушах. Щоб отримати такий звіт, треба відкрити діалогове вікно Диспетчер сценаріїв (команда Сервіс/Сценарії) і натиснути кнопку Звіт. Це приведе до відкриття діалогового вікна Звіт за сценарієм, в якому є текстове поле Комірки результату і два перемикачі (структура і зведена таблиця), що визначають тип звіту.

У разі включення у звіт декількох клітин з результатами адреси цих клітин у полі Комірки результату мають відокремлюватися крапкою з комою (за введення адрес з допомогою миші треба тримати натиснутою клавішу Ctrl).

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

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

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