Как построить балансограмму в экселе
Сбалансировать финансовые показатели, найти оптимальные значения коэффициентов, упростить подбор параметров и пропорций при составлении бюджета — все это можно сделать в Excel с помощью полосы прокрутки (ScrollBar), одного из элементов ActiveX. Это удобно, когда руководство требует предоставить в наглядной форме информацию, например, о том, как при повышении цены на выпускаемую продукцию изменится рентабельность продаж. Как это сделать, мы расскажем в данной статье.
Для начала разрабатываем финансовую модель, то есть выбираем влияющие показатели, определяем их предельные значения, устанавливаем для каждого показателя специальный элемент управления — полосу прокрутки. В качестве примера рассмотрим модель точки безубыточности. Напомним, что объем продаж в этой точке в натуральном выражении (Q, шт.) определяется по формуле:
где Зпост — постоянные затраты, руб.;
Ц — цена реализации единицы продукции, руб./шт.;
С — переменные расходы на единицу продукции (себестоимость), руб./шт.
Также в модели будут задействованы еще два показателя: прибыль (П) и рентабельность продаж (Р):
Предельные значения перечисленных показателей компании «Альфа», выпускающей электротехническое оборудование, представлены в табл. 1 (напоминаем, что все расчеты ведутся в Excel).
Таблица 1. Предельные значения показателей компании «Альфа»
Показатель
Предельное значение
минимальное
максимальное
Цена реализации (Ц), руб.
Себестоимость единицы продукции (С), руб./шт.
Постоянные расходы (Зпост), руб.
Размещаем для каждого показателя полосу прокрутки. На вкладке «Разработчик» в группе «Элементы управления» нажимаем кнопку «Вставить», далее в раскрывшемся списке в разделе «Элементы ActiveX» кликаем по значку полосы прокрутки и устанавливаем в любое удобное место рабочего листа (рис. 1).
Рис. 1. Полосы прокрутки показателей
Так, для показателя «Себестоимость единицы продукции» в контекстном меню выбираем «Свойства» и далее в строках Min и Max открывшегося окна Properties указываем соответствующие цифры — 5 000 000 и 9 000 000. В строке LinkedCell записываем ячейку В19: там будет отображаться значение 5 150 345, установленное в строке Value (рис. 2).
Рис. 2. Свойства полосы прокрутки, характеризующей показатель «Себестоимость единицы продукции»
Далее переходим к автоматизированному управлению ограничениями. Для этого стоит создать макрос и кнопку (ее можно назвать, например, «Установка ограничений»). Каждому максимальному и минимальному значению присваиваем имя. Так, показателю «Цена» в ячейках С16 и Н16 присваиваем соответственно имена Цmin и Цmax. Затем создаем саму кнопку. Для этого переходим на вкладку «Разработчик» и выбираем один из элементов ActiveX — значок, который обозначает кнопку (CommandButton). Далее в контекстном меню выбираем «Свойства» и в открывшемся окне Properties в строке Caption присваиваем кнопке имя «Установка ограничений» (рис. 3).
Рис. 3. Свойства для кнопки «Установка ограничений»
Затем для кнопки создаем макрос: заходим в редактор Visual Basic и задаем правила для автоматического изменения полос прокрутки:
Private Sub CommandButton1_Click()ScrollBar1.Min = Range("Цmin")
ScrollBar1.Max = Range("Цmax")
ScrollBar2.Min = Range("Cmin")
ScrollBar2.Max = Range("Cmax")
ScrollBar3.Min = Range("Зпостmin")
ScrollBar3.Max = Range("Зпостmax")
ScrollBar4.Min = Range("dQmin")
ScrollBar4.Max = Range("dQmax")
End Sub
Поясним, что ScrollBar1, ScrollBar2, ScrollBar3, ScrollBar4 — это порядковые номера и коды соответствующих полос;
Цmin и Цmax, Cmin и Cmax, Зпостmin и Зпостmax, dQmin и dQmax — заданные имена ячеек с минимальным и максимальным значениями.
Текст кода повторяется, меняются только порядковые номера элементов (отражаются в окне «Имя» слева от строки формул при выборе объекта в режиме конструктора) и адреса ячеек. После введения новых минимальных или максимальных значений исходных параметров остается только нажать кнопку, и диапазон значений на полосах прокрутки автоматически поменяется.Далее переходим к построению модели определения точки безубыточности, которая состоит из таблицы и графика.
Таблица состоит из восьми столбцов:
Начнем заполнять таблицу. Сначала задаем объем продаж в точке безубыточности. В Excel формула будет такой:
где B22 — постоянные затраты, руб.;
B16 — цена реализации, руб./шт.;
B19 — удельные переменные затраты, руб./шт. (все значения заданы с помощью полос прокрутки).
В ячейке, соответствующей точке безубыточности (A8), значение должно быть равно 0. В диапазон A4:A7 вводим формулу:
где j — номер строки последующей ячейки.
Для диапазона A10:A12 формула аналогична, только после A[j] ставится минус, а сама переменная j обозначает номер строки предыдущей ячейки. Данные в других графах таблицы рассчитываются по приведенным в заголовках формулам (рис. 4).
Рис. 4. Формулы для модели точки безубыточности
В нашем примере прибыль будет равна 0 при условии, что объем продаж составит 4 шт., переменные затраты на единицу выпускаемой продукции — 5 150 345 руб./шт., а постоянные затраты — 27 849 899 руб. (табл. 2). Если же отклонение от точки безубыточности составляет 35 %, например, это значит, что компания выпустит продукцию в количестве 6 шт., при этом выручка составит 66 717 580 руб., а рентабельность продаж — 15 %.
Таблица 2. Анализ точки безубыточности продукции, выпускаемой компанией «Альфа»
Построение диаграммы в Microsoft Excel по таблице – основной вариант создания графиков и диаграмм другого типа, поскольку изначально у пользователя имеется диапазон данных, который и нужно заключить в такой тип визуального представления.
В Excel составить диаграмму по таблице можно двумя разными методами, о чем я и хочу рассказать в этой статье.
Способ 1: Выбор таблицы для диаграммы
Откройте необходимую таблицу и выделите ее, зажав левую кнопку мыши и проведя до завершения.
Вы должны увидеть, что все ячейки помечены серым цветом, значит, можно переходить на вкладку «Вставка».
Там нас интересует блок «Диаграммы», в котором можно выбрать одну из диаграмм или перейти в окно с рекомендуемыми.
Откройте вкладку «Все диаграммы» и отыщите среди типов ту, которая устраивает вас.
Справа отображаются виды выбранного типа графика, а при наведении курсора появляется увеличенный размер диаграммы. Дважды кликните по ней, чтобы добавить в таблицу.
Предыдущие действия позволили вставить диаграмму в Excel, после чего ее можно переместить по листку или изменить размер.
Дважды нажмите по названию графика, чтобы изменить его, поскольку установленное по умолчанию значение подходит далеко не всегда.
Не забывайте о том, что дополнительные опции отображаются после клика правой кнопкой мыши по графику. Так вы можете изменить шрифт, добавить данные или вырезать объект из листа.
Для определенных типов графиков доступно изменение стилей, что отобразится на вкладке «Конструктор» сразу после добавления объекта в таблицу.
Как видно, нет ничего сложного в том, чтобы сделать диаграмму по таблице, заранее выбрав ее на листе. В этом случае важно, чтобы все значения были указаны правильно и выбранный тип графика отображался корректно. В остальном же никаких трудностей при построении возникнуть не должно.
Способ 2: Ручной ввод данных
Преимущество этого типа построения диаграммы в Экселе заключается в том, что благодаря выполненным действиям вы поймете, как можно в любой момент расширить график или перенести в него совершенно другую таблицу. Суть метода заключается в том, что сначала составляется произвольная диаграмма, а после в нее вводятся необходимые значения. Пригодится такой подход тогда, когда уже сейчас нужно составить график на листе, а таблица со временем расширится или вовсе изменит свой формат.
На листе выберите любую свободную ячейку, перейдите на вкладку «Вставка» и откройте окно со всеми диаграммами.
В нем отыщите подходящую так, как это было продемонстрировано в предыдущем методе, после чего вставьте на лист и нажмите правой кнопкой мыши в любом месте текущего значения.
Из появившегося контекстного меню выберите пункт «Выбрать данные».
Задайте диапазон данных для диаграммы, указав необходимую таблицу. Вы можете вручную заполнить формулу с ячейками или кликнуть по значку со стрелкой, чтобы выбрать значения на листе.
В блоках «Элементы легенды (ряды)» и «Подписи горизонтальной оси (категории)» вы самостоятельно решаете, какие столбы с данными будут отображаться и как они подписаны. При помощи находящихся там кнопок можно изменять содержимое, добавляя или удаляя ряды и категории.
Обратите внимание на то, что пока активно окно «Выбор источника данных», захватываемые значения таблицы подсвечены на листе пунктиром, что позволит не потеряться.
По завершении редактирования вы увидите готовую диаграмму, которую можно изменить точно таким же образом, как это было сделано ранее.
Вам остается только понять, как сделать диаграмму в Excel по таблице проще или удобнее конкретно в вашем случае. Два представленных метода подойдут в совершенно разных ситуациях и в любом случае окажутся полезными, если вы часто взаимодействуете с графиками во время составления электронных таблиц. Следуйте приведенным инструкциям, и все обязательно получится!
) полученные удельные значения выносятся на балансограмму.
Балансограмма состоит из шести столбцов, в каждом из которых фиксируется соответствующая структура: в первом столбце - структура разделов актива баланса, во втором - расшифровка разделов актива баланса, в третьем - расшифровка подраздела «Запасы», в четвертом - структура выручки от продаж (взвешенная в валюте баланса), в пятой - расшифровка краткосрочных обязательств предприятия, в шестой - структура пассива баланса. Последовательность статей в каждом столбце приведена в табл. 7.
Пример построения балансограммы для условного предприятия приведен на рис.13. Вертикальная шкала балансограммы представляет удельные значения статей баланса и отчета о прибылях и убытках нарастающим итогом.
Рис. 13. Балансограмма условного предприятия
Применения балансограмм для анализа финансового состояния условного предприятия рассмотрим также на примере определения финансовой устойчивости. Для этого проведем горизонтальную прямую через всю балансограмму на начало периода на уровне суммарной доли внеоборотных активов, запасов и НДС. В пассивах оценим, за счет каких источников формируются основные и оборотные производственные активы. В нашем случае прямая пересекает пассивы на уровне краткосрочных займов и кредитов, что соответствует рис. 8, характеризующему неустойчивое финансовое состояние. Проведя линию через балансограмму на уровне суммарной доли внеоборотных активов и непродаваемой части запасов, получим ситуацию, представленную на рис. 10.9, - предприятие на начало периода находится в допустимо неустойчивом финансовом состоянии. На конец периода предприятие находится в кризисном финансовом состоянии, так как горизонтальная прямая пересекает пассивы на уровне кредиторской задолженности.
Приведенные примеры позволяют говорить о взаимозаменяемости табличного метода и метода балансограмм.
В ходе проведения финансового анализа вскрываются не только недостатки в финансовой деятельности предприятия, но и имеющиеся резервы. С помощью финансового анализа устанавливается эффективность запланированных мер на стадии проектирования, реализации и подведения итогов выполнения финансовых планов.
В Excel частота - это встроенная функция, которая подпадает под статистическую категорию. Распределение частоты может быть определено как список данных или графика, который дает частоту различных результатов. В Excel это встроенная функция, которая часто используется для получения данных о различных результатах потока и затем возвращает вертикальный массив числа, имеющего еще один элемент, т.е. функция частоты обычно возвращает значение массива, которое должно быть обновлено как формула массива на основе данные.
Формула частоты в Excel
Ниже приведена формула частоты в Excel:
Функция частоты имеет два аргумента:
- Массив данных: набор значений массива, в котором он используется для подсчета частот. Если значения массива данных равны нулю (то есть значения NULL), то функция частоты в Excel возвращает массив нулевых значений.
- Массив бинов: набор значений массива, который используется для группировки значений в массиве данных. Если значения массива bin равны нулю (т. Е. Значения NULL), он возвращает количество элементов массива из массива данных.
Как сделать распределение частот в Excel?
Распределение частот в Excel очень простое и удобное в использовании. Давайте разберемся с работой распределения частот Excel на некотором примере.
Вы можете скачать этот шаблон Excel для распределения частот здесь - Шаблон Excel для распределения частот
В Excel мы можем найти «частотную функцию» в меню формул, которая подпадает под статистическую категорию, выполнив следующие шаги следующим образом.
- Нажмите на Дополнительные функции .
- В категории « Статистика » выберите « Функция частоты», как показано на скриншоте ниже.
- Мы получим диалоговое окно Frequency Function, как показано ниже.
Где массив данных - это массив или набор значений, где мы хотим подсчитать частоты, а Bins_array - это массив или набор значений, где мы хотим сгруппировать значения в массиве данных.
Пример № 1
В этом примере мы увидим, как найти частоту с помощью доступной базы данных студентов.
Давайте рассмотрим приведенный ниже пример, который показывает оценку студентов, которая показана ниже.
Теперь, чтобы вычислить частоту, мы должны сгруппировать данные с оценками учащихся, как показано ниже.
Теперь с помощью функции частоты мы сгруппируем данные, выполнив следующие шаги.
- Создайте новый столбец с именем Частота.
- Используйте частотную формулировку в столбце G, выбрав от G3 до G9.
- Здесь нам нужно выбрать весь столбец частоты, тогда только функция частоты будет работать правильно, иначе мы получим значение ошибки.
- Как показано на скриншоте выше, мы выбрали столбец в качестве массива данных и массив бинов в качестве меток ученика = FREQUENCY (F3: F9, C3: C22) и перейдем к сочетаниям клавиш CTRL + SHIFT + ENTER .
- Так что мы получим значения во всем столбце.
- Как только мы нажмем CTRL + SHIFT + ENTER, мы увидим открывающую и закрывающую скобки, как показано ниже.
Теперь, используя распределение частот Excel, мы сгруппировали оценки учеников по меткам, которые показывают, что ученики набрали баллы: 0-10, у нас 1 студент, 20-25, у нас 1 студент, 50-55, у нас 1 студент, 95-100. 1 студент, как показано ниже.
Пример № 2
Распределение частот Excel с использованием сводной таблицы
В этом примере мы увидим, как добиться превосходного распределения частоты, используя графические данные с доступной базой данных продаж.
Один из самых простых способов сделать распределение частоты Excel - это использовать сводную таблицу, чтобы мы могли создавать графические данные.
Рассмотрите ниже данные о продажах, которые имеют год распродажи. Теперь мы увидим, как использовать это с помощью сводной таблицы, выполнив следующие шаги.
- Создайте сводную таблицу для вышеуказанных данных о продажах. Для создания сводной таблицы нам нужно перейти в меню вставки и выбрать сводную таблицу.
- Перетащите вниз Продажи в метках строк. Перетащите вниз те же продажи в ценности.
- Убедитесь, что мы выбрали параметр поля поворота для подсчета, чтобы получить числа подсчета продаж, показанные ниже.
- Нажмите на номер продажи ярлыка строки и щелкните правой кнопкой мыши, затем выберите вариант группы.
- Так что мы получим диалоговое окно группировки, как показано ниже:
- Измените номера группировки, начиная с 5000 и заканчивая 18000, и сгруппируйте их по 1000, а затем нажмите кнопку ОК.
- После этого мы получим следующий результат, где данные о продажах были сгруппированы по 1000, как показано ниже:
Мы видим, что данные о продажах были сгруппированы по 1000 со значениями от минимума до максимума, которые могут быть показаны более профессионально при отображении в графическом формате.
- Зайдите в меню вставки и выберите столбчатую диаграмму.
Пример № 3
Распределение частот Excel с использованием гистограммы
Используя сводную таблицу, мы сгруппировали данные о продажах, теперь мы увидим, как сделать исторические данные о продажах с помощью распределения частоты в Excel.
Рассмотрим приведенные ниже данные о продажах для создания гистограммы с именем продавца с соответствующими значениями продаж. Где CP - это не что иное, как Consumer Pack, а Tins - это значения диапазона, то есть, сколько банок было распродано конкретным продавцам.
Мы можем найти гистограмму в группе анализа данных в меню данных, которое представляет собой не что иное, как надстройки. Мы увидим, как применить гистограмму, выполнив следующие шаги.
- Зайдите в меню данных справа вверху, мы можем найти анализ данных. Нажмите на анализ данных, который выделен, как показано ниже.
- Так что мы получим диалоговое окно ниже. Выберите опцию «Гистограмма» и нажмите «ОК».
- Укажите входной диапазон и диапазон бункера, как показано ниже.
- Убедитесь, что у нас есть галочка для всех опций, таких как метка, совокупный процент, выходной график, а затем нажмите кнопку ОК.
- На графике ниже мы получили вывод, который показывает совокупный процент вместе с частотой.
Мы можем отобразить вышеупомянутую гистограмму более профессионально, отредактировав данные о продажах следующим образом.
- Щелкните правой кнопкой мыши по гистограмме и выберите «Выбор данных».
- Мы получим диалоговое окно для изменения диапазонов. Нажмите на редактировать.
- Так что мы можем редактировать диапазоны, которые нам нужно дать. Измените значение Bins, что нам нужно, чтобы указать диапазон, чтобы мы получили соответствующий результат, а затем нажмите кнопку ОК.
- Таким образом, результат будет таким, как показано ниже.
Что нужно помнить о распределении частот в Excel
- В Excel Распределение частот при группировании мы можем потерять некоторые данные, поэтому убедитесь, что мы группируем должным образом.
- При использовании частотного распределения Excel убедитесь, что классы должны быть одинакового размера с верхним и нижним предельными значениями.
Рекомендуемые статьи
Это было руководство по распределению частот в Excel. Здесь мы обсуждаем формулу частоты в Excel и как сделать распределение частоты в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
Читайте также: