Как построить график прибыли в excel

Инвестграм#7. Работа с Excel. Построение графиков доходностей.

Доброго времени суток, коллеги!

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

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

Итак, продолжим… Заполним поля.

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

Колонка В – Месяц, Колонка С – Число, D – Сумма на начало дня, Е – Сумма наконец дня, сразу можете создать колонку % и Комментарий. Обращаю ваше внимание на то, что колонки Начало и Конец дня отображаются оценку портфеля на плановую дату. То есть с учетом нерассчитанных сделок Т+. Эта оценка включает в себя и деньги и активы. Вы можете использовать и свои данные, но такой подход будет более точным.

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

Рассмотрим ситуацию с дозачислением денег. В нашем примере мы зачисляли 50 000 рублей.

Для того, чтобы % считался верно, вам НЕОБХОДИМО в день зачисления прибавить зачисленную сумму к концу предыдущего дня. В нашем случае 101635 (конец 14.08) + 50 000 = 151 635 рублей. Деньги мы зачисляли 15 числа, поэтому на начало 15 числа мы помещаем значение 151 635 на начало дня.

При начислении дохода (дивиденды, купоны и т.д.) лично я закладываю значение начисленной суммы в результат дня. Вы можете поступать иначе. В данном примере нам начислили дивиденды в размере 4 000 рублей, и наш портфель увеличился на 3 000 рублей за день, таким образом, сумма на начало дня 152 550+4 000+3 000=157 550. Для эксперимента или верности расчетов можете закладывать сторонний доход как зачисление денег. Учет будет вестись как с примером 50 000 рублей.

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

Получаем результат

Нахождение точки безубыточности в Excel

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

Назначение точки безубыточности

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

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

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

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

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

Расчет точки безубыточности

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

• Постоянные затраты;
• Переменные затраты на единицу готового товара (шт., партий и т.д.);
• Стоимость продажи единицы товара.

Приступим к выполнению поставленной задачи.

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

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

В третьем столбце показываем суммы переменных затрат, которые считаются путем перемножения количества произведенных единиц товара на 4100 руб (переменные затраты на 1 ед. товара).

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

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

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

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

Построение графика

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

Ось X будет содержать данные по количеству единиц продукции. Ось Y – суммы затрат и доходов.

1. Зажав клавишу Ctrl на клавиатуре поочередно выделяем три диапазона данных, которые нам нужны для построения графика: значения 1, 4 и 5 столбцов (вместе с шапками).
2. Переключаемся во вкладку “Вставка”, находим группу инструментов “Диаграммы” и жмем кнопку “Вставить точечную или пузырьковую диаграмму”.
3. В открывшемся перечне разновидностей графиков выбираем вариант “Точечная с гладкими кривыми и маркерами”.
4. График был автоматически сформирован и вставлен на лист.
5. Осталось внести финальные правки:
6. Дважды щелкнув по названию графика можно отредактировать его.
7. Щелкаем по любой цифре оси X, после чего вокруг шкалы должна появиться рамка. Затем кликом правой кнопкой мыши по этой же цифре вызываем контекстное меню и выбираем в нем пункт “Формат оси” (или можно просто дважды щелкнуть по любой цифре шкалы).
8. В открывшемся вспомогательном окне “Формат оси” указываем основное деление, равное цифре 1 (шаг роста объема производства), после чего закрываем окно.
9. При желании аналогичным образом меняется шкала оси Y.
10. Для большей наглядности график можно слегка растянуть, потянув за любой из его углов. При этом нужно зажать клавишу Shift, если требуется сохранить пропорции.
11. Также, с помощью зажатой левой кнопки мыши можно переместить график в любое удобное место на листе, чтобы он не перекрывал таблицу. Тянуть нужно за любые белые участки вокруг графика.

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

Заключение

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