Файл: Автоматизация расчетов в бухгалтерском учете с помощью MS Excel.docx
Добавлен: 25.10.2018
Просмотров: 1583
Скачиваний: 9
Добавим к таблице на листе «Приход» три колонки.
В ячейку 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 – Пример реализации подхода с явным заданием ставки НДС для каждой строки приходных документов.
Помимо рассмотренного только что подхода с явным указанием ставки НДС может быть реализовано и другое решение, которое предполагает задание ставки НДС для каждого объекта учета в справочнике ТМЦ на листе «Обороты». Соответствующее значение может быть выбрано с помощью функции ВПР(). Мы не будем рассматривать способ задания формул в этом случае, и предлагаем читателю сконструировать их самостоятельно.
Расход
На листе «Расход» мы будем вводить сведения о движении материальных ценностей в соответствии с расходными документами. Так же, как и в случае с приходными документами рекомендуется разделять вводимые документы одной-двумя пустыми строками и предварять их указанием номеров и дат.
В ячейку 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.