Освоение возможностей табличного процессора Excel

 

 

Анализ отчета по результатам.

 

В разделе Целевая ячейка указана максимальная суммарная прибыль – 525 денежных единиц.

В разделе Изменяемые ячейки показан оптимальный план:

 х1 (количество изделий А) – 0;

 х2 (количество изделий Б) – 9;

 х3 (количество изделий  В) – 16;

В разделе Ограничения показано, что при оптимальном плане ресурсы Сырье и Оборудование используются полностью (Статус – связанное), а Труд недоиспользуется, о чем сообщает  Статус – несвязанное.

 

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

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

 

У недефицитных ресурсов (труд и оборудование) теневая цена равна нулю.

Сырье: 7,5х9=67,5  обеспечивает максимум выпуск продукции. 

 

2.Ценность каждого ресурса  и его приоритет при решении  задачи увеличения запасов ресурсов.

      Для того, чтобы узнать, в какой ресурс следует вкладывать средства, то есть увеличить его запасы до предельного значения, нужно для каждого ресурса вычислить произведение его теневой цены на допустимое увеличение:

 

Сырье :  7,5х130= 975денежных единиц

Оборудование: 0х57,5= 0денежных единиц

Заметим, что значение 1Е+30 в допустимом увеличении Труда свидетельствует  о том, что данный параметр не влияет на целевую функцию.

Отсюда делаем вывод, что  выгоднее всего вкладывать средства в закупку Сырье .

3.Максимальный интервал  изменения запасов каждого вида  ресурсов, в пределах которого  структура оптимального решения,  то есть номенклатура выпускаемой  продукции, остается без изменений.

В разделе Ограничения есть параметры Допустимое увеличение и Допустимое уменьшение. С помощью этих параметров можно узнать:

  а) изменение запасов  ресурса Труд изменяется от 150 до 150-68=82

  б) изменение запасов  ресурса Сырье  изменяется  от 70+130=200 до 

    70-25=45

   в) изменение запасов  ресурса Оборудование изменяется  от 100+57,5=157,5 

    до 100-65=35

 

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

 

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

По тем видам изделий, где  нормированная стоимость больше или равна нулю, делается вывод, что  выпуск этих изделий предприятию  выгоден (в нашем случае это 2 и 3 вид). Если нормированная стоимость отрицательна, то выпуск этого изделия предприятию невыгоден, и выпуск каждой единицы этого изделия снижает суммарную прибыль на указанное значение нормированной стоимости (в нашем  случае это 1 вид).

5.Насколько уменьшится стоимость  выпускаемой продукции при принудительном  выпуске единицы нерентабельной  продукции?

   Затраты ресурсов на производство единицы продукции  вычисляются путем умножения теневой цены каждого ресурса на нормы его расхода на единицу изделия.

 а) для изделия 1вида:

     0х3 +7,5х4 +0х2=30

     Прибыль от  реализации одного изделия 1 вида  составляет 25 денежных единиц, тогда  нормированная стоимость будет  равна разности прибыли и затрат  на одно изделие:

Прибыль-затраты=25-30=-4,999999999

Это означает, что производство изделия А предприятию не рентабельно.

  б) для изделия 2вида:

    0х7 + 7,5х4 + 0х9=30

 Прибыль от реализации  одного изделия  2 вида составляет 30 денежных единиц, тогда нормированная  стоимость будет равна разности  прибыли и затрат на одно  изделие:

Прибыль-затраты=  30-30=0

 

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

в) для изделия 3 вида:

  0х1 + 7,5х2 + 0х1=15

    Прибыль от  реализации одного изделия 3 вида  составляет 15 денежных единиц, тогда  нормированная стоимость будет  равна разности прибыли и затрат  на одно изделие:

Прибыль-затраты=  15-15=0

Это означает, что производство изделия 3 вида предприятию рентабельно.

6.Интервал изменения цен  на каждый вид продукции, при  котором сохраняется структура  оптимального плана.

В разделе Ограничения есть параметры Допустимое увеличение и Допустимое уменьшение. С помощью этих параметров можно узнать интервал изменение запасов каждого вида ресурсов:

 

 а) прибыль от реализации  одного изделия 1 вида изменятся  от   25+4,999999999  до 25 денежных    единиц.

    б) прибыль от  реализации одного изделия 2 вида  изменятся от 30+105=135 до 30-0=30 денежных  единиц.

    в) прибыль от  реализации одного изделия 3 вида  изменятся от 15+0=15

 до 15-2,5=12,5  денежных  единиц.

7. Как изменится общая  стоимость продукции и план  ее выпуска при увеличении  запасов каждого вида сырья  на 50 единицы?

Для этого вычислим выражение:

     0×50+7,5×50+ 0х50=375

    То есть, в данном  случае общая стоимость продукции  увеличится на 375 единицы.

8.Целесообразно ли включать  в план изделие IV вида, на изготовление  которого расходуется по 4 ед. каждого  вида ресурсов ценой 40 единиц?

Рассчитываем нормированную  стоимость:

0х4+7,5х4+0х4=30      затраты на одно изделие

40-30=10                       нормированная стоимость

Следовательно, включать в  план изделие IV вида предприятию выгодно.

 

 

Решение штатного расписания.

Задача 2

Определение штатного расписания организации, что включает в себя следующие действия:

1. построение математической модели решения задачи;
2. создание штатного расписания в Excel;
3. создание базы данных в Access «Штатное расписание»;
4. Экспорт штатного расписания из Excel в СУБД Access;
5. Создание нескольких вариантов запросов и организация на их.

 

Вариант 2/17

 

                 Условие задачи:

Штатное расписание сотрудников показано на рисунке.

 

Необходимо распределить надбавку в сумме 540 тыс. рублей. Надбавка к окладу рассчитывается путем умножения  коэффициента надбавки на среднюю заработную плату за последний квартал.

Найти решение при следующих  ограничениях: коэффициент В уборщицы должен находится в пределах от 0,12 до 0,23, младшего продавца – от 0,21 до 0,35, старшего продавца – от 0,3 до 0,45, менеджера и товароведа – от 0,51 до 0,62, директора – от 0,75 до 0,8, заместителя директора – от 0,55 до 0,75

 

 

Решение:

Построение математической модели задачи.

За основу возьмем коэффициент  надбавки уборщицы, а остальные коэффициенты будем вычислять, исходя из него: во столько-то раз или на столько-то больше.

Для распределение надбавки в сумме 540 тыс.рублей , необходимо рассчитать коэффициент  надбавки и надбавку к з/п .

Надбавка к окладу рассчитывается путем умножения коэффициента надбавки на среднюю заработную плату за последний  квартал.

В режиме просмотра формул эта таблица имеет вид:

Следует также вычислить  суммарную надбавку, путем умножения  надбавки з/п на количество работников

 

 

Затем нужно вызвать команду Поиск решения. В появившемся диалоговом окне Поиск решения: в качестве целевой ячейки необходимо установить адрес $J$3, где находится сумма надбавки:

 

 

Установим значение целевой  ячейки равное числу 540.

 Изменяемыми  ячейками, то есть ячейками результата, являются ячейки, коэффициент  надбавки и  суммарная надбавка  ($F$3:$F$9;$I3)

Затем нужно установить ограничения. В качестве ограничений нужно  установить допустимый диапазон варьирования изменяемых ячеек. Например, уборщицы должен находится в пределах от 0,12 до 0,23 . Ограничения устанавливаются с помощью кнопки Добавить.

В левой части окна указывается  адрес изменяемой ячейки, например адрес $F$3, содержащий коэффициент надбавки. Затем указывается нужный знак, например, <=, в правой части вводится предельно допустимое значение, по условию задачи равное числу 0,23. Таким же образом, устанавливаются условия: $F$3>=0,12. Аналогично заполняются остальные ограничения. Путем нажатия кнопки Выполнить получим решение задачи:

 

 

 

 

 

 

Использование возможности  ACCESS

Программа ACCESS

Microsoft Access в настоящее время является одной из самых популярных среди настольных (персональных) программных систем управления базами данных Среди причин такой популярности следует отметить:

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

- глубоко развитые возможности  интеграции с другими программными  продуктами, входящими в состав  Microsoft Office, а также с любыми программными продуктами, поддерживающими технологию OLE;

- богатый набор визуальных  средств разработки.

Работы с объектами  базы данных унифицирован. По каждому из них предусмотрены стандартные режимы работы:

- Создать - предназначен для создания структуры объектов;

- Конструктор - предназначен  для изменения структуры объектов;

- Открыть (Просмотр, Запуск) - предназначен для работы с  объектами базы данных.

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

Специфической особенностью СУБД Access является то, что вся информация, относящаяся к одной базе данных, хранится в едином файле. Такой файл имеет расширение *.mdb. Данное решение, как правило, удобно для непрофессиональных пользователей, поскольку обеспечивает простоту при переносе данных с одного рабочего места на другое. Внутренняя организация данных в рамках mdl формата менялась от версии к версии, но фирма Microsoft поддерживала их ее вместимость снизу вверх, то есть базы данных из файлов в формате ранних вер сии Access могут быть конвертированы в формат, используемый в версиях боле поздних.

Основными понятиями или  объектами этой системы являются: таблицы, запросы, формуляры, отчеты, макросы  и модули.

Запросы служат для отбора данных по какому-либо признаку или  комбинации признаков. Выборка данных выполняется для просмотра записей  или их изменения. Запросы можно  создавать либо с помощью мастера, либо в режиме конструктора.

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

База данных это набор  данных, который связан с определенной темой или назначением. Когда  Вы создаете базу данных в Microsoft Access, Вы создаете один файл, который содержит все таблицы, запросы, формуляры, отчеты и другие объекты, которые помогают Вам использовать информацию в базе данных.

Созданную базу данных можно  наполнить объектами различного рода и выполнять операции с ними. Но с базой данных можно выполнять  операции как с неделимым образованием. Все операции такого рода - операции управления базой данных - сосредоточены  в меню File прикладного окна Access или в окне базы данных.