Построение простой компьютерной модели экономического или другого процесса. Проведение исследования на основе построенной компьютерной модели.

ПРАКТИЧЕСКАЯ РАБОТА
(с использованием табличного процессора MS Exel)

Тема: Построение простой компьютерной модели физического, биологического или другого процесса. Проведение исследования на основе построенной компьютерной модели.
Цель: закрепить алгоритм моделирования на примере построения компьютерной модели, научиться анализировать результаты моделирования, научиться использовать шаблон готовой компьютерной модели для решения типовых задач.
Теоретические сведения к практической работе
Компьютерная модель или численная модель - компьютерная программа, реализующая представление объекта, системы или понятия в форме, отличной от реальной, но приближенной к алгоритмическому описанию, включающей и набор данных, характеризующих свойства системы и динамику их изменения со временем.
Построение компьютерной модели базируется на абстрагировании от конкретной природы явлений или изучаемого объекта-оригинала и состоит из двух этапов - сначала создание качественной, а затем и количественной модели. Чем больше значимых свойств будет выявлено и перенесено на компьютерную модель - тем более приближенной она окажется к реальной модели, тем большими возможностями сможет обладать система, использующая данную модель. Компьютерное моделирование заключается в проведении серии вычислительных экспериментов на компьютере, целью которых является анализ, интерпретация и сопоставление результатов моделирования с реальным поведением изучаемого объекта и, при необходимости, последующее уточнение модели и т. д.
Различают аналитическое и имитационное моделирование. При аналитическом моделировании изучаются математические (абстрактные) модели реального объекта в виде алгебраических, дифференциальных и других уравнений, а также предусматривающих осуществление однозначной вычислительной процедуры, приводящей к их точному решению. При имитационном моделировании исследуются математические модели в виде алгоритма(ов), воспроизводящего функционирование исследуемой системы путем последовательного выполнения большого количества элементарных операций.
Основные этапы компьютерного моделирования:
Постановка задачи и её анализ:
1.1. Определить цель создаваемой модели.
1.2. Уточнить исходные результаты и в каком виде следует их получить.
1.3. Определить исходные данные для создания модели.
Построение информационной модели:
2.1. Определить параметры модели и выявить взаимосвязь между ними.
2.2. Оценить, какие из параметров влиятельные для данной задачи, а какими можно пренебрегать.
2.3. Математически описать зависимость между параметрами модели.
Разработка метода и алгоритма реализации компьютерной модели:
3.1. Выбрать или разработать метод получения исходных результатов.
3.2. Составить алгоритм получения результатов по избранным методом.
3.3. Проверить правильность алгоритма.
Разработка компьютерной модели:
4.1. Выбрать средства программной реализации алгоритма на компьютере.
4.2. Разработать компьютерную модель.
4.3. Проверить правильность созданной компьютерной модели.
Проведение эксперимента:
5.1. Разработать план исследования.
5.2. Провести эксперимент на базе созданной компьютерной модели.
5.3. Проанализировать полученные результаты.
5.4. Сделать выводы насчет свойств прототипа модели.
В процессы проведения эксперимента может выясниться, что нужно:
скорректировать план исследования;
выбрать другой метод решения задачи;
усовершенствовать алгоритм получения результатов;
уточнить информационную модель;
внести изменения в постановку задачи.
В таком случае происходит возвращение к соответствующему этапу и процесс начинается снова.

Универсальность моделей - это свойство, позволяющее применять модели при описании или функционировании однотипных групп объектов.
Содержание работы:

Задание № 1.
Построить простую компьютерную модель экономической задачи (с применением MS Excel) на примере составления штатного расписания автотранспортного предприятия.
Основные этапы компьютерного моделирования:
1. Постановка задачи:
Генеральный директор АТП должен составить штатное расписание, т.е. определить, сколько сотрудников, на каких должностях и с каким окладом он должен принять на работу.

2. Построение информационной модели:
Исходными данными являются:
Общий месячный фонд зарплаты, который составляет 10 000$.
Для нормальной работы АТП нужно:
5 - 7 диспетчеров;
8 - 10 ремонтных рабочих;
10 - 12 водителей;
1 заведующий гаражом;
3 механика;
1 главный инженер;
1 зам. директора по эксплуатации;
1 генеральный директор.
На некоторых должностях число людей может меняться. Например, руководитель может принять решение сократить число диспетчеров, чтобы увеличить оклад каждому из них.

3. Разработка метода и алгоритма реализации компьютерной модели:
Допустим, что:
Ремонтный рабочий должен получать в 1,5 раза больше диспетчера, т.е. A=1,5 B=0;
водитель в 3 раза больше ремонтного рабочего, т.е. А=3 B=0;
механик на 30$ больше, чем водитель, т.е. A=3 B=30;
зав. гаражом в 2 раза больше диспетчера, т.е. A=2 B=0;
зам. директора на 40$ больше ремонтного рабочего, т.е. A=1,5 B=40;
главный инженер в 4 раза больше диспетчера, т.е. A=4 B=0;
Ген. директор на 20$ больше главного инженера, т.е. A=4 B=20

4. Разработка модели:
Генеральный директор принимает для себя следующую модель задачи. За основу берется оклад диспетчера, а все остальные вычисляются через него.


Математическая модель
Каждый оклад является линейной функцией от оклада диспетчера и рассчитывается по формуле: А ( С+В
где C - оклад диспетчера;
A - коэффициент, который определяет во сколько раз оклад какой-либо должности больше оклада диспетчера;
B - коэффициент, который определяет, на сколько оклад какой-либо должности больше оклада диспетчера.
Задав количество человек на каждой должности, можно составить уравнение:
N1 ( (A1 ( C+B1) +N2 ( (A2 ( C+B2) +...+N8 ( (A8 ( C+B8)=10000
где N1 - количество диспетчеров, N2 - количество ремонтных рабочих, и т.д.;
A1.
·..A8 и B1...B8 - коэффициенты для каждой должности.
В этом уравнении нам известны A1...A8 и B1...B8, но не известны C и N1...N8.
Решить такое уравнение можно путем подбора.
Взяв первоначально какие-либо приемлемые значения неизвестных, подсчитаем сумму. Если фонд заработной платы превышен, то можно снизить оклад диспетчера, либо отказаться от услуг какого-либо работника, и т.д., пока эта сумма не будет равна установленному фонду оплаты труда. Проделать такую работу вручную трудно. Для создания данной модели используется MS Excel 2010 (2013).
Компьютерная модель
Создайте таблицу и сохраните её в свою папку под именем Штатное расписание:
A
B
C
D
E
F
G
H

коэф.
А
коэф.
В
Должность
Зарплата
сотрудника
Кол-во
сотрудников
Суммарная
зарплата

Зарплата
диспетчера

1
0
Диспетчер

7


150,00

1,5
0
Ремонтный рабочий

9




3
0
Водитель

10




3
30
Механик

3




2
0
Зав. гаражом

1




1,5
40
Зам. директора

1




4
0
Гл. инженер

1




4
20
Ген. директор

1






Суммарный месячный фонд зарплаты:




В столбце D следует вычислить заработную плату для каждой должности.
В постановке задачи было объяснено, что заработная плата вычисляется по формуле А ( С+В. В нашей таблице коэффициенты А и В находятся в столбцах А и В, а С - зарплата диспетчера указана в ячейке Н2. Обратите внимание, что формулы вычисления зарплаты сотрудников должны содержать абсолютный адрес ячейки Н2.
В ячейку D2 введите формулу =A2*$H$2+B2
Скопируйте формулу из ячейки D2 в ячейки D3:D9.
При копировании адрес ячейки с зарплатой диспетчера остался постоянным (абсолютным), а адреса A2 и B2 перенастраиваются (они относительные).
В столбце F следует вычислить заработную плату всех сотрудников каждой должности.
В столбце Е указано количество сотрудников каждой должности. Данные в ячейках E2:E4 могут изменяться в пределах штатного расписания, а количество сотрудников на других должностях неизменно (см. постановку задачи).
В ячейку F2 введите формулу =D2*E2 (т.е. "зарплата" * "количество сотрудников").
Скопируйте формулу из ячейки F2 в F3:F9.
В ячейке F10 найдите суммарный месячный фонд заработной платы всех сотрудников, т.е. сумму значений ячеек F2:F9.
Оформите таблицу:

Составьте штатное расписание: вносите изменения в зарплату диспетчера в ячейке H2 или меняйте количество сотрудников в ячейках E2:E4 (см. постановку задачи) до тех пор, пока полученный суммарный месячный фонд заработной платы не будет равен заданному (т.е. в ячейке F10 необходимо получить значение приблизительно равное 10000).
Сохраните таблицу и предъявите преподавателю файл работы Штатное расписание с 1 листом: Модель

Задание № 2.
4. Компьютерный эксперимент:
Составьте штатное расписание с использованием функции автоматизации расчетов – Подбор параметра.
Функция Подбор параметра удобное средство Excel для анализа “Что - если”. При этом подбирается такое значения для ячейки с изменяемым параметром, чтобы число в целевой ячейке стало равно заданному.
Выберите команду меню Данные, Анализ ”Что - если”, Подбор параметра и:
укажите в окне. Установить в ячейке адрес целевой ячейки F10 (Фонд заработной платы);
введите в окно Значение - 10000;
укажите в окне. Изменяя значение ячейки адрес ячейки H2 (зарплата диспетчера), т.е. адрес именно той ячейки, от которой зависит расчет всей таблицы;
нажмите OK.
Начнется процесс подбора параметра. На рисунке показан результат подбора параметра.







Если нажать на кнопку OK, значения ячеек в таблице будут изменены в соответствии с найденным решением.
Создайте лист и переименуйте его в Варианты.
Составьте 4 варианта штатного расписания и оформите их в виде таблицы: Варианты штатного расписания

A
B
C
D
E
F
G
H

12
Варианты штатного расписания

13

Диспетчер
Рем. рабочий
Водитель
Зарплата
диспетчера

14
Вариант 1 (минимальное количество сотрудников)
5
8
10


15
Вариант 2 (максимальное количество сотрудников)
7
10
12


16
Вариант 3 (среднее количество сотрудников)
6
9
11


17
Вариант 4 (среднее количество сотрудников)
6
10
10



Для каждого из 4-х вариантов:
в основной таблице с компьютерной моделью измените, количество сотрудников на должностях диспетчера, ремонтного рабочего и водителя (фонд заработной платы в ячейке F10 сразу изменится);
подберите зарплату диспетчера в новых условиях с использованием функции Подбор параметра;
скопируйте найденное решение в таблицу Вариантов штатного расписания.
Сохраните таблицу.

Анализ результатов моделирования

Создайте копию листа Модель и переименуйте его в Отчет.
Выберите один из 4-х (оптимальный с Вашей точки зрения) вариант штатного расписания. Выбор обоснуйте.
На листе Отчет оформите таблицу, например, как на рисунке.
















Вставьте перед таблицей две строки (1-ю и 2-ю).
Оставьте столбцы C, D, E и F, а столбцы. А, В, G, Н скройте, выполнив команду Скрыть контекстно-зависимого меню.


Внимание! Удалять столбцы A , В и H нельзя, так как в таблице на них есть ссылки, но их можно скрыть (при этом ширина столбцов становиться равной нулю).
















13 SHAPE \* MERGEFORMAT 1415


Примечание: чтобы отобразить скрытый столбец (например, столбец Н) следует выделить соседние с ним столбцы (столбцы G и I) и выполнить команду меню Формат, Столбец, Отобразить или пункт Показать контекстно-зависимого меню.

Введите заголовок ”Штатное расписание АТП” и подзаголовок “Генеральный директор и Ваши Ф.И.О.”
Оформите заголовок и подзаголовок.
Добавьте своё оформление.
Сохраните файл.
Подготовьте листы Варианты и Отчет к печати:
выберите альбомную ориентацию страницы;
укажите в верхнем колонтитуле фамилию, а в нижнем - дату и время.
Сохраните файл.
Предъявите преподавателю файл АТП с 3 листами: Модель, Варианты, Отчет










13PAGE \* MERGEFORMAT14815