text
Открыть свежий номер журнала>> / Подписка за 22 308 13 990 руб! >> ✆ 8 (800) 550-07-98
Журнал

Как упростить анализ валовой прибыли с помощью Excel

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

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

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

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

Какие данные потребуются для анализа валовой прибыли в Excel

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

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

Как настроить выборку данных в Excel

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

Первым делом для каждого значения аналитики создаем форму. Для этого используем встроенный элемент управления формы «Флажок». На панели Excel переходим во вкладку «Разработчик», нажимаем кнопку «Вставить» и в ниспадающем меню выбираем элемент управления формы «Флажок» (см. рисунок 4).

Далее нужно изменить формат объекта, для этого необходимо выделить его и, щелкнув правой кнопкой мыши, перейти во вкладку «Формат объекта» (см. рисунок 5).

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

Далее нужно установить связи между значениями в каждой форме и ячейками в соседнем столбце (на рисунке 1 это столбец С). Сделать это можно в окне «Форматирование объекта» во вкладке «Связь с ячейкой», как это показано на рисунке 6. Для всех форм последовательно. Так, для формы «Проект №1» установлена связь с ячейкой $C$4, для формы «Проект №2» – с ячейкой $C$6, «Проект №3» – $C$8 и т. д. (см. рисунок 1).

Связывая элементы формы и ячейки в столбце C, в последнем получим перечень логических значений («Истина» или «Ложь», см. рисунок 3). К примеру, если на флажке стоит галочка, то в соответствующей ячейке из столбца C – «ИСТИНА, если нет, то «ЛОЖЬ».

Завершающий этап при формировании блока управления – создание поля числовых значений для элементов формы. Для этого необходимо преобразовать логические значения флажков из столбца С (на рисунке 7) в числовой формат. Сделать это нужно в соседнем столбце D с помощью функции Ч(), см. рисунок 7.

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

После завершения подготовки блока управления выборкой его необходимо связать с таблицами исходных данных. Поле числовых значений в блоке управления (см. на рисунке 1 столбец D) позволяет привязать к нему любые таблицы из тех, что содержат аналитику «Проект» простым умножением данных в поле значений таблицы на именованную метку в блоке управления (см. рисунок 8). В зависимости от целей анализа можно подключить к этому блоку управления все таблицы на листе файла или только те, что отражают совокупный результат.

Например, на рисунке 1 и рисунке 8 показано, как используются метки из блока управления в формулах итоговой таблицы «Анализ рентабельности проектного портфеля». Для того чтобы связать ее с блоком управления, используют функцию СУММЕСЛИ. Формулу с ее участием указывают в каждой ячейке столбцов «Доходы» и «Затраты» (см. рисунок 8). Так, при заполнении ячейки Н23 указывают формулу:
= СУММЕСЛИ($F$5:$F$16;$F23;$H$5:$H$16)*№4, где:
$F$5:$F$16 – диапазон номеров проектов, из которых необходимо выбрать конкретный номер проекта (4);

$F23 – номер проекта, по которому должны быть просуммированы все доходы;

$H$5:$H$16 – диапазон сумм доходов, из которых нужно выбрать суммы по проекту 4;

№4 – привязывает выбранную сумму к блоку управления, в частности, к флажку, где указан «Проект №4».

После ввода аналогичных формул во всех ячейках (см. на рисунке 8 «Анализ рентабельности проектного портфеля») можно приступать к анализу валовой прибыли. Например, на всех флажках, кроме проекта № 4, поставить галочки, тогда в итоговой форме отобразятся расчетные значения из таблиц с исходными данными (см. рисунок 1), а строка по проекту № 4 обнулится. Для удобства столбцы, содержащие значения «ИСТИНА» и «ЛОЖЬ», а также соответствующие им 0 и 1, лучше скрыть.

Рекомендации по теме

Школа

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

Записаться

Самое выгодное предложение

Самое выгодное предложение

Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

Мы в соцсетях
А еще:
×
Чтобы скачать документ, зарегистрируйтесь на сайте!

Это бесплатно и займет меньше минуты!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Зарегистрируйтесь или войдите на сайт:

Вы получите доступ к уникальному контенту, который закрыт для незарегистрированных пользователей!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться
×
Пожалуйста, войдите на сайт

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться
Пожалуйста, войдите на сайт

Зарегистрируйтесь или войдите на сайт:

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться
×

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