Использование табличного процессора MS Excel для реализации численных методов в инженерных и экономических расчетах
Министерство образования РФ
Нижегородский государственный
технический университет им. Р.Е. Алексеева
Факультет Экономики, Менеджмента и
Инноваций
Кафедра: "Менеджмент"
Курсовая работа на тему:
Использование табличного процессора
MS Excel для реализации численных методов в инженерных и экономических расчетах
Выполнил: студент группы 10-МЕНк
Селина Анастасия Сергеевна
Проверил: Зубов Николай Викторович
Нижний Новгород 2011 год
Содержание
Введение
Лабораторная работа № 1. "Задача максимизации прибыли
предприятия"
Лабораторная работа № 2. "Модель Леонтьева"
Лабораторная работа № 3. "Предельный анализ и оптимизация
прибыли, издержек и объема производства"
Заключение
Используемая литература
Введение
При решении многочисленных инженерных и экономических задач
обычно реальное явление заменяется математической моделью. Модель является
упрощенным представлением реальности и обычно содержит некоторое количество
уравнений. Главной задачей моделирования является максимальное приближение к
реальности при достаточной простоте модели. В ряде случаев удается найти
аналитическое решение задачи. Однако в большинстве своем приходится
использовать численные методы. Эти методы предполагают, применение ЭВМ и
сводятся к некоторым действиям над числами. При этом в большинстве случаев
решение является приближенным.
Существуют различные подходы к реализации численных методов.
Традиционный подход предполагает построение алгоритма метода с последующим
программированием на языке высокого уровня. В последнее время широко используются
специализированные программные продукты - математические пакеты типа MathCad,
которые существенно упрощают процесс составления алгоритма и обладают
встроенными библиотеками и графическими возможностями. В данной работе описан
еще один подход, позволяющий в ряде случаев существенно ускорить процесс
решения задачи. Он основан на использовании табличного процессора Excel, широко
распространенного среди пользователей. Вместе с тем, применение данного
программного продукта для реализации численных методов до сих пор не нашло
соответствующего отражения в литературе.
Методическая разработка содержит краткое описание некоторых
численных методов, примеры инженерных и экономических задач и технологию их
решения с использованием пакета Excel. Предполагается наличие у студентов
основных навыков работы с электронными таблицами типа Excel.
Лабораторная
работа № 1. "Задача максимизации прибыли предприятия"
Одной из распространенных экономических задач является задача
максимизации прибыли предприятия. Известно, что балансовая прибыль есть разница
между выручкой и затратами на производство продукции P=N-Z. В общем случае выручка от
реализации продукции может быть представлена полиномом 2-й степени от
количества продукции N=a0Q+a1Q2. Нелинейность может быть
связана с тем, что в условиях монополии цена единицы продукции k может уменьшаться с
ростом количества выпущенной продукции Q: k=a0+a1Q (a0>0, a1<0). В свою очередь,
функция затрат может быть представлена полиномом 3-й степени Z=b0+b1Q+b2Q2 +b3Q3. Кубическая нелинейность
может объясняться тем, что при производстве малой партии товаров издержки
быстро растут, затем с ростом Q темп роста издержек уменьшается, но по
достижении некоторого критического значения Q начинает работать
"закон убывающей отдачи", в соответствии с которым издержки вновь
начинают расти ускоренными темпами. Прибыль максимальна, когда dP/dQ = 0. С помощью пакета Excel решим данную задачу,
полагая заданными коэффициенты: b0 = 0, b1=1, b2= 7, b3 = - 0,15, a0= 15, a1= 3, a2=-0,1, a3=0,01.
Последовательность действий при реализации в пакете Excel
. Оформить заголовок в строке 1 "Максимизация
прибыли".
. В ячейки A3, ВЗ, СЗ, D3 и ЕЗ записать заголовки
рядов - соответственно Q, N, Z, P, и dP/dQ.
. В ячейки F3, F4, F5, F6, F9, F10 записать названия коэффициентов - соответственно
b0, b1, b2, b3, a0, a1.
. В ячейки G3, G4, G5, G6, G9, G10, G11 записать значения коэффициентов - соответственно 15; 3; -
0,1; 0,01; 0; 7; - 0,15.
. В ячейку Н9 ввести текст "Издержки Z=
a0+a1*Q+a2*Q^2+a3*Q^3"
. В ячейку Н10 ввести текст "Выручка
N=b0+b1*Q+b2*Q^2"
. В ячейку Н11 ввести текст "Прибыль P=N-Z"
. В ячейки А4 и А5 ввести первые два значения аргумента - 0 и
1.
. Выделить ячейки А4-А5 и протащить ряд данных до конечного
значения
(21), убедившись в правильном выстраивании арифметической
прогрессии.10. В ячейку В4 ввести формулу
"=$G$9+$G$10*A4+$G$11*A4^2".
. Скопировать формулу на остальные элементы ряда, используя
прием протаскивания. В интервале В4: В25 получен ряд результатов вычисления
выручки N
(Q).
. В ячейку С4 ввести формулу " =$G$3+$G$4*A4+$G$5*A4^2+$G$6*A4^3".
. Скопировать формулу на остальные элементы ряда, используя
прием протаскивания. В интервале С4: С25 получен ряд результатов вычисления
издержек Z
(Q).
. В ячейку D4 ввести формулу "=B4-C4".
. Скопировать формулу на остальные элементы ряда, используя
прием протаскивания. В интервале D4: D25 получен ряд результатов вычисления прибыли P (Q).
16. В ячейку Е4 ввести формулу "= ($G$10-$G$4) +2*
($G$11-$G$5) *A4-3*$G$6* A4^2".
17. Скопировать формулу на остальные элементы ряда, используя
прием протаскивания. В интервале Е4: Е25 получен ряд результатов вычисления dP/dQ для различных значений
Q.
. Построить на одной диаграмме графики зависимостей N (Q), Z (Q) и P (Q), используя
соответствующие ряды данных.
. Построить на отдельной диаграмме зависимость dP/dQ от Q. Точка пересечения
графика с осью абсцисс дает значение Q, соответствующее максимальной прибыли
(шаговый метод).
Вывод:
С помощью пакета Microsoft Excel можно решить задачу
максимизации прибыли. Прибыль максимальна, когда производная (dP/dQ) равна 0.
При этом точка пересечения графика с осью абсцисс (в данном случае это ось Q)
дает значение оптимального выпуска продукции, который соответствует
максимальной прибыли. В итоге я получила оптимальный выпуск продукции, равный
13 шт, при котором максимальная прибыль равна 4 ден. ед.
excel инженерная экономическая задача
Лабораторная работа № 2. "Модель Леонтьева"
Основой многих линейных моделей производства является схема
межотраслевого баланса. Идея метода впервые в явном виде была сформулирована в
работах советских экономистов в 20-х годах и получила затем развитие в трудах
В.В. Леонтьева по изучению структуры американской экономики. Предположим, что
производственный сектор народного хозяйства разбит на п отраслей. Причем
каждая отрасль выпускает продукт только одного типа, а разные отрасли выпускают
разные продукты. Кроме того, в процессе производства своего вида продукта
каждая отрасль нуждается в продукции других отраслей. В качестве примера
рассмотрим упрощенную модель межотраслевого баланса, предполагая, что экономика
страны состоит из 3-х отраслей (промышленности, сельского хозяйства и
транспорта).
Введем следующие обозначения уi - конечный
спрос на продукцию i-й отрасли, хi - выпуск продукции i-й
отрасли. cij - доля продукции отрасли i, потребленной в процессе
производства продукции отрасли j. В этом случае в соответствии с моделью
Леонтьева имеем следующую систему линейных уравнений:
Задача состоит в нахождении неизвестных x1, x2, x3. Остальные величины считаются заданными. Заметим, что все
коэффициенты cij изменяются
в пределах от 0 до 0,3. Это обеспечивает сходимость при использовании
итерационных методов.
Последовательность действий при реализации модели в пакете Excel с использованием метода простой итерации
(рис.8).
. Ввести в ячейку H1 текст
заголовка "Модель Леонтьева" (выравнивание по центру).
. Ввести в ячейку H2 текст "Данные" (выравнивание по
центру).
. В области F4: J7 ввести исходные данные как показано на рисунке.
. Обозначить в области А9: А12 номер итерации k и названия
переменных х1, х2, x3.
. В области В9: В12 задать начальные значения переменных (нули).
. В ячейку С9 ввести 1, выделить ячейки В9 и С9 и, используя прием
протаскивания, заполнить ряд до столбца О.
. Ввести в ячейку С10 формулу "= ($J$5+$H$5*B11+$I$5*B12) /
(1-$G$5) Получим значение переменной х1 на первой итерации.
. Ввести в ячейку С11 формулу "= ($J$6+$G$6*B10+$I$6*B12) /
(1-$H$6)". Получим значение переменной х2 на первой итерации.
. Ввести в ячейку С12 формулу "= ($J$7+$G$7*B10+$H$7*B11) /
(1-$I$7) Получим значение переменной х3 на первой итерации.
. Выделить диапазон С10: С12 и скопировать его до столбца О,
используя прием протаскивания
. В области A14: O33 построить диаграмму, показывающую процесс приближения
значений переменных х1, х2, х3 к решению системы.
Диаграмма строится в режиме "Точечная", где по оси абсцисс
откладывается номер итерации.
Вывод:
Задачу межотраслевого баланса можно решить с помощью пакета Excel. Решив данную задачу при
помощи Модели Леонтьева, были найдены значения х1. х2, х3 (x1≈616; x2≈934; x3≈746) - выпуска
продукции 3-х отраслей (промышленности, сельского хозяйства и транспорта). По
графику, можно определить какая из отраслей обладает наибольшим выпуском
продукции.
Лабораторная
работа № 3. "Предельный анализ и оптимизация прибыли, издержек и объема
производства"
Вернемся к задаче максимизации прибыли предприятия.
Математическое решение данной задачи сводится к максимизации функции прибыли:
P = kQ - Z
Функция имеет экстремум, когда ее производная равна нулю:
Аналогично проводится анализ зависимости между издержками и
количеством выпускаемой продукции, который позволяет определить для функции
издержек линейную форму связи вида Z = b0 + b1Q.
Неизвестные b0 и b1 также находятся на
основе решения системы нормальных уравнений вида:
Оптимальные параметры определяются из соотношений:
Qopt = (b1 - a0)
/ (2a1); Zopt = b0 + b1Qopt;
kopt = a0 + a1Qopt;opt = koptQopt.; Popt = Nopt. - Zopt = (a0+a1Qopt) Qopt - (b0+b1Qopt)
Обычно предельный анализ проводится с использованием метода
наименьших квадратов путем решения систем линейных уравнений для нахождения
функций спроса и издержек. Табличный процессор Excel позволяет существенно уменьшить объем вычислений путем
использования встроенных функций линейной регрессии.
Найденные функции спроса k (Q) и издержек Z (Q) позволяют
определить функцию прибыли P (Q). Максимальное значение этой функции
может быть найдено средствами пакета анализа "что-если" Excel.
Команда Он позволяет находить значение параметра-переменной, при
котором зависящее от него значение функции в целевой ячейке достигает максимума
или любого другого заданного значения (рис.13).
Последовательность действий:
. Введем исходные данные (табл.1).
2. Применим функцию ЛИНЕЙН для вычисления коэффициентов a1,
a0 функции спроса k (Q):
· выделить интервал A17: B17;
· напечатать формулу =ЛИНЕЙН (B9: G9; B8: G8);
· нажать <Ctrl+Shift+Enter>.
Результат в ячейке A17 - значение коэффициента a1,
в ячейке B17 - значение коэффициента a0.
. Аналогично находим коэффициенты b1,
b0 функции издержек Z (Q):
· выделить интервал D17: E17;
· напечатать формулу =ЛИНЕЙН (B10: G10; B8: G8);
· нажать <Ctrl+Shift+Enter>.
Результат в ячейке D17 - значение коэффициента b1,
в ячейке E17 - значение коэффициента b0.
. Найденные функции спроса k (Q) и издержек Z
(Q) позволяют определить функцию прибыли P (Q). Максимальное
значение этой функции (оптимальная прибыль Popt при некотором
значении Q (Qopt) может быть найдено средствами оптимального
решения анализа "что-если" пакета Excel.
Вывод:
Исходя из данных таблицы 2 и графика зависимости прибыли от
объема производства, видно, что максимальная прибыль (P=631,80) достигается при
объеме производимой продукции равной 59.
Заключение
Основной целью данной работы является использование численных
методов. Данные методы предполагают, использование пакета Microsoft Excel и
сводятся к некоторым действиям над числами, при этом множество решений являются
приближенными.
В процессе создания данной работы я приобрела новые знания в
сфере решения экономических задач при помощи пакета Microsoft Excel численных
методов. С первым, с чем я столкнулась в курсовой работе это задача
максимизации прибыли предприятия. Прибыль максимальна, когда производная
(dP/dQ) равна 0. При этом точка пересечения графика с осью абсцисс (в данном
случае это ось Q) дает значение оптимального выпуска продукции, который
соответствует максимальной прибыли. В процессе работы я использовала модель
Леонтьева. Задача заключалась в рассмотрении упрощенной модели межотраслевого
баланса, предполагая, что в экономике только 3 отрасли. Для этого нужно было
найти выпуск продукции в каждой отрасли. Так же в течение работы я находила
предельный анализ оптимизацию прибыли, издержек и объема производства. В данном
примере я анализировала зависимости между ценой продукции и его количеством в
первом случае, а во втором зависимость между издержками и количеством
выпускаемой продукции.
Используемая
литература
1. Методическая
разработка по курсу "Информатика" для студентов всех форм обучения
"Использование табличного процессора Excel для реализации численных
методов в инженерных и экономических расчетах". Составители: В.Ф. Билюба,
В.Н. Ершов, С.Н. Митяков, О.И. Митякова, С.П. Никитенкова, Н.Я. Николаев.
2000год
2. "Основные
технологии работы с табличным процессом Excel". Составители: Н.В. Зубов, И.В. Лапшин, С.Н.
Митяков, С.П. Никитенкова, А.Н. Демин.