Вычисления в электронных таблицах Некоторые часто употребляемые функции Практические примеры. Ч 2. 1
Пример 4. Расчеты по вкладам. Построить таблицу вычисления прибыли от суммы 1000 (клетка В 1), на четыре года в некоторое производство или в банк. Предполагаемая, выплачиваемая ежегодно, норма прибыли должна составить 10%, 15%, 20%, 30% соответственно. Используемые табличные функции очевидны и реализуют выражение вида <прибыль>=<вклад>*<норма>. Способ вычисления дохода по вкладу здесь соответствует так называемым простым процентам. 2
Пример 5. Расчеты по вкладам (сложные проценты) Аналогичная задача, но предполагается, что инвестор не изымает прибыль, а реинвестирует ее снова в дело. Такой механизм исчисления дохода определяется термином «сложные проценты» или «капитализация дохода» . <прибыль>=<норма>*<предыдущая сумма на счету> <сумма >=<предыдущая сумма на счету >+<прибыль> 3
Финансовая функция БЗРАСПИС Значение нарастающей суммы можно выяснить и с помощью имеющейся в Excel финансовой функции вычисления будущего значения прибыли с изменяющейся процентной ставкой =БЗРАСПИС(<основной капитал>; <ставки>), где осн. кап. -текущая стоимость инвестиции, ставки - массив применяемых процентных ставок. <сумма>= БЗРАСПИС(<вклад>; <нормы предшествующих лет>) Если не требуется знать значения накопленных вкладов в конце каждого года, можно вообще ограничиться только одной формулой из последней ячейки. А если не нужна таблица, то и =1973, 4 4
Пример. Продуктовый расчет o o При планировании производственной программы, например, в пищевой промышленности, необходимо иметь полное представление об ожидаемых абсолютных потерях и объемномвесовом количестве продукта на каждом этапе обработки. Поскольку все пищевое производство (виноделие, консервирование, хлебобулочная промышленность) является многоступенчатым, контроль за этими параметрами здесь чрезвычайно актуален и осуществляется с помощью так называемого продуктового расчета. Перекачка отстой фильтрация выдержка разлив К 1 К 2 К 3 К 4 К 5 Типовая технологическая цепочка обработки виноматериалов 5
Обозначим: Пвх – количество продукта на входе всего процесса или его отдельного этапа, o Пвых – количество продукта на выходе, o К – коэффициент потерь на этапе обработки, o - объем потерь на этапе обработки в абсолютном исчислении. Отсюда можем записать рабочие формулы для каждого этапа: Пвых = Пвх - , где = К*Пвх. Задача может быть поставлена как прямая (известен объем сырья, нужно найти объем продукта), так и обратная (известен объем продукта, найти объем потребного для него сырья). o Обработка Пвх Пвых = К*Пвх 6
Прямая задача Заданы: o Исходное количество продукта (сырья) Пвх, o Количество этапов обработки N, o Коэффициенты нормативных потерь на каждом i-ом этапе – Кi от поступившего на данный вид обработки объема продукта. Вычислить абсолютные величины потерь на каждом этапе обработки i и итоговый выход продукта Пвых. 7
Обратная задача Задано: o потребное количество готового продукта Пвых, Вычислить абсолютные величины потерь на каждом этапе обработки i и необходимое количество исходного продукта Пвх. Рабочие формулы для каждого этапа обратной задачи: = Пвых*К/(1 -К) и Пвх = Пвых + . Как видим, в результате расчетов для обратной задачи получено число 100, т. е. вычисления выполнены верно. 8
Итоговые формулы Пвых = Пвх (1 -К 1)(1 -К 2)…(1 -КN), o Пвх = Пвых / (1 -К 1)(1 -К 2)…(1 -КN). Прямой продуктовый расчет похож на предыдущую задачу определения вклада для сложных процентов. Только там происходит наращивание вклада, а здесь уменьшение продукта. Это обстоятельство можно учесть, изменив на минус знаки при всех коэффициентах потерь. Тогда объем продукта на выходе получим воспользовавшись функцией =БЗРАСПИС(С 2; {-0, 05; -0, 11; -0, 02}) = 82, 86 Или (введя выражение как массив) =БЗРАСПИС(С 2; -{В 4: В 6)} = 82, 86 o 9
Подбор параметра Для решения обратной задачи можно воспользоваться таблицей прямого расчета, использовав функцию Подбор параметра из меню Сервис. Например, мы хотим выяснить сколько сырья требуется для выпуска 800 единиц продукции. Для этого в окне Подбор параметра нужно задать: Произведен перерасчет потерь и остатков на выходе для всех этапов технологического процесса. 10
Функция БЗ Возвращает будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки. = БЗ(ставка; кпер; плата; нз; тип) o Ставка — это процентная ставка за период, o Кпер — это общее число периодов выплат годовой ренты, o Плата — это выплата, производимая в каждый период; это значение не может меняться в течении всего периода выплат. Обычно плата состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если аргумент опущен, должно быть указано значение аргумента Нз. o Нз — это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента. Если аргумент нз опущен, то он полагается равным 0. В этом случае должно быть указано значение аргумента плата. o Тип — это число 0 или 1, обозначающее, когда должна производиться выплата. Если аргумент тип опущен, то он полагается равным 0. Тип Когда нужно платить 0 В конце периода 11 В начале периода
Примеры • =БЗ(0, 5%; 10; -200; -500; 1) равняется 2581, 40 р. • =БЗ(1%; 12; -1000) равняется 12 682, 50 р. • =БЗ(11%/12; 35; -2000; ; 1) равняется 82 846, 25 р. Предположим, необходимо зарезервировать деньги для специального проекта, который будет осуществлен через год. Предположим, предполагается вложить 1000 рублей под 6% годовых (что составит в месяц 6%/12 или 0, 5%) и вкладывать по 100 рублей в начале каждого следующего месяца в течение следующих 12 месяцев. Сколько денег будет на счете в конце 12 месяцев? =БЗ(0, 5%; 12; -1000; 1) равняется 2301, 40 р. 12
Амортизационные функции =АМР(стоимость; остаток; период) Возвращает величину непосредственной амортизации имущества за один период. o Нач_стоимость - это начальная стоимость имущества. o Ост_стоимость - это стоимость в конце периода амортизации (иногда называется остаточной стоимостью имущества). o Время_эксплуатации - это количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации). Пример. Предположим Вы купили за 30 000 руб. грузовик, который имеет срок эксплуатации 10 лет, после чего оценивается в 7 500 руб. Снижение стоимости для каждого года эксплуатации составит: =АМР(30000; 7500; 10) равняется 2 250 руб. 13
Функция АМГД =АМГД(стоимость; остаточная_стоимость; время_эксплуатации; период) Возвращает годовую амортизацию имущества для указанного периода. o Нач_стоимость - это начальная стоимость имущества. o Ост_стоимость - это стоимость в конце периода амортизации (иногда называется остаточной стоимостью имущества). o Время_эксплуатации - это количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации). o Период - это период (должен быть измерен в тех же единицах, что и время полной амортизации). Примеры. Если Вы купили грузовик за 30 000 руб. , который имеет срок эксплуатации 10 лет и остаточную стоимость 7 500 руб. , то годовая амортизация за первый год составит: =АМГД(30000; 7500; 1) равняется 4090, 91 руб. Годовая амортизация за десятый год составит: =АМГД(30000; 7500; 10) равняется 409, 09 руб. 14
Функция КПЕР =КПЕР(ставка; платеж; нз; бз; тип) Возвращает общее количество периодов выплаты для данного вклада на основе периодических постоянных выплат и постоянной процентной ставки. o Ставка - это процентная ставка за период. o Плата - это выплата, производимая в каждый период; это значение не может меняться в течении всего периода выплат. o Нз - это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента. o Бз - это будущая стоимость или баланс наличности, который нужно достичь последней выплаты. Если аргумент бз опущен, то предполагается, что он равен 0 (например, бз для займа равно 0). o Тип - это число 0 или 1, обозначающее, когда должна производиться выплата. Тип Когда нужно платить 0 или опущен В конце периода 1 В начале периода Примеры. =КПЕР(12%/12; -1000; 10000; 1) равняется 60 =КПЕР(1%; -1000; 10000) равняется 60 =КПЕР(1%; -100; 1000) равняется 11 15
Функция ПЗ =ПЗ(ставка; кпер; выплата; бз; тип) Возвращает текущий объем вклада. Текущий объем — это общая сумма, которую составят будущие платежи. Например, когда деньги берутся взаймы, заимствованная сумма и есть текущий объем для заимодавца. o. Ставка — это процентная ставка за период. Например, если получена ссуда под автомобиль под 10 процентов годовых и делаются ежемесячные выплаты, то процентная ставка за месяц составит 10%/12, или 0, 83%. В качестве значения аргумента ставка нужно ввести в формулу 10%/12 или 0, 83% или 0, 0083. o. Кпер — это общее число периодов выплат годовой ренты. Например, если получена ссуда на 4 года под автомобиль и делаются ежемесячные платежи, то ссуда имеет 4*12 (или 48) периодов. В качестве значения аргумента кпер в формулу нужно ввести число 48. o. Выплата — это выплата, производимая в каждый период и не меняющаяся за все время выплаты ренты. Обычно выплаты включают основные платежи и платежи по процентам, но не включают других сборов или налогов. Например, ежемесячная выплата по четырехгодичному займу в 10 000 руб. под 12 процентов годовых составит 263, 33 руб. В качестве значения аргумента выплата нужно ввести в формулу число -263, 33. o. Бз — требуемое значение будущей стоимости или остатка средств последней выплаты. Если аргумент опущен, он полагается равным 0 (будущая стоимость займа, например, равна 0). Например, если предполагается накопить 50000 руб. для оплаты специального проекта в течение 18 лет, то 50 000 руб. это и есть будущая стоимость. Можно сделать предположение о сохранении заданной процентной ставки и определить, сколько нужно откладывать каждый месяц. 16 o. Тип — это число 0 или 1, обозначающее, когда должна производиться выплата.
Пример функции ПЗ Предположим, что выкупается страховка, по которой выплачивается по 500 руб. в конце каждого месяца в течение 20 последующих лет. Стоимость ренты составляет 60 000 руб. и выплачиваемые деньги принесут 8 процентов годовых. Необходимо определить, будет ли это хорошим способом инвестировать капитал. Используя функцию ПЗ получаем, что настоящий объем вклада составит: =ПЗ(0, 08/12; 12*20; 500; ; 0) равняется -59 777, 15 руб, Результат получается отрицательный, поскольку он представляет деньги, которые необходимо выплатить. Настоящий объем вклада (59 777, 15 руб. ) меньше, чем запрашиваемая цена (60 000 руб. ). Следовательно, можно сделать вывод, что это не самый лучший способ инвестирования денег. 17
Скачать презентацию Вычисления в электронных таблицах Некоторые часто употребляемые функции
Примеры_2.ppt