Решение транспортной задачи в excel: пример

Достоинства и недостатки модели Г. Марковица

Рассмотрим ряд недостатков присущих модели Г. Марковица.

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

Многие из данных недостатков модели были решены последователями: прогнозирование доходности с помощью многофакторных моделей (Ю. Фама, К. Френч, Росс и др.), нейронных сетей; оценка риска на основе моделей ARCH, GARCH и т.д. Следует отметить одно из главных достоинств модели Г. Марковица: систематизация подхода к формированию инвестиционного портфеля и управление его доходностью и риском.

★ Программа InvestRatio — расчет всех инвестиционных коэффициентов в Excel за 5 минут(расчет коэффициентов Шарпа, Сортино, Трейнора, Калмара, Модильянки бета, VaR)
+ прогнозирование движения курса

Резюме

В данной статье мы рассмотрели, как с помощью Excel можно сформировать инвестиционный портфель по модели Г. Марковица и решить две классические задачи: максимизация доходности портфеля при минимальном риске и минимизация риска при заданной доходности. Портфель Марковица позволяет снизить систематические риски за счет комбинации различных активов. Несмотря на сложности использования данной модели в современной экономике данная модель применима для таких низковолатильных активов как недвижимость, облигации товарные фьючерсы и т.д. В настоящее время сократился срок пересмотра активов в портфеле, так если раньше он мог составлять год, то сейчас это 2-6 месяцев

С вами был Иван Жданов, спасибо за внимание

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Бесплатный Экспресс-курс «Оценка инвестиционных проектов с нуля в Excel» от Ждановых. Получить доступ

Оценка стоимости бизнеса	Финансовый анализ по МСФО	Финансовый анализ по РСБУ
Расчет NPV, IRR в Excel	Оценка акций и облигаций

4.5Поиск решений

«Поиск решения» — это надстройка для Microsoft Excel. С помощью этой надстройки можно найти оптимальное значение (максимум или минимум) формулы, содержащейся в одной ячейке, называемой целевой, с учетом ограничений на значения в других ячейках с формулами на листе.

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

Примечание. В версиях надстройки «Поиск решения», выпущенных до Excel 2007, ячейки переменных решения назывались изменяемыми или регулируемыми.

Постановка задачи и решение проблемы

В поле Оптимизировать целевую функцию введите ссылку на ячейку или имя целевой ячейки. Целевая ячейка должна содержать формулу.
Выполните одно из указанных ниже действий.

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

В поле Изменяя ячейки переменных введите имена диапазонов ячеек переменных решения или ссылки на них. Несмежные ссылки разделяйте запятыми. Ячейки переменных должны быть прямо или косвенно связаны с целевой ячейкой. Можно задать до 200 ячеек переменных.

Рис. 33. Окно Параметры поиска решения

В поле В соответствии с ограничениями введите любые ограничения, которые требуется применить. Для этого выполните указанные ниже действия.
1. В диалоговом окне Параметры поиска решения нажмите кнопку Добавить.
2. В поле Ссылка на ячейку введите ссылку на ячейку или имя диапазона ячеек, на значения которых налагаются ограничения.
3. Выберите в раскрывающемся списке отношение ( <=, =, >=, int, bin или dif), которое нужно использовать между ссылкой и ограничением.

Примечание: Если выбрать вариант int, в поле Ограничение появится значение целое число. Если выбрать вариант bin, в поле Ограничение появится значение двоичное число. Если выбрать вариант dif, в поле Ограничение появится значение все разные.

Если в поле Ограничение было выбрано отношение <=, = или >=, введите число, ссылку на ячейку (или имя ячейки) или формулу.
Выполните одно из указанных ниже действий.
- Чтобы принять данное ограничение и добавить другое, нажмите кнопку Добавить.
- Чтобы принять ограничение и вернуться в диалоговое окно Параметры поиска решения, нажмите кнопку ОК.

Примечание Отношения int, bin и dif можно использовать только в ограничениях для ячеек переменных решения.

Выберите метод решения

Методы поиска решения

В диалоговом окне Параметры поиска решения можно выбрать любой из указанных ниже алгоритмов или методов поиск решения.

Нелинейный метод обобщенного понижающего градиента (ОПГ) Используется для гладких нелинейных задач.
Симплекс-метод Используется для линейных задач.
Эволюционный метод Используется для негладких задач.

Нажмите кнопку Найти решение и выполните одно из указанных ниже действий.

Рис. 34. Окно Результат поиска решений

Чтобы сохранить значения решения на листе, в диалоговом окне Результаты поиска решения выберите вариант Сохранить найденное решение.
Чтобы восстановить исходные значения перед нажатием кнопки Решить, выберите вариант Восстановить исходные значения.

Примечания:

Чтобы прервать поиск решения, нажмите клавишу ESC. Лист Microsoft Excel будет пересчитан с учетом последних найденных значений для ячеек переменных решения.
Чтобы создать отчет, основанный на найденном решении, выберите тип отчета в поле Отчеты и нажмите кнопку ОК. Отчет будет помещен на новый лист книги. Если решение не найдено, будут доступны только некоторые отчеты или они вообще не будут доступны.
Чтобы сохранить значения ячейки переменной решения в качестве сценария, который можно будет отобразить позже, нажмите кнопку Сохранить сценарий в диалоговом окне Результаты поиска решения, а затем введите имя этого сценария в поле Название сценария.

Включение функции

Можно долго искать на ленте, где находится Поиск решения, но так и не найти данный инструмент. Просто, для активации данной функции, нужно её включить в настройках программы.

Для того, чтобы произвести активацию Поиска решений в программе Microsoft Excel 2010 года, и более поздних версий, переходим во вкладку «Файл». Для версии 2007 года, следует нажать на кнопку Microsoft Office в левом верхнем углу окна. В открывшемся окне, переходим в раздел «Параметры».

В окне параметров кликаем по пункту «Надстройки». После перехода, в нижней части окна, напротив параметра «Управление» выбираем значение «Надстройки Excel», и кликаем по кнопке «Перейти».

Открывается окно с надстройками. Ставим галочку напротив наименования нужной нам надстройки – «Поиск решения». Жмем на кнопку «OK».

После этого, кнопка для запуска функции Поиска решений появится на ленте Excel во вкладке «Данные».

Примеры и задачи на поиск решения в Excel

Рассмотрим аналитические возможности надстройки. Например, Вам нужно накопить 14 000$ за 10 лет. На протяжении 10-ти лет вы хотите каждый год откладывать на депозитный счет в банке по 1000$ под 5% годовых. Ниже на рисунке построена таблица в Excel, по которой хорошо видно остаток накопленных средств на каждый год. Как видно при таких условиях депозитного счета и взносов накопления цель не будет достигнута даже через 10 лет. При решении данной задачи можно пойти двумя путями:

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

Мы можем изменять переменные значения в ячейках B1 и B2 так, чтобы подобрать необходимые условия для накопления необходимой суммы денег.

Надстройка «Поиск решения» – позволяет нам одновременно использовать 2 этих варианта, чтобы быстро смоделировать наиболее оптимальные условия для достижения поставленной цели. Для этого:

Перейдите в ячейку B14 и выберите инструмент: «Данные»-«Анализ»-«Поиск решения».

В появившемся диалоговом окне заполните все поля и параметры так как указано ниже на рисунке. Не забудьте убрать галочку напротив опции: «Сделать переменные без ограничений неотрицательными». И нажмите «Найти решение».

Как видно программа немного увеличила процентную ставку и сумму ежегодных взносов.

Транспортная задача: описание

С помощью транспортной задачи можно найти наилучший вариант перевозки с минимальными издержками между двумя взаимодействующими контрагентами (в рамках данной статьи будем рассматривать покупателей и продавцов). Чтобы приступить к решению, нужно представить исходные данные в схематичном или матричном виде. Последний вариант применяется в Эксель.

Транспортные задачи бывают двух типов:

Закрытая – совокупное предложение продавца равняется общему спросу.
Открытая – спрос и предложение не равны. Чтобы решить такую задачу, нужно сначала привести ее к закрытому типу. В этом случае добавляется условный покупатель или продавец с недостающим количеством спроса или предложения. Также в таблицу издержек следует внести соответствующую запись (с нулевыми значениями).

Оценка риска инвестиционного портфеля Марковица

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Бесплатный Экспресс-курс «Оценка инвестиционных проектов с нуля в Excel» от Ждановых. Получить доступ

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

где:

σp – риск инвестиционного портфеля;

σ_i – стандартное отклонение доходностей i-го финансового инструмента;

k_ij – коэффициент корреляции между I,j-м финансовым инструментом;

w_i – доля i-го финансового инструмента (акций) в портфеле;

V_ij – ковариация доходностей i-го и j-го финансового инструмента;

n – количество финансовых инструментов инвестиционного портфеля.

Алгоритм решения

Итак, приступи к решению нашей задачи:

Для начала строим таблицу, количество строк и столбцов в которой соответствует числу продавцов и покупателей, соответственно.
Перейдя в любую свободную ячейку щелкаем по кнопке “Вставить функцию” (fx).
В открывшемся окне выбираем категорию “Математические”, в списке операторов отмечаем “СУММПРОИЗВ”, после чего щелкаем OK.
На экране отобразится окно, в котором нужно заполнить аргументы:
- в поле для ввода значения напротив первого аргумента “Массив1” указываем координаты диапазона ячеек матрицы затрат (с желтым фоном). Сделать это можно, используя клавиши на клавиатуре, или просто выделив нужную область в самой таблице с помощью зажатой левой кнопки мыши.
- в качестве значения второго аргумента “Массив2” указываем диапазон ячеек новой таблицы (либо вручную, либо выделив нужные элементы на листе).
- по готовности жмем OK.
Щелкаем по ячейке, расположенной слева от самого верхнего левого элемента новой таблицы, после чего снова жмем кнопку “Вставить функцию”.
На этот раз нам нужна функция “СУММ”, которая также, находится в категории “Математические”.
Теперь нужно заполнить аргументы. В качестве значения аргумента “Число1” указываем верхнюю строку созданной для расчетов таблицы (целиком) – вручную или методом выделения на листе. Жмем кнопку OK, когда все готово.
В ячейке с функцией появится результат, равный нулю. Наводим указатель мыши на ее правый нижний угол, и когда появится Маркер заполнения в виде черного плюсика, зажав левую кнопку мыши тянем его до конца таблицы.
Это позволит скопировать формулу и получить аналогичные результаты для остальных строк.
Выбираем ячейку, которая находится сверху от самого верхнего левого элемента созданной таблицы. Аналогично описанным выше действиям вставляем в нее функцию “СУММ”.
В значении аргумента “Число1” теперь указываем (вручную или с помощью выделения на листе) все ячейки первого столбца, после чего кликаем OK.
С помощью Маркера заполнения выполняем копирование формулы на оставшиеся ячейки строки.
Переключаемся во вкладку “Данные”, где жмем по кнопке функции “Поиск решения” (группа инструментов “Анализ”).
Перед нами появится окно с параметрами функции:
- в качестве значения параметра “Оптимизировать целевую функцию” указываем координаты ячейки, в которую ранее была вставлена функция “СУММПРОИЗВ”.
- для параметра “До” выбираем вариант – “Минимум”.
- в области для ввода значений напротив параметра “Изменяя ячейки переменных” указываем диапазон ячеек новой таблицы (без суммирующей строки и столбца).
- нажимаем кнопку “Добавить” в блоке “В соответствии с ограничениями”.
Откроется небольшое окошко, в котором мы можем добавить ограничение – сумма значений первых столбцов исходной и созданной таблицы должны быть равны.
- становимся в поле “Ссылка на ячейки”, после чего указываем нужный диапазон данных в таблице для расчетов.
- затем выбираем знак “равно”.
- в качестве значения для параметра “Ограничение” указываем координаты аналогичного столбца в исходной таблице.
- щелкаем OK по готовности.
Таким же способом добавляем условие по равенству сумм верхних строк таблиц.
Также добавляем следующие условия касательно суммы ячеек в таблице для расчетов (диапазон совпадает с тем, который мы указали для параметра “Изменяя ячейки переменных”):
- больше или равно нулю;
- целое число.
В итоге получаем следующий список условий в поле “В соответствии с ограничениями”. Проверяем, чтобы обязательно была поставлена галочка напротив опции “Сделать переменные без ограничений неотрицательными”, а также, чтобы в качестве метода решения стояло значение “Поиск решения нелинейных задач методов ОПГ”. Когда все готово, нажимаем “Найти решение”.
В результате будет выполнен расчет и отобразится окно с результатами поиска решения. Оцениваем их, и в случае, когда они нас устраивают, нажимаем OK.
Все готово, мы получили таблицу с заполненными данными и транспортную задачу можно считать успешно решенной.

Пример задачи

На складах A1 — A4 есть суммарно 100 тонн зерна, и их нужно развести по текущим расценкам в пункты B1 – B3, потратив как можно меньше средств на доставку. Тарифы на доставку указаны в центре таблицы.

Дублируем нашу таблицу в Excel.

Рисуем другую таблицу.

Диапазон ячеек D12 – F15 заполняем единицами. Эти значения мы впоследствии будем изменять, чтобы найти самый дешёвый вариант перевозки. В диапазоне H12 – H15 должна быть сумма трёх единиц таблицы в строке D12 – F12, а в D17 – F17 – сумма четырёх единиц в столбце. Так напротив каждой строки и каждого столбца

Рисуем третью таблицу, которая перемножит соответствующие ячейки первых двух таблиц.

Для этого выделяем диапазон 3 на 4 клетки, жмём на кнопку « = », выделяем диапазон D3-F6, жмём на клавиатуре « * », выделяем D12 – F15 и зажимаем сочетание клавиш Ctrl + Shift + Enter. Всё, вы перемножили значения.

Теперь суммируем все значения последней таблицы. Для этого просто выберите произвольную свободную ячейку в MS Excel. Введите в неё « =СУММ( » и выделите третью таблицу. Нажмите Enter.

Переходим во вкладку «Данные» и находим там «Поиск решения».

Щелкаем по данной кнопке. Далее всё делаем, как представлено на рисунке.

Описываю сверху вниз всё окно. Выберите целевую ячейку ту, которую мы сделали в 4-ом шаге нашего решения. Далее выберите минимум. В поле «Изменяя ячейки переменных» выберите диапазон, где мы проставили единицы. Выставляем ограничения. Значения, которые будут находиться вместо единиц, должны быть больше нуля и целыми, а потребности не должны превысить запасов. Жмём «Найти решение».

Получаем следующий результат.

Если вы всё сделали правильно, то у вас должно быть всё точно так же.

Подготовка таблицы

Теперь, после того, как мы активировали функцию, давайте разберемся, как она работает. Легче всего это представить на конкретном примере. Итак, у нас есть таблица заработной платы работников предприятия. Нам следует рассчитать премию каждого работника, которая является произведением заработной платы, указанной в отдельном столбце, на определенный коэффициент. При этом, общая сумма денежных средств, выделяемых на премию, равна 30000 рублей. Ячейка, в которой находится данная сумма, имеет название целевой, так как наша цель подобрать данные именно под это число.

Коэффициент, который применяется для расчета суммы премии, нам предстоит вычислить с помощью функции Поиска решений. Ячейка, в которой он располагается, называется искомой.

Целевая и искомая ячейка должны быть связанны друг с другом с помощью формулы. В нашем конкретном случае, формула располагается в целевой ячейке, и имеет следующий вид: «=C10*$G$3», где $G$3 – абсолютный адрес искомой ячейки, а «C10» — общая сумма заработной платы, от которой производится расчет премии работникам предприятия.

Эконометрический вид модели Марковица

Для того чтобы сформировать инвестиционный портфель необходимо решить оптимизационную задачу. Существует два вида задач: поиск долей акций в портфеле для достижения максимальной эффективности при заданном уровне риска (σ_p) и минимизация риска при заданном уровне доходности портфеля (r_p). Помимо этого на уравнения накладываются дополнительные очевидные ограничения: сумма долей активов должна быть равна 1 и сами доли активов должны быть положительными.

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

Портфель Марковица минимального риска	Портфель Марковица максимальной эффективности

Заключение

Таким образом, с помощью программы Эксель достаточно просто решить транспортную задачу. Самое главное – правильно заполнить начальные данные и четко следовать плану действий, и тогда проблем быть не должно, т.к. программа все расчеты выполнит сама.

Решим транспортную задачу методом потенциалов. Нам известны торговые запасы, потребительские запросы и стоимость доставки за единицу продукции. Сделаем три исходные таблицы.

Построим опорный план транспортной задачи с помощью инструмента «Поиск решений». Рядом составим такие же по объему таблицы с пустыми ячейками. Таблица А – аналог стоимостной, Б – «запасов», В – «спроса».

Элементы таблицы Б – сумма соответствующих строк в таблице А. Элементы таблицы В – сумма соответствующих столбцов в таблице А.

Отдельно составим результирующую таблицу Г. В ней отразятся оптимальные транспортные расходы. Каждый элемент таблицы Г – произведение элемента А и соответствующего элемента стоимостной таблицы.

В отдельном месте листа введем формулу функции: =СУММПРОИЗВ(A1:C3;G1:I3)

Первый массив – стоимостная таблица, второй – диапазон А.

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

Целевая ячейка – ссылка на ячейку со значением функции.
Она должна быть равна «максимальному значению», как наиболее выгодному для перевозчика.
Команда изменяет значения ячеек в таблице А. Значения – целые числа.
Диапазон таблицы Б = «Запасам».
Диапазон В = «Потребительскому спросу».

В открытом диалоговом окне нажимаем кнопку «Параметры» и устанавливаем следующие настройки:

Жмем ОК – «Выполнить». Получаем опорный план транспортной задачи:

Он залит бледно-зеленым цветом. Ячейки со значениями выше нуля называются «базисными», «занятыми». Ячейки со значением 0 – «свободными».

Далее действуем по плану:

Посчитаем число занятых клеток с помощью функции СЧЕТЕСЛИ.

Так как результат равен 5, опорный план является не вырожденным. Проверим оптимальность опорного плана – найдем потенциалы по занятым клеткам.

Нужно составить систему уравнений. Предполагается, что αj = 0, а αi + βj = сij (стоимость доставки единицы груза). Вызываем команду «Поиск решения». Вносим условия системы уравнений в качестве ограничений.

Заполненное диалоговое окно:

Результат работы инструмента «Поиск решения»:

Посчитаем оценки свободных клеток. Формула: сij – (αi + βj). Берем свободную клетку из таблицы А. Смотрим ее значение в стоимостной таблице. Это будет сij. Далее смотрим, какие потенциалы соответствуют данной клетке. Вставляем их значения в формулу.

В программе Excel найдем оценки с помощью математических операторов и ссылок на соответствующие ячейки.

План считается оптимальным, если оценки больше или равны 0. В нашем случае получились отрицательные значения – план не является оптимальным. Поэтому двигаемся дальше.

Находим, какой клетке в таблице А соответствует минимальная оценка. Строим для этой клетки цикл – замкнутую ломаную линию. Условия: обязательно чередование вертикального и горизонтального направления, только по базисным клеткам.

В исходной клетке (с минимальной оценкой) ставим знак «+». Далее чередуем: «-», «+» и т.д.

В таблице стоимости находим минимальное значение со знаком «-».

В нашем примере – это «5», ячейка В1. Эту клетку нужно убрать из базиса. А ячейку с минимальной оценкой сделать базисной.

С учетом изменившихся данных вновь строим опорный план транспортной задачи. Применяем инструмент «Поиск решения». Пересчитанный план перевозок выглядит так:

Обратите внимание: ячейка I1 (где была минимальная оценка) стала базисной, занятой. Проводим те же расчеты для нового плана (с пункта №1): находим потенциалы, оценки свободных клеток для проверки оптимальности

И так до тех пор, пока оценки свободных клеток не будут больше или равны 0

Проводим те же расчеты для нового плана (с пункта №1): находим потенциалы, оценки свободных клеток для проверки оптимальности. И так до тех пор, пока оценки свободных клеток не будут больше или равны 0.

Полученное решение сохраняется в файле Word (Пример решения транспортной задачи). Также автоматически генерируется шаблон решения в Excel .

Решение матричной игрыС помощью сервиса в онлайн режиме можно определить цену матричной игры (нижнюю и верхнюю границы), проверить наличие седловой точки, найти решение смешанной стратегии методами: минимакс, симплекс-метод, графический (геометрический) метод, методом Брауна.

Задачи динамического программирования

вычеркивания (метод двойного предпочтения);
северо-западного угла;
минимального элемента;
аппроксимации Фогеля.