161123 (Моделювання бізнес-ситуації: інвестування запропонованих до реалізації проектів і автоматизація маркетингових досліджень і фінансово-економічних розрахунків в бізнес-плануванні), страница 5

3. Натискуйте кнопку Додати. У діалоговому вікні Додавання обмежень (мал. 6) натискуйте кнопку в полі Ссилка і виділите комірку В4 в робочому аркуші. Ця комірка містить ціну виробу.

4. Натискуйте на кнопку для розкриття списку Обмеження і виберіть оператора =.

5. У полі (справа) введіть значення 1,596 (ціна виробу, збільшена на 20%).

6. Натискуйте на кнопку Додати.

7. Виконаєте дії, описані в пп. 4- 7, для комірок С4, Е4, аби ввести обмеження для питомих змінних витрат і кількості виробів, яка має бути позитивним.

8. Закінчивши заповнення комірки Е4 в якості обмежувача, натискуйте на кнопку ОК. Ви повернетеся в діалогове вікно Пошук рішення (мал. 7).

9. Натискуйте на кнопку Виконати.

В результаті цих дій надбудова «Пошук рішення» починає підбирати значення, вказані як змінні комірки, аби набути значення 0 для точки беззбитковості. Оскільки ви вказали як мінімальний допустимого значення для комірки Е4 значення 0, надбудова «Пошук рішення» не стане розглядати негативні значення у комірці Е4.

В процесі роботи надбудови «Пошук рішення» з'являються повідомлення про проміжні рішення в рядку стану. Через деякий час надбудова «Пошук рішення» виведе вікно Результати пошуку рішення. В деяких випадках введені вами обмеження роблять неможливим знаходження рішення за допомогою надбудови «Пошук рішення», про що діалогове вікно негайно повідомить вас. Проте в нашому прикладі дані дозволяють звести точку самоокуповування до нуля.

Надбудова «Пошук рішення» може змінювати певні комірки для досягнення необхідного результату. З цієї причини зручніше зберігати різні рішення у вигляді сценаріїв. Для цього натискуйте на кнопку Зберегти сценарій діалогового вікна Результати пошуку рішення. При цьому на екрані з'явиться діалогове вікно Збереження сценаріїв. У текстовому полі Ім'я сценарію наберіть ім'я сценарію. Для того, щоб повернутися в діалогове вікно Результати пошуку рішення, натискуйте на кнопку ОК. Встановите перемикач Відновити вихідні значення в діалоговому вікні Результати пошуку рішення і натискуйте на кнопку ОК для відновлення вихідних значень параметрів робочого аркуша.

Мал. 5. Вхідні дані для застосування «Пошуку рішень»

Мал. 6. Обмеження для ціни, збільшеної на 20%.

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

Мал. 7. Використання надбудови «Пошук рішення» для оптимізації кількості виробів в точці беззбитковості.

Аналогічно описаній методиці побудуйте сценарії для визначення точки беззбитковості (у одиницях продукції) при зміні наступних параметрів:

1. Ціна виробу збільшилася на 20%, сукупні фіксовані витрати зросли на 25%, кількість виробів, що випускаються, - величина позитивна.

1. Ціна виробу збільшилася на 20%, змінні витрати зросли на 30%, кількість виробів, що випускаються, - величина позитивна.

3. Ціна виробу збільшилася на 20%, питомі змінні витрати зросли на 30%, сукупні фіксовані витрати - на 25%, кількість виробів (шукана величина), що випускаються, не може бути негативним значенням.

4. Ціна виробу збільшилася на 50%, питомі змінні витрати, постійні витрати і кількість виробів, що випускаються, при оптимізації рішення повинні залишатися позитивними.

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

9. Побудова звіту по різних фінансових сценаріях для аналізу чутливості

Побудуйте п'ять окремих сценаріїв, що відображають різні пропозиції. Найбільш ефективний метод порівняння сценаріїв - підсумовування результатів даних сценаріїв в одному робочому аркуші. Це можна здійснити, виконавши команду Сервіс/Сценарії. У діалоговому вікні, що розкрилося, Диспетчер сценаріїв натискуйте на кнопку Звіт. На екрані відображуватиметься діалогове вікно Звіт за сценарієм (мал. 8).У діалоговому вікні Звіт за сценарієм вводяться необхідні для звіту вічка результатів, адреси яких встановлені за умовчанням. Якщо ви хочете ввести більшу кількість результатів, то їх можна додати уручну. Потім натискуйте на кнопку ОК, і Ехсе1 створить в активній робочій книзі новий робочий аркуш, що містить звіт про сценарії, доступні для активного робочого аркуша (мал. 9).

Мал. 8. Сумування адреси результатів сценаріїв робочого листа.

Мал. 9. Звіт по 5 сценаріях, що використовуються для аналізу бізнес-ситуації.

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

1. Активізуйте робочий аркуш, що містить звіт про сценарії.

2. Виконаєте команду Сервіс/Аналіз даних. Якщо в меню Сервіс відсутній команда Аналіз даних, перейдіть до п. 3. Інакше пропустите його і перейдіть до п. 4.

3. Виконаєте команду Сервіс/Надстройки. У діалоговому вікні Надбудови, що розкрилося, активізуйте в списку надбудов опцію Пакет аналізу. Натискуйте на кнопку ОК.

4. У діалоговому вікні Пакет аналізу виберіть із списку Інструменти аналізу інструмент Описова статистика, а потім натискуйте кнопку ОК.

5. На екрані відображуватиметься діалогове вікно Описова статистика (мал. 10).

6. Натискуйте кнопку в текстовому полі Вхідний інтервал і виділите комірки в аркуші звіту, що містить чотири рядки комірок результатів. Виділите діапазон D11:Н14, що включає сукупні фіксовані витрати, ціну виробу, змінні витрати на одиницю продукції і точку беззбитковості в одиницях продукції.

7. Встановите перемикач Групування в положення по рядках.

8. Встановите прапорці опцій Підсумкова статистика і Рівень надійності. Натискуйте на кнопку ОК.

Мал. 10. Інструмент Описова статистика, що дозволяє розширити аналіз чутливості.

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

Таблиця 9. Результати роботи інструменту Описова статистика для п'яти сценаріїв

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

1. У комірку В18 - текст «Середнє + рівень надійності».

2. У комірку С18 - формулу = С4 + С17.

3. Скопіюйте введену формулу в діапазон комірок D18:F18.

4. У комірку В19 введіть текст «Середнє - рівень надійності».

5. У комірку С19 - формулу = С4 - С17.

6. Скопіюйте введену формулу в діапазон комірок D19:F19.

Результати, отримані за допомогою даних формул, є мінімальним і максимальним значеннями діапазонів 95-процентної упевненості від середніх показників постійних і змінних витрат, ціни виробу і точки окупності (у одиницях продукції). Іншими словами, якби вам довелося повторити цей аналіз чутливості 100 разів, використовуючи як вхідні припущення дані розглянутого прикладу, то 95 з 100 аналізів чутливості дали б 95-процентний діапазон упевненості найкращих очікувань cредніх постійних і змінних витрат, ціни виробу і точки окупності (у одиницях продукції).