2. Програмний пошук оптимального плану на один період.

Знову скопіюємо на новий лист першу таблицю з рис. 3.1. Для пошуку оптимального плану використаємо програму Excel Поиск решения. В меню Сервис виконайте команду Поиск решения. З’явиться діалогове вікно (рис. 3.4). Примітка! В разі відсутності команди Поиск решения в меню Сервис, виконайте послідовність: Сервис – Надстройки – Поиск решения – ОК.

Рис. 3.4. Діалогове вікно Поиск решения одноперіодної оптимізації без обмежень

Задайте В15 (прибуток за 1-й квартал) у полі Установить целевую ячейку. Виберіть пошук найбільшого значення та вкажіть в якості змінної комірки В11 (витрати на рекламу в 1-му кварталі). Запустіть процес пошуку розв’язку.

В процесі розв’язання задачі в рядку стану будуть відображатись повідомлення. Через деякий час з’явиться повідомлення про те, що розв’язок знайдений. У відповідності зі знайденим розв’язком, витративши 17 093 грн. на рекламу в 1-му кварталі, можна отримати найбільший прибуток, який складе 15 093 грн. Цей розв’язок точніший від отриманого раніше графічним способом.

3. Розробка багатоетапного оптимального плану без обмежень.

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

Скопіюємо першу таблицю з рис. 3.1 (за 1 квартал) на новий лист робочої книги і доповнимо інформацією за 2-4 квартали, використовуючи швидке копіювання формул в комірках та формули з таблиці 3.3.

Ручний пошук оптимального плану

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

Таблиця 3.3

	А	В	С	D	E	F	Примітка!
2		1 квартал	2 квартал	3 квартал	4 квартал	Всього	Формули для обчислення Всього
3 5 6 7 8 10 11	Сезонність Число продажів, шт. Виручка від реалізації Собівартість Валовий прибуток Торговий персонал Реклама	0,9	1,1	0,8	1,2
		3592 143662 89789 53873	4390 175587 109742 65845	3192 127700 79812 47887	4789 191549 119718 71831	15962 638498 399061 239437	=СУММ(В5:Е5) =СУММ(В6:Е6) =СУММ(В7:Е7) =СУММ(В8:Е8)
		8000	8000	9000	9000	34000	=СУММ(В10:Е10)
		10000	10000	10000	10000	40000	=СУММ(В11:Е11)
12 13 15	Непрямі витрати Сумарні витрати Виробничий прибуток	21549 39549	26338 44338	19155 38155	28732 47732	95775 169775	=СУММ(В12:Е12) =СУММ(В13:Е13)
		14324	21507	9732	24099	69662	=СУММ(В15:Е15)
16	Норма прибутку	10 %	12 %	8 %	13 %	11 %	=F15/F6
18 19	Ціна виробу Собівартість виробу	40 25

Підберіть числа плану квартальних витрат на рекламу в рядку 11, домагаючись найбільшої суми річного прибутку в комірці F15. Оскільки задана в 3-му рядку сезонна поправка входить до розрахунку числа продажів (рядок 5) в якості співмножника, доцільно збільшити витрати на рекламу в 4-му кварталі, коли прибуток від продажів найбільший, та зменшити відповідно в 3-му.

Програмний пошук оптимального плану

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

Настройка моделі у вікні Поиск решения

В меню Сервис виконайте команду Поиск решения. З’явиться діалогове вікно для настройки моделі оптимізатора (рис. 3.5).

Задайте F15 (загальний прибуток за рік) в полі Установить целевую ячейку. Виберіть пошук максимального значення. Задайте в якості змінних комірок В11:Е11 (витрати на рекламу в кожному кварталі). Поле Ограничения – порожнє.

Задача є нелінійною задачею оптимізації середнього ступеня складності. Нелінійність рівняння пов’язана з операцією піднесення до степеня в формулі рядка числа продажів. Тому, натиснувши кнопку Параметры, задаємо у вікні настройки алгоритмів нелінійну задачу та вибираємо методи Ньютона чи градієнтний.

Рис. 3.5. Вікно настройки моделі оптимізації

Після настройки моделі та установки параметрів алгоритму натискаємо кнопку Выполнить вікна Поиск решения. За секунду оптимальний розв’язок готовий. В табл. 3.4 наведений оптимальний розв’язок, знайдений програмою.