Раздел 4. (Модуль4) Информационные технологии обработки таблиц

4.1. Организация финансово-экономических расчетов в электронных таблицах.

Важная сфера приложения MS Excel связана с осуществлением финансовых расчетов. Финансовые вычисления включают в себя всю совокупность методов и расчетов, используемых при принятии управленческих решений, - от элементарных арифметических операций и до сложных алгоритмов построения многокритериальных моделей, позволяющих получить оптимальные характеристики коммерческих сделок в зависимости от различных условий их проведения. Проведение подобных вычислений - трудоемкая процедура, требующая определенной математической подготовки и большого количества справочных материалов.
Считается, что как наука коммерческая арифметика начала формироваться "на заре новой истории" в Венеции, являвшейся в то время одним из крупнейших торговых центров Европы, хотя некоторые ее элементы встречаются уже в древнеегипетских манускриптах.

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

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

На данный момент стандартный курс финансовых вычислений включает в себя следующие основные темы:

логика финансовых операций (временная ценность денег, операции наращения и дисконтирования и т. д.);

простые проценты (операции наращения и дисконтирования, налоги, инфляция, замена платежей); сложные проценты (то же и эквивалентность ставок, операции с валютой и т. п.);

денежные потоки;

анализ эффективности инвестиционных проектов;

оценка финансовых активов.

Возрождение финансовой и страховой математики происходит в нашей стране в своеобразных условиях. С одной стороны, в мировой финансовой науке в течение XX века интенсивно развивались различные математические методы расчетов, появилась международная система унифицированных математических обозначений для стандартных финансовых и страховых схем. С другой стороны, бурное развитие индустрии ПК и их повсеместное внедрение привели к тому, что программы расчета основных финансовых показателей были реализованы на уровне, понятном широкому кругу пользователей, в электронных таблицах, например в Excel.

В Excel реализовано 15 встроенных и 37 дополнительных финансовых функций. В случае необходимости применения дополнительных финансовых функций необходимо установить надстройку «Пакет анализа».

По типу решаемых задач все финансовые функции Excel можно разделить на следующие условные группы:

функции для анализа аннуитетов и инвестиционных проектов;

функции для анализа ценных бумаг;

функции для расчета амортизационных платежей;

вспомогательные функции.

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

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

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

Количественный анализ таких операций сводится к исчислению следующих основных характеристик:

текущей величины потока платежей (Present value - Pv);

будущей величины потока платежей (Future value - Fv);

величины отдельного платежа (payment - R);

нормы доходности в виде процентной ставки (interest rate ~ r);

числа периодов проведения операции (число лет, месяцев).

К этой группе также относятся и функции, которые позволяют эффективно осуществить расчет наиболее широко применяемых критериев эффективности инвестиционных проектов, - NPV, IRR и т. п.

Функции этой группы используют сложные итерационные алгоритмы для исчисления соответствующих показателей. При этом делаются некоторые допущения:

потоки платежей на конец (начало) периода известны;

для всего срока проведения операции определена оценка в виде процентной ставки, в соответствии с которой средства могут быть инвестированы.

Финансовые функции для расчета амортизации.

Существует несколько подходов к определению экономической категории "амортизация". Согласно одному из них, амортизация - это процесс перенесения стоимости средств труда по мере износа на производимый продукт. Как правило, процесс амортизации подразумевает дальнейшее использование перенесенной стоимости в новых циклах производства. Амортизационные фонды, формируемые предприятием (или иным экономическим субъектом), являются важнейшим источником для его развития.

Под ускоренной амортизацией понимается формирование амортизационных фондов в более высоком темпе, чем происходит реальный (физический) износ оборудования и других средств производства. Она может быть применена для снижения налога с прибыли, уплачиваемого предприятиями, и стимулирования, таким образом, инвестиций. На современном этапе развития российской экономики метод ускоренной амортизации становится важнейшим инструментом стимулирования малого и среднего предпринимательства.

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

Понятие "ценная бумага" в общем случае является многозначным термином. В узком смысле под ценной бумагой может пониматься документ, удостоверяющий некоторые права ее владельца по отношению к эмитенту (субъекту, выпустившему данную бумагу).

Среди основных видов ценных бумаг (активов), обращающихся на современном рынке, можно выделить:

облигации - письменные долговые обязательства эмитента выплатить полученную сумму с процентами в определенный срок;

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

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

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

Тема 9. Организация вычислений в электронных таблицах

Создание и использование формул. Адресация данных. Виды ссылок. Создание и использование имен диапазонов ячеек, строк, столбцов.

Применение в вычислениях стандартных функций. Виды функций. Математические и логические функции. Функции даты и времени. Статистические функции. Функции просмотра и ссылки. Функции баз данных. Финансовые функции. Копирование формул. Обработка ошибок в расчетных формулах.

Способы организации вычислений. Компьютерный практикум

Формульные выражения, их назначение, способы записи и правила ввода. Ссылки и их виды.

Формула – это краткая запись некоторой последовательности действий, приводящих к конкретному результату. Формула может содержать не более 1024 символов. Структуру и порядок элементов в формуле определяет ее синтаксис.

Все формулы в Excel должны начинаться со знака равенства. Без этого знака все введенные символы рассматриваются как текст или число, если они образуют правильное числовое значение.

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

По умолчанию вычисления по формуле осуществляется слева направо, начиная с символа «=». Для изменения порядка вычисления в формуле используются скобки.

Пример формулы:

=А1+В1

Пример функции:

=ВПР(A4;$A$34:$D$40;4;ЛОЖЬ)

В Excel включено 4 вида операторов: арифметические, текстовые, операторы сравнения, адресные операторы.

Арифметические операторы используются для выполнения основных математических вычислений над числами. Результатом вычисления формул, содержащих арифметические операторы, всегда является число. К арифметическим операторам относятся: +, -, *, /, %,^.

Операторы сравнения используются для обозначения операций сравнения двух чисел. Результатом вычисления формул, содержащих операторы сравнения, являются логические значения Истина или Ложь. К операторам сравнения относятся: =, >, <, >=, <=, <>.

Текстовый оператор & осуществляет объединение последовательностей символов в единую последовательность.

Адресные операторы объединяют диапазоны ячеек для осуществления вычислений. К адресным операторам относятся:

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

, - оператор объединения, который ссылается на объединение ячеек диапазона. Например, СУММ(В5:В15,С15:С25);

“ “ – оператор пересечения, который ссылается на общие ячейки диапазона. Например, в формуле СУММ(В4:С6 В4:D4) ячейки В4 и С4 являются общими для двух диапазонов. Результатом вычисления формулы будет сумма этих ячеек.

Приоритет выполнения операций:

операторы ссылок (адресные) «:», «,», «»;

знаковый минус ‘-‘

вычисление процента %;

арифметические ^, *, /, +, -;

текстовый оператор &;

операторы сравнений =, <, >, <=, >=, <>.

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

После ввода формулы в ячейку рабочего листа на экране в окне рабочего листа в ячейку выводится результат вычисления. Для вывода в ячейки формул следует установить флажок Формулы на вкладке Вид команды Параметры меню СЕРВИС.

Ссылка является идентификатором ячейки или группы ячеек в книге. При создании формул, содержащих ссылки на ячейки, формула связывается с ячейками книги. Значение формулы зависит от содержимого ячеек, на которые указывают ссылки, и оно изменяется при изменении содержимого этих ячеек. С помощью ссылок в формулах можно использовать данные, находящиеся в различных местах листа, или использовать значение одной и той же ячейки в нескольких формулах. Кроме того, можно ссылаться на ячейки, находящиеся на других листах книги, или в другой книге, или даже на данные другого приложения. Ссылки на ячейки других книг называются внешними. Ссылки на данные других приложений называются удаленными.

В Excel существуют три типа ссылок: относительные, абсолютные, смешанные.

Относительная ссылка указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула, например «на две строки выше». При перемещении формулы относительная ссылка изменяется, ориентируясь на ту позицию, в которую переносится формула. Например, если в клетке С1 записана формула: =А1+В1, то при копировании ее в клетку С2 формула будет иметь следующие относительные ссылки =А2+В2; при копировании в D1: =В1+С1.

Абсолютными являются ссылки на ячейки, имеющие фиксированное расположение на листе. Эти ссылки не изменяются при копировании формул. Абсолютная ссылка содержит знак $ перед именем столбца и именем строки. Например: $A$1

Смешанные ссылки - это ссылки, являющиеся комбинацией относительных и абсолютных ссылок. Например, фиксированный столбец и относительная строка: $D6.

Ссылки на ячейки других листов книги имеют следующий формат:

<имя раб.листа>!ссылка на ячейку, например: Лист2!А1:А10.

Если имя рабочего листа содержит пробелы, то оно заключается в одинарные кавычки, например: ‘лицевой счет’!А1:А10.

Excel позволяет ссылаться на диапазон ячеек нескольких рабочих листов. Такая ссылка называется объемной. Например: Лист1:Лист5!$A$1:$D$3.

Ссылки на ячейки других книг имеют следующий формат:

[имя книги]<имя листа>!ссылка на ячейку, например: [книга2]Лист3!Е5:Е15.

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

В большинстве случаев работа с текстовыми значениями происходит так же, как с числами. Для объединения текстовых значений используется оператор &, причем таких операторов в формуле может быть несколько. С помощью оператора & можно объединять и числовые значения. В результате будет сформирован числовой текст. Этот оператор можно также использовать для объединения текстовых и числовых значений.