 |
Финансовые функции электронных таблиц для вычисления будущего значения
|
|
|
|
Для решения задачи из примера 1 в Excel можно использовать функцию БС (будущая сумма). В программе Calc, входящей в состав пакета программ OpenOffice.org, эта функция называется FV (Futurum Value). Ее синтаксис
=БС(Норма;Кпер;Плт;ПС;Тип)
Аргументы функции БЗ:
Норма – процентная ставка для периода;
Кпер – число периодов (лет, месяцев, кварталов);
Плт – промежуточные выплаты за весь срок предоставления ссуды. (Если таких выплат нет, этот аргумент можно опустить);
ПС(приведенная сумма) -начальное значение, размер ссуды;
Тип – аргумент вводится, если есть аргумент Выплаты, иначе опускается. Аргумент Тип равен единице, если промежуточные выплаты проводятся в начале периода и нулю (или опускается), если выплаты проводятся в конце периода.
Особенностью всех финансовых функций является результат со знаком минус, если сумму придется отдавать, и со знаком плюс, если это сумма, которую получают.
Пример 2. Используя функции электронных таблиц, решить задачу примера 1.
Решение
Введем в любую ячейку Excel формулу: =БС(20%;1;;700000).
В результате в ячейке вычисляется значение –840000 р. Знак минус показывает, что эта сумма подлежит возврату.
При работе с программой Calc формула имеет вид: =FV(20%;1;;700000).
Примечание. Следует отметить, что функцию БС (FV) можно использовать для проведения операций по схеме простых процентов только в том случае, когда число периодов равно единице.
Пример 3. Используя функции электронных таблиц, вычислить накопленную сумму в задании 6.2.
Решение
Введем в любую ячейку Excel формулу:
=БС(10%;1;;-5000).
Обратите внимание! В качестве начального значения введено число 5000 со знаком минус, так как эта сумма отдана клиентом банку. Результатом будет значение 5500 р. со знаком плюс, поскольку эта сумма будет получена клиентом.
|
|
|
При работе с программой Calc формула имеет вид: =FV(10%;1;;-5000).
Задание 6.3. Используя функции электронных таблиц, вычислить, какую сумму накопит клиент, поместивший в банк 35000 руб. под 15% годовых, простые проценты.
____________________________________________________________________
Сложные проценты
В этом случае в договорах указывается годовая ставка r и количество периодов начисления процентов. Начисление производится каждый раз на наращенную в прошлом периоде сумму. Тогда сумма долга за n периодов будет равна
S= P(1+r)n. (6.4)
Для вычисления по схеме сложных процентов в Excel используется функция БС, а в программе Calc - функция FV.
Пример 4. Кредит в 100 000 руб. предоставили на три года под 20 % годовых с начислением по схеме сложных процентов. Какую сумму придется возвращать?
Решение
Р=100 000 руб, r=20 %, n=3 года.
S= P(1+r)n=100000(1+0,2)3=172 800 (руб).
Задание 6.4. В банк на 2 года помещена сумма в $ 2000 под 10% годовых, сложные проценты. Какая сумма будет на счету у вкладчика через год?
_________________________________________________________
Пример 5. Используя функции электронных таблиц, вычислить накопленную сумму в задании примера 4.
Решение
Введем в любую ячейку Excel формулу:
=БС(20%;3;;100000).
При работе с программой Calc формула имеет вид: =FV(20%;3;;100000).
Задание 6.5. Используя функции электронных таблиц, вычислить накопленную сумму в задании примера 4.
_________________________________________________________
Пример 6. Ссуда в 10 000 долларов дана в долг на 2 года под 12 % годовых с ежеквартальным начислением. Какова будет сумма накопленного долга? Расчеты провести аналитически и в ЭТ.
Решение
1. Аналитические расчеты
P=$10 000, r=12 %, n=2 года.
Так как процентная ставка дана годовая, а период начисления процентов – квартал, сначала рассчитаем процентную ставку за период, она равна
|
|
|
.
Тогда число периодов (кварталов) равно 2Ÿ4=8. Накопленный долг равен
S= P (1+ rn) n =10000(1+0,03)8=12 666,7 (долларов).
2. Решение в Excel.
Вводим в любую ячейку формулу: =БС(12%/4;2*4;;-10000). Результат: 12666,7.
Задание 6.6. Ссуда в размере 30 000 долларов дана на три года под ставку 32 % годовых с ежеквартальным начислением. Определить сумму конечного платежа. Расчеты провести аналитически и в ЭТ.
_________________________________________________________
____________________________________________________________________
____________________________________________________________________
6.2.4. Финансовые функции для вычисления текущих значений
Теперь рассмотрим, как по будущему значению определить текущее (современное) значение. В этом случае в Excel используется финансовая функция ПС (приведенная сумма). В программе Calc, входящей в состав пакета программ OpenOffice.org, эта функция называется РV (Present Value). Функция имеет синтаксис
=ПС(Ставка;Кпер;Плт;БС;Тип).
Здесь БС – будущая сумма.
Пример 7. Вкладчик собирается положить деньги в банк на четыре года под 25 % годовых и накопить 15 000 руб. Какую сумму он должен вложить?
Решение
S =15 000 руб., n =4, r =25 %.
Из уравнения (6.4) найдем 
(руб).
Задание 6.7. Семья собирается за два года накопить 60000 руб., поместив деньги в банк под 10% годовых, сложные проценты. Какую сумму нужно поместить в банк?
_________________________________________________________
Пример 8. Используя функции электронных таблиц, вычислить вкладываемую сумму в задании примера 7.
Решение
Введем в любую ячейку Excel формулу:
=ПС(25%;4;;15000).
При работе с программой Calc формула имеет вид: =РV(25%;4;;15000).
Результатом вычислений будет значение -6144 р.Знак минус показывает, что данную сумму клиент помещает в банк.
Задание 6.8. Используя функции электронных таблиц, вычислить вкладываемую сумму в задании 6.7.
_________________________________________________________
6.3. Планирование производства. Балансовая модель
Одна из серьезнейших задач, стоящих перед любым управляющим и экономистом – на основе анализа деятельности предприятия за прошлый период осуществить планирование его деятельности в следующем периоде.
Рассмотрим задачу планирования производства на примере балансовой модели.
|
|
|
Экономическая система состоит их трех отраслей. Объемы производства каждой из отраслей за предыдущий период, текущее производственное потребление в отраслях, а также прогнозируемый конечный спрос на продукцию каждой из трех отраслей приведены в табл. 5. Определить конечную продукцию каждой из отраслей за предыдущий период и план выпуска продукции в следующем периоде, считая, что технология производства не изменилась.
Таблица 5
| Отрасли | Объемы производства отраслей | Производственное потребление отраслей за предыдущий период | Прогнозируе-мый конечный спрос | ||
1. Математическая постановка задачи
Для решения поставленной задачи можно использовать балансовую модель Леонтьева. Она представляет собой систему уравнений, каждое из которых выражает требование равенства (баланса) между количеством продукции, производимой отдельным экономическим объектом, и совокупной потребностью в этой продукции. В рассматриваемой задаче экономическая система состоит из трех отраслей.
Обозначим:
Хi - суммарный выпуск продукции отрасли i;
xij - количество продукции отрасли i, необходимое для того, чтобы отрасль j произвела Xj единиц своей продукции;
Yi - количество продукции отрасли i, оставшееся для внешнего потребления (конечная продукция).
Тогда взаимосвязь отраслей в процессе производства и потребления отдельного продукта Хi (i =1, 2, 3) может быть описана в виде следующих уравнений:
(6.5)
Используем понятие технологических коэффициентов aij:
- количество продукции отрасли i, необходимое для того, чтобы отрасль j произвела одну единицу своей продукции.
Тогда xij = aijXj и система уравнений (6.5) будет иметь следующий вид:
(6.6)
Или в матричной форме
Х = АХ + Y, (6.7)
где 
- матрица прямых затрат;
Х - вектор-столбец выпуска продукции в предыдущем периоде 
;
Y - вектор-столбец конечного спроса в предыдущем периоде 
.
Решим уравнение (6.7) относительно Х:
Х-АХ=Y,
отсюда,
Х (Е-А) = Y, (6.8)
где Е – единичная матрица. Из уравнения (7.8) получаем
|
|
|
Х =(Е-А)-1 Y. (6.9)
2. Условие решения задачи (проверка продуктивности матрицы)
Для того чтобы система уравнений (6.7) имела единственное неотрицательное решение при любом векторе спроса Y, необходимо, чтобы матрица А была продуктивной. Экономический смысл продуктивности состоит в том, что существует такой план выпуска продукции, который каждая отрасль сможет реализовать при существующей технологии производства. Математически для продуктивности матрицы А необходимо и достаточно, чтобы сумма элементов каждого из столбцов матрицы А была положительна и строго меньше единицы.
3. Реализация балансовой модели в электронной таблице
Компьютерная реализация балансовой модели в ЭТ показана в табл. 6 (режим показа формул) и в табл. 7 (режим вычислений).
Таблица 6
| A | B | C | D | |
| БАЛАНСОВАЯ МОДЕЛЬ | ||||
| Объём производ- ства | Потребление отраслей | |||
| Вычисле-ние техноло-гических коэффи-циентов | =В4/А$4 | =С4/А$5 | =D4/А$6 | |
| =В5/А$4 | =С5/А$5 | =D5/А$6 | ||
| =В6/А$4 | =С6/А$5 | =D6/А$6 | ||
| Проверка продуктивности матрицы А | ||||
| =СУММ(В7:В9) | =СУММ(C7:C9) | =СУММ(D7:D9) | ||
| =ИЛИ(В10>=1;С10>=1;D10>=1) | =ЕСЛИ(А11=”ИСТИНА”;"Решения нет"; "Матрица продуктивна") | |||
| Единичная матрица | ||||
| Вычисле- ние Е-А | =В12-В6 | =C12-C6 | =D12-D6 | |
| =В13-В7 | =C13-C7 | =D13-D7 | ||
| =В14-В8 | =C14-C8 | =D14-D8 | ||
| Вычисление обратной матрицы | =МОБР(В15:D17) | =МОБР(В15:D17) | =МОБР(В15:D17) | |
| =МОБР(В15:D17) | =МОБР(В15:D17) | =МОБР(В15:D17) | ||
| =МОБР(В15:D17) | =МОБР(В15:D17) | =МОБР(В15:D17) | ||
| Спрос на будущий период | План выпуска продукции | =МУМНОЖ(В18:D20;В21:В23) | ||
| =МУМНОЖ(В18:D20;В21:В23) | ||||
| =МУМНОЖ(В18:D20;В21:В23) |
Обратите внимание!
1. В программе Calc вместо функции МОБРиспользуется MINVERSE, а вместо функции МУМНОЖ - MMULT.
2. При работе с матричными операциями следует выделить диапазон ввода, набрать имя функции, ее аргументы и одновременно нажать три клавиши:Ctrl + Shift + Enter.
Таблица 7
| A | B | C | D | |
| БАЛАНСОВАЯ МОДЕЛЬ | ||||
| Объём производства | Потребление отраслей | |||
| Вычисление технологических коэффициентов | 0,417 | 0,1 | 0,2 | |
| 0,25 | 0,5 | |||
| 0,3 | 0,5 | |||
| Проверка продуктивности матрицы А | ||||
| 0,667 | 0,900 | 0,700 | ||
| ЛОЖЬ | Матрица продуктивна | |||
| Единичная матрица | ||||
| Вычисление Е-А | 0,583 | -0,1 | -0,2 | |
| -0,25 | 0,5 | |||
| -0,3 | 0,5 | |||
| Вычисление обратной матрицы | 2,113 | 0,930 | 0,845 | |
| 1,056 | 2,465 | 0,423 | ||
| 0,634 | 1,479 | 2,254 | ||
| Спрос на будущий период | План выпуска продукции | 8619,72 | ||
| 8309,86 | ||||
| 10985,92 |
|
|
|
Пример 9. Рассчитать конечную продукцию отраслей для данных табл.5.
Решение
Y1 = 600 – 250 – 100 - 160 = 90
Y2 = 1000 – 150 – 500 – 0 = 350
Y3 = 800 – 0 – 300 – 400 = 100
Задание 6.9. Рассчитать конечную продукцию отраслей для данных следующей таблицы:
| Отрасли | Объемы производства отраслей | Производственное потребление отраслей за предыдущий период | Прогнозируе-мый конечный спрос | ||
____________________________________________________________________
____________________________________________________________________
Пример 10. Проверить продуктивность матрицы прямых затрат.
Решение
Матрица продуктивна, если суммы элементов ее столбцов положительны и строго меньше единицы. Суммы элементов каждого столбца заданной матрицы А соответственно равны:
Следовательно, матрица А продуктивна, отрасли могут справиться с выпуском продукции без изменения технологии.
Задание 6.10. Проверить продуктивность матрицы прямых затрат
______________________________________
______________________________________
______________________________________
______________________________________
6.4. Оптимизация управленческих задач
На практике часто приходится искать наилучшее (оптимальное) решение, например дающее предприятию максимальную прибыль или обеспечивающее минимальные затраты. На компьютере подобные задачи можно решать, используя имеющуюся в табличных процессорах программу Поиск решения.
Для работы с данной программой необходимо предварительно построить математическую модель задачи и определить ограничения решения.
Транспортная задача
Фирме необходимо организовать перевозку продукции с трех складов в пять магазинов. Сведения о наличии продукции на складах, о потребности в этой продукции у магазинов и о стоимости перевозки единицы продукции с каждого склада во все магазины приведены в табл. 8.
Таблица.8
| Склады | Магазины | |||||
| М1 | М2 | М3 | М4 | М5 | ||
| Номер склада | Запас | Стоимость перевозок | ||||
| S1 | ||||||
| S2 | ||||||
| S3 | ||||||
| Потребности магазинов | ||||||
Считая, что можно везти продукцию с любого склада в любой магазин, следует так организовать перевозки, чтобы затраты были минимальными.
Решение задачи включает три этапа:
1. Построение математической модели.
2. Построение начального плана решения.
3. Оптимизация начального плана.
1. Построение математической модели
а). Ввод обозначений.
Обозначим:
Хij – количество продукции, отправляемой со склада i в магазин j:
Cij – стоимость перевозки единицы продукции со склада i в магазин j.
б). Определение ограничений.
На переменные накладываются три ограничения:
Первое. Исходя из физического смысла задачи, количество и стоимость продукции не может быть отрицательной величиной, то есть
Хij ³ 0; Cij ³ 0. (6.10)
Второе. Ограничения по предложению (со складов нельзя вывезти продукции больше, чем там имеется):
(6.11)
Третье. Ограничения по спросу (в магазины следует завезти не меньше продукции, чем им требуется):
(6.12)
в). Определение целевой функции
Общая стоимость перевозок (целевая функция) равна
(6.13)
Необходимо определить такие неотрицательные значения переменных Х, которые удовлетворяют условиям (6.10), (6.11) и (6.12) и обращают в минимум целевую функцию Z (6.13).
г). Проверка баланса.
Необходимым и достаточным условием разрешимости транспортной задачи является условие баланса
, (6.14)
где 
- суммарное количество продукции на складах
(при этом Si = 
- количество продукции на одном складе, i =1, 2, 3);
- суммарное количество продукции, требуемое в магазинах
(при этом 
- количество продукции, которое требуется j- му магазину, j =1, 2, …,5)
.
В нашем случае
;
=60,
следовательно, задача с балансом.
Задание 6.11. Определите суммарные затраты на перевозки, если с каждого склада в каждый магазин перевозится одна единица продукции.
______________________________________________________________________
______________________________________________________________________
__________________________________________________________________
__________________________________________________________________
2. Построение начального плана решения
Поскольку построение оптимального плана решения сразу невозможно, запланируем перевоз с каждого склада в каждый магазин одной единицы продукции.
Начальный план решения, реализованный в электронной таблице, приведен в табл. 9 (режим показа формул) и в табл. 10 (режим показа вычислений).
Таблица 9
Таблица 10
3. Оптимизация начального плана
Используем режим Поиск решения.
1) После выполнения команд Сервис, Поиск решения открывается диалоговое окно Поиск решения (для Excel оно представлено на рис.1, а для Calc на рис.2).
Рис. 1
Рис. 2
2)Ввести данные:
Установить целевую ячейку В17;
Равной минимальному значению;
Изменяя ячейки C11:G13.
Для ввода ограничений щелкнуть по кнопке Добавить. Появится окно Добавление ограничений (рис.3). Ввести первое ограничение. Для этого заполнить поля:
Рис. 3
а ) Ссылка на ячейку: C11:G13;
В среднем поле выбираем знак неравенства ³
Ограничение: 0, щелкнуть по кнопке Добавить.
б) В появившемся новом окне Добавление ограничений:
Ссылка на ячейку: C11:G13;
В среднем поле выбираем цел, щелкнуть по кнопке Добавить.
В программе Calc это ограничение не добавляется. На этом шаге его пропускаем.
Аналогично п. а) ввести следующие ограничения.
В11:В13 £ B5:В7;
С15:G15 ³ C9:G9.
После ввода каждого ограничения щелкнуть по кнопке Добавить, после ввода последнего – по кнопке ОК.
3 ) Для запуска режима Поиск решения щелкнуть по кнопке Выполнить (в программе Сalc – кнопка Решить).Появится окно Результаты поиска решения в Excel (а в программе OpenOffice.org Calc - окно Message Dialog). Щелкнуть по кнопке ОК. В результате улучшения плана получим оптимальный план (табл.11) стоимости перевозок с целевой функцией (стоимостью перевозок) Z =121.
Таблица 11
Рис. 3
|
|
|
