Дейчтвие 3-е. Проблема учета постоянных издержек 3 страница

Для расчета суммарного количества складских площадей в марте нужно сложить все площади, нанятые в марте, и добавить площади, нанятые в феврале на срок два месяца и более и нанятые в январе на срок три месяца и более. И т.д. вплоть до июня, в котором мы имеем все площади, нанятые в июне на месяц, плюс нанятые в мае на два месяца, плюс нанятые в апреле на три месяца, …, плюс нанятые в январе на шесть месяцев.

Эти количества имеющихся в каждом месяце площадей должны быть не меньше плановой потребности (ячейки K7:K12).

 

A B C D E F G H I J K

 

					Стоимость	аренды
	На сколько		на 1	на 2	на 3	на 4	на 5	на 6
	месяцев			12.8

5 Возможный план Переменные: сколько тыс. м2 арендовать и на сколько месяцев

январь =СУММ(C7:H7)     февраль =СУММ(C8:G8;D7:H7)     март =СУММ(C9:F9;D8:G8;E7:H7)     апрель =СУММ(C10:E10;D9:F9;E8:G8;F7:H7)     май =СУММ(C11:D11;D10:E10;E9:F9;F8:G8;G7:H7)     июнь =СУММ(C12;D11;E10;F9;G8;H7)             январь =СУММПРОИЗВ($C$3:$H$3;C7:H7)   тыс.     февраль =СУММПРОИЗВ($C$3:$H$3;C8:H8)   тыс.   март =СУММПРОИЗВ($C$3:$H$3;C9:H9)   тыс.   апрель =СУММПРОИЗВ($C$3:$H$3;C10:H10)       май =СУММПРОИЗВ($C$3:$H$3;C11:H11)       июнь =СУММПРОИЗВ($C$3:$H$3;C12:H12)       С мин.= =СУММ(C14:C19)

6 аренды в: на 1 на 2 на 3 на 4 на 5 на 6 Ограничения:

 

13

 

Рис. 44

 

 

В ячейках C14:C19 (Рис. 44) подсчитаем сколько денег будет затрачено на аренду в каждом месяце. При принятой схеме оплаты для этого нужно просто умножить нанятые в данном месяце площади на цены аренды и сложить.

В последней ячейке столбца (C20) все месячные выплаты просуммированы. Эта сумма и будет целевой функцией задачи оптимизации.

Кроме упомянутого выше ограничения на количество необходимых площадей в задаче есть и другие ограничения. Они касаются максимальных денежных расходов в январе, феврале и марте. Для того, чтобы можно было удобно задать соответствующие ограничения в Поиске решения максимальная величина затрат в каждый из этих трех месяцев также записана в таблице в ячейках H14:H16.

Таким образом, все необходимое для постановки задачи Поиску решения

сделано, остается поставить задачу и выполнить оптимизацию.

Однако, до того, как найти оптимальное решение, неплохо было бы попробовать подыскать план аренды «вручную», для сохранения интриги, так сказать, и чистоты эксперимента.

Попробуем первое очевидное решение – нанимать каждый месяц ровно столько, сколько нужно в этом месяце на срок в один месяц (Рис. 45).

 

	A	B	C	D	E	F	G	H	I	J	K
	Возможный план	Переменные: сколько тыс. м2 арендовать и на сколько месяцев
	аренды в:		на 1	на 2	на 3	на 4	на 5	на 6		Ограничения:
	январь		30.00
	февраль		20.00
	март		40.00
	апрель		10.00
	май		50.00
	июнь		20.00
	январь		210.0	тыс.	Плата за аренду				тыс.
	февраль		140.0	тыс.						тыс.
	март		280.0	тыс.						тыс.
	апрель		70.0	тыс.
	май		350.0	тыс.
	июнь		140.0	тыс.
	С мин.=	1 190.0	Целевая функция:

Рис. 45

 

Итого, общая величина затрат 1190 тыс. Пока нам не с чем сравнивать это число. Однако, можно отметить, что при таком плане аренды мы не укладываемся в лимит расходов в марте месяце.

Можно даже не пробовать аренду 50 тыс. м2 на шесть месяцев в январе.

Очевидно, что расходы превысят 1500 тыс., притом, что лимит расходов в январе

400 тыс.

Так как плата за месяц аренды при больших сроках меньше, очевидно, что следует максимально использовать такую скидку. Наименьшие потребности в площадях составляют 10 тыс. м2 (в апреле). В связи с этим можно нанять в январе

10 тыс. м2 на шесть месяцев и еще 20 на один месяц. А в остальные месяцы до- нанимать к имеющимся 10 тыс. м2 столько, сколько не хватает до плановой потребности.

 

 

	A	B	C	D	E	F	G	H	I	J	K
	Возможный план	Переменные: сколько тыс. м2 арендовать и на сколько месяцев
	аренды в:		на 1	на 2	на 3	на 4	на 5	на 6		Ограничения:
	январь		20.00					10.00
	февраль		10.00
	март		30.00
	апрель		0.00
	май		40.00
	июнь		10.00
	январь		452.0	тыс.	Плата за аренду				тыс.
	февраль		70.0	тыс.						тыс.
	март		210.0	тыс.						тыс.
	апрель		-	тыс.
	май		280.0	тыс.
	июнь		70.0	тыс.
	С мин.=	1 082.0	Целевая функция:

Рис. 46

 

 

Такой план приведен на Рис. 46. Расходы снизились до 1082 тыс. Однако есть перерасход средств в январе и марте.

A B C D E F G H I J K   Возможный план Переменные: сколько тыс. м2 арендовать и на сколько месяцев     аренды в:   на 1 на 2 на 3 на 4 на 5 на 6   Ограничения:   январь   10.00         20.00         февраль                       март   20.00                   апрель                       май   30.00                   июнь                                               январь   694.0 тыс. Плата за аренду       тыс.     февраль   - тыс.           тыс.     март   140.0 тыс.           тыс.     апрель   - тыс.                 май   210.0 тыс.                 июнь   - тыс.                 С мин.= 1 044.0 Целевая функция:

Если не задаваться целью не иметь лишних площадей, можно обратить внимание на то, что в феврале и июне нужно 20 тыс. м2, Таким образом, сняв в январе 20 тыс. м2 на все шесть месяцев можно полностью покрыть потребности этих месяцев и заодно сильно сократить затраты на ежемесячный найм площадей. Правда в апреле 10 тыс. м2 будут простаивать, но проверить такой план не мешает. Результат расчета приведен на следующем рисунке (Рис. 47).

 

 

Рис. 47

 

Отличный по деньгам план – 1044 тыс. долларов! Действительно, выгодно иметь некоторое количество лишних площадей, так как скидки за длительный срок аренды перекрывают расходы от найма лишних площадей. К сожалению, применение такого плана невозможно, из-за перерасхода средств в январе – у нас просто нет 694 тыс. в этом месяце.

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

Перечислим сначала все требующиеся установки Поиска решения.

Целевая ячейка – C20, суммарные затраты.

Цель – минимум расходов.

Изменяемые ячейки – C7:H12. Можно, конечно, выделить шесть диапазонов ячеек: C7:H7, C8:G8, C9:F9, C10:E10, C11:D11 и С12 (для этого при указании ячеек в Поиске решения надо удерживать нажатой клавишу CTRL), но это необязательно. Во-первых, экономия переменных тут не требуется. А во- вторых, Поиск решения и сам, без дополнительных ограничений оставит лишние ячейки нулевыми. Ведь при подсчете снятой площади (ячейки J7:J12) лишние ячейки не используются, зато они учитываются при расчете оплаты (ячейки C14:C19). Так что при минимизации расходов лишние переменные автоматически обнулятся.

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

1. Суммарные арендуемые площади не меньше, чем ежемесячные потребности – J7:J12>=K7:K12.

2. Суммарные затраты в первые три месяца не должны превышать 400,

200 и 200 тыс. соответственно – C14:C16<=H14:H16.

Все, можно делать расчет.

 

A B C D E F G H I J K   Возможный план Переменные: сколько тыс. м2 арендовать и на сколько месяцев     аренды в:   на 1 на 2 на 3 на 4 на 5 на 6   Ограничения:   январь   22.15 0.00 0.00 0.00 0.00 7.85         февраль   0.00 10.00 0.00 0.00 2.15 0.00         март   20.00 0.00 0.00 0.00 0.00 0.00         апрель   0.00 0.00 0.00 0.00 0.00 0.00         май   30.00 10.00 0.00 0.00 0.00 0.00         июнь   0.00 0.00 0.00 0.00 0.00 0.00                                 январь   400.0 тыс. Плата за аренду       тыс.     февраль   187.1 тыс.           тыс.     март   140.0 тыс.           тыс.     апрель   - тыс.                 май   338.0 тыс.                 июнь   - тыс.                 С мин.= 1 065.1 Целевая функция:

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

 

 

Рис. 48

 

В общем, это похоже на два последних наших плана. Они только слегка скорректированы для учета ограничений на расход средств. Тем не менее, теперь мы можем быть уверены в том, что лучшего плана аренды не существует.

A B C D E F G H I J K   аренды в:   на 1 на 2 на 3 на 4 на 5 на 6   Ограничения:   январь   10.00 0.00 0.00 0.00 0.00 20.00         февраль   0.00 0.00 0.00 0.00 0.00 0.00         март   20.00 0.00 0.00 0.00 0.00 0.00         апрель   0.00 0.00 0.00 0.00 0.00 0.00         май   30.00 0.00 0.00 0.00 0.00 0.00         июнь   0.00 0.00 0.00 0.00 0.00 0.00                                 январь   694.0 тыс. Плата за аренду   1 000   тыс.     февраль   - тыс.       1 000   тыс.     март   140.0 тыс.       1 000   тыс.     апрель   - тыс.                 май   210.0 тыс.                 июнь   - тыс.                 С мин.= 1 044.0 Целевая функция:

Здесь кстати проверить, каковы будут издержки, если не учитывать лимит средств. Для этого можно убрать соответствующее ограничение из Поиска решения, а лучше просто заменить верхние границы расходов большими числами (Рис. 49).

 

 

Рис. 49

 

 

Как вы можете убедиться, этот план мы нашли раньше (Рис. 47) и отметили его, как очень хороший, но не укладывающийся в лимит расходов. Видимо, именно этот план имеет смысл реализовывать за счет кредита.

Давайте рассчитаем финансовые потоки при взятом кредите (Рис. 50).

Понятно, что нам необходимо взять 294 тыс. (694-400). Тогда в январе долг составит 294 тыс. и за месяц набежит 14.7 тыс. долларов по процентам (5%*294).

A B C D E F G H I J             Долг Проценты       январь   694.0 тыс.   Плата за аренду 294.0 14.7 1 000   тыс.   февраль   - тыс. 108.7 5.4 1 000   тыс.   март   140.0 тыс. 54.1 2.7 1 000   тыс.   апрель   - тыс.   -           май   210.0 тыс.               июнь   - тыс.               С мин.= 1 066.8 Целевая функция: 22.8

В феврале у нас расходов нет, но зато есть 200 тыс., которые мы можем направить на погашение кредита. Т.о. наш долг в феврале уменьшится до 108.7 тыс. (294+14.7-200). Однако на эту сумму снова будут начислены проценты и она возрастет за февраль на 5.4 тыс. (5%*108.7).