Описание и программирование решения задач оптимизации средствами Excel

-    Поле «Сходимость» необходимо для решения нелинейных задач.

-    Поле «Допустимое отклонение» используется для решения целочисленных задач.

-    Кнопки «Оценка», «Разность», «Метод поиска» служат для выбора алгоритмов оптимизации и их параметров.

Запуск и  результаты работы оптимизатора.

После ввода всех параметров решения запускаем оптимизатор  нажатием кнопки «Выполнить» в окне «Поиск решения». Программа начинает работать, в строке сообщений слева  внизу листа выходит сообщение «Постановка задачи».

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

Отчет «Устойчивость» содержит сведения о чувствительности решения  к малым изменениям в формуле  модели или в формулах ограничений.

 

 

 

 

 

Задача №1

Задача нахождения оптимальной ставки налога. Имитационное

моделирование.

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

Проблема: Теория и практика не знают величину применяемой для всех налоговой ставки.

Задача: В определении и обосновании величины налоговой ставки.

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

Цель работы: Исследовать зависимость налоговых поступлений в бюджет за фиксированный период времени от величины налоговой ставки на прибыль.

Постановка задачи. Словесная  модель.

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

Постналоговая прибыль (все, что осталось у фирмы от прибыли) полностью включается в собственный капитал фирмы, то есть в данной задаче считаем что никаких других отчислений от прибыли не производится, таким образом, вся прибыль распределяется на два потока:

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