Практическое занятие 5. Объединение данных

 

Задание 1.

На основе приведенных данных за январь - март (рис. 123)

 

Январь

 

Февраль

 

 

Март

 

 

Рис. 123

 

выполнить консолидацию и получить приведенный ниже результат (рис. 124).

 

 

Рис. 124

Задание 2.

На основании исходных данных, приведенных ниже в таблице (рис. 125), создать сводную таблицу:

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

 

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

 

 

Рис. 125

Практическое занятие 6. Решение задач путем анализа данных

Сценарии и подбор параметров

Задание 1. Расчет ипотечной ссуды.

1.Создать таблицу для расчета ипотечной ссуды (рис. 126) и расчитать сумму первого взноса, размер ссуды, месячную выплату по ссуде, общую сумму выплат и сумму комиссионых. При выполнении задания присвоить имена ячейкам С6, С7, С9, С10, С11, С12, С13 и ввести их в соответствующие формулы.

 

 

 

Рис. 126

 

Постановка задачи. При покупке дома покупатель оплачивает первый взнос, равный некоторому проценту от стоимости дома, т.е.

 

,

 

а на оставшуюся сумму берет ссуду в банке под некоторую годовую процентную ставку на некоторый срок (срок погашения ссуды), т.е.

 

 

Ежемесячно покупатель выплачивает банку определенную постоянную сумму, которая определяется с помощью функции ПЛТ(ставка; кпер; пс; бс; тип), т.е

 

,

 

Эта месячная плата включает в себе основные платежи и платежи по процентам. Аргументы функции ПЛТ имеют следующий смысл:

Ставка — годовая процентная ставка. Например, если получена ссуда на автомобиль под 10 процентов годовых и делаются ежемесячные выплаты, то процентная ставка за месяц составит 10%/12 или 0,83%. В качестве значения аргумента ставка нужно ввести в формулу 10%/12 или 0,83% или 0,0083.

Кпер — общее число выплат по ссуде (срок погашения ссуды). Например, если получена ссуда на 4 года под автомобиль и делаются ежемесячные платежи, то ссуда имеет 4*12 (или 48) периодов. В качестве значения аргумента кпер в формулу нужно ввести число 48.

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

 

 

Бс — требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (нулю), что соответствует нашему случаю.

Тип — число 0 (нуль) или 1, обозначающее, когда должна производиться выплата.

 

Тип	Когда нужно платить
0 или опущен	В конце периода
	В начале периода

 

В нашем случае выплата производится в конце периода (в конце каждого месяца). В итоге обращение к функции ПЛТ(…) будет иметь вид .

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

 

 

Сумма комиссионных, которую получит банк, равна

 

 

После проведения расчетов создать следующие сценарии (рис. 127):

 

 

	Сценарии
Срок погашения ссуды, мес.
Годовая процентная ставка

 

 

Рис. 127

 

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

4. Подобрать параметры срока погашения ссуды при месячной плате 1500.

Задание 2. Расчет прибыли.

1.Создать таблицу для расчета прибыли (рис. 128). При выполнении задания присвоить имена ячейкам В3, В4, В5, В17 и ввести их в соответствующие формулы.

 

 

Рис. 128

 

Постановка задачи. Предприятие изготавливает и продает три вида изделий: изделие1, изделие 2, изделие 3. Затраты предприятия на изготовление изделия складываются из затрат на оплату работ и затрат на оплату материалов. Эти затраты предприятия определяют себестоимость изделия. Количество нормо-часов и единиц материалов на каждое изделие известно. Известны также стоимость нормо-часа работы и стоимость единицы материала. Чтобы предприятие получило прибыль от продажи изделий вводится торговая надбавка, равная некоторой доли от себестоимости. Отпускная цена на изделие определяется как себестоимость плюс торговая надбавка.

Расчетные формулы имеют следующий вид:

Себестоимость го изделия определяется по формуле:

 

 

где себестоимость го изделия,

стоимость единицы материала для го изделия,

количество единиц материала для го изделия.

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

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

Торговая надбавка для го изделия определяется по формуле

 

 

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

относительное значение торговой надбавки для го изделия, в % от себестоимости.

Отпускная цена на ое изделие опреляется по формуле

 

.

 

Прибыль по му (одному) изделию рассчитывается по формуле

 

.

 

Прибыль на изготовленные ые изделия рассчитывается по формуле

 

,

 

где количество изготовленных изделий го типа.

Суммарная прибыль по изготовленным изделиям всех типов определится по формуле

 

.

 

Необходимо создать таблицу для расчета прибыли (рис. 128). Ввести в ячейки необходимые формулы и провести расчет себестоимости, отпускной цены, прибыли на одно изделие и суммарной прибыли по каждому изделию и по всем трем изделиям. Присвоить имена ячейкам В3, В4, В5, в которых размещены исходные данные, и ячейке В17, в которой размещена выходная величина – сумма прибыли по всем изделиям.

 

2. Создать следующие сценарии (рис. 129):

 

	Сценарии
Стоимость нормо-часа работы
Стоимость единицы материалов

 

Рис. 129

 

3. Получить отчет по сценарию в виде структуры с отражением результатов: сумма прибыли (ячейка В5).

4. Подобрать параметры стоимости нормо-часа работы при сумме прибыли по всем изделиям, равной 10000 руб.

Задание 3.

В примере из задания 2 занятия 2, использую процедуру подбора параметра, определить объем продаж, при котором прибыль равна нулю (определить точку безубыточности).

 

 

 

Поиск решения.

Задание 4. Расчет оптимальной реализации продукции.

1. Создать таблицу расчета реализации (рис. 130). Рассчитать Столбец Сумма как произведение Количества на Цену. В ячейке В8 подсчитать сумму по столбцу Количество.

 

 

Рис. 130

 

2.Установить целевую ячейку, изменяемые ячейки, ограничения (рис. 131).

 

 

Рис. 131

3. Выполнить поиск решения и получить отчет.

Результаты оптимизации (поиска решения) приведены на рис. 131а

 

 

Рис. 131а

 

 

Задание 5. Расчет оптимальной цены реализации продукции.

В примере из задания 2 занятия 1, ввести зависимость между объемом продаж и ценой изделия (такая зависимость называется функцией спроса). Она имеет вид

q=b-a*p, где a=20, b=30000 (см. рис. 131б)

 

 

 

 

Рис. 131 б.

 

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

 

 

Рис. 131 в.

 

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