Прибыль от реализации 1 т молока, кефира и сметаны равна соответственно 30, 22 и 136 тыс.руб.

Задача 4

Фирма переводит свой головной завод на производство определенного вида изделий, которые будут выпускаться в течение четырех месяцев. Величины спроса по месяцам составляют 100, 200, 180 и 300 изделий соответственно. В каждый месяц спрос можно удовлетворить за счет:

· запасов изделий, произведенных в прошлом месяце, сохраняющихся для реализации в будущем;

· производства изделий в течение текущего месяца;

· избытка производства в более поздние месяцы в счет невыполненных заказов.

Затраты на одно изделие в каждом месяце составляют 4 ден.ед. Изделие, произведенное для более поздней реализации, влечет за собой дополнительные издержки на хранение в 0,5 ден.ед. в месяц. Каждое изделие, выпускаемое в счет невыполненных заказов, облагается штрафом в размере 2 ден.ед.

Объем производства изделий меняется от месяца к месяцу в зависимости от выпуска других изделий. Предполагается выпуск по месяцам: 50, 180, 280 и 270 изделий.

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

ЛАБОРАТОРНАЯ РАБОТА 8. АНАЛИЗ «ЧТО-ЕСЛИ»

Подбор параметра

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

Содержание задания:

Известна сумма займа, срок кредитования и процентная ставка. Найти ежемесячный платеж (аннуитет). Используя Подбор параметра определить срок кредитования при различных значениях ежемесячного платежа.

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

1. По формуле, показанной на рисунке, рассчитайте в ячейке В4 размер ежемесячного платежа.

2. Выделите ячейку В4, на вкладке Данные в группе Работа с данными щелкните на пункте Анализ «что-если» и в выпадающем меню выберите Подбор параметра.

3. В диалоговом окне Подбор параметра в поле Установить в ячейке укажите В4, в поле Значение введите, например, 6000, в поле Изменяя значение в ячейке задайте адрес В2. Подтвердите, нажав ОК. В диалоговом окне Результат подбора параметра нажмите ОК.

4. По аналогии найдите срок кредитования при сумме ежемесячного платежа 9000, 12000 и 15000 руб.

Задача 2

Используя Подбор параметра, определите:

1) объем продаж продукции, при котором фирма достигнет уровня безубыточности (прибыль равна 0);

2) объем продаж, при котором фирма получит прибыль 25000 руб.;

3) объем продаж, при котором фирма получит прибыль 50000 руб.

Объем продаж
Цена за единицу
Переменные издержки на единицу продукции	0,45
Постоянные издержки
Переменные издержки	=B3*B1
Выручка от реализации	=B2*B1
Прибыль	=B6-B4-B5

Таблицы подстановки

2.1 Таблица подстановки с одним входом

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

Содержание задания:

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

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

1. По формулам, показанным на рисунке, рассчитайте размер ссуды, месячную выплату по кредиту, общую сумму выплат и комиссионных.

2. В ячейках F2, G2, H2, I11 введите адреса ячеек с формулами расчета нужных показателей.

3. Выделите диапазон ячеек Е2:I11, на вкладке Данные в группе Работа с данными щелкните на пункте Анализ «что-если» и в выпадающем меню выберите Таблица данных.

4. В открывшемся диалоговом окне Таблица данных в поле Подставлять значения по строкам в укажите адрес ячейки В5. Щелкните по кнопке ОК.

2.2 Таблица подстановки с двумя входами

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

Содержание задания:

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

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

1. Скопируйте исходные данные на чистый лист. Оформите таблицу для вариантных расчетов. В ячейке Е2 укажите адрес ячейки с формулой расчета месячной выплаты.

2. Выделите диапазон ячеек Е2:J11, на вкладке Данные в группе Работа с данными щелкните на пункте Анализ «что-если» и в выпадающем меню выберите Таблица данных.

3. В открывшемся диалоговом окне Таблица данных в поле Подставлять значения по столбцам в введите адрес ячейки В4, а в поле Подставлять значения по строкам в укажите адрес ячейки В5. Щелкните по кнопке ОК.

Сценарии

Сценарий — это набор значений, которые в Excel сохраняются

и могут автоматически подставляться в лист.

Содержание задания:

Автозавод планирует запустить в производство новую бюджетную модель автомобиля. Необходимо составить пятилетний план по трем вариантам развития событий (сценариям).

Сценарий	Объем продаж в 1-й год	Рост продаж	Цена
Лучший		20%	$10000
Наиболее вероятный		10%	$7500
Худший		5%	$6500

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

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

	А	В	С	D	E	F
	Исходные данные
	Налог на прибыль	20%
	Продажи в 1-й год
	Рост продаж	20%
	Цена в первый 1-й год	$10000
	Себестоимость в 1-й год	$6000
	Рост себестоимости	5%
	Рост цены	3%
	Годы	1-й	2-й	3-й	4-й	5-й
	Продано единиц	=B3	=B11+B11*$B$4
	Цена единицы	=B5	=B12+B12*$B$8
	Себестоимость единицы	= B6	=B13+B13*$B$7
	Выручка	=B11*B12	=C11*C12
	Издержки	=B13*B11	=C13*C11
	Прибыль до вычета налога	=B14-B15	=C14-C15
	Налог	=B16*$B$2	=C16*$B$2
	Чистая прибыль	=B16-B17	=C16-C17

2. Перейдите на вкладку Данные, в группе команд Работа с данными щелкните на пункте Анализ «что-если» и в выпадающем меню выберите Диспетчер сценариев. В диалоговом окне Диспетчер сценариев щелкните по кнопке Добавить. В диалоговом окне Добавление сценария введите имя Лучший и укажите диапазон изменяемых ячеек В3:В5, щелкните по кнопке ОК. В диалоговом окне Значение ячеек сценария введите В3 = 20000, В4 = 20%, В5 = 10000. Щелкните по кнопке Добавить, по аналогии введите еще два сценария, после вода последнего нажмите ОК.

3. В диалоговом окне Диспетчер сценариев щелкните по кнопке Отчет и выберите вариант Структура, укажите ячейки результата В18:F18. Подтвердите нажатием ОК. Будет добавлен отдельный рабочий лист Структура сценария.

Задача 3

Используя исходные данные задачи 2, определите, как повлияет на выручку от реализации и прибыль изменение цены единицы продукции (от 2 до 4 руб. с шагом 0,25 руб.). Планируемый объем продаж составляет 40000 ед.

(Совет: воспользуйтесь таблицей подстановки с одним входом).

Задача 4

Используя исходные данные задачи 2, определите, как будет варьироваться прибыль при изменении цены (от 1,5 до 5 руб. с шагом 0,5 руб.) и переменных издержек на единицу продукции (от 0,3 до 0,6 руб. с шагом 0,05 руб.). Планируемый объем продаж составляет 50000 ед.

(Совет: воспользуйтесь таблицей подстановки с двумя входами).

Задача 5

Используя исходные данные задачи 2, создайте отчет по сценариям, где будет отражена прибыль.

Сценарий	Цена	Переменные издержки	Постоянные издержки
Высокие затраты и высокая цена
Средние затраты и средняя цена		0,75
Низкие затраты и низкая цена	2,5	0,4