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

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

Подготовительные действия выполнены. Наша таблица приняла вид, показанный на рис. 6.2.

Рисунок 6.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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рисунок 6.6 – Расчетная таблица с установленными кодами счетов отнесения износа

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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