Решение задач моделирования и оптимизации с помощью программ Excel и Mathcad Новосибирская государственная академия водного транспорта Омский филиал По информатике Решение задач моделирования и оптимизации с помощью программ Excel и Mathcad Выполнил студент: Назарова Наталья Евгеньевна группа ЭК-23 Проверила: Иванова Татьяна Леонидовна. г. Омск-2006 г. Содержание. Лист заданий Введение Задача нахождения оптимальной ставки налога. Имитационное моделирование Производственная задача Транспортная задача Лист заданий Задача №1 (билет №23К): Исследовать средствами Excel и Mathcad зависимость оптимальной ставки налога от величины начального капитала предприятия. Исходные данные: Начальный капитал фирмы (млн. руб.): 7010, 8010, 9010, 10010, 10210, 10310. Интервал моделирования 10 лет. Рентабельность фирмы 80%. Налоговая ставка: 10%, 20%, 30%, 40%, 50%, 60%, 70%. Задача №2 (билет№47 II ): Туристическая фирма в летний сезон обслуживает в среднем 10000 туристов и располагает флотилией из двух типов судов. В месяц выделяется 9000 тонн горючего. Потребность в рабочей силе не превышает 1000 человек. Определить количество судов первого и второго типа, чтобы обеспечить максимальный доход, который составляет от эксплуатации первого типа – 20 млн. руб., а от второго – 30 млн. руб. в месяц, если судов первого типа должно быть в два раза больше судов второго. Показатели Судно 1 типа Судно 2 типа Пассажировместимость, чел. 2000 1000 Горючее, тонны 700 800 Экипаж 100 100 Задача №3 (билет№59ТВ) : На заводах А1, А2, А3 имеется груз, поставляемый на склады В1, В2, В3. Запасы на заводах: 500, 600, 400 тонн соответственно. Нужно поставить груз на склады соответственно 500, 600, 500 тонн. Найти план перевозок груза, при котором затраты на перевозку были бы минимальными. Условия перевозки: 1. Минимальное количество поставляемого груза с завода А1 и завода А2 к каждому потребителю составляет 160 тонн. 2. С завода А2 на склад В1 нужно привезти не более 200 тонн, а с завода А3 на склад В1 – не менее 100 тонн. Потребители В1 В2 В3 Поставщики А1 9 6 2 А2 4 15 15 А3 17 7 8 Введение. Microsoft Excel . Называть Excel просто «электронной таблицей» - все равно, что употребить в отношении Word термин «текстовый редактор». Вроде бы все сказано, а на самом деле ничего. Так как по количеству возможностей, скрытым и открытым функциям и кругу решаемых задач Excel, вероятно может обскакать тот же Word, не говоря уже об остальных программах. И это не удивительно. Любой текстовой редактор, и Word в том числе, программа одномерная, линейная. Пользователь же Excel живет и работает уже в двух измерениях, оперируя сотнями, а то и тысячами отдельных, автономных элементов информации. Рабочее поле Excel поделено вертикальными и горизонтальными линиями на прямоугольные ячейки. Объединенные по вертикали ячейки составляют столбцы, а по горизонтали – строки. А заполнить эти клеточки можно чем угодно – текстом, численными значениями, даже графикой. Правда, для того, чтобы введенная вами информация обрабатывалась корректно, необходимо присвоить ячейке (а чаще – целому столбцу или строке) определенный формат: - Общий – это ячейки могут содержать как текстовую, так и цифровую информацию. - Числовой – для цифровой информации. - Денежный – для отображения денежных величин в заранее заданной пользователем валюте. - Финансовый – для отображения денежных величин с выравниванием по разделителю и дробной части. - Дата. - Время. - Процентный. - Дробный. - Экспоненциальный. - Текстовый. - Дополнительный – этот формат используется при составлении небольшой базы данных или списка адресов для ввода почтовых индексов, номеров телефонов, табельных номеров. Операцию эту, как и многие другие, можно выполнить с помощью Контекстного меню ячейки или выделенного фрагмента таблицы. При этом объем информации, которую вы можете внести в клетки рабочего поля Excel, не ограничен видимой площадью клетки: как столбцы, так и строки обладают способностью услужливо растягиваться под напором пользовательской фантазии. Кроме того, «внутренности» каждой клетки могут иметь, куда большие размеры, чем видимая пользователю часть. Рабочая книга – основной документ Microsoft Excel, состоящий из листов, максимальное количество которых составляет 255. Создание новой книги. Файл/ Создать. Группировка листов. Ряд операций (копирование, перемещение, удаление, параллельный ввод информации на нескольких листах) могут выполняться как для одного листа, так и для группы листов одновременно. Создание группы из смежных листов. - Щелкнуть клавишей мыши на первом листе ярлыка. - Нажать и удерживать Shift. - При нажатой клавише Shift щелкнуть по последнему ярлыку листа. Создание группы из несмежных листов. - Щелкнуть клавишей мыши на ярлыки первого листа и нажать Ctrl. - Прощелкать остальные ярлыки листа. - В результате выделенные листы группы имеют подсвеченные ярлыки. Теперь можно осуществлять параллельный ввод данных и другие операции. Для снятия выделенных листов нужно выполнить команду контекстного меню «Группировать листы». Операции с листами. - Переименование: Формат/ Лист/ Переименовать (максимальная длина имени листа 31 символ). - Перемещение и копирование листа: Правка/ Переместить/ Скопировать. - Выделенные (сгруппированные) листы можно временно скрыть с помощью: Формат/ Лист/ Скрыть. - Для просмотра и открытия скрытых листов: Формат/ Лист/ Отобразить. Примечание и индикатор. Примечание и индикатор – маленький красный треугольник в верхнем правом углу ячейки. Ввод примечания. - Выделить ячейки. - Команда Ставка/ Примечание. - Ввод текста. Изменение примечания. Вставка/ Изменить примечание. Другие команды контекстного меню. - Удалить примечание. - Скрыть примечание. - Отобразить примечание. Лист диаграммы. На отдельном листе диаграмм может разместиться одна диаграмма. На листе электронной таблицы может разместиться произвольное количество диаграмм. Количество листов диаграмм в пределах одной книги не ограничено. Панель инструментов «Рисование» позволяет включить дополнительные элементы для диаграммы. Форматирование ячеек. Формат/ Ячейки. Прогрессии. Создание прогрессии используется для автоматического заполнения больших блоков ячеек числами, значения которых подчиняются арифметической и геометрической прогрессии. - Выделение блока смежных или несмежных ячеек. - Правка/ Заполнить/ Прогрессия или команда контекстного меню «Прогрессия». - Задание параметров прогрессии. Типы адресации ячеек в Excel . Ссылки – адреса ячеек. - Относительный тип адресации – способ, при котором адрес ячейки определяется как пересечение столбца и строки. В этом случае Excel заполняет расположение относительной текущей ячейки. Этот тип адресации применяется при заполнении больших таблиц формулами. - Абсолютная адресация. Используется при копировании формул, когда требуется сохранить ссылку на конкретную ячейку или область ячейки. При задании этой ссылки используется знак - $. - Смешанная адресация – адресация, при которой один параметр адреса изменяется, а другой нет. Ошибки при вводе или редактировании формул. - ##### - ширина ячеек недостаточна для отображения результата. - # ИМЯ ? – если программа не распознала имя функции или имя ячейки, которая используется в формуле. - # ЗНАЧ! – некорректное использование функции, например несоответствие данных установленному формату (вместо числа и даты в аргументе используется текст), либо если для функции, которая требует единственного значения аргумента, задан диапазон данных. - # ЧИСЛО! – возникают проблемы с представлением или использованием чисел, например функции с числовым аргументом используется аргумент нечислового формата. - # ССЫЛКА! – указывает на проблему с адресацией ячеек, участвующих в формуле, например, формула содержит ссылку на ячейку, которая уже удалена. - # ДЕЛ/0! – при попытке деления на 0 такая ситуация возникает из-за того что в качестве делителя используется ссылка на пустую ячейку или ячейку с нулем. - # ПУСТО! – в случае задания в ссылке пустого множества ячеек. - # Н/Д – сообщение говорит о наличии неопределенных данных. Если после ввода формул не появились сообщения об ошибках, то это означает, что формулы введены правильно. Для отладки программы часто необходимо использовать команду: Сервис/ Зависимости/ Панель зависимостей. Программа оптимизации в Excel . С помощью данного оптимизатора можно находить множество значений переменных удовлетворяющих критериям оптимизации. Результаты работы оптимизатора могут быть оформлены в виде отчета. Можно полученные значения занести в таблицу. Программа оптимизации в Excel вызывается командой Сервис /Поиск решения. В результате выполнения команды появляется диалоговое окно «Поиск решения». В диалоговом окне «Поиск решения» поле «Установить целевую» служит для указания целевой ячейки, значение которой нужно максимизировать, минимизировать или задать равным какому-то числу. В указанной целевой ячейке должна храниться формула. В поле «Изменяя ячейки» указываются адреса ячеек значения, которых меняются до тех пор, пока не выполняются все ограничения и само условие оптимизации целевой функции. Изменяемые ячейки должны быть прямо или косвенно связаны с целевой ячейкой. В Excel допускается установка до 200 изменяемых ячеек. Поле «Ограничения» вводим, изменяем, удаляем ограничения с помощью соответствующих кнопок, предварительно выделив нужное ограничение. Команда «Выполнить» служит для запуска программы оптимизации. Команда «Закрыть» служит для выхода из диалогового окна без запуска оптимизатора, при этом все введенные установки сохраняются. Кнопка «Параметры» служит для настройки алгоритма и программы оптимизации. Кнопка «Восстановить» служит для очистки полей диалогового окна, при этом принимаются параметры, принятые по умолчанию. Ввод и редактор ограничений. В окне «Поиск решения» щелкнуть по кнопке «Добавить», в результате появляется еще одно окно. В поле «Ссылка на ячейку» вводится адрес на ячейку или диапазон ячеек на значение которых вводится ограничения. В поле «Ограничения» вводится адрес ячейки или диапазон ячеек, в которых содержится или вычисляется ограничиваемое значение. Кнопка «Добавить» нужна для добавления следующего ограничения. При нажатии «ОК» возврат в окно «Поиск решения». Настройка параметров алгоритма и программы. Настройка параметров алгоритма и программы производится в окне «Параметры поиска решения». Для его вызова необходимо в окне «Поиск решения» щелкнуть по кнопке «Параметры». В окне «Параметры поиска решения» задаются: - В поле «Максимальное время» устанавливаются ограничения на время время решения задачи. По умолчанию время решения задачи равно 100 секунд. Максимальное время – 32767 секунд. - В поле «Относительная погрешность» задается точность рения задачи. В поле должно содержаться число в интервале от 0 до 1. Чем меньше введенное число, тем выше точность результата. С другой стороны, чем выше точность результата, тем больше требуется времени на нахождение решения. По умолчанию относительная погрешность равна 0,000001. - Поле «Сходимость» необходимо для решения нелинейных задач. - Поле «Допустимое отклонение» используется для решения целочисленных задач. - Кнопки «Оценка», «Разность», «Метод поиска» служат для выбора алгоритмов оптимизации и их параметров. Запуск и результаты работы оптимизатора. После ввода всех параметров решения запускаем оптимизатор нажатием кнопки «Выполнить» в окне «Поиск решения». Программа начинает работать, в строке сообщений слева внизу листа выходит сообщение «Постановка задачи». По окончании счета появляется диалоговое окно «Результаты поиска решения». Результаты работы оптимизатора, возможно, вывести в виде отчета на отдельном листе. В отчете «Результаты» для целевой ячейки и изменяемых ячеек указываются исходные и конечные значения ячеек и формулы ограничения. Отчет «Устойчивость» содержит сведения о чувствительности решения к малым изменениям в формуле модели или в формулах ограничений. Задача №1 (билет №23К). Задача нахождения оптимальной ставки налога. Имитационное моделирование. Определение проблемы: Государство стремится увеличить налоги, чтобы наполнить бюджет для выполнения социальных и оборонных функций. Бизнес жалуется, что налоги высоки. Экономисты утверждают, что высокие налоги сдерживают развитие экономики, то есть накопление бюджета в будущем. Проблема : Теория и практика не знают величину применяемой для всех налоговой ставки. Задача: В определении и обосновании величины налоговой ставки. Рабочая гипотеза: Поступление в бюджет за определенный период времени будут наибольшими не при максимальной налоговой ставке, а при каком-то значении, называемым оптимальным, то есть с увеличением налоговой ставки ожидаем, что поступления в бюджет сначала увеличатся, а затем уменьшатся. Цель работы: Исследовать зависимость налоговых поступлений в бюджет за фиксированный период времени от величины налоговой ставки на прибыль. Постановка задачи. Словесная модель. Источником развития бизнеса налогового накопления бюджета является прибыль предприятия. Государство объявляет ставку налога на прибыль и получает от фирмы отчисления в бюджет. Фирмы обладают собственным капиталом, производят прибыль и по налоговой ставке отчисляют в бюджет. Постналоговая прибыль (все, что осталось у фирмы от прибыли) полностью включается в собственный капитал фирмы, то есть в данной задаче считаем что никаких других отчислений от прибыли не производится, таким образом, вся прибыль распределяется на два потока: 1. в бюджет 2. в остаток в собственный капитал фирмы. Математическая модель. Исходные данные задачи: 1. Величина налоговой ставки Сn ; 2. Рентабельность фирмы Rent; 3. Начальный капитал фирмы Кнач. ; 4. Интервал моделирования t; П – прибыль; Sбюдж./год – отчисления в бюджет за год; П = Кнач. * Rent; Пналог = П * Сn ; Пост. = П * (1 – Сn ) = П – Пналог ; Пкапитализ. = Пост. ; Ксальдо = Кнач. + Пост. ; Sбюдж./год = Пналог ; t Sбюдж./период = ∑ Sбюдж./год ; 1 В данной задаче эта математическая модель реализуется виде имитационной табличной модели в Excel, то есть создается таблица, где для фирмы с заданными значениями рентабельности, начального капитала и ставки налога, по формулам рассчитывается сумма отчислений в бюджет за заданный период времени t Однофакторный имитационный анализ. Время год 0 1 2 3 4 5 6 7 8 9 10 Начальный капитал млн. руб. 7010 12057,2 20738,38 35670,02 61352,44 105526,2 181505 312189 536964,5 923579 Ставка налога % 10 10 10 10 10 10 10 10 10 10 10 Рентабельность % 80 80 80 80 80 80 80 80 80 80 80 Прибыль млн. руб. 5608 9645,76 16590,71 28536,02 49081,95 84420,95 145204 249751 429571,6 738863,2 Прибыль в налог млн. руб. 560,8 964,576 1659,071 2853,602 4908,195 8442,095 14520,4 24975,1 42957,16 73886,32 Прибыль в остаток млн. руб. 5047,2 8681,184 14931,64 25682,41 44173,75 75978,86 130684 224776 386614,5 664976,9 Капитализированная прибыль млн. руб. 5047,2 8681,184 14931,64 25682,41 44173,75 75978,86 130684 224776 386614,5 664976,9 Капитал сальдо млн. руб. 7010 12057,2 20738,38 35670,02 61352,44 105526,2 181505 312189 536965 923579 1588556 Поступление в бюджет за год млн. руб. 560,8 964,576 1659,071 2853,602 4908,195 8442,095 14520,4 24975,1 42957,16 73886,32 Поступление за период млн. руб. 0 560,8 1525,376 3184,447 6038,048 10946,24 19388,34 33908,7 58883,8 101841 175727,3 Отчисления в бюджет при разных налоговых ставках для фирм с начальным капиталом 7010 млн. руб. Период 1 2 3 4 5 6 7 8 9 10 Ставка налога Сумма 10% 560,8 1525,38 3184,45 6038,048 10946,24 19388,34 33908,74 58883,84 101841 175727 20% 1121,6 2961,02 5977,68 10924,99 19038,59 32344,89 54167,22 89955,84 148649 244906 30% 1682,4 4306,94 8401,23 14788,32 24752,18 40295,81 64543,86 102370,8 161381 253437 40% 2243,2 5563,14 10476,6 17748,63 28511,17 44439,73 68014,01 102903,9 154541 230964 50% 2804 6729,6 12225,4 19919,62 30691,46 45772,05 66884,87 96442,81 137824 195758 60% 3364,8 7806,34 13669,2 21408,1 31623,49 45107,8 62907,1 86402,17 117416 158353 70% 3925,6 8793,34 14829,3 22313,99 31594,95 43103,33 57373,73 75069,03 97011,2 124219 Вывод: За период 10 лет с начальным капиталом 7010 млн. руб. наибольшие отчисления в бюджет составляют 253437 млн. руб. при налоговой ставке 30%. Отчисления в бюджет при разных налоговых ставках для фирм с начальным капиталом 8010 млн. руб. Период 1 2 3 4 5 6 7 8 9 10 Ставка налога Сумма 10% 640,8 1743 3638,72 6899,4 12507,76 22154,15 38745,94 67283,8 116369 200795 20% 1281,6 3383,4 6830,42 12483,5 21754,51 36959 61894,35 102788 169854 279843 30% 1922,4 4921,3 9599,7 16897,9 28283,17 46044,14 73751,26 116974 184402 289590 40% 2563,2 6356,7 11971,2 20280,5 32578,39 50779,21 77716,43 117584 176587 263912 50% 3204 7689,6 13969,4 22761,2 35069,7 52301,58 76426,22 110201 157485 223683 60% 3844,8 8919,9 15619,1 24462 36134,68 51542,58 71881,01 98727,7 134165 180943 70% 4485,6 10048 16944,8 25497,2 36102,07 49252,17 65558,29 85777,9 110850 141940 Вывод: За период 10 лет с начальным капиталом 8010 млн. руб. наибольшие отчисления в бюджет составляют 289590 млн. руб. при налоговой ставке 30%. Отчисления в бюджет при разных налоговых ставках для фирм с начальным капиталом 9010 млн. руб. Период 1 2 3 4 5 6 7 8 9 10 Ставка налога Сумма 10% 720,8 1960,6 4092,99 7760,74 14069,3 24920 43583,1 75683,8 130897 225863 20% 1441,6 3805,8 7683,15 14042 24470,4 41573,1 69621,5 115621 191060 314780 30% 2162,4 5535,7 10798,2 19007,5 31814,1 51792,5 82958,7 131578 207424 325744 40% 2883,2 7150,3 13465,7 22812,4 36645,6 57118,7 87418,9 132263 198633 296859 50% 3604 8649,6 15713,4 25602,8 39447,9 58831,1 85967,6 123959 177146 251608 60% 4324,8 10034 17569,1 27516 40645,9 57977,4 80854,9 111053 150915 203533 70% 5045,6 11302 19060,3 28680,3 40609,2 55401 73742,8 96486,7 124689 159660 Вывод: За период 10 лет с начальным капиталом 9010 млн. руб. наибольшие отчисления в бюджет составляют 325744 млн. руб. при налоговой ставке 30%. Отчисления в бюджет при разных налоговых ставках для фирм с начальным капиталом 10010 млн. руб. Период 1 2 3 4 5 6 7 8 9 10 Ставка налога Сумма 10% 800,8 2178,2 4547,26 8622,09 15630,8 27685,8 48420,3 84083,8 145425 250932 20% 1601,6 4228,2 8535,89 15600,5 27186,3 46187,2 77348,6 128453 212265 349716 30% 2402,4 6150,1 11996,6 21117,1 35345,1 57540,8 92166,1 146181 230445 361897 40% 3203,2 7943,9 14960,2 25344,3 40712,8 63458,2 97121,3 146943 220678 329807 50% 4004 9609,6 17457,4 28444,4 43826,2 65360,7 95508,9 137716 196807 279534 60% 4804,8 11147 19519 30569,9 45157,1 64412,1 89828,8 123379 167665 226122 70% 5605,6 12557 21175,7 31863,5 45116,3 61549,8 81927,4 107196 138528 177380 Вывод: За период 10 лет с начальным капиталом 10010 млн. руб. наибольшие отчисления в бюджет составляют 361897 млн. руб. при налоговой ставке 30%. Отчисления в бюджет при разных налоговых ставках для фирм с начальным капиталом 10210 млн. руб. Период 1 2 3 4 5 6 7 8 9 10 Ставка налога Сумма 10% 816,8 2221,7 4638,12 8794,36 15943,1 28238,9 49387,8 85763,8 148330 255945 20% 1633,6 4312,7 8706,43 15912,2 27729,5 47110 78894 131020 216506 356704 30% 2450,4 6273 12236,3 21539,1 36051,3 58690,5 94007,5 149102 235050 369128 40% 3267,2 8102,7 15259,1 25850,7 41526,3 64726,1 99061,8 149879 225088 336397 50% 4084 9801,6 17806,2 29012,7 44701,8 66666,6 97417,2 140468 200739 285119 60% 4900,8 11370 19909 31180,7 46059,3 65699,1 91623,6 125844 171015 230640 70% 5717,6 12807 21598,8 32500,1 46017,7 62779,6 83564,3 109337 141296 180925 Вывод: За период 10 лет с начальным капиталом 10210 млн. руб. наибольшие отчисления в бюджет составляют 369128 млн. руб. при налоговой ставке 30%. Отчисления в бюджет при разных налоговых ставках для фирм с начальным капиталом 10310 млн. руб. Период 1 2 3 4 5 6 7 8 9 10 Ставка налога Сумма 10% 824,8 2243,5 4683,54 8880,5 16099,3 28515,5 49871,5 86603,8 149783 258452 20% 1649,6 4354,9 8791,71 16068 28001,1 47571,4 79666,8 132303 218627 360197 30% 2474,4 6334,5 12356,2 21750 36404,4 59265,3 94928,3 150563 237352 372743 40% 3299,2 8182 15408,6 26103,9 41933 65360 100032 151347 227292 339692 50% 4124 9897,6 17980,6 29296,9 45139,7 67319,5 98371,3 141844 202705 287912 60% 4948,8 11481 20104 31486,1 46510,4 66342,6 92521 127077 172690 232899 70% 5773,6 12933 21810,4 32818,4 46468,5 63394,5 84382,8 110408 142680 182697 Вывод: За период 10 лет с начальным капиталом 10310 млн. руб. наибольшие отчисления в бюджет составляют 372743 млн. руб. при налоговой ставке 30%. Двухфакторный имитационный анализ. Зависимость поступлений в бюджет от ставки налога и величины начального капитала за период 10 лет. Начальный капитал 7010 8010 9010 10010 10210 10310 Ставка налога Сумма Ставка налога 10% 175727 200795 225863 250932 255945 258452 Ставка налога 20% 244906 279843 314780 349716 356704 360197 Ставка налога 30% 253437 289590 325744 361897 369128 372743 Ставка налога 40%