Технологии организации решения финансово-экономических задач в табличном процессоре 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;