Как рассчитать экономии в excel

Обновлено: 05.07.2024

При работе с капитальным бюджетом IRR (внутренняя норма доходности) используется для понимания общей нормы доходности, которую проект будет генерировать на основе его будущих рядов денежных потоков.

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

Разъяснение внутренней нормы прибыли (IRR)

IRR отображается в процентах и может использоваться для определения прибыльности проекта (инвестиции) для компании.

Позвольте мне объяснить IRR на простом примере.

Предположим, вы планируете купить компанию за 50 000 долларов, которая будет приносить 10 000 долларов ежегодно в течение следующих 10 лет. Вы можете использовать эти данные для расчета IRR этого проекта, которая представляет собой норму прибыли, которую вы получаете от своих инвестиций в размере 50 000 долларов США.

В приведенном выше примере IRR составляет 15% (мы увидим, как рассчитать это позже в руководстве). Это означает, что вы вкладываете свои деньги со ставкой 15% или доходом в течение 10 лет.

Получив значение IRR, вы можете использовать его для принятия решений. Поэтому, если у вас есть какой-либо другой проект, в котором IRR составляет более 15%, вам следует вместо этого инвестировать в этот проект.

Или, если вы планируете взять ссуду или привлечь капитал и купить этот проект за 50 000 долларов, убедитесь, что стоимость капитала составляет менее 15% (иначе вы заплатите больше как стоимость капитала, чем вы зарабатываете из проект).

Excel позволяет рассчитать внутреннюю норму прибыли с помощью функции IRR. Эта функция имеет следующие параметры:

Вот некоторые важные предпосылки для использования функции:

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

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

Расчет IRR для различных денежных потоков

Предположим, у вас есть набор данных, показанный ниже, в котором у нас есть начальные инвестиции в размере 30 000 долларов США, а затем изменяющийся денежный поток / доход от них в течение следующих шести лет.

Для этих данных нам нужно рассчитать IRR, что можно сделать, используя следующую формулу:

Узнайте, когда инвестиции принесут положительную внутреннюю норму доходности

Вы также можете рассчитать IRR для каждого периода в денежном потоке и увидеть, когда именно инвестиции начинают иметь положительную внутреннюю норму доходности.

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

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

Для этого вместо расчета IRR для всего проекта мы найдем IRR для каждого года.

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

Как видите, IRR после года 1 (значения D2: D3) составляет -80%, после года 2 (D2: D4) -52% и т. Д.

Этот обзор показывает нам, что инвестиции в размере 30 000 долларов США с заданным денежным потоком имеют положительную внутреннюю норму доходности после пятого года.

Это может быть полезно, когда вы выбираете из двух проектов с похожей IRR. Было бы более прибыльным выбрать проект, в котором IRR быстрее станет положительным, поскольку это означает меньший риск возврата вашего первоначального капитала.

Обратите внимание, что в приведенной выше формуле ссылка на диапазон смешана, то есть первая ссылка на ячейку ($ C $ 2) заблокирована знаками доллара перед номером строки и буквой столбца, а вторая ссылка (C3) не является заблокирован.

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

Использование функции IRR для сравнения нескольких проектов

Функцию IRR в Excel также можно использовать для сравнения инвестиций и прибылей нескольких проектов и определения наиболее прибыльного проекта.

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

Чтобы получить лучший проект (с самым высоким IRR, нам нужно будет рассчитать IRR для каждого проекта, используя простую формулу IRR:

Приведенная выше формула даст IRR для проекта 1. Аналогичным образом вы можете рассчитать IRR для двух других проектов.

Проект 1 имеет IRR 5,60%.
Проект 2 имеет IRR 1,75%.
Проект 3 имеет IRR 14,71% .

Если мы предположим, что стоимость капитала составляет 4,50%, мы можем сделать вывод, что вложение 2 неприемлемо (так как приведет к убыткам), в то время как вложение 3 является наиболее прибыльным с самой высокой внутренней нормой доходности.

Поэтому, если вам нужно принять решение об инвестировании только в один проект, вам следует выбрать проект 3, а если вы можете инвестировать в несколько проектов, вы можете инвестировать в проекты 1 и 3.

Определение : если вам интересно, какова стоимость капитала, то это деньги, которые вам придется заплатить, чтобы получить доступ к деньгам. Например, если вы берете в долг 100 тыс. Долларов под 4,5% годовых, стоимость капитала составляет 4,5%. Точно так же, если вы выпускаете привилегированные акции, обещая доходность 5% для получения 100 тыс., Ваша стоимость капитала составит 5%. В реальных сценариях компания обычно привлекает деньги из различных источников, а ее стоимость капитала представляет собой средневзвешенное значение всех этих источников капитала.

Расчет IRR для нерегулярных денежных потоков

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

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

Например, ниже представлен набор данных, в котором денежные потоки происходят с нерегулярными интервалами (см. Даты в столбце A).

Функция XIRR принимает денежные потоки, а также даты, что позволяет ей учитывать нерегулярные денежные потоки и давать корректирующую IRR.

В этом примере IRR можно рассчитать по следующей формуле:

Когда дело доходит до оценки проектов, используются как NPV, так и IRR, но NPV является более надежным методом .

Если это значение окажется выше вашего первоначального оттока, значит, проект прибыльный, иначе проект не прибыльный.

С другой стороны, когда вы рассчитываете IRR для проекта, он сообщает вам, какой будет норма прибыли для всего будущего денежного потока, чтобы вы получили сумму, эквивалентную текущему оттоку. Например, если вы тратите сегодня 100 тысяч долларов на проект с IRR или 10%, это говорит вам, что если вы дисконтируете все будущие денежные потоки по ставке дисконтирования 10%, вы получите 100 тысяч долларов.

Хотя при оценке проектов используются оба метода, более надежным является метод NPV. Существует вероятность того, что вы можете получить противоречивые результаты при оценке проекта с использованием метода NPV и IRR.

В таком случае лучше всего воспользоваться рекомендацией, полученной с помощью метода NPV.

В целом метод IRR имеет ряд недостатков, которые делают метод NPV более надежным:

Более высокий метод или предполагает, что все будущие денежные потоки, полученные от проекта, будут реинвестированы с той же нормой доходности (т. Е. IRR проекта). в большинстве случаев это необоснованное предположение, поскольку большая часть денежных потоков будет реинвестирована в другие проекты, которые могут иметь другую IR или ненадежность, например облигации, которые будут иметь гораздо более низкую норму доходности.
Если у вас есть несколько оттоков и притоков в проекте, для этого проекта будет несколько IRR. Это снова сильно затрудняет сравнение.

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

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

В Т⁠—⁠Ж вышло много статей про эксель-таблицы для ведения бюджета.

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

Мы выбрали шесть популярных таблиц в одной статье и сравнили их способности: выбирайте, где вам удобнее вести бюджет.

План-2035

Главная цель: построить долгосрочный финансовый план для семьи и накопить на пенсию.

Как устроена таблица. Основная идея таблицы — составить план на год уже в первые дни января и отслеживать его выполнение каждые две недели.

В годовой финансовый план вносятся все планируемые доходы: зарплаты, авансы и отпускные, кэшбэк, налоговые вычеты и подарки.

А расходы делятся на четыре блока:

Обязательные — услуги ЖКХ и еда, накопления, детские секции, ипотека.
Автомобиль — бензин, ремонт, страхование, налоги и штрафы.
Дом — налоги и строительство.
Семья — одежда, подарки, здоровье и отпуск.

Доходы и расходы нужно планировать на каждые две недели

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

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

В статистике за месяц и за две недели видно разницу между доходами и расходами, а также долю, которую съедает из бюджета каждая категория трат

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

Приятные мелочи. Если в какой-то месяц остается меньше 10 тысяч свободных денег, загорается тревожная кнопка «Мало!».

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

Бюджет на 1000 дней

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

Как устроена таблица. Для записи расходов есть три вкладки: «Повседневные», «Крупные» и «Квартира».

В первую отправляется большинство расходов: продукты, бары, транспорт, кино, подарки и другие.

Каждой трате присуждается категория. Категорию нужно выбирать из выпадающего списка

Для детального анализа есть подкатегории. Они автоматически подтягиваются при выборе категории, и их также можно выбрать из выпадающего списка. Так, в категории «Транспорт» нужно выбрать такси, метро, автобус, электричку или что-то другое, а в «Ресторанах» — бары, рестораны, бургерные, доставку и кальянные.

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

Во вкладку «Крупные траты» создатель таблицы заносит расходы на путешествия, абонемент в спортзал и покупки дороже 15 000 Р . Так, майка за 2000 Р и зимняя куртка за 20 000 Р отправятся в разные вкладки, хотя относятся к одежде: майка — в «Повседневные», а куртка — в «Крупные».

На вкладку «Квартира» уходит все, что связано с недвижимостью — ипотека, аренда, услуги ЖКХ и ремонт.

Чтобы на все хватало и даже оставалось. Дважды в неделю рассказываем в нашей рассылке

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

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

Для каждой категории трат в таблице есть своя вкладка

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

На вкладке «Справочники» есть инструкции, которые помогают быстрее разобраться в функциях таблицы.

Некоторые подсказки автор оставил прямо в описании сводных таблиц. Это помогает не запутаться в формулах и не сломать все при настройке под себя

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

Сделай сам!

Главная цель: сделать таблицу, в которой не придется заполнять бесполезные вкладки и которая на 100% подойдет под ваши расходы и экономические цели.

Как устроена таблица. Это упрощенная версия таблицы «1000 дней», которую легко настроить под себя.

Траты здесь также вносятся на трех вкладках: «Повседневные», «Крупные» и «Квартира».

Для каждой покупки надо указать категорию, стоимость и дату, чтобы потом найти нужный день

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

Для этого на последней вкладке — «Справочники» — нужно изменить содержимое ячеек

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

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

Суммы повседневных трат можно оценить по дням недели и категориям

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

Голубая линия показывает рост или падение расходов. Синяя — среднее значение трат за месяц

Приятные мелочи. Есть подробная инструкция. Можно быстро понять, как работают формулы, и оставить только нужные функции.

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

В чем сложность. В таблице нет раздела с доходами, поэтому, если траты растут синхронно с заработком, этого никто не оценит.

Бюджет с IFTTT

Главная цель: вести таблицу со смартфона.

Как устроена таблица. Главная фишка таблицы — простой ввод данных и быстрый доступ к информации. Для этого придется потратить около 10 минут для интеграции с сервисами IFTTT и «Гугл-формами» — это сервис, который помогает вносить изменения в таблицу в простом окне вместо открытия громоздкой конструкции. IFTTT — это бот для мессенджеров, который берет данные из общей таблицы и рассылает оповещения об остатке и превышении лимита.

Таблица делит расходы на три группы: «Постоянные», «Крупные» и «Повседневные».

«Постоянные» — ипотека, услуги ЖКХ, мобильная связь, подписки и все, что повторяется каждый месяц. «Крупные» — это нерегулярные большие покупки. Например, расходы на ремонт и отпуск, покупка бытовой техники, гаджетов и автомобиля. «Повседневные» — это все остальное: продукты, бытовая химия, развлечения и другие ежедневные траты.

Расходы можно заносить в «Гугл-форму» даже не открывая таблицу: для этого надо вынести ярлык на экран смартфона и открывать как приложение После этого бот IFTTT отправит уведомление об остатке на день в удобном мессенджере

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

Все траты отображаются в виде графиков и диаграмм. Можно выбрать категорию и посмотреть детальный отчет

Приятные мелочи. Есть подробная инструкция, как вместо сводных таблиц на вкладке «Дашборд» собирать красивые отчеты в Datastudio.

Теперь по соседству с графиками повседневных трат на «Дашборде» есть диаграммы

В чем сложность. Для удобной работы с таблицей нужно подключить сторонние сервисы: «Гугл-формы», IFTTT, Datastudio.

Личный бюджет на год

Главная цель: спланировать бюджет на год в небольшой и понятной таблице.

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

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

Основные функции. Если в каком-то месяце расходы превышают доходы, он выделяется красным цветом. В этом случае надо сократить крупные траты или перенести остаток с другого месяца — для этого есть строчка «Перенос».

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

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

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

Приятные мелочи. Есть инструкция по планированию бюджета. Автор построчно объясняет что, куда и зачем записывать в таблицу.

Также в ячейки можно добавлять напоминания: продлить ОСАГО, подать документы на вычет или пройти техобслуживание авто.

Напоминания остаются в виде небольших меток в каждой категории и не мешают работать с таблицей

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

Семейный бюджет за 15 минут в неделю

Главная цель: накопить деньги без ежедневного учета расходов.

Как устроена таблица. Финансовый план состоит из одной таблицы, которую нужно каждую неделю копировать и начинать с чистого листа. Новый период начинается с блока «Остатки». В нем отметьте, сколько осталось денег на разных счетах, и все отрицательные активы — займы и долги по кредиткам.

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

Планирование нового периода начинается с подведения итогов прошлого в блоке «Остатки»

Блок финансовых операций разделен на три группы: «Регулярные расходы», «Поступления» и «Разное».

«Регулярные расходы» — это все повседневные траты. Тут не обязательно учитывать все до копейки, достаточно указать средний расход за месяц. Чтобы было проще, выходные и будние дни выделены разным цветом, так как траты в праздники обычно выше.

В «Разное» попадают все остальные покупки, а в «Поступления» — зарплаты, дивиденды и другие доходы.

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

Основные функции. Так как главная цель таблицы — накопления, все самое интересное происходит в блоке «Фонды».

В столбцах «Обороты по фондам» нужно заносить каждую трату. Чтобы не запутаться, автор предлагает оставлять комментарии в ячейках: например, по скриншоту понятно, что 2306 Р потратили в «Икее»

Светло-желтые строчки — это запасы, которые можно тратить в течение месяца. Если что-то останется — хорошо. Если нет — ничего страшного. Столбец «Баланс» автоматически считает остаток на каждую категорию по мере записи расходов.

Темно-желтые строки — это цели, на которые нужно накопить. Сумму, которая в конце месяца остается в строке «Баланс», можно добавить к любой цели. Если во время планирования в остатке получается отрицательное значение, нужно пересмотреть категории расходов: значит, где-то потратили больше планируемого.

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

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

РАБОТА ПО СНИЖЕНИЮ ПРОИЗВОДСТВЕННОЙ СЕБЕСТОИМОСТИ

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

Работа по снижению производственной себестоимости идет по двум направлениям:

1) пересмотр конструкции изделия;

2) изменение технологии производства.

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

Рабочая группа рассматривает каждую деталь изделия и выносит на обсуждение следующие вопросы:

можно ли данную деталь изготовить из более дешевого материала и более дешевым способом?
не присутствует ли при изготовлении детали излишняя обработка?
не приведет ли изменение материала или технологии к изменению потребительских свойств?
если произойдет ухудшение потребительских свойств, то насколько это будет критично для потребителей?

После проработки данных вопросов выносится мотивированное решение.

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

РАССЧИТАЕМ СУММУ ЭКОНОМИИ

Сумма экономии при внедрении новых видов материалов зависит от изменения нормы расхода и цены за единицу материала и рассчитывается по формуле:

где Э_нм — экономия от внедрения новых видов сырья и материалов, руб.;

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

Ц₀, Ц₁ — цена единицы данного вида сырья, материалов, топлива до и после осуществления мероприятия, руб.;

K — объем производства продукции (или объем потребления данного вида ресурса) в данном периоде.

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

Деталь изготавливается из стали холоднокатаной толщиной 0,8 мм, предложено заменить материал на сталь холоднокатаную толщиной 0,7 мм. На потребительских свойствах продукции это не скажется.

Расчет экономии представлен в табл. 1.

Несмотря на то что цена на сталь толщиной 0,7 мм незначительно превышает цену на сталь толщиной 0,8 мм, сумма экономии составляет 321 тыс. руб.

Вывод: решение рациональное, его необходимо внедрить в производственный процесс.

Экономию от снижения нормы расхода материальных ресурсов рассчитывают по формуле:

где Э_Н — сумма экономии от снижения нормы расхода материального ресурса, руб.;

Н₀ и Н₁ — норма расхода данного вида сырья, материала, топлива и энергии на единицу продукции в соответствующих единицах измерения (кг, т, м и др.) до и после осуществления организационно-технических мероприятий;

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

K — объем выпуска продукции, по которой изменяют нормы расхода в связи с внедрением данного организационно-технического мероприятия в планируемом периоде, в соответствующих единицах.

Рабочая группа рассмотрела технологический процесс изготовления деталей и выявила, что некоторые детали, изготавливаемые из стали толщиной 2 мм, имеют большой процент деловых отходов. Связано это с раскроем листов железа. Был предложен вариант по изменению раскроя и, как следствие, уменьшение на 3 % нормы расхода материала. Расчет экономии приведен в табл. 2.

Экономия по данному предложению составила 59 тыс. руб. Вывод: предложение принять.

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

где Э_сд — сумма экономии по сдельной заработной плате и сборам, начисляемым на фонд оплаты труда, руб.;

В₀, В₁ — норма времени на производство единицы продукции до и после проведения мероприятий, нормо-часов;

T₀, T₁ — тарифная ставка, соответствующая разряду выполняемых работ, руб.;

С — установленный процент отчислений с фонда оплаты труда;

К — количество изделий, по которым снижается норма времени за счет проведения мероприятий.

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

Экономия по данному предложению составит 8,4 тыс. руб. Вывод: предложение является рациональным, необходимо принять в работу.

Р. В. Казанцев, исполнительный директор ООО «УК Теплодар»

Материал публикуется частично. Полностью его можно прочитать в журнале «Планово-экономический отдел» № 5, 2018.

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

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

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

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

ПРОСТЫЕ ФУНКЦИИ И ФОРМУЛЫ MS EXCEL

Начнем изучение с наиболее простых функций из раздела «Мастер функций» MS Excel.

Функция «СУММ»

Данная функция помогает суммировать значения нескольких ячеек. Рассмотрим пример использования этой функции (рис. 1).

№ п/п

Наименование

Ед. изм.

Стоимость ед. изм., руб.

Расход

Сумма, руб.

Итого

1006,00

Рис. 1. Пример использования функции «СУММ»

Необходимо посчитать стоимость материальных расходов, затраченных на единицу выпущенной продукции, если известна стоимость закупки единицы измерения и фактический расход каждого вида материала на изготовление единицы продукции (графы 4 и 5 таблицы, представленной на рис. 1). Итог по каждой позиции материала выведен в графе 6 путем перемножения фактического расхода на стоимость закупки.

«Итого» рассчитывают сложением всех подытогов по каждой позиции материала. Для этого используют функцию «СУММ» и выделяют диапазон ячеек с необходимыми значениями данных (в нашем случае — графа 6, которой в MS Excel соответствует столбец «Н»). Тогда формула приобретет следующий вид:

= СУММ(H5:H9), где H5:H9 — диапазон данных по графе 6 от материала № 1 до материала № 5.

Когда пользователю нужно рассчитать сумму значений ячеек или применить иную функцию, но при этом получить результат расчетов с округлением (например, без копеек), применяют функции «ОКРУГЛ», «ОКРУГЛВВЕРХ» и «ОКРУГЛВНИЗ». Как правило, эти функции не используют как самостоятельные, чаще их применяют в комплексе с другими функциями (например, с «СУММ»). В нашем случае по материалу № 2 сумма составляет 50,77 руб. (графа 6). Составим формулу для расчета итоговой суммы с учетом округления:

=ОКРУГЛ(СУММ(H5:H9);0), где «0» — число разрядов для округления.

Справочная информация о форматировании ячеек:

1. Чтобы установить количество знаков после запятой, нужно кликнуть правой кнопкой мыши по необходимой ячейке и выбрать «Формат ячеек», где определяется категория формата: числовой, текстовый, процентный, дата и др. (в нашем случае для граф 4–6 нужен числовой формат), а затем устанавливается количество десятичных знаков (для рассматриваемого примера — 2).

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

2. Чтобы применить конкретный формат одной ячейки к другим ячейкам, используют функцию «Формат по образцу», представленную на вкладке «Главная» основного меню.

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

Функция «СУММЕСЛИ»

Функция также предназначена для суммирования значений ячеек. Отличительная особенность — назначение конкретного условия (критерия) отбора. Для определения условия используются символы («˃», «<» и др.) и непосредственно текстовые значения ячеек. Рассмотрим пример применения функции «СУММЕСЛИ» (рис. 3).

В таблице с исходными данными, приведенной на рис. 3, отображены расходы предприятия по двум обособленным подразделениям (ОП) — г. Москва и г. Липецк. Суммарные расходы по этим подразделениям составляют 4924 руб. Необходимо рассчитать расходы каждого подразделения. Для этого воспользуемся функцией «СУММЕСЛИ», формула которой имеет следующий вид:

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

Для рассматриваемого примера (см. рис. 3) формулы приобретут следующий вид:

=СУММЕСЛИ(F17:F24;"Москва";E17:E24) = 2750 руб.;

=СУММЕСЛИ(F17:F24;"Липецк";E17:E24) = 2174 руб.

Рассмотрим работу формулы на примере обособленного подразделения в г. Москва:

первый диапазон ячеек (F17:F24) — это столбец для отбора, где представлены наименования подразделений; критерий отбора в данном случае — Москва;
второй диапазон (E17:E24) — столбец с суммами расходов, из которых программа выберет те, которые имеют отношение только к критерию отбора, и просуммирует их.

Как отмечено ранее, диапазон суммирования (в формуле указан в квадратных скобках) не является обязательным к заполнению. Например, на основании исходных данных, представленных в таблице на рис. 3, необходимо посчитать сумму расходов в размере 1000 руб. Тогда формула приобретет следующий вид:

=СУММЕСЛИ(E17:E24;1000) = 3000 руб.

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

Функции «ЕСЛИ» и «СЧЕТЕСЛИ»

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

Функция «СЧЕТЕСЛИ» предназначена для расчета количества ячеек по заданному критерию в формуле и имеет следующий вид:

Функция «ЕСЛИ» позволяет сравнивать значения и в зависимости от результата выводить итог при верном или неверном сравнении. Формула выглядит следующим образом:

Рассмотрим пример применения данных функций (рис. 4).

Для рассматриваемого примера необходимо определить, опаздывал ли сотрудник Иванов И. И. на работу, при условии, что рабочий день согласно трудовому распорядку предприятия начинается в 9 утра. Для этого в графе «Примечание» нужно установить факт наличия опозданий. С этой целью применяем формулу:

=ЕСЛИ(G40>F40;"опоздание";"-"), где необходимым условием к выполнению является превышение значения ячеек «G» (время фактического зафиксированного прибытия работника) над значением ячеек «F» (нормативное время прибытия).

Если неравенство выполняется, функция «ЕСЛИ» установит в ячейках «Н» — «опоздание»; если неравенство не выполняется, будет установлен прочерк, который показывает, что факт нарушения трудовой дисциплины не выявлен.

Для определения количества опозданий воспользуемся функцией «СЧЕТЕСЛИ»:

=СЧЕТЕСЛИ(H40:H47;"опоздание") = 2, где функция отбирает ячейки в диапазоне H40:H47 со значением «опоздание» и выводит их количество. В нашем случае Иванов И. И. опоздал на работу дважды, что и посчитала указанная функция.

Дополнительно отметим еще несколько функций с критериями: «ЕСЛИОШИБКА», «СЧЕТЕСЛИМН» и «СЧЕТЗ».

«ЕСЛИОШИБКА» возвращает значение, если вычисление по формуле выдает ошибку, в противном случае — возвращает результат формулы:

«СЧЕТЕСЛИМН» — функция, похожая на «СЧЕТЕСЛИ», единственное отличие заключается в возможности применения нескольких критериев. Если бы в рассматриваемом примере (рис. 4) не провели предварительный отбор по конкретному сотруднику и по графе 2 встречалось бы несколько сотрудников, то для определения количества опозданий для каждого сотрудника в отдельности нужно было применять функцию «СЧЕТЕСЛИМН».

«СЧЕТЗ» — наиболее простая функция среди рассмотренных, которая рассчитывает количество непустых ячеек в заданном для анализа диапазоне.

Функции «МИН» и «МАКС»

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

На основании исходных данных таблицы, представленной на рис. 4, определим максимальное и минимальное время прибытия на работу сотрудника Иванова И. И.:

Функция «ЧИСТРАБДНИ»

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

=ЧИСТРАБДНИ(нач_дата;кон_дата;[праздники]), где начальная и конечная дата являются обязательными условиями для заполнения, а праздники заполняются при необходимости.

Рассмотрим пример определения количества рабочих дней за период на основании таблицы, представленной на рис. 5.

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

=ЧИСТРАБДНИ(B63;C63) = 22 рабочих дня.

Определим количество рабочих дней в июне2018 г., если известно, что 12.06 — государственный праздник. При написании формулы нужно уточнить информацию о празднике:

=ЧИСТРАБДНИ(B64;C64;C66) = 20 рабочих дней.

Функция «СРЗНАЧ»

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

Рассчитаем на основании исходных данных таблицы, отраженной на рис. 4, среднее время прибытия сотрудника на работу. Формула будет иметь следующий вид:

Часто функцию «СРЗНАЧ» используют для расчета среднего уровня заработной платы. Рассмотрим соответствующий пример с числовыми данными (рис. 6).

Таблица на рис. 6 содержит сведения о зарплате каждого сотрудника. Нужно рассчитать средний уровень зарплаты среди представленных сотрудников:

=СРЗНАЧ(D77:D82) = 55 222,39 руб.

Данная формула рассчитала среднеарифметическое по диапазону ячеек с суммами заработных плат. Аналогичный результат получим, разделив итоговую сумму (331 334,34 руб.) на количество сотрудников (6 чел.).

АНАЛИЗ ДАННЫХ С ПОМОЩЬЮ ИНСТРУМЕНТА «СВОДНЫЕ ТАБЛИЦЫ»

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

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

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

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

Пример использования инструмента «Сводные таблицы»

Рассмотрим пример использования инструмента MS Excel «Сводные таблицы» на основании исходных данных, приведенных в табл. 1 .

Таблица 1. Исходные данные для применения инструмента MS Excel «Сводные таблицы»

Читайте также: