Лабораторная работа №8. Построить гистограмму для исходных данных, находящихся на разных листах

1. Создайте новую книгу. Переименуйте первый лист как «2004год», второй лист - как «2005год», третий лист - как «2006год». На каждом листе создайте таблицу с данными по переработке сырья.

Например, на первом листе введите данные соответственно таблице 9.

 

Таблица 9.

	A	B	C	D
	Продукт	План	Переработано	% выполнения
	Пшеница			=C2/B2
	Гречка			=C3/B3
	Ячмень			=C4/B4
	Кукуруза			=C5/B5

 

На листах «2005год» и «2006год» данные представьте таким же образом.

 

	A	B	C	D
	Продукт	План	Переработано	% выполнения
	Пшеница			=C2/B2
	Гречка			=C3/B3
	Ячмень			=C4/B4
	Кукуруза			=C5/B5
Лист «2005год»
	A	B	C	D
	Продукт	План	Переработано	% выполнения
	Пшеница			=C2/B2
	Гречка			=C3/B3
	Ячмень			=C4/B4
	Кукуруза			=C5/B5
Лист «2006год»

 

2. Щелкните мышью на любой пустой ячейке листа. Вызовите мастер диаграмм. Выберите гистограмму.

3. Перейдите на вкладку «Ряд» (список «Ряд» пока является пустым).

4. Выберите команду «Добавить». В поле «Имя» введите название первого ряда (столбика): 2004. Перейдите на поле «Значения». Удалите его прежнее содержимое. Щелкните на ярлыке первого листа «2004год» и выделите на этом листе диапазон D2: D5 (т.е. четвертый столбец таблицы).

5. Снова нажмите «Добавить», в поле «Имя» введите 2005, а в поле «Значения» укажите диапазон D2: D5 из листа «2005год».

6. Повторите эти действия (шаг 4) для данных из третьего листа «2006год».

7. Перейдите на поле «Подписи оси X» и выделите на листе «2006год» диапазон A2: A5 (содержимое ячеек этого диапазона появится в качестве подписей на горизонтальной оси диаграммы). Нажать «Далее».

8. Задайте заголовок диаграммы: «Выполнение плана в 2004-2006 гг.». Нажать «Далее».

9. Задайте имя листа: «Гистограмма». Выберите вариант размещения: На отдельном листе.

В итоге получится диаграмма, представленная на рис. 23.

Рис. 23

 

4.10. Лабораторная работа № 9. Финансовый анализ

 

1. Задача: вычислить 30-летнюю ипотечную ссуду для покупки автомобиля за 201900 р. со ставкой 8% годовых при начальном взносе 20% и ежемесячной (ежегодной) выплате.

Расчет будет производиться с помощью функции ППЛАТ (ПЛТ):

Функция ППЛАТ вычисляет величину периодической выплаты (например, регулярных платежей по займу) при постоянной процентной ставке.

Синтаксис:

ППЛАТ (ставка; кпер; нз; бз; тип), где:

ставка – процентная ставка за период;

кпер – общее число периодов выплат;

нз – общая сумма, которую составят будущие платежи (текущее значение);

бз – будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если бз опущен, то его значение полагается равным 0.

тип – число, равное 0 или 1, обозначающее, когда должна производиться выплата. Если значение параметра тип равно 0 или он опущен, то оплата производится в конце периода, если его значение равно 1, то в начале периода.

 

Заполните Лист1 новой книги следующим образом:

 

Рис. 24

Результаты расчета:

 

 

Рис. 25

 

2. Задача: Вас просят дать в долг 10000 руб. и обещают вернуть через год 2000 руб., через два года – 4000 руб., через три года – 7000 руб. При какой годовой процентной ставке эта сделка имеет смысл?

Расчет будет производиться с помощью функции НПЗ (NPV или ЧПС):

Функция НПЗ возвращает чистый текущий объем вклада – это сегодняшний объем будущих платежей (отрицательные значения) и поступлений (положительные значения). Например: у вас берут в долг некоторую сумму денег и предлагают через k₁ год вернуть Р₁ денег, через k₂ года вернуть Р₂ денег и т.д. через k_n лет вернуть P_n денег. Кроме данной сделки у вас есть альтернативный способ использования ваших денег: положить их в банк под i% годовых. Тогда чистым текущим объемом вклада является та сумма денег, которой вы должны располагать в начальный год, чтобы, положив их в банк под i% годовых, получить предлагаемую вам прибыль.

 

Синтаксис функции:

НПЗ (ставка; 1-е значение; 2-е значение; …), где:

ставка – процентная ставка за период;

1-е значение; 2-е значение; … - (1-29) расходы и доходы, равномерно распределенные во времени и осуществляющиеся в конце каждого периода.

 

Заполните Лист 2 следующим образом (рис. 26):

 

Рис. 26

Затем войдите в Сервис-Подбор параметра, заполните поля и нажмите ОК:

 

Рис. 27

 

Искомая процентная ставка получится 11, 79%.

 

3. Задача: Вас просят дать в долг 10000 руб. и обещают возвращать по 2000 руб. в течение 6 лет. Будет ли выгодна эта сделка при годовой ставке 7%?

Расчет будет производиться с помощью функции ПЗ (PV или ПС):

Функция ПЗ возвращает текущий объем вклада на основе постоянных периодических платежей. Аналогична функции НПЗ. Различия: ПЗ допускает, чтобы денежные взносы происходили либо в конце, либо в начале периода. Кроме того, в отличие от переменных денежных взносов в функции НПЗ, денежные взносы в функции ПЗ должны быть постоянны в течение всего периода инвестиции.

Синтаксис:

ПЗ (ставка; кпер; выплата; бз; тип)

выплата – величина постоянных периодических платежей;

остальные параметры – см. функцию ППЛАТ.

 

Заполните Лист3 следующим образом:

Рис. 28

При этом в ячейку В6 введите формулу:

В6 =ЕСЛИ(В1< В5; ”Выгодно дать деньги в долг”; ЕСЛИ(В5=В1; ”Варианты

равносильны”; “Выгоднее деньги положить под проценты”)):

 

Рис. 29

В результате чистый текущий объем вклада равен 9533, 08 руб.

4. Задача. Вычислить основные платежи, плату по процентам, общую ежегодную плату и остаток долга на примере ссуды 100000 руб. на срок 5 лет при годовой ставке 2%.

 

На Листе 6 введите данные:

 

 

Рис. 30

 

Для облегчения чтения формул присвоим ячейкам (диапазонам) имена. Присвоение имени осуществляется следующим образом:

1. Выделите ячейку (или диапазон).

2. Выберите Вставка-Имя-Присвоить.

3. Если Excel предлагает подходящее имя, оставьте его, или введите нужное имя в поле Имя.

4. Нажать ОК.

 

Присвойте ячейкам следующие имена:

Ячейка	Имя
В1	Процент
В2	Срок
В3	Ежегодная_плата
В4	Размер_ссуды

 

В ячейки введите формулы:

 

Ячейка	Формула	Комментарий формулы
В3	=ППЛАТ(Процент; Срок; -Размер_ссуды)	Ежегодная плата
D6	=Размер_ссуды	Начальный остаток долга
A7	=А6+1	Номер года
B7	=D6*Процент	Плата по процентам за первый год
C7	=Ежегодная_плата	Основная плата за первый год
D7	=ЕСЛИ(D6-C7< > 0; D6-C7)	Остаток долга за первый год

 

В остальные годы эти платы определяются перемещением маркера заполнения диапазона А7: D7 вниз по столбцам до тех пор, пока в столбце остатка долга не появится ноль.

 

Результаты расчетов:

Рис. 31

5. Задача: Вы берете в долг 1000 руб. при годовой ставке 1% и собираетесь выплачивать по 100 руб. в год. Сколько лет займут эти выплаты?

 

Используем функцию КПЕР (ставка; выплата; нз; бз; тип), где

 

выплата – величина постоянных периодических платежей;

нз – текущее значение, т.е. общая сумма, которую составят будущие платежи.

 

=КПЕР(1%; -100; 1000)

Ответ: 11.

4.11. Лабораторная работа № 10. Задача анализа безубыточности предприятия

 

1.Задача: Компания Boats производит три вида гоночных яхт - S, R и B. Данные о затратах и доходах на ближайший плановый период представлены в табл. 7.

Таблица 7.Данные о затратах и доходах компании Boats

Яхты	Цена, долл. за ед.	Переменные затраты, долл. за ед.	Фиксированные затраты, долл. за ед.
S	10 000		5 000 000
R			3 000 000
B	15 000		10 000 000

 

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

На следующий плановый год руководство компании заключило контракт на производство 700 яхт S. Клиент заказал 400 яхт В, и руководство заинтересовано в выполнении этого заказа. Анализ рынка, проведенный отделом маркетинга компании, показал, что следует произвести не более 300 яхт R. Руководство компании хочет выяснить, сколько яхт необходимо продать, чтобы добиться безубыточности.

Точка безубыточности характеризуется тем, что суммарный доход равен суммарным затратам. Руководство заинтересовано в том, чтобы минимизировать расходы. Поскольку фиксированные затраты придется нести в любом случае, целью можно считать минимизацию суммарных переменных затрат.

Обозначим:

- количество произведенных яхт класса S;

- количество произведенных яхт класса R;

- количество произведенных яхт класса B.

Тогда уравнение точки безубыточности примет вид:

, или

.

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

Минимизировать

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

Решение задачи в MS Excel приведено на рис.32; 33; 34.

 

Рис. 32

Для решения набрать формулы и тексты на листе, как показано на рис. 32. Затем войти в меню Сервис - Поиск решения. Если в Сервис такого пункта нет, то выбрать Сервис – Надстройки – щелкнуть флажок (установить) в строке Поиск решения, этот пункт появится в меню Сервис. Выбрать этот пункт и открывшееся окно заполнить, как показано на рис. 33. Ограничения добавлять по одному, предварительно нажав кнопку Добавить (в этом окне). Результаты показаны на рис. 34.

Рис. 33

 

Рис. 34

⇐ Предыдущая234567891011Следующая ⇒

Поделиться:

Популярное:

1. I.4. СЕМЬЯ И ШКОЛА : ОТСУТСТВИЕ УСЛОВИЙ ДЛЯ ВОСПИТАНИЯ
2. IDEF1X - методология моделирования данных, основанная на семантике, т.е. на трактовке данных в контексте их взаимосвязи с другими данными.
3. II. Ассистивные устройства, созданные для лиц с нарушениями зрения
4. II. Порядок представления статистической информации, необходимой для проведения государственных статистических наблюдений
5. II. Работа с раздаточным материалом
6. III. Защита статистической информации, необходимой для проведения государственных статистических наблюдений
7. III. Перечень вопросов для проведения проверки знаний кандидатов на получение свидетельства коммерческого пилота с внесением квалификационной отметки о виде воздушного судна - самолет
8. III. Работа по теме урока Представление журналов
9. IV. Работа над пройденным материалом.
10. MS Excel. Расчеты с условиями. Работа со списками
11. MS Word. Работа с математическими формулами
12. Qt-1 - сглаженный объем продаж для периода t-1.