Калькуляция цены поставляемой продукции «А» на месяц

Задание 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 кв. м заполнить с помощью функции ВПР.