Калькулятор коммерческого предложение в excel по прайс листу без VBA.

Калькулятор Доктор на работе

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

Исходные файлы: мы предоставляем файл xlsx, состоящий из 5 листов. 3 листа содержат информацию о емкости аудитории (103 позиции), стоимости каждого инструмента размещения (114 позиций), KPI и др. Используя эти данные Программа формирует 2 сметы – отдельно на размещение и на продакшн в формате заданного медиаплана.

Результат: готовый калькулятор представляет собой таблицу, в которой менеджер выбирает позиции из выпадающего списка, Программа производит расчет, используя данные с других листов и выдает готовый расчет.
Пример: 
1. выбор целевой аудитории из выпадающего списка – Программа подтягивает количество врачей с соответствующего листа. Специальностей может быть несколько. На некоторые специальности предусмотрена наценка – программа должна это учитывать при расчетах.
2. выбор рекламного инструмента из выпадающего списка в каждой группе (6 групп) – Программа подтягивает стоимость с соответствующего листа, считает НДС, рассчитывает прогнозный KPI на 1000 врачей и 2 индекса (FTE и GRP) с помощью данных на соответствующих листах. 

Все расчеты построены на простом умножении/делении: 
Цена: X врачей * Y рублей = стоимость + 20% НДС.
KPI: X показов/1000 врачей * кол-во врачей/1000 = KPI
FTE: KPI * коэффициент на соответствующем листе
GRP: KPI/емкость аудитории с соответствующего листа

3. Расчеты по выбранным форматам суммируются. Сумма отражается в сводной таблице.
Внешний вид предыдущей версии Калькулятора, см. вложение.

Нюансы:
1. Рабочие листы/расчеты не должны быть видны. Пользователь видит только итоговый медиаплан.
2. Параметры в таблице/наименования нельзя менять – всё выбирается из выпадающего списка и рассчитывается автоматически.
3. Если инструмент размещается на несколько аудиторий, включающих разные наценки, расчет стоимости производится для каждой аудитории отдельно, далее результаты суммируются, в таблицу выводится только результат (этап расчета с учетом наценки для каждой аудитории отдельно не виден)
4. Есть инструменты, в которых менеджер сам должен выбрать количество пользователей, на которых размещается инструмент. Если аудиторий несколько, выбранное менеджером количество автоматически распределяется пропорционально емкости аудитории, расчет стоимости проводится для каждой отдельно с учетом наценки (см. пункт 3)