Технологии организации решения финансово-экономических задач в табличном процессоре EXCEL (Л. Еремин, "Финансовая газета. Региональный выпуск", N 19, 20, май 2006 г.)

Технологии организации решения финансово-экономических задач
в табличном процессоре EXCEL

В последнее время предъявляются качественно новые требования к организации и проведению учебных занятий по подготовке специалистов в сфере финансово-экономического управления. С 1985 г. в Финансовой академии при Правительстве Российской Федерации в программу обучения преподавателей финансово-экономических специальностей вузов - слушателей института повышения квалификации (ИПКП) включен курс, посвященный освоению компьютерных технологий и их использованию при подготовке и проведении учебных занятий. Сейчас через систему ИПКП по программе курса "Современные информационные технологии в обучении" (СИТО) ежегодно повышают свое компьютерное образование примерно от 100 до 120 преподавателей вузов нашей страны и стран СНГ.

Естественно, что за истекшие 20 лет характер обучения преподавателей компьютерным технологиям претерпел значительные изменения. Это связано даже не столько с появлением новых программно-инструментальных средств, используемых преподавателями финансово-экономических дисциплин в своей профессиональной деятельности, сколько с возросшим уровнем их компьютерных знаний и навыков и, как следствие, повышением их требований к содержанию программы учебного курса по информационным технологиям. При этом среди преподавателей отмечается резкая дифференциация по уровню их компьютерных знаний и навыков работы. С одной стороны, в настоящее время большинство слушателей ИПКП достаточно грамотно используют возможности информационных технологий в своей профессиональной деятельности, демонстрируют хорошие навыки работы за компьютером, с другой - все еще приходится встречаться с отдельными преподавателями (до 2-3 человек в группе), вообще не умеющими работать за компьютером.

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

Естественно, все это предъявляет повышенные требования к уровню компьютерной подготовки преподавателей вузов, максимальному использованию ими возможностей современных информационных технологий. Кроме того, уровень владения компьютером оказывает огромное влияние и на имидж преподавателя в глазах студенческой аудитории, особенно если он, не являясь профессионалом в компьютерной сфере, демонстрирует знания и навыки высококвалифицированного пользователя. Понимая это, слушатели ИПКП на занятиях в компьютерных классах Финансовой академии стремятся овладеть эффективными технологическими приемами организации решения финансово-экономических задач.

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

расчетов амортизации для целей бухгалтерского учета и налогообложения;

расчета налогооблагаемой прибыли;

автоматизации расчетов с персоналом;

получения расчетов для анализа хозяйственной деятельности, финансовых результатов и финансового состояния организации.

Excel широко распространен среди различных пользователей. Табличный процессор Excel благодаря:

простоте подготовки различного рода экономических документов (по принципу работы с "пустографкой");

наличию большого числа встроенных функций (математических, статистических, финансовых и т.п.);

возможности "проигрывания" различных вариантов решения задач и выбора лучшего из них (за счет быстрого автоматического пересчета конечных результатов при любом изменении исходных данных);

более наглядному представлению результатной информации, за счет его поддержки средствами графической интерпретации;

возможности обмена информацией с различными программными средами общего и специального назначения и т.п.

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

Следует отметить, что функция "если" досталась пользователям табличных процессоров в наследие от программирования задач на высокоуровневых языках, в которых эта конструкция имеет следующий вид:

IF - THEN - ELSE (если "условие" соблюдается, то выполняется "действие-1", иначе выполняется "действие-2")

и реализуется в табличном процессоре по схеме:

= ЕСЛИ (условие; выражение-1; выражение-2)

          |                       

          |                 |      |

          | условие         |      |

          |-----------------/      |

          |соблюдается             |

          \------------------------/

           условие не соблюдается

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

Однако часто эффективные компьютерные решения таких задач могут вообще быть не связанными с какой-либо алгоритмической подготовкой пользователей электронных таблиц, а опираться исключительно на знания элементарной математики. Например, если в задаче для каких-либо целей (например, группировки или выборки информации) требуется определение номера квартала по хранящемуся номеру i-месяца, то можно использовать математическую функцию "целое", так как номер квартала = целое ((номер месяца + 2) / 3).

Для выделения фрагмента числа, например 2 старших разрядов из 6-значного табельного номера, отражающих код отдела, в котором работает сотрудник, можно воспользоваться математической формулой: код отдела = целое (табельный номер / 10000).

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

Кроме того, часто умение вникнуть во внутреннюю сущность поставленной задачи, понимание ее экономического смысла и благодаря этому умение по-иному представить исходную информацию для ее решения, позволяют выбрать более простой и эффективный способ компьютерной реализации задачи. Так, ранее были опубликованы материалы*(2), демонстрирующие, как в результате использования различных форм интерпретации матричного представления исходной, промежуточной и результатной информации решение задачи расчета страховых резервов вместо сложных процедур (с элементами рекуррентного расчета), изложенных в работе*(3), свелось к использованию всего двух встроенных математических функций табличного процессора Excel: "сумм" и "суммесли".

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

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

Другие не сложные, но весьма эффективные технологические приемы по автоматизации решения финансово-экономических задач, ориентированные на конечных пользователей - непрофессионалов в области компьютерных технологий, можно рассмотреть на примере компьютерной реализации достаточно распространенной типовой задачи, ориентированной на работу со справочниками и приведенной в книге С.М. Лавренова*(5). Здесь в качестве примера приведен расчет ставки подоходного налога (табл. 1).

Таблица 1

Размер облагаемого совокупного дохода, полученного в 1998 г.	Сумма налога
До 20000 руб.	12%
От 20001 руб. до 40000 руб.	2400 руб. + 15% с суммы, превышающей 20000 руб.
От 40001 руб. до 60000 руб.	5400 руб. + 20% с суммы, превышающей 40000 руб.
От 60001 руб. до 80000 руб.	9400 руб. + 25% с суммы, превышающей 60000 руб.
От 80001 руб. до 100000 руб.	14400 руб. + 30% с суммы, превышающей 80000 руб.
От 100001 руб. и выше	20400 руб. + 35% с суммы, превышающей 100000 руб.

Табл. 1 можно проиллюстрировать графически (см. рисунок).

"Графическая иллюстрация процедуры расчета отчислений в условиях варьируемой шкалы процентных ставок: рисунок 1"

"Графическая иллюстрация процедуры расчета отчислений в условиях варьируемой шкалы процентных ставок: рисунок 2"

На рисунке даны следующие обозначения:

yi - размер отчислений с суммы xi, рассчитанный в соответствии с тарификационной таблицей;

xi - значение суммы (в пределах i-интервала тарификационной таблицы), для которой рассчитывается размер отчисления;

ai - значение нижней границы i-интервала (равно минимальному значению xi на i-интервале);

вi - коэффициент отчисления, действующий в пределах i-интервала, (%);

ki- минимальное значение отчисления для i-интервала.

Типовое решение подобных задач в соответствии с экономико-математической моделью имеет вид:

    /---------------------------------\

    |yi = ki + Дki = ki + (xi - ai)вi.|

    \---------------------------------/

В данном случае

     Размер налога = Константа + Процентная ставка х (Облагаемая сумма  -

Значение налоговой шкалы)

сводится к трем обращениям к справочной таблице:

для выбора значения "константы";

для определения размера "процентной ставки";

для определения значения "налоговой шкалы".

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

На основании приведенных данных в табл. 1 далее предлагается построить справочную таблицу "ставка" (табл. 2) и настроить табличный процессор Excel на расчет размера налога с помощью встроенных функций ВПР в соответствии с ее данными.

Таблица 2

	G	H	K
1	1	0	12%
2	20001	2400	15%
3	40001	5400	20%
4	60001	9400	25%
5	80001	14400	30%
6	100001	20400	35%

Однако такое прямолинейное решение задачи, как это представлено, например, у С.М. Лавренова, не только приводит к усложнению расчетной формулы в D1:

     D1 = ВПР (С1, ставка, 2) + ВПР (С1, ставка, 3)  х  (С1  -  ВПР  (С1,

ставка, 1) + 1),

но и делает реализацию задачи менее гибкой.

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

Таким образом, вместо ввода константы 2400, как это сделано у С.М. Лавренова, в Н2 следует ввести формулу:

    /-------------------------\

    |Н2 = (G2 - G1) х K1 + H1 |,

    \-------------------------/

а затем скопировать ее в соответствующие строки справочной таблицы (табл. 3).

Таблица 3

	G	H	K
1	0	0	12%
2	20000	= (G2 - G1) * K1 + H1	15%
3	40000	5400	20%
4	60000	9400	25%
5	80000	14400	30%
6	100000	20400	35%

Во-вторых, изменив граничные значения налоговой шкалы в столбце G на 0 вместо 1, 20000 вместо 20001 и т.д., как это представлено в табл. 3, можно упростить формулу расчета налогов в D1, убрав из нее последнее лишнее слагаемое, т.е. "1":

     D1 = ВПР (С1, ставка, 2) + ВПР (С1,  ставка,  3)  х  (С1  -  ВПР (С1,

ставка, 1)).

Наконец, если в расчетной формуле в D1 вместо функции ВПР использовать универсальную функцию "просмотр", то выборку всех исходных параметров из справочной таблицы можно осуществить с помощью одного, а не трех обращений к ней:

     = Просмотр (C1; G$1 : G$6; H$1 : H$6 + K$1 : K$6 х (C1 - G$1 : G$6)).

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

Согласно Закону Российской Федерации от 9.12.91 г. N 2005-1 "О государственной пошлине" (в ред. от 21.03.02 г., 8.12.03 г., 20.08.04 г.) размер государственной пошлины зависит от цены и характера иска. В соответствии с п. 2 ст. 4 указанного Закона по делам, рассматриваемым в арбитражных судах, государственная пошлина взимается в следующих размерах (табл. 4):

Таблица 4

1) исковых заявлений имущественного характера при цене иска до 10 млн руб.	5% от цены иска, но не менее минимального размера оплаты труда
свыше 10 млн руб. до 50 млн руб.	500 тыс. руб. + 4% от суммы свыше 10 млн руб.
свыше 50 млн руб. до 100 млн руб.	2 млн 100 тыс. руб. + 3% от суммы свыше 50 млн руб.
свыше 100 млн руб. до 500 млн руб.	3 млн 600 тыс. руб. + 2% от суммы свыше 100 млн руб.
свыше 500 млн руб. до 1 млрд руб.	11 млн 600 тыс. руб. + 1% от суммы свыше 500 млн руб.
свыше 1 млрд руб.	16 млн 600 тыс. руб. + 0,5% от суммы свыше 1 млрд руб., но не свыше тысячекратного размера минимального размера оплаты труда

При непосредственной интерпретации исходной таблицы (табл. 4) в ее компьютерный эквивалент (с учетом указанных выше правил формирования подобных справочников, т.е. табл. 5) для решения поставленной задачи можно воспользоваться уже знакомой формулой расчета размера пошлины, проверяя полученные значения на верхнее и нижнее ограничения*(6): 16 700 000 >= yi >= 100.

Таблица 5

	А	В	С	D	E	F
1	шкала	процент	константа		цена иска	госпошлина
2	0	5,0%	0		3.000	150
3	10.000.000	4,0%	500.000
4	50.000.000	3,0%	2.100.000
5	100.000.000	2,0%	3.600.000
6	500.000.000	1,0%	11.600.000
7	1.000.000.000	0,5%	16.600.000

Однако расчетную формулу в F2 можно упростить, если оценивать не получаемые значения функции, а параметры ее аргументов, при которых функция приобретает эти "критические" значения, т.е. предварив вычисление размера государственной пошлины проверкой на критические значения аргумента:

  = Если (E2 < 2.000; 100; Если (E2 > 1.020.000.000; 16.700.000; Просмотр( .... ))).

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

     K1 = 2.000; K2 = 100; K3 = 1.020.000.000; K4 = 16.700.000.

В данном случае расчетная формула в F2 примет вид:

     = Если (E2 < K$1; K$2; Если (E2 > K$3; K$4; Просмотр (....))).

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

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

     = Просмотр(E2; A$2 : A$9; C2$ : C$9 + B$2 : B$9 * (Е2 - A$2 : A$9)).

Таблица 6

	А	В	С	D	E	F
1	шкала	процент	константа		цена иска	госпошлина
2	1	0,0%	100		3.000	150
3	2.000	5,0%	100
4	10.000.000	4,0%	500.000
5	50.000.000	3,0%	2.100.000
6	100.000.000	2,0%	3.600.000
7	500.000.000	1,0%	11.600.000
8	1.000.000.000	0,5%	16.600.000
9	1.020.000.000	0,0%	16.700.000

При подготовке электронной формы данного документа (подробно технология создания таких документов рассматривалась в статье "Таблицы решений в реализации задач финансово-экономического управления" (Финансовая газета. Региональный выпуск, апрель 2004, N 16-18) описанную выше для F2 формулу необходимо представить как элемент функции "если", например, в качестве "выражения-2" :

     = Если (или (E2 = <  >; E2 = 0); <  >; Просмотр(........))).

Такая запись формулы обеспечит отображение искомого результата только в случае ввода информации о конкретном значении размера предъявляемого иска.

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

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

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

Таблица 7

	А	В	С	D	E
1	Первоначальная стоимость	30.000
2	Остаточная стоимость	7.500
3	Срок амортизации	5
4	Коэффициент ускорения	1,3
5	Расчет амортизационных отчислений
6	Год	Норма	Амортизация	Остаточная стоимость
7	1	34,67%	22200,00	7800,00
8	2	25,65%	16428,00	5772,00
9	3	18,98%	12156,72	4271,28
10	4	14,05%	8995,97	3160,75
11	5	6,65%	7500,00	1495,97
12	15	100,00%		22500,00
13
14

Очевидно, что значение, записываемое в В3 (срок амортизации), не должно быть менее 2, так как при В3 = 1 теряется экономический смысл осуществлять расчет амортизации, которая в этом случае даст единый результат, равный В1 - В2 при любом методе амортизации. Примечательно, что благодаря этому экономически оправданному ограничению (В3 >= 2) подготовка универсальной формулы, записываемой в А9, значительно упрощается.

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

Ниже приводятся формулы, запись и копирование которых (помеченных с помощью символа v) в соответствующие клетки электронной таблицы обеспечат автоматическое формирование электронного документа, "самонастраивающегося" при любых изменениях в исходных данных:

     А7 = Если (B3 = < >; < >; 1)

     А8 = Если (B3 = < >; < >; 2)

     А9 = Если (А8 < B$3; А8 + 1; Если (и (А8 = B$3; B$3 < >< >);  Сумм (А$7 : А8);  <

>))

     D7 = Если (А7 = < >; <  >;  Если (А7 < = В$3;  ДДОБ (B$1; B$2; B$3; А7; B$4);

B$1 - B$2))

     C7 = Если (А7 = < >; < >; B1 - D7)

     C8 = Если (И (А8< >< >; А8<=B$3); C7-D8; < >)

     B7 =ЕСЛИ (А7=< >; < >; D7/(B$1-B$2)).

Следует отметить, при замене в формуле, представленной в D7, конструкции ДДОБ(B$1;B$2;B$3;А7;B$4) на любую другую встроенную функцию расчета амортизационных отчислений и ее последующем копировании вниз по столбцу электронного документа будет получена электронная форма нового документа для расчета амортизации соответствующим новым методом.

Л. Еремин,

доцент кафедры "Информационные технологии" Финансовой академии

при Правительстве Российской Федерации

"Финансовая газета. Региональный выпуск", N 19, 20, май 2006 г.

-------------------------------------------------------------------------

*(1) Никольская Ю., Спиридонов А. Excel в помощь бухгалтеру и экономисту. - М.: Вершина, 2006.

*(2) Еремин Л. Расчет страховых резервов с использованием табличного процессора Excel // Финансовая газета. Региональный выпуск, июль 2001, N 29.

*(3) Чернова Г. Расчет резерва неоплаченных убытков на основе метода треугольника. - М.: Страховое дело, август, 1997. С. 55-61.

*(4) Еремин Л. Таблицы решений в реализации задач финансово-экономического управления // Финансовая газета. Региональный выпуск, апрель 2004, N 16-18.

*(5) Лавренова С. Excel: сборник примеров и задач. - М.: Финансы и статистика, 2004.

*(6) F2 =ЕСЛИ(ПРОСМОТР(E2;A$2:A$7;C2$:C$7+B$2: B$7*(Е2-A$2:A$7))<100;100; ЕСЛИ (ПРОСМОТР(E2;A$2:A$7; C2$:C$7+ B$2:B$7*(Е2-A$2:A$7))>16700000; 16700000; ПРОСМОТР(E2;A$2: A$7;C2$:C$7+B$2:B$7*(Е2-A$2: A$7)))).