Аналіз чутливості (еластичності)

Розібраний далі приклад аналізу чутливості стосується тільки задач з однією змінною рішення. Якщо ж їх більше, то для аналізу слід використовувати звіти засобу «Поиск решения», які описані у навчальному посібнику [2] та вданій роботі не повторюються.

У ході аналізу чутливості досліджується вплив відносно малих змін вхідних змінних на інші змінні, частіш усього – на показник ефективності. У даній задачі нас цікавить вплив малих змін ціни ізюму на прибуток, який є критерієм ефективності. Тобто, на скільки відсотків зміниться прибуток при заданій відсотковій зміні ціни (Рис. 4).

Рис. 4. Аналіз чутливості

Для аналізу було додано рядки відсотків зміни базової ціни (рядок 3) і зміни прибутку (рядок 31). Рядки параметрів моделі були сховані (рядки 6-16), або згруповані, тому що не становлять інтересу для аналізу чутливості.

Таким чином, аналіз чутливості показав, що зменшення базової ціни на п’ять відсотків призведе до збільшення прибутку на 3%. Але і об’єм продажів буде становити при цьому 850 000 кг (комірка Е18) замість базових 700 000 кг.

Перевіримо, а чи дає ціна у 2,05$ максимальний прибуток. В тім що це не так легко переконатися хоча б з графіку залежності прибутку від ціни. Обравши дані рядків 4 і 30, здобудемо наглядне представлення.

Рис. 5. Графік залежності ціни від прибутку

Вочевидь, оптимальна ціна знаходиться десь між 2,05 та 2,10$. Знайти точне значення можна за допомогою засобу «Поиск решения». Це буде найпростіший варіант його застосування, оскільки не буде потребувати уведення обмежень (графічно видно що це локальний максимум). Встановлюємо цільову комірку у Е30 (Рис. 4), а комірка що змінюється – Е4.

Рис. 6. Оптимальна ціна

Отже з’ясувалося, що найбільший прибуток у 346 901,04$ буде здобутий за ціною 2,07$.

Точка беззбитковості

Аналізуючи початкову модель задачі (Рис. 1), можна знайти що прибуток залежить не тільки від ціни глазурованого ізюму, а і від таких параметрів, як витрати на купівлю винограду, витрати на купівлю глазурі, витрати на переробку та постійні накладні витрати.

Беззбитковість означає що у найгіршому випадку буде отримано дохід, що дорівнюватиме загальним витратам, тобто прибуток буде дорівнювати нулю.

Припустимо, що ринкова ціна винограду різко зростає. Логічним кроком є збільшення ціни на ізюм. Але це призведе до зниження попиту та об’єму продажів, втрати клієнтів і як слідство – до зниження прибутку. Отже необхідно деякій час тримати ціну, але важливо знати межу збільшення ринкової ціни винограду, щоб не понести збитки.

Використаємо засіб «Подбор параметра» (Рис. 7).

Рис. 7. Добір параметра

D29 – це комірка з прибутком. Значення, що встановлюється – це нуль – беззбитковість. $D$6 – містить значення ринкової ціни винограду.

Рішення буде мати такий вигляд:

Рис. 8. Визначення точки беззбитковості

Визначено, що найвища ринкова ціна винограду, при якій компанія буде мати позитивний прибуток, складає 0,68$. Зверніть увагу що базова ціна змінена з 2,15$ на 2,07$. Якщо це не зробити, то результати будуть інші.

Регресійний аналіз

Компанія «Схід» за останні декілька років мала стабільний прибуток, але статистичний відділ на останній нараді надав дані (Рис. 9) щодо витрат на переробку винограду, які не співпадають з модельними. Виявилося, що витрати різко збільшуються, якщо завод працює не на повну потужність. Цей факт можна пояснити, наприклад, додатковими витратами у зв’язку з простоєм обладнання.

Рис. 9. Дані статистичного відділу

Таким чином, наша задача – визначити рівняння залежності витрат на переробку від кількості винограду. Оберемо для цього засіб «Регрессия» з меню «Анализ данных». Вхідним інтервалом Y має бути F3:F10, а вхідним інтервалом X – G3:G10 – незалежна змінна.

Рис. 10. Регресійний аналіз

Здобуті результати (Рис. 10) говорять о дуже щільній кореляційній залежності (коефіцієнт кореляції 0,99) та високій статистичній значимості здобутих параметрів (статистика Фішера 463).

Рівняння лінійної залежності має вигляд: Витрати на переробку = 0,13227 * Кількість винограду + 229369,1. Тепер слід уточнити існуючу модель розраховуючи витрати на переробку за здобутим рівнянням, якщо потужність заводу не повністю використовується. Для цього, звісно, повинна застосовуватися функція ЕСЛИ (вкладена).