1
Вероятностные игры с взаиморасчетами
Разработчики:
Бабинцева Е.А
Федотова Д.А.
Матухин П.Г.
1.1. Функциональное назначение и область применения разработки
Программно-методическая разработка по теме «Вероятностные игры с взаиморасчетами» в виде комплекта, включающего детальное описание алгоритма настройки и выполнения расчетов в среде MS-EXCEL в формате документа MS-WORD и презентации MS POWER POINT, на русском и английском языках предназначена для русских и иностранных студентов бакалавриата и специалитета, обучающихся по направлениям «Государственное и муниципальное управление», «Менеджмент», «Экономика и управление промышленным предприятием» и др. Пособие может быть использовано для изучения предметов «Основы математического моделирования социально-экономических процессов», «Экономико-математические методы и модели», «Программные средства решения экономических задач» и т.п.
В работе рассмотрена классическая задача о взаиморасчетах, лежащая в основе современных методов бухгалтерского учета. Приведено детальное описание всех этапов компьютерного моделирования вероятностной парной игры 5 игроков в среде MSEXCEL 2007 в виде набора таблиц, включая схемы размещения исходных данных и промежуточных и окончательных результатов расчетов.
Пособие может быть полезным также и для студентов магистратуры, аспирантов, преподавателей и всех, интересующихся применением компьютерных технологий для моделирования социально-экономических процессов и преподавания соответствующих дисциплин. С применением данного пособия можно проводить занятия и открытые уроки как среди русскоязычных студентов, так и среди студентов, владеющих английским языком, а также заниматься самостоятельным обучением.
Достоинством разработки является то, что она выполнена в среде Excel и доступна широкому круг пользователей имеющих стандартный офисный пакет Microsoft.
1.2. Формулировка задачи
Задача о взаиморасчетах представляет собой одну из первых математических моделей, реально использованных для регулирования социально-экономических отношений и практически не претерпевших существенных модификаций с момента создания. Задача о взаиморасчетах была сформулирована, изучена и использована в практической деятельности итальянским ученым Лукой Пачоли в XV веке. Данная модель является основой современной методологии бухгалтерского учета. Рассмотрим ее на примере некоторой азартной игры, в которой принимают участие пять игроков - элементы множества M={a, b, c, d, e}[1]. Игра парная, т. е. партии могут вестись между любыми парами участников и в результате каждой партии один из игроков оказывается должен другому определенную сумму денег. Предполагается, что игра идет в кредит, то есть проигрыши выплачиваются не сразу по окончании партии, а по результатам всех партий, сыгранных за некоторый период (например - за месяц), единовременно по окончании данного периода. То есть предполагается, что есть некто (следуя терминологии О.Кольваха [1], назовем его бухгалтер), который регистрирует возникшие в процессе игры взаимозадолженности по мере их возникновения по датам внутри месяца, т. е. в хронологическом порядке, занося соответствующие записи в журнал. Требуется построить компьютерную модель учета результатов всех партий.
Лука ПачолиFra Luca Bartolomeo de Pacioli
Портрет Луки Пачоли, предп. Якопо де Барбари, 1495
Теоретико-множественной моделью взаимозадолженности трех игроков является отношение S=“быть должным”, определенное на прямом произведении множества М самого на себя. Всего вариантов взаимозадолженностей при пяти участниках (мощность отношения R) может быть двадцать.
Количественной характеристикой данного отношения будут являться суммы взаимных задолженностей игроков. Чтобы их определить, построим компьютерную модель, позволяющую моделировать результаты партий и на их основе вычислять объемы взаимных платежей игроков друг другу.
Используя возможности ЭТП, мы можем построить вероятностную компьютерную модель игры. Пусть в течение месяца сыграно всего 20 партий, распределение игроков по партиям равномерное и результаты партий равномерно распределены в интервале от 0 до 1000 д.е. Модель будет представлять собой совокупность пяти таблиц. Первая таблица - модельный журнал регистрации результатов игры. Вторая - шахматная таблица взаимных задолженностей, третья - транспонированная шахматная таблица, четвертая - дополнительная, пятая - таблица сальдо.
1.3. Журнал регистрации результатов игры
Модельная таблица журнала регистрации результатов игр предназначена для компьютерного моделирования случайных результатов партий. Моделируются участники партий и суммы выигрыша. Для моделирования используется функция генерирования псевдослучайных чисел СЛЧИС. Рассмотрим краткий план построения Журнала регистрации игры.
Рис. 1. Заготовка таблицы журнала.
1 столбец: Номер партии «№». Должны присутствовать номера 1-20, которые можно сформировать при помощи автозаполнения.
Рис. 2. Моделирование победителя.
2 столбец: Идентификатор выигравшего игрока «Выиграл». Во втором столбце генерируются случайным образом равномерно распределенные пары игроков. Для этого используются композиции функций EXCEL вида:
=ЕСЛИ(ОКРУГЛ(1+4*СЛЧИС();0)=1;"a";
ЕСЛИ(ОКРУГЛ(1+4*СЛЧИС();0)=2;"b";
ЕСЛИ(ОКРУГЛ(1+4*СЛЧИС();0)=3;"c";
ЕСЛИ(ОКРУГЛ(1+4*СЛЧИС();0)=4;"d";"e"))))
Рис. 3. Моделирование проигравшего.
4 столбец: Идентификатор проигравшего игрока «Проиграл». В четвертом столбце генерируются случайным образом равномерно распределенные пары игроков. Для этого используются композиции функций EXCEL вида:
=ЕСЛИ(ОКРУГЛ(1+4*СЛЧИС();0)=1;"a";
ЕСЛИ(ОКРУГЛ(1+4*СЛЧИС();0)=2;"b";
ЕСЛИ(ОКРУГЛ(1+4*СЛЧИС();0)=3;"c";
ЕСЛИ(ОКРУГЛ(1+4*СЛЧИС();0)=4;"d";"e"))))
Рис. 4. Настройки
Рис. 5. Настройки.
Рис. 6. Моделирование пар игроков.
Рис. 7. Индикатор совпадений.
3 столбец: Сочетание пар из колонок 1 и 4 «Сц». Используется выражение вида =СЦЕПИТЬ(B4;D4), которое вводится в колонку С и копируется на все строки таблицы.
5 столбец: Пятая колонка таблицы «Инд. совпадений» содержит проверку совпадений содержимого колонок 2 и 4. Здесь используется формула логического сравнения типа =B4=D4. Результат - индикатор совпадений ИСТИНА/ЛОЖЬ.
Рис. 8. Моделирование суммы выигрыша.
6 столбец: В последней колонке «Сумма» генерируется случайный результат игры. Формула
=ОКРУГЛ(1000*СЛЧИС();2)
позволяет получить равномерно распределенный выигрыш первого игрока в диапазоне от 0 до 1000.
Рис. 9. Вычисление общего объема взаимных платежей.
7. Внизу таблицы вычисляется общая сумма всех результатов
=СУММ (F4:F23)
Таким образом, получена таблица, позволяющая моделировать результаты игр - случайные пары игроков и случайные суммы выигрышей. Использование генератора случайных чисел позволяет получать новые наборы результатов. Для этого следует нажать кнопку F9.
1.4. Алгоритм расчета
Главной целью работы является подведение результатов игр, проходивших за некоторый календарный период. Для этого используется предложенный Л. Пачоли метод двойной записи, реализуемый в форме шахматных таблиц. Таблицы иллюстрируют вычисления по формуле:
?S = S - ST ,
где
?S - сальдо, в бухгалтерском учёте -- остаток по бухгалтерскому счёту, разность между суммой записей по дебету и кредиту счетов.
S - дебет,-- левая сторона бухгалтерского счета. По активным и активно-пассивным счетам, увеличение дебета означает увеличение имущества или имущественных прав организации. По пассивным счетам, увеличение дебета означает уменьшение собственных средств организации (источников). Происходит от лат. debet, что означает «он должен». В латыни для этого термина используется слово debitum -- «долг».
ST - кредит -- правая сторона бухгалтерского счета. По активным и активно-пассивным счетам, увеличение кредита означает уменьшение стоимости имущества или имущественных прав организации. По пассивным счетам, увеличение кредита означает увеличение собственных средств организации (источников).
Существует два вида счетов: активные и пассивные. Пассивные -- это привлеченные средства; активные -- размещенные средства компании, предприятия или банка. Для активных счетов, дебет -- приход, кредит -- расход средств. Для пассивных, кредит -- приход, дебет -- расход.
1.5. Шахматная таблица взаимных задолженностей.
В первой таблице вычисляются суммы взаимных задолженностей за период.
Рис. 10. Заготовка шахматной таблицы
Рис. 11. Программирование шахматной таблицы
Шахматная таблица программируется на основании данных журнала регистрации итогов партий. Соответствующие формулы имеют вид
=СУММЕСЛИ($C$4:$C$23;СЦЕПИТЬ($H4;I$3);$F$4:$F$23)
1.6. Транспонированная шахматная таблица взаимных задолженностей
Рис. 12. Заготовка транспонированной шахматной таблицы
Рис. 13. Программирование транспонированной шахматной таблицы.
Для того, чтобы рассчитать значения ячеек новой таблицы, нам необходимо транспонировать предыдущую матрицу. Для этого мы применим формулу
После выделения массива, который необходимо транспонировать, необходимо нажать =ТРАНСП(I4:M8)+Ctrl+Shift+Enter
1.7. Дополнительная шахматная таблица
Рис. 14. Заготовка дополнительной таблицы
Рис. 15. Программирование дополнительной таблицы
Обратного знака у всех значений в следующей таблице можно добиться, применив формулу
=-I13
Введя ее в ячейку I22 и скопировав в другие ячейки таблицы, или для ввода в матричном режиме можно применить формулу
=-I13:М17
1.8. Шахматная таблица сальдо взаимных задолженностей
Рис. 16. Заготовка таблицы сальдо
Рис. 17. Программирование таблицы сальдо.
Для расчета сальдо нам необходимо получить сумму значений S и -ST, что и будет являться необходимой нам разностью дебета и кредита. Для этого воспользуемся формулой
=I4+I22
Которую следует ввести в ячейку I31 или для вычислений в матричном режиме
=I4:M8+I22:M26.
В результате получается таблица сальдо игры, которая показывает, сколько всего кто из игроков кому должен. Такой метод расчетов технологичен и требует для окончательного расчета минимальный объем наличных денежных средств.
1.9. Расчет сумм выигрыша и проигрыша
После расчета таблицы итогового сальдо взаимных задолженностей можно подвести окончательные итоги игр за прошедший период. Для этого в таблице сальдо следует вычислить суммы по столбцам (просуммировать выигрыши по строке). В результате получится суммарный выигрыш каждого игрока. Если результат положительный, данный игрок в итоге всех партий выиграл, и он имеет право получить соответствующую сумму из общей кассы. Если результат отрицательный, то игрок проиграл и должен внести в кассу соответствующую сумму. Просуммировав столбцы по строкам, получим проигрыш каждого игрока.
Рис. 18. Дополнительные расчеты
Сделаем это в матричном режиме по формулам типа: для «выигрышей»
=СУММ(I31:M31),
и типа:
=СУММ(I31:I35)
для «проигрышей».
Рис. 19. Расчет сумм выигрышей и проигрышей
1.10. Анализ результатов серии игр
Подведя итоги игр за рассматриваемы период, по данным, показанным на рис. 18 (блок ячеек [N31:30], можно определить, что все игроки оказались в проигрыше, за исключением игрока С. Он выиграл у остальных сумм в размере 2207,1 р.
1.11. Презентация на русском и английском языках
Для проведения занятий в группах, имеющих в своем составе иностранных студентов, в состав комплекта включена презентация, выполненная на русском и английском языках. Презентация содержит набор слайдов, представляющих основные положения разработки. В том числе - историческую справку об авторе метода двойной записи в бухгалтерском учете Л.Пачоли, теоретические сведения и расчетные формулы, алгоритм подготовки таблиц и настройки расчетных формул с комментариями.
2. Используемые технические и программные средства
Для создания данного электронного комплекта использовался персональный компьютер типа Intel ® Core ™2 CPU c операционной системой Microsoft Vista HomePremium версия 2007. Использовался пакет MSOFFICE с текстовым редактором MS-Word 2007, MS-Excel 2007 и MS-Power Point 2007.
ЛИТЕРАТУРА
1. Кольвах О. И. Компьютерная бухгалтерия для всех.- Ростов н/Д: Издательство «Феникс». 1996. -. 416 с