Рішення задач оптимізації в Excel
Побудова математичної моделі
Фірма рекламує свою продукцію з використанням чотирьох засобів: телевізора, радіо, газет і рекламних плакатів. Маркетингові дослідження показали, що ці кошти приводять до збільшення прибутку відповідно на 10, 5, 7 і 4 долари в розрахунку на 1 долар, витрачений на рекламу. Розподіл рекламного бюджету з різних видів реклами підпорядковане наступним обмеженням:
а) Повний бюджет становить 500000 доларів;
b) Слід витрачати не більше 40% бюджету на телебачення і не більше 20% бюджету на рекламні щити;
с) Внаслідок привабливості для молодіжної частини населення різних музичних каналів на радіо з цієї позиції слід витрачати принаймні половину того, що планується на телебачення.
Необхідно:
1. Сформулювати та розв'язати задачу розподілу коштів за різними джерелами для отримання максимального прибутку від реклами;
2. Пояснити сенс даних звіту за стійкості;
3. Визначити чи зміниться оптимальний план розподілу коштів, якщо збільшення прибутку від газетної реклами знизиться до 5 доларів в розрахунку на 1 долар, витрачений на рекламу;
4. Визначити, в який вид реклами буде вигідніше вкласти додаткові кошти в разі збільшення бюджету фірми.
Складемо математичну модель завдання, вибравши в якості змінних, х 4 - кількість коштів, витрачених на телебачення, рекламні плакати, радіо і газети відповідно. Тоді очікувана прибуток від реклами може бути підрахована за формулою
(1)
Змінні завдання задовольняють обмеженням
В В
(2)
В
в лівих частинах яких обчислені витрати грошових ресурсів на телебачення, радіо, газети і рекламні плакати, а в правих частинах записані максимально можливі запаси коштів на ці ресурси. Враховуючи, що змінні завдання за своїм економічним змістом не можуть приймати негативні значення, отримуємо математичну модель задачі оптимального розподілу грошових ресурсів з метою отримання максимального доходу від реклами.
В В В В В
.
Очевидно, що побудована модель має лінійну структуру і, отже, є завданням лінійного програмування.
Створення електронної моделі
Щоб залучити комп'ютер до вирішення цього завдання необхідно ввести вихідні дані на аркуш Excel.
Спочатку заносимо в таблицю незмінні дані, а потім заповнюємо змінювані комірки, в яких розташовані компоненти плану . На етапі введення вихідних даних сюди заносяться будь-які числа, наприклад, одиниці. Після рішення в цих осередках будуть знаходитися оптимальні значення змінних. Цільова функція (сумарний дохід) і ліві частини обмежень (Витрати за видами реклам) підраховуються у відповідність з складеної моделлю за формулою (1) і лівих частинах (2). span align=center>
Вид реклами
Х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
