Таблиця 3.5

	А	В	С	D	E	F
2		1 квартал	2 квартал	3 квартал	4 квартал	Всього
3 5 6 7 8 10 11	Сезонність Число продажів, шт. Виручка від реалізації Собівартість Валовий прибуток Торговий персонал Реклама	0,9	1,1	0,8	1,2
		3193 127709 79818 47891	4769 190776 119235 71541	2523 100906 63066 37840	5676 227039 141899 85140	16161 646430 404019 242411
		8000	8000	9000	9000	34000
		7273	12346	5117	15263	40000
12 13 15	Непрямі витрати Сумарні витрати Виробничий прибуток	19156 34430	28616 48963	15136 29253	34056 58319	96965 170965
		13461	22578	8587	26820	71447
16	Норма прибутку	11 %	12 %	9 %	12 %	11 %
18 19	Ціна виробу Собівартість виробу	40 25

Річний бюджет на рекламу в 40 000 грн. не перевищений, обмеження враховано. У відповідності із знайденим розв’язком на рекламу буде виділено 5 117 грн. в 3-му кварталі та 15 263 грн. – в 4-му кварталі.

Прибуток збільшиться з 69 662 грн. (при рівномірних вкладеннях по 10 000 грн. в квартал, див. табл. 3.3.) до 71 447 грн. (див. табл. 3.5) при однаковому річному бюджеті на рекламу (40 000 грн.).

Зміна обмежень

Поиск решения дозволяє експериментувати з різними параметрами задачі для визначення найкращого варіанту розв’язку. Наприклад, змінивши обмеження, можна оцінити зміни результату. Спробуйте змінити обмеження на рекламний бюджет з 40 000 до 50 000 грн. та подивіться, як зміниться при цьому загальний прибуток.

Скопіюйте таблицю з попереднього завдання (табл. 3.5) на наступний лист. В меню Сервис виберіть пункт Поиск решения. В списку обмеження вже задане обмеження $F$11 <= 40 000. Натисніть кнопку Изменить. Змініть в полі значення з 40 000 на 50 000. Натисніть кнопку ОК, а далі – Выполнить.

Знайдений розв’язок наведений в таблиці 3.6.

Таблиця 3.6

	А	В	С	D	E	F
2		1 квартал	2 квартал	3 квартал	4 квартал	Всього
3 5 6 7 8 10 11	Сезонність Число продажів, шт. Виручка від реалізації Собівартість Валовий прибуток Торговий персонал Реклама	0,9	1,1	0,8	1,2
		3486 139450 87156 52294	5208 208314 130196 78118	2755 110182 68864 41318	6198 247910 154944 92966	17646 705856 441160 264696
		8000	8000	9000	9000	34000
		9249	15298	6678	18776	50000
12 13 15	Непрямі витрати Сумарні витрати Виробничий прибуток	20917 38166	31247 54545	16527 32205	37187 64962	105878 189878
		14127	23573	9113	28004	74817
16	Норма прибутку	10 %	11 %	8 %	11 %	11 %
18 19	Ціна виробу Собівартість виробу	40 25

Прибуток дорівнює 74 817 грн., що на 3 370 грн. більше попереднього значення 71 447 грн. при обмеженні 40 000 грн. Для більшості підприємств збільшення капіталовкладень на 10 000 грн., що приносить прибуток 3 370 грн. (тобто 33,7% рентабельності вкладень), є виправданим.

Прибуток при такому розв’язку буде на 4 889 грн. меншим порівняно із задачею без обмежень, але при цьому потрібно також на 39 706 грн. капіталовкладень менше.

Аналіз результатів

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

Аналіз швидко отримуваних варіантів плану дозволяє менеджеру осмислити та відчути вплив ряду факторів на показники плану, отримати додатковий прибуток за рахунок розробки науково обґрунтованого оптимального варіанту плану.

5. Оформити електронний звіт по виконаній лабораторній роботі засобами текстового процесора MicrosoftWord.

6. Ознайомитись з контрольними питаннями та захистити виконану лабораторну роботу викладачу.

Контрольні питання

1. Чому актуальна проблема оптимального планування витрат на рекламу продукції?

2. З яких етапів складається процес автоматизації оптимального планування витрат на рекламу продукції?

3. Сформулювати цілі та характеристики визначених підзадач (об’єкти проблемної системи)?

4. Пояснити структуру планової таблиці.

5. Перерахувати вхідні, змінні та вихідні дані моделі.

6. Пояснити формули планової таблиці.

7. Які засоби аналізу даних Excelвикористані в задачі?

8. Пояснити загальний принцип роботи таблиці підстановки в Excel?

9. Дати коротку характеристику технології розв’язання задачі планування витрат на рекламу в програмі Excel Поиск решения.

10. Яким чином здійснюється програмний пошук оптимального плану на один період?

11. Яким чином здійснюється розробка багатоетапного оптимального плану?

12. Як виконати програмний пошук з обмеженнями?