II. Расчет себестоимости выпуска книги
Замечание. Второе задание очень похоже на предыдущее, но в нем используется немного больше расчетов, кроме того, исходные данные расположены на другом листе рабочей книги. Выбрав объем книги, ее формат, тираж и прочие исходные данные, вы сразу узнаете себестоимость одного экземпляра.
Начальные условия задачи. Все исходные данные и результаты в примере расположены на одном листе, а вспомогательная информация, нужная для расчетов, — на другом. В качестве исходных данных используется объем книги в страницах, формат книги, то есть ее размеры, используемая бумага, тип переплета, то есть обложки книги, тираж и коэффициент гонорара авторам. Условно считается, что авторы получают фиксированную плату за каждый лист, но для некоторых книг может использоваться повышающий или понижающий коэффициент.
1. На первом и втором листе рабочей книги (переименуйте их в «Расчет» и «Данные») создайте следующие заготовки.
Введите заголовок таблицы и названия в столбцы А и С. Все исходные данные вводятся в ячейки столбца В. Если объем, тираж и коэффициент гонорара вводится обычным способом, то остальные три ячейки заполняются данными из списка. Однако если вы создадите список на другом листе, то не сможете просто указать его для проверки, как в предыдущем примере. Для этого нужно выполнить некоторые предварительные действия.
2. Перейдите на второй лист рабочей книги и найдите варианты переплета книги. В нашем примере эта информация введена в ячейки с A3до В5. Выделите диапазон ячеек с А3 до А5 и введите в поле имени, расположенное в левой части строки формул, имя Переплет. Далее вернитесь на первый лист и сделайте текущей ячейку В6. Выберите команду меню Данные > Проверка, и на вкладке Параметры появившегося диалога выберите в списке Тип данных вариант Список. В поле Источник введите формулу =Переплет. Если данные для списка расположены на другом листе, для ссылки нужно использовать имя. Установите флажок Список допустимых значений и нажмите кнопку ОК. Диалог закроется, список значений будет задан для данной ячейки.
3. Аналогично задайте проверку ячеек В4и В5.
4. Сразу можно ввести формулу для расчета окончательной суммы. Введите в ячейку В14 формулу =(D3+D6+D9+D10+D16+D17+D18)/B9. После сложения все расходы делятся на размер тиража книги.
5. Далее нужно ввести формулы для расчета, и начнем мы с самых простых. Введите в ячейку D3 формулу =D4+D5. Расходы на обложку состоят из двух частей. Первая часть - расходы на подготовку к печати, например, на изготовление диапозитивов, — не зависят от тиража. И для одной книги и для десяти тысяч книг нужно изготовить одни и те же диапозитивы. Вторая часть зависит от тиража (например, стоимость печати и бумаги). Аналогично, стоимость основной части книги также состоит из двух частей, поэтому введите в ячейку D6формулу =D7+D8. Так как некоторые другие составляющие также состоят из нескольких частей, введите в D10формулу =СУММ(D11:D15), а в ячейку D18формулу =D19+D20.
6. Некоторые суммы в нашем примере фиксированы, и не зависят от вводимых данных. Их можно просто ввести в нужные ячейки, но для единообразия лучше вставить ссылки на ячейки второго листа. Введите в ячейку D4 формулу =Данные!Е14, а в ячейку D11 формулу =Данные!Е7.
7. Все простые формулы введены, и настала пора вводить основные формулы для расчетов. При расчетах часто используется функция ВПР, описанная выше, так что мы не будем снова рассказывать об особенностях ее применения. Введите в ячейку D5 формулу:
=В9*ВПР(В4;Данные!А18:В20;2;ЛОЖЬ)*ВПР(В6;Данные!АЗ:В5;2;ЛОЖЬ)
Тираж книги умножается на стоимость печати обложки, различающейся для разных форматов. Полученное число умножается на коэффициент, учитывающий тип обложки, например, твердый переплет дороже мягкого.
8. В ячейку D7 введите формулу =ВЗ*Данные!Е13, так как постоянные расходы, не зависящие от тиража, все же зависят от объема книги. Далее введите наиболее сложную формулу в ячейку D8:
=В9*В3*(ВПР(В4;Данные!А18:С20;3;ЛОЖЬ)+
ВПР(В5;Данные!D3:Е4;2;ЛОЖЬ)*ВПР(В4;Данные!А18:D20;4;ЛОЖЬ))
Тираж книги умножается на количество страниц, на стоимость страницы, которая в свою очередь состоит из двух частей — стоимости печати и стоимости бумаги. Стоимость печати берется из таблицы со второго листа, в зависимости от формата книги, то есть ее ширины и высоты. Стоимость бумаги определяется на основе формата и типа используемой бумаги.
9. В ячейку D9введите формулу =В3*Данные!В7*В7. Гонорар авторам определяется как произведение объема книги на базовую ставку и на коэффициент.
10. Примерно так же определяются затраты на других работников, только без учета коэффициента. Все оставшиеся ячейки, кроме налогов, рассчитываются как произведение соответствующего показателя с листа Данные на объем книги, хранящийся в ячейке В3.
11. Самостоятельно рассчитайте ячейки с суммами налогов.
12. После того, как введены все формулы и занесены справочные данные на втором листе, можно выполнять расчет. Вводя разные значения в ячейки, можно узнать себестоимость издания.
Кстати, с помощью данной таблицы можно подбирать нужные варианты. Выберите команду меню Сервис > Подбор параметра, и откроется диалог настройки подбора. В качестве ячейки, в которой нужно установить заданное значение, выберите ячейку В14, в которой выводится рассчитанная себестоимость. Введите нужное вам значение, а для изменения укажите ячейку В9, то есть тираж книги. Нажмите кнопку ОК, и Excel найдет тираж, при котором можно получить нужную себестоимость. Аналогично можно при неизменном тираже найти оптимальный объем книги.
Дата добавления: 2014-11-30; просмотров: 5536;