Учет основных средств с использованием MS Excel

Размещено на http://www.allbest.ru/

1. Автоматизация расчетов по учету основных средств

Технология учета основных средств с использованием MS Excel предполагает выполнение следующих действий.

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

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

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

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

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

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

Естественно, рассматриваемая технология имеет определенные ограничения. Однако она достаточно проста, вполне работоспособна и может использоваться во многих относительно небольших предприятиях.

1.1 Основная расчетная таблица

Для создания основной расчетной таблицы выполните следующие действия.

Создайте новую рабочую книгу Excel.

В ячейку A1 введите текст “Инвентарный номер”.

В ячейку B1 - “Наименование основного средства”.

В ячейку C1 - “Стоимость”.

В ячейку D1 - “Износ на начало текущего периода”.

В ячейку E1 - “Норма амортизации (% в год)”.

В ячейку F1 - “Износ за период”.

В ячейку G1 - “Износ на конец периода”.

В ячейку H1 - “Износ по норме”.

Пояснения по содержанию колонок будут даны позже, а их названия могут быть выбраны по вашему усмотрению.

Выделите ячейки A1:H1, отформатируйте их по своему вкусу и установите удобную для просмотра информации ширину колонок. В нашем примере мы выбрали режим форматирования со следующими параметрами выравнивания (Формат ® Ячейки, вкладка “Выравнивание”): горизонтальное - по центру, вертикальное - по центру, а также установили флажок “Переносить по словам” (Рис. 1).

Рис. 1. Установка параметров выравнивания текста в заголовке расчетной таблицы.

Далее, в ячейку A2 введите текст “Итоги”.

В ячейку C2 - формулу =СУММ(C3:C1000)

В ячейку D2 - формулу =СУММ(D3:D1000)

В ячейку F2 - формулу =СУММ(F3:F1000)

В ячейку G2 - формулу =СУММ(G3:G1000)

Поскольку формулы однотипные, то можно ввести формулу только в ячейку C2, а в другие просто скопировать ее - Excel автоматически подставит идентификаторы нужных колонок. В указанных ячейках будут суммироваться значения по стоимости, износу на начало периода, износу за период и износу на конец периода по всем учитываемым объектам. Диапазон суммирования в нашем примере ограничивается строкой 1000. Если на предприятии более 1000 объектов основных средств, то следует установить большее значение, если меньше - то меньшее. Однако мы настоятельно советуем указывать диапазон с “запасом”, чтобы впоследствии, при пополнении таблицы строками уже не думать, попадают ли нужные показатели в итоги.

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

Подготовительные действия выполнены.

Наша таблица приняла вид показанный на рис. 2.



Рис. 2. Расчетная таблица с установленными формулами расчета итогов.

Теперь можно вводить данные по строкам. В каждой строке должна быть сосредоточена необходимая информация по одному объекту учета. Начнем со строки 3.

Порядок заполнения колонок A (“Инвентарный номер”), B (“Наименование основного средства”) и C (“Стоимость”) очевиден и в комментариях не нуждается.

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

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

Следующие три колонки являются расчетными и содержат формулы.

Для лучшего понимания последовательности расчетов начнем с колонки H (“Износ по норме”). Данная колонка - чисто технологическая и мы используем ее только для того, чтобы упростить ввод формул в колонках F и G. Введите в ячейку H3 формулу:

=C3*E3/1200

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

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

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

=ЕСЛИ(D3+H3>C3;C3-D3;H3)



Она означает, что если износ на начало периода (D3), сложенный с износом, рассчитанным в соответствии с нормой амортизации (H3), больше стоимости данного объекта учета, то износ за текущий период может быть начислен в сумме, не превышающей его остаточной стоимости (C3-D3). В противном случае износ за месяц составляет величину, рассчитанную в соответствии с нормой амортизации (H3). Более подробно об использовании функции ЕСЛИ() см. справочную системуExcel.

Износ на конец периода равен износу на начало периода, сложенному с износом за период. Поэтому в ячейку G3 следует ввести формулу:

=D3+F3

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

Рис. 3. Запись о первом объекте картотеки основных средств.

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

Для этого выделите ячейки F3:H3 и далее “уцепитесь” мышкой за правый нижний угол выделенной области. “Мышиный курсор” должен принять форму черного креста. Теперь “потяните” мышь вниз на столько строк, сколько у Вас есть основных средств (один объект - одна строка). Не повредит “размножить” формулы с запасом.

Все. Таблица готова. Остается только перенести в нее данные картотеки (рис. 4.). Поскольку формулы скопированы с “запасом”, в тех строках где не введены необходимые данные, соответствующие ячейки имеют нулевое значение.

Рис. 4. Пример заполнения расчетной таблицы данными картотеки основных средств.

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



2. Автоматизация составления проводок по начислению износа

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

В заголовке (1-ая строка) колонки I напишите “Счет отнесения износа” и перенесите на него формат заголовка таблицы. Отформатируйте эту колонку I так, чтобы все введенные в нее значения воспринимались как текстовые.

Для этого выделите колонку I, вызовите карточку настройки форматов ячеек Excel (Формат - Ячейки), выберите раздел “Число”, в списке форматов выберите строку “Текстовый” и нажмите клавишу OK (рис. 5). Установите для данного столбца выравнивание по центру.

Рис. 5. Установка форматирования для колонки ввода счетов отнесения износа.

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

Для каждого объекта учета (строки) укажите коды счетов, на которые должен быть отнесен износ.

Переименуйте “Лист 1” в “Картотека” (Формат ? Лист ? Переименовать), потом перейдите на “Лист 2” и переименуйте его в “Проводки”.

В ячейку A1 листа “Проводки” впишите текст “Дебетуемый счет”, в ячейку B1 - “Кредитуемый счет”, а в ячейку C3 - “Сумма”. Отформатируйте колонки A и Bтак, чтобы все вводимые в них значения воспринимались как текстовые и размещались по центру.

Начиная со строки 2 впишите все проводки, которые используются при начислении износа. В колонке A - “Дебетуемый счет”, должны быть перечислены все счета, которые хоть один раз были указаны в колонке I листа “Картотека”. В противном случае итоги по износу за месяц и итоги по проводкам не сойдутся.

В ячейку C2 листа “Проводки” впишите формулу:

=СУММЕСЛИ(Картотека!I$3:I$1000;A2;Картотека!F$3:F$1000)

Функция СУММЕСЛИ() суммирует ячейки, специфицированные заданным критерием.

В общем виде она имеет следующую структуру:

СУММЕСЛИ(интервал_просмотра; критерий; суммируемый_интервал)

Интервал просмотра - это интервал ячеек, среди которых осуществляется поиск определенной информации.

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

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

Более подробно о функции СУММЕСЛИ см. cправочную систему Excel.

В нашем случае функция СУММЕСЛИ() суммирует числа из диапазона F3:F1000 листа “Картотека” в том случае, если значение ячейки A2 листа “Проводки” совпадает с соответствующим значением интервала поиска I3:I1000. Содержательно это означает, что суммируются величины износа за месяц (колонка F) по тем объектам, у которых счет отнесения износа (колонка I) совпадает со счетом, дебетуемым в данной проводке (A2).

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

Кроме того, границы диапазонов заданы в смешанной форме, при которой используется абсолютная адресация строк начала интервалов. Это нужно для того, чтобы при копировании формулы для других проводок, границы интервалов оставались бы неизменными. Более подробно о порядке адресации ячеек см. справочную систему Excel. Выбор нижней границы интервалов просмотра и суммирования функции СУММЕСЛИ() лучше установить равным интервалу подсчета итогов на листе “Картотека”.

Скопируйте формулу во все строки столбца C, содержащие проводки. Их суммы будут автоматически рассчитаны.

Для контроля правильности задания формул расчета сумм проводок и кодов счетов отнесения износа в столбце C под проводками разместите формулу расчета суммы по колонке. Полученный здесь итог должен совпасть с итогом по колонке F (“Износ за месяц”) листа “Картотека”.

Теперь изменяя данные в картотеке мы можем не заботиться о пересчете сумм проводок. Единственное о чем надо помнить, так это о том что при использовании новых кодов счетов отнесения износа на листе “Картотека” для них нужно определять макеты проводок на листе “Проводки”, вставляя новые строки, записывая в них корреспонденции счетов и копируя формулы расчета сумм проводок.

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

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

Закройте файл рабочей книги с данными текущего месяца, если он открыт.

Скопируйте его, присвоив другое имя. В условиях нашего примера мы назвали исходный файл 1998-11.xls. Поэтому новому файлу логично дать имя 1998-12.хls.

Загрузите новый файл в Excel.

Теперь требуется перенести значения колонки “Износ на конец периода” (G) в колонку “Износ на начало периода” (D). Отметим, что нам нужно переносить только полученные значения, а не формулы, по которым рассчитывается износ на конец периода.

Для этого выделите блок ячеек, содержащий искомые значения. В нашем примере это диапазон G3:G8. Скопируйте его в буфер промежуточного хранения (Правка ? Копировать). Вокруг выделенного блока появится “бегущая” пунктирная линия.

Установите активной ячейку D3. Выберите пункт меню “Правка - Специальная вставка”. В окне параметров специальной вставки пометьте пункт “Значения” и нажмите клавишу OK (рис. 8).



Рис. 8. Установка параметров специальной вставки при установке значений износа на начало нового периода.

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

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

Рассмотренная технология ведения учета основных средств с использованием Microsoft Excel технология имеет несколько достаточно существенных ограничений.

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

Во-вторых, не поддерживается расчет износа по автотранспортным средствам в зависимости от их пробега. Эта задача также допускает довольно простое решение. В основную расчетную таблицу можно ввести еще одну колонку, в которую будет проставляться пробег за текущий период. При этом следует модифицировать формулу расчета технологической колонки H, названной нами “Износ по норме” таким образом, чтобы для тех строк, где пробег задается (соответствующая ячейка не пуста), расчет выполнялся бы по формуле учитывающей пробег, а для других строк (там, где пробег не указывается) - по обычным правилам. В этом случае для расчета следует использовать функции ЕСЛИ(), а для проверки наличия значения в колонке пробега можно применить функцию ЕПУСТО(), проверяющую задано ли значение в той или иной ячейке (см. справочную систему Excel).

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



3. Автоматизация базовых расчетов по учету материальных ценностей

Рассмотрим простейшую технологию ведения учета товарно-материальных ценностей (ТМЦ) на основе MS Excel. Она состоит в следующем.

В рабочей книге Excel ведутся четыре отдельных листа.

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

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

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

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

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

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

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

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

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

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

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

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

3.2 Приход

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

В ячейку 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 - о совокупной стоимости их партии в соответствии с приходным документом. Для корректного выполнения расчетов единицы измерения по документам должны быть приведены к тем же, которые предполагаются в справочнике.

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

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

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

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

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

Для решения поставленной задачи мы модифицируем таблицу приходов таким образом, чтобы при необходимости 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 их следует скопировать на весть диапазон ввода приходных документов. Пример построения таблицы с автоматическим отделением НДС приведен на рис. 12.

Рис. 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)

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

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

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

3.4 Расход

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

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

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

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

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

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



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

Пример заполнения таблицы на листе “Расход” приведен на рис. 14.

Рис. 14. Пример ввода данных на листе “Расход”.

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

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

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

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

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

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

Рис. 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. Дело вкуса просто отображать отрицательные числа красным цветом или еще и выводить перед ними знак минус. Для нашего примера мы выбрали второй случай.

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

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

Рис. 16. Пример расчета оборотной ведомости.

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



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

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

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

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

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

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

В ячейку 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)

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

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

Размещено на Allbest.ru

...