Использование табличного процессора MS Excel для реализации численных методов в инженерных и экономических расчетах

Министерство образования РФ Нижегородский государственный технический университет им. Р. Е. Алексеева Факультет Экономики, Менеджмента и Инноваций Кафедра: «Менеджмент»

Курсовая работа на тему:

Использование табличного процессора MS Excel для реализации численных методов в инженерных и экономических расчетах

Выполнил: студент группы 10-МЕНк Селина Анастасия Сергеевна Проверил: Зубов Николай Викторович Нижний Новгород 2011 год

• Введение
• Лабораторная работа № 1. «Задача максимизации прибыли предприятия»
• Лабораторная работа № 2. «Модель Леонтьева»
• Лабораторная работа № 3. «Предельный анализ и оптимизация прибыли, издержек и объема производства»
• Заключение
• Используемая литература

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

Существуют различные подходы к реализации численных методов. Традиционный подход предполагает построение алгоритма метода с последующим программированием на языке высокого уровня. В последнее время широко используются специализированные программные продукты — математические пакеты типа MathCad, которые существенно упрощают процесс составления алгоритма и обладают встроенными библиотеками и графическими возможностями. В данной работе описан еще один подход, позволяющий в ряде случаев существенно ускорить процесс решения задачи. Он основан на использовании табличного процессора Excel, широко распространенного среди пользователей. Вместе с тем, применение данного программного продукта для реализации численных методов до сих пор не нашло соответствующего отражения в литературе.

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

Лабораторная работа № 1. «Задача максимизации прибыли предприятия»

Одной из распространенных экономических задач является задача максимизации прибыли предприятия. Известно, что балансовая прибыль есть разница между выручкой и затратами на производство продукции P=N-Z. В общем случае выручка от реализации продукции может быть представлена полиномом 2-й степени от количества продукции N=a₀Q+a₁Q². Нелинейность может быть связана с тем, что в условиях монополии цена единицы продукции k может уменьшаться с ростом количества выпущенной продукции Q: k=a₀+a₁Q (a₀>0, a₁<0). В свою очередь, функция затрат может быть представлена полиномом 3-й степени Z=b₀+b₁Q+b₂Q² +b₃Q³. Кубическая нелинейность может объясняться тем, что при производстве малой партии товаров издержки быстро растут, затем с ростом Q темп роста издержек уменьшается, но по достижении некоторого критического значения Q начинает работать «закон убывающей отдачи», в соответствии с которым издержки вновь начинают расти ускоренными темпами. Прибыль максимальна, когда dP/dQ = 0. С помощью пакета Excel решим данную задачу, полагая заданными коэффициенты: b₀ = 0, b₁=1, b₂= 7, b₃ = - 0,15, a₀= 15, a₁= 3, a₂=-0,1, a₃=0,01.

Последовательность действий при реализации в пакете Excel

1. Оформить заголовок в строке 1 «Максимизация прибыли» .

2. В ячейки A3, ВЗ, СЗ, D3 и ЕЗ записать заголовки рядов — соответственно Q, N, Z, P, и dP/dQ.

3. В ячейки F3, F4, F5, F6, F9, F10 записать названия коэффициентов - соответственно b₀, b₁, b₂, b₃, a₀, a₁.

4. В ячейки G3, G4, G5, G6, G9, G10, G11 записать значения коэффициентов — соответственно 15; 3; - 0,1; 0,01; 0; 7; - 0,15.

5. В ячейку Н9 ввести текст «Издержки Z= a0+a1*Q+a2*Q²+a3*Q³»

6. В ячейку Н10 ввести текст «Выручка N=b0+b1*Q+b2*Q²»

7. В ячейку Н11 ввести текст «Прибыль P=N-Z»

8. В ячейки А4 и А5 ввести первые два значения аргумента — 0 и 1.

9. Выделить ячейки А4-А5 и протащить ряд данных до конечного значения

(21), убедившись в правильном выстраивании арифметической прогрессии.10. В ячейку В4 ввести формулу «=$G$ 9+$G$ 10*A4+$G$ 11*A4²» .

11. Скопировать формулу на остальные элементы ряда, используя прием протаскивания. В интервале В4: В25 получен ряд результатов вычисления выручки N (Q).

12. В ячейку С4 ввести формулу «=$G$ 3+$G$ 4*A4+$G$ 5*A4²+$G$ 6*A4³» .

13. Скопировать формулу на остальные элементы ряда, используя прием протаскивания. В интервале С4: С25 получен ряд результатов вычисления издержек Z (Q).

14. В ячейку D4 ввести формулу «=B4-C4» .

15. Скопировать формулу на остальные элементы ряда, используя прием протаскивания. В интервале D4: D25 получен ряд результатов вычисления прибыли P (Q).

16. В ячейку Е4 ввести формулу «= ($G$ 10-$G$ 4) +2* ($G$ 11-$G$ 5) *A4−3*$G$ 6* A4²» .

17. Скопировать формулу на остальные элементы ряда, используя прием протаскивания. В интервале Е4: Е25 получен ряд результатов вычисления dP/dQ для различных значений Q.

18. Построить на одной диаграмме графики зависимостей N (Q), Z (Q) и P (Q), используя соответствующие ряды данных.

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

Вывод:

С помощью пакета Microsoft Excel можно решить задачу максимизации прибыли. Прибыль максимальна, когда производная (dP/dQ) равна 0. При этом точка пересечения графика с осью абсцисс (в данном случае это ось Q) дает значение оптимального выпуска продукции, который соответствует максимальной прибыли. В итоге я получила оптимальный выпуск продукции, равный 13 шт, при котором максимальная прибыль равна 4 ден. ед.

excel инженерная экономическая задача

Лабораторная работа № 2. «Модель Леонтьева»

Основой многих линейных моделей производства является схема межотраслевого баланса. Идея метода впервые в явном виде была сформулирована в работах советских экономистов в 20-х годах и получила затем развитие в трудах В. В. Леонтьева по изучению структуры американской экономики. Предположим, что производственный сектор народного хозяйства разбит на п отраслей. Причем каждая отрасль выпускает продукт только одного типа, а разные отрасли выпускают разные продукты. Кроме того, в процессе производства своего вида продукта каждая отрасль нуждается в продукции других отраслей. В качестве примера рассмотрим упрощенную модель межотраслевого баланса, предполагая, что экономика страны состоит из 3-х отраслей (промышленности, сельского хозяйства и транспорта).

Введем следующие обозначения у_i — конечный спрос на продукцию i-й отрасли, х_i — выпуск продукции i-й отрасли. c_ij — доля продукции отрасли i, потребленной в процессе производства продукции отрасли j. В этом случае в соответствии с моделью Леонтьева имеем следующую систему линейных уравнений:

Задача состоит в нахождении неизвестных x₁, x₂, x₃. Остальные величины считаются заданными. Заметим, что все коэффициенты c_ij изменяются в пределах от 0 до 0,3. Это обеспечивает сходимость при использовании итерационных методов.

Последовательность действий при реализации модели в пакете Excel с использованием метода простой итерации (рис.8).

1. Ввести в ячейку H1 текст заголовка «Модель Леонтьева» (выравнивание по центру).

2. Ввести в ячейку H2 текст «Данные» (выравнивание по центру).

3. В области F4: J7 ввести исходные данные как показано на рисунке.

4. Обозначить в области А9: А12 номер итерации k и названия переменных х1, х2, x3.

5. В области В9: В12 задать начальные значения переменных (нули).

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

7. Ввести в ячейку С10 формулу «= ($J$ 5+$H$ 5*B11+$I$ 5*B12) / (1-$G$ 5) Получим значение переменной х1 на первой итерации.

8. Ввести в ячейку С11 формулу «= ($J$ 6+$G$ 6*B10+$I$ 6*B12) / (1-$H$ 6)». Получим значение переменной х2 на первой итерации.

9. Ввести в ячейку С12 формулу «= ($J$ 7+$G$ 7*B10+$H$ 7*B11) / (1-$I$ 7) Получим значение переменной х3 на первой итерации.

10. Выделить диапазон С10: С12 и скопировать его до столбца О, используя прием протаскивания

11. В области A14: O33 построить диаграмму, показывающую процесс приближения значений переменных х1, х2, х3 к решению системы. Диаграмма строится в режиме «Точечная», где по оси абсцисс откладывается номер итерации.

Вывод:

Задачу межотраслевого баланса можно решить с помощью пакета Excel. Решив данную задачу при помощи Модели Леонтьева, были найдены значения х1. х2, х3 (x₁?616; x₂?934; x₃?746) — выпуска продукции 3-х отраслей (промышленности, сельского хозяйства и транспорта). По графику, можно определить какая из отраслей обладает наибольшим выпуском продукции.

Лабораторная работа № 3. «Предельный анализ и оптимизация прибыли, издержек и объема производства»

Вернемся к задаче максимизации прибыли предприятия. Математическое решение данной задачи сводится к максимизации функции прибыли:

P = kQ — Z

Функция имеет экстремум, когда ее производная равна нулю:

Анализ зависимости между ценой продукта и его количеством в динамике позволяет выбрать для функции спроса линейную форму вида k = a₀ + a₁Q. Анализируется n периодов, в каждом из которых считаются заданными параметры k_i и Q_i. По методу наименьших квадратов определяются неизвестные параметры a₀ и a₁ на основе составления и решения системы нормальных уравнений вида

Аналогично проводится анализ зависимости между издержками и количеством выпускаемой продукции, который позволяет определить для функции издержек линейную форму связи вида Z = b₀ + b₁Q. Неизвестные b₀ и b₁ также находятся на основе решения системы нормальных уравнений вида:

Оптимальные параметры определяются из соотношений:

Q_opt = (b₁ - a₀) / (2a₁); Z_opt = b₀ + b₁Q_opt; k_opt = a₀ + a₁Q_opt;

N_opt = k_optQ_opt.; P_opt = N_opt. - Z_opt = (a₀+a₁Q_opt) Q_{opt — (}b₀+b₁Q_opt)

Обычно предельный анализ проводится с использованием метода наименьших квадратов путем решения систем линейных уравнений для нахождения функций спроса и издержек. Табличный процессор Excel позволяет существенно уменьшить объем вычислений путем использования встроенных функций линейной регрессии.

Найденные функции спроса k (Q) и издержек Z (Q) позволяют определить функцию прибыли P (Q). Максимальное значение этой функции может быть найдено средствами пакета анализа «что-если» Excel. Команда Он позволяет находить значение параметра-переменной, при котором зависящее от него значение функции в целевой ячейке достигает максимума или любого другого заданного значения (рис.13).

Последовательность действий:

1. Введем исходные данные (табл.1).

2. Применим функцию ЛИНЕЙН для вычисления коэффициентов a₁, a₀ функции спроса k (Q):

· выделить интервал A17: B17;

· напечатать формулу =ЛИНЕЙН (B9: G9; B8: G8);

· нажать .

Результат в ячейке A17 — значение коэффициента a₁, в ячейке B17 — значение коэффициента a₀.

3. Аналогично находим коэффициенты b₁, b₀ функции издержек Z (Q):

· выделить интервал D17: E17;

· напечатать формулу =ЛИНЕЙН (B10: G10; B8: G8);

· нажать .

Результат в ячейке D17 — значение коэффициента b₁, в ячейке E17 — значение коэффициента b₀.

4. Найденные функции спроса k (Q) и издержек Z (Q) позволяют определить функцию прибыли P (Q). Максимальное значение этой функции (оптимальная прибыль P_opt при некотором значении Q (Q_opt) может быть найдено средствами оптимального решения анализа «что-если» пакета Excel.

Вывод:

Исходя из данных таблицы 2 и графика зависимости прибыли от объема производства, видно, что максимальная прибыль (P=631,80) достигается при объеме производимой продукции равной 59.

Заключение

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

В процессе создания данной работы я приобрела новые знания в сфере решения экономических задач при помощи пакета Microsoft Excel численных методов. С первым, с чем я столкнулась в курсовой работе это задача максимизации прибыли предприятия. Прибыль максимальна, когда производная (dP/dQ) равна 0. При этом точка пересечения графика с осью абсцисс (в данном случае это ось Q) дает значение оптимального выпуска продукции, который соответствует максимальной прибыли. В процессе работы я использовала модель Леонтьева. Задача заключалась в рассмотрении упрощенной модели межотраслевого баланса, предполагая, что в экономике только 3 отрасли. Для этого нужно было найти выпуск продукции в каждой отрасли. Так же в течение работы я находила предельный анализ оптимизацию прибыли, издержек и объема производства. В данном примере я анализировала зависимости между ценой продукции и его количеством в первом случае, а во втором зависимость между издержками и количеством выпускаемой продукции.

Используемая литература

1. Методическая разработка по курсу «Информатика» для студентов всех форм обучения «Использование табличного процессора Excel для реализации численных методов в инженерных и экономических расчетах». Составители: В. Ф. Билюба, В. Н. Ершов, С. Н. Митяков, О. И. Митякова, С. П. Никитенкова, Н. Я. Николаев. 2000 год

2. «Основные технологии работы с табличным процессом Excel». Составители: Н. В. Зубов, И. В. Лапшин, С. Н. Митяков, С. П. Никитенкова, А. Н. Демин.