Тема 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 – х₁, х₂ … х₁₀. Аналогично, второе значение — Подставлять значения по строкам в — задает адрес ячейки той переменной (у_i), вместо которой в целевую формулу будут подставляться значения из крайнего левого столбца таблицы подстановки (у₁, у₂ … у₁₀). В данном примере вместо переменной из ячейки В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

Задача 1.

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

Примечания. Прибыль предприятия зависит от его рентабельности и определяется как произведение капитала на процент рентабельности (таблица 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<=$C$13:$C$17 означает, что расход комплектующих на производство не может превышать запасы комплектующих на складе, $Е$11:$G$11>=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.

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

Rp → max,

Vp <= Vb,

,

Wi >= 0,

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

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

Vp → min,

Rp <= Rb,

,

Wi >= 0,

где 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. Диалоговое окно программы Поиск решения после загрузки модели

Рис 1.65. Сформированный портфель ценных бумаг

Контрольные вопросы:

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

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

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

 

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

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

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

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

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

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

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

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

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

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

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

Aa <=10% - в любой другой проект не более 10% средств фонда,

N1<=0,5 – для максимального значения норматива соответствия,

Норматив соответствия вычисляется как отношение риска потери активов к сумме активов:

 

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

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

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

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

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

N3 = А/L,

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

Технологические ограничения:

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

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

 

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

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

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

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

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

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

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

Показатели экономики фонда, млрд. руб.	Лимиты, %		План, прогноз
Мин.	Макс.
Минимальная оплата труда, т.р. в месяц
Собственные средства компании
Уставной капитал компании (не менее 50000мин зарплат)	0,4
Обязательства фонда (современная стоимость)
Активы к размещению
Группы активов	Мин.	Макс.	Структура портфеля, %	Портфель, план, млрд.р.	Коэф.риска	Риски, млрд.р.	Доходность,%	Доход, млрд.р.
Средства на р/с в банке (А7)				=структура портфеля*активы к размещению		=коэф.риска*потфель, план		=коэф.доходности*портфель,план
Гос ц.б. (А1)
ГКО
КО
ОГСЗ
Казначейские векселя США (дисконт)
Местные ц.б. (А2)
МКО, Москва
ОКО, Челябинск
Банковские вклады (А3)
Депозиты в КБ Роскредит
Депозиты в КБ Автобанк
Другие ц.б. (А4)
Акции АКБ "Оптбанк"
Акции OLIVETTI,Италия
Недвижимость (А5)
Гаражи в Москве
Квартиры в Москве
Катера прогулочные, Крым
Валютные ценности (А6)
Доллар США
Золото
Итого по портфелю
Нормативы деятельности фонда и компании	Мин.	Макс.		План
Нормативы соответствия, N1 (риски активов/активы)
Достаточность собствен-ных средств, N2, (собств средства/риски активов)
Отношение активов Фонда к обяз-ствам, N3
Рис. 1.66. Оптимальный план портфеля активов НПФ

⇐ Предыдущая12345678910Следующая ⇒

Поделиться:

Воспользуйтесь поиском по сайту: