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

На 42 листах собраны
самые необходимые формулы
финансовой математики и финансового анализа:

1. Анализ финансового состояния. Вводите данные баланса и ОПУ, получаете готовый расчет: 70 показателей и 50 графиков.

Расчет включает:

Анализ баланса и ОПУ:

• Горизонтальный анализ Баланса за 3 года.
• Вертикальный анализ Баланса за 3 года.
• Горизонтальный анализ Отчета о прибылях и убытках за 3 года.

Динамика важнейших показателей за 3 года:

• Валюта баланса.
• Выручка.
• Себестоимость.
• Прибыль от продаж.
• Прибыль до налогообложения.
• Чистая прибыль.

Финансовые коэффициенты за 5 периодов (лет, полугодий, кварталов):

• 4 Группы активов (А1, А2, А3, А4) и пассивов (П1, П2, П3, П4), анализ их соотношения и динамику.
• 6 Показателей и коэффициентов ликвидности и их динамику.
• 13 Показателей и коэффициентов финансовой устойчивостии и их динамику.
• 14 Показателей и коэффициентов деловой активности и их динамику.
• 10 Коэффициента деловой активности и их динамику.

Основой для расчета финансовых показателей служат данные Баланса и ОПУ. Предусмотрены старая и новая форма баланса и ОПУ согласно Приказу Минфина от 02.07.2010 66н

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

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

• Краткое пояснение к показателю.
• Формула расчета.
• Нормативные границы. Показатели, выходящие за нормативные границы выделяются цветом.
• Динамика показателя — изменение показателя по отношению к предыдущему периоду.
• График абсолютных значений.
• График относительных изменений в процентах.

Эта часть таблицы состоит из 19 листов. 1 лист с пояснениями, 9 листов для расчета по старому Балансу и ОПУ и 9 листов для расчета по новому Балансу и ОПУ.

1. Баланс — Баланс
2. ОПУ — Отчет о прибылях и убытках
3. ОП — Основные показатели, налоговая нагрузка
4. Активы — Структура активов
5. Пассивы — Структура пассивов
6. Ликвидность — Показатели ликвидности
7. ФинУст — Показатели финансовой устойчивости
8. ДелАктив — Показатели деловой активности
9. Рентабельность — Показатели рентабельности
10. Баланс-11 — Новый Баланс
11. ОПУ-11 — Новый Отчет о прибылях и убытках
12. ОП-11 — Основные показатели, налоговая нагрузка
13. Активы-11 — Структура активов
14. Пассивы-11 — Структура пассивов
15. Ликвидность-11 — Показатели ликвидности, рассчитанные по новому балансу и ОПУ
16. ФинУст-11 — Показатели финансовой устойчивости, рассчитанные по новому балансу и ОПУ
17. ДелАктив-11 — Показатели деловой активности, рассчитанные по новому балансу и ОПУ
18. Рентабельность-11 — Показатели рентабельности, рассчитанные по новому балансу и ОПУ

Далее полностью показаны листы с 1 по 9. Листы с 10 по 14 аналогичны листам с 3 по 7.

Листы защищены от случайных изменений. Ячейки для ввода (не защищенные) помечены фоном. При желании Вы можете снять защиту, как это сделать написано в самом первом листе ВНИМАНИЕ.

Лист «Баланс» — Горизонтальный и вертикальный анализ баланса.

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

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

Иногда меня спрашивают, как сделать анализ не за 5 лет, а за 2 года или на начало и конец одного года, например, за 2008 год.

Надо поступить так:

• в столбцы Г1 и Г2 ввести данные для конца 2007 года (или начала 2008)
• в столбец Г3 ввести данные для конца 2008 года.

Тогда, в каждом коэффициенте значения для Г1 и Г2 это значения для 2007, а Г3 — для 2008. Так же и на графиках.

Лист «ОПУ» — Горизонтальный анализ Отчета о прибылях и убытках

На этом листе Вы вводите данные Отчета о прибылях и убытках за 5 периодов. Числа вводятся в ячейки залитые фоном. Итоги и горизонтальный анализ ОПУ рассчитывает таблица.

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

И это все! Больше ничего вводить не требуется. Дальше 5 листов с анализом финансового состояния, включающих расчет 70 коэффициентов и 50 графиков. Графики можно скопировать и вставить в свою работу.

Лист СтрАП. Основные показатели, структура активов и пассивов.

Лист Ликвидность. Показатели ликвидности.

Лист ФинУст. Показатели финансовой устойчивости.

Лист ДелАктив. Показатели деловой активности.

Лист Рентабельность. Показатели рентабельности.

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

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

2. Точка безубыточности.

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

Но это только число. Значительно полезнее увидеть на графике как ведут себя затраты и прибыль при отклонении от точки безубыточности. Еще полезнее быстро посчитать разные варианты и посмотреть, как меняются графики. Так уж устроен человек, что основную долю информации он получает визуально. В связи с этим разглядывание графиков приносит не только удовольствие, но и пользу — рождаются новые идеи.

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

Эта таблица не просто считает точку безубыточности, а еще строит графики восьми величин связанных с ней. Скопировать графики из Excel проще простого. Для тех, кто не умеет, сделана специальная кнопка — СКОПИРОВАТЬ.

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

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

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

Абсолютно УНИКАЛЬНЫЙ расчет. Совершенно точно, Вы нигде не найдете расчета и графика точки безубыточности в пересчете на единицу продукции. При таком подходе постоянные и переменные затраты как бы меняются местами, это позволяет посмотреть на ситуацию с новой стороны и приводит к неожиданным выводам. Чтобы не путать, этот график назван «Порог рентабельности». Вообще-то «Порог рентабельности» и «Точка безубыточности» обозначают одно и то же.

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

Описание таблицы и как она работает.

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

Полное описание этой части таблицы

Эта часть таблицы состоит из 3 листов.

1. Точка безубыточности
2. Порог рентабельности
3. Min Наценка

Лист «Точка безубыточности»

Задаете:

• Постоянные затраты
• Пременные затраты
• Цену
• Пределы измененения объема реализации: максимальный и минимальный объем реализации (выпуска) продукции.

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

• Суммарные переменные затраты
• Общие затраты
• Доход
• Маржинальный доход
• Прибыль
• Операционный рычаг
• Запас финансовой прочности

Данные для расчета:

Результаты расчета:

На графиках:

Чувствуете? Ввели всего 5 чисел и получили таблицу финансовых показателей и три графика.

Вид графика можно настраивать.

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

В верхней части колонок с рассчитанными показателями есть ячейки с управляющими символами. Если в этой ячейке есть любой символ (напр., буква) — соответствующий показатель рисуется на графике. Цвет символа определяет цвет линии на графике. "Жирность" символа определяет толщину линии.

Например, на приведенном выше рисунке в колонке Постоянные затраты стоит символ «y» красного цвета, шрифт жирный (bold). На графике будет изображена красная толстая линия постоянных затрат. Линии переменных затрат на графике не будет. Ячейка B21 (обведена зеленым) задает цвет фона графика, изменяя цвет фона (заливки) ячейки можно изменять цвет фона графика.

График перестраивается с учетом сделанных изменений после нажатия на кнопку Рассчитать.

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

Графики можно скопировать и вставить в отчет, доклад, дипломную работу, курсовую, реферат.

Лист «Порог рентабельности»

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

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

• Постоянные затраты на единицу продукции
• Общие затраты на единицу продукции
• Доход на единицу продукции
• Маржинальный доход на единицу продукции
• Прибыль на единицу продукции

Данные для расчета:

Результаты расчета:

На графике:

Вид графика можно настраивать.

Лист «Минимальная наценка»

Еще один взгляд на точку безубыточности — меня часто спрашивают: если объемы реализации известны, как рассчитать минимальную наценку на группу товаров, позволяющую окупить затраты.

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

Считаем, что переменные затраты — это затраты на закупку данной группы товаров. Т.е. это стоимость товара по закупочной цене.

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

• Доход
• Маржинальный доход
• Прибыль

Данные для расчета:

Результаты расчета:

На графике:

Вид графика можно настраивать.

3. Проценты и НДС.

Вы сможете делать расчеты с простыми и сложными процентами

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

Расчеты с НДС

• Расчет НДС
• Расчет суммы с НДС
• Расчета суммы без НДС
• Выделение НДС из общей суммы

4. Сравнение доходности депозитов.

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

• Расчет суммы вклада с процентами помесячно
• Расчет суммы процентов помесячно
• Расчет суммы процентов нарастающим итогом
• Подробный расчет в табличной форме
• Наглядное графическое сравнение доходности

5. Кредиты.

Вы сможете делать в табличной и графической форме подробные расчеты помесячных аннуитетных (равных) платежей по кредиту

• Сумма платежа помесячно (при аннуитете эта сумма постоянна)
• Сумма платежа нарастающим итогом
• Сумма процентов помесячно
• Сумма процентов нарастающим итогом
• Сумма гашения основного долга помесячно (при аннуитете эта сумма постепенно растет)
• Сумма гашения основного долга нарастающим итогом
• Сумма остатка основного долга помесячно
• Подробный расчет в табличной форме
• Наглядное графическое представление

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

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

Таблица работает на Excel для Windows (Excel 2003, 2007, 2010).

Как получить таблицу

Данная Excel-таблица объединяет 4 таблицы, которые можно скачать с этого сайта по отдельности:

• «Точка безубыточности» стоимостью 595 руб.
• «Расчет депозитов и кредитов» стоимостью 170 руб.
• «Расчет НДС и счет-фактура» стоимостью 170 руб.
• «Анализ финансового состояния» стоимостью 695 руб.

Вы можете получить эту «Объединенную» Excel-таблицу не за 1630 рублей (595+170+170+695), а за 1095 рублей, со скидкой 435 руб. от суммарной цены.

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

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

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

Меня зовут Владимир Прохоров, я автор сайта и этих электронных таблиц, со мной можно связаться по почте v_v_prokhorov@mail.ru (v_v_prokhorov@mail.ru) или по ICQ 419382331. Я знаю о чем многие подумали: хорошо бы узнать мнение покупателей этой таблицы.

Вот что пишут покупатели в гостевой книге сайта:

Ссылка на гостевую книгу сайта.

Если Вам нужна эта таблица, советую решать поскорее. Цена на таблицу периодически повышается, в сентябре 2009 года она стоила всего 295 руб.

Как получить
(способы оплаты ниже)

После оплаты напишите мне на v_v_prokhorov@mail.ru (v_v_prokhorov@mail.ru), в ответном письме будет таблица. Для большей надежности можно продублировать по ICQ 419382331.

В письме укажите реквизиты Вашего платежа.

Укажите при оплате на расчетный счет:

• Название таблицы «Объединенная».
• Сумму.
• Платеж на р/с.
• От кого платеж.
• Дату платежа.

Укажите при оплате на счета электронных денег:

• Название таблицы «Объединенная».
• Сумму.
• Куда платили: номер кошелька (счета) на который платили.
• Способ платежа. С WebMoney (укажите номер кошелька с которого платили), с Яндекс-кошелька (укажите номер кошелька с которого платили), через терминал (укажите систему: QIWI, Элекснет, и т.д.), через банк (укажите название банка).
• Дату и время платежа по московскому времени.
• Номер платежа (код платежа, идентификатор платежа и т.п.).

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

Таблица отправляется на тот адрес с которого пришло письмо с реквизитами платежа.

При использовании корпоративных почтовых ящиков

При использовании корпоративных почтовых ящиков указывайте запасной адрес на бесплатном почтовом сервере (mail.ru, gmail, yandex). Системные администраторы корпоративной почты часто блокируют почту с mail.ru и письма с прикрепленными файлами. Если корпоративный сервер сообщит, что отклонил письмо с таблицей как нежелательное, таблица будет отправлена на запасной адрес.

Дальнейшая переписка по поводу полученной таблицы

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

Способы оплаты

Расчетный счет

Деньги доходятся за время от 1 до 3 рабочих дней. Самый медленный платеж через Сбербанк.

Сейчас не нужно квитанций, просто дайте реквизиты операционисту.

Перевести 1095 рублей на расчетный счет
ИП Прохоров Владимир Викторович, ОГРНИП 311645410900040, ИНН 645400330452
р/с 40802810500000053426 в
ЗАО АКБ «Экспресс-Волга»
К/с 30101810600000000808 ГРКЦ ГУ Банка России по Саратовской области
БИК 046311808, ИНН 6454027396, КПП 645501001
Назначение платежа:
За электронную таблицу «Объединенная» От Фамилия И.О. по счету б/н от 05.02.2012. Без НДС.

Распечатать реквизиты платежа

Другие способы оплаты

 Из России  (Не из России далее)

Электронные деньги. Можно платить через терминалы и банкоматы.

Деньги доходятся за время от несколько минут до нескольких часов.

• Webmoney — перевести 1095 рублей на кошелек R199445241093
• Webmoney — перевести 37 USD на кошелек Z239057479947
• Яндекс.Деньги — перевести 1095 рублей на счет 41001190251168
• QIWI — с кошелька или через терминал перевести 1095 рублей на Webmoney кошелек R199445241093
  
  
• Пластиковая карта Visa или MasterCard — перевести 1095 руб. на счет Яндекс.Денег номер 41001190251168 с помощью сервиса netexchange.ru
  Слева под словами Я ХОЧУ КУПИТЬ (ВВОД) выбрать тип карты с которой платите (отдаете) и тип кошелька (Яндекс.Деньги) на который платите (получаете). Нажать кнопку ПРОДОЛЖИТЬ. Сумму платежа вводить в поле ПОЛУЧАЕТЕ.
  
  
• Через платежный терминал положить 1095 руб. на счет Яндекс.Денег 41001190251168.
  
  
• Через платежную систему CONTACT пополнить счет 41001190251168 Яндекс.Денег
  
  
• Через платежную систему Юнистрим и пополнить счет 41001190251168 Яндекс.Денег
  
  
• Через банкомат из этого списка и положить 1095 руб. на счет Яндекс.Денег 41001190251168

Распечатать реквизиты платежа

 Не из России 

• Webmoney — перевести 1095 рублей на кошелек R199445241093
• Webmoney — перевести 37 USD на кошелек Z239057479947
• Яндекс.Деньги — перевести 1095 рублей на счет 41001190251168
  
  
• Пластиковая карта Visa или MasterCard — перевести 1095 руб. на счет Яндекс.Денег номер 41001190251168 с помощью сервиса netexchange.ru
  Слева под словами Я ХОЧУ КУПИТЬ (ВВОД) выбрать тип карты с которой платите (отдаете) и тип кошелька (Яндекс.Деньги) на который платите (получаете). Нажать кнопку ПРОДОЛЖИТЬ. Сумму платежа вводить в поле ПОЛУЧАЕТЕ. Деньги доходятся за несколько минут.
  
  
• Через систему платежей CONTACT сделать перевод на мое имя. Платеж доходит за несколько часов.
  Список пунктов системы CONTACT
  Данные для перевода:
  Мои ФИО: Прохоров Владимир Викторович
  Пункт: Россия, Саратов, Экономбанк, Октябрьский филиал, ул. Тараса Шевченко 28, код пункта в системе CONTACT - XHGF
  Валюта перевода: рубли или USD
  
  Вы мне должны сообщить:
  
  1. от кого перевод (ФИО как в переводе)
  2. откуда (страна и город)
  3. номер/код перевода
  4. сумму и валюту перевода


• По Вестерн Юнион перевести 1095 руб. или 37 USD.
  Данные для перевода:
  Мои ФИО: Прохоров Владимир Викторович
  Страна и город назначения: Россия, Саратов
  Валюта перевода: рубли или USD
  
  Вы мне должны сообщить:
  
  1. от кого перевод (ФИО как в переводе)
  2. откуда (страна и город)
  3. номер/код перевода
  4. сумму и валюту перевода

Распечатать реквизиты платежа

Владимир Прохоров, 05.03.2010
исправлено 10.11.2011

P.S.
Обязательно подпишитесь на получение сообщений о новых таблицах и доработках существующих.

Посмотрите другие полезные таблицы