Как вычислить тарифные ставки в excel
Эффективная ставка возникает, когда имеют место Сложные проценты . Понятие эффективная ставка встречается в нескольких определениях. Например, есть Эффективная (фактическая) годовая процентная ставка, есть Эффективная ставка по вкладу (с учетом капитализации), есть Эффективная процентная ставка по потребительским кредитам . Разберемся, что эти ставки из себя представляют и как их рассчитать в MS EXCEL.
Эффективная (фактическая) годовая процентная ставка
В MS EXCEL есть функция ЭФФЕКТ(номинальная_ставка, кол_пер), которая возвращает эффективную (фактическую) годовую процентную ставку, если заданы номинальная годовая процентная ставка и количество периодов в году , в которые начисляются сложные проценты. Под номинальной ставкой здесь понимается, годовая ставка, которая прописывается, например, в договоре на открытие вклада. Предположим, что сложные проценты начисляются m раз в год. Эффективная годовая процентная ставка дает возможность увидеть, какая годовая ставка простых процентов позволит достичь такого же финансового результата, что и m-разовое наращение в год по ставке i/m, где i – номинальная ставка. При сроке контракта 1 год по формуле наращенной суммы имеем: S = Р*(1+i/m)^m – для сложных процентов, где Р – начальная сумма вклада. S = Р*(1+iэфф) – для простых процентов
Так как финансовый результат S должен быть, по определению, одинаков для обоих случаев, приравниваем оба уравнения и после преобразования получим формулу, приведенную в справке MS EXCEL для функции ЭФФЕКТ() iэфф =((1+i/m)^m)-1
Примечание . Если задана эффективная годовая процентная ставка, то величина соответствующей ей годовой номинальной процентной ставки рассчитывается по формуле
или с помощью функции НОМИНАЛ(эффективная_ставка, кол_периодов). См. файл примера .
Эффективная ставка по вкладу
Если договор вклада длится, скажем, 3 года, с ежемесячным начислением по сложным процентам по ставке i, то Эффективная ставка по вкладу вычисляется по формуле: iэфф =((1+i/12)^(12*3)-1)*(1/3) или через функцию ЭФФЕКТ( ): iэфф= ЭФФЕКТ(i*3;3*12)/3 Для вывода формулы справедливы те же рассуждения, что и для годовой ставки: S = Р*(1+i/m)^(3*m) – для сложных процентов, где Р – начальная сумма вклада. S = 3*Р*(1+iэфф) – для простых процентов (ежегодной капитализации не происходит, проценты начисляются раз в год (всего 3 раза) всегда на первоначальную сумму вклада). Если срок вклада =1 году, то Эффективная ставка по вкладу = Эффективной (фактической) годовой процентной ставке (См. файл примера ).
Эффективная процентная ставка по потребительским кредитам
Пример . Рассчитаем Эффективную ставку по кредиту со следующими условиями: Сумма кредита - 250 тыс. руб., срок - 1 год, дата договора (выдачи кредита) – 17.04.2004, годовая ставка – 15%, число платежей в году по аннуитетной схеме – 12 (ежемесячно). Дополнительные расходы – 1,9% от суммы кредита ежемесячно, разовая комиссия – 3000р. при открытии банковского счета.
Сначала составим График платежей по кредиту с учетом дополнительных расходов (см. файл примера Лист Кредит ). Затем сформируем Итоговый денежный поток заемщика (суммарные платежи на определенные даты).
Эффективную ставку по кредиту iэфф определим используя функцию ЧИСТВНДОХ (значения, даты, [предп]). В основе этой функции лежит формула:
Где, Pi = сумма i-й выплаты заемщиком; di = дата i-й выплаты; d1 = дата 1-й выплаты (начальная дата, на которую дисконтируются все суммы).
Учитывая, что значения итогового денежного потока находятся в диапазоне G22:G34 , а даты выплат в B22:B34 , Эффективная ставка по кредиту для нашего случая может быть вычислена по формуле =ЧИСТВНДОХ(G22:G34;B22:B34) . Получим 72,24%. Значения Эффективных ставок используются при сравнении нескольких кредитов: чья ставка меньше, тот кредит и более выгоден заемщику. Но, что за смысл имеет 72,24%? Может быть это соответствующая ставка по простым процентам? Рассчитаем ее как мы делали в предыдущих разделах: Мы переплатили 80,77т.р. (в виде процентов и дополнительных платежей) взяв кредит в размере 250т.р. Если рассчитать ставку по методу простых процентов, то она составит 80,77/250*100%=32,3% (срок кредита =1 год). Это значительно больше 15% (ставка по кредиту), и гораздо меньше 72,24%. Значит, это не тот подход, чтобы разобраться в сути эффективной ставке по кредиту. Теперь вспомним принцип временной стоимости денег: всем понятно, что 100т.р. сегодня – это значительно больше, чем 100т.р. через год при 15% инфляции (или, наоборот - значительно меньше, если имеется альтернатива положить эту сумму в банк под 15%). Для сравнения сумм, относящихся к разным временным периодам используют дисконтирование, т.е. приведение их к одному моменту времени . Вспомнив формулу Эффективной ставки по кредитам, увидим, что для всех платежей по кредитам рассчитывается их приведенная стоимость к моменту выдачи кредита. И, если мы хотим взять в 2-х банках одну и туже сумму, то стоит выбрать тот банк, в котором получается наименьшая приведенная стоимость всех наших платежей в погашение кредита. Почему же тогда не сравнивают более понятные приведенные стоимости, а используют Эффективную ставку? А для того, чтобы сравнивать разные суммы кредита: Эффективная ставка поможет, если в одном банке дают 250т.р. на одних условиях, а в другом 300т.р. на других. Итак, у нас получилось, что сумма всех наших платежей в погашение основной суммы кредита дисконтированных по ставке 72,24% равна размеру кредита (это из определения эффективной ставки). Если в другом банке для соблюдения этого равенства потребуется дисконтировать суммы платежей идущих на обслуживание долга по б о льшей ставке, то условия кредитного договора в нем менее выгодны (суммы кредитов могут быть разными). Поэтому, получается, что важнее не само значение Эффективной ставки, а результат сравнения 2-х ставок (конечно, если эффективная ставка значительно превышает ставку по кредиту, то это означает, что имеется значительное количество дополнительных платежей: убрав файле расчета все дополнительные платежи получим эффективную ставку 16,04% вместо 72,24%!).
Примечание . Функция ЧИСТВНДОХ() похожа на ВСД() (используется для расчета ставки внутренней доходности, IRR ), в которой используется аналогичное дисконтирование регулярных платежей, но на основе номера периода выплаты, а не от количества дней.
Использование эффективной ставки для сравнения кредитных договоров с разными схемами погашения
Представим себе ситуацию, когда в 2-х разных банках нам предлагают взять в кредит одинаковую сумму на одинаковых условиях, но выплата кредита в одном будет осуществляться дифференцированными платежами , а в другом по аннуитетной схеме (равновеликими платежами). Для простоты предположим, что дополнительные платежи не взимаются. Зависит ли значение эффективной ставки от графика погашения? Сразу даем ответ: зависит, но незначительно.
В файле примера на листе Сравнение схем погашения (1год) приведен расчет для 2-х различных графиков погашения (сумма кредита 250 т.р., срок =1 год, выплаты производятся ежемесячно, ставка = 15%).
В случае дифференцированных платежей Эффективная ставка по кредиту = 16,243%, а в случае аннуитета – 16,238%. Разница незначительная, чтобы на ее основании принимать решение. Необходимо определиться какой график погашения больше Вам подходит.
При увеличении срока кредита разница между Эффективными ставками практически не изменяется (см. файл примера Лист Сравнение схем погашения (5лет) ).
Примечание . Эффективная годовая ставка, рассчитанная с помощью функции ЭФФЕКТ() , дает значение 16,075%. При ее расчете не используются размеры фактических платежей, а лишь номинальная ставка и количество периодов капитализации. Если грубо, то получается, что в нашем частном случае (без дополнительных платежей) отличие эффективной ставки по кредиту от номинальной (15%) в основном обусловлено наличием периодов капитализации (самой сутью сложных процентов).
Примечание . Сравнение графиков погашения дифференцированными платежами и по аннуитетной схеме приведено в этой статье .
Примечание. Эффективную ставку по кредиту можно рассчитать и без функции ЧИСТВНДОХ() - с помощью Подбора параметра. Для этого в файле примера на Листе Кредит создан столбец I (Дисконтированный денежный поток (для Подбора параметра)). В окне инструмента Подбор параметра введите значения указанные на рисунке ниже.
После нажатия кнопки ОК, в ячейке I18 будет рассчитана Эффективная ставка совпадающая, естественно, с результатом формулы ЧИСТВНДОХ() .
В этой статье описаны синтаксис формулы и использование функции СТАВКА в Microsoft Excel.
Описание
Синтаксис
СТАВКА(кпер; плт; пс; [бс]; [тип]; [прогноз])
Примечание: Полное описание аргументов "кпер", "плт", "пс", "бс" и "тип" см. в разделе, посвященном функции ПС.
Аргументы функции СТАВКА описаны ниже.
Кпер — обязательный аргумент. Общее количество периодов платежей по аннуитету.
Плт Обязательный. Выплата, производимая в каждый период и не меняющаяся на протяжении всего периода ежегодного платежа. Обычно аргумент "плт" состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если он опущен, аргумент "пс" является обязательным.
Пс — обязательный аргумент. К настоящему моменту — общая сумма, на которую сейчас стоит ряд будущих платежей.
Fv Необязательный. Будущая стоимость или баланс, который вы хотите достичь после последнего платежа. Если значение "ок" опущено, предполагается значение 0 (например, будущая стоимость займа — 0). Если аргумент "пс" опущен, необходимо включить аргумент "pmt".
Тип Необязательный. Число 0 или 1, обозначающее, когда должна производиться выплата.
Когда нужно платить
В конце периода
В начале периода
Прогноз Необязательный. Предполагаемая величина ставки.
Если аргумент "прогноз" опущен, предполагается, что его значение равно 10 %.
Если функция СТАВКА не сходится, попробуйте изменить значение аргумента "прогноз". Функция СТАВКА обычно сходится, если значение этого аргумента находится между 0 и 1.
Замечания
Убедитесь, что единицы измерения, выбранные для аргументов "прогноз" и "кол_пер" соответствуют друг другу. При ежемесячных выплатах по четырехгодичному займу под 12 процентов годовых используйте значение 12%/12 для аргумента "прогноз" и 4*12 — для аргумента "кол_пер". При ежегодных платежах по тому же займу используйте значение 12% для аргумента "прогноз" и 4 —для аргумента "кол_пер".
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Управление личными финансами может быть сложной задачей, особенно если вам нужно планировать свои платежи и сбережения. Excel формулы и шаблоны бюджетов помогут вам вычислить будущую стоимость своих задолженности и инвестиций, что упростит расчет времени, необходимого для достижения целей. Используйте следующие функции:
ПЛТ: возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и процентной ставки.
КПЕР: возвращает количество периодов выплаты для инвестиции на основе регулярных постоянных выплат и постоянной процентной ставки.
ПВ: возвращает приведенную (к текущему моменту) стоимость инвестиции. Приведенная (нынешняя) стоимость представляет собой общую сумму, которая на данный момент равноценна ряду будущих выплат.
БС: возвращает будущую стоимость инвестиции при условии периодических равных платежей и постоянной процентной ставки.
Расчет ежемесячных платежей для погашения задолженности по кредитной карте
Предположим, остаток к оплате составляет 5400 долларов США под 17% годовых. Пока задолженность не будет погашена полностью, вы не сможете рассчитываться картой за покупки.
С помощью функции ПЛТ(ставка;КПЕР;ПС)
=ПЛТ(17%/12;2*12;5400)
получаем ежемесячный платеж в размере 266,99 долларов США, который позволит погасить задолженность за два года.
Аргумент "ставка" — это процентная ставка на период погашения кредита. Например, в данной формуле ставка 17% годовых делится на 12 — количество месяцев в году.
Аргумент КПЕР 2*12 — это общее количество периодов выплат по кредиту.
Аргумент ПС или приведенной стоимости составляет 5400 долларов США.
Расчет ежемесячных платежей по ипотеке
Представьте дом стоимостью 180 000 долларов США под 5% годовых на 30 лет.
С помощью функции ПЛТ(ставка;КПЕР;ПС)
=ПЛТ(5%/12;30*12;180000)
получена сумма ежемесячного платежа (без учета страховки и налогов) в размере 966,28 долларов США.
Аргумент "ставка" составляет 5%, разделенных на 12 месяцев в году.
Аргумент КПЕР составляет 30*12 для ипотечного кредита сроком на 30 лет с 12 ежемесячными платежами, оплачиваемыми в течение года.
Аргумент ПС составляет 180 000 (нынешняя величина кредита).
Расчет суммы ежемесячных сбережений, необходимой для отпуска
Необходимо собрать деньги на отпуск стоимостью 8500 долларов США за три года. Процентная ставка сбережений составляет 1,5%.
С помощью функции ПЛТ(ставка;КПЕР;ПС;БС)
получаем, что чтобы собрать 8500 долларов США за три года, необходимо откладывать по 230,99 долларов США ежемесячно.
Аргумент "ставка" составляет 1,5%, разделенных на 12 месяцев — количество месяцев в году.
Аргумент КПЕР составляет 3*12 для двенадцати ежемесячных платежей за три года.
Аргумент ПС (приведенная стоимость) составляет 0, поскольку отсчет начинается с нуля.
Аргумент БС (будущая стоимость), которую необходимо достичь, составляет 8500 долларов США.
Теперь допустим, вы хотите собрать 8500 долларов США на отпуск за три года, и вам интересно, какую сумму необходимо положить на счет, чтобы ежемесячный взнос составлял 175,00 долларов США. Функция ПС рассчитает размер начального депозита, который позволит собрать желаемую сумму.
С помощью функции ПС(ставка;КПЕР;ПЛТ;БС)
мы узнаем, что необходим начальный депозит в размере 1969,62 долларов США, чтобы можно было откладывать по 175,00 долларов США в месяц и собрать 8500 долларов США за три года.
Аргумент "Ставка" составляет 1,5%/12.
Аргумент КПЕР составляет 3*12 (или двенадцать ежемесячных платежей за три года).
Аргумент ПЛТ составляет -175 (необходимо откладывать по 175 долларов США в месяц).
Аргумент БС (будущая стоимость) составляет 8500.
Расчет срока погашения потребительского кредита
Представьте, что вы взяли потребительский кредит на сумму 2500 долларов США и согласились выплачивать по 150 долларов США ежемесячно под 3% годовых.
С помощью функции КПЕР(ставка;ПЛТ;ПС)
=КПЕР(3%/12;-150;2500)
выясняем, что для погашения кредита необходимо 17 месяцев и несколько дней.
Аргумент "Ставка" составляет 3%/12 ежемесячных платежей за год.
Аргумент ПЛТ составляет -150.
Аргумент ПС (приведенная стоимость) составляет 2500.
Расчет суммы первого взноса
Скажем, вы хотите приобрести автомобиль стоимостью 19 000 долларов США под 2,9 % годовых за три года. Вы хотите, чтобы ежемесячные платежи были на уровне 3500 долларов США в месяц, поэтому вам нужно выяснить сумму своего взноса. В этой формуле результатом функции ПС является сумма займа, которая затем вычитается из цены покупки, чтобы получить первый взнос.
С помощью функции ПС(ставка;КПЕР;ПЛТ)
= 19000-ПС(2,9%/12; 3*12;-350)
выясняем, что первый взнос должен составлять 6946,48 долларов США.
Сначала в формуле указывается цена покупки в размере 19 000 долларов США. Результат функции ПС будет вычтен из цены покупки.
Аргумент "Ставка" составляет 2,9%, разделенных на 12.
Аргумент КПЕР составляет 3*12 (или двенадцать ежемесячных платежей за три года).
Аргумент ПЛТ составляет -350 (необходимо будет выплачивать по 350 долларов США в месяц).
Оценка динамики увеличения сбережений
Начиная с 500 долларов США на счету, сколько можно собрать за 10 месяцев, если класть на депозит по 200 долларов США в месяц под 1,5% годовых?
Функция СТАВКА используется для определения процентной ставки по инвестиции либо денежному займу с аннуитетной структурой графика погашения для одного периода выплат (при условии, что будущая стоимость ценных бумаг, обеспечивающих инвестицию либо кредит является известной величиной) и возвращает полученное значение.
Если при заключении сделки процентная ставка не была установлена жестко, функция СТАВКА позволяет определить размер неявной ставки (то есть такой ставки, которая обеспечила бы получение эквивалентного дохода).
Примеры финансовых расчетов по функции СТАВКА в Excel
Пример 1. В МФО был взят кредит сроком на 16 дней, сумма которого составляет 1000 долларов. Сумма возврата составляет 1250 долларов. Определить годовую процентную ставку по указанным условиям займа.
Для расчета в ячейку B7 введем следующую формулу:
- B4 – число периодов выплат (в данном случае – 1);
- 0 – размер фиксированной выплаты (поскольку в данном примере только один период выплат, указано значение 0);
- B5 – тело кредита;
- B6 – сумма на момент погашения долга;
- 0 – характеризует тип выплат, при котором выплата производится в конце периода;
- 0,1 – предполагаемое значение процентной ставки (любое число из диапазона от 0 до 1);
- B3/2 – коэффициент для пересчета полученного значения ставки к годовой процентной ставке.
Пи данных условиях микрокредитования сроком займа на 16 дней процентная ставка составляет 570,31% годовых! Несмотря на это, услуги по микрокредитованию сегодня продолжают набирать популярность.
Анализ пенсионных отчислений с использованием функции СТАВКА в Excel
Пример 2. Определить темпы роста пенсионных отчислений (процентную ставку), если баланс средств на конец года составляет 12000 долларов, а в начале года – 2400 долларов. Еженедельные платежи на протяжении года составляли 150 долларов (то есть, количество периодов – 52).
Формула для расчета:
- B2 – количество периодов выплат;
- B3 – сумма платежа (расходная операция, поэтому отрицательное значение);
- B4 – сумма средств до наступления первого периода выплат;
- B5 – сумма по окончанию последнего периода выплат;
- 0 – выплаты в конце периода;
- 0,1 – произвольное значение из интервала от 0 до 1;
- 52 – количество периода выплат для пересчета размера ставки в годовых.
То есть, пенсионные отчисления выполняются под 7% годовых.
Определение реальной процентной ставки по кредиту
Пример 3. Ноутбук одной и той же модели можно приобрести за 1200 долларов в рассрочку (беспроцентную, судя по рекламе в первом магазине) или за 1050 долларов в другом магазине. Рассрочка выдается на 1 год с 12 периодами выплат. Определить реальный процент «беспроцентной» рассрочки платежей по кредиту.
Формула для расчета:
- B2 – число периодов выплат;
- -B3/B2 – выражение для расчета размера ежемесячного платежа;
- B4 – реальная стоимость ноутбука (используется как начальная стоимость финансового инструмента, цена которого повысится до 1200 к окончанию последнего периода выплат);
- 0 – остаток по окончанию последнего периода выплат;
- 0 - выплаты в конце периода;
- 0,01 - произвольное значение предполагаемой ставки.
То есть, фактически в первом магазине клиенту предложили кредит на ноутбук под 25,4% годовых.
Определить общую стоимость продуктов и процентный состав.
Выяснить, на какое количество порций весом в 100 г рассчитан данный рецепт.
Определить стоимость продуктов для одной порции.
Определить стоимость порции для покупателя, если зарплата сотрудника составляет 25%, а накладные расходы — 80% от стоимости продуктов одной порции.
Отформатировать заголовок таблицы.
Построить круговую диаграмму процентного состава салата. Выделить наименьший сектор. Отобразить на диаграмме значения и подписи данных. Применить двухцветную градиентную заливку фона.
стоимости продуктов для приготовления салата
ИТОГО:
Стоимость продуктов одной порции
Стоимость порции для покупателя
Задание 2
Составить таблицу, содержащую следующие поля:
Заполнить таблицу в соответствии с условиями:
Записей в таблице — 12.
Различных месяцев — 3.
Значения столбца «Внесено» задаются произвольно.
Столбец «Льгота» заполняется следующим образом: 0 — нет льгот, 1 — скидка в оплате 25%, 2 — скидка в оплате — 50%.
Различных фамилий — 4, фамилии повторяются в каждом месяце.
Тариф без льгот записан в свободной ячейке вне таблицы.
Вычислить сумму оплаты в соответствии с номером льготы. Если льгота равна 0, то 100% тарифа, если льгота равна 1, то скидка в оплате 25%, если льгота равна 2, то скидка 50%.
Вычислить общую сумму внесенной оплаты и общую сумму долга.
Вычислить среднее значение по полю «Долг».
Используя функцию СЧЕТЕСЛИ, определить количество абонентов с долгом больше среднего.
Используя стандартные функции, найти минимальное и максимальное значения суммы долга.
Вычислить сумму долга по каждой фамилии и оформить в виде таблицы со столбцами «ФИО», «Долг».
Вычислить сумму начисленной и внесенной оплаты по месяцам и оформить в виде таблицы в соответствии с образцом.
Построить диаграмму начисленной и внесенной оплаты по месяцам.
Практическая работа №2 (2 часа)
Составить таблицу, содержащую следующие поля:
Стоимость проката в сутки
Заполнить таблицу в соответствии с условиями:
Записей в таблице — 10.
Значения в столбце «Дата сдачи» вводятся таким образом, чтобы были наименования с просроченным сроком возврата (относительно текущей даты).
Различных наименований — 4.
Вставить в таблицу столбец «Просрочено дней» и вычислить.
Вставить столбец «Пеня» и вычислить сумму пени в соответствии со следующим условием: если количество просроченных дней меньше 5, то 0,05% за каждый день, от 5 до 10 дней – 0,1% за каждый просроченный день сверх 5 и по предыдущей ставке за остальные, более 10 дней – 0,3% за каждый день сверх 10 и по 0,1% – до 10.
Дополнить таблицу столбцами «Дней в прокате» и вычислить значения.
Дополнить таблицу столбцом «К оплате» и вычислить с учетом пени.
Используя функцию СЧЕТЕСЛИ, определить количество просроченных заказов.
Используя стандартные функции, найти минимальное и максимальное значения дней проката.
Вычислить сумму к оплате по наименованиям и оформить в виде таблицы в соответствии с образцом.
Построить диаграмму по наименованиям.
Задание 2
Составить таблицу, содержащую следующие поля:
Месяц выплаты пособия
Заполнить таблицу в соответствии с условиями.
Записей в таблице — 10.
Значения в столбце «Дата рождения» вводятся таким образом, чтобы были дети в возрасте меньше 3 лет, до 8 лет, от 8 до 17 лет и старше 17 лет.
Различных месяцев — 3.
Различных фамилий — 4, фамилии повторяются в каждом месяце.
Минимальный заработок задается произвольно в свободной ячейке вне таблицы.
Вставить в таблицу столбец «Возраст» и вычислить возраст ребенка.
Дополнить таблицу столбцами «Сумма пособия». Значения в столбце вычислить в соответствии с условием: если возраст меньше 3 лет, то 100% от мин. заработка, если возраст меньше 8 лет, то 80%, от 8 до 17 лет — 60%, старше 17 лет — пособия нет.
Вычислить общую сумму пособий.
Вычислить средний возраст детей.
Используя функцию СЧЕТЕСЛИ, определить количество детей старше 8 лет.
Вычислить общую сумму пособий по фамилиям. Оформить в виде таблицы.
Вычислить сумму выплаты пособий по месяцам и оформить в виде таблицы в соответствии с образцом.
Построить диаграмму выплат по месяцам.
Практическая работа №3 ( 2 часа)
Составить таблицу, содержащую следующие поля:
Дата увольнения (перевода)
Заполнить таблицу в соответствии с условиями.
Записей в таблице — 6.
Значения в столбце «Дата увольнения (перевода)» соотносятся со столбцом «Дата поступления».
Различных должностей — 3.
Вставить в таблицу столбец «Стаж» и вычислить стаж работы в каждой должности.
Дополнить таблицу столбцами «Надбавка» и «Оплата труда». Значения в столбце «Надбавка» вычислить в соответствии с условием: Если общий стаж работы меньше 5 лет, то надбавка 10%, если стаж больше 10 лет, то надбавка 20%, от 5 до 10 лет — 15%.
Вычислить оплату труда для каждого места работы.
Вычислить среднемесячную оплату труда за два последних года (предполагается, что в течение последних двух лет было изменение должности и оклада, т.е. две записи в таблице).
Используя функцию СЧЕТЕСЛИ, определить количество сотрудников с оплатой меньше средней.
Используя стандартные функции, найти минимальное и максимальное значения оплаты труда.
Вычислить среднюю оплату труда по должностям и оформить в виде таблицы в соответствии с образцом.
Построить диаграмму по таблице, полученной в п.7.
Составить таблицу, содержащую следующие поля:
Рабочих дней в месяце
Количество больничных дней
Заполнить таблицу в соответствии с условиями.
Записей в таблице — 12.
Значения в столбце «Дата поступления» вводятся так, чтобы были сотрудники со стажем меньше 5 лет, больше 8 лет и от 5 до 8 лет.
Различных месяцев — 3.
Средний заработок задается произвольно.
Должностей, как и фамилий, по 4 разных.
Вставить в таблицу столбец «Стаж» и вычислить стаж.
Дополнить таблицу столбцами «К оплате по б/л». Значения в столбце вычислить в соответствии с условием: если стаж меньше 5 лет, то 50% от среднего заработка, если стаж больше 8 лет, то 100%, от 5 до 8 лет — 80%.
Вычислить общую сумму оплаты больничных листов.
Вычислить средний стаж.
Используя функцию СЧЕТЕСЛИ, определить количество сотрудников со стажем меньше 8 лет.
Используя стандартные функции, найти минимальное и максимальное значения количества больничных дней.
Вычислить среднее количество больничных дней в каждом месяце, оформить в виде таблицы.
Вычислить количество дней по больничному листу по должностям и оформить в виде таблицы в соответствии с образцом.
Построить диаграмму по таблице, полученной в п.8.
Практическая работа №4 ( 2 часа)
Составить таблицу, содержащую следующие поля:
Заполнить таблицу в соответствии с условиями.
Записей в таблице — 10.
Значения в столбце «Дата поступления» соотносятся со столбцом
Различных должностей — 3.
Вставить в таблицу столбец «Возраст» и вычислить возраст каждого сотрудника.
Вставить в таблицу столбец «Стаж» и вычислить стаж.
Дополнить таблицу столбцами «Надбавка» и «Оплата труда». Значения в столбце вычислить в соответствии с условием: Если стаж меньше 5 лет, то надбавка 10%, если стаж больше 10 лет, то надбавка 20%, от 5 до 10 лет — 15%.
Вычислить общую оплату труда.
Вычислить среднюю оплату труда всех сотрудников.
Используя функцию СЧЕТЕСЛИ, определить количество сотрудников с оплатой меньше средней.
Используя стандартные функции, найти минимальное и максимальное значения оплаты труда.
Вычислить среднюю оплату труда по должностям и оформить в виде таблицы в соответствии с образцом.
Построить диаграмму средней оплаты по должностям.
Составить таблицу, содержащую следующие поля:
Рабочих дней в месяце
Дата выдачи больничного
Дата окончания больничного
Заполнить таблицу в соответствии с условиями.
Записей в таблице — 10, фамилии могут повторяться.
Значения в столбце «Дата поступления» вводятся таким образом, чтобы были сотрудники со стажем меньше 5 лет, больше 8 лет и от 5 до 8 лет.
Различных месяцев — 3.
Средний заработок задается произвольно.
Вставить в таблицу столбец «Стаж» и вычислить стаж.
Вставить столбец «Дней по больничному листу» и вычислить продолжитель- ность больничного.
Дополнить таблицу столбцом «К оплате по больничному». Значения в столбце вычислить в соответствии с условием: если стаж меньше 5 лет, то 50% от среднего заработка, если стаж больше 8 лет, то 100%, от 5 до 8 лет — 80%.
Вычислить общую сумму оплаты больничных листов.
Вычислить средний стаж.
Используя функцию СЧЕТЕСЛИ, определить количество сотрудников со стажем больше 8 лет.
Используя стандартные функции, найти минимальное и максимальное значения среднего заработка.
Вычислить сумму оплаты больничных листов по месяцам и оформить в виде таблицы в соответствии с образцом.
Количество дней по больничному
Построить диаграмму оплаты по месяцам.
Вычислить количество больничных дней для каждого сотрудника.
Практическая работа № 5 ( 2 часа)
Определить надбавку за стаж работы. Если стаж более 25 лет, то 30%
оклада, если более 15 лет, то 15%, иначе надбавки нет.
Зада ние 2
Определить стоимость проката авто. Если автомобиль был в прокате меньше 2 дней, то 45 руб., если от 2 до 7 дней, то 40 руб., если больше 7 дней, то 35 руб. за день.
Определить сумму оплаты за провоз багажа в зависимости от расстояния и веса.
Если перевозка на расстояние меньше 1000 км, то тариф 3 руб. за кг веса, от 1000 до 2000 — 2,5 руб./кг, больше 2000 км — 2 руб./кг.
Если вес груза до 5 тонн то 100% от тарифа, если от 5 до 10 тонн, то
90%, выше 100 тонн — 80% тарифа.
Определить количество и стоимость бензина, необходимое для выполнения рейса, в зависимости от марки авто и расстояния.
Установить проверку данных для ячеек (выделите диапазон ячеек и выберите инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных», дальше сами) «Марка авто» и «Марка бензина».
Показания спидометра вводятся произвольно.
Практическая работа № 6. Форматирование таблиц, вычисления и создание диаграмм (4 часа)
1. На рабочем листе MS Excel создайте « Расчет нагрузки преподавателя»
2. Заполните порядковыми номерами столбец “№” и расчетными данными остальные пустые ячейки таблицы.
3. Выделите каким-либо цветом шапку таблицы и строку, содержащую суммы по столбцам.
4. Постройте гистограмму, отображающую распределение общей нагрузки (столбец “Всего по всем видам занятий, ак. часов”) по потокам.
5. Постойте круговую диаграмму, отображающую доли (%) в суммарной нагрузке лекционных часов, часов по зачетам, и часов по контрольным работам.
1. На рабочем листе MS Excel создайте « Отчет менеджера по продажам».
2. Заполните порядковыми номерами столбец “№” и расчетными данными остальные пустые ячейки таблицы.
3. Отсортируйте данные в таблице по убыванию значений в столбце "Сумма, руб."
4. Постройте круговую диаграмму, отображающую в процентах вклад от продаж различных моделей фотокамер в общую сумму выручки (модели с нулевым вкладом на диаграмме отображать не нужно). Сколько процентов от общей суммы выручки составил вклад от самой продаваемой за этот период модели?
1. На рабочем листе MS Excel создайте отчет по « Начисления по оплате услуг ЖКХ».
2. Заполните порядковыми номерами столбец “№” и расчетными данными ячейки столбца “Начислено, руб.”
3. Отсортируйте данные в таблице по убыванию значений в столбце "Начислено, руб." в диапазоне видов услуг.
4. Постройте круговую диаграмму, отображающую в процентах долю вклада услуги каждого вида в общую сумму оплаты за текущий месяц.
Читайте также: