В начало

Приложение. Тексты программ пользовательских функций

Глава 8. Бухгалтерия на Excel

В предыдущей главе были рассмотрены общие принципы построения системы автоматизированного бухгалтерского учета на основе программы электронных таблиц Excel, а также продемонстрирован простейший вариант такой системы. Он может быть использован для ведения дел только что созданного предприятия, деятельность которого еще находится в зачаточном состоянии. Однако предприятие развивается, приходится делать следующий шаг в бухгалтерском учете - привести простейшую бухгалтерию к нормальному варианту, рассчитанному на устойчивое серьезное малое предприятие. Этой теме посвящена данная глава. Вспомним, что говорилось в главе 4 о двух подходах к ведению бухгалтерского учета: "базе данных" ("от сложного к простому") и "таблице" ("от простого к сложному"). В основе первого лежит единая база данных. Журнал хозяйственных операций, все учетные регистры, отчетные формы и другие документы являются частными выборками из этой базы, обработанными соответствующим образом. Во втором способе учет ведется в отдельных таблицах, аналитических учетных регистрах, результирующая информация которых объединяется в главный учетный регистр.

Первый подход реализуют системы управления базами данных (СУБД), на основе которых создаются специализированные бухгалтерские программы. Второй применялся в неавтоматизированном бухучете и взят авторами за основу при создании бухучета на Excel уже из других соображений. Как и журнально-ордерная, система бухучета на Excel состоит из учетных регистров двух уровней. Впрочем, уровней может быть и больше, но мы не видим в них необходимости, - во всяком случае, для малого предприятия. Первый уровень - это регистры аналитического учета, второй - главный регистр, именуемый по традиции Главная книга, хотя внешне с традиционной главной книгой журнально-ордерной системы у него мало общего. Кроме того, используются специальные структуры (книги или страницы Excel) для генерации документов (платежных поручений, счетов-фактур, отчетов в налоговую инспекцию и т. д.). В отдельных случаях учетные регистры и формы генерации документов могут быть объединены.

Распределение информации по книгам и страницам Excel выбрано исходя из требований компактности расположения, удобства анализа и генерации отчетности в налоговую инспекцию и другие контрольные органы. На последнее следует обратить особое вынимание, поскольку это один из наиболее ответственных и сложных моментов бухгалтерской деятельности, прежде всего для малого предприятия. Налоговые и отчетные периоды по разным налогам не совпадают друг с другом и между собой и имеют диапазон от месяца до года. Поэтому мы стремились создать учетные формы, содержавшие компактную информацию разных налоговых периодов. В этом, кстати, заключается одно из главных преимуществ использования электронных таблиц для этих целей. Бухгалтерская программа предлагает жесткие учетные формы - частично свои, частично традиционные. А в электронных таблицах можно действовать по своему усмотрению. И хотя далее мы покажем свои учетные формы, они не являются догмой и могут быть переделаны читателем на свой вкус, если он сочтет это необходимым.

Регистры аналитического учета

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

Рассматриваемая в книге система предусматривает следующие виды учетных регистров:

• учета денежных средств;
• учета материальных ценностей;
• учета амортизируемого имущества;
• учета расчетов с партнерами;
• учета зарплаты;
• прочих операций.

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

Ведомость учета банковских операций

Структура данного регистра именно та, что была описана в предыдущем разделе: три листа с информацией (месяц1 – месяц3) и лист с суммарной информацией - Результаты. Эти названия имеют стандартный характер и являются общими для всех регистров аналитического учета.

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

На рис. 8.1 представлен фрагмент листа месяц1 книги Банк1 (ведомость учета банковских операций за 1 квартал 2003 года), на котором мы видим названия полей и сальдо на начало квартала.

Рис. 8.1. Лист Ведомость учета банковских операций (начало)

Дата начала квартала генерируется автоматически по номеру месяца даты первой операции по формуле:

=ДАТА(ГОД(В4);МЕСЯЦ(В4);1) [1]

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

="Ведомость учёта банковских операций за "&ВЫБОР(МЕСЯЦ(В3);"январь ";"февраль ";"март ";"апрель ";"май ";"июнь ";"июль ";"август ";"сентябрь ";"октябрь ";"ноябрь ";"декабрь ")&ГОД(В3)&" года" [2]

Теперь несколько слов об использованных функциях и приведенных формулах.

В первую очередь разберемся с датами. Не следует путать понятия даты как формы представления числа и даты как класса функций. Вначале о первом. Excel обеспечивает возможность представления чисел в формате даты и времени. Для целей финансов и бизнеса, в том числе бухгалтерского учета, нас интересует только дата. Excel оперирует с датами в интервале от 1 января 1900 года до 31 декабря 9999 года. Дата для электронных таблиц - то же число, которое только по-другому выглядит. Занеся в ячейку дату, мы на самом деле занесли туда число, равное количеству дней, отделяющих эту дату от 1 января 1900 года. Например, 2 марта 2003 года - это число 37682. Это легко проверить, если назначить данной ячейке числовой формат.

В Excel есть несколько форматов отображения даты: 20.03.2003, 20.03.03 и др. Их можно увидеть, выполнив команду Формат > Ячейки > Число > Дата.

Функция ДАТА

Данная функция имеет синтаксис ДАТА(ГОД;МЕСЯЦ;ДЕНЬ) и возвращает число в формате даты. Аргументами функции ДАТА, как, впрочем, и любой функции числовых аргументов, могут быть числа, ссылки на ячейки, содержащие числовую информацию, функции, возвращающие числовые значения, - в общем, все, что в конечном итоге является числом.

В формуле [1] мы применяем функцию ДАТА для формирования даты начала учетного периода. В качестве аргументов используются год и месяц даты первой банковской операции и первое число.

Дата начала следующего отчетного периода формируется аналогично в ячейке В15 (рис. 8.2).

Если функция ДАТА() вводится вручную, последовательность ввода компонентов должна быть обратной той, что вводится при обычном наборе даты и отображается в рабочей ячейке.

Функции ГОД(ДАТА), МЕСЯЦ(ДАТА), ДЕНЬ(ДАТА)

Данные функции выполняют задачу выделения из даты отдельных компонентов. В частности, эти функции могут выступать в качестве аргументов функции ДАТА(), что и было использовано в ячейке В3 (дата начала периода) и В15 (дата начала следующего периода). Там в качестве второго аргумента служило выражение МЕСЯЦ(В4)+1.

Подобные варианты применения функций даты достаточно типичны для бухгалтерского учета.

Функция ВЫБОР

В формуле 2 одна из рассмотренных функций, а именно МЕСЯЦ(В4), служит в качестве аргумента функции ВЫБОР(индекс;значение1;значение2;...). Она соответствует своему названию - по номеру индекса выбирает значение, которое ему соответствует, то есть индексу, равному 1, соответствует значение1, а 2 - значение2 и т. д. В формуле [2] в качестве индекса использован номер месяца, а в качестве значений - названия месяцев. Таким образом, с помощью данной функции преобразуется номер месяца в его название.

Остальная часть формулы 2 формирует основной текст названия. С текстовыми строками можно производить только одно действие - "сцеплять" их между собой, используя знак конкатенации &. При этом в качестве текстовых строк можно применить и числа без их преобразования в текстовый формат. Это выполняется автоматически.

Значение начального остатка в 1-м квартале, ячейка 13, вносится вручную, во всех последующих - как ссылка¹ на конечное сальдо предыдущего квартала. На рис. 8.2 представлен фрагмент нижней части таблицы. Здесь показаны последние операции месяца и итоговые суммы.

Текущее сальдо формируется не в каждой строке, а только по завершении банковского дня. Чтобы бухгалтеру не пришлось гадать, в каждую ячейку столбца Сальдо заносится формула, которую в дальнейшем можно скопировать в любое число ячеек. Например, в ячейке I15 она выглядит следующим образом:

=ЕСЛИ(ИЛИ(В14>В13;И(ЕПУСТ0(В14);НЕ(ЕПУСТ0(В13))));СУММ(Нач.сальдо;С$4:613)-СУММ(Н$4:Н13);"") [3]

Логические функции

Логические функции в качестве своих аргументов используют величины, имеющие логические значения ИСТИНА или ЛОЖЬ, то есть некоторые условия. Функция И возвращает значение ИСТИНА, если все аргументы имеют это значение, то есть выполняются все заданные условия; функция ИЛИ - если верно хотя бы одно из заданных условий. Функция ЕСЛИ имеет структуру ЕСЛИ(логическое выражение; значение1; значение2) и возвращает значение1, если логическое выражение имеет значение ИСТИНА, и значение2, если ЛОЖЬ.

Функции проверки свойств и значений

Функция ЕПУСТО относится к классу функций Проверка свойств и значений. Она возвращает значение ИСТИНА, если аргумент функции удовлетворяет проверяемому свойству, и ЛОЖЬ, если нет. В данном случае это проверка того, является ли ячейка пустой. Формула [3], содержащая эти функции, работает таким образом. Логическая часть формулы обеспечивает следующее: остаток заносится в случае, если дата следующей операции больше текущей даты или если текущая операция последняя. Арифметическая часть формирует текущее сальдо как сумму начального остатка и оборота по дебету за вычетом кредитового оборота. Текст Нач.сальдо в формуле - это имя ячейки, содержащей начальное сальдо, в данном случае I3. Таким образом осуществляется расчет сальдо в конце каждого банковского дня.

Рис. 8.2. Нижняя часть листа Ведомость учета банковских операций

Лист Результаты представляет собой перечень суммарных проводок по месяцам (рис. 8.3). Для их реализации служит уже знакомая нам функция СУММЕСЛИ. Формула аналогична той, что мы использовали в последнем варианте журнала операций в последней части предыдущей главы. Проводки проставляются вручную. При необходимости их можно заменить или дополнить.

Если же вы что-то забудете, предусмотрена контрольная строка, где сравниваются суммарные обороты по дебету и кредиту на листе Результаты с соответствующими суммарными оборотами по ведомости. В случае их расхождения в контрольных ячейках появляется текст "ошибка"; если все хорошо, вы видите слово "правильно", что, собственно, и отражено на рис. 8.3. Контрольная формула выглядит тривиально. Например, в ячейке Е5 (для остальных аналогично) содержится следующая формула:

=ЕСЛИ(месяц1!С15=Е4;"Правильно";"Ошибка") [4]

Процесс получения перечня суммарных проводок можно частично автоматизировать. Создаем маленькую вспомогательную таблицу непосредственно на листе Результаты или на отдельном листе (рис. 8.4). Эта таблица состоит из двух столбцов: в первом расположен натуральный ряд чисел, во втором - формула. В частности, в ячейке С30 она выглядит так:

=ЕСЛИ(НАИМЕНЬШИЙ(к_с1;В30)>С29;НАИМЕНЬШИЙ(к_с1;В30);НАИМЕНЬШИЙ(к_с1;СЧЁТЕСЛИ(к_с1;С29)+РАНГ(С29;к_с1;1))) [5]

Рис. 8.3. Лист Результаты

Функция НАИМЕНЬШИЙ

Функция НАИМЕНЬШИЙ(массив;k) возвращает k-oe наименьшее значение в множестве данных. Например, пусть имеется массив, условно именуемый А, в виде набора чисел: 2, 4, 5, 7, 9, 10, 12. Тогда при k = 1 функция возвращает самое маленькое значение, то есть в данном случае 2. Чтобы получить значение функции при k = 2, мы должны исключить из массива наименьшее значение и определить наименьшее в оставшемся массиве, при k = 3 мы исключаем оба эти значения и т. д.

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

Массив - это массив или диапазон числовых данных, для которого определяется k-oe наименьшее значение; к - это позиция (начиная с наименьшей) в массиве или интервале ячеек данных.

В формуле [5] выбирается из поименованного интервала к_с1, представляющего собой столбец с проводками на листе месяц1, первое наименьшее значение, в следующей строке - второе и т. д.

Может возникнуть вопрос, не достаточно ли одной этой функции, зачем на нее еще что-то накручивать. Здесь есть одна тонкость. Если в массиве имеются повторяющиеся элементы, например в приведенном выше списке было бы не одно значение, равное 2, а три, то следующее по величине число (в данном случае - 4) будет не вторым наименьшим, а четвертым. А наибольшее в списке число возвращается данной функцией при k, равном количеству элементов в массиве.

Поскольку ведомость любых хозяйственных операций, в том числе и банковских, содержит достаточно много одинаковых корреспонденции, счетов, такой вариант содержал бы число строк, равное количеству операций, и имел бы слишком большое число повторений. Это, естественно, не очень удобно. Поэтому стоит задача на основе функции НАИМЕНЬШИЙ создать последовательность формул, реализующую в порядке возрастания список примененных проводок.

Визуально такой список можно получить в окне раскрывающегося списка, выполнив команду Выбрать из списка через контекстное меню, но нам необходимо получить этот список на рабочем листе. Поэтому в формуле [5] используется следующая логика: если наименьшее k-ое (k - число в соседнем столбце) больше предыдущего числа, то результатом действия формулы должно быть это наименьшее k-e; если нет, следует сформировать число в ячейке как ранг предыдущего числа, плюс количество его повторений в исходном списке.

Функция РАНГ

Под рангом числа понимается его порядок в списке, то есть, если число является наименьшим k-м, то ранг - это число k. Для вычисления ранга числа в Excel существует функция РАНГ, относящаяся к категории Статистические:

РАНГ(число;ссылка;порядок)

Ее аргументами являются: число, массив и дополнительный параметр. Если он не равен 0, мы предполагаем, что Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке возрастания, при нулевом значении - в порядке убывания. Иначе говоря, функция РАНГ с ненулевым параметром является функцией, обратной функции НАИМЕНЬШИЙ, а с нулевым - функции НАИБОЛЬШИЙ.

Рис. 8.4. Вспомогательная таблица для выделения проводок

Функция СЧЕТЕСЛИ похожа на знакомую нам СУММЕСЛИ: она подсчитывает количество ячеек, отвечающих заданному критерию, в данном случае равных определенному числу, то есть определяет количество повторений данного числа в списке. Применение последних двух функций наряду с логической функцией ЕСЛИ и обеспечивает однозначность информации. На рис. 8.4 приведена вспомогательная таблица для листа Результаты, изображенного на рис. 8.3 (лист Результаты имеет унифицированный вид для всех регистров аналитического учета). Нетрудно заметить, что в эту таблицу попадают те проводки, по которым в первом месяце есть обороты. Аналогично можно поступить в отношении двух других месяцев квартала. Впрочем, все это носит необязательный характер и изложено больше не по необходимости, а для демонстрации возможностей Excel для целей бухучета.

Прочие регистры учета денежных средств

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

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

Регистры учета расчетов с партнерами

К рассматриваемой категории относятся регистры учета с:

• поставщиками и подрядчиками;
• покупателями и заказчиками;
• подотчетными лицами;
• разными дебиторами и кредиторами.

Все перечисленные регистры имеют абсолютно одинаковую форму, поэтому выберем для рассмотрения какой-либо один, например Расчеты с поставщиками и подрядчиками.

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

Рис. 8.5. Регистр учета расчетов с партнерами

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

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

Некоторая особенность формулы расчёта остатков заключается в том, что в ней необходимо еще дополнительно посчитать обороты по данному партнеру. Поэтому формула выглядит следующим образом (ячейка 19):

=ЕСЛИ((СУММ(С9:С13;В9)-СУММ(Н9:Н13;С9))>0;СУММ(G9:G13;B9)-СУММ(H9:H13;C9);"") [6]

Некоторую проблему при организации отчетной информации может представлять стыковка остатков начала периода с концом предыдущего. Решена она следующим образом. Рассмотрим формулу в ячейке С9, которая выглядит так:

=ЕСЛИ(ЕНД(ВПР($А9;месяц1;10;ЛОЖЬ));0;ВПР($А9;месяц1;10;ЛОЖЬ)) [7]

Для понимания выделим вначале одну ее часть:

ВПР($А9;месяц1;10;ЛОЖЬ)

Здесь применена функция ВПР, относящаяся к классу Ссылки и массивы, так же как и уже знакомая нам функция Выбор. Синтаксис функции ВПР следующий:

ВПР(искомое значение;интервал;номер столбца;интервальный просмотр;параметр)

Искомое значение - это значение, которое должно быть найдено в первом столбце массива. Искомое значение может быть значением, ссылкой или текстовой строкой.

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

Номер столбца - это номер столбца в заданном интервале, а не на странице. Параметр может иметь одно из двух логических значений: ЛОЖЬ или ИСТИНА.

Прокомментируем действие формулы [7] на нашем конкретном примере. В заданном интервале, то есть в учетном регистре за предыдущий месяц, она ищет искомое значение (в данном случае название партнера) в первом столбце интервала. Найдя его, отсчитывает 10 столбцов вправо и выдает то, что там находит. В данном случае это интересующий нас конечный остаток за предыдущий месяц. Значение параметра определяет, ищем мы точное совпадение искомого значения или приближенное. В данном случае параметр имеет значение ЛОЖЬ, что означает точное совпадение.

Может показаться, что функция [7] несколько сложна, но дело в том, что некоторые партнеры появляются только в данном учетном периоде, а в предыдущем их может не быть, тогда функция ВПР возвращает информацию об ошибке #Н/Д. Можно, конечно, в таких случаях удалять формулы и вручную заносить нулевые остатки, но тогда структура регистра теряет свою универсальность. Чтобы формула давала правильный результат независимо от конкретных обстоятельств, мы дополнили эту функцию некоторыми логическими построениями. Логика очень проста и исходит из того, о чем мы только что говорили, то есть: если результатом применения функции ВПР будет #Н/Д, то в качестве начального остатка заносим 0, если нет, то используем результат функции. Для реализации этой логики задействована функция ЕНД, относящаяся к классу проверки свойств и значений, так же как и использованная ранее функция ЕПУСТО. Функция ЕНД реагирует именно на конкретный вид ошибки #Н/Д (значение недоступно). Есть и более универсальная функция ЕОШИБКА, которая реагирует на ошибку любого типа. Она тоже может быть применена, что показано в следующем примере:

=ЕСЛИ(ЕОШИБКА(ВПР($А9;пост1.хls!месяц3;10;ЛОЖЬ));0;ВПР($А9;пост1.хls!месяц3;10;ЛОЖЬ)) [8]

Это формула [8] переноса начальных остатков в первый месяц текущего квартала из последнего месяца предыдущего. По своей идеологии она аналогична только что рассмотренной с той лишь разницей, что ссылка ведет на другую книгу Excel и применена другая функция.

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

=СУММЕСЛИ(банк2.xls!Пол_плат2;А9;банк2.хls!Кредит2) [9]

Пол_плат2 - поименованный интервал в книге банк2, представляющий собой столбец с наименованиями получателей или плательщиков в ведомости банковских операций.

Кредит2 - поименованный интервал в книге банк2, представляющий собой столбец с оборотами по кредиту.

Таким образом, формула [9] суммирует все оплаты по банку по данному партнеру, прошедшие в данном месяце. Это уменьшает трудоемкость ввода информации, но имеет две особенности. Во-первых, необходимо быть уверенным, что данная организация не выступает в ином качестве и не проходит по другим счетам бухгалтерского учета, - например, с какой-то организацией у вас кроме основного договора поставки могут быть иные деловые отношения, проходящие по счету 76.

Во-вторых, если вы работаете с книгой, содержащей формулы, имеющие ссылки на ячейки не открытой в данный момент книги, то в ячейках, содержащих эти формулы, может появиться информация об ошибке #ЗНАЧ!.

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

Регистры учета оборотных материальных ценностей

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

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

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

Сразу отметим, что существуют следующие способы оценки материально-производственных запасов при их отпуске (кроме товаров, учитываемых по продажной стоимости) в производство и ином выбытии:

• по себестоимости каждой единицы;
• по средней себестоимости;
• по себестоимости первых по времени приобретения материально-производственных запасов (способ ФИФО);
• по себестоимости последних по времени приобретения материально-производственных запасов (способ ЛИФО).

Первый способ тривиален и в комментариях не нуждается. Что же касается остальных способов, то здесь сразу можно отметить преимущество ведения бухучета с помощью электронных таблиц, которое заключается в том, что учетный регистр или отчетная форма объединены с инструментом расчета. В регистре, представленном на рис. 8.6, применен метод расчета по среднему. Рассмотрим формулу, содержащуюся в одной из ячеек, представляющей собой ячейку таблицы с отпускной ценой материала, например К10. Формула имеет вид:

=ЕСЛИ((C9+CУMM(I9:I10))<>0;(D9+CУMM(J9:J10))/(C9+CУMM(I9:I10));0) [10]

Формула [10] не содержит округления, поэтому если вас будет смущать излишнее количество знаков после запятой, отформатируйте эти ячейки числовым форматом с двумя десятичными знаками. Округление здесь вводить нецелесообразно, так как это увеличивает вероятность возникновения ошибок округления при дальнейших расчетах. Но в расчете оборотов и остатков округление введено, поэтому копейки дробить самостоятельно не придется. В ячейке М10 содержится формула:

=ОКРУГЛ(К9*L10;2) [11]

Функция ОКРУГЛ относится к категории Математические функции, так же как и функции СУММ и СУММЕСЛИ. Первым аргументом функции является округляемое число, вторым - число десятичных знаков округления. Данная функция производит классическое округление. Заметим, что существуют функции, округляющие вверх или вниз, но нам они не нужны.

В расчете остатков цена принята равной цене списания, а остатки по количеству и стоимости считаются по стандартной формуле для чисто активного счета. Так, в ячейке O10 содержится следующая формула:

=СУММ(I9:I10;С9)-СУММ(L9:L10) [12]

В ячейке Р10 следующая формула:

=СУММ(J9:J10;D9)-СУММ(M10:M10) [13]

Рис. 8.6. Регистр учета материалов

Рис. 8.7. Учет основных средств

Регистры учета амортизируемого имущества

К амортизируемому имуществу относятся основные средства и нематериальные активы. Методика учета аналогична рассмотренным в предыдущих примерах, поэтому изучим только основные средства, тем более что в связи с новой трактовкой понятия "нематериальные активы" малое предприятие вряд ли будет иметь их на своем балансе.

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

Прежде всего, здесь нет разнообразия проводок. Все оприходование основных средств идет через счет 08, а списание осуществляется на затраты производства. Главной задачей является правильное начисление амортизации. Поэтому для амортизируемого имущества выбрана форма учета в виде единой сквозной таблицы за год с расчетом суммарной месячной амортизации. На рис. 8.7 приведена начальная (левая) часть таблицы. В столбце Н осуществляется расчет суммы амортизации за месяц по формуле (для ячейки Н6):

=ЕСЛИ(G6>0;ОКРУГЛ(I6/G6/12;2);0) [14]

В формуле [14] начальная стоимость делится на срок полезного применения в годах и на число месяцев в году; результат, естественно, округляется с точностью до копеек. Дополнительная логика введена для исключения ложного начисления амортизации по тем позициям, которые внесены в таблицу, но еще не взяты на учет в качестве основных средств.

В столбец J заносится амортизация, начисленная на начало года для имущества, приобретенного в предыдущие периоды или бывшего в употреблении. В столбце К рассчитывается остаточная стоимость на начало года по формуле:

=ЕСЛИ($C6<=J$2;$I6-J6;0) [15]

Логическая функция введена в формулу [15] для обнуления этой величины применительно к имуществу, приобретаемому в текущем календарном году, в противном случае результаты будут ошибочными.

В формуле [15] сравнивается текущая дата с датой принятия на учет основного средства, в результате этого сравнения либо производится расчет амортизации и остаточной стоимости (если текущая дата позже даты приема ОС на учет), либо все обнуляется (если ОС на учет еще не принято). Здесь очень важно следить, чтобы все даты были занесены в числовом формате, а не в текстовом. Последнее получается, как правило, при ошибочном использовании разделительных знаков, например вместо точки вводится запятая.

На рис. 8.8 приведен фрагмент текущего учета, конкретно 1-й квартал и часть 2-го. Рассмотрим формулы в ячейках R6 и S6. В первой осуществляется расчет начисленной амортизации на 1 мая 2002 года по формуле:

=ЕСЛИ($С6<Р$2;Р6+МИН($Н6;Q6);0) [16]

В ячейке S6 остаточная стоимость на ту же дату рассчитывается по формуле:

=EСЛИ($C6<=R$2;$I6-R6;0) [17]

Фигурирующая в обеих формулах логическая функция введена с целью исключения начисления амортизации в периоды до принятия на учет основного средства. Функция МИН(...) относится к классу статистических функций и возвращает наименьший из ее аргументов. Она введена для ситуации, когда в конце срока эксплуатации основного средства из-за ошибок округления или по другим причинам остаточная стоимость становится меньше месячной амортизации.

В верхней части таблицы расположены заголовки с информацией о квартале и годе, на следующей строке - даты начала месяца. И то и другое формируется автоматически. Например, в ячейке N2 находится формула:

=ДАТА(ГОД(L2);МЕСЯЦ(L2)+1;1) [18]

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

В ячейке L1 формула следующая:

=ЦЕЛОЕ(ЕСЛИ(МЕСЯЦ(Р2)<3;4;МЕСЯЦ(Р2)/3))&" КВАРТАЛ "&ГОД(L2)&" года" [19]

Функция ЦЕЛОЕ относится к категории Математические и выделяет целую часть числа - иначе говоря, округляет число до ближайшего целого в меньшую сторону. Поскольку в последнем столбце (рис. 8.8) в каждом квартале стоит дата начала следующего квартала, для всех кварталов, кроме 4-го, номер квартала можно выделить путем деления месяца даты в последнем столбце на 3. Остальная информация получается путем сцепки числовых строк, в качестве которых выступает слово "квартал" и функция ГОД от первой даты квартала.

Рис. 8.8. Учет основных средств (продолжение)

В предпоследней строке таблицы (см. рис. 8.8), которая показана на рис. 8.9, суммируются величины амортизации и остаточной стоимости по всем основным средствам. В нижней строке выполняется расчет суммарной амортизации за месяц по всем основным средствам, которая переносится затем в главный учетный регистр. Расчет осуществляется как разница между суммами амортизации данного и предыдущего месяца.

Рис. 8.9. Учет основных средств (итоги)

Для наглядности в данном примере выбраны различные варианты даты ввода в эксплуатацию основных средств:

• предыдущие периоды;
• текущем году, но до начала текущего квартала;
• в текущем квартале;
• в последующем периоде текущего года.

Обратите внимание, как работают формулы в каждом случае.

Регистр учета зарплаты

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

Регистр учета прочих операций

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

Главный учетный регистр

Главный учетный регистр (по традиции именуемый Главной книгой) представляет собой книгу Excel, состоящую из следующих листов:

• План счетов;
• Главная книга;
• Оборотная ведомость;
• Сальдовая ведомость.

На листе План счетов содержатся рабочий план счетов и вспомогательная информация для обеспечения генерации бухгалтерской отчетности.

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

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

План счетов

Фрагмент листа План счетов представлен на рис. 8.10. В первых двух столбцах расположены наименование и номер счета. В третьем столбце представлен тип счета. Эта информация нуждается в некоторых комментариях. Под типом счета подразумевается не традиционное разделение на активные, пассивные и активно-пассивные счета, а несколько иная классификация, основанная на специфике учета. Счета первого типа (активные) - это счета, не имеющие субсчетов и аналитических счетов, второго типа (пассивные) - это счета или субсчета, имеющие субсчета более низкого уровня; и, наконец, счета третьего типа (активно-пассивные) - это счета, имеющие аналитические счета. Необходимость подобного разделения вызвана спецификой формирования оборотов и остатков в каждом из перечисленных случаях (подробности приведены ниже при рассмотрении оборотной и сальдовой ведомостей).

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

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

Рис. 8.10. Лист План счетов

Главная книга

Фрагмент Главной книги представлен на рис. 8.11. На первый взгляд здесь все тривиально - механическое объединение результирующей информации аналитических регистров. Однако есть некоторые тонкости. Прежде всего, для механического объединения применяются формулы. Самые простейшие - ссылки на соответствующие ячейки аналитических регистров. В частности, формула в ячейке Е4 выглядит следующим образом:

='D:\ROM\Бухг2002\[Kacca2.xls]результат’!E2 [20]

Поскольку соответствующий регистр закрыт в настоящее время, в формуле отражается полный путь. Естественно, когда формируется Главная книга, не стоит набирать этот неудобоваримый текст формулы [20], следует просто открыть соответствующий файл. С помощью мыши можно легко сделать ссылку на нужную ячейку, а затем скопировать полученную формулу во весь остальной массив.

Рис. 8.11. Лист Главная книга

Следующим важным моментом является то обстоятельство, что большинство проводок проходит по двум учетным регистрам, поэтому необходимо исключить двойной учет. Можно выборочно исключить дублирующие проводки, но это довольно трудоемко, ненадежно и снижает наглядность информации. Более интересно, сохранив информацию, исключить ее учет. Для этого достаточно выполнить информацию корреспондирующих счетов в текстовом формате. При этом недостаточно просто сделать это через меню Формат. В таких проводках вставляются кавычки перед цифрой. Данный процесс можно выполнить вручную, но можно и автоматизировать.

Если помните, в рабочем плане счетов (см. рис 8.10) есть столбец D с порядковым номером регистра. На листе Главная книга (см. рис 8.11) также имеется эта информация, которая и служит для исключения двойного счета. Идея проста - регистр с меньшим порядковым номером имеет приоритет. Его проводка попадает в Главную книгу в рабочем варианте, то есть корреспонденция счетов отображается в числовом виде, а регистр с большим порядковым номером является вспомогательным, и его корреспонденция отражается как текст и в дальнейших расчетах не участвует. Осуществляется это следующим образом. Исходная информация о корреспондирующих счетах располагается не в столбцах С и D, а в столбцах Н и I (см. рис. 8.11). В ячейках столбцов С и D находятся формулы, использующие информацию соответствующих ячеек столбцов Н и I. На рис. 8.12 приведена другая Главная книга, но с отображенными столбцами Н и I и с демонстрацией действия формулы, исключающей двойной счет.

Рассмотрим, например, содержимое ячейки С15, которая выглядит следующим образом:

=ЕСЛИ(ИЛИ(ВПР($Н15;план_счетов!$В$3:$Е$91;4;ЛОЖЬ)<$А15;ВПР($115;план_счетов!$В$3:$Е$91;4;ЛОЖЬ)<$А15);""""&Н15;Н15) [21]

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

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

Рис. 8.12. Лист Главная книга. Устранение двойного счета

Оборотная ведомость

Фрагмент оборотной ведомости приведен на рис. 8.13. Первый столбец - номер счета, второй-третий - обороты с начала года на начало квартала, следующие шесть - обороты по дебету и кредиту за каждый месяц квартала, затем идут два столбца с оборотами за текущий квартал и, наконец, в последних двух столбцах - обороты с начала года на конец текущего квартала. Между первым и вторым столбцом есть вспомогательный, находящийся в скрытом состоянии, в котором отображен тип счета. Формульная часть данного листа довольно проста. Номер и тип счета образуются как ссылки на соответствующие ячейки плана счетов, обороты на начало квартала - как соответствующие обороты с начала года на конец предыдущего квартала. Например, формула в ячейке С9 выглядит следующим образом:

='D:\ROM\Бухг2002\[Гл-кн1.xls]Оборотная ведомость'!М9 [22]

Книга Гл-кн1.xls в настоящий момент закрыта, поэтому формула отображает полный путь.

Что касается оборотов текущих месяцев, то формула зависит от типа счета. Для счетов типа 1 и 3 применяется хорошо знакомая нам функция СУММЕСЛИ. Суммирование ведется по соответствующему столбцу главной книги по критерию номера счета (или субсчета). Например, в ячейке Е9 записана формула:

=СУММЕСЛИ('главная книга'!С:С;А9;'главная книга'!Е:Е) [23]

Что же касается счетов второго типа, то обороты по ним формируются как суммы по их субсчетам. Так, в ячейке Е10 находится формула:

=ПРОМЕЖУТОЧНЫЕ.ИТ0ГИ(9;Е11:Е13) [24]

Что это за функция, мы уже знаем, и, наверное, понятно, почему нельзя просто написать обычное "сумм". Вспоминаем, что у функции ПРОМЕЖУТОЧНЫЕ ИТОГИ есть очень полезное свойство, которое характеризуется ее названием. Поскольку итоги промежуточные, они не участвуют в расчете окончательных итогов. Поэтому применение этой функции освобождает нас от проблем двойного счета при расчете суммарных оборотов по всем счетам за отчетный период, а также, что еще более важно, при использовании субсчетов более чем одного уровня.

Напоминаем, что с необходимостью введения субсчетов второго уровня мы уже столкнулись при начислении налога на прибыль. Аргументами функции, как и в функции СУММ, является диапазон суммирования, а также параметр, определяющий, итоги какого характера нам нужны. Синтаксис данной функции предусматривает наличие аргумента, который определяет вид итогов. Если он равен 9, это означает, что итоги представляют собой сумму. Обороты по счетам бухгалтерского учета за квартал формируются простым суммированием за три месяца, а нарастающие итоги - как суммы оборотов за квартал и на начало квартала. Например, в ячейке К9 записана формула

=СУММ(E9;G9;I9) [25]

А в ячейке М9:

=С9+К9 [26]

Внизу таблицы формируются суммарные обороты по всем счетам. Здесь может применяться функция Промежуточные итоги, а также СУММЕСЛИ. В частности, в ячейке F61 находится формула:

=СУММЕСЛИ($В3:$В60;"<>2";F3:F60) [27]

Рис. 8.13. Лист Оборотная ведомость

Рис. 8.14. Оборотная ведомость (итоги)

Это означает суммирование по всем счетам, кроме счетов второго типа.

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

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

На рис. 8.14 приведен фрагмент нижней части оборотной ведомости с результирующими суммарными оборотами.

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

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

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

Сальдовая ведомость

Фрагмент сальдовой ведомости 2-го квартала приведен на рис. 8.15. Первые два столбца - остатки на начало года, третий и четвертый - на начало квартала, далее остатки по дебету и кредиту на конец каждого месяца квартала. Формульная часть также достаточно примитивна. Остатки на начало года формируются путем ссылки на аналогичные ячейки предыдущего квартала либо копированием этой информации, а в первом квартале они создаются путем переноса итоговой информации предыдущего года. Остатки на начало квартала создаются ссылкой на результирующую информацию предыдущего квартала. К примеру, в ячейку Е4 занесена формула:

='D:\ROM\Бухг2002\[Гл-кн1.хls]Сальдовая ведомость'!Е4 [28]

Рис. 8.15. Сальдовая ведомость

Рис. 8.16. Сальдовая ведомость (итоги)

В принципе, эту информацию можно получать и путем копирования в режиме Специальная вставка > Значения.

Сальдо конца месяца по счетам первого типа получается по универсальной формуле. Для напоминания приводим содержимое ячейки G4:

=ЕСЛИ((Е4-F4+'Оборотная ведомость'!Е4-'Оборотная ведомость'!F4)>=0;(E4-F4+'Оборотная ведомость'!Е4-'Оборотная ведомость'!F4);0) [29]

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

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

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

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

Заключение

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

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

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