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

В ячейку A1 введите текст «Код», в B1 – «Наименование», а в C1 – «Кол-во».

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

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

Итак, в ячейку B2 листа «Расход» следует ввести формулу

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

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

Пример заполнения таблицы на листе «Расход» приведен на рис. 6.14.

Рисунок 6.14 – Пример ввода данных на листе «Расход».

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

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

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

Для этого дополним таблицу несколькими столбцами.

В колонки C и D будут вводиться сведения об остатках по каждому виду ТМЦ на начало периода в натуральном и стоимостном выражении, соответственно. Для первого периода они вводятся вручную, а в дальнейшем их значения просто переносятся из столбцов остатков на конец периода. В столбцах E и F будут рассчитываться итоги по приходу ТМЦ в натуральном (E) и стоимостном (F) выражении. В столбцах G и H будут рассчитываться итоги по расходу каждого вида ТМЦ, а в столбцах I и J остатки на конец периода также в натуральном и стоимостном выражении, соответственно. В столбце K будут рассчитываться средневзвешенные цены заготовления. Они являются базой для расчета стоимостной оценки расхода.

Мы разметили заголовок таблицы так, как показано на рис. 6.15.

Рисунок 6.15 – Разметка заголовка таблицы оборотной ведомости

Ничто не мешает присвоить колонкам другие наименования. При разметке заголовка в нашем примере мы исходили из того, что он должен быть «двухслойным». Поэтому он и размещен на двух строках. При этом центрирование надзаголовков «Остаток на начало», «Приход», «Расход», «Остаток на конец» получено путем выполнения операции «Центрирование по выделению» (ввести текст, выделить соседние столбцы, на которых он должен быть центрирован, Формат ® Ячейки, Выравнивание, Горизонтальное, По центру выделения, Ok). Заголовки во второй строке просто центрированы по горизонтали. Для дальнейшего изложения форматирование и название столбцов не имеет никакого значения.

В третью строку таблицы в ячейки D3, F3, H3 и J3 введем формулы для подсчета итогов по стоимостным показателям в соответствующих столбцах. Не забудьте указать диапазон суммирования, включающий все виды ТМЦ, а лучше – с запасом.

Теперь зададим основные расчетные формулы.

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

=СУММЕСЛИ(Приход!$A$2:$A$5000;$A5;Приход!C$2:C$5000)

С функцией СУММЕСЛИ() мы уже знакомы. В данном случае она выполняет суммирование данных в тех строках столбца C листа «Приход», у которых код ТМЦ совпадает с кодом из данной строки листа «Обороты» (A5). Поскольку в столбце C листа «Приход» мы вводим данные о поступлении ценностей в натуральном выражении в соответствии с приходными документами, то данная формула подсчитывает суммарный приход в натуральном выражении по их данному виду, задаваемому кодом в ячейке A5 листа «Обороты».

В ячейке F5 следует задать формулу:

=СУММЕСЛИ(Приход!$A$2:$A$5000;$A5;Приход!F$2:F$5000)

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

В ячейке G5 задаем формулу для расчета итогов по расходу данного вида ТМЦ в натуральном выражении. Она выглядит так:

=СУММЕСЛИ(Расход!$A$2:$A$5000;$A5;Расход!C$2:C$5000)

Все то же самое, но только выбираются и суммируются данные на листе «Расход».

Пропустим пока ячейки H5, I5, J5 и перейдем к ячейке K5. Как было указано выше, здесь должна рассчитываться средневзвешенная цена заготовления единицы ТМЦ данного вида. Для расчета этой величины мы должны сложить остаток на начало периода (D5) с приходом за период (F5) в стоимостном выражении и поделить полученный результат на сумму остатка на начало периода (C5) и прихода за период (E5) в натуральном выражении. В рамках соглашений о размещении показателей в нашей таблице эта формула выглядела бы так:

=(D5+F5)/(C5+E5)

Однако следует предусмотреть случай, когда C5+E5 = 0. Эта ситуация может возникнуть если еще не заданы ни количество входящего остатка, ни данные о приходе. Ведь мы же можем «размножать» формулы впрок, до ввода сведений о ТМЦ или приходных документов. В этом случае возникнет ситуация деления на ноль. В принципе, ничего страшного, но таблица примет весьма непрезентабельный вид и, кроме того «испортятся» итоги по колонкам со стоимостными показателями. Можете проверить это сами.

Поэтому мы зададим формулу для ячейки K5 в ином виде:

=ЕСЛИ(C5+E5>0;(D5+F5)/(C5+E5);0)

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

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

Для расчета себестоимости расхода в ячейку H5 введем формулу:

=G5*K5

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

Формулы для расчета остатков на конец периода чрезвычайно просты. Остаток в натуральном выражении (ячейка I5) определяется по формуле:

=C5+E5-G5

Она суммирует остаток на начало периода с приходом за период и вычитает из полученной величины расход за период.

Аналогично задается и формула для остатка в стоимостном выражении (ячейка J5): =D5+F5-H5

Теперь все основные формулы введены. Однако перед тем как копировать их в другие строки зададим форматирование ячеек остатков таким образом, чтобы в случае возникновения в них отрицательных величин они отображались красным цветом. Для этого нужно выделить ячейки I5 и J5, выбрать пункт меню Формат ® Ячейки, выбрать закладку «Число», в списке числовых форматов выбрать пункт «Числовой», далее перейти в поле «Отрицательные числа», пометить там один из примеров с отображением числа красным цветом и нажать клавишу Ok. Дело вкуса просто отображать отрицательные числа красным цветом или еще и выводить перед ними знак минус. Для нашего примера мы выбрали второй случай.

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

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

Рисунок 6.16 – Пример расчета оборотной ведомости

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

Формирование проводок

Если справочник – оборотная ведомость – содержит сведения о материальных ценностях, учитываемых на одном синтетическом счете, то проблемы формирования проводок не существует. Действительно, на листе «Обороты» мы имеем всю необходимую информацию для отражения движений по данному счету в синтетическом учете: ячейка F3 содержит стоимостной итог по поступлению ТМЦ за текущий период – дебетовый оборот счета, в ячейке H3 содержится итог по списанию себестоимости ТМЦ – кредитовый оборот счета. Для того, чтобы получить итоги по поступлению сумм НДС достаточно на листе «Приход» вставить формулу суммирования столбца G.

Мы рассмотрим более общий случай, когда справочник на листе «Обороты» включает сведения о ТМЦ разной экономической природы, учитываемых на разных счетах.

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

Введите в ячейку L1 текст «Счет» и отформатируйте столбец L так, чтобы все вводимые в него значения воспринимались как текстовые. Далее, для каждого вида ТМЦ проставьте код счета, на котором он учитывается.

В нашем примере мы ввели данные, приведенные на рис. 6.17.

Рисунок 6.17 – Справочник ТМЦ с включенными сведениями о счетах.

Теперь следует перейти на лист «Проводки».

В ячейку A1 введите текст «Дебет», в ячейку B1 – «Кредит», в ячейку C1 – «Сумма», а в ячейку D1 – «Комментарий».

Методика заполнения таблицы на этом листе состоит в следующем.

Задаются все проводки, которые так или иначе возникают в связи с движением материальных ценностей, занесенных в справочник на листе «Обороты». Дебетуемый счет проводки вводится в соответствующую строку столбца A, а кредитуемый счет – в столбец B. Формулы для расчета суммы проводки зависят от ее содержания.

Формула для расчета суммы «приходной» проводки имеет следующий вид:

=СУММЕСЛИ(Обороты!$L$4:$L$2000;A2;Обороты!$F$4:$F$2000)

Она суммирует значения из столбца F (приход в стоимостном выражении) листа «Обороты» по тем строкам, в столбце L которых содержится код счета, совпадающий с кодом счета, дебетуемым в данной проводке (A2).

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

Формулы расчета сумм «расходных» проводок, то есть тех, в которых кредитуется тот или иной счет учета материальных ценностей, составляются по идентичным правилам, но в качестве критерия отбора (второй аргумент функции СУММЕСЛИ) используется ссылка на ячейку из столбца B, а суммирование производится по столбцу H. Например, в рассматриваемом нами примере, для строки 5 формула будет выглядеть следующим образом:

=СУММЕСЛИ(Обороты!$L$4:$L$2000;B5;Обороты!$H$4:$H$2000)

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

Итак, в колонке M листа «Обороты» будем подсчитывать итоги по НДС, полученному при поступлении каждого вида материальных ценностей. Введем в ячейку M1 текст «НДС», в ячейку M3 формулу подсчета итогов по колонке =СУММ(M4:M2000), а в ячейку M5 формулу подсчета итогов по суммам НДС, поступившим с данным видом ТМЦ:

=СУММЕСЛИ(Приход!$A$2:$A$5000;A5;Приход!$G$2:$G$5000)

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

Теперь можно на листе «Проводки» задать формулы для расчета сумм проводок по учету НДС, поступившего с материальными ценностями. Если на счете 19.1 учитывается НДС, поступивший с товарами, то для проводки Д19.1 К60 формула для расчета суммы может быть записана в следующем виде:

=СУММЕСЛИ(Обороты!$L$4:$L$2000;»41»;Обороты!$M$4:$M$2000)

Ее отличие от ранее рассмотренных формул состоит в том, что она суммирует числа в столбце M листа «Обороты», а критерий поиска задан в виде константы – кода счета «41». Данная формула суммирует НДС для товаров, то есть тех строк справочника на листе «Обороты», в которых проставлен код счета 41. Аналогично следует построить формулы и для материальных ценностей, учитываемых на других счетах.

В условиях нашего примера были получены результаты, представленные на рис. 6.18.

Рисунок 6.18 – Автоматически сформированные проводки по движению материальных ценностей и смежным операциям

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

Переход к следующему периоду

Переход к следующему периоду осуществляется по методике уже известной нам по предыдущей главе. Рабочая книга копируется в новый файл и в нем значения остатков материальных ценностей на конец периода стандартными средствами Excel переносятся в колонку остатков на начало периода как значения (но не как формулы!). Далее, из новой рабочей книги удаляется вся оперативная информация, относящаяся к предшествующему периоду (данные листов «Приход» и «Расход»). В результате обороты обнуляются, итоги на начало периода становятся равными итогам на конец предшествующего периода и цикл операций повторяется заново для текущего периода.

Обработка проводок

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

Суть предлагаемого подхода состоит в следующем.

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