Решение финансовых и оптимизационных задач в Microsoft Excel

       ;

       ;

где  – запас сырья на -м пункте его получения;

      – потребность в сырье на -м предприятии.

      Для решения этой задачи с помощью  средства поиска решений введем данные, как показано на рис. 9.1. 

Рисунок 9.1

       В ячейки С5:F7 введена матрица С (тарифы перевозок). Ячейки С14:F16 отведены под значения неизвестных – объема перевозок. В ячейку G19 введена целевая функция

       .=СУММПРОИЗВ(C5:F7;C14:F16)

       В ячейки С17:F17 введены формулы (см. рис. 8.1), определяющие объем сырья, необходимого соответствующему предприятию.

       В ячейки G14:G16 введены формулы (см. рис. 8.1), определяющие объем сырья, сосредоточенного на пунктах его получения.

      Далее выбираем команду Сервис, Поиск решения и заполняем открывшееся диалоговое окно Поиск решения , как показано на рис. 9.2.

Рисунок 9.2 

       В диалоговом окне Параметры поиска решения (рис. 9.3) устанавливаем флажок Линейная модель и Неотрицательные значения. После

Рисунок 9.3 

нажатия кнопки Выполнить средство поиска решений находит оптимальный план перевозок, при котором общая стоимость перевозок является минимальной (рис 9.4).

Рисунок 9.4

 

       Задача № 10 (Набор задач № 9.1)

      В данной задаче необходимо определить суммы кредитов по указанным видам так, чтобы максимизировать доход. Обозначим через , , , , – суммы кредитов на личные нужды, покупку авто, жилье, с/х и бизнес соответственно. Суммарный доход от размещения всех кредитов, учитывая долю дохода и долю невозврата по каждому из вышеперечисленных кредитов, равен: 

 

      Упрощая данное выражение, получим: 

 

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

       .

      Сумма всех кредитов не должна превышать 12 млн  $, следовательно:

      

      Банк  обязан разместить всех кредитов на нужды с/х и бизнеса, следовательно:

       , упрощая, получим:

      

      Банк  обязан разместить от кредитов на личные нужды, авто и жилье – на жилье , следовательно:

       , упрощая,  получим:

      

      Общая доля невозврата по всем кредитам не должна превосходить 0,08, следовательно:

       , упрощая,  получим:

      

      Таким образом, математическая модель данной задачи имеет следующий вид:

      максимизировать

      

      при следующих ограничениях:

      

      Данная  модель является линейной, т. к. целевая функция и ограничения линейно зависят от переменных.

      Задача  решается в Microsoft Excel при помощи команды Сервис, Поиск решения. Ячейки Е4:Е8 отведены под значения переменных , , , , . В ячейку Е12 введена целевая функция (рис 10.2).

      Для приведенного на рис 10.1 расчета в  соответствующие ячейки введены  формулы, показанные на рис. 10.2.

Рисунок 10.1 

Рисунок 10.2

      В диалоговом окне Поиск решения введены данные, показанные на рис 10.3 и рис 10.4.

Рисунок 10.3 

Рисунок 10.4 

      Из  результатов расчета видно, для того чтобы максимизировать доход, необходимо разместить 7,2 млн $ в кредит на жилье и 4,8 млн $ в кредит на бизнес.

 

Заключение

     В данной работе были рассмотрены примеры  решения финансово-экономических  задач с использованием функций  ПЛТ, ПС и ЧПС, а также оптимизационных  задач линейного программирования (планирование производства, транспортная задача, задача о кредитах) с использованием средства Поиск решения в Microsoft Excel.

 

Список  используемой литературы

1. С.М. Лавренов. Excel. Сборник примеров и задач. – М: Финансы и статистика 2003г. – 335 с.
2. Хемди А. Таха. Введение в исследование операций. – М: Вильямс 2005г. – 901 с.
3. А.Ю. Гарнаев. Использование MS Excel и VBA в экономике и финансах. – СПб: БХВ – Санкт Петербург 2000 г. – 336 с.
4. И.Я. Лукасевич. Анализ финансовых операций. Методы, модели вычислений. – М: Финансы, ЮНИТИ, 1998г. – 400 с.
5. С. Фишер и др. Экономика. – М: Дело Лтд 1995г. – 829 с.