«ОСНОВЫ ИНФОРМАТИКИ ЛАБОРАТОРНЫЙ ПРАКТИКУМ Для студентов экономического факультета БГУ МИНСК БГУ 2005 УДК 004 (076.5) ББК 32.973.26–018.2я73 В75 Р е ц е н з е н т ы: кандидат физико-математических наук В. В. Горячкин; ...»

• диаграмму распределения процента прибыли по видам продукции за первый и второй кварталы;

• диаграмму распределения процента прибыли по регионам за первый квартал.

ПРИНЯТИЕ РЕШЕНИЙ

Задание 1. Задача об оптимальном ассортименте.

Предприятие выпускает два вида продукции. Цена единицы первого вида продукции – 25 000, второго – 50 000. Для изготовления продукции используются три вида сырья, запасы которого составляют 37, 57,6 и 7,0 условных единиц. Нормы затрат каждого сырья на единицу продукции представлены в следующей таблице.

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

Выполнение 1. Такие задачи решаются при помощи инструмента Excel Поиск решения. Для установки этого инструмента необходимо:

Главное меню: Сервис / Надстройки / Установить флажок «Поиск решения» / OK.

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

2. Математическая модель задачи. Пусть продукция производится в количестве:

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

для которой необходимо найти максимум.

При этом следует учесть ограничения по запасам сырья:

и по смыслу задачи x1, x2 должны быть неотрицательными и целыми:

3. Ввод исходных данных в компьютер.

3.1. Введем целевую функцию и ограничения. Для переменных x1, x2 определим соответственно ячейки С2 : D2 и зададим им начальные значения, равные нулю. Затем коэффициенты целевой функции и нормы расхода сырья расположим соответственно под неизвестными в ячейках С3 : D3 и С6 : D8. Запасы сырья расположим справа от матрицы норм расхода в ячейках G6 : G8. В ячейке F2 вычислим значение целевой функции, а в ячейках F6 : F8 – реальный расход сырья.

3.2. Задание параметров для диалогового окна Поиск решения. Выполните команду Сервис / Поиск решения.

В диалоговом окне Поиск решения нужно указать:

• адрес ячейки, в которой находится формула, вычисляющая значение целевой функция;

• цель вычислений (задать критерий для нахождения экстремального значение целевой функции);

• адреса ячеек, в которых находятся значения изменяемых переменных х1, х2;

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

• параметры решения задачи, для чего нажимается кнопка Параметры.

Диалоговое окно Поиск решения и схема расположения исходных данных приведены ниже. Информация в этом окне соответствует решаемой задаче.

После ввода всех данных и задания параметров нажать кнопку Выполнить.

Задание 2. Сетевая транспортная задача.

На складах имеется груз, количество которого определяется в следующей таблице.

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

Стоимость перевозок определяется таблицей:

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

Задание 3. Балансовые модели.

Имеется трехотраслевая балансовая модель экономики с матрицей коэффициентов затрат:

Производственные мощности отраслей ограничивают возможности ее валового выпуска числами 300, 200, 500. Определить оптимальный валовой выпуск всех отраслей, максимизирующий стоимость суммарного конечного продукта, если задан вектор цен на конечный продукт (2, 5, 1).

а) Решите эту же задачу, если на конечный продукт накладываются следующие ограничения: валовой выпуск продукции первой и третьей отрасли относятся как 2:1 и конечный выпуск второй отрасли не должен превосходить 100.

б) К данным задачи заданы коэффициенты прямых затрат труда на выпуск продукции каждой отрасли: 0,2, 0,3, 0,15. Определите максимально возможный выпуск конечного продукта в стоимостном выражении, если суммарные затраты труда не должны превышать 70 ед.

Задание 4. Задача о смесях.

Фирма «Корма» имеет возможность покупать четыре различных вида зерна (компонентов смеси) и изготавливать различные виды кормов. Зерновые культуры содержат разное количество питательных ингредиентов. Произведенный комбикорм должен удовлетворять некоторым минимальным требованиям с точки зрения питательности. Требуется определить, какая из возможных смесей является самой дешевой.

Исходные данные приведены в следующей таблице.

Затраты в расчете на ед. веса (цена)

ПРИМЕНЕНИЕ ЭЛЕМЕНТОВ УПРАВЛЕНИЯ

В EXCEL

1. Используя элементы управления Переключатель и Рамка составить формулу для нахождения суммы всех, положительных или отрицательных значений из диапазона ячеек A10 : D11, в зависимости от установки переключателей.

Для выполнения задания необходимо:

• вывести панель инструментов Форма;

• перенести элемент управления Переключатель на лист Excel и установить желаемые размеры. Сверху от переключателя должно остаться свободное место. Оно понадобится при объединении переключателей в группу;

• ввести название этого переключателя, например Положительные.

• повторить последние два шага для размещения переключателей Отрицательные и Все;

• выбрать элемент управления Рамка и перенести его на лист Ехcel таким образом, чтобы он охватывал, ранее построенные переключатели. Ввести название группы – Суммировать;

• щелкнуть правой кнопкой мыши по любому из переключателей и из контекстного меню выберать Формат объекта и установить связь между переключателями и ячейкой Excel, например A1;

• ввести формулу, вычисляющую требуемую сумму.

Используемые функции: ECЛИ, СУММЕСЛИ, СУММ.

Примерный вид решения задачи:

2. Используя элементы управления Флажок и Рамка, для ввода исходных данных, решите предыдущую задачу 3. Используя элементы управления Счетчик и Поле со списком, для ввода исходных данных, постройте календарь на заданный год и месяц, который должен иметь примерно следующий вид:

Для выполнения задания необходимо:

• вывести панель инструментов Форма;

• перенести и расположить элементы управления Счетчик и Поле на листе Excel;

• связать элементы управления с ячейками Excel;

• ввести формулу, заполняющую значения ячеек.

Используемые функции: ECЛИ, МЕСЯЦ, ДАТА, ДЕНЬНЕД 4. Построите календарь на заданный месяц указанного года, используя элементы управления Полоса прокрутки и Список, для ввода необходимых исходных данных. Дни недели расположите по вертикали, начиная с понедельника.

5. Решите задачи 3 и 4, используя формулы массивов.

6. Составьте макрокоманду, изменяющую в выделенном диапазоне размер и тип шрифта, цвет и обрамляющую диапазон. Обеспечить возможность выполнения построенной макрокоманды с помощью меню, панели инструментов, клавиатуры и с помощью элемента управления Кнопка.

ИТЕРАЦИОННЫЕ ВЫЧИСЛЕНИЯ

Задание 1. Составьте электронную таблицу расчета отпускной цены изделия (калькуляцию) исходя из затрат на сырье, материалы и основную заработную плату.

Выполнение 1. Постройте таблицу, введите исходные данные.

2. Задайте необходимые вычисления при помощи формул.

3. Выполните форматирование таблицы при помощи команды Формат / Ячейки / Число или контекстного меню.

4. Выполните вычисление в электронной таблице, для этого необходимо разрешить циклические ссылки при помощи команды Сервис / Параметры / Вычисления / Итерации.

5. Сделайте обрамление клеток таблицы, см. Формат / Ячейки / вкладка Границы.

6. Уберите сетку таблицы. См. Сервис / Параметры / вкладка Вид.

7. Сохранить таблицу в файле. См. Файл / Сохранить как, Сохранить.

8. Напечатайте таблицы. См. Файл / Параметры страницы, Область печати, Предварительный просмотр, Печать.

Внимание! Изучите тщательно алгоритм «Калькуляции». Обратите внимание на то, что 8-й пункт «Калькуляции» использует результат вычисления 13-го пункта, который еще не вычислен и в этом пункте используются результаты вычислений пунктов 10 и 12. А 10-й пункт использует результаты вычислений предыдущих пунктов и 8-го в том числе. Такие вычисления содержат так называемые циклические ссылки.

Наименование продукции_ Калькуляционная единица_ 10 Произведенная себестоимость 13 Оптовая цена 19 Отпускная цена Электронная таблица для расчета отпускной цены изделия (калькуляция) Наименование продукции Ваза Калькуляционная единица штука 5 Отчисления на соц.страхование = 35 %*(C6+C7) 7 Отч. на детск. дошк. учреждения = 5 %*(C6 + C7) 10 Производственная себестоимость = СУММ(C5 : C13) 16 Налог на добавленную стоимость = 20 %*C ЭкономистИванова В. B.

Экономист Иванова В. В.

Задание 2. Составьте электронную таблицу для приближенного решения уравнения сos(x+0,5) = x^3. методом половинного деления.

Выполнение Уравнение F(x) = 0 будем рассматривать на отрезке [a;b], внутри которого находится только один корень уравнения и функция F(x) непрерывна. Пусть h = (b – a)/N, где N = 10.

В столбце х вычислим значения a, a + h,...,a + (N – 1) h, b, а в столбце F(x) – соответствующие значения функции F(x).

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

Ниже приведена электронная таблица для решения этой задачи.

Приближенное значение приближенного значения корня

ФИНАНСОВЫЕ РАСЧЕТЫ

ПО ПРОСТЕЙШИМ СХЕМАМ

Основные понятия финансовой операции.

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

Если Т – продолжительность сделки в годах, t – продолжительность сделки в днях, K – временная база (360 или 365 дней), то T = t / K.

Для расчета финансовых операций применяют:

• схему простых процентов;

• схему сложных процентов;

• комбинированную схему.

Схема простых процентов применяется в краткосрочных операциях, если продолжительность сделки не больше года. Начисления ведутся на одну и ту же сумму А0 и величина процентных начислений пропорциональна длительности сделки. Наращенная сумма А находится по формуле Схема сложных процентов означает, что начисленные проценты прибавляются к сумме долга. Для вычисления наращенной суммы применяют формулу В практике применяется начисление процентов несколько раз в году: ежемесячно, поквартально, раз в полгода. Если m – количество начислений, то наращенная сумма за T лет будет равна Комбинированную схему применяют, когда Т > 1 и не является целым числом. Если n – целая часть Т; – дробная часть Т, то Задание 1. Вклад в сумме 100 000 руб. вносится в банк под 40 % годовых на 1,5 года. Рассчитайте наращенную сумму по схемам простых и сложных процентов и комбинированной схеме.

Ответ: простые – 160 000 руб., сложные – 165 650 руб., комбинированные – 168 000 руб.

Задание 2. Определите сумму первоначального вклада, который обеспечивает клиенту ежегодные выплаты в сумме 10 млн руб. в течении 5 лет (сложные проценты, 65 % процентов годовых).

Ответ: 23,308980 млн руб. при выплате в начале периода и 14,126650 млн руб. при выплате в конце периода.

Задание 3. Через 2,5 года Вам понадобится для покупки дачи 30 млн.

руб. Какую сумму для этого необходимо положить в банк, если ставка сложных процентов – 40 % годовых. Сделайте расчеты по схеме сложных процентов и комбинированной схеме.

Ответ: сложные 12,93 млн руб., комбинированные – 12,755 млн руб.

Замена платежей при схеме простых процентов.

Под заменой платежей понимается продление срока платежа, замена нескольких платежей одним, замена одного платежа несколькими и т. д. При замене платежей не должны пострадать обе стороны сделки.

Заемщик занял денежные суммы S1, S2,…, Sn, обязуясь возвратить долг кредитору в установленные сроки V1, V2,…, Vn при постоянной ставке процентов р для всех платежей. В дальнейшем платежи S1, S2,…, Sn решено заменить одним со сроком V. Такая финансовая операция называется консолидацией платежей. Необходимо найти сумму S консолидированного платежа.

Будем считать, что сроки платежей упорядочены:

платежей (пролонгация) на дней соответственно. По схеме простых процентов Задание 4. Два платежа S1 = 100 000 руб., V1 = 12.02.1999 г. и S2 = 150 000 руб., V2 = 15.03.1999 г. заменяются одним платежом со сроком V = 5.04.1999 г. Стороны договорились на замену платежей при р = 50 % годовых. Найдите величину консолидированного платежа.

Ответ: при К = 360 примерно 261 388 руб.

б) Пусть теперь Vm < V < Vm. В этом случае m платежей пролонгируются, а платежи, начиная с m + 1 выплачиваются ранее намеченных сроков. Величина консолидированного платежа определяется формулой Задание 5. Три платежа S1 = 100 000 руб., V1 = 15.05.1999 г.; S2 = 150 000 руб., V2 = 15.06.1999 г. S3 = 200 000 руб., V3 = 15.08.1999 г; заменяются одним платежом со сроком V = 1.08.1999 г. Найти величину консолидированного платежа, если используются простые проценты при ставке р = 80 % годовых.

Ответ: при К = 360 примерно 466 828 руб.

с) Платежи S1, S2,…, Sn сроками V1, V2,…, Vn заменяются одним платежом S со сроком V, причем Необходимо найти дату консолидированного платежа. Будем считать, что ставка процента р одинакова для всех платежей. Пусть Тогда по принципу эквивалентности S1(1 + pt1/K) + S2(1+pt2/K) +…+ Sn(1 + ptn/K) = S(1 + pt/K).

Отсюда нетрудно получить и тогда Задание 6. Заемщик должен кредитору три различных суммы S1 = 1 000 руб., V1 = 11.03.2000 г.; S2 = 2 000 руб., V2 = 20.04.2000 г.; S3 = 5 000 руб., V3 = 6.05.2000 г. и желает погасить долг одним единовременным платежом 8 000 руб. Определите дату этого платежа, считая ставку процентов для всех платежей одинаковой.

Ответ: примерно 25.04.2000г..

Задание 7. Выполните расчет долгосрочного кредита при следующих условиях: сумма кредита – 24 млн руб., кредит взят на 5 лет в 1998 г., годовая ставка – 9 %. Постройте диаграмму, в которой отображается динамика изменения остатка и выплат за кредит и проценты.

(все эти функции используют одни и те же аргументы, хотя некоторые применяются Бз Будущее значение вклада Норма;Кпер;Выплата;[Нз];[Тип] Пз Текущий объем вклада Норма;Кпер; Выплата;[Бз];[Тип] ППЛАТ Величина выплаты Норма;Кпер;Нз;[Бз];[Тип] ОСНПЛАТ Выплата на основной капитал Норма;Период;Кпер;Нз;[Бз];[Тип] ПЛПРОЦ Выплата прибыли Норма;Период;Кпер;Нз;[Бз];[Тип] Норма Норма прибыли за период Кпер;Выплата;Нз;[Бз];[Тип];[Н.П.] Кпер Количество периодов Норма; Выплата;Нз;[Бз];[Тип] Приведем краткое описание аргументов.

Норма (ставка) – процентная ставка за период.

Кпер(число периодов) – общее количество платежей или периодов выплат.

Выплата – плата, производимая в каждый период и не меняющаяся за все время займа.

Бз – будущая стоимость или баланс наличности, которых нужно достичь после последней выплаты. Если Бз опущено, оно полагается равным 0 (будущая стоимость займа, например, равна 0).

Нз – текущее значение – начальная стоимость вложения или ссуды. Так, начальная стоимость ссуды равна, собственно, сумме займа.

Тип – задает режим выплат. Для выплат в конце месяца указывается значение 0, и 1 для выплат в начале месяца. Если аргумент тип опущен, то он полагается равным 0.

Н.П. – предполагаемая величина процентной ставки. Если аргумент опущен, то он полагается равным 10 процентам. Если функция Норма не сходится, следует попытаться использовать различные значения начального приближения.

ПЕРЕДАЧА ДАННЫХ МЕЖДУ ПРОГРАММАМИ

ПАКЕТА MICROSOFT OFFICE

Данные между программами передаются тремя способами:

• копирование или перемещение данных при помощи буфера обмена;

• внедрение данных;

• связывание данных.

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

Для внедрения и связывания передаваемые данные одной программы заносятся в буфер обмена и затем при помощи команды Правка / Специальная вставка помещаются в другую программу. Диалоговое окно этой команды имеет следующий вид:

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

Задание 1. Изучите способы передачи данных между программами пакета Microsoft Office на примере программ Excel и Word.

Выполнение 1. В Excel составьте таблицу.

Анализ спроса и продаж продукции фирмы «Ритм»

Телевизоры Видеомагнитофоны Музыкальные центры Видеоплееры Аудиоплееры Видеокамеры 2. По данным таблицы постройте линейчатую диаграмму.

3. В программе Word создайте новый документ.

3.1. При помощи команды копирования переместите таблицу из документа Excel в документ Word.

3.2. Диаграмму из документа Excel вставьте в документ Word двумя способами: внедрением и связыванием.

3.3. Сделайте изменения в столбцах «Спрос» и «Предложение»

таблицы документа Excel. Отметьте, что в документе Word произойдут изменения только во внедренной диаграмме.

3.4. Сохраните документ Word в файле и закройте его окно.

3.5. В документе Excel измените диаграмму: добавьте в диаграмму еще столбец «Продажа». Загрузите ранее сохраненный документ Word и отметьте произошедшие изменения в нем.

ЛИТЕРАТУРА

Брайан Андердал, Джон Уокенбах. Библия пользователя Excel 2002. М.:

Вильямс, 2003.

Виллетт Э., Крудер Д., Крудер Р. Библия пользователя Officе 2000. М.:

Вильямс, 2001.

Власенко С., Беленький Ю. Word 2002 в подлиннике. BHV, 2001.

Долженков В., Колесников Ю. Excel в подлиннике. BHV, 2002.

Конюхович П. В. Экономическая информатика: Учебник. СПб.: Питер, 2000.

Морозевич А. Н. Основы информатики. М.: Новое знание, 2003.

Хислоп Б., Энжелл Д. Microsoft Word 2000. Библия пользователя: Учеб.

пособие. М.: Вильямс, 2001.