Деятельность любой организации связана с движением денежных средств, и данные операционного учета являются одной из самых важных составляющей точности планирования деятельности. По сути не так важно каким образом осуществляется регистрация первичных данных - это может быть CRM, ERP система или просто таблица в Экселе.
Создание управленческой таблицы
ПРОЕКТ ОТ РАЗРАБОТЧИКОВ HELPEXCEL
Деятельность любой организации связана с движением денежных средств, и данные операционного учета являются одной из самых важных составляющей точности планирования деятельности. По сути не так важно каким образом осуществляется регистрация первичных данных - это может быть CRM, ERP система или просто таблица в Экселе.
Рассмотрим очень простую, но правильную структуру организации учета. Предположим, что у нас несколько источников доходов - могут быть как отдельные виды деятельности, так и филиалы одной сети.
Инструментом ведения учета в рамках примера будут Гугл Таблицы.
Ввод данных
1. Создаем таблицу, в которую будут записываться основные данные:
Таблица выглядит как реестр операций. В нее будут записываться все операции прихода и расхода на каждом из объектов деятельности. Каждую операцию будем относить к наличному или безналичному расчету, поэтому для удобства анализа все операции группируем по статьям бюджета. Необходимо выделить основные направления движения денежных средств организации.
2. Сделаем ввод данных максимально удобным и стандартизированным (это очень важно!)
Дату можно вводить выбором из выпадающего календаря. Для этого выделяем столбец с датой, выбираем пункт меню «Данные» на панели управления и в выпадающем меню выбираем пункт «Проверка данных»
В поле выбора правил проверки выбираем значение «Дата» из выпадающего списка:
Нажимаем кнопку «Сохранить» . Теперь двойным мыши нажатием в столбце ввода даты появляется календарь:
Организуем выбор объекта из выпадающего списка. Для этого есть два варианта:
Вариант 1: Выделяем столбец «Объект», выбираем пункт меню «Данные», «Проверка данных». В поле выбора правил выбираем «Значение из списка» и вписываем через запятую наименования объектов
Получаем выпадающий список с введенными значениями:
Вариант 2: Список объектов хранится в таблице на любом из листов. В примере это будет лист «Справочник»
Выделяем столбец «Объект» на листе «Данные». Переходим в пункт меню «Данные», выбираем «Проверка данных» и в поле правила выбираем «Значение из диапазона» .
Далее указываем диапазон, содержащий нужные нам значения наименований объектов:
Далее создаем аналогичным образом выпадающий список для столбца «Тип операции» и «Тип платежа». В рамках примера предполагаются операции прихода и расхода, а платежи наличные и безналичные.
По очереди выделяем столбцы, выбираем «Проверка данных» в пункте меню «Данные», выбираем правило «Значение из списка» и вводим нужные значения через запятую. Получаем выпадающие списки:
Статьи бюджета удобнее вывести в справочник. Там их будет удобнее редактировать и дополнять. Выделяем столбец «Статьи бюджета». Переходим в меню «Данные», «Проверка данных». Выбираем «Значения из диапазона» и указываем диапазон.
Теперь осталось только привести в порядок таблицу, чтобы она имела презентабельный вид. Сперва нужно удалить проверку с заголовка таблицы. Выделяем всю строку заголовка, выбираем пункт меню «Данные», «Проверка данных» и нажимаем «Удалить проверку» в появившемся диалоговом окне.
Далее задаем внешний вид:
Ввод данных и разграничение прав пользователей
Теперь в таблицу можно вводить данные. Гугл таблицы удобны тем, что ими может пользоваться одновременно почти неограниченное число пользователей, для которых администратор распределяет права доступа. В правом верхнем углу располагается пункт меню «Настройки доступа».
Пользователь вводит логин аккаунта Gmail и определяет права:
В расширенных настройках можно определить полномочия делегируемых прав
Можно включить доступ по ссылке, который в свою очередь можно предоставить с правом редактирования и без редактирования
Просмотр истории изменений и откат таблицы
В верхней части таблицы отображается информация о времени последнего изменения в таблице и аккаунте, который их произвел.
В Гугл таблицах предусмотрена возможность просмотра истории изменений. Область просмотра истории активируется нажатием на строку с записью о статусе сохранения документа.
Перед пользователем открывается таблица, в которой цветом выделены изменяемые области, а в окне справа отображены время изменения и пользователь, который внес редакцию.
Анализ данных и отчетность
Рассмотрим распространенный вариант анализа данных операционной деятельности компании. Сформируем таблицу по основным статьям бюджета в разрезе каждого месяца по каждому объекту в отдельности:
Верхняя таблица содержит суммарные результаты всех нижних таблиц, в которых должна отображаться деятельность каждого из объектов.
Для каждого объекта будем подтягивать данные из листа «Данные» формулой «СУММЕСЛИМН». Функция СУММЕСЛИМН суммирует ячейки, удовлетворяющие заданному набору условий. Вид функции: =СУММЕСЛИМН(Диапазон суммирования; Диапазон условия 1; Условие 1; Диапазон условия 2; Условие 2;…)
Поехали.
Сперва добавим на лист "Данные" столбец с названием «Месяц» и выведем формулой порядковый номер месяца из значения даты. Встаем курсором в ячейку H2 и вводим простую формулу: =МЕСЯЦ(A2). Протягиваем ее до конца таблицы:
Далее приступаем к самому главному. Перемещаемся на лист отчета.
Встаем в ячейку D10 и вводим в строке формул «=СУММЕСЛИМН(…»
Выделяем на листе «Данные» столбец F с суммами и ставим точку с запятой «;» и фиксируем перемещение по столбцам знаком $
=СУММЕСЛИМН( ' Данные ' ! $F : $F;
Далее выделяем столбец с критериями для суммирования Е – статьи бюджета и ставим точку с запятой «;» и фиксируем перемещение по столбцам знаком $
Далее указываем второй столбец с критериями – столбец В с наименованиями объектов и ставим точку с запятой «;» и фиксируем перемещение по столбцам знаком $
=СУММЕСЛИМН( ' Данные ' ! $F : $F; ' Данные ' ! $E : $E;$C10; ' Данные ' ! $B : $B;
И указываем наименование объекта в ячейке В10 в качестве критерия и фиксируем перемещение по столбцам и строкам знаком $
=СУММЕСЛИМН( ' Данные ' ! $F : $F ; ' Данные ' ! $E : $E;$C10; ' Данные ' ! $B : $B; $B$10;
Далее добавляем условие отбора данных по месяцам. В качестве критериев отбора выделяем столбец H и фиксируем перемещение по столбцам, а в качестве условия выбираем ячейку D2 заголовка таблицы отчета и фиксируем перемещение по строкам.
Получаем формулу: =СУММЕСЛИМН( ' Данные ' ! $F : $F ; ' Данные ' ! $E : $E;$C10; ' Данные ' ! $B : $B;$B$10; ' Данные ' ! $H : $H;D$2)
Протягиваем формулу на все статьи бюджета и каждый месяц в таблице объекта 1
=СРЗНАЧ(D10:O10) Аналогичным образом считаем итоги, используя функцию «СУММ». Аргументом формулы является набор или массив данных.
Получаем заполненную расчетами таблицу:
Данные в таблице имеют разный формат. Приведем все к одному виду, оставив в таблице только целые числа. Выделяем всю область значений таблицы, переходим в пункт меню «Другие форматы» и выбираем «Число»
Нажимаем два раза на кнопку «Уменьшить число знаков после запятой»
Получаем отчет по объекту 1:
Производим аналогичные вычисления для других объектов. Получаем отчет следующего вида:
Считаем сумму, складывая данные бюджета каждого объекта:
Получаем отчет по всем объектам деятельности в разрезе статей бюджета на каждый месяц года:
Подводя итоги
В данном кейсе рассмотрена укрупненная схема организации управленческого учета компании. Информация на листе «Данные» может представлять собой данные в системе учета или данные которые формируются вручную из разных источников. Лист отчет представляет собой отчет о прибылях и убытках. Отчет можно отнести к данным для первичного анализа. По разбросу его значений можно выявить положительные или отрицательные факторы и при необходимости искать причины в других источниках информации.
В отчет можно добавить сравнение с аналогичными данными прошедших периодов, сравнение с плановыми значениями, а так же расчеты таких показателей как рентабельность, валовая прибыль и т. д.
Хотите самостоятельно разбираться в Гугл таблицах?
Быстро. Грамотно. Полезно. Пройдите новый усовершенствованный курс по Гугл таблицам от наших разработчиков