Ревчук И.Н. Практикум по MS Excel для экономистов

Подождите немного. Документ загружается.

61 62

Рисунок 40

Полученное в результате решение приведено на рисунке 41.

Рисунок 41

Задача 3.3. От нити, равной периметру некоторого квадрата,

отрезано 36 см. Укороченная таким образом нить представляет

собой периметр другого квадрата, площадь которого в 2,25

раза меньше первого. Определить первоначальную длину нити.

Решение. Построим модель. Пусть ячейка А1 является изменяе-

мой. В ней будет располагаться длина стороны первоначального

квадрата. Тогда сторона нового квадрата вычисляется

по формуле

=A1-9.

Поместим эту формулу в ячейку А2. В ячейках Е1 и Е2 распола-

гаются площади исходного и нового квадратов соответственно.

В ячейке А4 расположен периметр исходного квадрата, а в ячей-

ке Е4 – отношение периметров исходного и нового квадратов

(рисунок 42).

Выполнив команду Сервис/Поиск решения, приведем окно

диалога к виду

, как на рисунке 43.

Рисунок 42

Рисунок 43

Результат приведен на рисунке 44.

63 64

Рисунок 44

Задача 3.4. Таблица на рисунке 45 представляет смету расхо-

дов на выполнение некоторых работ, где отдельные статьи

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

Эти зависимости для ячеек С5:С11 приведены на рисунке 46.

Анализ сметы сводится к сопоставлению отдельных статей

расходов, общих расходов и прибыли с общей стоимостью ра-

бот.

Определить:

1) при какой общей стоимости работ прибыль достигнет ве-

личины 500 ед.;

2) при какой общей стоимости работ отношение прибыли к

общей стоимости достигнет величины 50 %.

Рисунок 45

=0,37*Заработная_плата

=0,05*Заработная_плата

=0,1*(Материалы_и_комплектующие+Заработная_плата)

=СУММ(C3:C7)

=0,05*(Всего-Услуги_сторонних_организаций)

=0,05*Всего

=Всего-Итого-Услуги_сторонних_организаций-Накладные_расходы

Рисунок 46

Решение. 1. Присвоим ячейкам С11 и С12 имена Прибыль и Все-

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

установим ячейку Прибыль в качестве целевой, а в качестве из-

меняемой – ячейку Всего (рис. 47), получим решение (рис. 48).

2. Введем в ячейку D12 формулу =Прибыль/Всего и отформати-

руем ее с помощью процентного формата. Выполнив команду

Сервис/Поиск решения, установим ячейку D12 в качестве целе-

вой, значение которой равно 50 %, а в качестве изменяемой –

ячейку Всего (рис. 49), получим решение (рис. 50).

Рисунок 47

65 66

Рисунок 48

Задачи для самостоятельного решения

1. Для перевозки А т овощей было потребовано несколько

грузовиков определенной грузоподъемности. За неимением

свободных грузовиков этой грузоподъемности гараж выслал

грузовики с грузоподъемностью на полтонны меньше и дал та-

ких грузовиков на один больше. Сколько тонн овощей взял

каждый из высланных грузовиков?

Рисунок 49

Рисунок 50

2. Два автомобиля выезжают одновременно в пункт, отстоящий

от него на А км. Скорость первого на 10 км больше скорости

второго, и поэтому первый автомобиль приезжает на место на 1

час раньше второго. Определить скорости автомобилей.

3. Заданы координаты 20 точек на плоскости (множество «На-

селенные пункты»). Найти такую точку на этой плоскости, сум-

марное расстояние от которой до остальных точек множества

«Населенные пункты» минимально.

4. Стулья, произведенные на предприятии, продаются на той

же неделе. Удельная валовая прибыль (доход – расход) состав-

ляет А ед. для каждого проданного стула марки С и В ед. для

каждого стула марки М. Для сборки стула нужны длинные и ко-

роткие штифты и одно из двух типов сидений, которые имеются

на складе

в ограниченном количестве. Запас длинных и корот-

ких штифтов, которые можно будет использовать на следующей

неделе, составляет 1280 и 1600 штук соответственно. Для произ-

водства одного стула марки С требуется 8 длинных и 4 коротких

штифта, а для производства стула марки М – 4 длинных и 12 ко-

ротких штифтов. Запас ножек на следующую неделю составляет

760 штук

. Для производства одного стула любого типа требуется

4 ножки. Запас прочных и облегченных сидений составляет 140

и 120 штук соответственно. Для производства стульев типа С

используются прочные сиденья, а для стульев марки М – облег-

67 68

ченные. Общее число произведенных стульев по договору не

может быть менее 100. Определить, сколько стульев каждой

марки необходимо произвести на следующей неделе для полу-

чения максимальной прибыли.

5. Наилучшим образом внести сумму в А руб. на годовые де-

позиты в три банка. Условия банка №1: 10 % годовых при еже-

квартальном начислении процентов. Условия банка №2: 12 %

годовых при полугодовом начислении процентов. Условия

банка №3: 9 % годовых при ежемесячном начислении процен-

тов. Не следует доверять одному банку более половины капи-

тала. Банки не

принимают сумм меньших 50000 руб.

6. Цех мебельного комбината выпускает трельяжи, трюмо и

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

на одно изделие, плановая себестоимость, оптовая цена пред-

приятия, плановый (месячный) ассортимент и трудоемкость

единицы продукции приведены в таблице 4. Запас древесно-

стружечных плит, досок еловых и березовых – 90, 30 и 14 м

Плановый фонд рабочего времени составляет 16800 человеко-

часов.

Исходя из необходимости выполнения плана по ассор-

тименту и возможности его перевыполнения по отдельным

(или даже всем) показателям, построить модель, на основе ко-

торой можно решить а) задачу максимизации объема реализа-

ции (за плановый период); б) задачу максимизации прибыли

(за тот же

период).

Таблица 4

Показатели

Трель-

яжи

Трюмо

Тум-

бочки

Норма расхода материала, м

Древесностружечные плиты 0,032 0,031 0,038

Доски: еловые 0,020 0,020 0,008

березовые 0,005 0,005 0,006

Трудоемкость, чел.-ч. 10,2 7,5 5,8

Плановая себестоимость 88,81 63,98 29,60

Продолжение таблицы

Оптовая цена предприятия 93 76 30

Плановой ассортимент 350 290 1200

7. Фабрика выпускает кожаные брюки, куртки и пальто специ-

ального назначения в ассортименте, заданном соотношением

2:1:3. В процессе изготовления изделия проходят три производ-

ственных участка – дубильный, раскройный и пошивочный.

Фабрика имеет практически неограниченную сырьевую базу,

однако сложная технология предъявляет высокие требования к

квалификации рабочих. Численность их в рамках планируемого

периода ограничена.

Время обработки изделий на каждом участке, их плановая

себестоимость, оптовая цена предприятия приведены в

таблице 5.

Ограничения на фонд времени для дубильного, рас-

кройного и пошивочного участков составляют соответственно

3360, 2688 и 5040 часов.

Учитывая заданный ассортимент, построить модель, на

основе которой можно сформулировать экстремальную задачу

определения напряженного месячного плана по прибыли от реа-

лизованной продукции.

Таблица 5

Показатели Брюки Куртки Пальто

Норма времени на участках,

чел.-ч.

Дубильном 0,3 0,4 0,6

Раскройном 0,4 0,4 0,7

Пошивочном 0,5 0,4 0,8

Полная себестоимость 15 40,5 97,8

Оптовая цена предприятия 17,5 42 100

8. На приобретение оборудования для нового произ-

водственного участка выделено 300 ед. Его предполагается раз-

69 70

местить на площади 45 м

. Участок может быть оснащен обо-

рудованием трех видов – машинами стоимостью 6 ед. (здесь и

далее все показатели приводятся на единицу оборудования),

размещающимися на площади 9 м

, производительностью 8

тыс. ед. продукции за смену; машинами стоимостью 3 ед., раз-

мещающимися на площади 4 м

, производительностью 4 тыс.

ед. продукции за смену; машинами стоимостью 2 ед., разме-

щающимися на площади 3 м

, производительностью 3 тыс. ед.

продукции за смену.

Построить модель, на основе которой можно сфор-

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

обретения оборудования, обеспечивающего наибольшую про-

изводительность всего участка.

9. В плановом году строительные организации города пере-

ходят к сооружению домов типов Д-1, Д-2, Д-3 и Д-4. Данные

о количестве квартир разного

типа в каждом из указанных ти-

пов домов и их плановая себестоимость приведены в таблице 6.

Таблица 6

Показатели Д-1 Д-2 Д-3 Д-4

Типы квартир

Однокомнатные 10 18 20 15

Двухкомнатные

Смежные 40 - 20 -

Несмежные - 20 - 60

Трехкомнатные 60 90 10 -

Четырехкомнатные 20 10 - 5

Плановая себестоимость 83000 83500 36000 45000

Годовой план ввода жилой площади составляет соответ-

ственно 800, 1000, 900, 2000 и 7000 квартир указанных типов.

Исходя из необходимости выполнения плана ввода квартир

(возможно его перевыполнение по всем показателям) и обес-

печенности строительными материалами, построить модель и

сформулировать на ее основе экстремальную задачу, анализ

которой позволит обосновать объем капиталовложений в жи-

лищное строительство на

плановый год.

10. Компания производит две марки телевизоров – А и С. Рабо-

тают два конвейера, каждый из которых выпускает телевизоры

одной марки, и два цеха, занятых производством деталей для те-

левизоров обеих марок. Производственная мощность конвейера,

выпускающего телевизоры марки А, составляет 70 телевизоров в

день, а конвейера С – 50 телевизоров в день. Цех

1 производит

телевизионные трубки. На производство одной телевизионной

трубки для телевизора марки А требуется 1 час рабочего време-

ни, а на производство трубки для телевизора марки С – 2 часа. В

настоящее время в цехе 1 производству трубок для телевизоров

обеих марок может быть уделено не более 120 часов рабочего

времени в день. В цехе 2 изготавливаются

корпуса телевизоров,

причем для производства одного корпуса для телевизора любой

марки требуется 1 час рабочего времени. Цех 2 может посвятить

изготовлению корпусов не более 90 часов рабочего времени в

день. Удельная валовая прибыль от реализации одного телеви-

зора марки А и С составляет 20 и 10 единиц соответственно..

Каким должен быть дневной план производства для максимиза

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

произведенные телевизоры?

11. Завод должен переслать заказчику 1100 деталей, которые для

пересылки требуется упаковать в ящики трех типов. Один ящик

первого типа вмещает 70 деталей, второй – 40 деталей, третий –

25 деталей. Стоимость пересылки одного ящика первого, второ-

го и третьего типов равна соответственно 20, 10 и 7 единиц

. По-

строить модель, на основе которой можно сформулировать экс-

тремальную задачу определения плана перевозки, обеспечи-

вающего минимальную стоимость пересылки. Рассмотреть ва-

рианты, когда а) недогрузка ящиков не допускается.; б) не-

догрузка ящиков допускается.

12. Необходимо разменять денежную сумму в S единиц так,

чтобы получить р купюр достоинством k, n и m единиц соответ-

ственно.

13. Необходимо разменять денежную сумму в S единиц так,

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

тоинством k, n и m единиц соответственно

14. На складе имеются М станков четырех типов весом k, l, m и

n. Необходимо перевезти их на машине заданной грузоподъемно-

сти так, чтобы количество рейсов было минимальным.

71 72

15. Имеются А предметов, каждый из которых характеризует-

ся своим весом и стоимостью. Необходимо упаковать эти пред-

меты в «рюкзак» ограниченной вместимости так, чтобы их сум-

марная стоимость была максимальной.

16. Заданы координаты К селений. Построить модель, на осно-

ве которой можно сформулировать экстремальную задачу выбо-

ра пункта размещения станции скорой

помощи таким образом,

чтобы поездка в самое удаленное селение (по времени) занимала

бы минимальное время.

17. Банка корма весом 450 г для собаки должна содержать как

минимум следующее количество питательных веществ: белков –

85 г, углеводов – 140 г и жиров – 115 г. Нужно смешать четыре

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

дешевую банку

собачьего корма, соответствующего требовани-

ям по содержанию питательных веществ. Содержание питатель-

ных веществ и цена каждого вида корма в расчете на 450 г при-

ведены в таблице 7.

Таблица 7

Содержание питательных веществ и цена

Корм Белки Углеводы Жиры Цена

85 198 142

142 113 170

57 57 170

85 227 57

18. Предприятие имеет возможность вложить а) до 55 млн.

руб., б) до 90 млн. руб. При этом цена капитала составляет

10 %. Составьте оптимальный инвестиционный портфель из

следующих делимых возможных проектов:

Значения в млн. руб.

А -30 6 11 13 12

Б -20 4 8 12 5

В -40 12 15 15 15

Г -15 4 5 6 5

19. Анализируются 4 независимых делимых проекта. Цена капи-

тала составляет 12 %. Бюджет ограничен А. ед. Составьте опти-

мальный инвестиционный портфель.

Значения в тыс. ед.

А -31 6 6 6 6 6 6 6 6

Б -60 20 20 40 10

В -25 80

Г -40 30 25

20. Определить, в каком количестве следует выпускать продук-

цию четырех типов А, Б, В и Г для получения максимальной

прибыли, если для ее изготовления требуются ресурсы трех ти-

пов в количествах, приведенных в таблице 8.

Таблица 8

Тип п

од

кции

Ресурс А Б В Г Наличие

ибыль 60 70 120 130

Труд 1 1 1 1 16

Сырье 6 5 4 3 110

Финансы 4 6 10 13 100

21. Создать таблицу, приведенную на рисунке 51. В этой табли-

це представлены поквартальные сведения о сбыте некоторых то-

варов, себестоимости продукции, доходах от реализации и вели-

чине прибыли. Объем сбыта зависит от сезонного коэффициента

(К

), а также от затрат на рекламу продукции при прочих фикси-

рованных факторах. При создании таблицы исходными данными

являются величины К

, Q

, C и R. Остальные величины вычис-

ляются по формулам, приведенным на рисунке).

73 74

Рисунок 51

Определить:

• можно ли получить в IV квартале прибыль 100 000 ед., из-

меняя расходы на рекламу Q

;

• определить величину расходов на рекламу в IV квартале

), необходимую для получения прибыли 150 000 ед.;

• определить максимальное значение прибыли P

с точностью

до единиц целой части числа.

22. Инвестиционная компания должна определить, куда вло-

жить средства в размере 10 миллионов единиц. Цель – макси-

мизировать ожидаемый доход в следующем году. Четыре ва-

рианта вложения средств представлены в таблице 9. Компания

также приняла решение, что не менее 30 % средств должно

быть вложено в обыкновенные акции

и долгосрочные облига-

ции казначейства и не менее 40 % — в фонды денежного рын-

ка и муниципальные облигации. Необходимо инвестировать все

имеющиеся средства.

Таблица 9

Варианты инвестирования

Ожидаемый

доход, %

Максимально

возможная сумма

инвестиций,

млн. ед.

Обыкновенные акции 8 5

Облигации казначейства 6 7

Фонд денежного рынка 12 2

Муниципальные облигации 9 4

23. Организация управляет четырьмя фермами, производитель-

ность которых примерно одинакова. Каждая ферма имеет опре-

деленную площадь сельхозугодий и ей требуется определенное

количество часов рабочего времени, чтобы ухаживать за расте-

ниями и убирать урожай. Данные для предстоящего сезона

представлены в таблице 10.

Таблица 10

Ферма

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

площадь, акры

Ресурс рабочего

времени в месяц, ч

1 500 1700

2 900 3000

3 300 900

4 700 2200

Организация рассматривает варианты выращивания трех куль-

тур, которые отличаются ожидаемой удельной прибылью в рас-

чете на один акр занимаемой ими площади и требуемыми трудо-

затратами, что отражается в таблице 11. Общая площадь, выде-

ленная под каждую определенную культуру, ограничена требо-

ваниями к уборочной технике. Чтобы поддержать примерно

одинаковую рабочую нагрузку на ферму

, руководство организа-

ции решило, что процентные отношения площадей, занятых оп-

ределенными культурами, должны быть одинаковы на всех фер-

мах. Однако ферма может выращивать произвольную комбина-

75 76

цию культур, удовлетворяющую всем ограничениям (в том

числе и требованию одинаковой нагрузки). Руководство орга-

низации хочет знать, какие площади должны быть выделены

под каждую культуру на каждой ферме, чтобы максимизиро-

вать ожидаемую прибыль

Таблица 11

Культура

Максимальная

площадь, акры

Трудозатраты,

часы в месяц,

Ожидаемая

прибыль на

акр, ед.

A 700 2 500

B 800 4 200

C 300 3 300

Построение графиков функций и диаграмм

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

Задача 4.1. Построить график функции, отражающей измене-

ние численного состава жителей города Гродно за временной

интервал с 1588 по 1987 годы.

Решение. Выделяем диапазон G17:T17 и нажимаем кнопку

Мастер диаграмм на панели инструментов СТАНДАРТНАЯ

или выполняем команду ВСТАВКА/ДИАГРАММА. Да-

лее на рисунках 52–58 показаны шаги МАСТЕРА ДИАГРАММ,

приводящие к построению нужного графика на рисунке 59.

Рисунок 52

На первом шаге выбирается тип диаграммы. В данном случае

строится график.

77 78

Рисунок 53

На шаге 2 Мастер диаграмм пытается пронумеровать данные

по оси ОХ. Эти подписи неправильные (нужно, чтобы отобра-

жались года), поэтому переходим на вкладку РЯД и заполняем

строку Подписи оси Х, обводя мышью диапазон G16:T16.

Рисунок 54

79 80

Рисунок 55

Нажимаем на кнопку ДАЛЕЕ, вводим название диаграм-

мы и обозначение по оси Y.

На рисунке 54 подписи по оси ОХ правильные, но обо-

значение Ряд 1 лишено смысла, так как других рядов с данны-

ми нет. Поэтому переходим на вкладку ЛЕГЕНДА и снимаем

флажок ДОБАВИТЬ ЛЕГЕНДУ.

Рисунок 56

Рисунок 57