Калькуляция цены поставляемой продукции «А» на месяц
Задание 0
1. На листе Справочникисоздать таблицы.
| ФИО экскурсовода | Категория экскурсантов | Скидки | |
| Иванова | Школьники | 10% | |
| Михайлова | Ветераны | 15% | |
| Петрова | Не организованные | 0% | |
| Сидорова |
2. На листе Экскурсоводсоздать таблицу. Отформатировать данные так, как показано в таблице, а стоимость обслуживания представить в денежном выражении.
Подготовить именованные блоки на листе Справочникидля ввода данных на лист Экскурсовод. Заполнить поля ФИО экскурсоводаи Категория экскурсантовв виде списков.
Цену для группы - 450 р. поместить в отдельную именованную ячейку.
Учёт работы экскурсоводов фирмы «Спб Турист»
| Дата | ФИО экскурсовода | Категория экскурсантов | Стоимость обслуживания | Кол-во человек в группе |
| 10.04.2009 | Иванова | Школьники | ||
| 11.04.2009 | Иванова | Не организован | ||
| 12.04.2009 | Иванова | Школьники | ||
| 12.04.2009 | Иванова | Не организован | ||
| 13.04.2009 | Михайлова | Ветераны | ||
| 13.04.2009 | Михайлова | Ветераны | ||
| 10.04.2009 | Петрова | Не организован | ||
| 11.05.2009 | Петрова | Не организован | ||
| 12.05.2009 | Петрова | Не организован | ||
| 11.05.2009 | Сидорова | Школьники | ||
| 12.05.2009 | Сидорова | Не организован |
3. Вычислить:
§ Стоимость обслуживания = Цена_для_группы*(1 - Скидка).
Значение скидки, соответствующее категории экскурсантов, определяется с помощью функции ВПР.
4. На листе Фильтрвывести запись таблицы, которая содержит сведения об экскурсии с максимальным (наибольшим) количеством экскурсантов.
5. На листе Итогиполучить по каждому экскурсоводу общую сумму стоимости обслуживания и общее количество экскурсантов.
6. Построить смешанную диаграмму на двух осях для анализа работы экскурсоводов на основе результатов, полученных в пункте 5. Общая сумма стоимости обслуживания в виде гистограммы. Общее количество экскурсантов в виде линейного графика.
7. Построить Своднуютаблицу, в которой определить заработную плату каждого экскурсовода за каждый месяц при условии, что она составляет 50% от суммарной стоимости обслуженных им экскурсий.
8. Подготовить средствами MS Word серию рассылок экскурсоводам информации «о работе экскурсоводов фирмы «Спб Турист».
Задание 1
1. На листе Справочникисоздать таблицы.
| Месяц |
| январь |
| февраль |
| Код заказчика | Заказчик |
| Стиль | |
| Империя | |
| Престиж | |
| Волна |
| Пеня |
| 12% |
2. На листе Учётсоздать таблицу. Отформатировать данные так, как показано в таблице.
Подготовить именованные блоки на листе Справочникидля ввода данных на лист Учёт. Поля Месяци Код заказчиказаполнить в виде списков,а поле Заказчикс помощью функции ПРОСМОТР.
Учёт отгрузки и оплаты товаров заказчикам
| Месяц | Код заказчика | Заказчик | Отгружено, руб. | Оплачено, руб. | Возврат, руб. | Долг+Пеня, руб. |
| январь | ||||||
| январь | ||||||
| январь | ||||||
| январь | ||||||
| февраль | ||||||
| февраль | ||||||
| февраль | ||||||
| февраль |
3. Вычислить Долг+ Пеня= Долг * (1+ Пеня). Учесть в формуле, что Долг = Отгружено - Оплачено - Возврат, а пеня начисляется, если Долг превышает 500 руб.
4. На листе Итогиполучить итоги по каждому заказчику в столбцах Отгружено, Оплаченои Долг+Пеня.
5. На отдельном листе построить смешанную диаграмму по итоговым данным для анализа работы фирмы по заказчикам. Отгрузкуи Оплатупредставить в виде гистограммы, а Долг+Пеня– в виде линейного графика. Дать название диаграмме «Учет отгрузки и оплаты товаров заказчиками».
6. На листе Фильтрс помощью расширенного фильтра выбрать из исходной таблицы заказчиков, имеющих в феврале долг с учетом пени. Отобранные записи представить в новой таблице, включающей столбцы:
| Месяц | Заказчик | Долг+Пеня, руб. |
7. Создать Своднуютаблицу, в которой вывести по месяцам общие суммы по всем показателям. Вычислить Долг = Отгружено – Оплачено + Возврат. Обеспечить выборку данных по заказчикам.
8. На лист Слияние скопировать результаты фильтрации. Подготовить средствами Word рассылку писем должникам.
Задание 2
1. На листе Справочниксоздать таблицу. Сумма тарифа – именованная ячейка.
| Получатель | Удалённость,км |
| РП1 | 0,0 |
| РП2 | 750,0 |
| РП3 | 300,0 |
| РП4 | 120,0 |
| РП5 | 500,0 |
| Тариф (за 100 км) | |
| 250,00р. |
2. На листе Коммерцияоформить таблицу согласно образцу. Задать краткий формат дат.
Подготовить именованные блоки на листе Справочникидля ввода данных на лист Коммерция. Поле Получательзаполнить в виде списка; Удалённость(расстояние от поставщика до получателя) с помощью функции ВПР.
Коммерческие расчёты
Начало зимнего периода: 15 окт
Конец зимнего периода: 15 мар
| Получатель | Удалённость,км | Дата отгрузки | Сумма заказа, руб. | Оплата транспорта, руб. | Сезонность,руб. |
| РП2 | 1 июн | ||||
| РП3 | 15 янв | ||||
| РП4 | 6 дек | ||||
| РП5 | 29 июл | ||||
| РП4 | 25 май | ||||
| РП4 | 12 дек | ||||
| РП3 | 12 ноя | ||||
| РП3 | 17 окт | ||||
| РП1 | 20 фев | ||||
| РП1 | 12 ноя | ||||
| РП1 | 2 окт |
3. Вычислить:
§ Оплата транспорта= Удалённость / 100* Тариф.
§ Сезонность=Оплата транспорта * 8%. В формуле учесть, что сезонность начисляется в период от начала до конца зимнего периода текущего года.
4. На листе Итогидля каждого получателя получить всю сумму заказов и всю сумму оплаты транспорта.
5. По данным Итоговпостроить гистограмму с накоплением, подписать значения, для оси Хприменить денежный формат.
6. На листе Фильтрс помощью расширенного фильтра в новую таблицу вывести заказы, доставка которых займет больше пяти (5) часов при средней скорости 57км в час:
| Получатель | Удалённость,км | Сумма заказа, руб. |
7. Построить Своднуютаблицу, в которой для получателей вывести сумму заказов. Вычислить: Полная стоимость= Сумма заказа+ Оплата транспорта+ Сезонность. Сгруппировать даты и организовать выборку данных по кварталам.
8. Средствами Word по результатам фильтрации подготовить получателям рассылку информации о доставке заказов.
Задание 3
1. На листе Справочниксоздать таблицы.
| Тарифное расстояние, км | Процент тарифа | Транзитная норма(объём, л) | ||
| 0% | Тариф | 500,00 | ||
| 25% | ||||
| 50% | ||||
| 75% | ||||
| 100% |
2. На листе Калькуляцияоформить таблицу согласно образцу. Результаты вычислений отобразить в денежном формате.
Подготовить именованные блоки на листе Справочникидля ввода данных на лист Калькуляция. Столбец Тарифное расстояниезаполнить в виде списка; столбец стоимость Доставкис помощью функции ВПР.
Калькуляция цены поставляемой продукции «А» на месяц
Оформление (одного заказа) 277,00р.
Себестоимость продукции 15,40р.
| Получатель | Тарифное расстояние, км | Потребность продукции, л | Кол-во заказов | Доставка | Оформление | Цена |
| П1 | ||||||
| П2 | ||||||
| П3 | ||||||
| П4 | ||||||
| П5 | ||||||
| П6 | ||||||
| П7 | ||||||
| П8 | ||||||
| П9 | ||||||
| П10 |
3. Вычислить
§ Доставка = Тариф * Процент тарифа * Кол-во заказов; Процент тарифа, соответствующий тарифному расстоянию, задать с помощью функции ВПР.
§ Оформление= Кол-во заказов* Оформление(одного заказа).
§ Цена= Себестоимость+ (Доставка+ Оформление) / Потребность.
4. На листе Итогиполучить по тарифным расстояниям суммарное количество заказов и суммарную потребность.
5. По результатам итогов построить диаграмму График/гистограмма (2 оси), изменить цвет и шрифт надписей осей и данных, добавить таблицу данных
6. На листе Фильтрс помощью расширенного фильтра вывести те строки, в которых тарифное расстояние не ноль и потребность/кол-во заказов (т.е. объём одного заказа) превышает транзитную норму. Таблица результата:
| Получатель | Потребность продукции, л | Кол-во заказов |
7. Построить Своднуютаблицу, в которой вывести для тарифных расстояний доставку и оформление. Вычислить их сумму.
8. Средствами Word по данным исходной таблицы подготовить и оформить рассылку информации каждому получателю.
Задание 4
1. На листе Справочникисоздать таблицы.
| Наименование | Стоимость за 1 кв. м | Льготы | Скидка | |
| Выравнивание стен | 100р. | Пенсионеры | 20% | |
| Оклейка простыми | 60р | Декабрь | 15% | |
| Оклейка сложными | 150р. | Январь | 15% | |
| Побелка потолка краской | 120р. | Февраль | 15% | |
| Побелка потолка мелом | 70р. | Скидки не суммируются | ||
| Снятие старых обоев | 10р. |
2. На листе Учёт заявоксоздать таблицу. Отформатировать данные так, как показано в таблице, стоимость за 1 кв. м, стоимость без скидки и стоимость со скидкой представить в денежном формате.
Подготовить именованные блоки на листе Справочникидля ввода данных на лист Учёт работ. Поля Месяц(Декабрь; Январь; Февраль; Март), Пенсионер(Да;Нет) и Наименованиезаполнить в виде списков; поле Стоимость за 1 кв. м заполнить с помощью функции ВПР.