^
6.2. Компьютерная обработка учетных задач на малом предприятии
В связи с развитием электронно-вычислительной техники и появлением персональных компьютеров появилась возможность кардинального изменения подхода к постановке бухгалтерского учета на малом предприятии. Это связано с ростом количества ПЭВМ, повышением их технических характеристик, снижением цен на них.
Решая вопрос компьютеризации на малом предприятии, необходимо учитывать ряд факторов, связанных со спецификой финансово-хозяйственной деятельности конкретного предприятия, его экономическими возможностями, а также уровнем подготовки персонала. Подбор оптимальной конфигурации технических средств и программного обеспечения является серьезной проблемой, с которой сталкивается малое предприятие. Это связано с тем, что среди сотрудников малого предприятия редко встречаются специалисты, разбирающиеся в вопросах компьютеризации бухгалтерского учета. Для составления индивидуального проекта не у всякого малого предприятия хватит средств, поэтому важное значение здесь имеет правильный выбор типового проекта и адаптация его к конкретным условиям своего предприятия. Как правило, следует выбирать фирму, обеспечивающую конкретную помощь при внедрении типового проекта и издающую хорошую документацию по его освоению. При выборе типового проекта малому предприятию необходимо ориентироваться на хорошо зарекомендовавшие себя фирмы, ознакомиться с результатами конкурсов программ автоматизации бухгалтерского учета, которые регулярно публикуются в журнале «Бухгалтерский учет», «Финансовой газете», «Бухгалтер и компьютер».
Компьютеризация бухгалтерского учета на малых предприятиях ведется на основе большого количества программных средств, относящихся к классам «Мини-бухгалтерия» и «Интегрированная бухгалтерская система».
Пакеты мини-бухгалтерии имеют много общего и действуют по аналогичной схеме обработки информации. Рассмотрим принцип работы этих пакетов на примере «1С:Бухгалтсрия».
Программа ориентирована на привычную ручную работу бухгалтера и журнально-ордерную систему. Меню программы составлено в удобной форме, имеется возможность пересчета остатков и оборотов после ввода и изменений операций, получение итогов за любой расчетный период и интервал времени. В программе имеется возможность формирования и печати всех необходимых первичных документов, приходных и расходных кассовых ордеров, авансовых отчетов, платежных документов. Программа обеспечивает ведение учета в валюте, хранит курсы валют и позволяет автоматически выполнять переоценку валют.
Результатом обработки является набор форм отчетности для анализа финансового состояния предприятия на основе показателей, принятых в международной практике. В руководстве дано описание макроязыка, используемого для самостоятельного составления документов и отчетов произвольной формы.
В исходном состоянии программа меню содержит следующие блоки: «Операции», «Отчетность», «Сервис», «Окна» и «Помощь». Но при работе в большинстве режимов программы в меню появляется еще одна группа функций - «Действия», которая перечисляет действия, доступные в текущем режиме работы. Ниже строки меню располагается линейка пиктограмм. С ее помощью можно быстро вставить наиболее часто встречающиеся действия, например, печать, поиск строки, вставка, копирование, удаление и т.д. В нижней части окна располагается информационная строка - в ней выводятся сообщения о выполняемых программой действиях, подсказки и т.д. Остальная часть окна «1C: Бухгалтерии» является рабочим пространством - в нем выводятся списки, бланки запросов, редактируемые документы и т.д.
^
6.3. Учет материальных ценностей средствами MS Excel
В данной главе рассматривается одна из возможных технологий ведения учета товарно-материальных ценностей (ТМЦ) на основе MS Excel. Она состоит в следующем.
В рабочей книге Excel ведутся четыре отдельных листа. На первом отражаются основные сведения из фактурной части приходных документов: коды и наименования поступающих материальных ценностей, их количество и стоимость. На втором листе вводятся данные расходных документов. Третий лист содержит справочник материальных ценностей, совмещенный с оборотной ведомостью, в которой автоматически рассчитываются обороты и исходящие остатки по каждому наименованию в натуральной и стоимостной форме. Стоимостные данные по расходу оцениваются по средневзвешенным ценам, которые также отображаются на листе оборотной ведомости и автоматически пересчитываются при вводе приходных документов и внесении изменений в остатки на начало периода. Четвертый лист предназначен для формирования проводок по отраженным в таблицах операциям.
При переходе к следующему периоду Рабочая книга копируется в новый файл, в котором значения остатков на конец предшествующего периода переносятся в колонку остатков на начало периода стандартными средствами Excel. Далее очищаются сведения о приходах и расходах предыдущего периода, и цикл операций повторяется заново.
Данная технология является базой для решения задач по учету движения ТМЦ. Она позволяет решать задачу обобщения сведений о движении ценностей и автоматически дает оценку себестоимости их заготовления на основе метода средневзвешенных цен. Для более полного охвата задач учета готовой продукции, реализации товаров, учета малоценных и быстроизнашивающихся предметов (МБП) данная технология требует специфических дополнений, зависящих от экономической природы учитываемых объектов и специфики учетной политики предприятия.
Для начала присвоим листу 1 Рабочей книги имя «Приход», листу 2 – «Расход», листу 3 – «Обороты», листу 4 – «Проводки».
Перейдем на лист «Обороты». Здесь должны содержаться справочник материальных ценностей, сведения об их оборотах, остатках на начало и конец периода, средневзвешенные цены заготовления. Порядок заполнения этой таблицы мы рассмотрим в несколько этапов. Сначала сформируем справочник материальных ценностей. Он должен содержать коды и наименования всех материальных ценностей, движения которых предполагается включать в данную оборотную ведомость. В принципе, учет товаров, материалов, готовой продукции и МБП можно вести в одной Рабочей книге Excel, но в ряде случаев лучше выделить для этого отдельные Рабочие книги.
Итак, на листе «Обороты» в ячейку А1 введем текст «Код», а в ячейку В1 – текст «Наименование». Для удобства устанавливаем необходимую ширину колонок. В столбец А будем вводить коды материальных ценностей, а в столбец В – их полные наименования. Естественно, коды должны быть уникальны.
Столбец А необходимо отформатировать таким образом, чтобы все вводимые значения воспринимались как текстовые. Для этого выделяем столбец, открываем правой кнопкой мыши контекстное меню, выбираем «Формат ячейки», закладку «Число», выделяем пункт «Текстовый», нажимаем «ОК». Если предполагается использовать только числовые коды, то можно этого не делать. Однако мы строим таблицу в предположении, что коды ТМЦ могут иметь смешанное алфавитно-цифровое кодирование. В ряде случаев это и удобнее и нагляднее.
Коды и наименования используются для того, чтобы облегчить последующие ввод и контроль приходных и расходных документов.
Рекомендуем начать заполнение справочника на листе «Обороты» со строки 4, поскольку вторую и третью строки в последующем будем использовать для вспомогательных целей.
В качестве примера сведения о материальных ценностях представлены на рис.6.1.
Рис.6.1. Пример ввода данных справочника ТМЦ
В дальнейшем необходимо будет дополнить лист «Обороты» колонками и формулами, позволяющими рассчитывать средневзвешенные цены ТМЦ, их обороты и остатки. Но сначала необходимо заняться листами для ввода сведений о приходе и расходе (списании).
Перейдем на лист «Приход». Здесь должны вводиться сведения о поступлении материальных ценностей в соответствии с приходными документами. Рекомендуется разделять вводимые документы одной-двумя пустыми строками и предварять их указанием номеров и дат в произвольном формате.
В ячейку А1 введем текст «Код», в В1 – «Наименование», в С1 – «Кол-во», а в D1 – «Сумма по док-ту». Для компактного размещения данных отформатируем первую строку таблицы так, чтобы информация в ячейках строки заголовка переносилась по словам и была отцентрирована по вертикали и по горизонтали. Кроме того, подбираем нужную высоту первой строки и ширину столбцов для компактного отображения информации.
В столбец А вносятся коды учитываемых объектов. Он форматируется как «текстовый». Коды должны вводиться на основании фактурных частей приходных документов и в соответствии с кодированием, определенным в справочнике. Мы строим таблицу таким образом, чтобы в столбце В автоматически появлялись наименования объектов учета из справочника с листа «Обороты». Для этого в ячейку В2 введем формулу:
=ВПР(А2;Обороты!$A$4:$B$2000;2;ЛОЖЬ).
В соответствии с формулой в ячейку В2 будет подставлено наименование материальной ценности, код которой указан в ячейке А2 листа «Приход». Функция ВПР осуществляет поиск значения, указанного первым аргументом, в первом столбце диапазона ячеек (второй аргумент функции). При этом возвращается значение из ячейки, размещенной на пересечении найденной строки и столбца, номер которого относительно первого столбца диапазона поиска указан третьим аргументом. Четвертый аргумент содержит условие поиска.
В нашем случае ячейка А2 листа «Приход» содержит код объекта учета (вида материальной ценности). Диапазон «Обороты!$A$4:$B$2000» содержит два столбца. В первом (А) находятся коды материальных ценностей. Один из этих кодов обязательно должен совпадать с кодом, который имеется в строке приходного документа на листе «Приход». Во втором столбце (В) листа «Обороты», находятся названия материальных ценностей. Таким образом, функция ВПР в приведенной нами форме ищет название кода, указанного при вводе приходного документа, и возвращает наименование соответствующего объекта. Если код в справочнике на листе «Обороты» не найден, то в соответствующей ячейке столбца В наименование не появится. При поступлении нового вида материальных ценностей сведения о нем обязательно предварительно должны вводиться в справочник (лист «Обороты»).
Диапазон строк с 4 по 2000 выбран произвольно. Если предполагается учитывать большее число видов материальных ценностей, то следует установить иную границу диапазона. В целом же выбор диапазона не имеет особого значения. Главное, чтобы он перекрывал область с реально имеющимися данными. Исходя из тех же соображений выбираются диапазоны, используемые в других формулах.
После ввода формулы в ячейку В2 рекомендуется скопировать ее в другие ячейки столбца В.
Следует иметь в виду, что Excel - достаточно "капризная" программа. В частности, можно столкнуться с ситуацией, когда функция ВПР никак "не хочет" правильно интерпретировать коды объектов и даже для кодов, имеющихся в справочнике на листе "Обороты", не возвращает правильного названия ценности на листе "Приход". В чем здесь проблема, наверное, не знают даже разработчики из Microsoft. Однако в этом случае обычно помогает следующий прием. Вместо того, чтобы вводить код ТМЦ на листе "Приход" вручную, следует, находясь на листе "Обороты", скопировать нужный код в буфер обмена, а потом вставить это значение в нужную ячейку на листе "Приход". Мы рекомендуем при вводе информации размещать листы "Приход" и "Обороты" в двух окнах на одном экране.
В столбце С на листе "Приход" вводятся данные о количестве поступивших объектов учета, а в столбце D - о совокупной стоимости их партии в соответствии с приходным документом. Для корректного выполнения расчетов единицы измерения по документам должны соответствовать имеющимся в справочнике.
Пример ввода данных о поступлении материальных ценностей приведен на рис. 6.2.
Как следует из рис.6.2, мы разделяем отдельные документы пустой строкой. В принципе, это необязательно, но так данные лучше обозреваются. Значение #Н/Д в некоторых ячейках показывает, что данный код в справочнике отсутствует. В частности, #Н/Д проставляется функцией ВПР для тех ячеек, где код задан пустым значением.
Выделение НДС. Если данные по суммам поступающих материальных ценностей вводятся без учета НДС, то никакой проблемы нет и можно ограничиться рассмотренным выше порядком заполнения таблицы приходов. Однако, если в приходном документе суммы включают НДС, то целесообразно предусмотреть возможность автоматического выделения НДС из вводимых данных. Это нужно хотя бы для того, чтобы получать стоимостные оценки суммы прихода и средневзвешенные цены без учета НДС.
Для решения поставленной задачи модифицируем таблицу приходов таким образом, чтобы при необходимости Excel автоматически вычислял стоимость ТМЦ, выделяя из общей суммы НДС, и оставлял сумму без изменений в тех случаях, когда введенные данные не включают НДС.
Рис.6.2 Пример ввода данных о поступлении ТМЦ
Добавим к таблице на листе "Приход" три колонки.
В ячейку Е1 введем текст "Вкл.НДС?", в ячейку F1 - текст "Сумма без НДС", а в ячейку G1 - текст "НДС". Используем горизонтальное и вертикальное форматирование по центру с переносом по словам.
Организуем вычисления таким образом, чтобы при пустом значении ячейки столбца Е сумма из столбца D просто копировалась в соответствующую ячейку столбца F, а в противном случае в ней рассчитывалось значение суммы без НДС. Для организации таких вычислений следует ввести в ячейки столбца F формулы, проверяющие, является ли пустой соответствующая ячейка столбца Е, и исходя из этого либо копирующие значение из столбца D, либо на основании ставки НДС и суммы из столбца D вычисляющие сумму без НДС.
Если предприятие всегда работает с одной и той же ставкой НДС, то проще всего указать ее прямо в расчетной формуле. Для ячейки Е2 при ставке 20% эта формула должна быть задана в виде:
=ЕСЛИ(ЕПУСТО(Е2);D2;ОКРУГЛ(D2*100/120;2))
Формула работает следующим образом. Функция ЕПУСТО(Е2) проверяет, является ли ячейка Е2 пустой. Если это так, то функция ЕСЛИ() в качестве своего значения возвращает значение из ячейки D2. В противном случае возвращается значение, очищенное от НДС:
(D2*100/120).
При этом с помощью функции ОКРУГЛ() производится округление значения до двух десятичных разрядов. Подробную информацию о функциях ЕСЛИ() и ОКРУГЛ() можно найти в справочной системе Excel.
Здесь и везде далее для компактности представления данных мы используем форматирование чисел в виде целых с разделителями тысяч. Исключение составляет только описываемый далее показатель средневзвешенной цены, который форматируется с тремя знаками после запятой. При желании можно выбрать иное форматирование чисел, например, с двумя разрядами после запятой, обеспечивающее представление сумм с копейками.
Таким образом, если в ячейку Е2 что-либо введено (то есть ее значение не пусто), то предполагается, что сумма по документу включает НДС и в ячейку F2 попадает сумма без НДС. В противном случае считается, что сумма в ячейке D2 не включает НДС и просто копируется в ячейку F2.
В соответствии с нашими соглашениями для расчета суммы НДС в ячейку G2 должна быть введена следующая формула:
=ЕСЛИ(ЕПУСТО(Е2);ОКРУГЛ(D2*0,2;2);D2-F2)
После ввода формул в ячейки F2 и G2 их следует скопировать на весь диапазон ввода приходных документов. Пример построения таблицы с автоматическим отделением НДС приведен на рис.6.3.
Обращаем внимание на то обстоятельство, что формула будет работать правильно только тогда, когда ячейка Е2 действительно пустая, то есть не содержит никакого значения. В связи с этим нельзя "сбрасывать" ячейку, вводя в нее пробел, поскольку пробел считается непустым значением. Для того, чтобы сделать ячейку пустой, нужно полностью удалить ее содержимое. Проще всего это сделать, установив на нее курсор и нажав клавишу Delete. В этом случае ячейка полностью "сбрасывается" и наша формула отработает корректно.
Рис.6.3 Пример ввода документов, включающих и не включающих НДС в сумму по документу
Отметим, что ставку НДС можно задавать не только непосредственно в расчетных формулах. Иногда желательно параметризовать формулы, что позволяет ссылаться в формуле не на конкретное значение, а на ячейку, в которой задана ставка. Например, можно создать отдельный лист Рабочей книги, на котором задаются все важные для расчетов константы. Если этот лист называется "Константы", а значение ставки НДС содержится на нем в ячейке В2, то формула для ячейки F2 листа "Приход" будет выглядеть следующим образом:
=ЕСЛИ(ЕПУСТО(Е2);D2;ОКРУГЛ(D2*100/(100+Константы!$В$2);2))
При использовании параметризованной формулы нет необходимости в ее исправлении после введения другой ставки НДС. В этом случае при переходе к следующему периоду исправляется только значение константы. В нашем же случае при изменении ставки придется заменить все формулы в столбце F листа "Приход". Впрочем, это тоже несложно.
Теперь рассмотрим вопрос о том, как поступать в тех ситуациях, когда предприятие работает с несколькими ставками НДС.
Здесь самым простым и гибким решением будет задание в ячейках столбца Е конкретных значений ставок НДС по поступающим ценностям и модификация формул для столбца F таким образом, чтобы они использовали для расчета явно заданные ставки. Для того чтобы предусмотреть возможность обработки стоимости с включенным или не включенным НДС, да к тому же при разных ставках, можно применить следующий прием.
Будем считать, что ставки задаются в виде целого числа для каждой вводимой в таблицу строки приходного документа по приводимым ниже правилам. Если НДС входит в сумму, то ставка проставляется со знаком "минус". В противном случае - со знаком "плюс" (т.е. без знака).
В этих предположениях формула расчета суммы без НДС для второй строки столбца F может быть задана так:
=ЕСЛИ(Е2>0;D2;ОКРУГЛ(D2*100/(100-E2);2))
Вычитание (100-Е2) соответствует нашему условию о том, что при указании суммы с НДС ставка задается со знаком "минус".
Расчет суммы НДС в этом случае также зависит от знака числа в ячейке Е2. Соответствующая формула в ячейке G2 будет выглядеть так:
=ЕСЛИ(Е2>0;ОКРУГЛ(D2*E2/100;2);D2-F2)
Помимо рассмотренного только что подхода с явным указанием ставки НДС может быть реализовано и другое решение, предполагающее задание ставки НДС для каждого объекта учета в справочнике ТМЦ на листе "Обороты". Соответствующее значение может быть выбрано с помощью функции ВПР(). Мы не будем рассматривать способ задания формул в этом случае, а предложим сконструировать их самостоятельно.
Расход. На листе «Расход» вводятся сведения о движении материальных ценностей в соответствии с расходными документами. Так же как и в случае с приходными документами, вводимые документы рекомендуется разделять пустыми строками.
В ячейку А1 введем текст «Код», в В1 – «Наименование», а в С1 – «Кол-во».
В данном варианте технологии обработки данных по учету материальных ценностей мы не рассматриваем случая продажи товара, поэтому отпускные цены здесь не задаются. Для учета реализации таблицы Рабочей книги требуют определенной модификации.
Так же как и на листе «Приход» в ячейки столбца А будем вводить коды материальных ценностей, а их наименования будут подставляться автоматически путем выбора из справочника на листе «Обороты». Столбец А должен быть отформатирован так, чтобы все вводимые в него значения воспринимались как текстовые.
Итак, в ячейку В2 листа «Расход» следует ввести формулу:
=ВПР(А2;Обороты!$A$4:$B$2000;2;ЛОЖЬ).
Она полностью идентична формуле выборки наименования на листе «Приход». Затем следует скопировать эту формулу во все строки столбца В, в которые предполагается заносить сведения расходных документов.
Пример заполнения таблицы на листе «Расход» приведен на рис.6.4.
Оборотная ведомость. Теперь перейдем к реализации основных расчетных функций.
Как указывалось ранее, мы дополним справочник ТМЦ, размещенный на листе "Обороты", колонками, включающими сведения о входящих остатках, а также формулами для расчета оборотов, исходящих остатков и средних цен заготовления по каждому виду ТМЦ.
Для этого дополним таблицу несколькими столбцами.
В колонки С и D будут вводиться сведения об остатках по каждому виду ТМЦ на начало периода в натуральном и стоимостном выражении соответственно. Для первого периода они вводятся вручную, а в дальнейшем их значения просто переносятся из столбцов остатков на конец периода. В столбцах Е и F будут рассчитываться итоги по приходу ТМЦ в натуральном (Е) и стоимостном (F) выражении. В столбцах G и Н будут рассчитываться итоги по расходу каждого вида ТМЦ, а в столбцах I и J - остатки на конец периода также в натуральном и стоимостном выражении соответственно. В столбце К будут рассчитываться средневзвешенные цены заготовления. Они являются базой для расчета стоимостной оценки расхода.
Мы разметили заголовок таблицы так, как показано на рис.6.5.
При разметке заголовка в нашем примере мы исходили из того, что он должен быть "двухслойным". Поэтому он и размещен на двух строках. При этом центрирование заголовков "Остаток на начало", "Приход", "Расход", "Остаток на конец" получено путем выполнения операции "Центрирование по выделению" (ввести текст, выделить соседние столбцы, на которых он должен быть центрирован, Формат/ Ячейки, Выравнивание, Горизонтальное, По центру выделения, Ok). Заголовки во второй строке просто центрированы по горизонтали.
Рис.6.4. Пример ввода данных на листе «Расход»
Рис.6.5. Разметка заголовка таблицы оборотной ведомости
В третью строку таблицы в ячейки D3, F3, НЗ и J3 введем формулы для подсчета итогов по стоимостным показателям в соответствующих столбцах. Не забудьте указать диапазон суммирования, включающий все виды, ТМЦ, и лучше - с запасом.
Теперь зададим основные расчетные формулы.
В ячейку Е5 введем формулу, подсчитывающую итог по приходу данного вида материальных ценностей:
=СУММЕСЛИ(Приход!$А$2:$А$5000;$А5;Приход!С$2:С$5000)
Функция СУММЕСЛИ() в данном случае выполняет суммирование данных по столбцу С листа "Приход" по тем его строкам, у которых код ТМЦ совпадает с кодом строки А5 листа "Обороты". Поскольку в столбце С листа "Приход" мы вводим данные о поступлении ценностей в натуральном выражении в соответствии с приходными документами, то формула подсчитывает суммарный приход в натуральном выражении по их виду, задаваемому кодом в ячейке А5 листа "Обороты".
В ячейке F5 следует задать формулу:
=СУММЕСЛИ(Приход!$А$2:$А$5000;$А5;Приход!F$2:F$5000)
Здесь критерий отбора тот же, но суммируются данные из столбца F листа "Приход". В этом столбце сосредоточены "очищенные" от НДС данные по приходу в стоимостном выражении. Поэтому результатом действия формулы является итог по поступлению определенного вида ТМЦ в стоимостном выражении без НДС.
В ячейке G5 задаем формулу для расчета итогов по расходу ТМЦ названного вида в натуральном выражении. Она выглядит так:
=СУММЕСЛИ(Расход!$А$2:$А$5000;$А5;Расход!С$2:С$5000)
Все то же самое, но только выбираются и суммируются данные на листе "Расход".
Пропустим пока ячейки Н5, I5, J5 и перейдем к ячейке К5. Как было указано выше, здесь должна рассчитываться средневзвешенная цена заготовления единицы ТМЦ определенного вида. Для расчета этой величины мы должны сложить остаток на начало периода (D5) с приходом за период (F5) в стоимостном выражении и поделить полученный результат на сумму остатка на начало периода (С5) и прихода за период (Е5) в натуральном выражении. В рамках принятых договоренностей о размещении показателей в нашей таблице эта формула выглядела бы так:
=(D5+F5)/(C5+E5)
Однако следует предусмотреть случай, когда С5+Е5=0. Эта ситуация может возникнуть, если еще не заданы ни количество входящего остатка, ни данные о приходе. Ведь можно "размножать" формулы впрок, до ввода сведений о ТМЦ или приходных документов. В этом случае возникнет ситуация деления на ноль. В принципе, ничего страшного, но таблица примет весьма непрезентабельный вид и, кроме того, "испортятся" итоги по колонкам со стоимостными показателями.
Поэтому мы зададим формулу для ячейки К5 в ином виде:
=ЕСЛИ(С5+Е5>0; (D5+F5)/(С5+Е5);0)
Она проверяет, является ли сумма входящего остатка и итогов по приходу в натуральном выражении неотрицательной, и если это так, то рассчитывает среднюю цену обычным порядком. В противном случае формула выдает значение "ноль" как указание на то, что средневзвешенная цена не может быть рассчитана.
Теперь уже можно задать формулы для оценки себестоимости расхода, оцениваемой по средневзвешенной цене и остаткам на конец периода.
Для расчета себестоимости расхода в ячейку Н5 введем формулу:
=G5*K5
В соответствии с ней себестоимость расхода оценивается как произведение общего расхода в натуральном выражении (G5) на средневзвешенную цену заготовления.
Формулы для расчета остатков на конец периода просты. Остаток в натуральном выражении (ячейка I5) определяется по формуле:
=C5+E5-G5
Она суммирует остаток на начало периода с приходом за период и вычитает из полученной величины расход за период.
Аналогично задается и формула для остатка в стоимостном выражении (ячейка J5):
=D5+F5-H5
Теперь все основные формулы введены. Однако, перед тем как копировать их в другие строки, зададим форматирование ячеек остатков таким образом, чтобы в случае возникновения в них отрицательных величин они отображались красным цветом. Для этого нужно выделить ячейки I5 и J5, выбрать пункт меню Формат/Ячейки, выбрать закладку "Число", в списке числовых форматов выбрать пункт "Числовой", далее перейти в поле "Отрицательные числа", пометить там один из примеров с отображением числа красным цветом и нажать клавишу Ok. Можно просто отображать отрицательные числа красным цветом, а можно еще и выводить перед ними знак минус. Для нашего примера мы выбрали второй случай.
Теперь, при возникновении достаточно типичного случая, когда сведения по расходным документам вводятся раньше данных приходных документов и/или остатков на начало периода, отрицательные исходящие остатки будут отображаться красным цветом и их легче будет заметить.
Вот теперь можно выполнить операцию копирования формул пятой строки в последующие строки. В условиях нашего примера мы получили результат, представленный на рис.6.6.
Можно видеть, что происходит автоматический расчет средних цен, а также итоговых значений оборотов и остатков. Кроме того, там, где мы заранее ввели "перерасход", отображаются красные отрицательные остатки, а там, где не введены сведения о приходе и начальные остатки, средняя цена и расход в стоимостном выражении равны нулю.
Рис.6.6. Пример расчета оборотной ведомости
Если справочник (оборотная ведомость) содержит сведения о материальных ценностях, учитываемых на одном синтетическом счете, то проблемы формирования проводок не существует. Действительно, на листе "Обороты" мы имеем всю необходимую информацию для отображения движений по данному счету в синтетическом учете: ячейка F3 содержит стоимостный итог по поступлению ТМЦ за текущий период - дебетовый оборот счета, в ячейке НЗ содержится итог по списанию себестоимости ТМЦ - кредитовый оборот счета. Для того чтобы получить итоги по поступлению сумм НДС, достаточно на листе "Приход" вставить формулу суммирования столбца G.
Мы рассмотрим общий случай, когда справочник на листе "Обороты" включает сведения о ТМЦ разной экономической природы, учитываемых на разных счетах.
Для решения этой задачи справочник должен быть дополнен сведениями о счетах, на которых учитываются конкретные виды материальных ценностей. Мы будем хранить эту информацию в столбце L.
Введем в ячейку L1 текст "Счет" и отформатируем столбец L так, чтобы все вводимые в него значения воспринимались как текстовые. Затем для каждого вида ТМЦ проставим код счета, на котором он учитывается.
В нашем примере мы ввели данные, приведенные на рис.6.7.
Рис.6.7. Справочник ТМЦ со сведениями о счетах
Теперь следует перейти на лист "Проводки".
В ячейку А1 введем текст "Дебет", в ячейку В1 - "Кредит", в ячейку С1 - "Сумма", а в ячейку D1 - "Комментарий".
Методика заполнения таблицы на этом листе состоит в следующем. Задаются все проводки, которые возникают в связи с движением материальных ценностей, занесенных в справочник на листе "Обороты". Дебетуемый счет проводки вводится в соответствующую строку столбца А, а кредитуемый счет - в столбец В. Формулы для расчета суммы проводки зависят от ее содержания.
Формула для расчета суммы "приходной" проводки имеет следующий вид:
=СУММЕСЛИ(Обороты!L$4:$L$2000;A2;Обороты!$F$4:$F$2000)
Она суммирует значения из столбца F (приход в стоимостном выражении) листа "Обороты" по тем строкам, в столбце L которых содержится код счета, совпадающий с кодом счета, дебетуемым в данной проводке (А2).
Эта формула может быть скопирована для всех "приходных" проводок на листе "Проводки", то есть тех, в которых дебетуется счет, на котором учитывается какой-либо вид материальных ценностей, представленных в таблице на листе "Обороты".
Формулы расчета сумм "расходных" проводок, то есть тех, в которых кредитуется тот или иной счет учета материальных ценностей, составляются по аналогичным правилам, но в качестве критерия отбора (второй аргумент функции СУММЕСЛИ) используется ссылка на ячейку из столбца В, а суммирование производится по столбцу Н. Например, в рассматриваемом примере для строки 5 формула будет выглядеть следующим образом:
=СУММЕСЛИ(Обороты!L$4:$L$2000;В5;Обороты!$Н$4:$Н$2000)
Подсчет сумм проводок по учету НДС, полученному с разными видами материальных ценностей, можно организовать исходя из тех соображений, что субсчета 19-го счета в точности соответствуют счетам учета разных категорий материальных ценностей. На наш взгляд, эту задачу удобнее всего решать путем предварительного "сведения" итоговых сумм НДС по всем приходам на листе "Обороты". Само по себе "сведение" сумм по отдельным позициям учета материальных ценностей не представляет особого интереса. Однако, "привязав" полученные суммы НДС к конкретным видам ценностей, легко распределить их впоследствии по конкретным субсчетам счета 19 "НДС по приобретенным материальным ресурсам".
Итак, в колонке М листа "Обороты" будем подсчитывать итоги по НДС, полученному при поступлении каждого вида материальных ценностей. Введем в ячейку M1 текст "НДС", в ячейку М3 - формулу подсчета итогов по колонке =СУММ(М4:М2000), а в ячейку М5 - формулу подсчета итогов по суммам НДС, поступившим с данным видом ТМЦ:
=СУММЕСЛИ(Приход!$А$2:$А$5000;А5;Приход!$G$2:$G$5000)
Затем указанная формула должна быть скопирована во все последующие строки, содержащие сведения о материальных ценностях.
Теперь можно на листе "Проводки" задать формулы для расчета сумм проводок по учету НДС, поступившего с материальными ценностями. Если на счете 19.1 учитывается НДС, поступивший с товарами, то для проводки Д19.1 К60 формула для расчета суммы может быть записана следующим образом:
=СУММЕСЛИ(Обороты!$L$4:$L$2000;"41";Обороты!$М$4:$М$2000)
В отличие от ранее рассмотренных формул она суммирует числа в столбце М листа "Обороты", а критерий поиска задан в виде константы - кода счета "41". Данная формула суммирует НДС для товаров, то есть тех строк справочника на листе "Обороты", которые учитываются на счете 41. Аналогично следует построить формулы и для других категорий материальных ценностей, учитываемых на других счетах.
В условиях нашего примера были получены результаты, представленные на рис.6.8.
Рис.6.8. Автоматически сформированные проводки по движению ТМЦ и смежным операциям
Таблица легко может быть дополнена для формирования и подсчета сумм проводок других типов. В частности, для МБП можно составить проводки, отражающие начисление износа по передаваемым в эксплуатацию ценностям, базируясь на общих суммах расхода по ценностям, относящимся к данной категории.
Переход к другому периоду осуществляется по следующей методике. Рабочая книга копируется в новый файл, в котором значения остатков материальных ценностей на конец периода стандартными средствами Excel переносятся в колонку остатков на начало периода как значения (но не как формулы!). Затем из новой Рабочей книги удаляется вся оперативная информация, относящаяся к предшествующему периоду (данные листов "Приход" и "Расход"). В результате обороты обнуляются, итоги на начало периода становятся равными итогам на конец предшествующего периода и цикл операций повторяется заново для текущего периода.
