Диспетчер сценариев ms Excel
Якщо необхідно вирішити задачу з кількома змінними, і при цьому є кілька варіантів вихідних даних для цих змінних, то використовується інструмент Диспетчер сценариев. Кожен набір значень змінних, що використовуються як вихідні дані задачі, має назву Сценарій. Сценарію надається ім’я. Після створення сценаріїв можливо створити звіт по всіх вхідних значеннях та результатах.Використовуючи звіт, можна порівняти та проаналізувати результати вирішення задачі з різними сценаріями (наборами вихідних даних).
Диспетчер сценариевможевирішувати такі задачі: створювати та зберігати різні сценарії для однієї моделі, при цьому кожен сценарій має власний набір змінних; переглядати існуючі сценарії, використовувати пароль для захисту сценаріїв або приховувати їх; вилучати сценарії; створювати звіти на базі різних сценаріїв.
Вище наведений приклад вирішення задачі визначення витрат на рекламу, при яких прибуток від реалізації товару буде максимальним за допомогою інструмента Поиск решения. Подібну задачу можна вирішити, використовуючи Диспетчер сценариев.
Визначимо прибуток від реалізації продукції та рентабельність при різних значеннях витрат на рекламу. Змінювані дані - поквартальні витрати на рекламу. Результуючий звіт по створюваних сценаріях буде включати річний прибуток від реалізації товару та рентабельність. Плановані показники з 1 по 4 квартал, відповідно: сезонний фактор - 0,8; 1,1; 0,9; 1,2; витрати на заробітну плату персоналу -7, 7,5; 8, 8,5 тис. грн.; витрати на рекламу - 12 тис. грн. у кожному кварталі. Дані про продукцію: ціна реалізації – 45 грн.; собівартість продукції - 30 грн. Вихідні дані для вирішення задачі представлені на рис. 18.
Рис. 18.
Результат оптимізації прибутку від реалізації товару в задачі маркетингу за допомогою інструменту Поиск решения, показаний на рис. 19. Як видно з отриманого рішення, при витратах на рекламу 73 434 грн. річний прибуток від реалізації товару буде максимальний і становитиме 66 461 грн., видно, що витрати на рекламу більше запланованих і значення рентабельності зменшується з 8 до 7%.
Рис. 19.
Сценарії для даної задачі створюються таким чином:
В меню Сервис потрібно обрати команду Сценарии. У діалоговому вікні Диспетчер сценариев клацнути кнопку Добавить.
У вікні Добавление сценария (рис. 20) вказується ім’я сценарію та адреси ячейок, значення яких будуть змінюватися у цьому сценарії.
Рис. 20.
Після натиснення кнопки OK відкривається вікно (рис. 21), у відповідних полях якого вказуються нові значення змінюваних ячейок.
Рис. 21.
Для кожного нового сценарію потрібно повторювати попередні пункти. Після створення всіх сценаріїв можна клацнути кнопку Отчет (рис. 22) та обрати форму звіту (рис. 23).
Рис. 22.
Рис. 23.
У звіті у вигляді структури сценарію (рис. 24) наглядно відображаються значення змінюваних та результуючих ячейок. Набазі створеного звіту обирається сценарій з набором вихідних даних для досягнення найбільшого прибутку. Цей сценарій можна взяти за основу та далі створити кілька сценаріїв із кращими результатами. Таким чином, задача з кількома змінними вирішується методом послідовного наближення з використанням інструменту Диспетчер сценариев.
Рис. 24.
На будь-яку панель інструментів можна вивести кнопку поля зі списком Сценарий (рис. 25), за допомогою якої переглядається результат роботи створених сценаріїв.
Рис. 25.
Завдання практичної роботи № 1 виконується з використанням інструменту Поиск решения. Отримані дані беруться за основу для виконання завдання практичної роботи № 2 з за допомогою Диспетчера сценариев.