Функции и вычисления

(финансовые функции, " Подбор параметра" и " Поиск решения")

Финансовые функции. Более 50 различных функций предоставляет Excel для обработ­ки бухгалтерских ведомостей и банковских капиталов, для вычисления процентов по вкладам и займам, для работы с ценными бумагами. Рассмотрим некоторые часто используемые функции.

БС - будущая значение вклада с периодическими постоянными платежами и постоянной процентной ставкой. Синтаксис функции Бс: БС (ставка; кпер; плата; нз; тип), где 3 обяза-
тельных аргумента: -процентная ставка за период, - количество периодов выплат, - размер 1 выплаты;

и 2 необязательных: Нз - текущая (начальная) стоимость или общая сумма всех будущих платежей с настоящего момента (если аргумент опущен, то он равен 0);
Тип - 0 или 1 (0 - выплата в конце периода, 1 - в начале);
если аргумент опущен, то Тип = 0.

Пример 2. Какая сумма будет накоплена за 5 лет, если к начальному вкладу 10000 р. добавлять ежегодно по 3000 р. под 10% годовых?

Финансовые функции требуют согласования единиц измерения некоторых аргументов, например, числа периодов, процентной ставки и выплат (если выплаты ежемесячные, то ставка должна быть равна 1/12 от годовой).
Следует также помнить, что все денежные аргументы, которые означают выплаты налогов, депозитные вклады на банковские счета, платежи по кредитам, представляются отрицательными числами.

Рассмотрим эти функции: АПЛ - функция, определяющая амортиза­­цию
имущества за один период при равномерном распределении износа.
Её синтаксис: АПЛ (стоимость; остаток; период), где
стоимость - начальная стоимость имущества,
остаток - ликвидная стои­мость в конце периода аморти­зации,
период - количество периодов эксплуатации.

Пример 3. Вычислить ежегодную амортизацию автомобиля
за 8 лет с начальной стоимостью 20000 р. и ликвидной - 2500 р.

АСЧ - более полезный способ учёта износа, так как вычисляется амортизация для каждого
периода отдельно. Результат - амортиза­ция имущества для указанного периода.

Синтаксис: АСЧ (стоимость; остаточная_стоимость; время_эксплуатации; период), где

- стоимость - начальная стоимость имущества,

- остаточная_стоимость - остаточная стоимость в конце периода амортизации,

- время_эксплуатации - количество периодов, за которые собственность аморти­зи­руется (иногда называется периодом амортизации),

- период - заданный. период (измерен в тех же единицах, что и время полной амортизации).

Пример 4. Стоимость нового компьютера 32000 р. Время эксплуатации 8 лет. Остаточная стоимость 3000 р. Опр-лить годовую амортизацию в конце 1-го года и последнего.

АСЧ (32000; 3000; 8; 1) = 6444, 44 р. АСЧ (32000; 3000; 8; 8) = 805, 56 р.

Метод подбора параметра. Реализуется командой Подбор параметра, которая устанавливает режим итерационных вычислений для отыскания нужного решения при одном изменяю­­щемся параметре в заданной формуле. При этом начальное (" грубое") значение параметра должно быть известно. Метод итераций состоит в следующем: проверяется начальное значение функции, содержащей параметр, в " целевой" ячейке. Если это значение не дает нужную величину фун­­кции, то значение параметра изменяется и - следующий цикл. Для завершения вычислений задается точность и/или количество циклов-итераций (например, 100). Если точность за 100 ци­клов не достигнута, вычисления прекращаются, выдается результат и соответствующее сообщение.

Пример 5. Вычислить корень алгебраического уравнения
(2x² + 3)(1 - Sin x) = ln x.

Решение: - преобразовать уравнение так, чтобы в его правой части не содержалось неизвест­ных, т.е. перенести все x в левую часть; в правой части может быть константа или 0 (значение целевой функции);
- выбрать ячейку, например, С1, присвоить этой ячейке имя х и поместить в эту ячейку приближенное значение корня - для данного уравнения оно равно 1;
- в ячейку D1 ввести формулу целевой функции - левую часть преобразованного уравнения; вводить без пробелов: =(2 * x ^ 2 + 3) * (1 - Sin (x)) - LN (x);
- команда Данные-Анализ -”Что-если”- кнопка6-Подбор параметров-Уста­но­вить в ячейке - $D$1 - Значение -0- Изменяя ячейку x - OK.

Если в уравнении f(x) = 0 корень не один и известен интервал, в котором находятся все корни, то сначала можно определить их грубые значения, построив график f(x)для заданного диапазона x и отметив точки пересечения графика с осью x. А затем применить метод подбора параметра для каждого из корней. Например, при определении корней уравнения 2x² +7x - 19, 7311 = 0 (если известно, что корни лежат между -6 и +5) по таблице значений функции строится график, затем применяется метод подбора параметра для двух начальных значений корней: +2 и -5 и определяются точные значения корней: 1, 67757 и -4, 52374.

Метод подбора параметра в сочетании с встроенными функциями можно использовать для решения " обратных" задач.

Пример 6. Вычислить размер ежегодных выплат на валют­ный счёт под 8% годовых для накопления необходимой суммы в $25000 за 5 лет.

Подбор параметра в сочетании с функцией БС потребует выполнения несложных действий:

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

- установить курсор в ячейку В5,

- затем Подбор параметра-Устано­вить в ячейке -$B$5- Значение -25000- Изменяя ячейку $B$4 - OK.
Появится диалоговое окно Состояние подбора параметра с отчетом о результатах вычислений;

- выполнить OK, чтобы зафик­сировать результаты в ячейках рабочего листа.

Поиск решения. Более сложные задачи, в которых значение целевой функции зависит от нескольких параметров, а допустимые значения подчиняются некоторым ограничениям, требуют более сложных средств и методики обработки данных. Метод " Поиск решения" (ПР) позволяет решать такие задачи. Он также основан на итерационных методах, но имеет ряд отличий: - - поиск решения может одновременно использовать большое количество изменяемых данных;

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

- даёт оптимальное решение - наилучшее из возможных с учётом всех ограничений.

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

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

Пример 7. Магазин продаёт изделия трёх типов: И1, И2, И3 по ценам 300 р., 445 р. и 540 р. Изделия поставляются в магазин еженедельно в определённом количестве: И2 и И3 - 350 иэделий и И3 - 125. Существующие ограничения по таре не позволяют продавать > 500 изделий в неделю. Нужно определить оптимальное соотношение продаваемого товара для получения максимальной прибыли.

Прежде всего нужно расположить на отдельном рабочем листе необходимую информацию:

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

Для запуска ПР нужно выделить целевую ячейку, а затем выполнить ряд действий:

Поиск решения-Установить целевую ячейку (если она выделена, её имя уже набрано)-Равной-Максимальному значению (по умолчанию)-Изменяя ячейки (выделить В4: D4)-Добавить-1-е ограничение: B7< =125-Добавить-2-е….­
Добавить-3-е ограничение-ОК-Выполнить.

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

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

Сначала разместим на новом рабочем листе исходные данные (они ограничены синей рамкой:
выходные дни - А2: А9 и то же в другой форме - С2: I9, где 0 - выходной день, 1 - рабочий.

Ячейки С12: I12 содержат данные о минимальном количестве сотрудников на каждый день недели.

Ячейки В3: В9 - это предварительный вариант распределения персонала по дням недели; их значения будут играть роль переменных в целевой функции и уточняться в процессе поиска.

Затем - подготовительные вычисления:
в ячейке В11 общее количество сотрудников, в H13 - общая плата за неделю (5 раб. дней),
в диапазоне С11: I 11 - количество работающих по дням недели, исходя из предварительного графика (см. формулу с массивами).

И наконец, можно применить Поиск решения, задав целевую ячейку - H13, изменяемые
значения В3: В9, ограничения - В3: В9 целые положительные числа, а массив С11: I11 поэлементно > = массива C12: I12.

2. ЛАБОРАТОРНАЯ РАБОТА №1
Создание и редактирование таблиц. Функции и вычисления в Excel. Графические
возможности Excel