Проект - Релавэкспо;doc

1
Лабораторная работа № 1
Основные финансовые функции Excel
Рассмотрим некоторые примеры использования пяти основных финансовых
функций. Ниже приводится синтаксис этих функций (полужирным шрифтом выделены
обязательные аргументы):
• ПС (ставка; кпер;плт;бс;тип)
• БС (ставка; кпер;плт;пс;тип)
• ПЛТ (ставка; кпер;пс;бс;тип)
• СТАВКА (кпер; ставка; пс;бс;тип;предположение)
• КПЕР (ставка; плт;пс;бс;тип)
Для эффективного использования этих функций необходимо различать три
основных понятия:
• обозначение денежных потоков положительными или отрицательными числами;
• временное изменение количества денег;
• эквивалентность процентных ставок.
Оснавная терминология
• Текущее значение (ПС) - это приведенная стоимость. Если вы помещаете в банк 5 000
р. на депозитный вклад, то эта сумма представляет начальную сумму или текущее
количество денег, которые вы вложили. Если вы занимаете 15 000р. для приобретения
автомобиля, эта сумма представляет приведенное или текущее значение займа.
Текущее значение может быть положительным или отрицательным.
• Будущее значение (БС)- это начальная сумма плюс проценты. Если вы вкладывае $5
000 на пять лет при 6% годовых, то получите $6 312,38 в конце пятилетнего срока. Это
будущее значение вашего вклада с начальным значением $5 000. Если вы берете
трехгодовой заем $15 000 для покупки авто и платите 7% годовых, то выплачиваете
общую сумму $16 673,16. Эта сумма представляет начальную сумму плюс
выплаченный процент. Будущее значение может быть положительным и
отрицательным.
• Платеж (ПЛТ) - это сумма одноразовой выплаты или одноразовой выплаты плюс
проценты. Если вы вкладываете $100 ежемесячно на сберегательный счет, то платеж с
ставляет $100. Если вы ежемесячно выплачиваете $825, то платеж составлен из
выплаты основной суммы и процентов.
• Процентная ставка - это процентное выражение начальной суммы, обычно имеющее
ежегодную основу. Например, вы можете ежегодно получать 5,5 % с банковского де~
зитного вклада. Или вы можете взять заем со ставкой 7,75 %.
• Период - представляет время, на протяжении которого проводятся выплаты или
получение процентов со счета. Например, банковский депозитный вклад с
ежеквартальной выплатой процентов или заем для покупки авто, который требует
ежемесячных платежей.
• Срок - это общее количество времени действия вклада или погашения займа. 12-ти
месячный банковский депозитный счет рассчитан на один год. 30-летний заем рассчитан на 30-летний срок.
2
Анализ денежных потоков
Имея дело с финансовыми функциями Excel, важно понимать то, как «представлять»
денежные потоки, идущие в обоих направлениях. Другими словами: вы используете знаки
«плюс» и «минус»?
Чтобы решить финансовые задачи, используя финансовые функции Excel, нужно
выполнить два предварительных действия:
1. Определить владельца денежных средств. Например, в простой задаче
накопления, вы смотрите на нее с точки зрения вкладчика или банка? В задаче
займа вы должник или кредитор? При вычислении стоимости ряда будущих
выплат, являетесь вы покупателем (выплата за приобретенный товар) или вы
продавец (получение выплат за проданный товар)?
2. Определить, поступают к вам денежные средства (знак «плюс») или уходят от
вас (знак «минус»).
Определившись с описанными выше категориями, вы сможете использовать
финансовые функции Excel для проведения эффективных финансовых расчетов и
правильно интерпретировать возвращаемые результаты.
Обычно поступающие к вам деньги отмечаются знаком «плюс». Уходящие от вас
деньги имеют знак «минус». Например, если задача приведенной стоимости решается в
пользу отрицательного значения, то это означает, что сумма предоставляется вами. Если
значение положительное, то деньги вам одалживаются. Давайте рассмотрим пример
вычисления платежей вклада. Если вы берете деньги взаймы, то подсчитанные выплаты
имеют знак «минус» (который указывает на то, что вы каждый период выплачиваете
определенную сумму). Вычисляя процентную ставку займа, необходимо правильно
определить направление выплат. Иначе Excel предположит, что все выплаты происходят
в одном направлении и породит ошибку. Например, формула может показать значение
#ЧИСЛО!, которое указывает на бесконечно высокую норму прибыли (деньги приходят
к вам, при этом вами ничего не выплачивается).
Задачи накопления
Рассмотрим примеры, которые демонстрируют простые операции накопления.
Пример 1
Сколько накопится на счету в 10 000 рублей no истечении трех лет при 7%
годовых?
Вычисление накопления на рабочем листе Excel выглядит следующим
образом:
3
Рис.1
Используемая функция: БС (ставка; кпер; плт; пс; тип) Эта формула возвращает
12 250,43р.
=БС(7%;3;0;-10000;0), на рабочее листе аргументами функции служат ячейки.
Обратите внимание, что эта задача рассматривается с точки зрения вкладчика.
Таким образом, начальный вклад (пс) имеет отрицательное значение. Регулярные
выплаты не производятся, так что аргумент плт равен 0. Без выплат тип аргумента
несущественен.
Пример 2
Если вклад с $1000 увеличился до $2000 за 8 лет, то какой была средняя годовая
ставка?
Используемая функция: CTABКА(кпер;плт;пс;бс;тип;предположение). Эта функция
возвращает значение 9,050773%.
=СТАВКА(8;0;-1000;2000;0)
Этот пример рассматривается с точки зрения вкладчика, таким образом аргумент пс
отрицательный, а аргумент бс (получаемые средства) положительный. Поскольку срок
выражался в годах, полученное значение – эффективная годовая ставка.
Пример 3
У меня на депозитном счету 100 000 рублей, вложенные под 14% годовых. Сколько
времени потребуется для того, чтобы я стал миллионером?
Используемая функция: КПЕР(ставка;плт;пс;бс;тип)
Эта формула возвращает 17,573
=КПЕР(14%;0;-100000;1000000;0)
Этот пример рассмотрен с точки зрения вкладчика. Таким образом, аргумент пс
отрицателен и аргумент бс (поступление 1 млн.рублей) положителен. Поскольку ставка
представляется в годовых процентах, то эффективный срок тоже выражается в годах.
Пример 4
У меня на депозитном счету 10573,45 рублей, положенные под 1% ежемесячно.
Счет открыт 12 месяцев назад. Каков начальный вклад?
Используемая функция: ПС ( ставка;кпер;плт;бс;тип)
4
Эта формула возвращает -9383,40
=ПС(1%; 12;0;10573,45;0)
Без регулярных выплат аргумент плт равен 0 и тип аргумента не существенен. Так как
10573,45 уже есть на счету, то аргумент бс получает знак «плюс», а подсчитанное
начальное значение отрицательное.
Пример 5
Если я вношу $300 ежемесячно (начиная с сегодня) счет, положенный под 1%
ежемесячных начислений, сколько у меня будет на счету через 2 года?
Используемая функция: БС (ставка;кпер;плт;пс;тип)
Эта формула возвращает -$8172,96
=ПС(1%;24;-300;0;1)
В этом примере срок указывается в годах, но процент и выплаты ежемесячные. Это
требует предварительного преобразования единиц времени. Наиболее эффективный
способ - преобразовать годы в месяцы. Другой вариант – преобразовать процентную
ставку в ежегодную эффективную ставку, а потом преобразовать $300 в эквивалентную
сумму за год. Расчеты в данном случае более громоздкие.
Обратите внимание, что начало выплат с «сегодня» характеризует авансовые
платежи. Следовательно, тип аргумента равен 1. Никакой начальный взнос не указан,
поэтому аргумент пс равен 0.
Во всех предыдуших примерах вопросы можно перефразировать таким образом, что
отрицательное значение станет положительным, а положительное – отрицательным. Так,
пример 1 может быть перефразирован следующим образом.
Пример 6
Если я занимаю 100000 рублей на три года под 7% годовых, то сколько всего я
должен выплатить?
Используемая функция: БС (ставка; кпер; плт; пс; тип)
Это формула возвращает 122504,30 р.
=БС(7%;3;0;100000;0)
Здесь вопрос ставится с точки зрения заемщика, поэтому формула изменена так, что
начальный вклад (аргумент пс) положительный. Регулярные выплаты не производятся,
так что аргумент плт равен 0. Без выплат тип аргумента несущественен.
Примеры 2-5 можно также перефразировать, например, вкладчик становится
заемщиком, а заемщик становится вкладчиком.
Пример 7
Если с$1 000 вклад уееличился до $3 000 по истечении восьми лет, то какой была
средняя годовая ставка?
Используемая функция: СТАВКА (кпер; плт; пс; бс; тип; предположение)
Это формула возвращает 14,720269%
=СТАВКА(8;0;-1000;3000;0)
Этот пример рассмотрен с точки зрения вкладчика. Таким образом, аргумент пс
отрицателен, а аргумент бс положителен. Из-за того, что срок выражался в годах, то
возвращаемое значение - эффективная годовая ставка. Без регулярных выплат аргумент
плт равен 0, а тип аргумента несущественен.
5
Пример 8
С начальным балансом 5 500р. и ежемесячной выплатой 500р. (в конце каждого
месяца), сколько я могу накопить за срок более трех лет при 0,75% в месяц?
Используемая функция БС (ставка; кпер; плт; пс; тип)
Эта формула возвращает $27773,91
=БС(0,75%;36;-500;-5500;0)
Отрицательный знак аргумента пс может поставить вас в тупик из-за того, что он
представляет собой текущий баланс. Однако, поскольку рассматривается будущий баланс,
текущее значение необходимо рассматривать как вклад. Выплаты и сроки указываются на
ежемесячной основе, поэтому трехлетний срок необходимо преобразовать в 36 месяцев.
БС возвращает положительное значение, что указывает на увеличение вклада.
Пример 9
Мой остаток на счету nять лет назад составлял 25000 р. Я добавлял 4500 р. в
конце каждого года. Сейчас бaланс равен 70000 р. Какой была моя среднегодовая ставка?
Используемая функция: СТАВКА(кпер;плт;пс;бс;тип;предположение).
Это формула возвращаст 10,9382%
=СТАВКА(5;-4500;-25000;70000;0;0)
СТАВКА - это особенно мощная функция, так как решение поставленной задачи получается
только в результате итерации. Только иногда приходится добавлять предполагаемую ставку в
качестве шестого арryмента. Если его опустить то Ехcel по умолчанию предложит 0.
Проработав эти 9 примеров, можно получить общий алгоритм вычислений:
1. Определите требуемую функцию.
2. Определите направление денежных потоков и используемые для расчетов функци.
3. Убедитесь, что единицы измерения времени в функциях СТАВКА, КПЕР и ПЛТ одни и те
же (или преобразуйте значения так, чтобы сделать их равными).
4. Вставьте аргументы в правильном порядке (предпочтительно с помощью ссылок на
ячейки).
5. Проанализируйте полученный результат.
6. Убедитесь в отсутствии ошибок.
Задачи дисконтирования.
Можно воспринимать заем как «обратное накопление». Вместо определения
будущего значения по начальному вкладу и ставке давайте вычислим значение
начального вклада по его будущей величине.
Как и в случае с накоплениям, здесь задачи решаются с помощью двух или трех
значений ПС, БС и ПЛТ.
Пример 10
Какой начальный вклад предполагает получение 25000 р. После пяти лет при
ставке 6,5% годовых?
На рисунке показан этот пример на рабочем листе.
6
Рис.2
Используемая функция: ПС (ставка;кпер;плт;бс;тип)
Эта формула возвращает -18247,02р.
=ПС(С9;С10;С11;С12;С13), т.е. =ПС(6,5%;5;0;25000;0)
Обратите внимание на логику знаков. Если вы собираетесь получить на вкладе
деньги, то бс – положительный аргумент. Чтобы получить в будущем положительное
значение, нам надо вначале положить в банк определенную сумма, т.е. использовать в
формуле отрицательный аргумент. Без выплат тип аргумента несущественен.
Пример 11
Имущество с текущей стоимостью $2000000 продается на правах аренды с
номинальной арендной платой сроком на пять лет. Покупатель оплатил $1750000. Не
принимая во внимание рост стоимости, какой была учетная ставка?
Номинальная арендная плата - плата, которая взимается с клиента в учет покупки
жилья.
Используемая функция: СТАВКА (кпер;плт;пс;бс;тип;предположение).
Эта формула возвращает 2,706609%:
=СТАВКА(5;0;-1750000;2000000;0)
Выплата сегодня представляет собой отрицательное текущее значение. Стоимость
после 5 лет - положительная.
Для проверки ответа используйте следующую формулу (которая возвращает
$2000000):
=БС(2,706609%; 5; -1750000;0)
Пример 12
Если использовать учетную ставку 0,75% в месяц, сколько необходимо выплатить
вначале за имущество, которое по оценке будет стоить $5 000 000, при ежемесячной
выплате в $25 000 в течение пяти лет?
Используемая функция: ПС(ставка;кпер;плт;бс;тип)
Следующая формула возвращает -$4 406 865,34
=ПС(0,75%;60;25000;5000000;1)
В этом примере в качестве единицы времени используется месяц, а выплаты
производятся ежемесячно. Таким образом, аргумент кпер преобразован в месяцы.
7
Можно проверить это вычисление с помощью функции СТАВКА. Следующая
формула возвращает 0,75%:
=СТАВКА (б0;25000;-4406865,34;5000000;1)
Пример 13
Имущество приобретено за $1 600 000. Расчетная арендная плата составляла $10
000 и выплачивалась авансом каждый месяц. Если я хочу получить доход равный 1% в
месяц, какой должна быть стоимость имущества через 5 лет (если я планирую его
продать)?
Используемая функция: БС ( ставка; кпер; плт; пс; тип)
Эта формула возвращает $2 081 851,05:
=БС(1%;60;10000;-1600000;1)
Этот результат можно проверить, используя следующую формулу (которая
возвращает $1 600 000):
=ПС(1%;60;10000;2081851,05;1)
Задачи выплаты займа.
Пример 14
Какие выплаты необходимо сделать по займу 200 000р., взятому на 10 лет под
0,5% в месяц?
Используемая функция: ПЛТ(ставка;кпер;пс;бс;тип).
Следующая формула возвращает 2220,41р.:
=ПЛТ(0,5%; 120; 200000;0;0)
В этом примере заем полностью выплачивается через 10 лет, и аргумент бс равен
нулю. Также обратите внимание, что выплаты производятся ежемесячно, т.о., 10-летний
срок преобразуется в месяцы.
Пример 15
В настоящий момент я имею задолженность 150000р. по займу, и выплачиваю 1900
в месяц. Текущая процентная ставка равна 0,45% в месяц. Сколько времени потребуется
для выплаты всего долга?
Используемая функция: КПЕР (ставка;плт;пс;бс;тип).
Следующая формула возвращает 97,76:
=КПЕР(0,45%;-1900;150000;0;0)
Этот ответ математически верен, но имеет редкое практическое применение.
Выплаты действительно производятся точно раз в месяц. Это подразумевает, что заем
будет выплачен к 98 месяцу. Фактически перед вами стоит выбор: произвести
дополнительную оплату в конце 97 месяца, или сделать уменьшенную оплату на 98 месяц.
Каждый вариант подсчитывается с помощью функции БС.
Чтобы вычислить дополнительную оплату в конце 97 месяца, подсчитайте сумму с
помощью следующей формулы (она возвращает -429,85):
=БС(0,45%; 97; -199; 150000; 0)
Таким образом, заключительная оплата после 97 месяца равна -3329,85 (т.е.,
нормальный платеж -1900 плюс -1429,85).
Чтобы вычислить уменьшенную оплату в 98 месяце, используйте эту же формулу
(возвращаемое значение 463,72):
8
=БС(0,45%; 98; -1900; 150000; 0)
Таким образом, конечная оплата через 98 месяцев равна -1436,28 (т.е., нормальный
платеж -1900 плюс 463,72).
Еще о некоторых функциях: ЧПС, ПРПЛТ, ВСД, АПЛ, АСЧ
Вы хотите купить новый дом или машину? Функция ПЛТ представляет собой
превосходный инструмент для анализа ссуд и выплат. Как только вы разберетесь с
аргументами, работа с функцией ПЛТ становится самым простым делом.
Предположим, вы взяли кредит в 150000 на 15 лет под 7% годовых. Используйте
функцию ПЛТ для вычисления ежемесячных платежей. Только убедитесь, что все
величины приведены к ежемесячным значениям.
• Годовой процент следует поделить на 12, чтобы получить процент в месяц.
• Кредит взят на 15 лет при 12 платежах в год. Всего будет 12*15=180 ежемесячных
платежей (см. рис.3).
Задайте функцию ПЛТ, как показано на рис.4, и вы увидите в диалоговом окне
функции ПЛТ сумму ежемесячных выплат.
Рис.3
Рис.4
ПРПЛТ вычисляет процентную ставку
Предположим, вы хотите более подробно разобраться с этим кредитом. Используйте
функцию ПРПЛТ для вычисления суммы, которую вы должны выплатить за любой
заданный период. Вызовите функцию ПРПЛТ так же, как и функцию ПЛТ. В поле
Период задайте интересующий вас период.
В данном примере это должно быть число в диапазоне от 1 до 180, представляющего
все периоды платежей в течение срока действия кредита.
На рис.5 представлена функция ПРПЛТ, вычисляющая сумму выплат за 90-й период.
9
Рис.5
Функции ПС и ЧПС
Функции ПС и ЧПС вычисляют текущий объем вклада и чистый текущий объем
вклада, основываясь на простой идее: доллар сегодня — это больше, чем доллар через
год.
Текущий объем вклада — это сумма на данный момент, равная будущей сумме, из которой вычтена плата
по выбранной процентной ставке. Будущее значение (БЗ) — это величина, до которой сегодняшняя сумма
вырастет к определенной дате в будущем в результате выплат по процентной ставке. Чистый текущий
объем вклада вычитает исходную вложенную сумму из суммы последующих платежей с учетом
процентной ставки. НПЗ дает ответ на вопрос: сколько данная инвестиция стоит В
ДЕЙСТВИТЕЛЬНОСТИ?
Функция ЧПС говорит, что миллион – не всегда миллион
Поздравляю! Мы только что выиграли в лотерее, и выигрыш составляет миллион!
Эту сумму нам будут выплачивать в течение 10 лет. Мы будем получать по 50000 в год в
первые пять лет и по 150000 в течение последних пяти. Почему же вы не прыгаете от
радости? Потому что в действительности вы не получите миллион? Пять раз по 50000
плюс пять раз по 150000 равно миллиону, не так ли? На рис.3 видно, что это не так.
643517 — это не миллион, не так ли? Мы хотим применить функцию НПЗ для оценки
нашей лотерейной инвестиции, поэтому мы должны определить исходные издержки. В
конце концов, мы заплатили 5р. за лотерейный билет!
Сколько я вам должен?
Если вы сейчас дадите мне доллар (спасибо), я помчусь в банк и положу его на свой счет.
Процентная ставка по моему вкладу составляет, скажем, 3%. Через год с момента вложения на
этот доллар нарастет 3 цента (в действительности несколько больше с учетом сложных
процентов). Сумма вклада составит $1,03. Это называется будущее значение.
А какова сегодняшняя цена этой будущей суммы в $1,03? Она равна $1,00. Это есть текущий
объем вклада. Так что если я говорю вам, что верну вам ваш доллар через год, то я вас обсчитаю.
Почему? Потому что этот доллар-через-год стоит сегодня примерно $0,97. Если вы хотите вернуть
через год свой сегодняшний доллар, вы должны настаивать на возврате суммы $1,03.
Итак :
ЧПС = (инвестиция) + ПС от ежегодных платежей
5 + 643517 = 643512
На рис.6 приведены расчеты с использованием функции ЧПС, где 7% - годовая ставка
дисконтирования. Она может представлять собой темп инфляции или процентную ставку
по конкурирующим инвестициям. Мы можем вложить сегодняшний миллион в ценные
бумаги под 7% годовых, поэтому задаем процентную ставку 0,07.
Сумма 5р., которую мы внесли за лотерейный билет, — это деньги, выплаченные
10
нами, поэтому она выражается отрицательным числом. Мы не включили ее в выделенный
диапазон, подлежащий расчету с помощью функции ЧПС, потому что мы уже уплатили
эту сумму. Функция ЧПС вычисляет будущие инвестиции и доход, получаемый через
регулярные промежутки времени.
Если вы планируете сделать инвестицию в конце первого года, учитывайте начальную
сумму при расчетах с использованием функции ЧПС.
Рис.6
Рис.7
Функция ЧПС является одним из лучших инструментов для расчета инвестиций.
Часто финансовые аналитики, глядя на проект, говорят: "Если ЧПС представляет собой
положительное число, этот проект нужно реализовать". Это означает, что инвестиция
принесет больше, чем она стоит. Если мы будем подобным образом рассуждать о нашем
выигрыше в лотерею, то мы не должны слишком жалеть о том, что имеем не полный
миллион.
Но это все-таки не миллион.
Функция ВСД
Этот же подход мы можем применить к инвестициям: какую процентную ставку
должен я задать, чтобы величина ЧПС (чистой текущей стоимости) для данной
инвестиции была равна нулю?
Такая процентная ставка называется внутренняя скорость оборота. Формула для ее
11
вычисления под названием ВСД часто используется при принятии финансовых решений.
Она дает возможность сравнить прибыль с возможной стоимостью капитала. Если
величина ВСД превышает некую среднюю величину, то инвестиция считается хорошей.
Если нет — не стоит суетиться.
Возможная стоимость капитала — это процент дохода, который обеспечивает нам рынок,
например государственные ценные бумаги, и от которого мы отказываемся, вкладывая деньги в
другое предприятие.
Рассмотрим работу этой функции на следующем примере. Инвестиция в 50000
является отрицательным числом, так как это деньги, которые мы заплатили.
Рис.8
Если вы захотите рассчитать ВСД вручную, вам придется использовать метод проб и
ошибок. Вы должны задавать различные процентные ставки, пока не подберете такую,
которая превратит величину ЧПС в нуль. Если мы предоставим Excel сделать эту работу
за нас, программе потребуется для этого 20 итераций. В случае нашей "лотерейной
инвестиции" этот процент будет непомерно большим. Excel сделает 20 итераций и выдаст
сообщение об ошибке
#ЧИСЛО?
В диалоговом окне функции ВСД (см. рис.9) есть поле ввода Предположение. В этом
поле вы можете (не обязаны) задать предполагаемое решение. Если вы оставите поле
Предположение пустым, Excel начинает итерационный процесс с величины 10%.
Рис.9
Значение ВСД равно 5%; следует ли нам вкладывать деньги в данное предприятие?
Не следует, если государственные ценные бумаги дадут нам больше.
12
Оценка амортизации.
Амортизация определяет стоимость имущества в зависимости от срока его службы. Срок службы
обычно берется равным периоду обновления подобного имущества. Идея состоит в том, что по мере
использования этого имущества вы учитываете его износ и, соответственно, расходы на эксплуатацию.
АПЛ, или функция прямой амортизации.
В методе непосредственной амортизации берется разница между стоимостью
ржавого автомобиля и его продажной ценой и делиться на количество лет, которое
прослужила машина. В примере, представленном на рис.10 это (20000-2500) / 5 = 3500.
Функция АПЛ позволяет избежать арифметических расчетов. Т.е. сумма 3500р.–
амортизационные отчисления для каждого года эксплуатации.
Рис.10
Рис.11
Функция АСЧ
Функция АСЧ представляет более интересный способ снижения стоимости
имущества. Это широко используемый метод, позволяющий ускорить износ имущества в
начале его использования. Вы получаете большую стоимость амортизации на более
раннем отрезке времени. Из-за того что экономия на налогах сегодня выгоднее, чем
экономия на налогах завтра, АСЧ и другие схемы, ускоряющие износ, более популярны
при расчетах. Рассмотрим следующий пример (рис.12).
13
Рис.12
Функция АСЧ рассчитывает амортизацию за каждый период отдельно. Обратите
внимание в таблице на разницу между амортизацией за первый год и амортизацией за
пятый год, показанной на рис.13 в строке Значение диалогового окна функции АСЧ.
Рис.13
Статистические функции
Марк Твен писал в автобиографии: "Существует три способа обмана — ложь,
наглая ложь и статистика". С помощью функций Excel вы можете состряпать
любую ложь — извините, статистические данные, — выполнив несколько простых
действий.
Построение линии по точкам: линейное и экспоненциальное приближение
Предположим, что вы владелец компании. Последние несколько месяцев бизнес шел
хорошо, но вы смотрите в будущее. Каковы перспективы на следующий квартал?
Один обычный статистический инструмент может дать вам ключ к ответу на этот
вопрос. Если вы отложите на куске миллиметровки объемы продаж за последние шесть
месяцев, затем проведете линию, которая проходила бы как можно ближе к каждой из
точек, то, продолжив эту линию в будущее, вы получите ответ на свой вопрос. Иногда
такую линию называют линией наилучшего соответствия данных. Она также называется
линейное приближение.
Как построить линейное приближение
Пытаясь изобразить линию наилучшего соответствия по точкам, вы можете ис-
14
портить зрение. Excel сохранит ваши глаза, проведя эту линию за вас. Excel использует
при этом метод наименьших квадратов.
Математик Карл Фридрих Гаусс разработал метод наименьших квадратов двести лет назад и
сразу применил его для открытия первого из наблюдавшихся астероидов – Цереры.
Метод наименьших квадратов определяет расстояния между реальными данными и точками
на линии и выводит уравнение, описывающее эту линию. Такая линия будет линией наилучшего
соответствия, называемой также линией регрессии или линией тенденции. Эта процедура
называется регрессионным анализом.
Excel спасает вас от возни с миллиметровой бумагой и уравнениями. Если вам
нужно вывести линейное приближение, используйте автозаполнение ячеек. Для этого
выполните следующее:
1. Выделите диапазон, содержащий ваши данные, например ячейки от В4 до В10
(рис.14).
Рис.14
2. В нижней части окна выделенного диапазона захватите правой кнопкой мыши
маркер заполнения и тащите его вниз, охватывая контуром пустые ячейки,
которые вы хотите заполнить данными (В11-В13 в нашем примере на рис.14).
Отпустите кнопку мыши, после чего на экране появится контекстное меню.
3. Щелкнув на пункте Линейное приближение, вы получите результат, показанный
на рис. 15.
15
Рис.15
Выделенные пустые клетки в конце диапазона заполнятся данными, соответствующими точкам на линии наилучшего соответствия.
То же самое вы можете сделать, выделив весь диапазон, включая и пустые ячейки, и
вызвав Правка, Заполнить, Прогрессия. (Выбрать опцию Автоматическое определение шага.)
Диалоговое окно Прогрессия дублирует только что выполненные вами действия.
Внимание: Выбор пункта Линейное приближение или установка опции
арифметическая в диалоговом окне Прогрессия (чтобы вызвать его, выберите Правка,
Заполнение, Прогрессия) приводит к замене исходных данных точками, расположенными
на линии наилучшего соответствия. Вы можете получить линейное приближение, но
потерять исходные данные.
Линия загибается вверх: экспоненциальное приближение
Быть может, вы оптимист. Вы ожидаете, что рост продаж в вашем новом магазине
офисных принадлежностей будет происходить быстрее, чем это предсказывает линия
наилучшего соответствия. Если вы действительно предвидите взлет продаж, постройте
экспоненциальное приближение, как показано на рис.16.
16
Рис.16
Линейное приближение, представленное на рис.15, предсказывает рост продаж в
будущие периоды, исходя из равных приращений. Экспоненциальное приближение
предсказывает исходя из приращений, возрастающих от периода к периоду.
Для предсказания более сложных зависимостей и прогрессий, используйте различные статистические функции.
Отклонение от среднего значения
Что такое стандартное отклонение? Возьмите некоторые данные, усредните их, потом
рассчитайте, насколько каждое из них отстоит от полученной средней величины.
Стандартное отклонение — это оценка разброса данных.
Функция СТАНДОТКЛОН
Существуют два способа определения стандартного отклонения. Один состоит в том,
что берутся все данные, или, как говорят статистики, вся совокупность данных. Такой
подход оправдывает себя в некоторых случаях, но не применим для больших
совокупностей.
Поэтому мы берем случайную выборку из всей совокупности данных и вычисляем
стандартное отклонение для этой выборки. Оно может быть рассчитано достаточно точно,
что экономит много времени. Каждый из этих методов требует большого количества
утомительных расчетов, которых нам удается избежать при использовании функции
СТАНДОТКЛОН программы Excel.
Для чего нужно стандартное отклонение
Предположим, что вы гончар и делаете по 100 глиняных горшков в день. Вы не
беспокоитесь о том, чтобы все горшки были одинаковыми по размеру, но вы
предпочитаете, чтобы они не слишком отличались друг от друга. Вы не статистик, но
знаете, что стандартное отклонение является критерием того, насколько ваши горшки
отличаются от среднего размера. Чем меньше стандартное отклонение, тем ближе горшки
к среднему размеру.
Итак, вы берете 10 горшков, измеряете их и находите стандартное отклонение
17
измеренных величин (рис.17).
Рис.17
Использованная в данном примере функция СТАНДОТКЛОНА действует
аналогично функции СТАНДОТКЛОН, но может обрабатывать также логические
значения (рис.18).
Рис.18
Внимание: Функция СТАНДОТКЛОН работает только с выборками из
совокупностей. Для целых совокупностей расчет отличается, а значит, отличается и
функция.
Понять, зачем нужно стандартное отклонение, поможет следующее правило: для
любого набора величин более 90% из них попадают в пределы трех стандартных
отклонений от среднего значения этих величин.
Логические функции
Функция ЕСЛИ позволяет проверять логические выражения и выбирать возвращаемые
значения. Например, выражение
= ЕСЛИ(С12>10, "Высокое","Среднее")
вернет "Высокое", если С12 больше 10,
и "Среднее" — в противном случае. Excel позволяет
нам вложить одну функцию в другую для принятия более сложных решений. Функция
ЕСЛИ часто используется совместно с И и ИЛИ, при этом одна функция является
аргументом другой.
18
Предположим, что вы следите за курсом акций одной из компаний. Вы собираетесь
приобретать их по цене от $20 до $25. Свыше $25 — слишком дорого. Если же цена
падает ниже $20, то вы предполагаете, что у этой фирмы дела плохи, и не хотите
рисковать, покупая ее акции.
Функция И, вложенная в функцию ЕСЛИ, прояснит ситуацию с покупкой акций.
Введите текущую цену, и функция скажет вам, покупать или не покупать.
Рис.19
Рис.20
1. Начните с построения первой функции — ЕСЛИ. Выделите ячейку, в которую вы
хотите поместить результаты и щелкните на кнопке Вставка функции. В списке Категория:
выделите Логические и дважды щелкните на пункте ЕСЛИ в поле Функция: Появится
диалоговое окно функции ЕСЛИ.
2. Щелкните на стрелке, расположенной рядом со строкой формул. Если нужная вам
функция появится в списке, выделите ее. Если нет — откройте диалоговое окно Мастер
функций, выбрав пункт Другие функции. В диалоговом окне Мастер функций выделите в
списке Категория: пункт Логические, а затем дважды щелкните на пункте И списка Функция.
3. В диалоговом окне функции И введите условия, которым должна удовлетворять наша
сложная функция (рис.20).
4. Щелкните на строке формул и нажмите клавишу <Еnd>, чтобы перевести курсор в
конец выполняемой формулы. Снова появится диалоговое окно функции ЕСЛИ.
19
Рис.21
5. Введите значение Покупать в поле Значение_если_истина и Не покупать в поле
Значение_если_ложь, как показано на рис.21. Вводить кавычки, показанные на рис.21, не
надо – Excel вставляет их автоматически.
6. В строке формул (рис.19) представлена почти готовая сложная функция. Щелкните на
кнопке ОК диалогового окна функции ЕСЛИ, и процесс ее построения будет закончен.
Если цена акций компании заданная в ячейке С3, находится в пределах от 20 до 25,
функция вернет значение «Покупать». При любой другой цене, будет возвращено
значение «Не покупать».
Бизнес держится на своевременной информации. Но в наши дни данные поступают
стремительными электронными потоками, а не депешами, доставляемыми на лошадях.
Получить информацию легко; другое дело — уяснить ее смысл. В этом могут помочь
аналитические средства Ехсеl. Сортировка и оценка информации — это основная работа в бизнесе, выполняемая ежедневно. Ехсеl не положит к нашим ногам рынок ценных
бумаг, но окажет большую помощь в анализе деловой информации.
Как рассчитать выплаты по займу, если процентная ставка непостоянна
Во времена президента Эйзенхауэра инфляция была незначительна, прирост
устойчивый, а процентные ставки почти не менялись. Покупка в кредит означала ссуду
под стабильный процент. С тех пор мы испытали нефтяные кризисы, инфляцию,
исчисляемую многозначными цифрами, и гигантские колебания процентов по вкладам.
И в наши дни процентные ставки непостоянны. Задержитесь с оформлением кредита
на неделю, а то и на день, и вы его получите уже под другой процент. А при этом суммы
выплат меняются. Как проследить их?
Построение таблицы подстановки
Ответ подскажет таблица подстановки — инструмент Excel, работу которого
можно сравнить с конвейером. Команда Таблица подстановки программы Excel берет
строки и столбцы величин, "протягивает" их через формулу (подобно конвейерной
линии), а затем складывает ответы в столбец или укладывает их вдоль строки.
Скажем, мы берем кредит для покупки нового склада. Мы присмотрели отличное
помещение, но продавец пока не решается заключить договор. Пока он колеблется, банки
ежедневно изменяют процентную ставку. Тем не менее, мы должны знать, каковы будут
наши выплаты, чтобы мы могли составить бюджет.
Взгляните на рис. 22.
20
Рис. 22
Построение таблицы начинается с ввода данных. В данном случае входными
значениями являются процентные ставки, заданные в столбце В. Нам также нужна
формула для расчета платежей, для этого будем использовать функцию ПЛТ.
1. Выделите ячейку, в которую вы хотите поместить формулу. В данном случае
формула должна находиться на одну строку выше и на один столбец правее первого
входного значения, т.е. в ячейке С8.
2. Щелкните на кнопке Вставка функции для вызова диалогового окна Мастер функций.
В списке Категория выделите Финансовые. Затем, дважды щелкнув на пункте ПЛТ в поле
Функция, вызовите диалоговое окно ПЛТ.
3. Введите процентную ставку, число платежей и сумму на данный момент или
основную ставку. Помните: чтобы вычислять ежемесячные выплаты, надо годовую
процентную ставку разделить на 12, а срок, выраженный в годах, умножить на 12. Пример
заполнения окна показан на рис. 23.
Рис.23
Чтобы задать аргумент функции ПЛТ, щелкните на соответствующем поле диалогового окна ПЛТ, а
затем выделите ячейку, содержащую нужную величину.
4. Закончив построение формулы, щелкните на кнопке ОК диалогового окна ПЛТ.
Теперь выделите диапазон, содержащий входные величины, и формулу, в нашем примере
это В8:С13. Пустые ячейки диапазона будут заполнены величинами платежей,
рассчитанных командой Таблица подстановки.
5. Выберите Данные, Таблица подстановки, на экране появится диалоговое окно
Таблица подстановки. Выберите опцию Подставлять значения по строкам в.
6. Щелкните на ячейке, на которую ссылается формула, в ней мы анализируем
21
изменяющееся значение. В данном случае это В5 - та ячейка, в которую мы поместили
процентную ставку на сегодня и которая задана как аргумент функции ПЛТ.
7. Щелкните на кнопке ОК — и наша таблица будет заполнена, как показано на рис.
24. Числа отрицательные, поскольку прибыль получит кредитор, а нам придется платить.
Рис.24
Теперь мы можем скорректировать наш бюджет, используя полученную информацию.
Конечно, вы не сможете повлиять на решение продавца, но, по крайней мере, у вас есть
правильные данные.
Как использовать в таблице данных несколько формул
Предположим, вам надо не только рассчитать выплаты, но и знать, какую сумму
составляют проценты по кредиту. Мы можем поместить в нашу таблицу функцию
ПРПЛТ, чтобы получить для каждого значения процентной ставки сумму, определяемую
процентами.
Дополнительные формулы располагаются справа от исходной формулы, в той же
строке. Выделите всю таблицу, включая первоначальные значения. В данном случае это
диапазон В8:D13. Вызовите Данные, Таблица подстановки, затем задайте процентную
ставку в качестве Подставлять значения по строкам в: (ячейка В5) и щелкните на кнопке
ОК. Результаты показаны на рис.25.
Рис.25
22
Чтобы узнать выплату по процентам за периоды со 2-го по 60-й, добавьте справа от
первой столько дополнительных формул ПРПЛТ, сколько вам надо.
Функция ПРПЛТ возвращает отрицательное значение, потому что эту сумму вы
выплачиваете, а не получаете. Чтобы отобразить эти значения положительными, используйте
функцию АВS, возвращающую абсолютную величину числа. Соответствующая формула должна
выглядеть так:
=АВS(ППЛАТ(В5/10,В4*12,ВЗ))
Введите ее или используйте кнопку Вставка функции.
Построение таблицы данных с двумя входными величинами
На сумму ежемесячных выплат влияет не только процентная ставка, но и сроки.
Когда мы берем кредит, мы стараемся растянуть срок погашения, чтобы уменьшить
выплачиваемые суммы.
Построим таблицу данных с двумя входными величинами: одна нужна для того,
чтобы проследить влияние процентной ставки, а другая — различных сроков погашения
кредита. Таблица данных с двумя входными величинами должна включать две
переменные, но только одну формулу.
1. Сформируйте таблицу так, как показано на рис. 26. Одна входная величина
должна находиться в столбце, а другая — в строке. Столбец входных величин
располагается ниже формулы, а строка входных величин — справа от нее. В данном
случае формула находится в ячейке В8.
Рис. 26
2. Выделите диапазон В8:F13.
3. Выберите Данные, Таблица подстановки. Сроки погашения ссуды, которые мы
хотим видеть, располагаются справа от формулы. Поэтому в поле Подставлять значения
по столбцам в выберите В4. Эта ячейка содержит срок погашения кредита, используемый
в формуле.
4. В поле Подставлять значения по строкам в задайте ячейку B5. Она содержит процентную
ставку. В формуле используются обе входные ячейки.
5. Щелкните на ОК. Рассчитанные значения расположились в ячейках таблицы, как
показано на рис.27. Заметьте, что входные величины и формула образуют
прямоугольник. Это единственный вариант размещения данных в таблице.
23
Рис. 27
В формуле должны присутствовать обе входные ячейки, заданные командой Таблица
подстановки. Убедитесь, что вы указали те же самые входные ячейки, что и в формуле.
Как узнать, стоит ли вкладывать деньги
Получить кредит под покупку складского помещения — достаточно сложное дело. Но
нужен ли нам вообще новый склад? Наличие дополнительного помещения для хранения
товаров обещает нам дополнительный ежегодный доход. Оправдает ли он
дополнительные расходы на содержание нового склада? Чтобы ответить на подобные
вопросы, надо выполнить расчеты с использованием функции ЧПС.
Функция ЧПС помогает оценивать инвестиции
Функция ЧПС обрабатывает величины будущих доходов и приводит их к текущему
значению. Иначе, устраняет влияние процентных ставок и позволяет нам реально оценить
инвестицию.
Если мы приведем все будущие денежные поступления к значению на сегодня, то
получим сумму реального дохода от данной инвестиции. И если полученная сумма
доходов превышает начальную инвестицию, то мы получаем больше, чем истратили.
Такую инвестицию стоит делать.
Начиная расчеты с использованием функции ЧПС, мы сталкиваемся с одной
проблемой. Нам известны будущие поступления, мы знаем сумму начальной инвестиции,
но что нам использовать в качестве процентной ставки? Возможны десятки различных
значений этой величины. Какую выбрать?
Используя функцию ЧПС и таблицу данных, мы можем проверить их все.
Проверка влияния малых изменений называется анализом чувствительности. Причудливый
термин, но как иначе назвать это? Получившуюся в результате такого анализа таблицу
иногда называют таблицей ответов.
Сочетание таблиц данных и функции ЧПС
Давайте оценим влияние различных процентных ставок на сумму инвестиции,
приведенную к текущему значению. Вы должны быть уверены, что правильно выбрали
процентную ставку. На рис. 28 показаны исходные данные.
24
Рис. 28
Вы планируете вложить $1600, и, по вашей оценке, вы заработаете в последующие
годы суммы, представленные в ячейках В23:В26. Оправдают ли эти доходы вложенные
деньги? И каково влияние различных процентных ставок на результат нашего расчета?
Это мы сейчас узнаем.
1. Поскольку формируется таблица данных, формула, включающая ЧПС, должна
находиться на одну строку выше и на один столбец правее первого из проверяемых
значений (в данном примере это В29). Выделите ячейку В29, затем щелкните на кнопке
Вставка функции.
2. Появится диалоговое окно Мастер функций. В поле Категория выделите пункт
Финансовые. Затем двойным щелчком выберите в области функция пункт ЧПС.
3. Щелкните в поле Ставка, затем выделите ячейку С21, в которой находится значение процентной ставки.
4. В поле Значение1 задайте начальную инвестицию, указанную в ячейке С20.
Поскольку инвестиция еще не сделана, ее сумму надо учесть в формуле.
5. В поле Значение2 задайте диапазон, в котором представлены ежегодные
поступления (в данном примере В23:В26), после чего включите формулу в ячейку (рис.
29).
Рис.29
6. Это вам знакомо, не правда ли? Выделите диапазон, содержащий формулу,
процентные ставки и примыкающие сводные ячейки, затем выберите Данные, Таблица
25
подстановки.
7. В данный момент в поле Подставлять значения по строкам в содержится С21. К
этой же ячейке обращается формула ЧПС (рис.30).
Рис. 30
8. Щелкните на ОК, и ячейки будут заполнены значениями ЧПС (рис. 31).
Рис. 31
Команда Таблица подстановки рассчитывает суммы выплат для разных процентных
ставок и сроков погашения кредита
Данная инвестиция выглядит привлекательно при значениях процентной ставки 7%
и меньше. Начиная с 8%, инвестицию делать не стоит. Мы истратим больше, чем
получим.
Ехсеl не выбирает за нас уровень процентной ставки. Мы сами должны дать ответ на
этот трудный вопрос. Но теперь мы, по крайней мере, знаем, какого результата можно
ожидать от нашего решения.
Вы можете внести изменения в любые данные таблицы. Можно, например, изменить
сумму кредита или ожидаемые доходы. Вы также можете задать вместо ЧПС другую
функцию. Команда Таблица подстановки также добавляет свои табличные формулы для
каждой из тестируемых величин.