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

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

 Пример 12

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

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

	A	B	C	D	E	F	G	H	I
1	Ведомость расчета амортизационных отчислений основных средств
2	Инвентарный номер	Наименование основного средства	Стоимость	Износ на начало периода	Норма амортизации (% в год)	Износ за период	Износ на конец периода	Износ по норме	Счет отнесения износа
3	Итоги
4
5
6
7
8
9
10
11
12
13
14
15
16

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

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

	А	В	С
1	Инвентарный номер	Наименование основных средств	Износ на начало периода
2	101	Компьютер	6000
3	102	Факс	4700
4	103	Проектор	1000
5	104	Вязальная машина	0
6	105	Телевизор	1000
7	106	Испытательный стенд	95000

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

1	А	В	C	D	E
2	Инвентарный номер-1	Наименование основных средств	Стоимость	Срок эксплуатации	Норма амортизации
3	101	Компьютер	12000	10	50
4	102	Факс	4800	8	30
5	103	Проектор	3600	10	20
6	104	Вязальная машина	20000	10	12
7	105	Телевизор	2400	5	20
8	106	Испытательный стенд	96000	7	0

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

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

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

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

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

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

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

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

Ячейка	Формула	Формула скопирована в ячейки
С3	=СУММ(C5:C1000)	D3, F3, G3
B4	=ПРОСМОТР(A4:A9;Справочник1!A2:A7;Справочник1!B2:B7)	B5:B1000
C4	=ПРОСМОТР(A4;Справочник2!A2;Справочник2!C2)	C5:C1000
D4	=ПРОСМОТР(A4;Справочник1!A2;Справочник1!C2)	D5:D1000
E4	=ПРОСМОТР(A4;Справочник2!A2;Справочник2!E2)	E5:E1000
H4	=C4*E4/1200	H5:H1000
F4	=ЕСЛИ(D4+H4>C4;C4-D4;H4)	F5:F1000
G4	=D4+F4	G5:G1000

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

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

	A	B		C		D		E		F	G	H	I
1	Ведомость расчета амортизационных отчислений основных средств
2	Инвентарный номер	Наименование основного средства	Стоимость		Износ на начало периода		Норма амортизации (% в год)		Износ за период		Износ на конец периода	Износ по норме	Счет отнесения износа
3	Итоги		126800		56700				400		57100
4	101	Компьютер	12000		6000		50		500,00		6500	500	26
5	102	Факс	4800		4700		30		100,00		4800	120	26
6	103	Проектор	3600		1000		20		60,00		1060	60	26
7	104	Вязальная машина	20000		0		12		200,00		200	200	25
8	105	Телевизор	2400		1000		20		40,00		1040	40	26
9	106	Испытательный стенд	96000		50000		0		0,00		50000	0	25
10		0	0		0
11		0	0		0
12		0	0		0
13		0	0		0
14		0	0		0
15		0	0		0
16		0	0		0

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

 Пример 13

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

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

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

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

	A	B	C
1	Дебетуемый счет	Кредитуемый счет	Сумма
2	25	2	1200
3	26	2	700

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

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

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

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

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

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

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

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

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

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

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

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