Выполнение задания
Задание 1
Цель — ознакомление с входной и выходной информацией для моделирования эффективности кредитования производства новой продукции.
В копии заполненного шаблона L1 заполним таблицы входной информацией, расположенные на листе Входная информация, данными из таблиц соответственно варианту №6
Ознакомимся с выходной информацией, расположенной на листах Калькуляция (по кредиту) и Эффективность кредита.
Задание 2
Цель — расчет формирования цены продажи и калькулирование себестоимости продукции.
Введем адреса необходимых входных данных для расчета цены и калькуляции себестоимости единицы продукции Изделия 1 и Изделия 2.
Для ввода данных Расход на 1 шт., коэффициенты - таблица 4 «Сводные нормы расхода материалов и вес изделий»;
Для ввода данных Цены основных материалов - таблица 5 Прейскурант цен на материалы;
Для получения показателя Стоимость сырья и основных материалов Изделия используем формулу в виде: = Расход на 1 шт., коэффициенты * Стоимость, руб.
Для расчета расхода вспомогательных материалов возьмем соответствующие адреса входной информации из таблицы 4 «Сводные нормы расхода материалов и вес изделий» и таблицы 5 «Прейскурант цен на материалы»;
Для расчета расхода электроэнергии и других видов энергии (сжатый воздух) возьмем адреса входной информации из таблицы 6 «Сводные нормы расхода энергоресурсов в натуральном выражении» и таблицы 7 «Прейскурант цен на энергоресурсы»;
Данные по основной заработной плате содержатся в таблице 8 «Сводные расценки на изделие»;
Дополнительная заработная плата составляет указанный в таблице 9 «Расчет цены и калькуляция себестоимости единицы продукции» процент от основной заработной платы (графы 4, 7):
формула для изделия 1: =F10*D11; формула для изделия 2: =G11*I10
Отчисления от зарплаты рассчитываются по формулам:
формула для изделия 1: = (F10 +F11)* D12; формула для изделия 2: =(I10+I11)*G12
Амортизационные отчисления для изделий рассчитываются по следующим формулам:
формула для изделия 1: ='Входная информация'!C21*Калькул_по_кредиту!D15*'Входная информация'!C32/('Входная информация'!F10*'Входная информация'!C32+'Входная информация'!F11*'Входная информация'!D32)
формула для изделия 2: ='Входная информация'!C21*Калькул_по_кредиту!G15*'Входная информация'!D32/('Входная информация'!C10*'Входная информация'!C32+'Входная информация'!C11*'Входная информация'!D32)
Прочие расходы на содержание и эксплуатацию оборудования, цеховые и общезаводские расходы составляют указанные в таблице 9 Расчет цены и калькуляция себестоимости единицы продукции проценты от основной заработной платы (графы 4, 7);
формула для изделия 1: =D16*F10; формула для изделия 2: =G16*I10
Производственная себестоимость представляет сумму статей затрат:
формула для изделия 1: =F6+F7+F8+F9+F10+F11+F12+F15+F16+F17+F18
формула для изделия 2: =I6+I7+I8+I9+I10+I11+I12+I15+I16+I17+I18
Внепроизводственные расходы:
формула для изделия 1: =D20*F19; формула для изделия 2: =G20*I19
Полная себестоимость представляет сумму производственной себестоимости и внепроизводственных расходов:
формула для изделия 1: =F19+F20; формула для изделия 2: =I19+I20
Цена предприятия (расчетная) определяется по следующей формуле:
формула для изделия 1: ='Входная информация'!C4/(1+20/100)
формула для изделия 2: ='Входная информация'!C5/(1+20/100)
Прибыль (расчетная):
формула для изделия 1: =F22-F21; формула для изделия 2: =I22+I21
Цена предприятия (установленная) определяется следующим образом:
формула для изделия 1: =F21+F23*0,98; формула для изделия 2: =I21+I23*0,98
Прибыль (ожидаемая):
формула для изделия 1: =F24-F21; формула для изделия 2: =I24-I21
Налог на добавленную стоимость (НДС):
формула для изделия 1: =F24*D26; формула для изделия 2: =I24*G26
Цена продажи (установленная):
формула для изделия 1: =F24+F26; формула для изделия 2: =I24+I26
Оценим конкурентоспособность предприятия согласно следующему условию: если цена продажи (установленная) ниже или равна рыночной цене, то продукция — конкурентная, в противном случае — неконкурентная.
формула для изделия 1: =ЕСЛИ(F27<='Входная информация'!C4;"конкурентная"; "неконкурентная");
формула для изделия 2: =ЕСЛИ(I27<='Входная информация'!C5;"конкурентная"; "неконкурентная")
Задание 3
Цель — Расчет эффективности получения кредита
Сумма кредита (кредитная линия) берется равной Стоимости оборудования:
='Входная информация'!C21
По годам выпуска продукции рассчитаем (для 1го года):
Доход от продажи (без НДС) ='Входная информация'!C10*Калькул_по_кредиту!F24 +'Входная информация'!C11*Калькул_по_кредиту!I24
Производственные издержки ='Входная информация'!C10*Калькул_по_кредиту!F21+ 'Входная информация'!C11*Калькул_по_кредиту!I21
Валовая прибыль =C5-C6
Налог на прибыль =C7*B8
Чистая прибыль =C7-C8
Амортизационные отчисления ='Входная информация'!C21*Калькул_по_кредиту!D15
Свободных финансовые средства текущего года: =ЕСЛИ(C9+C10+B17<0;0;C9+C10+B17)
Плата за кредит =$B$13*B16
Общая сумма кредита и платы за кредит текущего года: =C12+C13+B16
Остаток общей суммы кредита и платы за кредит текущего года:
=ЕСЛИ(C15-C11<0;0;C15-C11)
Остаток свободных финансовых средств текущего года:
=ЕСЛИ(C11-C15<0;0;C11-C15)
Срок окупаемости =ЕСЛИ(C15/C11>1;B18+1;B18+(C15/C11))
Оценка эффективности получения кредита (по сроку окупаемости)
=ЕСЛИ(F18<=B14;"Кредит целесообразен";"Кредит не целесообразен")
Задание 4
Цель — расчет лизинговых платежей в условиях использования финансового лизинга с фиксированной общей суммой и применением механизма ускоренной амортизации.
Рассчитаем по годам выпуска продукции (для 1го года):
Амортизационные отчисления за год =B5*C5*D5
Балансовая стоимость оборудования на конец платежа =B5-E5
Среднегодовая стоимость оборудования =(B5+F5)/2
Плата за кредит =G5*H5
Комиссионное вознаграждение =G5*J5
Лизинговый платеж (без НДС) =E5+I5+K5
Лизинговый платеж (без НДС) за весь период лизинга =L5+L6+L7+L8
Размер ежемесячной выплаты лизингового платежа =L9/4/12
Задание 5
Цель — формирование цены продажи и калькулирования себестоимости продукции на основе использования данных по лизингу.
Рассчитаем величину Лизинговых отчислений:
формула для изделия 1 ='Лизинговый платеж'!M9*12*'Входная информация'!C32/ ('Входная информация'!C10 *'Входная информация'!C32+'Входная информация'!C11 *'Входная информация'!D32)
формула для изделия 2 ='Лизинговый платеж'!M9*12*'Входная информация'!D32/ ('Входная информация'!C10*'Входная информация'!C32+'Входная информация'!C11 *'Входная информация'!D32)
Задание 6
Цель — расчет эффективности лизинга.
По годам выпуска продукции рассчитаем (для 1го года):
Доход от продажи без НДС ='Входная информация'!$C$10*'Калькуляция по лизингу'!$F$24+'Входная информация'!$C$11*'Калькуляция по лизингу'!$I$24
Производственные издержки ='Входная информация'!$C$10*'Калькуляция по лизингу'!$F$21+'Входная информация'!$C$11*'Калькуляция по лизингу'!$I$21
Валовая прибыль =C5-C6
Налог на прибыль =C7*B8
Чистая прибыль =C7-C8
Чистая прибыль нарастающим итогом =C9 (сумма чистой прибыли за период лизинга)
Сумма эффекта («+» для лизинга, «-» для кредита) =C10-C11
Оценка эффективности лизинга =ЕСЛИ(C10-C11>0;"лизинг эффективнее";"кредит эффективнее")
Проследим, как изменится эффективность лизинга в условиях удорожания стоимости оборудования, например, Печи-миксера до 1010 тыс. руб. и цены на основной материал до 9,5 руб./кг. Для этого внесем необходимые изменения в таблицы на листе Входная информация.
Сумма прибыли уменьшилась на 11 % (с 5709,07 до 5051,65), но лизинг остался эффективнее кредита.