4. Организация решения учетных задач средствами Microsoft Excel

Рассмотрим организацию решения учетных задач средствами Microsoft Excel на примерах 12 и 13.

 Пример 12

Рассмотрим технологию решения задачи начисления износа по основным средствам предприятия. Выходной информацией является документ "Ведомость расчета амортизационных начислений основных средств". Нормативно-справочная информация содержится в справочниках основных средств и норм амортизационных отчислений (Справочник 1, Справочник 2). Для получения ведомости автоматизированным способом достаточно будет ввести только инвентарный номер объекта. Для решения задачи выполним следующие действия:

1. Создадим таблицу, показанную на рис. 21. Переименуем рабочий лист, присвоив ему имя Ведомость.

Рис. 21. Исходные данные

2. Спроектируем таблицы, предложенные на рис. 22 и 23. Соответственно присвоив имена рабочим листам Справочник ОС и Справочник Норм.

Рис. 22. Справочник ОС

Рис. 23. Справочник норм

4. Присвоим имена столбцам справочников. Для этого выполним для каждого справочника следующее:

 выделим заголовки столбцов и их содержание, т. е. всю таблицу справочника;

 выполним команду Вставка/Имя/Создать/В строке выше/ОК;

 просмотрим содержимое Поля имен и убедимся, что имена присвоены верно.

5. Введем формулы для расчета ведомости, указанные в табл. 11.

Примечание. Итоги по таблице Ведомость вынесены на верх таблицы (хотя это и непривычно) для того, чтобы они были видны сразу после ее загрузки. Кроме того, так будет удобнее пополнять картотеку новыми записями; в этом случае их можно просто вписывать в пустую ближайшую строку. Диапазон суммирования в нашем примере ограничивается строкой 1000.

Таблица 11. Формулы для заполнения ячеек рабочего листа Ведомость

Пояснения к формулам

Для лучшего понимания последовательности расчетов начнем со столбца Н. Данные столбца мы используем только для того, чтобы упростить ввод формул в столбцах F и G.

Введем в ячейку Н4 формулу =С4*Е4/1200.

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

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

Для корректного выполнения расчета износа за текущий период в ячейку F4 введем формулу

=ЕСЛИ(D4+Н4>C4;C4-D4;H4)

Она означает, что если износ на начало периода (D4), сложенный с износом, рассчитанным в соответствии с нормой амортизации (Н4), больше стоимости данного объекта учета, то износ за текущий период может быть начислен в сумме, не превышающей его остаточной стоимости (С4–D4). В противном случае износ за месяц составляет величину, рассчитанную в соответствии с нормой амортизации (Н4).

Так как износ на конец периода равен износу на начало периода, сложенному с износом за период, то в ячейку G4 введем формулу

=D4+F4

6. Введем значения реквизита "Инвентарный номер ОС". Данные вводятся по строкам. По мере ввода исходных данных происходит формирование ведомости. Полученный результат сверим с рис. 24.

Рис. 24. Итоговая таблица

 Пример 13

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

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

1. В таблицу Ведомость внесем корреспондирующие счета в ячейки I4:I9, соответственно: 26, 26, 26, 25, 26, 25. Зададим текстовый формат ячейкам данного диапазона.

2. Создадим на новом рабочем листе таблицу (рис. 26), присвоив имя рабочему листу Проводки.

Рис. 25. Исходные и результатные данные листа Проводки

3. Зададим текстовый формат столбцам А и В.

4. Введем номера дебетуемых счетов (25, 26) и кредитуемым счетом будет 02.

5. В ячейку С2 листа Проводки впишем формулу

=СУММЕСЛИ(Ведомость!I4:I9;B3;Ведомость!F4:F9)

Синтаксис функции: =СУММЕСЛИ(интервал просмотра, критерий, суммируемый интервал).

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

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

Суммируемый интервал — представляет собой ячейки, из которых выбираются данные для суммирования. При этом они суммируются только тогда, когда ячейки из интервала просмотра удовлетворяют условию, заданному критерием. В данном случае функция =СУММЕСЛИ() суммирует числа из диапазона F4:F1000 листа Ведомость в том случае, если значение ячейки А2 листа Проводки совпадает с соответствующим значением интервала поиска I4:I1000.

Содержательно это означает, что суммируются величины износа за месяц (колонка F) по тем объектам, у которых счет отнесения износа (колонка I) совпадает со счетом, дебетуемым в данной проводке.

Итог по столбцу С листа Проводки должен совпадать с итогом по столбцу F Ведомости.

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