Как сделать тарифный калькулятор в excel?

Первый урок по калькуляторам и расчетам в Microsoft Excel будет посвящен созданию расчета страхового платежа по ОСАГО.  Данный калькулятор может быть использован страховыми агентами и сотрудниками продажного подразделения.

Шаг 1. Создаем базу

Шаг 2. Создаем таблицу с параметрами ОСАГО

Создаем новую страницу «Расчет ОСАГО», на которой создаем таблицу с параметрами страхования:

• Место регистрации автомобиля;
• Тип транспортного средства;
• Стаж вождения;
• Статус страхователя;
• Срок страхования.

Шаг 3. Создаем выпадающий список значений параметра

Создаем значения в виде выпадающего списка для каждого параметра:

1. Переходим на вкладку «Данные»;
2. Устанавливаем курсор в месте, где должен быть выпадающий список (C2);
3. Выбираем в вкладке «Работа с данными» функцию «Проверка данных»;
4. В открывшемся окне на вкладке «Параметры», выбираем «Тип данных» «Список»;
5. В строке «Источник» указываем диапазон данных (Все условия параметра);
6. Нажимаем «ОК»

Итак, у нас создана таблица с параметрами и условиями страхования ОСАГО.

Шаг 4. Создаем таблицу критериев для отбора коэффициента значения

Для того чтобы извлекать коэффициенты, необходимые для расчета страхового платежа ОСАГО, по каждому параметру с выбранным значением, будем использовать функцию Excel «БИЗВЛЕЧЬ». Для этого  подготовим массив с критериями отбора отдельной таблицей, где сверху будет указан параметр, а снизу будет подтягиваться выбранное значение.

Значение параметра будет подтягиваться с таблицы на странице «Расчет ОСАГО» из выпадающего списка. Значение подтягивается простой функцией:

• Устанавливаем курсор, где необходимо установить значение;
• Нажимаем клавишу «=» на клавиатуре;
• Указываем путь к значению (выпадающий список).

Таким образом, заполняем всю таблицу с критериями.

Шаг 5. Извлекаем коэффициенты значений

Для того чтобы извлечь коэффициент для каждого значения, будем использовать функцию «БИЗВЛЕЧЬ».

1. Устанавливаем курсор на ячейку в которое хотим извлечь коэффициент;
2. Нажимаем кнопку «Вставить функцию»;
3. Выбираем Категорию «Работа с базой данных»;
4. Выбираем функцию «БИЗВЛЕЧЬ»;
5. В открывшемся окне:
   1. В строку «База_данных» вносим диапазон таблицы, вместе с заголовками, из которой хотим извлечь коэффициент;
   2. В строке «Поле» указываем ячейку заголовка таблицы по которому будет производится поиск. В нашем случае «Коэффициент»;
   3. В строку «Критерий» указываем критерий поиска (Заголовок столбца и значение). Например, Заголовок столбца «Место регистрации автомобиля», а значение «Житомир».

Шаг 6. Расчет страхового платежа

После того, как были извлечены для каждого значения коэффициент, рассчитываем страховой платеж по ОСАГО путем умножения базового страхового платежа 180 грн. на все коэффициенты.

Шаг 7. Оформление калькулятора ОСАГО

Скрываем все не нужные столбцы и строки, а также «листы», чтобы не вводили в заблуждение. Оформляем таблицу по собственному желанию и вкусу. Выделяя заголовки, параметры и значения шрифтом или цветом.

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

Расчет страхового платежа ОСАГО готов к использованию. Рассчитать ОСАГО становится легко и просто за какие-то полминуты драгоценного времени.

Источник: https://wpcalc.com/kalkulyator-rascheta-straxovogo-tarifa-po-osago/

Как создать кредитный калькулятор в Excel?

     Добрый день уважаемый пользователь!

     Сегодня я хотел бы поговорить о таком необходимом зле, как кредит.  Почему зло, вы и так знаете, особенно это касается потребительского кредитования, когда за вещь вы переплачиваете в 2-3 раза больше ее реальной цены.

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

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

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

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

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

Ну а потом на протяжении 2 недель полностью закрыл долг, не отлаживая его в долгий ящик.

     Вот исходя из этих соображений и всё же возникновению необходимости получения кредита вами или вашими близкими, необходимо, я бы сказал желательно, перед путешествием в банк прикинуть ориентировочно сумму, сроки переплаты и т.п… После того как вы прочувствуете цифры вы или будете готовы оформить кредит или попросту откажетесь от него. И в этом вопросе вам очень поможет Microsoft Excel.

     Рассмотрим три самых популярных варианта использования кредитного калькулятора в Excel:

Кредитный калькулятор для расчёта простых кредитов

     Начнём с простого варианта, быстро прикинем, сколько нам нужно ежемесячно оплачивать по аннуитетному кредиту, это когда выплаты делают одинаковыми суммами, как в большинстве случаев. Это можно произвести одной функцией Excel и несколькими простыми формулами.

Для получения результата в Excel существует функция ПЛТ в разделе «Финансовые».      Указываем, в какую ячейку нужен результат, вызываем «Мастер функций» ищем функцию ПЛТ, нажимаем кнопочку «ОК» и в окне мастера вводим необходимые аргументы для нашего расчёта, формула получается следующего вида:

             =ПЛТ(B5/12;B6;B4;0;0), где:

• Ставка (B5/12) – является аргументом, указывающим на процентную ставку по взятому кредиту в разрезе периодов выплат, в нашем случае это месяцы. Если ставка по кредиту в год 18%, то за один месяц будет составлять 1,5%;
• Кпер (B6) – аргумент, указывающий на количество периодов, то есть, на сколько месяцев взят кредит;
• Бс (B4) – указываем, какую сумму кредита будем рассчитывать;
• Пс (0) – это финишная пряма, какой итог кредита должен быть в конце, скорее всего это будет 0, что означает, что вы никому и ничего не должны;
• Тип (0) – аргумент необходимый для учёта выплат каждый месяц. Если равно 1 – это учитываем выплаты к началу месяца, если 0 – то учитываем на конец. В постсоветском пространстве большинство банков используют последний вариант, а значит вводим 0.

      Кроме этого, необходимо рассчитать, сколько составит общая сумма выплаты, и какая переплата получится, когда вы вернёте деньги банку. Это легко осуществить при помощи простых формул.      Теперь давайте немного улучшим и детализируем наш отчёт с помощью функции ОСПЛТ, которая определяет часть основного платежа по телу кредита и функции ПРПЛТ, которая посчитает всё, что касается процентов банку за использование кредита. Видоизменим ваш расчёт следующей таблицей:

     Теперь в поле «Тело кредита» в ячейку Е2 вводим формулу функции ОСПЛТ следующего вида:

            =ОСПЛТ($B$4/12;D2;$B$5;$B$3;0)

   Как видите, ее орфография практически аналогична функции ПЛТ, добавился только аргумент «Период», который указывает на номер текущего месяца, и дополнительно рассматривать ее я не буду.

Единственное, на что обращу ваше внимание, это то, что формула будет растянута на диапазон, а значит, аргументы необходимо закрепить абсолютными ссылками.      Следующим шагом для столбика «Проценты» будем использовать возможности функции ПРПЛТ. Вводится она аналогично вышеописанной и с теми же условиями и будет иметь такой вид:

             =ПРПЛТ($B$4/12;D2;$B$5;$B$3;0)      Теперь в оставшиеся столбики будем вводить простые формулы, для получения суммы выплаты нам нужна формула: =E2+F2, а для определения суммы остатка кредита используем формулу: =$B$3+СУММ($E$2:E2).     При необходимости, возможно, немножко улучшить и автоматизировать ваш кредитный калькулятор в Excel для уменьшения количества ошибок.

•       Для начала пропишем формулу в ячейку D3 для того чтобы она подстраивала и отслеживала срок кредита:
•           =ЕСЛИ(D2>=$B$5;»«;D2+1)
•      Следующим шагом с помощью логической функции ЕСЛИ для поля «Тело кредита», сделаем автоматическую проверку достигли ли вы последнего срока выплат или нет. Если период, достигнут, получаем пустую ячейку «», а если нет, то функцией ОСПЛТ выводим необходимый расчёт:
•          =ЕСЛИ(D3»»;ОСПЛТ($B$4/12;D3;$B$5;$B$3;0);»»)

Кредитный калькулятор для кредита с досрочным погашением

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

     Для реализации этого добавляем дополнительный столбик «Доп.платёж» в котором будут указываться сумма платежей уменьшающий остаток кредита. Но у банков есть два варианта развития событий:

• во-первых, сокращения суммы выплат по кредиту на каждый месяц;
• во-вторых, уменьшения срока выплат.

     Для лучшей наглядности будем рассматривать каждый случай в отдельности.

     Рассмотрим расчеты, когда происходит погашения кредита раньше срока, в этом случае будем использовать функционал функции ЕСЛИ и проверим, достигли ли мы нулевой задолженности раньше указанного срока:      В другом случае, когда у вас происходит уменьшение суммы выплат по кредиту, формула пересчитывает ваш ежемесячный платёж сразу же после внесённого дополнительного платежа.

Создаем кредитный калькулятор для кредитов с нерегулярными платежами

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

Согласно таким кредитным программам банк может вам выделять еще дополнительно денег на ваши нужды, но для расчётов такой структуры кредитования производить расчёты нужно с точностью до дня, а не до месяца.

    Ну вот в принципе и всё, единственно что хочу сказать, что подсчёт сколько точно дней находится между двумя указанными датами, лучше производить при помощи функции ДОЛЯГОДА.

     А на этом у меня всё! Я очень надеюсь, что всё о создании кредитного калькулятора в Excel вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!

     Не забудьте поблагодарить автора!

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

Источник: http://topexcel.ru/kak-sozdat-kreditnyj-kalkulyator-v-excel/

Кредитный калькулятор в Excel | Компьютер для чайников

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

Какой кредит брать? В каком банке? На какой срок? Чтобы ответить на эти вопросы и принять правильное решение, необходимо просчитывать множество вариантов. В этом деле поможет кредитный калькулятор в Microsoft Excel.

Калькулятор, о котором сейчас пойдет речь, будет рассчитывать суммы помесячных выплат по кредиту, при условии погашения его равными частями.

статьи

1. 1 Расчет суммы ежемесячных выплат
2. 2 Дополнительные расчеты

Расчет суммы ежемесячных выплат

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

1. Откройте программу Microsoft Excel и введите в столбик А описание исходных данных: сумма кредита, годовая ставка, срок кредита (в месяцах), а также строку результата – сумма ежемесячных выплат. В столбик В мы будем заносить соответствующие значения.
2. Ставим курсор на ячейку результата (в нашем примере это ячейка В5) и в меню «Вставка» находим пункт «функция». Откроется окно Мастера функций
3. Выберите категорию «финансовые» и в окне функций выделите функцию ПЛТ
4. Далее необходимо указать ячейки, которые будут служить аргументами функции. Первый аргумент Ставка. Чтобы не вводить название ячейки вручную, окно мастера можно свернуть кнопкой в конце строки.
5. Сверните окно и выделите ячейку, в которой будете указывать ставку (в нашем примере это ячейка В2).
6. Нажмите на кнопку в конце строки аргумента, чтобы вернуться в окно мастера.
7. Так как ставку вы буде указывать годовую, а результат выплат нужно получить помесячный, то в строке аргумента заданное значение нужно разделить на 12. Дополните строку аргумента «/12»
8. Следующий аргумент – количество периодов. Таким же образом сверните окно и укажите ячейку значения «срок кредита» (у нас это ячейка В3)
9. Аргумент ПС означает сумму кредита, сверните окно мастера и укажите соответствующую ячейку (в нашем примере В1)
10. Аргумент БС выражает конечный баланс. Очевидно, что наша задача погасить кредит полностью, поэтому введите в строку значение аргумента «0». Аргумент Тип указывает на способ учета зачисления выплат – в начале периода (месяца) или в конце. Большинство кредитов выдается по второму типу учета, поэтому определите значение аргумента равным «0»
11. Нажмите кнопку ОК и формула готова. Введите исходные значения, и в результативной ячейке вы найдете сумму ежемесячных выплат. Она будет со знаком минус. Это означает, что деньги вам нужно отдавать…

Дополнительные расчеты

Чтобы более четко представить масштабы затрат по погашению и обслуживанию кредита, калькулятор можно дополнить еще двумя значениями.

1. Всего выплат по кредиту (за весь период) – значение выражается формулой, умножьте сумму ежемесячных выплат на количество месяцев. В нашем примере В5*В3.
2. Сумма переплаты – сколько денег вы заплатите за пользование кредитными средствами. Для этого нужно сложить сумму всех выплат (эта величина у нас со знаком минус!) и сумму кредита. Формула в нашем случае: В6+В1
3. Просчитывать разные варианты и сравнивать предложения по кредитованию с таким калькулятором очень легко.

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

Источник: http://dontfear.ru/kreditnyiy-kalkulyator-v-excel/

uCalc — универсальный конструктор калькуляторов и форм

• Десятки шаблонов для разных сфер бизнеса и готовые блоки для самостоятельной сборки.
• Ползунок, список, галочки, сбор контактов и прием оплат — все под рукой в визуальном редакторе.
• Повышайте SEO-показатели сайта, получайте готовые заявки на почту и телефон, отправляйте сообщения клиентам.
• Не нужно обращаться к разработчикам, чтобы настроить, установить или обновить форму.
• Используйте на сайте, в приложении VK, любом мессенджере, соцсети и почте — виджет на 5 случаев жизни.

• Экономьте бюджет за счет работы с низкочастотными запросами, улучшайте поведенческие показатели, поднимайтесь в поисковой выдаче.
• Моментальный расчет стоимости доступен в любое время дня и ночи. Результаты и ваши контакты можно сохранять себе на почту и телефон.
• Реагируйте на обращения быстрее, получая готовые заявки. Перестаньте тратить время на пустые вопросы и тех, кто все равно не купит.

Создать бесплатно

Наши функциональные калькуляторы в действии

• Строительство и ремонт
• Автосервис
• Недвижимость
• Мебель и интерьер
• Уборка
• Ресторан и кафе
• Праздник
• Еще примеры

• styleИспользуйте готовые шаблоны под популярные запросы
• imageДобавляйте изображения к любому элементу
• font_downloadМеняйте цвета и шрифты без дизайнера

• Настраивайте скидки за объем и комбинацию товаров и услуг
• tuneМеняйте цены и настройки без программиста
• check_circleПринимайте заказы на почту и телефон

• mail_outlineОтправляйте email- и sms-подтверждения о статусе заказа
• paymentПринимайте предоплату прямо в калькуляторе
• insert_chartУзнавайте, откуда пришли заявки

• Калькулятор или форма защищены от копирования с вашего сайта
• Персональные данные ваших клиентов под защитой
• Мобильная версия калькулятора или формы в подарок

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

• 
• 
• 
• И десяткидругих систем

Инструкции по установке калькулятора на популярные конструкторы и CMS вы найдете после регистрации.

Создать бесплатно

Теперь я на шаг впереди конкурентов, так как использую на сайте калькулятор, а они — нет.

Калькулятор упрощает формирование заказов — все становится намного проще: клиент мгновенно видит результат своего выбора, ему не надо писать или звонить и ждать ответа.

УРАГАН!!! У меня много разных услуг, и под каждую нужен калькулятор со своей формулой расчета, своими скидками за объем работ и шкалой — раньше бы к JS-программисту с пачкой заказов идти, а тут я сам залудил калькулятор — и доволен работой этой штуки.

Источник: https://ucalc.pro/

Делаем кредитный калькулятор в Excel

Всем вам наверняка рано или поздно приходит мысль о кредите. Кому нужно машину, кому квартиру. Сам проходил, знаю . И тут  уже надо считать. Когда вы только прицениваетесь к кредиту, то достаточно тех калькуляторов, что есть в сети. Хотя и они отвечают не на все вопросы, к ним адресованные. Но когда вы уже берете кредит, то без расчета графика платежей никуда. Поэтому предлагаю завести собственный кредитный (ипотечный) калькулятор у себя в книге Excel.

Итак, любой кредит имеет 4 основных параметра:

• Срок
• Сумма
• Ставка
• Ежемесячный платеж. Состоит из части погашения основного долга и процентов, набежавших по нему за прошедший период.

• Так же есть две формы платежей – аннуитетные (когда вы каждый месяц платите одну и ту же сумму) и дифференцированные (когда постоянной остается часть ежемесячного платежа – та, которая погашает основной долг, а вторая часть регулярно пересчитывается).
• Если вы знаете 3 показателя, то сможете подобрать четвертый.
• Мы сделаем сначала калькулятор. За расчет всех четырех показателей отвечают эти функции:
• — Срок – Функция ПС()
• — Сумма – Функция КПЕР()
• — Ставка – Функция СТАВКА()
• — Ежемесячный платеж – Функция ПЛТ()

Параметры функций одни и те же – знаете три из 4-х показателей, соответствующая функция выдаст 4-й. Нагляднее смотрите первый лист файла.

Чтобы подготовить график платежей, нам понадобится дата выдачи кредита.

Небольшое отступление по досрочному погашению. Досрочное погашение уменьшает сумму основного долга, поэтому после него обычно пересчитывается ежемесячный платеж или меняется срок кредита.

1. Переходим ко второму листу.
2. 
3. Первая строчка графика – дата выдачи, поэтому тут будет только первоначальная сумма кредита.
4. На второй строке –

1. Дата – определяется как то же число, что и выдача кредита, но следующего месяца. Используем функцию ДАТА, где год и число те же, что и в предыдущем периоде, а месяц на один больше. Но есть нюанс – банк ведь не примет платеж в выходной день. Поэтому делаем корректировку числа с помощью функции ДЕНЬНЕД. Важно: дату можно корректировать вручную, на следующую дату влияния не окажет.
2. Сумма ежемесячного платежа (которая определяется по функции ПЛТ).
3. Сумма погашения процентов как умножение величины прошедшего периода на соответствующий процент. Используется функция ДОЛЯГОДА, чтобы убрать последствия високосности. Банки скрупулезно подходят к расчетам, поэтому период считается в днях, иначе можно было бы сделать проще – взять годовой процент, поделить на 12 месяцев и умножить на сумму.
4. Сумма погашения основного долга – берется как разница ежемесячного платежа и суммы погашения процентов.
5. Досрочное погашения и его дата ставятся произвольно. Единственное условие – ставится в тот период, где дата или меньше или совпадает с датой досрочного погашения.
6. Сумма долга после платежа определяется как сумма предыдущего периода за вычетом погашения основной части и суммы досрочного погашения.

• И последний нюанс построения графика – на третьей строчке мы меняем немного формулу ежемесячного платежа – ставим условие, что если было досрочное погашение, то сумма пересчитывается по функции ПЛТ, а если нет, остается как в предыдущей строке.
• Теперь сделаем такой же график для дифференцированных платежей.
• 
• Меняем две формулы:

1) Сумму погашения основного долга. Она будет неизменной — сумма долга разделить на количество периодов (месяцев).

1. 2) Ежемесячный платеж определяем как сумму двух частей — погашений основного долга и процентов.
2. Разница двух форм по сути в том, что вы больше платите в месяц по дифференцированному платежу, но быстрее расплачиваетесь и поэтому в итоге платите меньше процентов.
3. Какие еще можно вытащить показатели, которые важны нам, но не учитываются в доступных калькуляторах?

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

Цена кредита для меня равнялась сумме выплаченных процентов за минусом арендных платежей за весь период кредита. Если сумма небольшая или вообще отрицательная, то кредит брать стоит.

Бонусом для меня было проживание в СВОЕМ (!) доме, где я знал, что могу забить гвоздь в МОЮ стенку, да и вообще психологическое влияние большое.

• Если кому нужно более наглядно, то вот здесь видео, где этот файл и формировался.
• Эксель Практик
• «Глаза боятся, а руки делают»

P.S. Я веду рассылку, где идет немного другой формат подачи информации, поэтому если интересно — подписывайтесь на рассылку, форма в правой части страницы. Тем более, что сразу после подписки вы получите бонус — мой видеокурс о 10 полезных инструментах Excel, которые не всякому известны.

P.P.S. Для тех, кто хочет экстренно быстро изучить Excel, я специально сделал практикум-самоучитель, можете посмотреть его описание по вот этой ссылке.

Источник: http://excelpractic.ru/delaem-kreditnyj-kalkulyator-2

Тарифный калькулятор ОТМ. Вычислительная машина

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

Бывает так, что буквально за несколько запросов можно уловить закономерность и понять, что калькулятор обращается к очень простой тарифной сетке, которая умещается в обычную таблицу.

Как правило, в этом случае и заполнить пришлось всего-то 2-3 поля для получения цены услуги. Но бывает и так, что нужно изрядно потрудиться и выбрать/заполнить десяток уточняющих параметров, изменение которых приводит к совершенно непредсказуемым колебаниям цены.

Тут начинаешь подозревать, что имеешь дело с какой-то высокоинтеллектуальной вычислительной машиной.

И, разумеется, именно такая изощренная вычислительная машина идет в комплекте с ОТМ.  Причем нельзя сказать, что тарифный калькулятор в ОТМ имеет единственное назначение — рассчитывать стоимость перевозки для клиента по контрактному тарифу или расходы на привлеченный транспорт по тарифам поставщиков. Это прямое назначение и это на поверхности.

Но на самом деле тарифный движок выполняет гораздо более важную функцию, которая лежит в основе всех имеющихся оптимизационных алгоритмов ОТМ. А именно — стоимостную оценку каждого выработанного системой решения.

Каждый критерий оптимизации в конечном итоге может быть сведен к деньгам и идеальным вариантом ОТМ считает тот, который принес наибольший выигрыш по стоимости перевозки на единицу груза.

В статье про Задачу маршрутизации в ОТМ уже затрагивалась эта тема, но применительно к экономии на пробеге: логика поиска решения строится на понятии «выигрыша» — каждое следующее решение по объединению точек в один маршрут оценивается в сравнении с вариантом без этого объединения по общему пробегу. Решение с наибольшим выигрышем переходит на следующую итерацию. Этот способ известен как «Механизм сбережений» (Savings, Clark and Wright, 1964).

Надо отметить, что фактор стоимости затмевает экономию на пробеге в километрах. Это хорошо видно на таком примере — если наша цель состоит в сокращении времени ожидания на точках с жесткими окнами приемки и отгрузки, то мы задаем стоимостной показатель ожидания (ставка за 1 час простоя).

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

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

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

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

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

Еще одна интеллектуальная функция, в основе которой на самом деле лежит результат работы тарифного калькулятора, — это автоматический выбор лучшего поставщика. Как вы понимаете автоматически можно выбрать только лучшего по цене.

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

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

Специализированное против универсального

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

 Что есть универсальный тарифный калькулятор? Если доводить до крайности, то это собственно калькулятор с кнопочками и бумажные каталоги тарифных сеток, из которых нужно брать входные параметры для расчета, основываясь на параметрах груза и дальности перевозки. Что-то похожее на эту таблицу:

Если без крайностей, то остановимся на таком понятном примере как файл Excel. Это эталон универсальности сегодня. Все другие средства, которые пусть на полшага от Excel ушли — это уже более специализированные решения.

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

Теперь вопрос — Будет ли такой калькулятор универсальным? Ответ — Нет. Он работает только для очень конкретного вида тарифа с зафиксированными и описанными с помощью таблицы параметрами. Хотя создан калькулятор был с помощью самого универсального инструмента.

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

Но по сути они не так далеко ушли от нашего файла Excel, как может показаться, и созданные с их помощью калькуляторы всегда будут неуниверсальными. Ведь под каждый тариф нужно продумывать и создавать свою модель данных, которая вместит все участвующие параметры и придаст им смысл. Новый вид тарифа — новая модель или надстройка старой.

Казалось бы — выстраивай что хочешь, изобретай, выдумывай! Но в наше время все меньше первооткрывателей и желающих изобретать велосипед новой модели.

Конкретное против абстрактного

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

С этой точки зрения весь ОТМ — это мощная, разветвленная и хорошо продуманная модель данных, каждый значимый элемент которой закодирован в виде одного из параметров для тарификации.

 Эти параметры называются тарифными базисами и на данный момент в ОТМ их уже более 1000.

Тарифные базисы обращаются к различным числовым, справочным и логическим элементам, присущим заказам и перевозкам.

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

Отдельного упоминания достоин такой важный элемент модели данных ОТМ как геоиерархия (geohierarchy). Есть подозрение, что этот термин существует только в ОТМ, поэтому и толкование возьмем оттуда же.

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

На уровне любой из этих географических сущностей (минимальной из которых как раз и является точка) можно вести тарифные сетки по направлениям перевозок.

Итак, именно конкретная модель данных наполняет смыслом формулы, которые применяются для вычисления стоимости по тарифу. Поэтому инструменты, которые позволяют строить формулы с абстрактными переменными (как наш любимый Excel), еще далеко не являются тарифными калькуляторами.

Шаблоны против импровизации

Наконец, подходим к тому с чего обычно начинают, когда речь заходит о тарифных калькуляторах — это собственно конструктор формул тарифов. Конструктор — это шаблон, призванный определенным образом организовать творческую деятельность по разработке собственных тарифных сеток.

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

Расскажу немного о принципах, заложенных в конструктор тарифных сеток ОТМ, чтобы вы могли проникнуться этими замечательными идеями.

1. Разделение услуг на базовые и дополнительные. Причем возможный набор дополнительных услуг ассоциируется с определенной базовой услугой.
2. Стоимость базовой услуги может состоять из нескольких компонентов, каждый из которых рассчитывается по отдельной формуле. Результаты вычислений могут складываться, а также среди них может быть выбран минимум или максимум, что полезно при выборе между результатом по весу и объему, например.
3. Стоимость дополнительных услуг может зависеть или не зависеть от географии перевозки. В последнем случае тариф на дополнительную услугу удобно задавать для всей тарифной сетки сразу,  а не в отдельности по направлениям перевозок.
4. Для задания формулы для дополнительных услуг используется тот же шаблон формулы, что и для базовых.
5. Шаблон формулы позволяет обращаться к предопределенным в ОТМ тарифным базисам, которых более 1000.
6. Шаблон формулы расчета стоимости услуги состоит из двух разделов: раздел условий и раздел ставки. Условия перед расчетом по тарифу позволяют выбрать только те компоненты стоимости, которые должны быть применены к конкретной перевозке. Например, если условие задано по типу подвижного состава, то в одном тарифе могут быть несколько ставок по различным типам машин, а для конкретной перевозки будет выбрана только одна из них. Именно условия позволяют создавать сетки по диапазонам веса или объема грузов. Ставка уже непосредственно участвует в расчете и задается в денежном выражении по определенному базису: за 1 км груженого пробега, за 1 кг веса груза, за 1 т/км, за 1 час простоя, за 1 рейс.
7. Работать со скидками и надбавками можно как в структуре базового тарифа, так и в формате дополнительных услуг. В этом случае ставка будет задаваться в процентном выражении. Скидки и надбавки могут применяться на всю стоимость, а также выборочно на стоимость базовой услуги или некоторых дополнительных услуг.

Согласитесь, уже вырисовывается очень неплохой инструментарий для воплощения самых замысловатых идей тарификации. А если к этому добавить все те многочисленные мелкие детали и нюансы, которым стоит посвятить отдельную статью, то картинка окончательно сложится.

Источник: http://blog.ikiseleva.com/?p=448

Калькуляция себестоимости продукции с примером расчета в Excel

Себестоимость – это денежное отражение текущих затрат предприятия на производство и реализацию товара.

Для производственной сферы этот показатель – основа для формирования цены. База расчета – калькуляция издержек обращения. А упростить процесс можно с помощью Excel.

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

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

Схема расчета калькуляции себестоимости:

1. Возвратные отходы считаем от расходов на сырье и материалы (берем указанный процент).
2. Для определения дополнительной заработной платы учитываем следующие данные: если основная зарплата более 200 тыс. руб., то дополнительная равняется 10% от основной; менее 200 – 15%.
3. Начисления на зарплату – 30% от суммы основной и дополнительной заработной платы (дополнительные 10%, которые введены с 2015 года на годовой доход более 600 тыс. руб. здесь не учитываются).
4. Издержки на содержание оборудования – 5% от основной зарплаты.
5. Общехозяйственные расходы – 9% от среднего показателя основной заработной платы.
6. Общепроизводственные – 18% от (25% ОЗП + 75% ДЗП). ОЗП – основная заработная плата, ДЗП – дополнительная.
7. Производственная себестоимость = сумма расходов на содержание оборудования, сырье и материалы, топливо и энергию, комплектующие, ОЗП и ДЗП, начислений на ЗП, общепроизводственных и общехозяйственных расходов за вычетом возвратных отходов.
8. Непроизводственные затраты (издержки) – 3% от производственной себестоимости.
9. Полная себестоимость = производственная себестоимость + издержки производства.
10. Прибыль изготовителя считаем в процентах от полной себестоимости.
11. Оптовая цена = полная себестоимость + прибыль изготовителя.
12. НДС считаем от оптовой цены.
13. Отпускная оптовая цена = оптовая цена производителя + косвенные налоги (НДС в примере).

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

Пояснения к расчету некоторых калькуляционных статей:

1. Возвратные отходы – =B2*12,54% (процент взят из первой таблицы).
2. Дополнительная заработная плата – =ЕСЛИ(B6
3. Начисления на зарплату – =(B6+B7)*30%. Если следовать букве закона и брать дополнительные 10% с годовой зарплаты свыше 600 тыс. руб., то используем все ту же функцию «ЕСЛИ».
4. оборудования – =B6*5%.
5. Общепроизводственные расходы – =18%*(B6*25%+B7*75%).
6. Общехозяйственные расходы – =9%*B6.
7. Производственная себестоимость – =(B2+B3+B5+B6+B7+B8+B9+B10+B11)-B4.
8. Издержки производства – =3%*B12.
9. Полная себестоимость – =B12+B13.
10. Прибыль изготовителя – =B14*3,45%.
11. Оптовая цена изготовителя – =B14+B15.
12. Формула для расчета НДС – =B16*20%.
13. Для расчета отпускной оптовой цены – =B16+B17.

По такому же принципу проводится калькуляция себестоимости продукции В и С.

Можно сделать так, чтобы Excel брал исходные данные для расчета сразу в соответствующих таблицах. Например, сырье и материалы – из производственного отчета. Зарплата – из ведомости. Если, конечно, все это ведется в Microsoft Excel.



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

Наиболее характерны для отечественных предприятий следующие:

1. Сырье и материалы.
2. Комплектующие материалы (для производственной техники: топливо и энергия).
3. Основная и дополнительная зарплата производственных рабочих.
4. Начисления на заработную плату.
5. Общепроизводственные расходы за вычетом возвратных отходов.
6. Общехозяйственные расходы.
7. Коммерческие.

Различают производственную себестоимость (пункты 1-6) и полную (сумма производственных и коммерческих расходов). Коммерческие расходы связаны с реализацией товара (реклама, тара, хранение и т.п.).

Непосредственно на себестоимость относятся прямые расходы (пункты 1-3). Косвенные расходы выражаются в коэффициентах или процентах, т.к. связаны с производством всего товара или отдельных видов.

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

Специфика предприятия «диктует» перечень прямых и косвенных расходов. В судостроении, например, практически все затраты отнесутся к прямым расходам. В химической промышленности – к косвенным расходам.

Источник: https://exceltable.com/formuly/kalkulyaciya-sebestoimosti-produkcii-skachat-obrazec

Ипотечный кредитный калькулятор в Excel. Как правильно рассчитать кредит в Excel?

Когда вы взяли кредит, вы так или иначе думаете о досрочном погашении.
Есть люди которые платят кредит и все. А есть те, которые каждый раз смотрят, сколько осталось платить, какая сумма основного долга. Я отношу себя ко второму типу людей, я смотрю сколько сейчас сумма основного долга, пытаюсь рассчитать, сколько будет платеж, если я сделаю досрочное погашение.

На данный момент у меня есть два калькулятора кредита для своих расчетов. Оба калькулятора сделаны в Excel. Калькуляторы позволяют достаточно быстро и просто рассчитать ипотеку.

Как рассчитать кредит в Excel самому?

Скачать кредитный калькулятор в Excel

Первый кредитный калькулятор в Excel можно скачать по ссылке.
Но Excel есть не на всех компьютерах. Пользователи MAC и Linux не пользуются Excel обычно, т.к. это продукт Microsoft.

Для расчета досрочного погашения можно также воспользоваться онлайн версией калькулятора с досрочным погашением. В нем предусмотрена возможность экспорта результатов расчета в Excel.

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

Достоинства данного калькулятора:

1. Кредитный калькулятор в Excel практически точно считает аннуитетный график платежей и дифференцированный график платежей
2. Изменения в графике платежей — учет досрочных погашений в уменьшение суммы основного долга
3. Построение и расчет графика платежей в виде таблицы в Excel.

   Таблица графика платежей может также редактироваться

4. При расчете учитывается високосный и невисокосный год.

   За счет этого сумма начисленных процентов практически совпадает с значениями,  рассчитываемыми ВТБ24 и Сбербанком

5. Точность расчетов — рассчеты совпадают с расчетами кредитного калькулятора ВТБ24 и Сбербанка
6. Калькулятор можно редактировать под себя, задавая разные варианты расчета.

Недостатки калькулятора

1. Нет учета возможное изменение процентной ставки во время выплат кредита
2. Если сделать расчет, делая досрочные платежи в изменение срока и суммы, то расчет будет неверным
3. Если сумма процентов, начисленных за период больше суммы аннуитетного платежа, то расчет будет не верным
4. Не рассчитывается вариант — первый платеж только проценты.  В случае когда дата выдачи не совпадает с датой первого платежа, вам нужно будет заплатить проценты банку за период между датой выдачи и датой первого платежа.
5. Расчет производится для процентой ставки с 2мя знаками после запятой.

Всех выше названных недостатков лишен кредитный калькулятор для iPad/iPhone. В целом недостатки не сильно критичны и они присущи любому кредитному калькулятору онлайн.
Другой кредитный калькулятор в Excel можно скачать по данной ссылке. Данный кредитный калькулятор не позволяет рассчитать досрочное погашение. Однако его плюс в том, что он рассчитывает кредит с несколькими процентными периодами.  Если сумма процентов по кредиту за данный месяц больше суммы аннуитетного платежа, то график для первого кредитного калькулятора в excel строится некорректно.  В графике получаются отрицательные суммы.

Попробуйте посчитать к примеру кредит 1 млн. руб под 90 процентов на срок 30 лет.
У второго калькулятора нет данного недостатка. Однако он делит кредит на 2 периода, т.е. возможно что после деления в графике снова будут отрицательные значения. Тогда график платежей нужно делить на 3 и более периода.

Естественно сам файл также можно отредактировать под свои нужды.

Все акции и скидки банков и МФО

Смотрите все акции крупных банков и МФО, получайте скидки, кешбек и подарки

Копирование материалов с сайта без согласия автора запрещено. Более подробно на http://mobile-testing.ru/rules

Источник: http://mobile-testing.ru/loancalc/loan_calc_excel/