Реферат: Методические указания по лабораторным занятиям По дисциплине

ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ

Государственное образовательное учреждение

высшего профессионального образования

Тихоокеанский государственный университет


Институт экономики и управления

Кафедра Экономическая кибернетика


Методические указания по лабораторным занятиям


По дисциплине Математическое моделирование в экономике


Для специальности 080301.65 «Коммерция (торговое дело)»

(ускоренное)


Методические указания разработаны в соответствии с составом УМКД


Методические указания разработала Макарова О.А. _____________


Методические указания утверждены на заседании кафедры,

протокол № ______ от «___» _______________ 200__ г.

Зав. кафедрой _________ «___» ______________ 200__ г. Пазюк К.Т.


Методические указания по лабораторным занятиям по дисциплине «Математическое моделирование в экономике» включают тематику лабораторных заданий, выполняемых студентами под контролем преподавателя или самостоятельно во время аудиторных занятий.


Методические указания рассмотрены и утверждены на заседании УМКС и рекомендованы к изданию

протокол № ______ от «___» _______________ 200__ г.

Председатель УМКС _______ «___» ______________ 200__ г.

Директор института _________ «___» ____________ 200__ г. Зубарев А.Е.


Введение


Изучение дисциплины «Математическое моделирование в экономике» имеет цель формирования у студентов специальности 080301.65 «Коммерция" (Торговое дело)» теоретических знаний и умений в области практического использования экономико-математических методов, развитие способности к логическому и алгоритмическому мышлению.

Основные задачи курса:

- на примерах математических моделей в экономике продемонстрировать студентам действие математических законов, специфику моделирования;

- научить студентов приемам исследования и решения математически сформулированных задач;

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

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

- привить студентам навыки самостоятельного изучения литературы по практическому применению математических методов.

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

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

Изучение курса «Математическое моделирование в экономике» проводится в форме лекции, практических и лабораторных занятий.

Практические занятия по дисциплине проводятся в форме решения задач.

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

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

Завершается изучение дисциплины «Математическое моделирование в экономике» сдачей зачета.

^ 1. Краткие характеристики лабораторных занятий

Тема 1. Решение задач линейного программирования в Excel

Задание. Методы решения задач линейного программирования.

Исполнение. Решение задач линейного программирования с помощью инструмента «Поиск решения».

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

Время выполнения заданий: 4 часа.


Методические указания.


Задание: студентам необходимо решить задачу 1, используя данные методические указания, задачу 2 – решить самостоятельно по аналогии с задачей 1. Условие задачи 2 одинаково для всех студентов, независимо от варианта.

Условие задачи 1: Фирма выпускает 2 вида мороженного: сливочное и шоколадное. Для изготовления используются 2 исходных продукта: молоко и наполнители, расходы которых на 1 кг мороженного и суточные запасы исходных продуктов даны в таблице.

Исходный продукт

Расход исходных продуктов на 1 кг мороженного


Запас, кг

Сливочное

Шоколадное

Молоко

0.8

0.5

400

Наполнители

0.4

0.8

365

Изучение рынка сбыта показало, что суточный спрос на сливочное мороженное превышает спрос на шоколадное мороженное не более чем на 100 кг. Кроме того, установлено, что спрос на шоколадное мороженное не превышает 350 кг в сутки. Отпускная цена 1 кг сливочного мороженного 16 ден.ед., шоколадного - 14 ден.ед. Определить количество мороженого каждого вида, которое должна производить фирма, чтобы доход от реализации продукции был максимальным.


^ Решение задачи:

Составляем математическую модель задачи.

Вводим обозначения (переменные величины):

х 1 – суточный объем выпуска сливочного мороженного, кг;

х 2 - суточный объем выпуска шоколадного мороженного, кг

Целевая функция:

f = 16 х 1 + 14 х 2→max

при ограничениях:

0.8 х 1 + 0.5 х 2 ≤ 400 (ограничение по молоку);

0.4 х 1 + 0.8 х 2 ≤ 365 (ограничение по наполнителям);

х 1 + х 2 ≤ 100 (рыночное ограничение по спросу);

х 2 ≤ 350 (рыночное ограничение по спросу);

х 1 ≥ 0, х 2 ≥ 0

Данная задача является задачей линейного программирования (ЗЛП), так как целевая функция и ограничения линейны. ЗЛП можно решить: графическим методом, симплекс-методом, с помощью EXCEL.

Для решения ЗЛП с помощью EXCEL строим три таблицы с исходными данными на основании математической модели задачи (рисунок 1)




Рисунок 1 – Таблицы с исходными данными

В ячейки С9, D14-D17 вставляем функцию СУММПРОИЗВ (Вставка – Функция – Математические – СУММПРОИЗВ). Массив1 для каждой ячейки это коэффициенты при переменных из таблицы 2 таблицы 3 соответственно. Массив2 – это ячейки значения переменных величин таблицы 1 (рисунок 2, 3, 4, 5).



Рисунок 2 – Вставка формулы СУММПРОИЗВ в ячейку таблицы 2




Рисунок 3 – Вставка формулы СУММПРОИЗВ в ячейку таблицы




Рисунок 4 - Вставка формулы СУММПРОИЗВ в ячейки таблицы 2, 3




^ Рисунок 5 - Вставка формулы СУММПРОИЗВ в ячейки таблицы 2, 3


Далее выделяем ячейку С9 и находим значения переменных х1, х2 и максимальное значение целевой функции с помощью поиска решений (Сервис – Поиск решения) – рисунок 6.



^ Рисунок 6 – Поиск решения


После того, как окно поиска решение заполнено необходимыми данными, нужно нажать Выполнить и решение будет найдено, его необходимо сохранить (рисунок 7).



Рисунок 7 – Результаты поиска решений

В результате решения получили х1* = 312.5кг, х2* = 300кг, max а = 9200 ден.ед. Все ограничения выполнены.

Ответ: максимальный доход фирмы от реализации продукции составит 9200 ден.ед. при выпуске 312,5кг сливочного и 300кг шоколадного мороженного.

Условие задачи 2: Предприятие рекламирует свою продукцию с использованием четырех источников массовой информации: телевидения, радио, газет и расклейки объявлений. Анализ рекламной деятельности в прошлом показал, что эти средства приводят к увеличению прибыли соответственно на 10, 5, 7 и 4 усл.ед., в расчете на 1 усл.ед., затраченную на рекламу. На рекламу выделено 50000 усл.ед. Администрация предприятия не намерена тратить на телевидение более 40% (20000 усл.ед), а на радио и газеты – более 50% (25000 усл.ед) от общей суммы выделенных средств. Как следует предприятию организовать рекламу, чтобы получить максимальную прибыль?


^ Тема 2. Решение транспортных задач в Excel

Задание. Методы решения специальных задач линейного программирования.

Исполнение. Решение транспортных задач с помощью инструмента «Поиск решения».

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

Время выполнения заданий: 3 часа.


Пример. На складах А1, А2, АЗ имеются запасы товаров в ко­личествах 90. 400 и 110 т соответственно. Грузополучатели В1, В2, ВЗ должны получить эти товары в количествах 130, 300, 1(50 т соответ­ственно. Требуется найти такой вариант перевозки грузов, при кото­ром сумма затрат на перевозки будет минимальной. Расходы по пере­возке 1 г грузов в условных единицах приведены в таблице.

Грузополучатели

Склад А1

Склад А2

Склад АЗ

В1

2

5

2

В2

4

1

5

ВЗ

3

6

8

Решение:

Математическая модель

Введем следующие обозначения:

X11— количество товара, перевозимое грузополучателю B1 со склада А1;

X12 — количество товара, перевозимое грузополучателю B1 со склада А2;

X13— количество товара, перевозимое грузополучателю В1 со склада АЗ;

Х21— количество товара, перевозимое грузополучателю В2 со склада А1;

X22 - количество товара, перевозимое грузополучателю В2 со склада А2;

X23— количество товара, перевозимое грузополучателю В2 со склада АЗ;

X31— количество товара, перевозимое грузополучателю ВЗ со склада А1;

Х32 - количество товара, перевозимое грузополучателю ВЗ со склада А2;

Х33— количество товара, перевозимое грузополучателю ВЗ со склада АЗ.


Тогда целевая функция будет иметь вид

L = 2х11+ 5х12 + 2 х13 + 4 х21 + х22+ 5 х23 +3 х31 + 6 х32 + 8 х32 → min

При ограничениях:

x11+ х12 + x13 = 140 - груз, привезенный грузополучателю В1со всех складов

x21+ х22 + x23 = 300 - груз привезенный, грузополучателю В2 со всех складов

x31+ х32 + x33 = 160 - груз, привезенный грузополучателю ВЗ со всех складов

x11+ х21 + x31= 90 - груз, отгруженный со склада А1

х12 + х22 + х32 = 400 - груз, отгруженный со склада А2

х13 + х23 + х33 = 90 - груз, отгруженный со склада АЗ

х11≥0; х12≥0; х13≥0;

х21≥0; х22≥0; х23≥0;

х31≥0; х32≥0; х33≥0.

Представим математическую модель на рабочем-листе в виде, изображенном на рисунок 1, записывая в ячейку В9 формулу функ­ции цели, а в ячейки В11 - В16 формулы ограничений.

В диалоговом окне ^ Поиск решения в соответствующих полях ус­тановим адрес целевой ячейки, адрес диапазона, содержащего подби­раемые параметры, введем все ограничения, установим переключатель Минимальному значению (рисунок 2, 3, 4).

После выполнения вычислений будет получен результат, представленный на рисунке 5. Минимальные затраты на перевозку грузов со складов потребителям при полученном плане перевозок составят 1360 у.е.



Рисунок 1




Рисунок 2




Рисунок 3




Рисунок 4



Рисунок 5


Тема 3. Технология решения задач регрессионного анализа в Excel

Задание. Уравнение линейной регрессии.

Исполнение. Построить уравнение линейной регрессии с помощью инструмента Пакета анализа «Регрессия».

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

Время выполнения заданий: 3 часа.

Регрессия позволяет проанализировать воздействие на какую-либо зависимую переменную одной или более независимых переменных и позволяет установить аналитическую фор­му (модель) этой зависимости в виде аппроксимирующего полинома.

Если рассматривается зависимость между одной зависимой пе­ременной у и несколькими независимыми х1, х2, ..., хп, то речь идет о множественной линейной регрессии. В этом случае уравнение рег­рессии имеет вид

у = а0 + а1х1 + а2х2+...+ апхп,

где а1, а2 ... ап - коэффициенты при независимых переменных, которые нужно

вычислить (коэффициенты регрессии);

ао — константа.

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

Мерой оценки адекватности регрессионной модели является коэффициент детерминации R2 (R-квадрат), который определяет, с ка­кой степенью точности полученное уравнение регрессии аппроксими­рует исходные данные.

Значимость регрессионной модели оценивается с помощью кри­терия Фишера (F-критерия). Если величина F-критерия значима (р < 0,05), то регрессионная модель является значимой.

В табличном процессоре можно аппроксимировать эксперимен­тальные данные линейным уравнением до 16-го порядка у = а0 + а1х1 + а2х2+...+ а16х16.

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

Выполнить команду Сервис/Анализ данных.

В раскрывшемся окне диалога Анализ данных выбрать из спис­ка строку Регрессия — раскроется окно диалога Регрессия.

В группе Входные данные в поле Входной интервал у указать адресную ссылку на диапазон, содержащий значения зависимой пере­менной, а в поле Входной интервал X — ссылку на диапазон, содержа­щий значения независимых переменных, т.е. переменных, влияние которых на зависимую переменную у оценивается. Установить фла­жок Метки, если исходная таблица имеет названия столбцов и фла­жок Константа-ноль, если а0 = 0.

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

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

Если нужно получить график нормальной вероятности, то уста­новить флажок ^ График нормальной вероятности.

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

Значения коэффициентов регрессии размещаются в столбце Коэффициенты:

у — пересечение а0;

x1 — коэффициент а1;

х2 — коэффициент а2 и т.д.

В столбце Р-Значение содержится оценка достоверности отличия соответствующих коэффициентов от нуля. Если Р > 0,05, то коэффи­циент можно считать нулевым. Это означает, что соответствующая независимая переменная практически не влияет на зависимую пере­менную.

Значение R-квадрат определяет, с какой степенью точности рег­рессионное уравнение будет аппроксимировать экспериментальные данные. Если R-квадрат > 0,95, то точность аппроксимации высокая. При 0,8
Кроме инструмента Регрессия в табличном процессоре для полу­чения параметров уравнения регрессии есть функция ЛИНЕЙН и функция ТЕНДЕНЦИЯ для получения значения у в требуемых точках.

Пример. Имеются статистические данные о затратах, свя­занных с рекламой по телевидению, с рекламой в метро и объеме реали­зации продукции в рублях, приведенные в таблице.


Затраты на рекламу но телевидению (руб.)

Затраты па рекламу в метро (руб.)

Объем реализации (руб.)

125850

20000

1850000

260500

21000

2500000

150000

22000

1900000

300000

21000

3000000

280000

24000

2600000

290000

23000

2700000

140000

20000

1900000
Требуется найти регрессионные коэффициенты для независимых переменных ^ Расходы на рекламу по телевидению и Расходы на рекламу в метро на объем реализации продукции и построить уравнение регрессии.

Решение:

На рабочем листе в диапазон А1:С8 введем данные приведен­ной таблицы.

Включим инструмент Регрессия.

3.В открывшемся диалоговом окне Регрессия установим пара­метры как показано на рисунке 1.

- Входной интервал у — диапазон С1:С8;

- Входной интервал х — диапазон А1:В8;

- Флажок ^ Метки;

- Выходной интервал — адрес D1;

- Флажок График нормальной вероятности;

- Флажок График остатков.

После щелчка на кнопке ОК в диапазон D1:L21 будет выведен результат регрессионного анализа (рисунок 2).




Рисунок 1

Полученные результаты и их интерпретация

Коэффициент детерминации R-квадрат - 0,974641 (аппрокси­мация высокая).

Значимость F = 0,000643 (р < 0,05 — регрессионная модель зна­чима).

У-пересечение a0= 2102438,6

a1 = 6,4004 — коэффициент при независимой переменной За­траты на рекламу по телевидению.

а2 = -54,068 — коэффициент при независимой переменной За­траты на рекламу в метро.

С учетом полученных данных функциональная зависимость ве­личины прибыли от затрат на рекламу запишется в виде полинома у = 2102438,6 + 6,4004х1 - 54,068х2,

где х1 — величина затрат на рекламу по телевидению;

x2 — величина затрат на рекламу в метро.





Рисунок 2

Тема 4. Построение производственной функции в Excel

Задание. Производственная функция Кобба-Дугласа.

Исполнение. Построить уравнение линейной регрессии с помощью инструмента Анализ данных «Регрессия» и получить из него степенную производственную функцию.

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

Время выполнения заданий: 3 часа.

Производственной функцией называется зависимость объема производства y от затрат производственных ресурсов x1 , x2, … ,xn

y = f(x1 , x2, … ,xn) (1)

Степенная производственная функция (функция Кобба-Дугласа) имеет вид:

y = α0 x1 α1 x2α2, (2)

где 0<α1, α2<1;

x1 – труд (рабочая сила);

x2 – основные производственные фонды (капитал).

Для построения степенной регрессионной зависимости необходимо исходную формулу (2) прологарифмировать, что приведет к представлению новой зависимости в линейной форме:

lny = ln α0 + α1 ln x1 + α2 ln x2 , (3)

для которой следует использовать функцию ЛИНЕЙН или инструмент Регрессия (Сервис/ Анализ данных /Регрессия). (см. Лабораторное занятие по теме 3)

При возврате к степенной зависимости не забыть, что α0 = elnα0.

^ Варианты индивидуальных заданий



Номер варианта

у

Х1

Х2

Номер варианта

у

Х1

Х2

№1

1

2

3

№2

1

2

3

1

2,27

112,5

48

1

2,27

48

2,12

2

1,94

116,4

42,1

2

1,94

42,1

2,2

3

2,32

111,6

42,3

3

2,32

42,3

2,11

4

2,49

108,9

43,7

4

2,49

43,7

2,03

5

2,57

116,5

42,8

5

2,57

42,8

2,21

6

2,01

104,5

41,8

6

2,01

41,8

1,88

7

1,87

102,7

30

7

1,87

30

1,91

8

2,39

110,2

44,4

8

2,39

44,4

2

9

2,18

104,7

51,2

9

2,18

51,2

1,9

10

2,17

109,4

54,6

10

2,17

54,6

1,99

11

1,8

101,1

57,4

11

1,8

57,4

1,54

12

2,36

102,6

53,2

12

2,36

53,2

1,74

13

2,5

128,5

57,6

13

2,5

57,6

2,23

14

2,27

122,5

58,3

14

2,27

58,3

2,14

15

2,33

105,2

55,7

15

2,33

55,7

1,87

№3

1

2

3

№4

1

2

3

1

48

2,27

112,5

1

15,16

32,1

24,56

2

42,1

1,94

116,4

2

16,7

31

23,7

3

42,3

2,32

111,6

3

15,44

32,4

23,78

4

43,7

2,49

108,9

4

15,65

33,2

24,1

5

42,8

2,57

116,5

5

13,13

31,2

24

6

41,8

2,01

104,5

6

14,22

34,8

23,67

7

30

1,87

102,7

7

16,73

35,4

24,9

8

44,4

2,39

110,2

8

17,8

33

32,75

9

51,2

2,18

104,7

9

16,88

34,8

26,24

10

54,6

2,17

109,4

10

15,67

33,3

25,37

11

57,4

1,8

101,1

И

15,99

36,1

25,66

12

53,2

2,36

102,6

12

14,33

38,3

24,34

13

57,6

2,5

128,5

13

15,77

30,6

22,1

14

58,3

2,27

122,5

14

15,28

32,1

20,57

15

55,7

2,33

105,2

15

17,04

37,6

24,61 '

№5

1

2

3

№6

1

2

3

1

3,45

2,27

48

1

15,16

2,12

32,1

2

3,48

1,94

42,1

2

16,7

2,2

31

3

3,06

2,32

42,3

3

15,44

2,11

32,4

4

3,66

2,49

43,7

4

15,65

2,03

33,2

5

3,79

2,57

42,8

5

13,13

2,21

31,2

6

3,85

2,01

41,8

6

14,22

1,88

34,8

7

3,44

1,87

30

7

16,73

1,91

35,4

8

4,08

2,39

44,4

8

17,8

2

33

9

4,5

2,18

51,2

9

16,88

1,9

34,8

10

4,31

2,17

54,6

10

15,67

1,99

33,3

11

3,57

1,8

57,4

И

15,99

1,54

36,1

12

3,55

2,36

53,2

12

14,33

1,74

38,3

13

4,61

2,5

57,6

13

15,77

2,23

30,6

14

3,99

2,27

58,3

14

15,28

2,14

32,1

№7

1

2

3

№8

1

2

3

1

3,45

48

112,5

1

3,45

112,5

2,12

2

3,48

42,1

116,4

2

3,48

116,4

2,2

3

3,06

42,3

111,6

3

3,06

111,6

2,11

4

3,66

43,7

108,9

4

3,66

108,9

2,03

5

3,79

42,8

116,5

5

3,79

116,5

2,21

6

3,85

41,8

104,5

6

3,85

104,5

1,88

7

3,44

30

102,7

7

3,44

102,7

1,91

8

4,08

44,4

110,2

8

4,08

110,2

2

9

4,5

51,2

104,7

9

4,5

104,7

1,9

10

4,31

54,6

109,4

10

4,31

109,4

1,99

11

3,57

57,4

101,1

И

3,57

101,1

1,54

12

3,55

53,2

102,6

12

3,55

102,6

1,74

13

4,61

57,6

128,5

13

4,61

128,5

2,23

14

3,99

58,3

122,5

14

3,99

122,5

2,14

15

4,78

55,7

105,2

15

4,78

105,2

1,87

№9

1

2

3

№10

1

2

3

1

3,45

2,12

32,1

1

3,45

32,1

24,56

2

3,48

2,2

31

2

3,48

31

23,7

3

3,06

2,11

32,4

3

3,06

32,4

23,78

4

3,66

2,03

33,2

4

3,66

33,2

24,1

5

3,79

2,21

31,2

5

3,79

31,2

24

6

3,85

1,88

34,8

6

3,85

34,8

23,67

7

3,44

1,91

35,4

7

3,44

35,4

24,9

8

4,08

2

33

8

4,08

33

32,75

9

4,5

1,9

34,8

9

4,5

34,8

26,24

10

4,31

1,99

33,3

10

4,31

33,3

25,37

11

3,57

1,54

36,1

И

3,57

36,1

25,66

12

3,55

1,74

38,3

12

3,55

38,3

24,34

13

4,61

2,23

30,6

13

4,61

30,6

22,1

14

3,99

2,14

32,1

14

3,99

32,1

20,57

15

4,78

1,87

37,6

15

4,78

37,6

24,61



Тема 5. Межотраслевой баланс

Задание. Составить межотраслевой баланс планового периода.

Исполнение. Определить межотраслевые потоки, конечную продукцию, валовую продукцию, условно-чистую продукцию, коэффициенты прямых и полных материальных затрат.

^ Оценка. Формирует необходимые представления о структуре и содержании межотраслевого баланса.

Время выполнения заданий: 4 часа.

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

Условие задачи 1: Представлен межотраслевой баланс отчетного периода: конечная продукция отраслей (Yi) и межотраслевые потоки (Xij). Определить

Недостающие данные в таблице.

Определить коэффициенты прямых материальных затрат (aij).

Составить плановый межотраслевой баланс, исходя из предположения, что конечный продукт в первой и во второй отраслях возрастет по сравнению с отчетным периодом на 5%, а в третьей отрасли на 2%. (Коэффициенты прямых материальных затрат те же, что и в отчетном периоде).


Межотраслевой баланс отчетного периода


Производящие

отрасли

Потребляющие отрасли

Конечная продукция

Валовая продукция

1

2

3

1

22

15

35

48




2

34

18

12

24




3

47

22

17

28




Условно-чистая продукция
















Валовая продукция

















^ Решение задачи:

На листе электронной таблицы Excel подготовить таблицу с исходными данными (рисунок 1).




Рисунок 1 - Таблица с исходными данными

Валовая продукция для каждой отрасли рассчитывается как сумма межотраслевого потока отрасли и конечной продукции. Валовая продукция по строкам и соответствующим столбцам совпадает. Условно-чистая продукция каждой отрасли – это разница между валовой продукцией отрасли и суммой межотраслевых потоков отрасли. Чтобы рассчитать сумму каких - либо величин используется встроенная функция СУММ (Вставка/Функции/Математические/СУММ).

Расчет валовой продукции и условно-чистой продукции по каждой отрасли в Excel приведен на рисунке 2.



Рисунок 2 - Расчет валовой продукции и условно-чистой продукции по каждой отрасли

Результаты расчетов приведены на рисунке 3.



Рисунок 3 - Результаты расчетов недостающих данных в таблице


Коэффициенты прямых материальных затрат aij рассчитываются по формуле

aij = xij/xj,

где xij - межотраслевые потоки;

xj – валовая продукция j-ой отрасли.

Расчет матрицы коэффициентов прямых материальных затрат А в Excel приведен на рисунке 4. Результаты расчетов приведены на рисунке 5.

Расчет межотраслевого баланса планового периода начнем с расчета конечной продукции, в первой и второй отраслях она увеличилась на 5%, а в третьей на 2% (рисунок 6). Результаты расчетов представлены на рисунке 7.

Далее рассчитываем вектор валовой продукции отраслей (Хпл) в плановом периоде по формуле

Хпл = В Упл,

где В – матрица обратная к матрице (Е-А) (Е – единичная матрица);

Упл – вектор конечной продукции в плановом периоде.

Сначала рассчитаем матрицу (Е-А). Расчет приведен на рисунке 8, а результаты расчета на рисунке 9.




Рисунок 4 – Расчет матрицы коэффициентов прямых материальных затрат




Рисунок 5 – Матрица коэффициентов прямых материальных затрат



Рисунок 6 – Расчет конечной продукции отраслей в плановом периоде





Рисунок 7 - Конечная продукция отраслей в плановом периоде



Рисунок 8 – Расчет матрицы (Е-А)




Рисунок 9 – Матрица (Е-А)


Расчет матрицы В – обратной к матрице (Е-А) проводим с помощью встроенной функции МОБР (Вставка/Функции/Математические/МОБР). Ячейку В28 делаем активной, и в нее вставляем функцию МОБР (рисунок 10), результатом расчета является одна заполненная ячейка В28 (рисунок 11), далее необходимо скопировать данную формулу во все ячейки массива, для этого выделяем диапазон ячеек В28:D30, начиная с ячейки В28, затем нажимаем клавишу F2, после этого комбинацию клавиш Ctrl +Shift+Enter. Результатом данных действий будет заполнение выделенного диапазона ячеек числовыми данными (матрица В) (рисунок 12).




Рисунок 10 – Встроенная функция МОБР




Рисунок 11 – Результат расчета по функции МОБР



Рисунок 12 – Матрица В


Используя матрицу В, по формуле Хпл = В Упл, рассчитываем валовую продукцию каждой отрасли в плановом периоде. Для этого необходимо матрицу В умножить на вектор-столбец валовой продукции, чтобы это сделать в Excel нужно воспользоваться встроенной функцией МУМНОЖ (Вставка/Функции/Математические/МУМНОЖ). Ячейку F14 делаем активной, и в нее вставляем функцию МУМНОЖ (рисунок 13), результатом расчета является одна заполненная ячейка F14 (рисунок 14), далее необходимо скопировать данную формулу во все ячейки массива, для этого выделяем диапазон ячеек F14: F17, начиная с ячейки F14, затем нажимаем клавишу F2, после этого комбинацию клавиш Ctrl +Shift+Enter. Результатом данных действий будет заполнение выделенного диапазона ячеек числовыми данными (столбец - валовая продукция) (рисунок 15). Валовая продукция планового периода по строкам и соответствующим столбцам совпадает.

Рисунок 13 - Встроенная функция МУМНОЖ




Рисунок 14 - Результат расчета по функции МУМНОЖ




Рисунок 15 – Валовая продукция планового периода


Межотраслевые потоки в плановом периоде рассчитываются по формуле xij = aij xjпл. Расчет межотраслевых потоков в Excel приведен на рисунке 16. Условно-чистая продукция каждой отрасли в плановом периоде – это разница между валовой продукцией отрасли планового периода и суммой межотраслевых потоков отрасли в плановом периоде (рисунок 16). Межотраслевой баланс планового периода представлен на рисунке 17.




Рисунок 16 – Расчет межотраслевых потов и условно-чистой продукции в плановом периоде




Рисунок 17 – Межотраслевой баланс планового периода

Условие задачи 2: Представлен межотраслевой баланс отчетного периода: конечная продукция отраслей (Yi) и межотраслевые потоки (Xij). Определить

Недостающие данные в таблице.

Определить коэффициенты прямых материальных затрат (aij).

Составить плановый межотраслевой баланс, исходя из предположения, что конечный продукт в первой и во второй отраслях возрастет по сравнению с отчетным периодом на 5%, а в третьей отрасли на 2%. (Коэффициенты прямых материальных затрат те же, что и в отчетном периоде).


Вариант 1

Межотраслевой баланс отчетного периода


Производящие

отрасли

Потребляющие отрасли

Конечная продукция

Валовая продукция

1

2

3

1

24

28

37

20




2

18

22

15

56




3

40

18

25

15




Условно-чистая продукция
















Валовая продукция

















Вариант 2

Межотраслевой баланс отчетного периода


Производящие

отрасли

Потребляющие отрасли

Конечная продукция

Валовая продукция

1

2

3

1

24

18

40

20




2

37

15

28

56




3

20

36

22

15




Условно-чистая продукция
















Валовая продукция

















Вариант 3

Межотраслевой баланс отчетного периода


Производящие

отрасли

Потребляющие отрасли

Конечная продукция

Валовая продукция

1

2

3

1

24

28

37

30




2

18

22

15

40




3

40

18

25

68




Условно-чистая продукция
















Валовая продукция


















Вариант 4

Межотраслевой баланс отчетного периода


Производящие

отрасли

Потребляющие отрасли

Конечная продукция

Валовая продукция

1

2

3

1

24

18

40

30




2

37

15

28

40




3

20

36

22

68




Условно-чистая продукция
















Валовая продукция


















Вариант 5

Межотраслевой баланс отчетного периода


Производящие

отрасли

Потребляющие отрасли

Конечная продукция

Валовая продукция

1

2

3

1

24

28

37

17




2

18

22

15

28




3

40

18

25

50




Условно-чистая продукция
















Валовая продукция

















Вариант 6

Межотраслевой баланс отчетного периода

Производящие

отрасли

Потребляющие отрасли

Конечная продукция

Валовая продукция

1

2

3

1

24

18

40

17




2

37

15

28

28




3

20

36

22

50




Условно-чистая продукция
















Валовая продукция

















Вариант 7

Межотраслевой баланс отчетного периода


Производящие

отрасли

Потребляющие отрасли

Конечная продукция

Валовая продукция

1

2

3

1

24

28

37

25




2

18

22

15

12




3

40

18

25

40




Условно-чистая продукция
















Валовая продукция
















Вариант 8

Межотраслевой баланс отчетного периода


Производящие

отрасли

Потребляющие отрасли

Конечная продукция

Валовая продукция

1

2

3

1

24

18

40

25




2

37

15

28

12




3

20

36

22

40




Условно-чистая продукция
















Валовая продукция

















Вариант 9

Межотраслевой баланс отчетного периода


Производящие

отрасли

Потребляющие отрасли<