Результат: Затраты на перевозку

Оглавление

Работа № 1. Оптимальные бизнес-планы, план по продукции, технология оптимизации.. 2

Работа № 2. Выбор поставщиков, план перевозок, транспортная задача.. 7

Работа № 3. Расчет резерва по вкладам... 13

Работа № 4. Оптимальная ставка налога, имитационное моделирование.. 20

Работа № 5. Разработка АИС для расчета амортизационных отчислений.. 25

Работа № 6. Разработка автоматизированной системы по начислению заработной платы... 35

работа № 7. Создание локальных реляционных баз данных 43

работа № 8. Обработка данных в локальных реляционных базах данных.. 53

работа № 9. Нормализация реляционной БД.. 61

работа № 10. Создание ER-модели и ее нормализация.. 70

 

 

Работа № 1. Оптимальные бизнес-планы, план по продукции, технология оптимизации

Проблема: менеджеры и планировщики разрабатывали план производства продукции без учета ресурсов, т. е. запасов материалов и комплектующих на складах и возможностей поставщиков.

Цель работы

1. Освоить методику и технологию оптимизации планов в табличном процессоре MS Excel с помощью программы Поиск решения (Slover).

2. Научиться составлять наилучший (оптимальный) план производства продукции с учетом ограниченного обеспечения материальными ресурсами.

Постановка задачи

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

Следует учитывать уменьшение удельной прибыли при увеличении объемов производства в связи с дополнительными затратами на сбыт.

Порядок выполнения работы

Исходные данные приведены в Таблице 1.1. Наименование продукции расположено в строке 2, в строке 3 расположены ячейки искомого плана. В колонке А приводится наименование комплектующих изделий, необходимых для производства продукции. В колонке B задан запас комплектующих на складе. Нормы расхода комплектующих на производство одного изделия задаются в матрице диапазона D 5: F 9. Плановые затраты комплектующих на производство всех типов изделий вычисляются в колонке С и не должны превышать запасов на складе. Прибыль по каждому типу изделий вычисляется в строке 17, числа 75, 50 и 35 означают прибыль на единицу продукции, которая умножается на количество изделий по плану и корректируется возведением в степень коэффициентом уменьшения прибыли из ячейки G 9. Коэффициент уменьшения отдачи отражает убывающую эффективность роста продаж за счет роста затрат на рекламу и другие затраты в системе маркетинга и сбыта. Необходимо назначить количество изделий в плане производства в строке 9 и получить максимально возможную прибыль в ячейке D 12.

Математическая модель поиска оптимального плана будет выглядеть следующим образом:

i — номер строки, ресурса;

j — номер столбца, продукта;

X_j — искомое плановое количество j -го продукта;

P_j — прибыль на единицу j -го продукта;

B_i — ограниченный запас i -го ресурса на складе;

R_ij — норма расхода i -го ресурса на единицу j -го продукта;

C_i — плановая сумма расхода i -го ресурса по всем продуктам;

Нам необходимо максимизировать прибыль

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

Таблицу 1.1 необходимо набрать в Excel. Для ввода формул удобнее воспользоваться режимом представления формул, для этого нужно установить галочку в меню Сервис>Параметры>Параметры окна>формулы.

Таблица 1.1.

	A	B	C	D	E	F	G
1
2	Наименование продукции:			Телевизор	Стерео система	Акустическая система
3	План производства, шт.
4	Наименование комплектующих	Запас на складе, шт.	Расход по плану, шт.	Нормы расхода ресурсов
5	Шасси	450	=$D$3*D5+$E$3*E5+$F$3*F5	1	1	0
6	Кинескоп	250	=$D$3*D6+$E$3*E6+$F$3*F6	1	0	0	Уменьшение коэффициента отдачи
7	Динамик	800	=$D$3*D7+$E$3*E7+$F$3*F7	2	2	1
8	Блок пит.	450	=$D$3*D8+$E$3*E8+$F$3*F8	1	1	0
9	Печатн. плата	600	=$D$3*D9+$E$3*E9+$F$3*F9	2	1	1	0,9
10
11	Прибыль по видам изделий:			=75*МАКС(D3;0)^$G$9	=50*МАКС(E3;0)^$G$9	=35*МАКС(F3;0)^$G$9
12	Прибыль всего:			=СУММ(D11:F11)

Задание № 1. Ручной поиск оптимального плана

Изменяя количество продукции в строке 3 попытаться получить максимальную прибыль в ячейке D 12. При этом необходимо визуально контролировать расход комплектующих в колонке С. Расход не должен превышать запасов на складе (колонка В).

Задание № 2. Настройка экономико-математической модели

Для обработки таблицы Excel оптимизатором необходимо вызвать его диалоговое окно Сервис> Поиск решения и настроить экономико-математическую модель. Поле Установить целевую ячейку служит для указания целевой ячейки, значение которой необходимо максимизировать, минимизировать или установить равным заданному числу. В нашем случае это максимальное значение ячейки $ D $1 2. Поле Изменяя ячейки служит для указания ячеек, значения которых изменяются в процессе поиска решения. В нашем примере это диапазон $ D $ 3:$ F $ 3. Поля Ограничения служат для отображения списка граничных условий поставленной задачи. В нашем случае величины диапазона расхода комплектующих C 5: C 9 не должны превышать запаса на складе D 5: D 9 ($ C $5:$ C $9<=$ B $5:$ B $9). Количество выпускаемых изделий не может быть отрицательным ($ D $3:$ F $3>=0) и должно быть целым ($ D $5:$ D $9=целое). Так как в формулу прибыли на изделие в ячейках D 11: F 11 входит показатель степени G 9 и его значение отлично от 1, то наша задача нелинейная. Необходимо вызвать окно настройки параметров модели и снять флажок линейной модели. Сохранить модель в ячейке B15.

Задание № 3. Компьютерный поиск оптимального плана

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

Оформление отчета

Отчет должен содержать:

1. Определение проблемы.

2. Плановую таблицу с результатами оптимального плана.

3. Краткую характеристику программы оптимизации Поиск решения.

4. Анализ оптимального плана и решения менеджера.

5. Написать формулы модели для оптимального планирования выпуска продукции.

6. Предложения по модификации и расширению модели.

7. Выводы обо всей проделанной работе.

 

Работа № 2. Выбор поставщиков, план перевозок, транспортная задача

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

Цель работы

1. Освоить методику и технологию оптимизации планов в табличном процессоре Excel с помощью программы Поиск решения (Slover).

2. Научиться составлять наилучший (оптимальный) план перевозок от поставщиков к потребителям с учетом ограниченных ресурсов поставщиков и известной потребности потребителей.

Постановка задачи

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

Необходимо определить объемы перевозок между каждым заводом и складом, в соответствии с потребностями складов и производственными мощностями заводов, при которых транспортные расходы минимальны.

Порядок выполнения работы

Исходные данные приведены в Таблице 2.1.

 

Таблица 2.1

	A	B	C	D	E	F	G
2			План по объемам перевозок от завода х к складу у:
3	Заводы	План поставок	Казань	Рига	Воронеж	Курск	Москва
4	Белоруссия	=СУММ(C4:G4)
5	Урал	=СУММ(C5:G5)
6	Украина	=СУММ(C6:G6)
7			Поставлено каждому складу
8		Итого:	=СУММ(C4:C6)	=СУММ(D4:D6)	=СУММ(E4:E6)	=СУММ(F4:F6)	=СУММ(G4:G6)
9			Исходные данные для расчета плана
10		Потребности складов	180	80	200	160	220
11	Заводы	Мощность заводов	Стоимость перевозки единицы груза
12	Белоруссия	310	10	8	6	5	4
13	Урал	260	6	5	4	3	6
14	Украина	280	3	4	5	5	9
15

Задание № 2. Настройка экономико-математической модели

Для обработки таблицы Excel оптимизатором необходимо вызвать его диалоговое окно Сервис> Поиск решения и настроить экономико-математическую модель.

Свод параметров приведен в Таблице 2.2.

Таблица 2.2

Параметры задачи	Ячейки	Семантика
Целевая ячейка	$ B $16	Цель — уменьшение всех транспортных расходов.
Изменяемые данные	$ C $4:$ G $6	Объемы перевозок от каждого из заводов к каждому складу.
Ограничения	$B$ 4:$B$ 6 <=$B$1 2:$B$1 4	Количества перевезенных грузов не могут превышать производственных возможностей заводов.
	$C$ 8:$G$ 8 >=$C$1 0:$G$1 0	Количество доставляемых грузов не должно быть меньше потребностей складов.
	$ C $4:$ G $6>=0	Число перевозок не может быть отрицательным.

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

Задание № 3. Компьютерный поиск оптимального плана

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

Оформление отчета

Отчет должен содержать:

1. Определение проблемы.

2. Плановую таблицу с результатами оптимального плана.

3. Краткую характеристику программы оптимизации Поиск решения.

4. Анализ оптимального плана и решения менеджера.

5. Предложения по модификации и расширению модели.

6. Выводы обо всей проделанной работе.

 

Цель работы

1. Научиться производить расчеты резервов банка по счетам и видам вкладов за месяц, определять удельный вес резервов.

2. Освоить методику и технологию построения моделей в табличном процессоре Excel с помощью встроенных функций ПРОСМОТР, ЕСЛИ, СУММЕСЛИ.

Постановка задачи

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

Порядок выполнения работы

В Таблице 3.1 приведены справочные данные характеризующие вклады, название, код, процентную ставку. Исходные данные приведены в Таблице 3.2. Первый столбец Таблицы 3.2 содержит информацию о коде лицевого счета, второй о коде вклада, в третий необходимо ввести вид вклада на основании Таблицы 3.1, далее в столбцах заданы дата открытия счета и размер вклада.

Математическая модель расчета резервов банка по счетам и видам вкладов за месяц будет выглядеть следующим образом:

T _{общ i} — общее количество дней хранения средств на i -том счету;

T_конец —дата конца месяца;

T _{откр. i} — дата открытия вклада i -того счета;

T _нач. — дата начала месяца;

T_{отчет. i} —количество дней хранения в отчетном периоде на i -том счету;

R_i — размер резерва по i -тому счету;

S_i — размер вклада на i -том счету;

PR — годовая процентная ставка по j -тому виду вклада;

V_j — размер резерва по j -тому вкладу.

T _{общ i} = T _конец - T _{откр . i}.

Если вклад был сделан в текущем месяце, то:

T _{отчетн . i} = T _конец - T _{откр . i} = T _{общ i}

Если нет, то:

T _{отчетн. i} = T_конец - T _нач.

Вычисляем размер резерва по счету за отчетный месяц

Размер резерва по вкладу находится как сумма размеров резервов по счетам для каждого вида вклада

Ввести исходные данные, т. е. Таблицу 3.1 набрать в Excel на одном листе и Таблицу 3.2 набрать на другом. Для всех столбцов Годовая процентная ставка и Удельный вес устанавливается процентный формат, а для столбцов Размер вклада, Размер резерва по счету, Размер резерва по вкладу — денежный формат.

Характеристика вкладов

Таблица 3.1

Вид вклада	Код вклада	Годовая процентная ставка
1	2	3
Осенний	301	44%
Накопительный-60	302	45%
Капитал-40 от 300 до 2000 руб	303	40%
Капитал-40 от 2000 до 5000 руб	304	41%
Капитал-40 свыше 5000 руб	305	42%
Капитал-60 от 300 до 2000 руб	306	42%
Капитал-60 от 2000 до 5000 руб	307	43%
Капитал-60 свыше 5000 руб	308	44%
Срочный пенсионный	401	43%
Капитал-90 от 300 до 2000 руб	402	43%
Капитал-90 от 2000 до 5000 руб	403	44%
Капитал-90 свыше 5000 руб	404	45%
Накопительный-100	501	46%
Капитал-120 от 300 до 2000 руб	502	44%
Капитал-120 от 2000 до 5000 руб	503	45%
Капитал-120 свыше 5000 руб	504	46%

Справочник открытых вкладов

Таблица 3.2

Дата начала периода:	1.09.10		Дата конца периода:	1.10.10
Код лицевого счета	Код вклада	Вид вклада	Дата открытия вклада	Размер вклада, руб
1	2	3	4	5
1	503		1.04.10	4500
2	502		3.04.10	1500
3	502		3.04.10	545
4	501		7.04.10	12000
5	502		17.04.10	320
6	501		20.04.10	15000
7	404		15.05.10	5542
8	404		20.05.10	14560
9	403		30.05.10	3890
10	402		7.07.10	900
11	403		9.07.10	2980
12	401		16.07.10	12350
13	503		18.07.10	2968
14	401		8.08.10	3540
15	403		9.08.10	4500
16	402		12.08.10	865
17	502		14.08.10	1420
18	307		16.08.10	1320
19	307		30.08.10	4425
20	308		5.09.10	25000
21	302		10.09.10	32000
22	301		24.09.10	42500
23	301		30.09.10	31250

Задание № 1. Определить вид вклада для каждого лицевого счета

Для данных столбца 2 задать проверку на правильность ввода данных (Данные, Проверка, Тип данных …) — целые числа в диапазоне от 100 до 999. Чтобы убедиться, что проверка работает можно изменить любое значение на двузначное число. Столбец 3 заполнить с использованием функции ПРОСМОТР. Для этого в первой ячейке столбца 3 вводим функцию просмотр, выбираем аргументы: «искомое_значение; просматриваемый_вектор; вектор_результатов». В качестве искомого значения задаем код вклада Таблицы 3.2, просматриваемый_вектор — вектор кода вкладов Таблицы 3.1, вектор_результатов — вектор вида вкладов Таблицы 3.1. Затем фиксируем все интервалы ячеек знаком $ и копируем с вычислением по всему столбцу.

Задание № 2. Расчет резерва по счетам в месяце …

На третьем листе создать Таблицу 3.3. Вместо многоточия необходимо поставить месяц, соответствующий периоду Таблицы 3.2. Код лицевого счета, Код вклада, Вид вклада выбираются автоматически ссылкой из Таблицы 3.2.

Общее количество дней хранения = Дата конца месяца – Дата открытия вклада.

Расчет резерва по счетам в месяце: ……….

Таблица 3.3

Код лицевого счета	Код вклада	Вид вклада	Общее количество дней хранения	Количество дней хранения в отчетном периоде	Годовая процентная ставка	Размер резерва по счету
1.	2.	3.	4.	5.	6.	7.

Для расчета значений столбца 5 реализовать следующий алгоритм: ЕСЛИ вклад был сделан в текущем месяце (т.е., если общее количество дней хранения < (дата конца месяца – дата начала месяца)), то Количество дней хранения в отчетном периоде = Дата конца месяца – Дата открытия вклада, ИНАЧЕ: Количество дней хранения в отчетном периоде = количеству дней в отчетном периоде (дата конца месяца – дата начала месяца). Использовать функцию ЕСЛИ.

Годовая процентная ставка заполняется с использованием функции ПРОСМОТР. Искомое значение, просматриваемый_вектор и вектор_результатов определите самостоятельно.

Размер резерва по счету = Размер вклада * Годовая процентная ставка / 365 * Количество дней хранения в отчетном периоде.

Задание № 3. Расчет резерва по видам вкладов в месяце …

На четвертом листе создать Таблицу 3.4. Код вклада, Вид вклада выбираются автоматически из Таблицы 3.1. Размер резерва по виду вклада рассчитывается с использованием функции СУММЕСЛИ. В качестве интервала выбираются значения столбца Код вклада Таблицы 3.3, критерий — код вклада (первое значение в столбце 1 Таблицы 3.4), сумм_интервал — значения столбца Размер резерва по счету Таблицы 3.3.

Расчет резерва по видам вкладов в месяце: ……….

Таблица 3.4

Код вклада	Вид вклада	Размер резерва по вкладу, руб.	Удельный вес, %
1	2	3	4
Всего	—

Провести расчет в итоговой строке по столбцу 3. Рассчитать удельный вес резерва по каждому виду вклада, для этого нужно каждое значение столбца Размер резерва по вкладу Таблицы 3.4 разделить на итоговое значение.

Оформление отчета

Отчет должен содержать:

1. Цель работы.

2. Расчеты резервов по счетам и видам вкладов за месяц.

3. Синтаксис формул вводимых в ячейки столбца 3 Таблицы 3.2, столбцов 3-7 Таблицы 3.3, столбцов 3, 4 Таблицы 3.4.

4. Краткую характеристику встроенных функций ПРОСМОТР, ЕСЛИ, СУММЕСЛИ.

5. Анализ полученной модели.

6. Выводы обо всей проделанной работе.

 

Цель работы

Построить модель с помощью которой исследовать зависимость поступлений в бюджет от величины налоговой ставки.

Постановка задачи

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

Порядок выполнения работы

Законодатель объявляет ставку налога 70%.

Фирма имеет начальный капитал 100 млн. руб., начальное сальдо бюджета принимаем за 0. Рентабельность фирмы 120%. Исследуемый период — 10 лет. Имитационную модель налоговых отношений государства и фирмы рекомендуется представить на листе MS Excel в соответствии с Таблицей 4.1.

Таблица 4.1

	B	C
1

Государство