Меню

Как решать таблицы подстановки

ТАБЛИЦЫ ПОДСТАНОВКИ

Таблицы подстановки

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

Задание 14. Таблица с одним входом

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

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

Рис. 6. 13. Таблица подстановки с одним входом

Воспользуемся тем же примером расчета, который рассматривали при подборе параметров. Для этого откройте лист «Задание 12».

На рис. 6.5. отображены результаты расчетов при цене товара, равной 1 руб. Создайте таблицу подстановки для следующих вариантов цены: 5, 10, 15, 20, 25 и 30 руб. Для этого нужно выполнить следующие операции:

В ячейки строки E9:J9 и столбца D2:D7 последовательно введите заданные варианты переменной (цены).

Следующие действия зависят от того, введены варианты в строку или в столбец:

Для таблицы подстановки, организованной в столбцы, следует:

В ячейку E2 скопировать формулу прибыли (из ячейки В8). Правее этой ячейки можно ввести другие формулы, например, расчета расходов (из В3 в F2) – рис. 6.17.

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

На вкладке Данные группы Работа с данными выберите команду из набора Анализ «что если» — Таблица данных(рис. 6.14).

Рис. 6. 14. Выбор команды подстановки таблицы данных

Появится диалоговое окно «Таблица подстановки» (рис. 6.15), в которые нужно ввести адрес ячейки ввода с формулой расчета прибыли.

Рис. 6. 15. Окно задания параметров в вертикальную таблицу подстановки

После нажатия кнопки ОК Excel поочередно подставит в ячейку ввода варианты переменных, а результатами расчетов заполнит соответствующие ячейки таблицы подстановки (рис. 6.17).

Для таблицы подстановки, организованной в строки, следует:

В ячейки D12 и D13 скопируйте формулу прибыли из ячейки В8 ( рис. 6.17).

Выделите диапазон ячеек, содержащий формулы и значения для подстановки (D11:J13).

На вкладке Данные группы Работа с данными выберите команду из набора Анализ «что если» — Таблица данных (рис. 6.14).

В диалоговом окне «Таблица подстановки» (рис. 6.16) введите адрес ячейки ввода с формулой расчета прибыли.

Рис. 6. 16. Окно задания параметров в горизонтальную таблицу подстановки

Рис. 6. 17. Результаты выполнения процедуры Таблица данных с одним входом

Для любой таблицы данных постройте график зависимости прибыли и расходов от цены (рис. 6.18). Для этого

Рис. 6. 18. График зависимости прибыли и расходов от цены (логарифмическая шкала)

Задание 15. Таблица с двумя входами

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

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

Рис. 6. 19. Таблица подстановки с двумя входами

Рис. 6. 20. Результаты выполнения процедуры Таблица подстановки с двумя входами

Для этого следует:

Подготовить на рабочем листе таблицу, в которую введите варианты переменных. В используемом примере (рис. 6.20), варианты цены размещены в столбец (ячейки I3:I8), а варианты количества – в строку (ячейки J2:M2).

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

Выделите всю таблицу с вариантами переменных и формулой (ячейки I2:M8).

На вкладке Данные группы Работа с данными выберите команду из набора Анализ «что если» — Таблица данных (рис. 6.14).

В диалоговом окне «Таблица подстановки» (рис. 6.21) введите адреса ячеек ввода с формулой расчета прибыли.

Рис. 6. 21. Окно задания параметров для таблицы подстановки с двумя входами

В поле «Подставлять значения по столбцам в» следует ввести адрес ячейки В1. В поле «Подставлять значения по строкам в» следует ввести адрес ячейки В2.

После нажатия кнопки ОК Excel поочередно подставит в соответствующие ячейки ввода все варианты переменных, а результаты расчетов введет в ячейки таблицы подстановки (рис. 6.20).

Для наглядности, по данным таблицы можно построить диаграмму поверхности (рис. 6.22), объединяющую все возможные варианты.

Рис. 6. 22. Диаграмма поверхности, иллюстрирующая все варианты таблицы с двумя входами (после форматирования)

НЕДОСТАТКИ АНАЛИЗА ДАННЫХ С ПОМОЩЬЮ ТАБЛИЦ ПОДСТАНОВКИ:

1. Одновременно можно анализировать данные только при изменении одного или двух исходных параметров.

2. Процесс создания таблицы подстановки интуитивно не всегда понятен.

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

Вышеперечисленные проблемы можно устранить, используя процедуру Excel — Сценарии.

Источник

Финансы в Excel

Главная Статьи Формулы Таблицы подстановки

Таблицы подстановки

Вложения:

tables2.xls [Таблицы подстановки] 42 kB

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

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

В файле-примере к данной статье построена микро-модель условного инвестиционного проекта. Анализ чувствительности проводится сначала для одного параметра — ставки дисконтирования. Во втором случае подставляется два параметра: ставка дисконтирования и сумма первоначальных инвестиций. Задача является типичной для проведения экономического анализа чувствительности. В условном примере вычисляния достаточно простые, поэтому вместо таблицы подстановки можно использовать стандартные формулы. В реальных экономических моделях вычисления гораздо сложнее, и реализовать матрицу чувствительности через формулы не удастся.

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

Затем следует выделить область таблицы, включая ячейку с формулой (в примере B10:C14), и вызвать диалог формирования таблицы подстановки. В Excel2007-2013 — через Данные \ Работа с данными \ Анализ «что-если» \ Таблица данных, в Excel 97-2003 через меню Data \ Table. В диалоге необходимо указать ячейку, в которую следует подставлять указанные в таблице параметры. В примере варианты ставки дисконтирования располагаются по строкам, поэтому заполняем поле диалога «Подставлять значения по СТРОКАМ в:». Указываем ссылку на ячейку с рабочей ставкой дисконтирования, которая применяется в основных расчетах — $B$4.

После закрытия окна будут заполнены значения NPV для разных ставок дисконтирования.

Похожие действия необходимо произвести в случае двухмерной таблицы подстановки (матрицы). В диалоговом окне, кроме ссылки на параметр в строках требуется заполнить поле «Подставлять значения по СТОЛБЦАМ в:». Там указываем ссылку на рабочую ячейку с начальными инвестициями — $B$3. В отличие от вектора при использовании матрицы ссылка на результат должна располагаться в верхнем левом углу таблицы.

Как же это работает? В момент проведения расчетов Excel последовательно подставляет значения из строк и/или столбцов заголовка матрицы к заданным в диалоге ячейкам. После завершения каждого цикла вычислений результат записывается в соответсвующую ячейку таблицы подстановки. Затем происходит переход к следующему параметру, и вычисления повторяются. И так до тех пор, пока не будут перебраны все варианты параметров.

Читайте также:  Как сделать сноску в Word снизу страницы за 2 минуты подробная инструкция

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

Считается, что таблицы подстановки — устаревшее интерфейсное средство, сохраняемое в Excel только в целях совместимости с предыдущими версиями. В частности, можно реализовать циклические вычисления при помощи макросов. Но, например, для проведения факторного анализа показателей в сложных экономических моделях вряд ли найдется способ проще.

Применение таблиц подстановки оправдано только в случае сложных расчетов. Если в конечном итоге требуется рассчитать одно- или двухпараметрическую формулу, то проще ее скопировать на всю матрицу результатов, используя абсолютные и относительные ссылки. Иными словами, если имеется возможность в эту ячейку ввести расчетную формулу целиком, то вероятнее всего для расчетной модели вообще не следует использовать таблицу подстановки. В нижней части файла-примера показано как таблица подстановки может быть заменена стандартной формулой.

Источник



Как решать таблицы подстановки

На этом шаге мы рассмотрим создание таблиц подстановки.

При работе с моделью «что-если» в определенный момент времени можно использовать только один сценарий (только один набор данных). Но что если необходимо сравнить результаты нескольких сценариев? Вот несколько вариантов решения подобной проблемы:

  • Отпечатать несколько копий рабочего листа с разными сценариями на каждом.
  • Скопировать модель на другие листы и расположить их окна на экране так, чтобы были видны все сценарии.
  • Вручную создать формулу, которая бы подытоживала значения из ключевых ячеек для каждого сценария.
  • Использовать команду Excel Данные | Таблица подстановки для автоматического создания итоговой таблицы.

Команда Данные | Таблица подстановки позволяет создавать удобные таблицы подстановки, которые позволяют проводить вычисления по формулам для одного из приведенных ниже случаев:

  • Имеется один набор данных для одной ячейки (одной переменной), на которую ссылаются несколько формул. В этом случае создается так называемая таблица подстановки с одним входом .
  • Имеются два набора данных для двух ячеек (две переменные), на которые ссылается одна формула. Создаваемая в этом случае таблица называется таблицей подстановки с двумя входами .

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

Создание таблицы подстановки с одним входом

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

Рис.1. Общий макет таблицы подстановки

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

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

Рис.2. Пример рабочего листа

Рассмотрим пример создания таблицы подстановки, в которой бы отражались значения, рассчитанные по формулам, находящимся в ячейках Размер ссуды, Месячная плата, Общая сумма, Общая сумма комиссионных , при изменении ставок от 7% до 9% с шагом 0,25%. На рисунке 3 показана заготовка таблицы подстановки для описанного примера. Строка 2 состоит из ссылок на соответствующие ячейки с формулами.

Рис.3. Подготовка к созданию таблицы подстановки с одним входом

Чтобы создать таблицу подстановки, выделите диапазон ячеек (для рассматриваемого примера G2:K11 ), а затем выберите команду Данные | Таблица подстановки . Появится диалоговое окно, показанное на рисунке 4.

Рис.4. Диалоговое окно Таблица подстановки

Вам необходимо определить ячейку листа, в которую должны подставляться исходные данные. Поскольку все исходные данные находятся в столбце, то адрес следует поместить в поле Подставлять значения по строкам в (для нашего примера следует ввести $D$7 ). Щелкните на кнопке OK , и Excel заполнит таблицу соответствующими результатами (рис. 5).

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

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

Создание таблицы подстановки с двумя входами

Таблица подстановки с двумя входами позволяет отобразить на экране результаты расчетов при изменении двух входных параметров. Макет для этого типа таблицы показан на рисунке 6.

Рис.6. Макет таблицы подстановки с двумя входами

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

Приведем пример таблицы подстановки с двумя входами. Это пример расчета эффективности проведения рекламной компании с помощью рассылки материалов по почте путем вычисления чистой прибыли после продажи (рис. 7).

Рис.7. Пример расчета чистой прибыли после проведения рекламной акции

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

  • Стоимость печатных материалов . Стоимость печати одного рекламного буклета. Цена изменяется в зависимости от количества: 0,20 — если количество экземпляров не превышает 200000; 0,15 — от 200001 до 300000 экземпляров; 0,10 — если больше 300000. Стоимость отпечатаннх материалов (в зависимости от их количества) определяется по фомуле:
    =ЕСЛИ(Разослано_материалов .
  • Почтовые расходы . Их стоимость фиксирована и составляет 0,32 за одно почтовое отправление.
  • Число респондентов . Количество ответов, которое предполагается получить. Оно определяется в зависимости от процента предполагаемых ответов и количества разосланных материалов. Формула для этой ячейки следующая:
    =Процент_ответевших*Разослано_материалов .
  • Доход на одного респондента . Это фиксированное значение. Компании известно, что за каждый заказ она получит прибыль 22.
  • Суммарный доход . Суммарный доход вычисляется по простой формуле, в которой величина дохода, полученного от одного заказа, умножается на количество заказов:
    =Доход_на_одного_респондента*Число_респондентов .
  • Суммарные расходы . По формуле, находящейся в этой ячейке, вычисляются суммарные расходы на рекламу, в которую входит стоимость печатных материалов и почтовых услуг:
    =(Стоимость_печатных_материалов+Почтовые_расходы)*Разослано_материалов .
  • Чистая прибыль . Определяется как разность суммарных доходов и суммарных расходов.

Создадим таблицу подстановки с двумя входами, которая позволит вычислить чистую прибыль при разных комбинациях количества разосланных рекламных материалов и предполагаемого процента полученных ответов. Расположите таблицу в диапазоне G4:O14 . Чтобы создать таблицу подстановки, выделите указанный диапазон и выполните команду Данные | Таблица подстановки . В поле Подставлять значения по столбцам в — введите имя ячейки Процент_ответивших , а в поле Подставлять значения по строкам в — имя ячейки Разослано_материалов . На рисунке 8 показан результат выполнения выше описанных действий.

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

По данным таблицы подстановки с двумя входами можно построить трехмерные диаграммы (рис. 9).

Рис.9. Пример трехмерной диаграммы

Файл с данным примером можно взять здесь.

На следующем шаге мы рассмотрим анализ данных с помощью средства Диспетчер сценариев .

Источник

ТЕМА 7. РЕШЕНИЕ ЗАДАЧ С ИСПОЛЬЗОВАНИЕМ ТАБЛИЦЫ ПОДСТАНОВОК.

Цель работы: освоить возможности модуля Таблица подстановок приложения Microsoft Excel в процессе решения финансово-экономических задач, требующих вычисления по одним и тем же формулам, но для различных серий данных.

Принцип использования таблицы подстановок состоит в том, что возможные значения одного или двух аргументов функции представляются в виде списка или таблицы. Для одного аргумента список исходных значений задается в виде строки или столбца таблицы, MicrosoftExcel подставляет эти значения в формулу (функцию), за­данную пользователем, а затем выстраивает результаты соответственно в строку или столбец.

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

Задание 1. Необходимо рассчитать таблицу умножения с помощью Таблицы подстановок.

Решение.В первую очередь определите формулу, по которой будут рассчитываться значения в таблице. Это формула х i*y i, где i обозначает расположение в таблице значения х и у. Далее задайте ячейки для х i и для y i пустые ячейки, которые позднее будут использованы в формуле. Для этого в ячейках А1 и А2 запишите х i и y i соответственно, ячейку В1 выкрасите в синий цвет, а В2 в желтый для наглядности (рис. 1.48). Эти ячейки будут использоваться при расчете в формуле в качестве х i и y i, так ячейки с текстом в расчетах использовать нельзя. Начальное содержимое данных ячеек может быть нулевым, так как они нужны для того, чтобы определить переменные, от кото­рых будет зависеть целевая формула.

Далее создайте таблицу, содержащую серию данных для расчета как это показано на рис. 1.7.1. По столбцу значения у, по строке — х. В ячейку В4 введите формулу для расчета всей таблицы =х i*y i (введите =В1*В2). Нажмите ОК. В ячейке В4 получите значение 0, так как пока ячейки В1 и В2 не содержат данных.

Рис. 1.48. Ввод расчетной формулы и выделение диапазона для таблицы подстановки

Рис. 1.49. Окно ТАБЛИЦЫ ПОДСТАНОВОК для задания необходимых ячеек

Выделите область таблицы, как это показано на рис. 1.49: строку с данными, столбец с данными, расчетную область и ячейку, содержащую формулу. Выполните команду меню ДАННЫЕ > ТАБЛИЦА ПОДСТАНОВКИ. Заполните параметры в появившемся диалоговом окне (рис. 1.49). Первое значение — Подставлять значения по столбцам в — должно содержать адрес ячейки с той пере­менной, вместо которой в целевую формулу будут подставляться значения из верхней строки таблицы подстановки. В данном случае вместо пере­менной из ячейки В1, то есть х i, последовательно будут подставлены в формулу значе­ния из интервала C4:L4 – х 1, х 2 … х 10. Аналогично, второе значение — Подставлять значения по строкам в — задает адрес ячейки той переменной (у i), вместо которой в целевую формулу будут подставляться значения из крайнего левого столбца таблицы подстановки (у 1, у 2 … у 10). В данном примере вместо переменной из ячейки В2 в формулу будут подставлены значения из интервала В5:В14. ОК.

Результат заполнения таблицы подстановки показан на рис. 1.50.

Рис. 1.50. Результат заполнения таблицы подстановки

Задание 2. Предполагается, что в конце года капиталовложения по проекту составят около 1280 тыс. руб. Ожидается, что за последующие 4 года проект принесет следующие доходы: 420, 490, 550, 590 тыс. руб. Рассчитайте чистую текущую стоимость проекта для различных норм дисконтирования и объемов капиталовложений.

Решение. На рабочем листе Excel представьтеисходные данные следующим образом (рис. 1.51).

Рис. 1.51 . Таблица расчета чистой текущей стоимости проекта.

В ячейку D3 поместите предполагаемую величину начальных затрат по проекту (1280 тыс. руб.) со знаком «минус». Это значение необходимо включить в список аргу­ментов функции НПЗ, так как чистая текущая стоимость рассчитывается на начало года, а капиталовложения будут осуществлены в конце года. В ячейки C9:F9 поместите различ­ные объемы капиталовложений. Для расчета чистой текущей стоимости возьмите значения процентных ставок 13%, 13,8%, 15%. В ячейку D9 с помощью мастера функций поместите формулу для расчета: =НПЗ (D2 , D3, D4, D5, D6, D7).

Для построения Таблицы подстановки выделите диапазон ячеек D9:H12, в меню ДАННЫЕ выберите команду ТАБЛИЦА ПОДСТАНОВКИ, и заполните диалоговое окно следующим образом:

Рис. 1.52. Диалоговое окно Таблицы подстановок

После нажатия кнопкиОК в ячейках Е10:Н12 появятся результаты расчета (рис. 1.51). Полученные значения представьте в виде гистограммы (рис. 1.53).

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

Очевидно, что максимальная величина чистой текущей стоимости достигается при минимальных капиталовложениях и минимальной ставке дисконтирования. Анализируя полученные результаты, можно отметить, что некоторые варианты дают практически одинаковую величину чистой текущей стоимости, например, при капиталовложениях 1310 тыс. руб. и норме дисконтирования 13,8% достигается та же величина NPV, что и при инвестициях размером 1270 тыс. руб. и ставке 15%.

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

В каких случаях возникает необходимость использовать Таблицу подстановок?

Каким образом необходимо выстроить данные для того, чтобы воспользоваться таблицей подстановок?

В какой ячейке рабочего листа записывается расчетная формула при проведении вычислений с помощью таблицы подстановок?

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ ПО ТЕМЕ 7

Задание. Государство стремится увеличить налоги, чтобы пополнить бюджет для выполнения социально-экономических и оборонных функций. Налогоплательщики в свою очередь не могут обеспечить выживание бизнеса при непомерном росте налогов. Определите оптимальное сочетание уровня рентабельности и налоговой ставки, при котором поступления в бюджет будут максимальными. На основе полученных данных постройте график, отображающий зависимость поступлений в бюджет от ставки налога и уровня рентабельности.

Примечания. Прибыль предприятия зависит от его рентабельности и определяется как произведение капитала на процент рентабельности (таблица 3 на рисунке 1.54).

Рис 1.54. Расчет поступлений в бюджет

Прибыль, идущая в качестве налога в бюджет, равна произведению суммы прибыли на налоговую процентную ставку. Остаток прибыли — это прибыль, оставшаяся после уплаты налога. Капитал сальдо определяется путем суммирования капитала за прошлый период и прибыли, оставшейся после уплаты налога за фактический период. Аналогичным образом определяется сумма поступлений в бюджет в последующие периоды. В таблице 1 необходимо получить суммы налоговых поступлений в бюджет при различных налоговых ставках и различном уровне рентабельности.

Задача 2. Создайте таблицу предполагаемых цен на основные продукты питания через 6, 12 и 18 месяцев (рис. 1.55). Инфляция составляет 10% в год. Цены на текущее число заданы. Цену товаров с учетом инфляции вычисляйте по формуле сложных процентов (функция БЗ). Расчет таблицы производите с помощью таблицы подстановок.

Рис. 1.55. Динамика цен на продукты питания

ТЕМА 8. ПРОГРАММА ОПТИМИЗАЦИИ «ПОИСК РЕШЕНИЯ»

Цель работы: используя возможности программы Microsoft Excel эффективно планировать экономическую деятельность (решать задачи оптимизации).

Задание. Завод выпускает аудио- и видеотехнику. Известно, что на складе имеется запас комплектующих изделий в количестве указанном в таблице. Для производства каждого вида изделий требуются определенные затраты комплектующих. Каждому типу изделий соответствует своя норма прибыли. При увеличении объемов производства происходит уменьшение удельной прибыли в связи с дополнительными затратами на сбыт.

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

Исходные данные приведены в таблице на рисунке 1.56.

Рис. 1.56. Бизнес-план производства продукции

Решение. Создайте в приложении Microsoft Ехсеl таблицу, как показано на рис. 1.56. Заполните имеющимися данными.

Рассчитайте данные по столбцу « расход по плану». Для этого сложите произведения нормы расходов ресурсов по каждому наименованию продукции и плана производства этих видов продукции. Т.е. формула для ячейки D13 будет выглядеть следующим образом: =$E$11*E13+$F$11*F13+$G$11*G13. Остальные ячейки столбца заполните самостоятельно. В результате получите значение 0 во всех ячейках, т.к. ячейки Е11:G11 не содержат данных.

Теперь необходимо записать формулы для расчета прибыли по каждому виду изделий и прибыли всего. По видам изделий прибыль рассчитывается путем умножения прибыли на единицу изделия на максимально возможное количество произведенной продукции, скорректированное на уменьшение коэффициента отдачи. Для ячейки Е19 формула будет выглядеть следующим образом: 75*МАКС(Е11;0)^$Н$17.

Прибыль всего равна сумме прибыли по каждому виду изделий.

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

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

Для вызова программы оптимизатора выберите команду меню Сервис, Поиск решения. Появилось Диалоговое окно Поиск решения. Поле Установить целевую ячейку служит для указания целевой ячейки, значение которой необходимо максимизировать, минимизировать или установить равным заданному числу. Эта ячейка должна содержать формулу. В данном примере это ячейка Е20.

Кнопка Равной служит для выбора варианта оптимизации значения целевой ячейки. В данном примере для максимизации прибыли нажмите кнопку Максимальному значению.

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

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

Поле Ограничения служит для отображения списка условий поставленной задачи. Заполните поле Ограничения как показано на рисунке 1.57. Неравенство $D$13:$D$17 =0 значит, что количество произведенной продукции не может быть меньше нуля.

Рис. 1.57. Диалоговое окно программы Поиск решений

После того, как все необходимые условия оговорены нажмите Выполнить. В окне Результаты поиска решения нажмите ОК (рис. 1.58).

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

Рис. 1.59. Рассчитанный бизнес-план производства продукции

В таблице в пустых ячейках появились значения, показывающие, что для получения максимальной прибыли в размере 14 917 р. в условиях ограниченного количества ресурсов необходимо произвести 160 телевизоров, 200 стереосистем и 80 акустических систем, при этом максимально эффективно будут использованы имеющиеся на складе комплектующие (рис. 1.59).

Задание 2. Клиент банка имеет инвестиционный капитал и желает получить от него максимальную прибыль при минимальном риске потери средств. Необходимо сформировать оптимальный портфель акций клиента.

Решение. В однофакторной модели Шарпа доходность портфеля определяется по формуле Rp=Rf+(Rm-Rf)*Bp,

где Rp – доходность портфеля, %,

Rf – доходность безрисковых активов, %,

Rm – доходность рынка, %,

Bp – Бета портфеля – показатель системного, рыночного риска портфеля.

где Wi – доля актива I в портфеле,

Bi – Бета i-й акции,

i – номер бумаги в списке портфеля,

n – количество бумаг в портфеле.

Риск портфеля определяется дисперсией доходности портфеля:

где Vp – дисперсия доходности портфеля,

Vm — дисперсия доходности рынка,

Vi – дисперсия доходности i-й бумаги.

Исходными данными для расчета характеристик портфеля являются доходность безрисковых активов (Rf) = 6%, доходность рынка (Rm) = 15%, дисперсия (риск) доходности рынка (Vm) = 3%, Бета каждой акции (Bi) — акция А = 0,80, акция В = 1,00, акция С = 1,80, акция D = 2,20, казначейские векселя = 0,00, остаточная дисперсия каждой акции (Vi) — акция А = 0,04, акция В = 0,20, акция С = 0,12, акция D = 0,40, казначейские векселя = 0,00.

Необходимо максимизировать доходность портфеля при ограниченном риске (дисперсии доходности портфеля):

Vp ,

где Vb – заданное инвестором ограничение риска портфеля в долях или процентах.

Минимизировать риск при заданном ограничении уровня доходности портфеля:

Rp ,

где Rb – заданное инвестором ограничение по уровню доходности портфеля в долях или процентах.

Все необходимые для решения формулы представлены в таблице на рисунке 1.60.

Рис. 1.60. Исходные данные для формирования эффективного портфеля ценных бумаг

Создайте аналогичную таблицу в приложении Microsoft Excel. Попытайтесь решить задачу вручную, подставляя значения в столбец Доля. Решение таким способом займет много времени.

Решите задачу с использованием команды Поиск решения. Для этого вызовите команду МЕНЮ СЕРВИС – ПОИСК РЕШЕНИЯ. В появившемся диалоговом окне оптимизатора заполните все необходимые поля (рис. 1.61).

Рис. 1.61. Диалоговое окно программы Поиск решения

Нажмите ВЫПОЛНИТЬ, СОХРАНИТЬ НАЙДЕННОЕ РЕШЕНИЕ, ОК.

Рис. 1.62. Рассчитанная таблица

В результате получили структуру портфеля ценных бумаг, при которой в условиях ограничения процента риска на уровне не более 7,1% процент доходности максимальный – 17% (рис. 1.62).

Теперь определите структуру портфеля, при которой в условиях ограничения доходности не ниже 16,4% риск будет наименьшим. Для этого вызовите команду ПОИСК РЕШЕНИЯ. Далее можно как в прошлом случае заполнить поля вручную, но можно воспользоваться заданной моделью. В окне поиск решения нажмите кнопку ПАРАМЕТРЫ. В новом окне – ЗАГРУЗИТЬ МОДЕЛЬ.

Рис. 1.63. Окно Загрузка модели

Укажите область $С$21:$C$29. ОК, ОК.

Рис. 1.64. Диалоговое окно программы Поиск решения после загрузки модели

Для решения каких задач используется процедура Поиска решения?

Какую ячейку называют «целевой»?

Для чего применяются ограничения в процессе поиска решений?

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ ПО ТЕМЕ 8

Задание. Необходимо сформировать портфель активов пенсионного фонда так, чтобы максимизировать прибыль и ликвидность и минимизировать риск (рис. 1.66).

В математической постановке задачи оптимального планирования портфеля активов требуется найти вектор активов (А), максимизирующий линейную форму прибыли портфеля:

Где Prf — прибыль системы портфелей как цель, критерий оптимизации (максимизации).

A — сумма фонда к размещению в портфеле активов,

n — количество типов активов в портфеле,

a — цифровое имя отдельного типа активов,

Aa — объем инвестиций в денежном выражении в отдельный тип активов в портфеле,

Da — доходность отдельного типа активов.

Ограничения Правил Инспекции НПФ:

А1>=30% — в государственные ценные бумаги инвестируется не менее 30% фонда,

Где Ra – коэффициент риска, Задаваемый правилами для каждой группы активов,

N2>=0,04 – для минимального значения норматива достаточности собственных средств,

Норматив достаточности собственных средств вычисляется по формуле:

где OwCp – собственный капитал компании,

N3>=1 – для минимального значения норматива соотношения фонда и его обязательств,

где L – современная стоимость обязательств пенсионного фонда.

Сумма процентных долей активов должна равняться 100%,

Аа >=0, запрет на отрицательные инвестиции, т.е. займы.

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

Нормативы Правил, устанавливающие нижние и верхние лимиты инвестиций в конкретные объекты или группы, вводятся в колонки Мин. и Макс.

В математическую модель эти значения вставляются программой как правые части ограничений-неравенств. Правила задают только два ограничения: в государственные ценные бумаги вложить не менее 30% средств портфеля и в каждый любой объект не более 10 % портфеля. Таким образом, установлены жесткие требования диверсификации активов в не менее, чем 10 объектов. Как правило, в колонки лимитов Фонды включают также собственные диверсификаторы или границы рынков и ресурсов.

Критерий оптимизации вычисляется в ячейке Итого по портфелю — Доход. Он подлежит максимизации.

Исходные данные таблицы по доходности финансовых инструментов (графа Доходность, %) можно считать условными, поскольку они изменяются даже в течение торговых сессий. Доходность инструментов должна быть сопоставима. Поэтому после прогноза номинальных ставок вычисляются годовые эффективные ставки, которые затем корректируются ставками налогообложения и темпами инфляции. Последние существенно отличаются для различных групп активов. Доходность валютных активов задается в рублевом эквиваленте. В графе Доход вычисляется ожидаемый годовой рублевый прямой доход по каждому инструменту. Суммарный доход по портфелю (критерий оптимизации) вычисляется в последней строке. Исходные данные в колонку Коэф. риска, % вводятся по группам из таблицы Нормативы размещения пенсионных активов. В следующей колон­ке вычисляются риски в стоимостном выражении, т. е. возможные потери активов в миллиардах рублей.

В разделе Нормативы деятельности фонда в колонки Мин. и Макс, вводятся предельные значения нормативов: портфельного риска (N1), покры­тия риска собственным капиталом (N2) и балансовой ликвидности Фонда

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

Источник