Volodina-vasilisa.ru

Антикризисное мышление
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Расчет прибыли в excel

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

Отчет по валовой прибыли в Excel с помощью сводных таблиц и диаграмм

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

В статье подробно написано о создании такого отчета, как на рисунке ниже.

Прежде чем начать читать, скачайте отчет и данные.

Исходные данные

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

Итак, нам нужен отчет в виде таблицы с такими столбцами:

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

Проанализируем продажи, представив все показатели отчета в понятном виде и на одном листе.

Структура отчета

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

Элементы управления

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

Карточки с показателями

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

Графики и диаграммы

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

Сводная таблица с деталями

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

Итак, в интерактивном отчете четыре блока:

А теперь подробно, по шагам. Начнем создавать отчет со сводной таблицы.

Сводная таблица

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

Откройте отчет о валовой прибыли в Excel и удалите из него сроку с итогами – мы рассчитаем их с помощью сводных таблиц. Еще лучше будет, если использовать форматированные (smart – умные) таблицы Excel. Для этого выделите мышкой любую ячейку отчета и перейдите в меню Главная -> Форматировать как таблицу -> выберите любой понравившийся вид таблицы.

Нажмите Ок. В появившемся меню Конструктор введите имя форматированной таблицы — «продажи»:

Добавьте в отчет сводную таблицу. Нажмите на любую ячейку форматированной таблицы, выберите Вставка -> Сводная таблица. Разместите сводную таблицу на новом листе, где-нибудь с 40-ой строки, т.к. эта таблица будет расположена в нижней части нашего отчета – для пояснений к графикам. Назовите лист «отчет».

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

Отформатируйте сводную таблицу. Щелкните правой кнопкой мышки по любой ячейке с цифрами таблицы, в появившемся меню выберите пункты:

    Параметры сводной таблицы -> убрать галочку «Автоматически изменять ширину столбцов при обновлении».

  • Задайте для данных числовой формат с разделителями разрядов, без цифр после запятой.
  • Чтобы отчет был нагляднее, сделайте сортировку по столбцу «Общий итог»: меню Данные -> Сортировка -> По убыванию.
  • После всех манипуляций должна получиться такая таблица:

    Сводные диаграммы и графики

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

    Кстати, из Power Pivot сводные диаграммы можно добавить без сводных таблиц.

    Читать еще:  Расчет сделки репо

    Всего в отчете будет пять графиков и диаграмм:

    1. График с динамикой продаж по периодам

    Выберите таблицу «продажи» и перейдите в меню Вставка -> Сводная диаграмма -> разместите таблицу на листе «вспомогательный». В область строк перенесите даты, а в область значений – показатели продаж без НДС и валовой прибыли.

    Измените тип диаграммы: щелкните по графику мышкой и перейдите на вкладку Конструктор -> Изменить тип диаграммы -> выберите График.

    • Щелкните правой кнопкой мышки по серым кнопкам на графике, в появившемся меню выберите «Скрыть все кнопки поля значения на диаграмме».
    • Удалите легенду.
    • Добавьте заголовок: Конструктор -> Добавить элемент диаграммы -> Название диаграммы -> Над диаграммой. Назовите диаграмму «Динамика продаж».

    После выполнения этих шагов должен получиться такой график:

    Выберем для отчета другие цвета вместо ярких синего и оранжевого. Перейдите на вкладку Разметка страницы -> Цвета -> выберите понравившиеся цвета (нам понравились пастельные цвета в настройке «Бумажная»). Здесь же в меню можно настроить корпоративные цвета компании.

    График готов, перенесите его на лист «отчет».

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

    2. Гистограмма продаж по покупателям

    Создаем следующий график. Вставьте сводную диаграмму: выделите отчет с данными из 1С (или любую его ячейку), перейдите в меню Вставка -> Сводная диаграмма. На самом деле, способов добавить сводную диаграмму несколько. Мы с вами рассматриваем самый очевидный – аналогичный добавлению простой (не сводной) диаграммы.

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

    Отформатируйте цифры в таблице — задайте числовой формат (выделить ячейки правой кнопкой мышки -> Числовой формат). Должна получиться такая диаграмма:

    • Щелкните правой кнопкой мышки по любой серой кнопке на диаграмме, выберите в появившемся меню «Скрыть все кнопки полей на диаграмме».
    • Удалите легенду.
    • Чтобы сделать диаграмму нагляднее, представим в ней столбцы в порядке убывания. Отсортируйте сводную таблицу по полю Продажи без НДС: выделите ячейку с столбце «Сумма по полю Продажи без НДС», перейдите в меню Данные -> Сортировка по убыванию. Теперь диаграмма выглядит так:
    • Покажем соотношение валовой прибыли и выручки: выделите правой кнопкой мышки столбец диаграммы с валовой прибылью. В появившемся меню выберите «Формат ряда данных»:

    Задайте параметры ряда – перекрытие столбцов 100%, боковой зазор 40%.

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

    Назовите диаграмму «Продажи по покупателям» и перенесите ее на лист «отчет».

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

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

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

    Соединить текст из разных ячеек

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

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

    Соединение текста экономит скорее время, чем деньги, но при правильном подходе это легко конвертировать

    Подобрать значения для нужного результата

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

    Для этого на вкладке «Данные» надо выбрать «Анализ „Что если“», с помощью функции «Подбор параметра» задать целевое значение и выбрать ячейку, которую нужно изменить для получения желаемой цифры.

    Как следить за бюджетом

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

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

    Обновить курс валют

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

    Чтобы использовать эту функцию, на вкладке «Данные» выберите кнопку «Из интернета» и вставьте адрес надежного источника, например cbr.ru. Эксель предложит выбрать, какую именно таблицу нужно загрузить с сайта — отметьте нужную галочкой.

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

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

    Планировать действия

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

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

    Вот так: =ЕСЛИ (ячейка с ценой акции >= цена выгодной продажи; «продавать»; ЕСЛИ (ячейка с ценой акции Это самый простой пример. Формула «Если» может совмещать данные из разных ячеек и таблиц и быть частью сложных формул, когда при нужном условии происходит умножение или другое действие

    Выделить цветом нужные данные

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

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

    Также можно выделить значения, которые находятся в определенном интервале (в условиях форматирования — «между»), содержат нужный текст («текст содержит»), или задать сразу несколько условий

    Суммировать только нужное

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

    Читать еще:  Расчет размера пособия

    Мы попробуем узнать, сколько Аня тратит на еду в офисе. Для этого в таблице создаем формулу =СУММ((А2:А16=F2)*(B2:B16=F3)*C2:С16) и получаем 915 рублей. Теперь постепенно.

    В первой скобке программа ищет значение из ячейки F2 («Аня») в столбце с именами. Во второй скобке — значение из ячейки F3 («Еда на работе») из столбца с категориями расходов. А после считает сумму ячеек из третьего столбца, которые выполнили эти условия.

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

    Расставить по порядку

    В экселе можно быстро узнать максимальное, минимальное и среднее значение для любого массива ячеек. Для этого в скобках формул =МАКС(), =МИН() и =СРЗНАЧ() нужно указать диапазон ячеек, в которых будет искать программа. Это пригодится для таблицы, в которую вы записываете все расходы: вы увидите, на что потратили больше денег, а на что — меньше. Еще этим тратам можно присвоить «места» — и отдать почетное первое место максимальной или минимальной сумме.

    Например, вы считаете зарплаты сотрудников и хотите узнать, кто заработал больше за определенный срок. Для этого в скобках формулы =РАНГ() через точку с запятой укажите ячейку, порядок которой хотите узнать; все ячейки с числами; 1, если нужен номер по возрастанию, или 0, если нужен номер по убыванию.

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

    Считаем доходность инвестиций в портфеле + готовая Excel таблица с формулами

    Для оценки эффективности вложений нужно обязательно знать, какую доходность они принесут (или принесли). А если таких вложений много? Их нужно как то сравнить. Чтобы понять — что было более выгодным. И вообще, как можно рассчитать доходность портфеля имея различные вложения (вклады в банке, облигации, акции и прочее). На разные суммы и различные сроки?

    Например, что более выгодно? Вложить 57 тысяч на 3 месяца и заработать 3 тысячи. Или инвестировать 75 000 на восемь месяцев и получить 5500?

    Как узнать процент эффективной доходности портфеля, если в течение года было постоянное снятие и внесение средств?

    Считаем прибыль

    Самая простая и базовая формула для определения «выгодности» вложений.

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

    Чтобы вывести в процентном соотношении воспользуйтесь формулой:

    Доходность = (чистая прибыль) / сумму вложения * 100%.

    Пример.

    Купили акции Газпрома на 10 000 рублей. Через год все продали за 13 000 тысяч.

    Чистая прибыль составила 3 тысячи рублей (13 000 — 10 000).

    Доходность вложений 30% (3 000 / 10 000) * 100%).

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

    Мы могли заработать 30% за 1 год. А могли бы и за 5 лет.

    Годовая доходность в процентах

    Более правильно оценить прибыль вложений можно с помощью годовой доходности.

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

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

    Как это выглядит на практике?

    Вложили деньги в акции Сбербанка — 30 000 рублей. И в акции Газпрома — 50 000 рублей

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

    Газпром вы держали ровно год и скинули бумаги за 65 тысяч.

    Итог: На Сбербанке вы заработали за полгода 6 тысяч. На Газпроме 15 тысяч, но за целый год.

    Если считать по общей доходности, то чистая прибыль в процентах составила бы:

    • Сбербанк — 6 тысяч или 20%;
    • Газпром — 15 тысяч или 30%.

    Для правильной оценки эффективности инвестиций нужно все перевести в годовые проценты:

    Доходность (% годовых) = (прибыль в % * 365 дней) / срок инвестиций в днях.

    Доходность Сбербанка = 20% х 365 дня / 180 дней = 40% годовых

    Доходность Газпрома = 30% х 365 / 365 = 30% годовых.

    Более выгодными оказались инвестиции в акции Сбербанка.

    Доходность с учетом движения средств

    А как вывести общий результат, например за год?

    Складывать все доходности не очень удобно и трудоемко.

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

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

    Чистая прибыль 40 000 рублей или 20% годовых.

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

    За рассматриваемый период не учитываются возможные движения средств по счету или портфелю.

    Что это за движения:

    • ввод-вывод средств;
    • получения прибыли «извне». Например, купонный доход по облигациям или дивиденды по акциям.

    Из примера выше. Если за месяц до окончания годового периода инвестор вносил дополнительные 40 тысяч рублей. Как это скажется на результате? В абсолютных цифрах мы также имеем +40 тысяч прибыли или 20% годовых. Но по факту результата ноль.

    Другой вариант. Через 1 месяц инвестор не внес, а снял 40 тысяч. В итоге почти целый год он оперировал суммой на 20% меньше первоначальной. И все равно заработал 40 тысяч прибыли.

    Или в течение года выплачивались дивиденды, купоны. Были постоянное внесение и вывод средств со счета. Как тогда? Как определить реальную доходность?

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

    Расчет доходности в Excel

    Есть более простой вариант расчета процентов в таблице Эксель. Нам поможет формула ЧИСТВНДОХ.

    Все что нам нужно знать — это даты и суммы движений средств.

    Как заполнить таблицу?

    Нам нужны 2 колонки по движению денежных средств:

    1. сумма входящих и выходящих потоков
    2. Даты движений.

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

    Вот как это выглядит на примере:

    Как это сделать в Excel?

    Вносим в таблицу собственные значения (по аналогии с примером выше).

    Вызываем функцию ЧИСТВНДОХ.

    В поля «Значение» и «Даты» вносим наши условия как на картинке ниже. Просто выделяя правой кнопкой мыши необходимый диапазон.

    Саму формулу еще нужно умножить на 100. Дабы привести к более привычному нам виду. По умолчанию показывается не в процентах, в доле от единицы. В нашем случае — 0,16.

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

    За обновлениями в этой и других статьях теперь можно следить на Telegram-канале: @vsedengy.

    Как в Excel просчитать все по новому проекту: от доходов до налогов

    Разработка бизнес-процесса — занятие трудоемкое, требующее времени. А если специалисты еще и вручную проводят расчеты, есть вероятность, что полученные данные будут некорректными. Чтобы этого избежать, можно автоматизировать данный процесс. Это позволит снизить риск ошибочного ввода и предоставления информации. Как это сделать в Excel?

    Читать еще:  Расчет налога на транспорт

    Предлагаем следующий алгоритм разработки и автоматизации бизнес-процесса с помощью MS Excel:

    Рассмотрим эти этапы подробнее.

    Задаем основные параметры проекта

    Предположим, компания «Альфа» планирует построить производственный цех и покрасочное помещение. Устанавливаем для данных объектов начальную проектную мощность, сроки выхода на проектную мощность, годовой темп прироста, выбираем год открытия. Изменяя начальное значение проектной мощности, темпы роста, срок, мощности (старт, темп, срок), получим график выхода на проектную мощность; изменяя год открытия по каждому объекту — календарный план развития данного объекта.

    На примере объекта «Производственный цех» задаем формулы:

    для расчета максимальной проектной мощности:

    где $D4 — год открытия объекта;
    $E4 — начальная проектная мощность;
    $H4 — темп роста;
    $G4 — срок выхода на максимальную проектную мощность.

    для календарного плана развития:

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

    • «Сценарий развития компании»;
    • «Календарный план развития»;
    • «График выхода на проектную мощность, тыс. руб.» (табл. 1).

    Рис. 1. Формула расчета графика выхода на проектную мощность

    Как следует из табл. 1, в 2015 г. планируется ввести в эксплуатацию производственный цех, при этом начальная проектная мощность составит 39 000 тыс. руб., срок выхода на максимальную мощность со значением 119 019 тыс. руб. — 5 лет.

    Составляем доходную часть проекта (лист «Доходы»)

    На листе располагаются следующие таблицы:

    • «Ассортиментная политика»;
    • «Доля, % от V продаж»;
    • «Выручка от реализации с НДС и без НДС, тыс. руб.».

    Компания производит низковольтные комплектные устройства, комплектные распределительные устройства и устройства безопасности. Наибольший удельный вес в структуре продаж занимают низковольтные комплектные устройства — порядка 45 %. Выручка от реализации (с НДС) меняется в зависимости от проектной мощности проекта и своего максимального значения — 63 824 тыс. руб. (141 832 x 45 / 100) — достигнет в 2020 г.

    Лист «Доходы»

    I. Ассортиментная политика

    Номенклатурная группа

    Собственная продукция, %

    Сезонность продаж, мес.

    Низковольтные комплектные устройства

    Комплектные распределительные устройства

    Проектная мощность, тыс. руб.

    II. Доля, % от V продаж

    Номенклатурная группа

    2015

    2016

    2017

    2018

    2019

    2020

    2021

    Итого

    Низковольтные комплектные устройства

    Комплектные распределительные устройства

    III. Выручка от реализации с НДС, тыс. руб.

    Номенклатурная группа

    2015

    2016

    2017

    2018

    2019

    2020

    2021

    Итого

    Низковольтные комплектные устройства

    Комплектные распределительные устройства

    IV. Выручка от реализации без НДС, тыс. руб.

    Номенклатурная группа

    2015

    2016

    2017

    2018

    2019

    2020

    2021

    Итого

    Низковольтные комплектные устройства

    Комплектные распределительные устройства

    Формируем кадровую политику компании (лист «Персонал»)

    На этом листе будут сформированы таблицы:

    • «Кадровая политика»;
    • «Штатное расписание»;
    • «ФОТ, налоги и отчисления, тыс. руб.».

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

    Для удобства расчета присваиваем значениям имена:

    инфляция по заработной плате — Sindex;

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

    где $C4 — среднемесячная заработная плата управленческого персонала (40 000 руб.);

    SIndex — инфляция по заработной плате (1 %);

    C$15 — порядковый номер периода (2015 году присваиваем значение 0);

    $D4 — занятость (12 месяцев);

    C9 — численность управленческого персонала (8 чел.).

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

    Таблица 1. Фонд оплаты труда, налоги и отчисления по категориям персонала

    Страховые взносы + НДФЛ

    ФОТ с отчислениями

    Составляем план расходов по проекту

    На листе «Расходы» создаем четыре таблицы:

    • «Расходы на закупку товарно-материальных ценностей, тыс. руб.»;
    • «Расчет себестоимости реализованной продукции, тыс. руб.»;
    • «Расчет накладных расходов, тыс. руб.»;
    • «Амортизация основных средств (ОС) и нематериальных активов (НА), тыс. руб.».

    Лист «Расходы»

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

    Показатели

    2015

    2016

    2017

    2018

    2019

    2020

    2021

    Итого

    Выручка от реализации, с НДС

    Страховой запас, t1

    Расходы на приобретение сырья, материалов и покупных комплектующих

    Расходы по предоставлению услуг сторонними организациями

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

    Статья

    2015

    2016

    2017

    2018

    2019

    2020

    2021

    Итого

    Выручка от реализации, без НДС

    Сырье и материалы, покупные комплектующие

    Заработная плата производственных рабочих

    Прочие производственные расходы

    IV. Амортизация ОС и НА, тыс. руб.

    Показатель

    2015

    2016

    2017

    2018

    2019

    2020

    2021

    Первоначальная стоимость ОС и НА, t1

    Остаточная стоимость ОС и НА, t1

    Рассмотрим порядок заполнения каждой таблицы подробно.

    В таблице «Расходы на закупку товарно-материальных ценностей» важно рассчитать:

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

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

    Итак, страховой запас и расходы по предоставлению услуг сторонними организациями занимают соответственно 10 и 2 % от выручки. Значит, если выручка за 2015 г. планируется в размере 27 300 тыс. руб., то страховой запас составит 2730 тыс. руб. (27 300 x 10 % / 100 %), а расходы по предоставлению услуг сторонними организациями — 546 тыс. руб.

    Расходы на приобретение сырья, материалов и покупных комплектующих — это 15 % от выручки. Для расчета показателя «Расходы на приобретение сырья, материалов и покупных комплектующих» суммируем выручку от реализации (27 300) и страховой запас (2730), а затем полученное значение умножаем на 0,15 (15 % / 100 %), получаем 4505 тыс. руб.

    Переходим к таблице «Расчет себестоимости реализованной продукции». Здесь все статьи затрат можно представить в укрупненном виде:

    • выручка от реализации без НДС;
    • сырье и материалы;
    • покупные комплектующие;
    • заработная плата производственных рабочих;
    • страховые взносы;
    • прочие производственные расходы.

    Например, сырье и материалы, покупные комплектующие и прочие расходы занимают соответственно 25 и 3 % от выручки, или в денежном выражении за 2015 г. соответственно 5784 (23 136 x 25 % / 100 %) и 694 тыс. руб.

    Рассчитываем накладные расходы (310 % от заработной платы производственных рабочих). В нашем примере накладные расходы за 2015 г. — 14 415 тыс. руб. (4650 x 310 % / 100 %).

    Далее планируем амортизационные отчисления — линейным методом по первоначальной стоимости, которая импортируется в расчет из вкладки «CарEх». Для расчета амортизации вводим следующую формулу:

    где B$25 — первоначальная стоимость ОС и НА (30 900);

    ОС_срок — имя ячейки срока службы оборудования (10 лет).

    Так, за 2015 г. амортизация составляет 3090 тыс. руб.

    Разрабатываем план капитальных расходов (лист «CapEx»)

    Для начала описываем варианты проектного решения. В нашем примере их два:

    • вариант 1 — строительство производственных площадей с полной заменой технологического оборудования;
    • вариант 2 — строительство производственных площадей с частичной заменой технологического оборудования.

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

    Сметный расчет капитальных затрат на строительство производственных площадей

    Ссылка на основную публикацию
    Adblock
    detector