Скидка на подписку 30% | 8(800)550-07-98
Журнал

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

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

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

Какие данные потребуются для анализа валовой прибыли в 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

Блок управления выборкой готов, его нужно связать с таблицами исходных данных. Поле числовых значений в блоке управления (см. столбец D на рисунке 1) позволяет привязать к нему любые таблицы из тех, что содержат аналитику «Проект», простым умножением данных в поле значений таблицы на именованную метку в блоке управления (см. рисунок 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».

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

Школа

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

Записаться

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

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

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

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

"Финансовый директор" - единственный профессиональный ресурс по управлению финансами компании. Материалы подготовлены финансовыми директорами и экспертами. Пройдите короткую регистрацию и получите доступ

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

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

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

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

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

"Финансовый директор" - профессиональный ресурс по управлению финансами компании. Авторские материалы подготовлены финансовыми директорами и экспертами находятся в закрытом доступе. Зарегистрируйтесь или войдите через соцсеть, чтобы прочитать эту статью бесплатно

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

Мы подобрали для вас книги:

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