Визначення залежності прибутку від витрат на рекламу

Нескінченне збільшення витрат на рекламу буде нескінченно збільшувати обсяг продажів. Але зміна прибутку, ймовірно, буде мати максимум, оскільки збільшення витрат на рекламу в кінці кінців “з’їсть” весь прибуток. Дослідимо цю залежність.

Скопіюємо першу таблицю з рис. 3.1 на новий лист робочої книги. Методом побудови прогресії заповнимо рядок витрат на рекламу в таблиці 3.2 (аналогічно до попереднього пункту розташуємо її в комірках С23:J24).

Таблиця 3.2

Кожне число копіюємо в комірку реклами (В11) та після перерахунку таблиці копіюємо число виробничого прибутку (В15) у відповідну комірку рядка прибутку таблиці 3.2 за допомогою команд Правка – Копировать / Правка – Специальная вставка – Значения.

За даними цієї таблиці побудуємо над нею графік (рис. 3.3).

Рис. 3.3. Залежність прибутку від витрат на рекламу

Залежність прибутку від витрат на рекламу має яскраво виражений максимум. Менеджер отримає оптимальний план, якщо він визначить витрати на рекламу у 18 000 грн. При цьому буде отриманий прибуток 15 000 грн.

Ми відчули вплив факторів на критеріальні показники і тепер можемо впевнено використовувати швидкі комп’ютерні алгоритми програмної оптимізації планів.

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

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

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

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

Поиск по сайту: