Глава 1. Анализ и обработка данных электронной таблицы

 

Электронные таблицы создаются с целью анализа и принятия решения. В экономических системах данные применяются для прогнозирования изменения тех или иных показателей или выполнения вычислений, связанные с получением итоговых показателей. Для решения таких задач в табличном процессоре Excel существует широкий набор средств анализа и обобщения данных:

F   автоматические вычисления;

F   средства для работы с базами данных и списками;

F   средства автоматического подведения  общих и промежуточных итогов;

F   возможность создания и использования структуры таблицы;

F   средства для обобщения данных;

F   возможность создания и модификации сводных таблиц, отчетов диаграмм;

F   средства условного анализа:

§       подбор параметра;

§       поиск решения;

§       сценарий;

§       таблицы подстановок.

 

1.1. Анализ данных с помощью функций

 

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

Синтаксисом формулы называется порядок, в котором вычисляются значения. Он задает последовательность вычислений. Формула начинается со знака равенства "=", за которым следует набор вычисляемых элементов (операндов) и операторов. Синтаксис формулы определяет структуру или порядок элементов формулы. Операндами могут быть кон­станты, ссылки или диапа­зоны ячеек, заголовки, име­на или функции.

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

Для простых и сложных вычислений используются функции. В Microsoft Excel применяется большое разнообразие функций, к которым относятся: математические, финансовые, статистические, текстовые, логические, информационные, инженерные, дата и время. Наиболее распространенной является функция СУММ, суммирующая диапазоны ячеек.

Все функции имеют имя и перечень аргументов, находящихся в круглых скобках, разделенных точкой с запятой. Пример содержимого ячейки с функцией: =A5+sin (C7), где А5 адрес ячейки; sin( ) — имя функции, в круглых скобках указывается аргумент; С7 — аргумент (число, текст и т.д.), в данном случае ссылка на ячейку, содержащую число.

Функции могут использоваться как аргументы в других функциях. Если функция используется в качестве аргумента или является вложенной функцией, то она должна возвращать аргументу значение того же типа. В формулах можно использовать до семи уровней вложения функций.

Вложенные функции

 

 


=ЕСЛИ (СРЗНАЧ(А1:А6)<45;СУММ(В1:B12);0)

Функции можно вводить с клавиатуры или для облегчения работы с помощью встроенных функций используется Мастер функций, вызываемого командой Вставка \ Функции или пиктограммой .

Мастер – инструмент, позволяющий выполнить требуемое действие по шагам с уточнением параметров по каждому шагу. Мастер функций Excel задается за 2 шага.

 

Рис.1.1. Диалоговое окно Мастера функций для выбора категорий и вида функции

 

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

На шаге 2 задаются аргументы функции (рис.1.2), которыми могут выступать число, текст, ячейка или диапазон ячеек:

Рис. 1.2. Аргументы функции

 

Финансовые функции позволяют выполнять такие типичные финансовые расчеты, как вычисление суммы платежа по ссуде, объем периодической выплаты по вложению или ссуде, стоимость вложения или ссуды по завершении всех отложенных платежей.

 

1.2. Примеры автоматизации финансовых вычислений

 

Пример 1. Обоснование выбора кредита для развития фирмы.

 

Какую сумму необходимо выплачивать фирме каждый месяц, если она дала взаймы 5000 руб. под 12 % годовых и хочет получить назад деньги через пять месяцев.

 

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

ПЛТ(ставка; кпер; пс; бс; тип)

Ставка   - это процентная ставка по ссуде.

Кпер   - это общее число выплат по ссуде.

пс   - это текущее значение или общая сумма, которую составят будущие платежи, называемая также основной суммой.

бс   - это будущая сумма или баланс наличности, которой нужно достичь после последней выплаты. Если бс опущено, оно полагается равным 0 (нулю), т.е. будущая сумма ссуды, например, равна 0.

Тип - это число 0 или 1, обозначающее, когда должна производиться выплата.

Если аргумент тип опущен, то он полагается равным 0.

Тип

Когда нужно платить

0

В конце периода

1

В начале периода

Для аргументов ставка и кпер используются согласованные единицы измерения. Если производятся ежемесячные платежи по четырехгодичному займу из расчета 12% годовых, то ставка должна быть 12%/12 , а кпер должно быть 4*12. Если производятся ежегодные платежи по тому же займу, то ставка должна быть 12%, а кпер должно быть 4.Таким образом, функция определения ежемесячной выплаты суммы по кредиту имеет вид:

ПЛТ(12%/12; 5; -5000) равняется 1030,20 руб.

Функцию ПЛТ можно использовать для расчета накоплений, за какой - то промежуток.

 

Требуется накопить 50 000 руб. за 18 лет, накапливая постоянную сумму каждый месяц.

 

 Если предположить, что удастся обеспечить 6 % годовых на накопления, можно использовать функцию ПЛТ, чтобы определить, сколько нужно откладывать каждый месяц.

ПЛТ(6%/12; 18*12; 0; 50000)

При ежемесячной выплате 129,08 руб. с 6 процентным накоплением в течение 18 лет, Вы получите 50000 руб. Минус “-” указывает на то, что сумма подлежит уплате.

 

1.2.1.Таблица подстановок

 

При оценке и анализе вариантов инвестиций часто требуется получить конеч­ные значения для различных наборов исходных данных, например, построить фи­нансовую модель для различных значений процентных ставок и периодических выплат и выбрать оптимальное решение. Для решения подобных задач в Ехсеl служит инструмент Таблица подстановки.

Принцип её использования состоит в следующем:

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

2.    Для одного аргумента список исходных значений задается в виде строки или столбца таблицы. Ехсеl подставляет эти значения в функцию, заданную пользователем, а затем выстраивает результаты соответственно в строку или столбец.

3.    При использовании таблицы с двумя переменными значения одной из них рас­полагаются в столбце, другой — в строке, а результаты вычислений — на пересече­нии столбца и строки.

 

Определить, какие ежемесячные выплаты необходимо вносить по ссуде 200 млн. рублей, выданной на 3 года, при разных процентных ставках.

 

Для этого:

1.    Подготовьте исходные данные на рабочем листе.

2.    Введите в ячейку D7 формулу для расчета выплаты:

=ПЛТ (С4/12; С3*12; С2)

3.    Выделите следующий диапазон ячеек: ячейки, содержащие исходные значе­ния процентных ставок; ячейка, содержащая формулу для расчёта; ячейки, где будут расположены результаты. Для нашего примера это диапазон С7:D13.

4.    В меню Данные выберите команду Таблица подстановки.

5.    В диалоговом окне инструмента задайте адрес ячейки, на которую ссылается формула расчета (рис.1.3).

 

Рис. 1.3. Диалоговое окно Таблица подстановки

 

6.    Щелкните на кнопке ОК. Получим следующий результат (рис. 1.4):

Рис.1.4. Результат таблицы подстановок при разных процентных ставках

 

Рассчитать ежемесячные выплаты по займу размером 300 млн. рублей для раз­личных сроков погашения и разных процентных ставок.

 

Для этого:

1.  Подготовьте исходные данные на рабочем листе:

-        введите первое множество исходных значений в столбец;

-        введите второе множество исходных значений в строку;

-        введите формулу для расчета в угловую ячейку блока ячеек на пересечении столбца и строки.

2.    Выделите следующий диапазон ячеек: ячейки, содержащие все исходные зна­чения; ячейка, содержащая формулу для расчета; ячейки, где будут расположены результаты. Для нашего примера это диапазон С7:G13.

3.    Выберите команду  Данные\Таблица подстановки и заполните диало­говое окно инструмента.

4.    Щелкните по кнопке ОК. Сравните полученный результат по рисунку 1.5.

 

Рис.1.5. Результат таблицы подстановок при разных процентных ставках и сроков погашения

 

Пример 2. Определение срока выплат.

 

Сколько времени потребуется, чтобы вклад размером 1000 руб. под 12 % годовых достиг 10000 рублей с равными выплатами 100 рублей.

 

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

КПЕР (ставка; плт; пс; бс; тип)

Для данной задачи функция имеет вид

КПЕР(12%/12; -100; -1000; 10000; 1)

И возвращает значение 60 периодов, то есть на уплату уйдет 60/12=5 лет.

 

Пример 3. Определение процентной ставки.

 

Необходимо определить процентную ставку для четырехлетнего займа в 8000 рублей с ежемесячными выплатами  в 200 рублей.

 

Функция СТАВКА возвращает процентную ставку за один период при выплате ренты. Функция СТАВКА вычисляется методом последовательного приближения и может не иметь решения или иметь несколько решений.

СТАВКА (кпер; плт; нз; бз; тип; предложение)

Предложение — это предполагаемая величина нормы. Если предложение опущено, то оно полагается равным 10 %.

Таким образом, функция определения  процентной ставки имеет вид:

СТАВКА (48; -200; 8000)

Это месячная процентная ставка = 0,77, так как период равен месяцу. Годовая процентная ставка составит 0,77%*12, что равняется 9,24 процентам.

 

Пример 4. Определение будущего значения вклада.

 

Необходимо вложить 1000 рублей под 6% годовых, (что составит в месяц 6%/12 или 0,5%) и вкладывать по 100 рублей в начале каждого следующего месяца в течение следующих 12 месяцев. Сколько денег будет на счету в конце 12 месяцев?

 

Функция БС возвращает будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки.

БС(ставка; кпер; плата; нз; тип)

Согласно внесенным данным функция имеет вид  и принимает значение = 2295,40 рублей.

БС (0,5%; 12; -100; -1000)

 

1.2.2. Подбор параметра

 

Вычислительные возможности Ехсеl позволяют подбирать значения аргумента под заданное значение функции. Необходимость в этом обусловлена отсутствие некоторых «симметричных» финансовых функций. Рассмотрим использование этого инструмента для решения приведенного следующего примера:

 

Какой должна быть годовая процентная ставка, чтобы будущее значение суммы оказалось равным 5000 рублей?

 

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

Для этого:

     1. Подготовьте исходные данные на рабочем листе.

2.    Введите в ячейку Е5 формулу для расчета выплаты:

=БС (В5/2; С5*2; ;D5)

Получим следующее решение:

3. В меню Сервис выберите инструмент Подбор параметра.

4. В появившемся диалоговом окне (рис.1.6) сделать следующие настройки:

-        в поле Установить в ячейке указать адрес ячейки с функцией;

-        в поле Значение указать предполагаемое значение функции;

-        в поле Изменяя значение ячейки указать адрес ячейки, содержащей подбираемый  параметр.

5.  Подтвердите настройки.

Рис. 1.6. Диалоговое окно Подбор параметра

При нажатии кнопки ОК подобранное значение аргумента сохраняется в ячейке аргумента; при нажатии кнопки Отмена происходит восстановление значения аргумента. При неуспешном подборе параметра выдается соответствующее сообщение.

 

Пример 5. Определение цены ценных бумаг.

Функция ЦЕНА, возвращает цену за 100 рублей нарицательной стоимости ценных бумаг, по которым выплачивается периодический процент.

ЦЕНА(дата_соглашения; дата_вступления_в_силу; ставка; доход; выкуп; периодичность ;базис)

Дата соглашения  — это дата соглашения для ценных бумаг.

Дата_вступления_в_силу  — срок погашения ценных бумаг. Эта дата определяет истечение срока действия ценных бумаг.

Ставка  — это годовая процентная ставка для купонов по ценным бумагам.

Доход  — это годовой доход по ценным бумагам.

Выкуп  — это выкупная цена ценных бумаг за 100 р. номинальной стоимости.

Периодичность  — это количество выплат по купонам за год. Для ежегодных выплат периодичность = 1; для полугодовых выплат периодичность = 2; для ежеквартальных выплат периодичность = 4.

Базис  — это используемый способ вычисления дня.

Облигации выпущены на следующих условиях:

Дата соглашения 15 февраля 1999 года
Срок погашения 15 ноября 2007 года
Полугодовой купон 5,75 %
Доход 6,5 %
Цена при погашении 100 р.
Полугодовая периодичность
Базис 30/360

 

Цена облигаций (в системе дат 1900) составит 95,043:

ЦЕНА("15.2.1999";"15.11.2007";0,0575;0,065;100;2;0)

 

1.2.3. Диспетчер сценариев

 

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

Сценарий – именованная совокупность значений изменяемых ячеек.

Например, необходимо решить следующую задачу:

1.          Вычислить значение функции ЦЕНА для заданного набора исходных данных:

2.          Используя, Диспетчер сценариев из меню Сервис, построить сценарии для следующих наборов аргументов:

Таблица 2

 

1

2

3

4

5

6

Купонная ставка

9 %

9 %

15 %

15 %

9 %

9 %

Доход

12,57

12,57

12,57

12,57

15,00

15,00

Частота

2

4

2

4

2

4

 

Для этого:

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

2.    Выберите Сервис \ Сценарии.

3.    В появившемся диалоговом окне щелкните на кнопке Добавить.

4.    Во втором диалоговом окне наберите имя сценария и подтвердите.

5.    В третьем диалогом окне запишите необходимые значения изменяемых ячеек (аргументов) и подтвердите.

6.    Для каждого следующего сценария выполните пункты 3 – 5.

7.    Щелкните по кнопке Отчет и закажите тип отчета.

 

Задания для самостоятельного выполнения

 

1.         Есть два варианта инвестирования средств в течение четырех лет:

F       в начале каждого года под 26% годовых;

F       в конце каждого года под 38% годовых.

Пусть ежегодно вносится 300 тыс. рублей. Для каждого варианта определите, сколько денег окажется на счете в конце четвертого года.

2.  Предположим, вы хотите зарезервировать деньги для специального проекта, который будет осуществлен через год. Вы собираетесь вложить 1 млн. рублей под 60% годовых. Вы собираетесь также вкладывать по 100 тыс. рублей в начале каждого месяца в течение 12 месяцев. Сколько денег будет на счете в конце 12 месяцев.

3. Для того чтобы начать свое дело, вам необходимы собственные средства в сумме 150000. Уровень вашего текущего дохода позволяет вам откладывать на счет 8000 ежемесячно. Через какое время вы накопите нужную сумму, если банк выплачивает по вкладам 9 % годовых.

4. На Вашем счете в банке имеется 2500 рублей. Годовая процентная ставка 12 %, проценты начисляются 1 раз в полугодие. Сколько времени Вам потребуется, чтобы накопить

a.           15000 рублей;

b.          10000 рублей;

c.           12500 рублей ?

5. Предполагается путем ежеквартальных взносов по 35000000 рублей в течение 3 лет создать фонд размером 500 млн. рублей. Какой должна быть годовая процентная ставка?

6.    Какой должна быть годовая процентная ставка по вкладу размером 800 тыс. рублей, если его величина к концу года составила 1200 тыс. рублей, а проценты начислялись ежемесячно?

7.    Определить ежемесячные платежи для погашения кредита в 130 млн. рублей за 18 лет при ставке 0,14.

8.    При покупке дома стоимостью 5000000 рублей предоставлена рассрочка на 9 лет. Определить ежегодные платежи при ставке 0,75 %.

9.    Облигации приобретены 06.09.1993 и будут погашены 12.09.97. Размер купонной ставки – 9 % с выплатой раз в полугодие. Ожидаемая годовая ставка помещения – 12,57 %, номинал облигации (погашение) – 100, базис расчета – 1. Определить цену покупки облигации.

10.  Вычислить сумму ежемесячных процентных выплат за кредит, взятый в размере 50000 $ под 13% годовых со сроком погашения 5 лет.

 

Вернуться к выбору                             На главную страницу