Будем учиться создавать универсальную таблицу, которая даст нам возможность рассчитать цену при нескольких заданных параметрах.
Создаем таблицу и заполняем ее шапку показателями, которые будут участвовать в расчетах.
При этом часть показателей неизменна для всех видов товара, поэтому мы их вынесем в отдельный блок «задаваемые параметры», и сделаем так, чтобы при изменении этих параметров менялись расчеты во всей таблице.
Основная задача данного урока — научиться делать абсолютную ссылку. Что это такое?
Абсолютная ссылка дает возможность ссылаться на одну и ту же ячейку при копировании формулы (в отличие от относительной ссылки). Таким образом, мы можем в первой строке сослаться на конкретную ячейку, скопировать ее и протянуть до конца списка. Весь список будет ссылаться на ту ячейку, в которой действует абсолютная ссылка. Соответственно при изменении этой ячейки меняется весь столбец или строка.
Перейдем к практике. Посчитаем процент менеджера от выручки.
Для того, чтобы сделать абсолютную ссылку, нужно произвести следующие шаги:
- добавить формулу;
- зайти в строку состояния;
- поставить знак $ перед буквой и перед цифрой, указывающих на адрес ячейки.
Здесь можно столкнуться с одной проблемой. В том случае если в параметрах задан стиль ссылок R1C1 (т.е. и строки, и столбцы обозначаются цифрами), мы увидим такую картину:
Для того, чтобы устранить эту проблему нужно зайти в «Сервис» главного меню, выбрать кнопочку «Параметры», найти закладку «Общие» и снять галочку в квадратике «Стиль ссылок R1C1».
Итак, мы сделали первую формулу, построенную на абсолютной ссылке. Проверим, что будет при ее копировании.
Скопируем ячейку с формулой и протянем ее до конца списка. Для проведения этой операции нужно поставить курсор на левый нижний край ячейки с формулой. Появится крестик. Нажмите на этот крестик левой клавишей мышки и тяните не отпуская пальца вниз до конца списка товаров.
Как видим, все формулы ссылаются на ячейку с процентом менеджеров, что нам и было нужно.
По аналогии считаем ретро-бонус оптовика.
Не забываем, что наша основная цель — поиск цены. Значения, которые там стояли, были выставлены произвольно для примера, поэтому сейчас можно очистить это поле для дальшейнего заведения туда формулы. Добавим ее после того, как все остальные поля будут заполнены.
Итак, продолжаем заполнять таблицу: заносим данные о себестоимости и считаем стоимость отсрочки платежа:
Как видим, при расчете стоимости отсрочки платежа использованы две абсолютные ссылки: на количество дней отсрочки и % банковской ставки.
Мы закончили с занесением параметров, необходимых для расчета цены. Можем добавлять формулу.
Напомним, что формула выглядит следующим образом:
Цена = Сумма переменных затрат, не зависимых от выручки / ( 1 — сумма переменных затрат, зависимых от выручки (в % от выручки) / 100 — маржа (в % от выручки) / 100 )
Рассчитаем цену Товара № 1:
Цена = ( 44,64 + 0, 37 ) / ( 1 — 5/100 — 15/100 — 40/100) = 112,52
Для того, чтобы рассчитать цену в excel, активизируем соответствующую ячейку и пишем в ней БЕЗ ПРОБЕЛОВ соответствующие знаки или активизируем соответствующие ячейки в следующем порядке:
= ( 44,64 + 0, 37 ) / ( 1 — % менеджера/100 — ретро-бонус/100 — маржа/100 ) ENTER
Значения, названные словами, мы рассчитаем с помощью абсолютных ссылок. Таким образом мы будем иметь возможность, рассчитав цену на один товар, скопировать формулу для других.
Мы нашли цену, которая позволит нам покрыть переменные затраты и иметь заданный уровень маржинального дохода. Нам осталось только проверить правильность расчетов и добавить НДС к цене.
Итак, по очереди считаем (как и раньше без пробелов активизируем указанные ячейки и знаки в следующем порядке):
для Цены с НДС
= Цена без НДС * 1,18 ENTER
для Валового дохода
= Расчетная цена без НДС — Себестоимость без НДС ENTER
для Наценки
= Валовый доход без НДС / Себестоимость без НДС * 100 ENTER
для Затрат
= % менеджеров + отсрочка + ретро-бонус ENTER
для МД
= Валовый доход без НДС — Затраты без НДС ENTER
для Маржи
= МД / Расчетная цена без НДС * 100 ENTER
Проверяем:
Все сошлось! Проверка подтвердила правильность наших расчетов.
Подобные таблицы позволяют подставлять разные параметры для разных товаров и быстро получать результат, экономя при этом наше время. Для того, чтобы просчитать показатели для нового товара, достаточно скопировать одну их строк и вставить ее вниз таблицы. Все формулы будут идентичными.
