Финансовый анализ и построение отчетных таблиц

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

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

Рассмотрим функции финансового анализа MS Excel, а также варианты составления финансовых отчетов от расчета затрат на производство до составления отчетной ведомости определения просроченных платежей.

Финансовая функция ПЛТ

Пример 1. Рассмотрим пример расчета с помощью функции рабочего листа ПЛТ 30-летней ипотечной ссуды со ставкой 8% годовых при начальном взносе 20% суммы и ежемесячной (ежегодной) выплате.

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

Функция ПЛТ(PMT) вычисляет величину выплаты за один период годовой ренты (например, регулярных платежей по займу) при постоянной процентной ставке.

Синтаксис:

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

• cтавка – процентная ставка за период;
• кпер – общее число периодов выплат;
• пс – текущее значение, т. е. общая сумма, которую составят будущие платежи;
• бс – будущая стоимость или баланс наличности, который нужно достичь после последней выплаты.

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

• тип – число, равное 0 или 1, обозначающее, когда должна производиться выплата. Если значение параметра типравно 0 или он опущен, то оплата производится в конце периода, если его значение равно 1, то в начале периода.

Если БС = 0 и тип – 0, то функция ПЛТ возвращает значение:

где: Р – пс; i – ставка; n – кпер.

Примечание

Важно быть последовательным в выборе единиц измерения для задания значений аргументов ставка и кпер. Например, если производятся ежемесячные выплаты по четырехгодичному займу из расчета 12% годовых, то установите значение аргумента ставка равным 12%/12, а значение аргумента кпер равным 4x12. Если же производятся ежегодные платежи по тому же займу, то установите значение аргумента ставка равным 12%, а значение аргумента кпер равным 4.

увеличить изображение
Рис. 10.1. Результат расчета ипотечной ссуды

увеличить изображение
Рис. 10.2. Формулы для расчета ипотечной ссуды

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

Интервал выплат – это последовательность постоянных денежных платежей, осуществляемых в непрерывный период.

Например, заем под автомобиль или заклад являются интервалами выплат.

В функциях, связанных с интервалами выплат, отдаваемые вами деньги, такие как депозит на накопление, представляются отрицательным числом. Деньги же, которые вы получаете, такие как чеки на дивиденды, представляются положительным числом. Например, депозит в банк на сумму 1000 руб. представляется значением параметра пс равным -1000, если вы вкладчик, и значением 1000, если вы – банк.

Расчет эффективности неравномерных капиталовложений с помощью функций ЧПС, ВНДОХ и Подбора параметра

Рассмотрим следующую задачу. Вас просят дать в долг 10 000 руб. и обещают вернуть через год 2000 руб., через два года – 4000 руб., через три года – 7000 руб. При какой годовой процентной ставке эта сделка имеет смысл?

На рис. 10.3 рис. 10.3 в ячейку В8 введена формула, зависящая от функции ЧПС (NPV):

=ЧПС(В7;ВЗ:В5)

Кроме того, для автоматизации составления таблицы в ячейку С6 введена формула:

=ЕСЛИ(В6=1;"год"; ЕСЛИ(В6<=4;"года";"лет"))

Для решения задачи выполните следующие действия:

1. Первоначально в ячейку B7 введите произвольный процент, например, 8%.

2. В меню Данные > Анализ "что-если" выбрать команду Подбор параметра. Заполните поля ввода отобразившегося на экране диалогового окна Подбор параметра, как показано на рис. 10.3 рис. 10.3.

В поле Установить в ячейке дается ссылка на ячейку В8, в которой вычисляется чистый текущий объем вклада по формуле:

=ЧПС (В7;B3:В5).

В поле Значение введите размер ссуды, равный 10000. В поле Изменяя значения ячейки задайте ссылку на ячейку B7, в которой вычисляется годовая процентная ставка.

3. Нажмите кнопку ОК. Средство Подбор параметра рассчитает, при какой годовой процентной ставке чистый текущий объем вклада равен 10000 р.

Искомая процентная ставка выводится в ячейку В7. В нашем случае годовая учетная ставка равна 11,79% (рис. 10.4 рис. 10.4). Можно сделать вывод: если банки предлагают большую годовую процентную ставку, то предлагаемая сделка не выгодна.

увеличить изображение
Рис. 10.3. Исходные данные по расчету годовой процентной ставки и диалоговое окно Подбор параметра

Функция ЧПС возвращает чистый текущий объем вклада, вычисляемый на основе ряда последовательных поступлений наличных и нормы амортизации.

Чистый текущий объем вклада – это сегодняшний объем будущих платежей (отрицательные значения) и поступлений (положительные значения). Например, вам предлагают следующую сделку. У вас берут в долг некоторую сумму денег и предлагают через k1 год вернуть Р_k1 денег, через k2 года вернуть Р_k2 денег и т. д. через kn лет вернуть Р_kn денег.

Кроме данной сделки у вас есть альтернативный способ использования ваших средств, например, положить их в банк под i% годовых. Тогда чистым текущим объемом вклада считается та сумма денег, которой вы должны располагать в начальный год, чтобы, положив их в банк под i% годовых, получить предлагаемую вам прибыль. В нашем случае чистый текущий объем вклада равен

Синтаксис:

НПЗ (ставка; 1-е значение; 2-е значение;…).

• ставка – процентная ставка за период;
• 1-е значение, 2-е значение, ...– от 1 до 29 аргументов, представляющих расходы и доходы. 1-е значение, 2-е значение,...должны быть равномерно распределены по времени и осуществляться в конце каждого периода. НПЗ использует порядок аргументов 1-езначение, 2-е значение, ... для определения порядка поступлений и платежей.

Считается, что инвестиция, значение которой вычисляет функция ЧПС, начинается за один период до даты денежного взноса 1-го значения и заканчивается с последним денежным взносом в списке.

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

Рис. 10.4. Результат расчета годовой процентной ставки и окно Результат подбора параметра

Если n – это количество денежных взносов в списке значений, P_j – j значение и i – ставка, то возвращаемое значение функцией ЧПС вычисляется по формуле:

Функция ЧПС тесно связана с функцией ВСД (внутренней скоростью оборота). Функция ВСД возвращает скорость оборота, для которой значение функции ЧПС равняется нулю, т. е.

ЧПС(ВСД(…); ...) =0

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

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

Синтаксис:

ВСД (значение; прогноз)

• Значение – массив или ссылка на ячейки, содержащие числовые величины, для которых вычисляется внутренняя скорость оборота средств.

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

• прогноз – величина, о которой предполагается, что она близка к результату ВСД.

Если п+1 – это количество значений в списке, P_j – j-e значение, то ВНДОХ является корнем относительно i (ставки) следующего уравнения:

Примечание

MS Excel использует метод итераций для вычисления ВСД. Начиная со значения прогноз, функция вндох выполняет циклические вычисления, пока не получит результат с точностью 0,00001. Если функция ВСД не может получить результат после двадцати попыток, то возвращается значение ошибки #ЧИСЛО! В большинстве случаев нет необходимости задавать прогноз для вычислений с помощью функции ВСД. Если прогноз опущен, то он полагается равным 0,1 (10%).

Если ВСД выдает значение ошибки #число!, или если результат далек от ожидаемого, можно попытаться выполнить вычисления еще раз с другим значением аргумента прогноз.

Таким образом, задачу, сформулированную в начале данного раздела, можно решить также при помощи функции вндох. Для этого в ячейку B2 вместо 10000р. надо ввести -10000р., а в ячейку В7 – функцию ВСД(В2:В5), которая и найдет минимальную годовую учетную ставку.

Расчет эффективности капиталовложений с помощью функции ПС

Рассмотрим следующую задачу. Вас просят дать в долг 10 000 руб. и обещают возвращать по 2000 руб. в течение 6 лет. Будет ли выгодна эта сделка при годовой ставке 7%?

В расчете, приведенном на рис. 10.5 рис. 10.5, в ячейку В5 введена формула с функцией ПС о которой речь пойдет ниже в данном разделе:

=ПС(B4;B2;-B3)

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

Рис. 10.5. Расчет эффективности капиталовложений

Как видно из рис. 10.5 рис. 10.5, в рассмотренном случае деньги выгоднее положить под проценты.

Функция ПС(PV) возвращает текущий объем вклада на основе постоянных периодических платежей. Функция ПС аналогична функции НПЗ. Основное различие между ними заключается в том, что ПС допускает, чтобы денежные взносы происходили либо в конце, либо в начале периода. Кроме того, в отличие от денежных взносов переменной величины в функции НПЗ, денежные взносы в функции ПС должны быть постоянны в течение всего периода инвестиции.

Синтаксис:

ПС (ставка; кпер; плт; бс; тип) (PV).

• Ставка – процентная ставка за период;
• Кпер – общее число периодов выплат;
• Плт – величина постоянных периодических платежей;
• Бс – будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если параметр бс опущен, то его значение полагается равным 0 (будущая стоимость займа, например, равна 0);
• Тип – число, равное 0 или 1, обозначающее, когда должна производиться выплата. Если значение параметра тип равно 0 или он опущен, то оплата производится в конце периода, если же его значение равно 1, то в начале периода.

Если тип = 0 и бс = 0, то значение функции ПС вычисляется по следующей формуле:

где: А – выплата; i – ставка; n – кпер.

Именование диапазонов и ячеек

Для того чтобы избавить себя от трудностей по расшифровке ссылок в формулах, следует воспользоваться именами ячеек или диапазонов ячеек. Например, можно присвоить итоговой области, состоящей из диапазона ячеек С2:C10 имя Итого. Теперь для того, чтобы найти суммарные итоги, достаточно воспользоваться формулой =СУММ(Итого) вместо сухой и безликой формулы =СУММ(С2:С10).

Использование имен ячеек или диапазонов ячеек позволяет:

• уменьшить вероятность появления ошибок в формулах. При вводе неизвестного имени MS Excel выводит сообщение #имя?;
• легко читать формулы. Например, формула = Доход-Издержки значительно легче для понимания, чем =В20-С20;
• легко ссылаться на один и тот же набор имен во всей рабочей книге;
• облегчить ввод ссылок на рабочие листы, находящиеся в других рабочих книгах.

Для того чтобы присвоить имя ячейке или диапазону необходимо:

1. Выделить ячейку или диапазон.
2. Выбрать команду Присвоить имя на вкладке Формулы в группе Определенные имена.
3. Ввести подходящее имя в диалоговом окне Присвоение имени (рис. 10.6 рис. 10.6)

Рис. 10.6. Присвоение имени ячейке и диалоговое окно

Чтобы использовать в формуле определенное имя, его также можно ввести одним из указанных ниже способов.

• c помощью ввода. Введите имя, например, в качестве аргумента формулы.
• c помощью автозавершения формулы. Используйте раскрывающийся список автозавершения формулы, в котором действительные имена перечислены автоматически.
• выбором из команды Использовать в формуле. Выберите определенное имя на вкладке Формулы в группе Определенные имена из списка Использовать в формуле.

Для изменения или удаления имени:

1. Выбрать команду Присвоить имя на вкладке Формулы в группе Определенные имена.
2. Перейти в поле Имя и отредактировать его.
3. Для удаления имени нажмите кнопку Удалить.
4. Нажмите кнопку ОК.

Создание имени путем выделения ячеек на листе

В имена можно преобразовать существующие заголовки строк и столбцов.

1. Выберите диапазон, которому нужно присвоить имя, включая заголовки строк и столбцов.
2. На вкладке Формулы в группе Присвоенные имена выберите команду Создать из выделенного.
3. В диалоговом окне Создание имен из выделенного диапазона укажите место, содержащее заголовки, установив флажок в строке выше, в столбце слева, в строке ниже или в столбце справа.

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

Применение имени

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

Для этого:

1. Выберите ячейку или диапазон ячеек, для которых вы хотите применить имя
2. Выполните команду Использовать в формуле в меню Определенные имена (Данные). На экране отобразится диалоговое окно со списком всех имен.
3. Выберите требуемое имя.
4. Переместите курсор в любую свободную ячейку.

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

Финансовые функции ПРПЛТ и ОСПЛТ

Пример. Рассмотрим пример вычисления основных платежей, платы по процентам, общей ежегодной платы и остатка долга на примере ссуды 100 000 руб. на срок 5 лет при годовой ставке 2% (рис. 10.7 рис. 10.7).

Итак, присвойте ячейкам следующие имена:

Кроме того, в ячейки введите формулы:

Примечание

Если в ячейку D7 ввести формулу =D6-C7, то из-за денежного формата при условии что в ячейках D6 и С7 введены нули, в ячейку D7 будет выведен -р, а не 0р. Для избежания этой ситуации в ячейку D7 вводится формула с функцией ЕСЛИ.

В остальные годы эти платы определяются перемещением маркера заполнения диапазона А7:D7 вниз по столбцам до тех пор, пока в столбце остатка долга не появится ноль.

Рис. 10.7. Вычисление основных платежей и платы по процентам

Отметим, что основную плату и плату по процентам можно было непосредственно найти при помощи функций ОСПЛТ (РРМТ) и ПРПЛТ (IRMT), соответственно.

Функция ПРПЛТ возвращает платежи по процентам за данный период на основе периодических постоянных выплат и постоянной процентной ставки.

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

Синтаксис:

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

• ОСПЛТ (ставка; период; кпер; пс; бс; тип)
• Ставка – процентная ставка за период;
• период – период, для которого требуется найти прибыль; должен находиться в интервале от 1 до кпер;
• кпер – общее число периодов выплат;
• Пс – текущее значение, т. е. общая сумма, которую составят будущие;
• Бс – будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если Бс опущено, оно полагается равным 0 (будущая стоимость займа, например, равна 0);
• тип – число, равное 0 или 1, обозначающее, когда должна производиться выплата. Если значение параметра тип равно 0 или этот параметр опущен, то оплата производится в конце периода, если же он равен 1, то в начале периода.

Функции ПРПЛТ и ОСПЛТ тесно между собой связаны, а именно:

ПЛП_j=i_bj-1 ,

ОСНП_j = А- ПЛП_j,

В_J= В_j-1 – OCHП_j при , где:

• j – номер периода; п – кпер;
• ПЛП_j, ОСНП_j и В_J – это ПРПЛТ, ОСПЛТ И остаток долга, соответственно, за j-й период,
• ПЛП_о=0, ОСНП_О = 0, В₀ =ПС;
• А – величина выплаты за один период годовой ренты на основе постоянных выплат и постоянной процентной ставки, вычисляемая с помощью функции ПЛТ.

Финансовые функции БС, КПЕР и СТАВКА

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

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

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

• Ставка – процентная ставка за период;
• Кпер – общее число периодов выплат;
• Плт –величина постоянных периодических платежей;
• пс – текущее значение, т. е. общая сумма, которую составят будущие платежи;
• Тип – число, равное 0 или 1, обозначающее, когда должна производиться выплата. Если значение параметра тип равно 0 или он опущен, то оплата производится в конце периода, если же он равен 1, то в начале периода.

Если тип = 0 и пс = 0, то функция БС вычисляет по следующей формуле:

где: А – выплата, i – ставка, n – кпер.

Приведем пример использования функции БС. Предположим, вы хотите зарезервировать деньги для специального проекта, который будет осуществлен через год. Допустим, вы собираетесь вложить 1000 руб. при годовой ставке 6%, а затем хотите вкладывать по 100 руб. в начале каждого следующего месяца в течение следующих 12 месяцев. Сколько денег будет на счету в конце12 месяцев? Результат вычисляется формулой

=БС(6/12%; 12; -100; -1000; 1),

которая возвращает 2301.40р.

Функция КПЕР (NPER) вычисляет общее количество периодов выплаты для данного вклада на основе периодических постоянных выплат и постоянной процентной ставки.

Синтаксис:

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

• Ставка – процентная ставка за период;
• Плт – величина постоянных периодических платежей;
• Пс – текущее значение, т. е. общая сумма, которую составят будущие платежи;
• Бс – будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если параметр Бс опущен, то его значение полагается равным 0 (будущая стоимость займа, например, равна 0);
• тип – число, равное 0 или 1, обозначающее, когда должна производиться выплата. Если значение параметра тип равно 0 или он опущен, то оплата производится в конце периода, если его значение равно 1, то в начале периода.

При тип = 0 и бс = 0 функция КПЕР вычисляет по следующей формуле

где: Р–пс, i – ставка, А– выплата.

Например, если вы берете в долг 1000 руб. при годовой ставке 1% и собираетесь выплачивать по 100 руб. в год, число выплат определяется формулой

=КПЕР(1%; -100; 1000)

которая возвращает значение 11.

ФУНКЦИЯ СТАВКА (RATE) вычисляет процентную ставку за один период, необходимую для получения определенной суммы за заданный срок путем постоянных взносов. Функция СТАВКА вычисляет процентную ставку методом итераций и может не найти решение. Если после 20 итераций погрешность определения ставки превышает 0.0000001, то функция СТАВКА возвращает значение ошибки #ЧИСЛО!.

Синтаксис:

СТАВКА (кпер; плт; пс; бс; тип; нач_прибл).

• Кпер – общее число периодов выплат;
• Плт – величина постоянных периодических платежей; текущее значение, т. е. общая сумма, которую составят будущие платежи;
• Пс – текущее значение, т.е. общая сумма, которую составят будущие платежи;
• Бс – будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если параметр бс опущен, то его значение полагается равным 0 (будущая стоимость займа, например, равна 0);
• тип – число, равное 0 или 1, обозначающее, когда должна производиться выплата. Если значение параметра тип равно 0 или он опущен, то оплата водится в конце периода, если же его значение равно 1, то в начале периода;
• нач_прибл – предполагаемая величина нормы. Если параметр нач_прибл опущен, то его значение полагается равным 10%.

Примечание

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

При бс = 0 и тип= 0 функция СТАВКА является корнем следующего уравнения:

где: A – выплата, i – СТАВКА, n – кпер, P – ПС.

Рассмотрим пример использования функции СТАВКА. Чтобы определить процентную ставку для четырехлетнего займа в 8000 руб. с ежемесячной выплатой в 200 руб., можно использовать формулу:

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

которая вычисляет значение 0.77%. Отметим, что это месячная процентная ставка т. к. период равен месяцу.

Создание простейшей отчетной ведомости

Предположим, что вы – менеджер торговой сети магазинов "Магнит" и вам необходимо составить отчетную ведомость по результатам ее деятельности за летний период с простейшей обработкой данных (рис. 10.8 рис. 10.8).

увеличить изображение
Рис. 10.8. Отчет продаж сети магазинов

В этой отчетной ведомости надо определить:

• суммарную и среднюю выручку каждого из магазинов за отчетный период;
• суммарную выручку всех магазинов за каждый месяц отчетного периода;
• место, которое занимает каждый из магазинов в суммарном объеме выручки;
• долю каждого из магазинов в суммарном объеме выручки;
• количество магазинов, имеющих суммарную выручку до 1000 млн. руб., от 1000 млн. руб. до 1500 млн. руб., от 1500 млн. руб. до 2000 млн. руб. и свыше 2000 млн. руб.

При составлении данного отчета необходимо выполнить следующие действия:

1. Для нахождения суммарной выручки первого магазина введите в ячейку Е3 формулу =СУММ (В3:D3)
2. Выберите ячейку Е3, расположите указатель мыши на маркере заполнения и переместите его вниз на диапазон Е4:Е8.

Это позволит найти суммарную выручку каждого из магазинов.

1. Для нахождения суммарного объема выручки всех магазинов за июнь введите в ячейку В9 формулу =СУММ (В3:В8)
2. Выберите ячейку В9, расположите указатель мыши на маркере заполнения и переместите его вправо на диапазон С9:Е9. Это позволит найти суммарную выручку сети магазинов как за каждый месяц в отдельности, так и за весь период в целом.
3. Для нахождения средней выручки первого магазина введите в ячейку G3 формулу =СРЗНАЧ (В3:D3)
4. Выберите ячейку G3, расположите указатель мыши на маркере заполнения и переместите его вниз на диапазон G4:G8. Это позволит найти среднюю выручку каждого из магазинов.
5. Для определения доли объема выручки первого магазина по отношению к суммарной выручке всей сети магазинов введите в ячейку Н3 формулу =ЕЗ/$Е$9
6. Выберите ячейку Н3, расположите указатель мыши на маркере заполнения и переместите его вниз на диапазон Н4:Н8. Это позволит найти долю объема выручки каждого из магазинов по отношению к суммарной выручке всей сети магазинов.
7. Выберите диапазон Н3:Н8 и нажмите кнопку Процентный формат панели инструментов Форматирование. Это позволит установить процентный формат в ячейках выбранного диапазона.
8. Для определения места первого магазина в суммарной выручке всей сети магазинов введите в ячейку F3 формулу =РАНГ (Е3; $E$3:$E$8)
9. Выберите ячейку F3, расположите указатель мыши на маркере заполнения и переместите его вниз на диапазон F4:F8. Это позволит найти место каждого магазина в суммарной выручке всей сети магазинов.
10. Для определения количества магазинов, имеющих суммарную выручку до 1000 млн руб., от 1000 млн руб. до 1500 млн руб., от 1500 млн руб. до 2000 млн руб. и свыше 2000 млн руб., сначала заполните ячейки согласно рис. 10.8 рис. 10.8, а затем выберите диапазон J3:J6 и введите в него формулу массива (не забудьте завершить ее ввод нажатием комбинации клавиш <Ctrl>+<Shift>+<Enter>)

{=ЧАСТОТА(Е3:Е8; I3:I5)},

При составлении отчетной ведомости использовались функции СРЗНАЧ, РАНГ и ЧАСТОТА.

Функция СРЗНАЧ (AVERAGE) находит среднее арифметическое значение из указанного диапазона ячеек.

Синтаксис:

СРЗНАЧ (число1; число2; …).

ФУНКЦИЯ РАНГ (RANK) возвращает ранг числа в списке чисел. Ранг числа – это его величина относительно других значений в списке. (Если список отсортировать, то ранг числа будет его позицией.).

Синтаксис:

РАНГ (число; ссылка; порядок).

• число – число, для которого определяется ранг;
• ссылка – массив или ссылка на список чисел. Нечисловые значения в ссылке игнорируются;
• порядок – число, определяющее способ упорядочения. Если значение параметра порядок равно 0 или он опущен, то MS Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке убывания. Если значение параметра порядок является любым ненулевым числом, то MS Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке возрастания.

Примечание

Функция РАНГ присваивает одинаковым числам одинаковый ранг.

Функция ЧАСТОТА (FREQUENCY)возвращает распределение частот в виде вертикального массива. Для данного множества значений и данного множества карманов (т.е. интервалов в математическом смысле) частотное распределение подсчитывает, сколько исходных значений попадает в каждый интервал.

Синтаксис:

ЧАСТОТА (массив_данных; двоичный_массив).

• массив_данных – массив или ссылка на множество данных, для которых вычисляются частоты; если массив_данных не содержит значений, то функция ЧАСТОТА возвращает массив нулей;
• двоичный_массив – массив или ссылка на множество интервалов, в которые группируются значения аргумента массив_данных. Если двоичный_массив не содержит значений, то функция ЧАСТОТА возвращает количество элементов в аргументе двоичный_массив.

Примечание

Функция СУММ позволяет не только складывать значения ее аргументов, но также и вычитать их. Например, пусть в ячейки А2 и В2 введен размер прибыли за июнь и июль в размере 10 000 руб. и 20 000 руб. соответственно, а в ячейку С2 – убытки за оба месяца в размере 5000руб. Тогда формула =СУММ (А2:В2;-С2) возвращает общую прибыль минус потери за отчетный период. Тот же самый результат дает формула =СУММ (А2: В2)-С2.

Расчет итоговой выручки по объему реализации

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

• таблица расценок товаров;
• таблица объемов реализации товаров.

Для того чтобы найти итоговую выручку в рублях реализации всех товаров за каждый месяц, надо в ячейку Е3 таблицы Отчет ввести формулу массива (не забудьте завершить ее ввод нажатием комбинации клавиш <Ctrl>+<Shift>+<Enter>):

{=СУММ(ВЗ:D3*Цена!$А$3:$С$3)},

а затем заполнить этой формулой диапазон Е4:Е5, потянув маркер заполнения вниз.

увеличить изображение
Рис. 10.9. Расчет итоговой выручки по объему реализации

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

=СУММПРОИЗВ(ВЗ:D3; Цена!$А$3:$С$3)

с ее последующим копированием и настройкой на диапазон Е4:Е5.

Расчет просроченных платежей

Предположим, что вы менеджер фирмы "ВЛАДОС" по продаже компьютеров. Компьютеры фирма продает в рассрочку, поэтому необходимо вести учет просроченных платежей. Просрочка платежей разбивается на "временные карманы" (рис. 10.10 рис. 10.10):

• от 1 до 14 дней;
• от 15 до 29 дней;
• от 30 до 10000 дней.

увеличить изображение
Рис. 10.10. Расчет просроченных платежей

На самом деле под последним интервалом подразумевается неограниченный интервал – не менее 30 дней. Для удобства обработки данных вместо неограниченного интервала рассматриваем интервал, ограниченный сверху очень большим числом. С практической точки зрения это одно и то же, что и неограниченный сверху интервал.

Итак, для проведения расчетов введите в ячейки рабочего листа следующие формулы:

Функция ДАТА (DATE) возвращает дату в числовом формате для заданной даты.

Синтаксис:

ДАТА (год; месяц; день).

• год – число от 1900 до 9999;
• месяц – число, представляющее номер месяца в году. Если месяц больше 12, то это число прибавляется к первому месяцу указанного года. Например, ДАТА(2000; 14; 2) возвращает числовой формат даты 14 февраля2000 года;
• день – число, представляющее номер дня в месяце. Если день больше числа дней в указанном месяце, то это число прибавляется к первому дню указанного месяца.

Приведем пояснения к формуле {=СУММ ( ($Е$2 : $Е$22>=I3) * ($Е$2 : $Е$22<=J3))}. Напомним, что в данном случае в ячейку I3 введено 1, а в ячейку J3 – 14.В формуле массива условие Е2:Е20>=1 возвращает массив, состоящий из 0 и 1, где 0 стоит на месте ячейки со значением меньше 1 и 1 – со значением не меньше 1.

Следовательно, данная формула вычисляет сумму произведений:

• элементов массива Е2:Е20>=1 (с единицами в случае просрочки заказа по крайней мере на один день и нулями в противном случае);
• элементов массива Е2:Е20<=14 (с единицами в случае просрочки до 14 дней и нулями в противном случае).

Таким образом, эта формула возвращает общее количество просроченных заказов сроком от 1 до 14 дней.

Приведем пояснения к формуле {=СУММ(($Е$2:$Е$22>=I3)*($E$2:$E$22<=J3)*($B$2:$B$22))}.

Напомним, что в ячейку I3 введено 1, а в ячейку J3 – 14. В этой формуле по отношению к предыдущей появился дополнительный сомножитель $В$2:$В$22. Поэтому данная формула вычисляет сумму произведений:

• элементов массива Е2:Е20>=1 (с единицами в случае просрочки заказа по крайней мере на один день и нулями в противном случае);
• элементов массива Е2:Е20<=14 (с единицами в случае просрочки заказа до 14 дней и нулями в противном случае);
• массива $В$2:$В$22 (с ценами процессоров).

Таким образом, эта формула возвращает суммарную стоимость просроченных заказов сроком от 1 до 14 дней.

Вычисление значения с использованием условия

Функция СЧЁТЕСЛИ (COUNTIF) возвращает количество непустых ячеек внутри интервала, удовлетворяющих заданному критерию.

Синтаксис:

СЧЁТЕСЛИ (диапазон; условие).

• диапазон – интервал, в котором нужно подсчитать ячейки;
• условие – в форме числа, выражения или текста, который определяет какие ячейки надо подсчитывать; например, критерий может быть выражен следующим образом: 17, "17", ">17", "Компьютер".

Рассмотрим пример. Пусть ячейки диапазона А1:А4 содержат следующие значения: яблоки, апельсины, персики, яблоки. Тогда =СЧЁТЕСЛИ(А1:А4;"яблоки") возвращает 2.

Пусть ячейки В1:В4 содержат 32, 54, 75 и 86 соответственно. Тогда =СЧЁТЕСЛИ(В1:В4;">50") возвращает 3.

Функция СУММЕСЛИ (SUMIF) суммирует ячейки, заданные критерием.

Синтаксис:

СУММЕСЛИ (диапазон; условие; диапазон_суммирования).

• диапазон – это интервал вычисляемых ячеек;
• условие – это критерий в форме числа, выражения или текста, который определяет, какая ячейка добавляется. Например, критерий может быть выражен как 32, "32", ">32", "яблоки";
• диапазон_суммирования – это фактические ячейки для суммирования. Ячейки в аргументе диапазон_суммирования складываются, если соответствующие им ячейки в аргументе диапазон удовлетворяют условию. Если интервал_суммирования опущен, то в расчетах участвуют ячейки в аргументе диапазон.

Пусть, имеются данные фирмы по работе с недвижимостью "Аякс" о стоимостях квартир и о комиссионных при их продаже (табл. 10.1 таблица 10.1).

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

=СУММЕСЛИ(А1:А4;">150000";В1:В4) возвращает 63000.

Продемонстрируем использование функций СЧЁТЕСЛИ и СУММЕСЛИ на примере создания отчетной ведомости, в которой необходимо проследить все заказы, размещенные коммивояжерами фирмы "Самовар", торгующей самоварами, и подсчитать объемы продаж по каждому из них.

Итак, в вашем распоряжении имеется рабочая книга, состоящая из двух рабочих листов (рис. 10.11 рис. 10.11):

• лист Заказы, где приведен список осуществленных продаж за текущий

день каждым из коммивояжеров вашей фирмы;

• лист Коммивояжер со списком всех коммивояжеров вашей фирмы.

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

Для проведения расчетов введите на рабочем листе Коммивояжер представленные ниже формулы:

Расчет комиссионных

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

Рис. 10.12. Расчет комиссионных

Для расчета комиссионных для объемов продаж, приведенных на рабочем листе (рис. 10.12 рис. 10.12), достаточно в ячейку С2 ввести следующую формулу, затем выбрать эту ячейку и, потянув за маркер заполнения, разместить формулы в диапазоне С3:С9 для определения комиссионных за каждую из указанных недель:

=ЕСЛИ(В2<=9999;В2*8%;0)+ЕСЛИ(И(10000<=В2;В2<=19999);
В2*10%;0)+ЕСЛИ(И(20000<=В2;В2<=39999);В2*12%;0)+
ЕСЛИ(40000<=В2;В2*14%;0)

Расчет затрат на производство

Составим отчетную ведомость по расчету затрат на производство компакт-дисков (рис. 10.13 рис. 10.13). Упаковка диска фирме обходится в 1 руб./шт., стоимость материалов – 4 руб./шт. Фирма продает готовые диски по цене 10 руб./шт. Технические возможности позволяют выпускать до 5000 дисков в день. Оплата труда рабочих является сдельной и зависит от количества выпущенных дисков по правилу, представленному в таблице 10.3.

Предположим, что поступил заказ на изготовление 4500 компакт-дисков. Необходимо посчитать суммарные издержки и прибыль после выполнения данного заказа.

увеличить изображение
Рис. 10.13. Расчет затрат на производство товара

Для упрощения чтения формул при помощи команды Вставка > Имя > Присвоить установите имена ячейкам и диапазонам ячеек, перечисленные ниже.

Зарплата рабочих в зависимости от объема выпущенных дисков находится в диапазоне F2:F7 по формуле массива (не забудьте завершить ее ввод нажатием комбинации клавиш <Ctrl>+<Shift>+<Enter>).

=ЕСЛИ (ЗаказШт-1000>ДискиШт;1000*ОплатаРубШт;
ЕСЛИ (ЗаказШт>ДискиШт;(ЗаказШт-ДискиШт)*ОплатаРубШт; 0))

Кроме того, для завершения расчетов введите в ячейки следующие формулы:

ЗАДАНИЯ

1. Вычислить n-годичную ипотечную ссуду покупки квартиры за Р руб. с годовой ставкой i % и начальным взносом А %. Сделать расчет для ежемесячных и ежегодных выплат для исходных данных, представленных в следующей таблице:

2. Вас просят дать в долг P руб. и обещают вернуть P₁руб. через год, Р₂руб. – через два года и т. д. P_n руб. – через nлет. При какой годовой процентной ставке эта сделка имеет смысл?

3. Вас просят дать в долг Р руб.. и обещают возвращать по А руб. в течение n лет. При какой годовой процентной ставке эта сделка имеет смысл?

4. Вычислить основные платежи, плату по процентам, общую ежегодную выплату и остаток долга на примере ссуды Р руб. под годовую ставку i% на срок п лет.

5. Вы берете в долг Р руб. под годовую ставку i% и собираетесь выплачивать по А руб. в год. Сколько лет займут эти выплаты?

выплачивать по А руб. в год. Сколько лет займут эти выплаты?

6. Вас просят дать в долг Р руб. в день D и обещают вернуть Р₁ руб. в день D₁,P₁, руб. – в день D₂ и т.д.P_n руб. – в день D_n. Имеет ли смысл эта сделка при годовой ставке i % (i, Р, п, Р₁,..., Р_n из задания 2)?

7. Составить отчетную ведомость реализации товаров п магазинами с месяца А по месяц В, приведенную ранее в примере "Создание простейшей отчетной ведомости"

8. Вы берете в долг Р руб. под годовую ставку i% и собираетесь отдавать по А руб. в год. Сколько лет займут выплаты?

9. Вы собираетесь вкладывать по А руб. в течение п лет при годовой ставке i%. Сколько денег будет на счету через п лет?

10. Определить процентную ставку для n летнего займа в Р руб. с ежегодной выплатой в А руб.

11. Составить таблицу начисления премии по итогам работы сети п магазинов с месяца А по месяц В по следующему правилу:

• если продукции продано не меньше чем на С руб., то комиссионные составляют /%;
• за первое м есто дополнительно начисляется j₁ %, за второе место – j₂% и т. д., за k-место дополнительно начисляется j_k %.