Рішення задач оптимізації в Excel
Побудова математичної моделі
Фірма рекламує свою продукцію з використанням чотирьох засобів: телевізора, радіо, газет і рекламних плакатів. Маркетингові дослідження показали, що ці кошти приводять до збільшення прибутку відповідно на 10, 5, 7 і 4 долари в розрахунку на 1 долар, витрачений на рекламу. Розподіл рекламного бюджету за різними видами реклами підпорядковане наступним обмеженням:
а) Повний бюджет складає 500000 доларів;
b) Слід витрачати не більше 40% бюджету на телебачення і не більше 20% бюджету на рекламні щити;
с) Внаслідок привабливості для молодіжної частини населення різних музичних каналів на радіо по цій позиції слід витрачати принаймні половину того, що планується на телебачення.
Необхідно:
1. Сформулювати та вирішити задачу розподілу засобів за різними джерелами для отримання максимального прибутку від реклами;
2. Пояснити сенс даних звіту по стійкості;
3. Визначити чи зміниться оптимальний план розподілу коштів, якщо збільшення прибутку від газетної реклами знизиться до 5 доларів в розрахунку на 1 долар, витрачений на рекламу;
4. Визначити, в який вид реклами буде вигідніше вкласти додаткові кошти в разі збільшення бюджету фірми.
Складемо математичну модель завдання, вибравши в якості змінних, х 4 - кількість коштів, витрачених на телебачення, рекламні плакати, радіо та газети відповідно. Тоді очікуваний прибуток від реклами може бути підрахована за формулою
(1)
Змінні задачі задовольняють обмеженням
(2)
в лівих частинах яких обчислені витрати грошових ресурсів на телебачення, радіо, газети і рекламні плакати, а в правих частинах записані максимально можливі запаси коштів на ці ресурси. Враховуючи, що змінні завдання за своїм економічним змістом не можуть приймати негативні значення, отримуємо математичну модель задачі оптимального розподілу грошових ресурсів з метою отримання максимального доходу від реклами.
.
Очевидно, що побудована модель має лінійну структуру і, отже, є задачею лінійного програмування.
Створення електронної моделі
Щоб привернути комп'ютер до вирішення цього завдання необхідно ввести вихідні дані на аркуш Excel.
Спочатку заносимо в таблицю незмінні дані, а потім заповнюємо змінні комірки, в яких розташовані компоненти плану . На етапі введення вихідних даних сюди заносяться будь-які числа, наприклад, одиниці. Після рішення в цих комірках будуть знаходитися оптимальні значення змінних. Цільова функція (Сумарний дохід) і ліві частини обмежень (Витрати за видами реклам) підраховуються у відповідність до складеної моделлю за формулою (1) і лівим частинам (2).
Вид реклами
Х1
Х2
Х3
Х4
Сумарний
дохід
План запуску реклами
1
1
1
1
Прибуток від 1 витраченого $
10
4
5
7
26
Витрати за видами реклам
Витрати за видами реклам
Прибуток від виду реклами
Запаси грошових
ресурсів
(тис. $)
Х1
Х2
Х3
Х4
На телебачення
1
0
0
0
1
200
На рекламні щити
0
1
0
0
1
100
На радіо
0
0
1
0
1
100
На газети
0
0
0
1
1
100
Загальні витрати на всі види реклам
1
1
1
1
4
500
Звіт за результатами
Звіт складається з трьох таблиць, розташованих на одному аркуші книги Excel.
Цільова комірка (Максимум)
Осередок
Ім'я
Початкове значення
Результат
$ F
Прибуток від 1 витраченого $ Сум-ий дохід
26
3700
Змінні осередку
<...
