Файл: Информационные бухгалтерские системы.docx

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

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

Справочник материальных ценностей

Присвойте листу 1 рабочей книги имя «Приход», листу 2 – «Расход», листу 3 – «Обороты», листу 4 – «Проводки».

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

На листе «Обороты» в ячейку A1 введите текст «Код», а в ячейку B1 – текст «Наименование». Установите удобную ширину колонок. В столбец A мы будем вводить коды материальных ценностей, а в столбец B – их полные наименования. Естественно, коды должны быть уникальны.

Настоятельно рекомендуется отформатировать столбец A таким образом, чтобы все вводимые здесь значения воспринимались как текстовые (Выделить столбец, Формат ® Ячейки, закладка «Число», выделить пункт «Текстовый», нажать Ok.) Если предполагается использовать только числовые коды, то можно этого не делать. Однако мы строим таблицу в предположении, что коды ТМЦ могут иметь смешанное алфавитно-цифровое кодирование. В ряде случаев это и удобнее и нагляднее.

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

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

Рисунок 6.10 – Пример ввода данных справочника ТМЦ.

Далее мы дополним лист справочника ТМЦ колонками и формулами, позволяющими рассчитывать их средневзвешенные цены, обороты и остатки. Но сначала займемся листами для ввода сведений о приходе и расходе (списании).

Приход

Перейдите на лист «Приход». Здесь должны вводиться сведения о поступлении материальных ценностей в соответствии с приходными документами. Рекомендуется разделять вводимые документы одной-двумя пустыми строками и предварять их указанием номеров и дат в произвольном формате.

В ячейку A1 введите текст «Код», в B1 – «Наименование», в C1 – «Кол-во», а D1 – «Сумма по док-ту». Для компактного размещения данных мы отформатировали первую строку таблицы так, чтобы информация в ячейках строки заголовка переносилась по словам и была отцентрирована и по вертикали и по горизонтали. Кроме того, мы подобрали нужную высоту первой строки и ширину столбцов для компактного отображения информации. Вы можете отформатировать таблицу по своему вкусу.

В столбец A должны вводиться коды учитываемых объектов. Он должен быть отформатирован так, чтобы все вводимые в него значения воспринимались как текстовые. Коды должны вводиться на основании фактурных частей приходных документов и в соответствии с кодированием, определенным в справочнике. Мы построим таблицу так, чтобы в столбце B автоматически появлялись наименования объектов учета из справочника с листа «Обороты». Для этого введите в ячейку B2 формулу:

=ВПР(A2;Обороты!$A$4:$B$2000;2;ЛОЖЬ)

В соответствии с ней в ячейку B2 будет подставлено наименование материальной ценности, код которой указан в ячейке A2 листа «Приход». Напомним, что функция ВПР осуществляет поиск значения, указанного первым аргументом, в первом столбце диапазона ячеек, заданного вторым аргументом. При этом возвращается значение из ячейки, размещенной на пересечении найденной строки и столбца, номер которого относительно первого столбца диапазона поиска указан третьим аргументом. Четвертый аргумент содержит условие поиска. Подробности см. в справочной системе Excel.

В нашем случае ячейка A2 листа «Приход» содержит код объекта учета (вида материальной ценности). Диапазон «Обороты!A$4:B$2000» содержит два столбца. В первом (A) находятся коды материальных ценностей. Один из этих кодов обязательно должен совпадать с кодом объекта, который фигурирует в данной строке данного приходного документа. Во втором столбце (B) листа «Обороты», как мы договорились, находятся названия материальных ценностей. Таким образом, функция ВПР в приведенной нами форме ищет название кода, указанного при вводе приходного документа и возвращает наименование соответствующего объекта. Если код в справочнике на листе «Обороты» не найден, то в соответствующей ячейке столбца B наименование не появится. Если поступает новый вид материальных ценностей, то сведения о нем следует обязательно ввести в справочник.

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

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

Следует иметь в виду, что Excel – достаточно «капризная» программа. В частности, можно столкнуться с ситуацией, когда функция ВПР никак «не хочет» правильно интерпретировать коды объектов и даже для кодов, имеющихся в справочнике на листе «Обороты», не возвращает правильного названия ценности на листе «Приход». В чем проблема, наверное, не знают даже разработчики из Microsoft. Однако в этом случае обычно помогает следующий прием. Вместо того чтобы вводить код ТМЦ на листе «Приход» вручную следует, находясь на листе «Обороты», скопировать нужный код в буфер промежуточного хранения (выделить ячейку, Правка ® Копировать), а потом вставить это значение в нужную ячейку на листе «Приход» (позиционировать ячейку, Правка ® Вставить). Можно использовать и соответствующие пиктограммы. Мы рекомендуем при вводе информации размещать листы «Приход» и «Обороты» в двух окнах на одном экране, нужным образом размещая окна и подбирая в них нужный масштаб. В этом случае рассмотренный только что прием легко осуществлять путем манипуляций мышью.

В столбце C на листе «Приход» вводятся данные о количестве поступивших объектов учета, а в столбце D – о совокупной стоимости их партии в соответствии с приходным документом. Для корректного выполнения расчетов единицы измерения по документам должны быть приведены к тем же, которые предполагаются в справочнике.

Пример ввода данных о поступлении материальных ценностей приведен на рис. 6.11.

Рисунок 6.11 – Пример ввода данных о поступлении ТМЦ.

Как следует из рис. 6.11 мы разделяем отдельные документы пустой строкой. В принципе это необязательно, но так данные лучше обозреваются. Кроме того, перед данными нового документа можно вводить его номер и дату, поскольку они позволяют идентифицировать, к какому документу относятся данные. Значения #Н/Д в некоторых ячейках означают, что данного кода нет в справочнике. В частности, это значение проставляется функцией ВПР для тех ячеек, где код задан пустым значением.

Выделение НДС

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

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

Добавим к таблице на листе «Приход» три колонки.

В ячейку E1 введите текст «Вкл. НДС?», в ячейку F1 – текст «Сумма без НДС», а в ячейку G1 – текст «НДС». Мы используем горизонтальное и вертикальное форматирование по центру с переносом по словам.

Мы организуем вычисления таким образом, чтобы при пустом значении ячейки столбца E сумма из столбца D просто копировалась бы в соответствующую ячейку столбца F, а в противном случае в ней вычислялось бы значение суммы без НДС. Для организации таких вычислений следует ввести в ячейки столбца F формулы, которые проверяют, является ли пустой соответствующая ячейка столбца E и в зависимости от этого, либо копируют значение из столбца D, либо на основании ставки НДС и суммы из столбца D вычисляют сумму без НДС.

Если предприятие всегда работает с одной и той же ставкой НДС, то проще всего указать ее прямо в расчетной формуле. Для ячейки E2 при ставке 20% эта формула должна быть задана в следующем виде:

=ЕСЛИ(ЕПУСТО(E2);D2;ОКРУГЛ(D2*100/120;2))

Формула работает следующим образом. Функция ЕПУСТО(E2) проверяет, является ли ячейка E2 пустой. Если это так, то функция ЕСЛИ() в качестве своего значения возвращает значение из ячейки D2. В противном случае возвращается значение, очищенное от НДС (D2*100/120). При этом с помощью функции ОКРУГЛ производится округление до двух десятичных разрядов. Подробную информацию о функциях ЕСЛИ() и ОКРУГЛ() можно найти в справочной системе Excel.

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

Таким образом, если в ячейку E2 что-либо введено (то есть ее значение не пусто), то предполагается, что сумма по документу включает НДС и в ячейку F2 попадает сумма без НДС. В противном случае считается, что сумма в ячейке D2 не включает НДС, и она просто копируется в ячейку F2.

В соответствии с нашими соглашениями для расчета суммы НДС в ячейку G2 должна быть введена следующая формула:

=ЕСЛИ(ЕПУСТО(E2);ОКРУГЛ(D2*0,2;2);D2–F2)

После ввода формул в ячейки F2 и G2 их следует скопировать на весть диапазон ввода приходных документов. Пример построения таблицы с автоматическим отделением НДС приведен на рис. 6.12.

Рисунок 6.12 – Пример ввода документов, включающих и не включающих НДС в проставленные суммы

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

Отметим, что ставку НДС можно задавать не только непосредственно в расчетных формулах. Иногда желательно параметризовать формулы. В этом случае можно было бы в формуле ссылаться не на конкретное значение, а на ячейку, в которой задана ставка. Например, можно создать отдельный лист рабочей книги, на котором задаются все важные для расчетов константы. Если этот лист называется «Константы», а значение ставки НДС содержится на нем в ячейке B2, то формулу для ячейки F2 листа «Приход» можно было бы задать в виде:

=ЕСЛИ(ЕПУСТО(E2);D2;ОКРУГЛ(D2*100/(100+Константы!$B$2);2))

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

Теперь рассмотрим вопрос о том, как поступать в тех случаях, когда предприятие работает с несколькими ставками НДС.

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

Будем считать, что ставки задаются в виде целого числа для каждой вводимой в таблицу строки приходного документа по следующим правилам. Если НДС входит в сумму, то ставка проставляется со знаком «минус». В противном случае ставка проставляется со знаком «плюс» (без знака).

В этих предположениях формула расчета суммы без НДС для второй строки столбца F может быть задана так:

=ЕСЛИ(E2>0;D2;ОКРУГЛ(D2*100/(100–E2);2))

Вычитание (100–E2) соответствует нашему соглашению о том, что при указании суммы с НДС, ставка задается со знаком «минус».

Расчет суммы НДС в этом случае также зависит от знака числа в ячейке E2. Соответствующая формула в ячейке G2 будет выглядеть так:

=ЕСЛИ(E2>0;ОКРУГЛ(D2*E2/100;2);D2–F2)

Пример применения данного подхода приведен на рис. 6.13.

Рисунок 6.13 – Пример реализации подхода с явным заданием ставки НДС для каждой строки приходных документов.

Помимо рассмотренного только что подхода с явным указанием ставки НДС может быть реализовано и другое решение, которое предполагает задание ставки НДС для каждого объекта учета в справочнике ТМЦ на листе «Обороты». Соответствующее значение может быть выбрано с помощью функции ВПР(). Мы не будем рассматривать способ задания формул в этом случае, и предлагаем читателю сконструировать их самостоятельно.

Расход

На листе «Расход» мы будем вводить сведения о движении материальных ценностей в соответствии с расходными документами. Так же, как и в случае с приходными документами рекомендуется разделять вводимые документы одной-двумя пустыми строками и предварять их указанием номеров и дат.