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

Автор работы: Пользователь скрыл имя, 16 Декабря 2013 в 18:03, курсовая работа

Описание работы

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

Содержание работы

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

Файлы: 1 файл

Моя курсовая работа МН.docx

— 334.56 Кб (Скачать файл)

Министерство  образования Российской Федерации

Нижегородский государственный технический  университет им. Р.Е Алексеева

                        Факультет экономики менеджмента и инноваций

 

Кафедра «Менеджмент»

 

 

 

 

 

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

 

Использование табличного процессора Excel

для реализации численных методов

в инженерных и экономических расчетах

 

Вариант 95

 

 

 

 

 

 

Выполнил  студент группы 12-УП-1

Маслова Н.В

 

 

Проверил  профессор Мисевич П.В

 

 

 

 

 

 

 

 

 

                                            Нижний Новгород  2013

 

Содержание

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

 

 

Введение

 

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

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

Одной из распространенных экономических  задач является задача максимизации прибыли предприятия. Известно, что балансовая прибыль есть разница между выручкой и затратами на производство продукции P=N-Z. В общем случае выручка от реализации продукции может быть представлена полиномом 2-й степени от количества продукции N=b0Q+b1Q2. Нелинейность может быть связана с тем, что в условиях монополии цена единицы продукции k может уменьшаться с ростом количества выпущенной продукции Q:

k=b0+b1Q (b0>0, b1<0). В свою очередь, функция затрат может быть представлена полиномом 3-й степени Z=a0+a1Q+a2Q2+a3Q3. Кубическая нелинейность может объясняться тем, что при производстве малой партии товаров издержки быстро растут, затем с ростом Q темп роста издержек уменьшается, но по достижении некоторого критического значения Q начинает работать «закон убывающей отдачи», в соответствии с которым издержки вновь начинают расти ускоренными темпами. Прибыль максимальна, когда dP/dQ = 0. С помощью пакета Excel решим данную задачу, полагая заданными коэффициенты: a0 = 10, a1=2, a2= -0.1, a3 = 0.01,  b0= 0, b1= 7.

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

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

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

3. В ячейки F3, F4, F5, F6, F9, F10 записать названия коэффициентов - соответственно a0, a1, a2, a3, b0, b1.

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

5. В ячейку Н5 ввести текст  «Издержки Z=a0+al*Q+ba2*Q^2+a3*Q^

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

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

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

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

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

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

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

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^2».

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

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

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

 

 

 

 

 

 

 


 

      Вывод:

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

 

 

 

 

 

 

 

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

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

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

  

Задача  состоит в нахождении неизвестных x1, x2, x3. Остальные величины считаются заданными. Заметим, что все коэффициенты cij изменяются в пределах от 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 (x1≈845; x2≈931; x3≈916) - выпуска продукции 3-х отраслей (промышленности, сельского хозяйства и транспорта). По графику, можно определить какая из отраслей обладает наибольшим выпуском продукции.

 

 

 

 

 

 

 

 

 

 

 

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

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

P = kQ - Z



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

Анализ  зависимости между ценой продукта и его количеством в динамике позволяет выбрать для функции  спроса линейную форму вида k = b0 + b1Q. Анализируется n периодов, в каждом из которых считаются заданными параметры ki и Qi.  По методу наименьших квадратов определяются неизвестные параметры b0 и b1 на основе составления и решения системы нормальных уравнений вида

 

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

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

Qopt = (b1 - a0)/(2a1);  Zopt = b0 + b1Qopt;  kopt = a0 + a1Qopt;

Nopt = koptQopt.;   Popt = Nopt.-Zopt =(a0+a1Qopt)Qopt - (b0+b1Qopt)

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

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


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


  1. Введем исходные данные (табл. 1).
  2. Применим функцию ЛИНЕЙН для вычисления коэффициентов a1, a0 функции спроса k(Q):
  • выделить интервал A17:B17;
  • напечатать формулу =ЛИНЕЙН(B8:G8;B7:G7);
  • нажать  <Ctrl+Shift+Enter>.

Результат в ячейке A17 - значение коэффициента a1, в ячейке B17 - значение коэффициента a0.


  1. Аналогично находим коэффициенты b1, b0 функции издержек Z(Q):
  • выделить интервал D17:E17;
  • напечатать формулу   =ЛИНЕЙН(B9:G9;B7:G7);
  • нажать  <Ctrl+Shift+Enter>.

Результат в ячейке D17 - значение коэффициента b1, в ячейке E17 - значение коэффициента b0.


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

 

 

 

 

 

 

 

 

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