Расчет бизнес плана в Excel

Содержание

Порядок разработки плана продаж

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

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

  1. Проанализировать тенденции в политике и макроэкономике. Как изменяется ВВП страны? Что происходит с ценами на нефть, газ и с курсом валют? Не лишним будет ознакомиться с мнениями экспертов, ведущих экономических СМИ.
  2. Изучить ситуацию на рынке. Увеличится или уменьшится спрос? Появились ли новые конкуренты и потенциальные клиенты?
  3. Вывести статистику продаж по прошлым периодам. За год в целом и за каждый месяц в частности.
  4. Проанализировать причины спада и роста. Это может быть сезонность, изменения в политике компании, новый ассортимент, кадровые изменения. Составляя план на будущий год, обязательно опирайтесь на значимые моменты.
  5. Составить статистику продаж отдельно по продавцам и отделам. Ориентироваться на лидеров будет слишком оптимистично, но постарайтесь немного приблизить к ним среднее значение.
  6. Сформировать базу постоянных покупателей. Сколько прибыли они приносят, как часто и за какими товарами они приходят? Конечно, этот этап не относится к компаниям, ориентированным на разовые продажи.
  7. Поставить цель. На основе сделанного ранее анализа уже можно представить какие были продажи в прошлом году, и на сколько их возможно увеличить в будущем. Лучше поставить две цели: выполнимую и идеальную. Именно наличие второй будет напоминать, что не стоит останавливаться на достигнутом.
  8. Обсудить план с подчиненными. Установить сроки и личностные инструкции.
  9. Составить бюджет. Имея наглядный план продаж, проще рассчитать, сколько придется потратить на закупки, рекламу, на премии сотрудникам.

Методы расчета плана продаж

При расчете планируемых продаж можно использовать следующие методы:

  1. Субъективные: опросы, анкеты, решения, основанные на опыте предпринимателя;
  2. Объективные: тестовые продажи, анализ ранних периодов, статистика спроса.

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

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

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

Метод Преимущества Недостатки Краткое описание
Анализ ожиданий покупателей Оценка и подробная информация о товаре идет от потенциальных потребителей. Эффективно для новых продуктов Возможны ошибки при определении группы покупателей. Зависимость от точности оценок Применяются опросы потенциальных покупателей для оценки товара
Мнение персонала Точность Низкая объективность План составляется на основе мнения продавцов
Коллективное мнение руководителей Просто и быстро Коллективная ответственность Оценка руководителей усредняется, а если возникают сильные разногласия – проводится обсуждение
Метод Дельфи Самый объективный из субъективных методов, влияние мнения группы сводится к минимуму Долго и относительно дорого Руководители компании (или другие сотрудники) составляют каждый свой прогноз относительно объема продаж (по продуктам и периодам) и передают его эксперту. Он формирует анонимное резюме и снова раздает его участникам исследования, которые изучают его и предлагают новый прогноз. Так продолжается до сглаживания всех разногласий.
Рыночный тест Полная проверка реакции потребителей на товар и оценка маркетинга Открытость перед конкурентами, долго и дорого В различных регионах проводятся тестовые продажи продукта
Анализ временных рядов Объективно и дешево Метод сложен в исполнении, не учитывает влияние маркетинговых кампаний, не подходит для новых товаров Делится на три вида: скользящее среднее, экспоненциальное сглаживание, декомпозиция
Статистический анализ спроса Объективный и понятный результат, позволяет выявить скрытые факторы, влияющие на сбыт Самый сложный и трудоемкий метод Прогноз делается на основе всех факторов, влияющих на продажи (экономические индексы, колебания курса валют и другие)

Анализ временных рядов

Скользящее среднее

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

Пример. В магазине канцтоваров за 2016 год продали 2700 шариковых ручек, за 2015 – 3140, за 2014 – 2900. Прогноз на 2017 год: (2700+3140+2900)/3=2910.

Экспоненциальное сглаживание

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

Константа сглаживания (КС) может быть от 0 до 1. При среднем уровне продаж она равна 0,2-0,4, а во время роста (например, праздничные дни) – 0,7-0,9. Наиболее подходящее значение КС определяется опытным путем – выбирается значение с наименьшей погрешностью за прошедшие периоды.

Формула: КС * Фактический спрос за текущий период + (1-КС) * Прогноз на текущий период.

Пример. За месяц в магазине канцтоваров продали 640 блокнотов при предыдущем прогнозе 610, КС – 0,3. Прогноз на следующий месяц: 0,3*640 + (1-0,3)*610= 619.

Декомпозиция и коэффициент сезонности

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

Шаг 1. Определение сезонной динамики. Наглядным цифровым показателем здесь выступит коэффициент сезонности.

Чтобы его рассчитать, нужно:

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

Пример. За прошлый год магазин осуществил продаж на 850 000 руб. Из них 44 000 в январе, 50 000 в феврале и так далее. Среднемесячное значение 850000/12 = 70 830 руб. Коэффициент сезонности января: 44000/70830=0,62, для февраля: 50000/70830=0,71.

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

Шаг 2. Определить цель. Например, вы ставите цель увеличить продажи на 20%. Расчет произвести просто: к сумме продаж за прошлый год нужно прибавить 20%.

850000+20% = 1 020 000 руб.

Шаг 3. Составить план продаж на месяц. Общий план на год далее предстоит разбить на более мелкие периоды – в нашем примере это месяцы.

  1. Годовую цель разделить на 12 – получится средний план на месяц.
  2. Умножить средний план на коэффициент сезонности по каждому месяцу.

Пример. Средний план на месяц: 1020000/12 = 85 000 руб. План на январь: 85000*0,62 = 52 700 руб., план на февраль: 85000*0,71 = 60 350 руб.

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

Оформление плана продаж

План продаж как документ состоит из нескольких пунктов.

Перечислим все основные по порядку:

  1. Шапка, состоящая из заголовка («План продаж отдела….») и указания автора («Составлен…» далее должность и Ф.И.О. составившего план).
  2. Первый пункт – сотрудники и достижения. Здесь стоит перечислить всех работников отдела, указать потребность в новых кадрах, если есть таковая, а также упомянуть ключевые достижения за прошлый период.
  3. Второй пункт – итоги прошлого периода. Для наглядности можно включить в документ график роста и спада продаж, привести итоговые значения не только по отделу в целом, но и по каждому сотруднику в частности, в процентном соотношении указать, насколько был перевыполнен или недовыполнен прошлый план.
  4. Третий пункт – план на будущий период. Указывается сумма плана, перечисляются основные планируемые сделки, клиенты, которые уже готовы заключить контракт и прочие моменты, обеспечивающие гарантию прибыли в новом периоде.
  5. Четвертый пункт – необходимые мероприятия. Далее, речь идет о действиях, которые еще только предстоит выполнить для достижения цели. Это могут быть изменения ценовой политики, рекламные акции, обновление технической базы компании и многие другие.
  6. Дата и подписи утвердивших план руководителей.

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

Структуризация плана

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

Опираясь на особенности своего дела, поставьте цели сразу в нескольких направлениях:

  • Доля регионального и макрорынка;
  • Объем продаж в целом;
  • Финансовая прибыль.

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

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

Подобная детализация плана необходима на каждом предприятии.

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

  • Регионы (где и сколько будет продано);
  • Продавцы (кто и сколько продаст);
  • Товары (сколько чего будет продано);
  • Время (когда и сколько будет продано);
  • Каналы сбыта (кому и сколько будет продано);
  • Характер продаж (сколько продаж гарантировано, а сколько только запланировано).

Распространенные ошибки

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

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

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

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

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

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

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

Будьте уверены, что план вы составили верно, если по итогам периода он оказался выполнен на 85-105%.

Как добиться выполнения плана

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

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

Лучше прислушайтесь к советам опытных предпринимателей:

  1. Коротко, но максимально полно сформулируйте, что вы хотите от своих работников. Лучше донести это до них в письменной форме.
  2. Стимулируйте финансово. Лучшие работники достойны премии.
  3. Установите бонусы не только за 100% выполнение, но и каждое преодоление некоего минимального порога (например, 60%). Пусть работник и не выполнил план, но видно, что он старался.
  4. Штрафуйте за систематические нарушения.
  5. Финансово зависеть от выполнения планов должна вся вертикаль сотрудников (от рядового продавца до топ-менеджера).
  6. Уважайте и цените своих сотрудников и стремитесь к тому, чтобы они любили свое место работы и были заинтересованы в развитии и процветании фирмы.

​3 крутых Excel отчета для продуктивного планирования продаж для малого бизнеса

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

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

Честно говоря, я так много провел в Excel, что начал думать в терминах таблиц и даже мечтать в колонках и строчках. Много моделей назад, когда я бы аналитиком хэдж-фондов, мой босс, один из двух гениев, которых я когда-либо встречал, дал мне небольшой совет, который определил мое будущее с финансовыми моделями: “Всегда следуй за деньгами”. Я анализировал потенциальные инвестиции в фонд и пытался понять некоторые последние стратегические решения компании. Комментарий моего босса только усилил мое новое увлечение и желание хорошо проработать финансовую модель, которая бы отражала, сколько денег проходит через организацию и насколько эффективно работают менеджеры по продажам, — делится в своей статье Тим Бранк.

Если вы управляете небольшой компанией, то умение связать стратегию с реальными цифрами действительно критично. Например вы уверены, что вы можете увеличивать вашу прибыль каждый месяц на 25% в следующем году. Какими показателями вы будете руководствоваться, чтобы достичь такого роста? Какую вы должны будете иметь CAC (Customer Acquisition Cost — стоимость привлечения клиента) в связи с такой задачей?

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

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

Три следующие модели помогут избежать таких ситуаций:

  1. Отчет по прогнозированию на три года.
  2. Модель последних четырех месяцев.
  3. Когортный анализ доходов.

Прогнозирование на три года

О чем:

Позволяет подсчитать простой доход и модель прогнозирования стоимости.

Описание:

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

Что нужно делать:

  1. Поставить стратегические цели, которых ваш бизнес должен достичь.
  2. Просчитать план роста.
  3. Провести стресс-тест (увеличить и уменьшить запланированную прибыль и посмотреть на способность вашего бизнеса справиться в новых условиях)/
  4. Установить ключевые метрики (KPI).
  5. Спланировать потребность в персонале, исходя из разных сценариев (обычно на 12 месяцев).
  6. Установить черту, сколько минимум/максимум капитала вы планируете привлечь.

Оговорки:

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

Если интересно разобраться, то отчет можно посмотреть или скачать .

Когорный анализ доходов

О чем:

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

Описание:

Эта модель позволяет глубже разобраться в росте прибыли, посмотрев на прошлые и ожидаемые изменения в ACV, количестве клиентов и MRR (ежемесячный текущий доход) в виде когорт. Когортный анализ оценить, что будет катализатором доходности — постепенное увеличение цены контракта или за счет роста объема клиентов (что может быть проблематично в определенном масштабе).

Что нужно делать:

  1. Распределить свой доход (прошлый и будущий) по когортам.
  2. Разобраться в показателях оттока клиентов и LTV.
  3. Расставить приоритеты в работе отдела продаж: необходимый объем контрактов или рост величины стоимости годового контракта.

Оговорки:

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

Если интересно разобраться, то отчет можно посмотреть или скачать .

Выводы

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

Не нужно быть финансовыми директором, чтобы понять каким образом добиться роста, если хорошо разобраться в этих трех моделях. Уверены, что если вы уделите этим Excel-таблицам достаточно времени, то скоро вы сможете строить комплексные бизнес-процессы и финансовые траектории. А если достигните настоящих высот в прогнозировании, то обязательно доберетесь и до моделирования в 3D-симуляторах.

Если вам интересна аналитика продаж, рекомендуем также прочитать статью на эту тему на блоге Ringostat.

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

— Реклама —

Представленные ниже шаблоны Excel можно дорабатывать самостоятельно, а информацию из них при необходимости можно распечатывать и использовать в бумажном варианте:

БЮДЖЕТ ПУТЕШЕСТВИЙ

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

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

Ссылка для скачивания формы калькулятора: Vertex42.com

СПИСОК ЗАДАЧ

Какой бы хорошей ни была ваша память, забыть что-то вполне реально. И еще ничего страшного, если среди забытых дел окажется поливка цветов. Забыть о дне рождения близкого человека или о необходимости оплатить интернет-услуги – проблема намного серьезнее. Для таких дел, как минимум, стоит вести списки. Более современным и гибким является учет дел в онлайн-планировщиках задач, подобно таким программам как Wunderlist и другие. Если же вы хотите хранить максимально исчерпывающую информацию в одном файле, то можно удобно воспользоваться перечнем дел в Excel, список которых можно вести параллельно с другими страницами.

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

Ссылка для скачивания шаблона списка: Vertex42.com

— Реклама —

ИНВЕНТАРНЫЙ СПИСОК

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

Ссылка для скачивания шаблона списка: Vertex42.com

КНИГА КОНТАКТОВ

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

Ссылка для скачивания шаблона книги контактов: Vertex42.com

ДИАГРАММА ГАНТА

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

Ссылка для скачивания шаблона диаграммы Ганта: Vertex42.com

ФАМИЛЬНОЕ ДЕРЕВО

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

Ссылка для скачивания шаблона семейного дерева: Vertex42.com

— Реклама —

ГРАФИК ДЕЖУРСТВ

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

Ссылка для скачивания шаблона расписаний: Vertex42.com

ЖУРНАЛ АВТОПРОБЕГА

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

Ссылка для скачивания шаблона журнала: Vertex42.com

ЕЖЕДНЕВНИК

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

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

Ссылка для скачивания шаблона ежедневника: Vertex42.com

Следить за выходом наших новых материалов теперь также можно и через Telegram-канал. Присоединяйтесь!

>Формулы расчета НДС

Одна из задач на проценты — расчет НДС, расчет суммы с НДС, суммы без НДС, выделения НДС из общей суммы.

Как рассчитать ндс в эксель?

Как правило, НДС (налог на добавленную стоимость) равен 18%, но также существуют товары, которые облагаются налогом по ставке 10%.

При расчёте НДС бывают 2 различные ситуации:

1) Есть сумма без НДС, нужно найти, чему равен НДС и сумма с НДС.

2) Сумма включает НДС, в этом случае требуется найти сумму без НДС и сам НДС.

Итак, как посчитать НДС в Экселе (Excel)?

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

Но в Интернете можно найти надстройку, в которой имеется специальная функция для НДС. Эта надстройка называется Vba-Excel, скачать её можно .

Открываем загруженный файл, после чего нажимаем на кнопку «Установить» (требуется поддержка макросов).

После установки включаем эту надстройку через «Параметры Excel» -> «Надстройки».

В результате этого в Экселе появится ряд дополнительных функций и макросов, в том числе функция «НДС» — НДС(ЧИСЛО; ТОЛЬКО_НДС; СТАВКА).

Обязательные аргументы:

ЧИСЛО — исходное число (оно может как включать в себя налог, так и не включать).

Необязательные аргументы:

ТОЛЬКО_НДС — может принимать значения 0 (требуется найти сумму без НДС) или 1 (требуется найти сумму с НДС). По умолчанию = 1.

СТАВКА — если она равна 18%, то пишем 0,18; если 10% — пишем 0,1. По умолчанию = 18%.

Рассмотрим работу этой функции на примере. Посчитаем, чему равна сумма без НДС, ставка НДС равна 18%.

Как посчитать НДС в Excel без использования указанной выше функции?

1) Если требуется найти, чему равно НДС от заданного числа, нужно умножить это число на ставку НДС. При этом формат конечной ячейки желательно указать как «денежный».

Ставку НДС указываем в отдельной ячейке, а в формуле делаем на неё абсолютную ссылку.

Сумма с НДС = Сумма без НДС + НДС.

2) Если дана сумма с НДС, то для определения НДС нужно воспользоваться формулой:

НДС = (Стоимость с НДС * Ставка) / (1 + Ставка).

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

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

  1. Время, потраченное сотрудниками на работу.
  2. Количество и качество используемых материалов.
  3. Использование оборудования.
  4. Сырьё.

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

Что входит в себестоимость

Себестоимость: основное понятие

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

Себестоимость у компаний бывает двух разновидностей:

  • Согласно планам.
  • По факту.

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

  1. Расходные нормы по материалам.
  2. Сырьё.
  3. Трудозатраты.
  4. Оборудование.

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

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

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

О видах

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

  1. Общие расходы, по производству и хозяйству.
  2. Вознаграждение для рабочих за труд. Сюда относится и стандартная заработная плата.
  3. Приобретение топлива, сырья и тому подобных расходных материалов.
  4. Использование определённых материалов.

Когда речь идёт о полной себестоимости, учитываются расходы, связанные не только с самим производством, но и, например, коммерческие. Речь идёт о процессе реализации продукции, рекламе, хранении, создании подходящей упаковки, оплате труда продавцов, и тому подобных вещах. Пример расчёта в excel поможет разобраться.

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

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

О роли калькуляций в управлении предприятием

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

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

У самих процессов калькуляции выделяют три этапа:

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

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

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

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

О принципах и объектах калькулирования

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

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

От особенностей производства зависит выбор конкретной единицы измерения.

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

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

Методы калькуляции

О методах в калькулировании

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

Пропроцессный

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

Применение метода подходит предприятиям со следующими признаками:

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

Для продукции себестоимость считается за три этапа:

  1. Определение себестоимости для всех товаров, выпущенных производством. Затем осуществляем деление затрат на количество изделий. Получаем итог, связанный именно с единицей продукции.
  2. Осуществляем деление сумм деятельности по коммерции и управлению на объём продуктов, созданных за определённый временной промежуток.
  3. Остаётся только сложить цифры, полученные за два предыдущих этапа.

Попередельный метод

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

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

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

Таблица в Excel

Позаказный

Метод лучше всего работает для уникальных изделий, каждое из которых изготавливается по индивидуальному заказу. Единичный тип производства – главная характеристика предприятий, для которых будет актуален позаказный способ калькулирования. Это имеет значение для тех, кому интересно, как сделать расчёты.

Следующие характеристики отличают производство единичного типа:

  • Широкий ассортимент по выпускаемым товарам. Повторения отсутствуют в значительной части. Создаются отдельные изделия, когда оформляются определённые заказы.
  • У каждого рабочего места своя специализация в плане технологий. Невозможно закрепить за отдельным местом рабочие процессы, детали на постоянной основе.
  • Использование инструментов с оборудованием универсального назначения.
  • Наличие серьёзного удельного веса при операциях по ручной сборке, доводке.
  • Персонал, состоящий из большого количества универсалов, чья квалификация находится на высоком уровне.

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

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

Калькуляция себестоимости продукции в Excel

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

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

Схема расчета калькуляции себестоимости:

  1. Возвратные отходы считаем от расходов на сырье и материалы (берем указанный процент).
  2. Для определения дополнительной заработной платы учитываем следующие данные: если основная зарплата более 200 тыс. руб., то дополнительная равняется 10% от основной; менее 200 – 15%.
  3. Начисления на зарплату – 30% от суммы основной и дополнительной заработной платы (дополнительные 10%, которые введены с 2015 года на годовой доход более 600 тыс. руб. здесь не учитываются).
  4. Издержки на содержание оборудования – 5% от основной зарплаты.
  5. Общехозяйственные расходы – 9% от среднего показателя основной заработной платы.
  6. Общепроизводственные – 18% от (25% ОЗП + 75% ДЗП). ОЗП – основная заработная плата, ДЗП – дополнительная.
  7. Производственная себестоимость = сумма расходов на содержание оборудования, сырье и материалы, топливо и энергию, комплектующие, ОЗП и ДЗП, начислений на ЗП, общепроизводственных и общехозяйственных расходов за вычетом возвратных отходов.
  8. Непроизводственные затраты (издержки) – 3% от производственной себестоимости.
  9. Полная себестоимость = производственная себестоимость + издержки производства.
  10. Прибыль изготовителя считаем в процентах от полной себестоимости.
  11. Оптовая цена = полная себестоимость + прибыль изготовителя.
  12. НДС считаем от оптовой цены.
  13. Отпускная оптовая цена = оптовая цена производителя + косвенные налоги (НДС в примере).

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

Пояснения к расчету некоторых калькуляционных статей:

  1. Возвратные отходы – =B2*12,54% (процент взят из первой таблицы).
  2. Дополнительная заработная плата – =ЕСЛИ(B6
  3. Начисления на зарплату – =(B6+B7)*30%. Если следовать букве закона и брать дополнительные 10% с годовой зарплаты свыше 600 тыс. руб., то используем все ту же функцию «ЕСЛИ».
  4. Содержание оборудования – =B6*5%.
  5. Общепроизводственные расходы – =18%*(B6*25%+B7*75%).
  6. Общехозяйственные расходы – =9%*B6.
  7. Производственная себестоимость – =(B2+B3+B5+B6+B7+B8+B9+B10+B11)-B4.
  8. Издержки производства – =3%*B12.
  9. Полная себестоимость – =B12+B13.
  10. Прибыль изготовителя – =B14*3,45%.
  11. Оптовая цена изготовителя – =B14+B15.
  12. Формула для расчета НДС – =B16*20%.
  13. Для расчета отпускной оптовой цены – =B16+B17.

По такому же принципу проводится калькуляция себестоимости продукции В и С.

Можно сделать так, чтобы Excel брал исходные данные для расчета сразу в соответствующих таблицах. Например, сырье и материалы – из производственного отчета. Зарплата – из ведомости. Если, конечно, все это ведется в Microsoft Excel.



Вариант задания

1. Создайте в табличном процессоре Excel таблицу 1 и заполните ее исходными данными. Автоматически пронумеруйте столбцы таблицы и показатели в столбце 1. Выполните следующие расчеты:

  • Валовая прибыль = Торговые доходы – Торговые расходы;
  • Общие затраты определяются как сумма расходов на зарплату, на рекламу и накладных расходов;
  • Производственная прибыль = Валовая прибыль – Общие за-траты;
  • Удельная валовая прибыль = Производственная прибыль / Торговые доходы;
  • Итого за год определяется как сумма квартальных данных.

Таблица 1. Расчет прибыли фирмы, тыс. руб.

№ п/п

Показатели

Год

Итого за год

1 кв.

2 кв.

3 кв.

4 кв.

1

2

3

4

5

6

7

1

Торговые доходы

143662

175587

127700

191549

2

Торговые расходы

89789

109742

79812

119712

3

Валовая прибыль

4

Расходы на зарплату

8000

8550

9000

9600

5

Расходы на рекламу

10100

10130

10180

10250

6

Накладные расходы

21549

26338

19155

28732

7

Общие затраты

8

Производственная прибыль

9

Удельная валовая прибыль

2. На втором листе создайте таблицу 2. Столбец 2 заполните на основании ссылки на соответствующую ячейку таблицы 1. На основании данных таблицы 1 Рассчитайте структуры затрат фирмы по кварталам и году в целом по следующей формуле: Расходы / Общие затраты * 100. Результаты расчетов округлить до одного знака после запятой.

Таблица 2. Структура затрат фирмы по кварталам, %

№ п/п

Показатели

Год

Итого за год

1 кв.

2 кв.

3 кв.

4 кв.

1

2

3

4

5

6

7

1

Расходы на зарплату

2

Расходы на рекламу

3

Накладные расходы

4

Общие затраты

100

100

100

100

100

3. На отдельном листе по данным таблицы 1 (№ п/п 1, 2, 3 и столбцы 3, 4, 5 и 6) постройте объемную гистограмму с заголовком, легендой, названием осей. 4. На отдельном листе по данным таблицы 1 (№ п/п 2, 4, 5, 6 и столбец 7) постройте объемную разрезанную круговую диаграмму с заголовком, легендой и указанием долей. 5. Каждому листу присвойте имя, отражающее содержание таблицы, например, Лист1 назовите «Прибыль_руб.». 6. Введите в нижний колонтитул факультет, курс, номер группы, свою фамилию, дату и время, имя файла.

Как при помощи Excel создать таблицу для расчета прибыли

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

Рис. 1. Таблица расчета прибыли (с числовыми данными)

В данной таблице имеются четыре области:

  • Ставки налогов (область А1:В2).
  • Суммы реализованных товаров, издержек и дебетового НДС (область А4:В6).
  • Расчет НДС, подлежащего уплате в бюджет (область А8:В9).
  • Расчет прибыли и налогов на прибыль (область А11:В14).

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

Рис. 2. Таблица расчета прибыли (с формулами)

Формула для расчета налогового обязательства по НДС описывалась в этой статье. Табличный вид этой формулы следующий: =ОКРУГЛ(B4*(B1/(1+B1));2).

Формула расчета НДС, подлежащего уплате в бюджет, определяется вычитанием суммы налогового кредита из суммы налоговых обязательств (находится в ячейке В10): =В8–В6.

Валовая прибыль без НДС (формула в ячейке В12) равна разности суммы реализации и суммы налоговых обязательств: =В4–В8.

Полученную в результате реализации прибыль можно определить путем вычитания издержек из суммы валовой прибыли: =В11–В5.

Налог на прибыль равен округленному до второго разряда (до копеек) произведению прибыли на действующую ставку налога на прибыль: =ОКРУГЛ(B12*B2;2).

Чистая прибыль в результате реализации равняется разности налогооблагаемой прибыли и суммы налога на прибыль: =В12–В13.

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

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

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

Точка безубыточности за 5 минут: формула расчёта и пример таблицы в Excel

Думаю, многие уже знают, что расчёт точки безубыточности является крайне важным мероприятием при старте любого бизнеса. В моем окружении это многие прекрасно понимают. Но недавно мне поступил вопрос от читателя. Вот как мне, говорит, в дни сезонных колебаний спроса да в дни тягостного кризисного застоя сбыт свой распланировать, чтобы и на плаву остаться и конкурентов перехитрить? Секунду поразмыслив, я его воодушевил названием вполне себе конкретного способа, который может помочь принять решение не только о том как сбыт подкорректировать, но и как цены установить наиболее оптимальные. Да и хорош этот способ не только и не столько в дни ослабления спроса, а ещё до момента выхода на рынок, когда особо остро встаёт вопрос ценообразования и осознания возможности выжить на рынке вообще. И как вы уже, наверное, догадались, связан этот способ с расчётом самой настоящей точки безубыточности вашего дела. Притом не важно, чем именно вы занимаетесь: у вас свой косметический салон в оффлайне или вы занимаетесь арбитражем трафика онлайн — она всегда готова прийти к вам на помощь и сохранить уйму вашего драгоценного времени и жизненной энергии, и уберечь от заранее предопределённого провала.

Итак, что же такое точка безубыточности? Вот что нам говорит экономический букварь:

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

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

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

Переменные издержки — издержки, которые появляются при производстве каждой новой единицы продукции (оказанной услуги).

Постоянные издержки — издержки, которые практически неизменны на протяжении всего расчётного периода.

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

  • Фонд оплаты труда.
  • Социальные отчисления.
  • Аренда помещения (если помещение не в собственности, естессно).
  • Коммунальные услуги и охрана.
  • Связь (телефон, хостинг, интернет).
  • Реклама.
  • Прочие раходы.

Таким образом, на данном этапе мы можем сделать 2 очень важных вывода:

  1. С увеличением объёма производства мы увеличиваем расходы исключительно за счёт переменных издержек.
  2. С увеличением объёма производства величина постоянных издержек на единицу продукции снижается. Это один из элементов т.н. эффекта масштаба.

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

Как рассчитать доход по облигациям, используя функции Excel

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

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

  • Расчетный день. Дата, в которую долговые обязательства переходят к покупателю.
  • Срок погашения. Дата, в которую заем (представленный облигацией) возвращается покупателю.
  • Ставка (также называемая купоном). Процентная ставка, которую выпустивший облигацию выплачивает покупателю.
  • Доходность. Норма прибыли, получаемая покупателем. Включает в себя выплаты по процентам и дисконт.
  • Погашение. Выкупная стоимость ценных бумаг за 100 долларов номинальной стоимости.
  • Частота. Количество выплат дивидендов за год.

Оценка облигаций

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

Для примера предположим, что некоторая компания выпустила облигации с номинальной стоимостью 100 долларов, сроком погашения 10 лет и 6%-ной ставкой, выплачиваемой дважды в год.

  • Если процентная ставка по кредитам растет, ставка в 6% становится не такой уж и привлекательной. Покупатели уже не захотят платить 100 долларов за облигацию — они будут их покупать за меньшую сумму.
  • Если процентная ставка по кредитам падает, ставка в 6% становится привлекательной. В данном случае покупатели готовы платить больше номинальной стоимости.

Функция ЦЕНА вычисляет сумму, которую покупатели потенциально готовы заплатить за облигацию в ожидании определенного дохода. Синтаксис этой функции таков: ЦЕНА(расчетный_день;срок_погашения;ставка;доходность;погашение;частота;базис). Учитывая приведенные выше факты, инвестор, желающий получить 7,5% годовых, должен использовать следующую формулу для вычисления приемлемой цены облигации, срок погашения которой наступает через 8 лет: =ЦЕНА(СЕГОДНЯ();СЕГОДНЯ()+ДАТА(8;1;0);,06;,075;100;2).

Результатом этой формулы будет 91,10 долларов — именно такую сумму стоит заплатить за облигацию номиналом в 100 долларов с шестью процентами годовых, чтобы прибыльность составила 7,5%. Каждый год инвестор будет получать 6%*100 долларов, плюс при погашении облигации он получит дополнительную прибыль в 8,90 долларов. Эти два компонента — проценты и дисконт — формируют доходность облигаций. Фактическая дата выпуска облигации может отличаться от даты ее покупки инвестором. Облигации выпускают многие организации, так производитель кондиционеров кватро клима имеет достаточно высокую ликвидность своих ценных бумаг, которая основывается на качестве продукции.

В рассмотренном примере облигации были выпущены за два года до их покупки, следовательно, инвестор будет получать за них проценты только в течение 8 лет. Если же процентная ставка упала с момента выпуска и инвестору достаточно 5,2%, формула слегка изменится: =ЦЕНА(СЕГОДНЯ();СЕГОДНЯ()+ДАТА(8;1;0);,06;,052;100;2). При этих обстоятельствах инвестор может заплатить за стодолларовую облигацию 105,18 долларов. На рис. 11.17 эти вычисления показаны на рабочем листе.

Вычисление доходности

В предыдущем примере инвестор знал необходимую ему доходность и вычислял такую цену облигации, чтобы получить ее. Если же, наоборот, он знает цену облигации, то функция ДОХОД поможет вычислить прибыльность инвестиций. Синтаксис этой функции следующий: ДОХОД (расчетный_день;срок_погашения;ставка;цена_покупки;номинал;частота;базис).

Рис. 1. Использование функции ЦЕНА

Инвестор все еще заинтересован в покупке десятилетней облигации с 6 процентами, выплачиваемыми дважды в год, однако на этот раз он хочет заплатить всего 93,95 долларов за стодолларовую облигацию. Следующая формула вычисляет процентную ставку за восемь лет, оставшихся до даты погашения: =ДОХОД(СЕГОДНЯ();СЕГОДНЯ()+ДАТА(8;1;0);,06;93,95;100;2). Если инвестор заплатит за облигацию 93,95 долларов, то получит от своих инвестиций 7%. Если бы он заплатил за облигацию больше ста долларов, то получил бы от инвестиций меньше 6% (рис. 2).

Рис. 2. Когда цена выше номинальной стоимости облигации, прибыльность ниже купона

Решение финансовых задач в Excel. Функции ДОХОД и ИНОРМА

Задача 1.

Постановка задачи.

Рассматривается возможность приобретения облигаций трех типов, каждая из которых с номиналом в 100 руб. и сроком погашения 9.10.2007 г. Курсовая стоимость этих облигаций на дату 25.07.2005 г. составила соответственно 90, 80 и 85 руб.

Годовая процентная ставка по купонам (размер купонных выплат) составляет:

для первой облигации 8 % при полугодовой периодичности выплат;

для второй облигации – 5 % при ежеквартальной периодичности выплат;

для третьей облигации – 10 % с выплатой 1 раз в год.

Расчеты ведутся в базисе фактический/фактический.

Провести анализ эффективности вложений в покупку этих облигаций, если требуемая норма доходности составляет 15% .

Алгоритм решения задачи.

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

ДОХОД (дата_согл; дата_вступл_в_силу; ставка; цена; погашение; частота; базис)

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

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

Рис. 4.27. Применение функции ДОХОД для оценки доходности облигаций

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

Для облигации первого типа:

=ДОХОД (ДАТА(2005;7;25);ДАТА(2007;10;9);8%;90;100;2;1)= 13,36%

Для облигации второго типа:

=ДОХОД (ДАТА(2005;7;25);ДАТА(2007;10;9);5%;80;100;4;1)= 15,93%

Для облигации третьего типа:

=ДОХОД (ДАТА(2005;7;25);ДАТА(2007;10;9);10%;85;100;1;1)= 18,83%

Результаты, полученные различными способами, совпадают.

Доходность по второй и третьей облигациям (15,93% и 18,83% соответственно) выше заданной нормы (15%), а по первой облигации (13,36%) – ниже. Следовательно, целесообразно покупать облигации второго и третьего типов.

Задача 2.

Постановка задачи.

Коммерческий банк предлагает свои сберегательные сертификаты номиналом 100 000 руб. сроком на 8 месяцев. Дата соглашения – 10.01.2005 г. Цена продажи составляет 85 000 руб. Способ вычисления дня – фактический/360. Необходимо определить доход за этот период.

Алгоритм решения задачи.

Для вычисления доходности данной финансовой операции, возвращающейся в виде годовой ставки, рассчитанной по простым процентам, используем функцию ИНОРМА, которая задается следующим образом:

ИНОРМА (дата_согл; дата_вступл_в_силу; инвестиция; погашение; базис)

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

Для проверки правильности результата в функцию ИНОРМА введем значения аргументов в непосредственном виде:

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

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

На рисунке представлен финансовый отчет определенной производственной фирмы:

Валовая прибыль находится в ячейке B4, а процентная валовая прибыль в ячейке C4: =B4/$B$2

Формула расчета валовой прибыли просто вычитает значение ячейки B3 от B2. В формуле вычисления валовой прибыли в процентах значение ячейки B4 разделено на значение в $B$2:

Обратите внимание здесь используется процентный формат ячеек – это значит не нужно умножать на 100. Такой же результат мы получим по выше описанной формуле =B4/B2*100, но здесь не используем процентный формат ячеек.

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



Формула расчета процента наценки в Excel

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

Наценка рассчитывается путем деления цены на расходы и от результата вычитается число 1:

Устанавливая наценку в размере 32% получаем валовую прибыль в процентах 24%. То есть как описано выше в предыдущем примере =B8/B6. Чтобы определить размер наценки покрывающую процентную валовую прибыль в 32% (как в столбце D) используйте следующую формулу:

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

Интересный факт! Главное отличие процентной валовой прибыли от наценки заключается в формуле вычисления. Разницу очень легко заметить в этих двух показателях по их сути – валовая прибыль никогда не будет равна или превышать 100% в отличии от наценки. Для наглядного доказательства приведем простой пример, умножим цену на 10 000:

Расчет бизнес плана в Excel

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *