ЗАДАНИЕ 5

ФИНАНСОВОЕ ПЛАНИРОВАНИЕ (ex_5_Name.xls)

Ключевые понятия: финансовые функции Excel, копирование данных и формул, выбор из списка, форматирование таблиц, суммирование по условию.

ЗАДАЧА

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

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

Для каждого вида продукции в таблице констант на первом листе задать следующие значения (использовать датчик случайных чисел

смотри Задание 4)

Стоимость основных средств (ОС)	[100 000; 500 000]
Срок амортизации основных фондов	[5;15]
Остаточная стоимость основных средств	(15% от ОС)
Начальный оборотный капитал	(от 0,5 до 1,5 от ОС)
Рентабельность оборотных активов	[0,1; 0,5]
Коэффициент расширения производства	[0,2; 0,4]

Расчѐты по планированию функционирования предприятия представить на новом листе Финансовый план. Таблица должна содержать 12 блоков данных аналогичных Таблице констант для каждого месяца. Слева добавляется столбец месяца. В итоге получится таблица, в которой для каждого вида продукции отводится отдельная строка, а в столбцах расположены следующие данные:

ИСХОДНЫЕ ЗНАЧЕНИЯ: МОДЕЛЬНЫЕ СЛУЧАЙНЫЕ ЗНАЧЕНИЯ:

Месяц		Город	Филиал	Вид продукции	Рентабельность оборотных активов Роа	Коэффициент расширения производства Крп	Стоимость основных средств ОС	Срок амортизации основных фондов Срок	Остаточная стоимость основных фондов Сост	Оборотный капитал ОбК (копируется только для первого месяца)

Добавьте в полученную таблицу справа новые столбцы
МОДЕЛЬНЫЕ РАСЧЕТНЫЕ ЗНАЧЕНИЯ:
	Потери от амортизации Ам					Оборотный капитал ОбК
	Чистая прибыль ЧПр					Планируемая прибыль ПлПр

ФОРМУЛЫ РАСЧЕТА МОДЕЛЬНЫХ РАСЧЕТНЫХ ЗНАЧЕНИЙ ДЛЯ ПОСЛЕДУЮЩИХ ПЕРИОДОВ t текущий месяц расчета [2; 12]:

ОбК t = ОбК(t-1) + ЧПр(t-1) * Крп ЧПр = ОбК * Роа Ам = АСЧ(ОС; Сост; Срок; месяц)

ПлПр = ЧПр * (1 - Крп) - Ам

В последний месяц года

ПлПр = ЧПр - Ам

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

Отформатировать информацию так, чтобы она хорошо читалась.

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

Используя Панель инструментов Рисование, нанести на диа-

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

В таблице начальных констант с помощью функции =СЧЕТЕСЛИ(диапазон; критерий) определить:

количество видов продукции, производящихся в каждом городе;

Подсчитать сколько раз производится каждый вид продукции в различных филиалах.

Число производств по видам продукции

	вид продукции			число
	вид продукции			число
	Молоко			СЧЕТЕСЛИ
	Ряженка
	Йогурт
	Сгущёнка
	Сметана
	Кефир
	Глаз. сырки