Разработка электронного калькулятора стоимости в Экскль

Техзадание по калькулятору

Исходные данные

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

На каждый бренд есть своя скидка. Сумма закупки = цена прайс минус скидка.

Себестоимость состоит из суммы закупки и логистики.

Логистика зависит от бренда и состоит из следующих компонентов:

- стоимости доставки на промежуточный склад (зависит от бренда)

- стоимости обработки на промежуточном складе (в Италии один тариф за куб, в Германии другой)

- стоимости доставки с промежуточного склада в Киев (из Италии один тариф, из Германии другой)

Все это привязано к объему доставляемого товара. Цена формируется за кубический метр товара.

По каждому бренду мы знаем, везем ли мы его через промежуточный склад, либо напрямую. От какого объема напрямую, до какого объема - через промежуточный склад и в какой стране.

Боль, которую решаем - формировать цену заказа «на лету» в зависимости от того, какая на него скидка и сколько примерно будет стоить логистика. Потому как если заказывать 1 штуку изделия, то накладные расходы могут быть равны цене закупки (свозка, обработка на складе, доставка). А в случае заказа 10 штук этого же артикула накладные расходы составят всего 10%.

На самом деле, у нас это все решено в 1С. Но я не могу пока пустить в 1С клиентов и не могу опубликовать 1С на сайте. Это в будущем.

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

Пример:

Цена прайсовая товара 1000 евро

Скидка 50%.

Цена закупки 500 евро.

При этом это люстра габаритами 60смХ80смХ80см.и ее объем составляет 0,384 куба (0,6 Х 0,8 Х 0,8).

За таким заказом перевозчик не поедет на конкретного поставщика, поставщик такой заказ бесплатно не доставит до промежуточного склада.

Будет доставка на промежуточный склад из расчета 80 евро за куб, но не менее 50 евро. В нашем случае это будет 50 евро.

На складе будет обработка груза 10 евро за куб (кратно кубам).  В нашем случае это будет 10 евро.

И будет доставка в Украину по тарифу 190 евро за куб (но не менее, чем 50 евро). В нашем случае 73 евро.

Итого накладных расходов будет 50+10+73=133 евро. Это 26,5%.

Если же везти с этой же фабрики 10 таких люстр (общим объемом 3,84 куба), то тогда перевозчик заедет прямо на фабрику. И у нас не будет доставки на промежуточный склад, затрат по складу и только стоимость в Украину = 190 Х 3,84 = 730 евро.

При этом сумма закупки будет 500 Х 10 = 5000 евро

Процент накладных расходов составит 14,6%.

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

Для этого и нужен калькулятор.

Как я это видел бы

Исходные данные - это 3 листа (возможно, ты что-то пересмотришь)

Лист «Бренды» - таблица с брендами, где напротив каждого бренда указана его скидка, срок выполнения заказа, через какой группах доставляется (Италия, Германия - можно выбрать только из списка, который хранится на отдельном листе), принцип расчета свозки на промежуточный склад (стоимость от куба, но не менее ХХХ, от объема ННН забираем прямо с фабрики). К примеру, есть столбцы «Минимальная свозка», «Стоимость свозки за куб», «Прямой забор от объема». В зависимости от их значений далее идут расчеты.

А также по каждому бренду есть поле «Скидка в Украине» и поле «Рентабельность».

Лист «Стоимость доставку в Украину»

Здесь фактически 2 строки:

Италия

Германия

На эти строки ссылаются записи в брендах (можно выбрать )

Здесь фактически указывается стоимость доставки куба в Украину.

Лист «Калькулятор»

Здесь пользователь заполняет:

1. выбирает бренд из выпадающего списка

2. руками заполняет поле описание товара (не обязательно)

3. руками заполняет поле артикул (не обязательно)

4. руками вносит количество единиц (если не внес - формула выдаст 0 как результат расчета цены)

5. руками вносит объем товара (если видит его в прайсе уже от производителя)

6. если поле Объем не заполнено - может руками вносить Высоту, Ширину, Глубину изделия. Далее к этим параметрам добавляется по 10 см на каждый на упаковку и для формулы рассчитывается транспортный объем изделия. Для определения доставляемого объема нужно сложить общий объем по бренду и добавить к нему еще 10%

7. программа по формуле рассчитывает и выдает стоимость изделия (Цену продажи)

Цена продажи изделия (ЦП) равна:

- если в листе по бренду есть значение в поле «Скидка в Украине», то ЦП = Прайсовая цена Х (1 - Скидка в Украине)

- если это значение пустое, то необходимо определить общий объем по каждому бренду на листе «Калькулятор» и рассчитать отдельную цену по следующему принципу:

ЦП = (Прайсовая Цена х (1-Скидка бренда)+Логистика изделия)/(1-Рентабельность бренда)

Логистика изделия = Объем изделия / Объем по бренду Х Стоимость доставки всего объема по бренду

Стоимость доставки всего объема по бренду = Суммарный объем бренда Х Стоимость свозки (рассчитывается согласно условий в закладе бренды) + Суммарный объем бренда Х Стоимость склада (рассчитывается согласно условий в закладе бренды) + Суммарный объем бренда Х Стоимость доставки в Киев (в зависимости от того, через какой группаж доставляется).

Если пользователь добавляет сроки позициями - то на лету определяется объем товара и выдается цена продажи каждой позиции и общая сумма по строке (Цена продажи Х  Количество) и по бренду (возможно, где-то в отдельной части таблицы собирается список брендов, складывается объем товара и общая сумма по бренду), срок выполнения заказа по бренду (просто указывается из листа «Бренды» по этому бренду).

Список бантиков:

8. пользователь не видит ничего, кроме листа «Калькулятор» (ну или не может зайти на остальные листы). Если так нельзя - то тогда делать отдельными файлами: отдельно расчетный файл, отдельно - исходные данные. Может, так и лучше.

9. кнопка на листе «Калькулятор» «сохранить расчет в ПДФ» - сохраняет ПДФ копию листа «Калькулятор»

10. кнопка на листе «Калькулятор» «сохранить расчет в Эксель» - сохраняет копию листа «Калькулятор», заполненную только значениями (никаких ссылок, формул и т.д.)

11. если пользователь делает какие-то действия с файлом - уведомляется владелец документа