Применение EXCEL при моделировании неоднородных товарных рынков

Задача. В результате эмпирического анализа получена следующая зависимость прибыли фирмы от объема выпуска (выработки) и расходов на рекламную деятельность:

,

где x – выработка фирмы, y – расходы на рекламу в денежном выражении.

Определить оптимальные объем выпуска x ^*, уровень расходов на рекламу y ^* и соответствующую максимальную прибыль П_max(x ^*, y ^*) при условии, что B ₀=600, B ₁=4, B ₂=1, B ₃=2, B ₄=116, B ₅=5.

Решение. Найдем частные производные прибыли и приравняем их нулю:

, .

Последнюю систему можно представить в матричном виде:

.

1) Решение задачи методом вычисления определителей по правилу Крамера:

,

,

.

.

Таким образом, точка (x =12, y =14) является стационарной. Исследуем функцию на экстремум. Составим матрицу Гессе:

.

, . Минор 1-го порядка < 0, а минор 2-го порядка > 0, тогда матрица Гессе в стационарной точке отрицательно определена, следовательно, в стационарной точке максимум.

Другой способ определения достаточных условий существования экстремума функции. Пусть А = , В = , С = . Составим детерминант Δ= А · С - В ².

Тогда: 1) если Δ>0, то функция имеет экстремум в стационарной точке, а именно, максимум, если А <0 (или С <0), и минимум, если А >0 (или С >0); 2) если Δ<0, то экстремума в стационарной точке нет; 3) если Δ=0, то вопрос о наличии экстремума остается открытым (требуется дальнейшее исследование).

В задаче А =-2 < 0, В =2, С =-10, Δ= А · С - В ²=16 > 0, следовательно, в точке (x =12, y =14) функция П имеет максимум.

Прибыль П_max(x ^*, y ^*) = П_max(12, 14) = 1388.

Решите задачу методом Крамера в программе Excel, используя функцию МОПРЕД.

2) Решение задачи методом обращения матрицы:

Начальная матрица . Вычислим определитель матрицы A: . Определитель матрицы А отличен от нуля, следовательно, матрица А – невырожденная и для нее существует единственная обратная матрица А ^-1.

Находим матрицу А ^т, транспонированную к А: .

Вычислим присоединенную матрицу A *, состоящую из алгебраических дополнений элементов матрицы А ^т: А ₁₁ = 10, А ₁₂ = 2, А ₂₁ = -2, А ₂₂ = -2.

. .

Находим вектор-столбец неизвестных: .

3) Вычисление обратной матрицы с использованием элементарных преобразований:

Обратную матрицу А ^-1 можно вычислить на основании элементарных преобразований (преобразований Жордана-Гаусса) над строками матрицы:

- перемена местами двух строк;

- умножение строки матрицы на любое число, отличное от нуля;

- прибавление к одной строке матрицы другой строки, умноженной на любое число.

Для вычисления обратной матрицы для матрицы А необходимо составить расширенную матрицу В = (А | Е),затем с помощью элементарных преобразований преобразовать матрицу А к виду единичной матрицы Е, тогда на месте Е получим матрицу А ^-1.

Составим расширенную матрицу В:

.

Отсюда следует .

Решите задачу методом обращения матрицы с использованием в Excel следующих встроенных функций:

МОБР – возвращает обратную матрицу после нажатия клавиши F2, выделения массива результирующей матрицы, а затем использования комбинации клавиш Ctrl+Shift+Enter.

МУМНОЖ – возвращает произведение двух матриц (для данной задачи – это произведение обратной матрицы на вектор-столбец свободных элементов) после нажатия клавиши F2, выделения массива результирующей матрицы (в данной задаче – вектор-столбец неизвестных), а затем использования комбинации клавиш Ctrl+Shift+Enter.

4) Решение задачи с помощью оптимизатора Excel:

Подготовьте на рабочем листе Excel начальные данные задачи, а также формулу для вычисления целевой функции и ограничений задачи, как на рисунке.

Установите надстройку «Поиск решения»: кнопка Office → Параметры Excel → Надстройки → Управление Надстройки Excel → Перейти…→ флажок Поиск решения.

Вызовите режим «Поиск решения»: Данные → Поиск решения. Введите в диалоговое окно адреса целевой функции и переменных.

Нажмите кнопку «Параметры» и установите значения параметров.

При нажатии кнопки «Выполнить» должны получиться результаты, как на рисунке.

Сохраните найденное решение или отмените его, если оно ошибочно.

5) Для определения Прибыли в зависимости от Объема выпуска и Расходов на рекламу постройте двухмерную таблицу чувствительности.

Краткая справка. Двухмерная таблица чувствительности используется для выявления одновременного влияния двух переменных на определенный показатель. В этом случае двухмерная таблица чувствительности должна показать влияние двух факторов (Объема выпуска и Расходов на рекламу) на функцию Прибыли. Для ее построения выполните следующие действия:

Подготовьте на новом листе «Чувствительность» исходные данные в виде, как на рисунке.

Введите формулу связи в ячейку для целевой функции J2.

В столбец таблицы D введите интересующие значения Расходы на рекламу (например, от 2 до 24). В клетки 6-й строки введите интересующие значения Объема выпуска (например, от 2 до 20). В ячейку D6 введите ссылку на ячейку J2, где находится формула связи (или можно непосредственно в ячейку D6 ввести эту формулу).

Для расчета таблицы чувствительности выделите интервал ячеек, в который необходимо включить столбец и строку исходных данных. В данном случае следует указать интервал D6:N18.

Выберите команду Данные / Работа с данными / Анализ «что-если» / Таблица данных и укажите местонахождение ячейки ввода столбца (Объема выпуска – H2) и ячейки ввода строки (Расходы на рекламу – H3). После нажатия ОК будет построена таблица чувствительности, которая показывает зависимость Прибыли от Объема выпуска и Расходов на рекламу: П(q, А).

Найдите в таблице максимальное значение 1388 и выделите его.

При построении двухмерной таблицы используется формула =ТАБЛИЦА(Н2;Н3). Первый аргумент представляет собой ссылку на ячейку (Н2), в которую подставляются значения из первой строки выделенной области (значения Объема выпуска). Вторым аргументом является ячейка (Н2), в которую подставляются значения из левого столбца выделенной области (значения Расходов на рекламу).