Файл: Автоматизация расчетов в бухгалтерском учете с помощью MS Excel.docx
Добавлен: 25.10.2018
Просмотров: 1585
Скачиваний: 9
Кроме того, границы диапазонов заданы в смешанной форме, при которой используется абсолютная адресация строк начала интервалов. Это нужно для того, чтобы при копировании формулы для других проводок, границы интервалов оставались бы неизменными. Более подробно о порядке адресации ячеек см. справочную систему Excel. Выбор нижней границы интервалов просмотра и суммирования функции СУММЕСЛИ() лучше установить равным интервалу подсчета итогов на листе «Картотека».
Скопируйте формулу во все строки столбца C, содержащие проводки. Их суммы будут автоматически рассчитаны.
Для контроля правильности задания формул расчета сумм проводок и кодов счетов отнесения износа в столбце C под проводками разместите формулу расчета суммы по колонке. Полученный здесь итог должен совпасть с итогом по колонке F («Износ за месяц») листа «Картотека» (рис. 6.7).
Рисунок 6.7 – Данные и формулы листа «Проводки» расчетной таблицы
Если вы сопоставите данные нашего примера, приведенные на этом и предыдущем рисунках, то принцип действия функции СУММЕСЛИ станет окончательно ясен.
Теперь изменяя данные в картотеке, мы можем не заботиться о пересчете сумм проводок. Единственное о чем надо помнить, так это о том, что при использовании новых кодов счетов отнесения износа на листе «Картотека» для них нужно определять макеты проводок на листе «Проводки», вставляя новые строки, записывая в них корреспонденции счетов и копируя формулы расчета сумм проводок.
Переход к следующему периоду
Для перехода к новому периоду необходимо выполнить следующие действия.
Закройте файл рабочей книги с данными текущего месяца, если он открыт.
Скопируйте его, присвоив другое имя. В условиях нашего примера мы назвали исходный файл 1998-11.xls. Поэтому новому файлу логично дать имя 1998-12.xls.
Загрузите новый файл в Excel.
Теперь требуется перенести значения колонки «Износ на конец периода» (G) в колонку «Износ на начало периода» (D). Отметим, что нам нужно переносить только полученные значения, а не формулы, по которым рассчитывается износ на конец периода.
Для этого выделите блок ячеек, содержащий искомые значения. В нашем примере это диапазон G3:G8. Скопируйте его в буфер промежуточного хранения (Правка Копировать). Вокруг выделенного блока появится «бегущая» пунктирная линия.
Установите активной ячейку D3. Выберите пункт меню «Правка - Специальная вставка». В окне параметров специальной вставки пометьте пункт «Значения» и нажмите клавишу OK (рис. 6.8).
Рисунок 6.8 – Установка параметров специальной вставки при установке значений износа на начало нового периода
Данные на конец прошлого периода будут перенесены в колонку данных об износе на начало периода. После выполнения переноса сработают расчетные формулы и будут рассчитаны новые значения износа за период и износа на конец периода (рис. 6.9).
Рисунок 6.9 – Расчетная таблица после установки значений износа на начало периода
Теперь можно вносить в картотеку изменения: вводить записи о вновь поступивших основных средствах, не забывая устанавливать для них нулевую норму амортизации для первого месяца эксплуатации, удалять записи о выбывших в прошлом периоде объектах, устанавливать значения норм амортизации для объектов, поступивших в прошлом месяце, корректировать нормы амортизации и т.д. Значения износа и суммы проводок будут пересчитываться автоматически.
Рассмотренная технология ведения учета основных средств с использованием Microsoft Excel технология имеет несколько достаточно существенных ограничений.
Во-первых, не поддерживается разделение основных средств по подразделениям и материально-ответственным лицам с получением соответствующих итогов. Для преодоления этого ограничения данные различных подразделений можно размещать на разных листах рабочей книги, а итоговый свод получать функцией консолидации данных на отдельном листе или иными способами.
Во-вторых, не поддерживается расчет износа по автотранспортным средствам в зависимости от их пробега. Эта задача также допускает довольно простое решение. В основную расчетную таблицу можно ввести еще одну колонку, в которую будет проставляться пробег за текущий период. При этом следует модифицировать формулу расчета технологической колонки H, названной нами «Износ по норме» таким образом, чтобы для тех строк, где пробег задается (соответствующая ячейка не пуста), расчет выполнялся бы по формуле учитывающей пробег, а для других строк (там, где пробег не указывается) – по обычным правилам. В этом случае для расчета следует использовать функцию ЕСЛИ(), а для проверки наличия значения в колонке пробега можно применить функцию ЕПУСТО(), проверяющую задано ли значение в той или иной ячейке (см. справочную систему Excel).
В-третьих, рассматривается только линейный способ начисления износа. Эта проблема также легко преодолевается корректировкой формул расчета технологической колонки H. В ряде случаев может потребоваться ввод дополнительной колонки, где может быть проставлен срок эксплуатации или другие необходимые для расчета параметры.
Автоматизация базовых расчетов по учету материальных ценностей
Рассмотрим простейшую технологию ведения учета товарно-материальных ценностей (ТМЦ) на основе MS Excel. Она состоит в следующем.
В рабочей книге Excel ведутся четыре отдельных листа.
На первом отражаются основные сведения из фактурной части приходных документов: коды и наименования поступающих материальных ценностей, их количество и стоимость. На втором листе вводятся данные расходных документов. Третий лист содержит справочник материальных ценностей, совмещенный с оборотной ведомостью, в которой автоматически рассчитываются обороты и исходящие остатки по каждому наименованию в натуральной и стоимостной форме. Стоимостные данные по расходу оцениваются по средневзвешенным ценам, которые также отображаются на листе оборотной ведомости и автоматически пересчитываются при вводе приходных документов и внесении изменений в остатки на начало периода. Четвертый лист предназначен для формирования проводок по отраженным в таблицах операциям.
При переходе к следующему периоду рабочая книга копируется в новый файл и в нем значения остатков на конец предшествующего периода стандартными средствами 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 автоматически вычислял сумму без НДС, оставляя ее без изменений в тех случаях, когда введенная сумма не включает НДС.