Описание расчета инвариантной сметы расходов с помощью электронных таблиц Excel

Задание.

• 1. Составить расчет инвариантной сметы расходов на ремонт квартиры.
• 2. Построить диаграмму структуры расходов по смете.
• 3. Разработать два сценария для расчета расходов при изменении цен на материалы и расценок на выполнение работ.
• 4. Подобрать параметры для расчета возможных размеров цен при заданной величине расходов.
• 5. Составить план погашения кредита на расходы по смете и рассчитать будущую стоимость расходов.

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

• · объект ремонта;
• · работы и расценки;
• · цены материалов и нормы расхода.

Состав исходных данных, использованных в примере, приведен ниже.

Рис. 12.

Расчет сметы состоит из трех расчетов:

• · Расчет объема работ;
• · Расчет потребности и стоимости материалов;
• · Расчет стоимости работ.

Ниже приводятся эти расчеты (См. Рис. 13).

Рис. 13.

Объем работ рассчитывается инвариантно, т. е. при помощи функции «ЕСЛИ» в зависимости от количества комнат:

=ЕСЛИ (B26=1;B4*C4;B4*C4+B5*C5);

=ЕСЛИ (B27=1;2*d4*(B4+С4);2*(B4+C4)*D4+2*(B5+C5)*D5).

Расчет потребности и стоимости материалов рассчитывается по следующим формулам (См. Рис. 14):

Рис. 14.

Расчет стоимости работ приведен ниже (См. Рис. 15).

Рис. 15.

Стоимость ремонта рассчитывается в зависимости от вида ремонта: 1 — стандартный ремонт, 2 — евроремонт.

Формулы расчета приведены ниже (См. Рис. 16).

Рис. 16.

Построение диаграммы структуры расходов по смете Диаграмма должна отражать структуру расходов по смете. Для этих целей применяется круговая диаграмма, приведенная ниже (См. Рис. 17).

Разработка сценариев для расчета расходов при изменении цен на материалы и расценок на выполнение работ. В приведенных двух сценариях показано как при изменении цен на материалы изменяются расходы по смете (См. Рис. 18).

Рис. 17 Структура затрат на ремонт квартиры

Рис. 18.

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

При выполнении подбора параметров устанавливаем расходы по смете в сумме 40 000 и задаем подобрать размер цены на обои. (См. Рис. 19 и Рис. 20).

Рис. 19.

Рис. 20.

В результате подобрана цена в размере 12.6 руб. за 1 м обоев (См. Рис. 21.).

Рис. 21.

Составление плана погашения кредита на расходы по смете и расчет будущих расходов.

Взят кредит на ремонт квартиры в сумме 50 000 тыс. руб. на один год под 20% годовых с ежеквартальными платежами. Требуется рассчитать сумму ежеквартальных выплат, в том числе по процентам и основному долгу, т. е. составить план погашения кредита с использованием финансовых функций, ППЛАТ, ПЛПРОЦ, ОСНПЛАТ.

Рис. 22 План погашения кредита

Финансовая функция БЗ используется для расчета будущей стоимости вклада. На ремонт квартиры в настоящее время согласно смете требуется 50 тыс. руб. Через три года стоимость ремонта квартиры при ставки 20% и ежеквартальном начислении процентов будет 89.8 тыс. руб. Окно расчета функции БЗ приведено ниже (См. Рис. 23.).

Рис. 23.