Какая формула здесь зашифрована?
Эта таблица позволяет понять, на сколько % надо увеличить продажи при сокращении цены, чтобы прибыль не уменьшалась. Под сокращением цены понимается скидка. То есть если делаешь скидку, то на сколько надо увеличить продажи. В колонке слева стоит размер скидки, справа сверху (5%, 10%, 15%, 20% и тп) указан размер валовой прибыли (видимо наценки). Ниже в таблице указано на сколько % надо увеличить продажи, чтобы прибыль не изменилась.
Пример: если текущая валовая прибыль равна 25% и вы делаете клиенту скидку в 10%, чтобы вы заработали не меньше, чем до скидки вам нужно увеличить ваши продажи на 66,7%.
Итак, чтобы перенести эту таблицу в эксель и подставлять значения скидки и прибыли, которых нет в таблице мне надо вывести формулу, которая все это дело считает.
Вот как я считала:
Взяла для примера скидку 2% и прибыль 10%. Значит судя по таблицу я должна увеличить продажи на 25%.
Возьмем партию яблок в 200шт.
Цена 1шт = 100р, себестоимость = 90р
цена партии в 200шт = 100*200=20000р, себестоимость = 90*200=18000р
маржа/прибыль партии получается = (20000-18000)/20000*100 = 10%
прибыль с партии = 200*100*10% = 2000р
Делаем скидку в 2% и увеличиваем размер партии на 25% = 20000*0,98*1,25=24500р стоимость партии
маржа/прибыль после скидки уменьшилась = (24500-18000)/20000*100 = 8,2% = 2000р
Как мы видим, прибыль до скидки в 2% была 2000р. После добавления скидки и увеличения размера партии на 25% мы имеем ту же прибыль в 2000р
Какую теперь поставить формулу для таблицы в эксель на место значений, показывающих на сколько надо увеличить размер партии?
Ну вы сделали нормальный анализ, проверили, как работает таблица - убедились, работает. На самом деле таблица и не нужна, формулу можно написать, исходя из постановки задачи.
Я могу написать за вас формулу, но разве это интересно? Попробуйте сами. Просто рассмотрите не конкретный пример, как сделали вы, а в общем виде. Замените конкретные цифры на буквы (неизвестные) и сделайте тоже самое.
1) Пусть у меня партия товара стоит Х (продажная цена). Я на ней без скидки зарабатываю X*P (в таблице P " = gross margin = 0,05; 0,1;….. но это не важно - вы сможете любое P подставить, например 0,075. P < 1, понятно - это значение в ячейке "маржа").
2) Если я сделаю скидку К (в ячейке "скидка"), я заработаю X*(1-K)*P. Но я хочу заработать столько-же, сколько в предыдущем случае. Поэтому я увеличиваю Х. Не "на сколько" а "во сколько раз". Пусть это значение будет С (это в ячейках результата, нам его и нужно найти)
3) Тогда я заработаю (С*Х)*(1-К)*P. И я хочу, чтобы это было равно предыдущему заработку, то есть X*P.
4) Приравниваете, получаете уравнение, выводите С - вот и есть ваша формула. Х уйдет - от него результат не будет зависеть. Останутся К и Р - значения в ячейках.