Опубликован: 26.05.2021 | Уровень: для всех | Доступ: платный
Лекция 10:

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

< Лекция 9 || Лекция 10 || Лекция 11 >
Аннотация: Цель работы: научиться работать с финансовыми функциями Excel и создавать отчеты. Содержание работы: Использование финансовых функций при экономических расчётах. Составление финансовых отчетов в Excel. Порядок выполнения работы: Изучить методические указания. Выполнить задания. Оформить отчет и ответить на контрольные вопросы.

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

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, то функция ПЛТ возвращает значение:

$$P=\frac{i(1+i)^{n}}{(1+i)^{n}-1}$$

где: Р – пс; 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% годовых, получить предлагаемую вам прибыль. В нашем случае чистый текущий объем вклада равен

$$\sum \limits_{j=1}\limits^{n}\frac{P_{kj}}{(1+i)^{k_{i}}}$$

Синтаксис:

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

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

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

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

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

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

$$\sum \limits_{j=1}\limits^{n}\frac{P_{j}}{(1+i)^{j}}$$

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

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

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

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

Синтаксис:

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

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

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

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

$$\sum \limits_{j=0}\limits^{n}\frac{P_{j}}{(1+i)^{j}}=0$$

Примечание

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)

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

Ячейка Формула
C2 =ЕСЛИ(В2=1;"год"; ЕСЛИ(В2<=4;"года";"лет"))
B6 =ЕСЛИ(В1<В5; "Выгодно дать деньги в долг"; ЕСЛИ (В5=В1; "Варианты равносильны"; "Выгоднее деньги положить под проценты") )
Расчет эффективности капиталовложений

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

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

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

Синтаксис:

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

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

$$А \frac{(1+i)^{n}-1}{i(1+i)^{n}}$$

где: А – выплата; 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).

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

Ячейка Имя
Bl Процент
В2 Срок
ВЗ Ежегодная_плата
В4 Размер_ссуды

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

Ячейка Формула Описание
ВЗ =ПЛТ (Процент; Срок; -Размер_ссуды) Ежегодная плата
D6 =Размер_ссуды Начальный остаток долга
А7 =А6+1 Номер года
В7 =D6*Процент Плата по процентам за первый год
С7 =Ежегодная_плата-В7 Основная плата за первый год
D7 =ЕСЛИ (D6-C7<>0;D6-C7) Остаток долга за первый год

Примечание

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

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

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

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

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

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

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

Синтаксис:

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

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

ПЛПj=ibj-1 ,

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

ВJ= Вj-1 – OCHПj при $j \in [0; >n]$, где:

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

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

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

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

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

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

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

$$А \frac{(1+i)^{n}-1}{i},$$

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

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

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

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

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

Синтаксис:

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

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

$$log_{1+i}\left( \frac{P_{i}}{A}+1\right)$$

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

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

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

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

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

Синтаксис:

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

Примечание

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

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

$$P=А \frac{(1+i)^{n}-1}{i(1+i)^{n}}$$

где: 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 дней. Для удобства обработки данных вместо неограниченного интервала рассматриваем интервал, ограниченный сверху очень большим числом. С практической точки зрения это одно и то же, что и неограниченный сверху интервал.

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

Ячейка Формула Описание
F2 =ДАТА(2000; 7;15) Дата переучета
E2 =ЕСЛИ(D2=0;$F$2-C2;'' '') Возвращает период просрочки. Выберите ячейку Е2 и, потянув за маркер заполнения, заполните диапазон Е3:Е22
G3 {=СУММ(($E$2:$E$22>=I3)*($E$2:$E$22<=J3))} Эта формула массива возвращает число заказов со сроком просрочки до 14 дней. Не забудьте завершить ее ввод нажатием комбинации клавиш <Ctrl>+<Shift>+<Enter>. Выберите ячейку G3 и протяните маркер заполнения вниз на диапазон G4:G5 для определения числа просроченных заказов для других временных интервалов
H3 {=СУММ(($E$2:$E$22>=I3)*($E$2:$E$22<=J3)*($B$2:$B$22))} Эта формула массива возвращает суммарную стоимость заказов периодом просрочки до 14 дней. Не забудьте завершить ее ввод нажатием комбинации клавиш <Ctrl>+<Shift>+<Enter>. Выберите ячейку НЗ и протяните маркер заполнения на диапазон Н4: Н5 для определения суммарной стоимости просроченных заказов для других временных интервалов

Функция ДАТА (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).

Таблица 10.1. Стоимость квартир и комиссионных
Ячейка Значение (стоимость квартиры) Ячейка Значение (комиссионные от продажи)
А1 1000000 В1 7000
А2 2000000 В2 14000
А3 3000000 В3 21000
А4 4000000 В4 28000

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

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

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

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

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

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

  • лист Коммивояжер со списком всех коммивояжеров вашей фирмы.
Стоимость и число заказов, выполненных каждым из коммивояжеров

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

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

Ячейка Формула Описание
Е2 =СЧЁТЕСЛИ(Заказы!$A$2:$A$10;A2) Возвращает число заказов, выполненных Ивановым. Выберите ячейку Е2 и протяните маркер заполнения вниз на диапазон Е3:Е4, для определения числа заказов, выполненных каждым из коммивояжеров
F2 =СУММЕСЛИ(Заказы!$A$2:$A$10;A2;Заказы!$B$2:$B$10) Возвращает стоимость заказов, выполненных Ивановым. Выберите ячейку F2 и протяните заполнения на диапазон F3:F4 для определения стоимости заказов, выполненных каждым из коммивояжеров

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

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

Таблица 10.2. Правила расчета комиссионных
Объем продаж за неделю, руб. Комиссионные, %
От 0 до 9999 8
От 10000 до 19999 10
От 20000 до 39999 12
Более 40000 14
Расчет комиссионных

Рис. 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.

Таблица 10.3. Правила оплаты труда
Выпущено дисков, шт. Оплата труда, руб./шт.
За первую тысячу 0,3
За вторую тысячу 0,4
За третью тысячу 0,5
За четвертую тысячу 0,6
За превышение над четвертой тысячей 0,7

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

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

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

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

Диапазон Имя
В1 ЗаказШт
В2 ПродажнаяЦена
В3 СтоимостьУп
В4 СтоимостьМат
В9 ОбщиеИздержки
D2:D6 ДискиШт
E2:E6 ОплатаРубШт
F2:F6 ОплатаРуб

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

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

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

Ячейка Формула Описание
В6 =ЗаказШт*СтоимостьМат Стоимость упаковки
В7 =ЗаказШт*СтоимостьМат Стоимость материалов
В8 =СУММ(ОплатаРуб) Суммарная зарплата
В9 =СУММ(В6:В8) Суммарные издержки
В10 =ЗаказШт*ПродажнаяЦена-ОбщиеИздержки Суммарная прибыль

ЗАДАНИЯ

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

Вариант N P i A
1 7 170000 5 10
2 8 200000 6 10
3 9 220000 7 20
4 10 300000 8 20
5 11 350000 9 15
6 7 210000 10 15
7 8 250000 11 30
8 9 310000 12 30
9 10 320000 13 25
10 11 360000 14 25

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

Вариант n P P1 P2 P3 P4 P5
1 3 17000 5000 7000 8000
2 4 20000 6000 6000 9000 7000
3 5 22000 5000 8000 8000 7000 5000
4 3 30000 5000 10000 18000
5 4 35000 5000 9000 10000 18000
6 5 21000 4000 5000 8000 10000 11000
7 3 25000 8000 9000 10000
8 4 31000 9000 10000 10000 15000
9 5 32000 8000 10000 10000 10000 11000
10 3 36000 10000 15000 21000

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

Вариант n P A
1 7 170000 30000
2 8 200000 31000
3 9 220 000 33 000
4 10 300 000 34 000
5 11 350 000 41 000
6 7 210000 32 000
7 8 250 000 37000
8 9 310000 40 000
9 10 320 000 35 000
10 11 360 000 41 000

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

Вариант n P i
1 7 170000 5
2 8 200000 6
3 9 220000 7
4 10 300000 8
5 11 350000 9
6 7 210000 10
7 8 250000 11
8 9 310000 12
9 10 320000 13
10 11 360000 14

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

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

Вариант P A i
1 170000 31000 3
2 200000 32000 4
3 220 000 33 000 5
4 300 000 34 000 6
5 350 000 41 000 7
6 210000 32 000 8
7 250 000 37000 9
8 310000 40 000 10
9 320 000 35 000 4
10 360 000 41 000 5

6. Вас просят дать в долг Р руб. в день D и обещают вернуть Р1 руб. в день D1,P1, руб. – в день D2 и т.д.Pn руб. – в день Dn. Имеет ли смысл эта сделка при годовой ставке i % (i, Р, п, Р1,..., Рn из задания 2)?


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


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


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


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


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

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

< Лекция 9 || Лекция 10 || Лекция 11 >
Арсен Никифоров
Арсен Никифоров

Есть такие задания, и они никак не принимаются. Притом ошибки только по этим заданиям, в какой бы последовательности я их не заполнял. Как их заполнять??? Инструкций в заданиях нет. Там через запятые, подряд как число, через пробел, или надо текст весь писать через запятую или точку?

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

Илья Шевченко
Илья Шевченко
Россия, Краснодар
Даниил Стасилович
Даниил Стасилович
Россия, Краснодар