| Наименование | Тип предприятия | Доход,тыс. руб. | Текущие расходы,тыс. руб. | Налог,тыс. руб. | Чистый доход, тыс. руб. | Уд. вес чистого дохода, % |
| Нева | АОЗТ | 5 000,50 | 2 993,80 | |||
| Север | ТОО | 12 600,00 | 3 520,00 | |||
| Звезда | АОЗТ | 50,80 | 165,50 | |||
| Аврора | АОЗТ | 1 000,00 | 720,00 | |||
| Заря | АОЗТ | 2 000,00 | 1 020,80 | |||
| Алмаз | ТОО | 3 000,00 | 2 070,00 | |||
| Верфи | ТОО | 4 500,00 | 2 270,00 | |||
| Рубин | АОЗТ | 5 000,00 | 3 080,80 | |||
| Гранит | ТОО | 2 800,50 | 1 097,00 | |||
| Восход | ТОО | 3 800,50 | 1 524,00 | |||
| Итого |
3. Вычислить:
§ Налог = Доход * Ставка налога. Ставки налога, соответствующие типу предприятия, определить с помощью функции ВПР.
§ Чистый доход = Доход – Текущие расходы – Налог
§ Удельный вес чистого дохода, % = Чистый доход/ Итоговый Доход всех предприятий
4. Построить по таблице круговую диаграмму, отобразив удельный вес чистого дохода предприятий, выдвинуть наибольший сектор.
5. На листе Итоги получить для каждого типа предприятий итоговые суммы текущих расходов и налогов.
6. На листе Фильтр с помощью расширенного фильтра получить сведения о предприятиях, чистый доход которых не превышает 500 тыс. руб. Результат вывести в новой таблице:
| Наименование | Доход,тыс. руб. | Налог,тыс. руб. |
7. Построить Сводную таблицу, в которой вывести сумму доходов по типам предприятий. В сводной таблице вычислить Затраты = Текущие расходы + Налог.
8. Средствами Word по результатам фильтрации подготовить и оформить рассылку информации предприятиям.
Задание 15
1. На листе Справочники создать таблицу.
| Заказчики |
| Бовин |
| Папирян |
| Навигатор |
| Вирт |
2. На листе Выполнение оформить таблицу по образцу.
Подготовить именованные блоки на листе Справочники для ввода данных на лист Выполнение. Заполнить столбец Заказчик в виде списка.
Расчёт доли выполненных заказов
| Всего заказов: | на сумму: |
| Номер заказа | Заказчик | Дата заказа | Сумма заказов | Сумма выполнено | Пометка о выполнении | Доля выполнениязаказа % |
| Бовин | 04.08.12 | |||||
| Навигатор | 04.08.12 | |||||
| Вирт | 04.08.12 | |||||
| Папирян | 06.08.12 | |||||
| Навигатор | 06.08.12 | 595,00р. | ||||
| Вирт | 06.08.12 | 600,00р. | ||||
| Вирт | 06.09.12 | 400,00р. | ||||
| Папирян | 06.08.12 | 400,00р. | ||||
| Вирт | 11.09.12 | 360,00р. | ||||
| Папирян | 11.09.12 | 315,00р. | 270,00р. | |||
| Вирт | 11.09.12 | 288,00р. | ||||
| Вирт | 11.09.12 | 396,00р. |
3. Вычислить над таблицей:
§ Всего заказов = общее количество по столбцу Номер заказа с помощью функции СЧЕТЗ.
§ на сумму = сумма по столбцу Сумма заказов, ячейке дать имя на_сумму.
§ Вычислить в столбцах:
§ Пометка о выполнении - вывести текст «Заказ вып», если Сумма выполнено больше или равна Сумма заказов. В противном случае вывести разницу этих сумм. Результат отобразить в формате денежный в рублях.
§ Доля выполнения заказа, % = Сумма выполнено / на_сумму.
Результат вывести в процентном формате, два десятичных знака.
4. Построить по исходной таблице смешанную диаграмму на двух осях. Сумму заказов в виде гистограммы, а Доля выполнения заказа - в виде линейного графика.
5. На листе Итоги получить количество заказов за каждый день.
6. На листе Фильтр с помощью расширенного фильтра выбрать заказы, выполненные в августе. Результат вывести в новой таблице:
| Номер заказа | Заказчик | Дата заказа |
7. Построить Сводную таблицу, в которой по месяцам вывести суммы заказано и выполнено. В сводной таблице вычислить разницу Сумма заказов – Сумма выполнено.
8. Подготовить и оформить средствами Word по результатам фильтрации серию рассылок уведомлений заказчикам.
Задание 16
1. На листе Справочник создать таблицу.
| Изделие | Стоимость |
| Изделие A | 400р. |
| Изделие B | 300р. |
| Изделие C | 500р. |
| Изделие D | 550р. |
2. На листе Дефицит создать таблицу.
Подготовить именованные блоки на листе Справочники для ввода данных на лист Дефицит. Ограничить ввод в столбцы Изделие и Период списками.
Анализ обеспеченности плана поставок
| Изделие | Период | План выпускаЦеха1, шт. | План выпускаЦеха2,шт. | План поставок,шт. | Дефицит,шт. | План поставки,руб. |
| Изделие | 1кв | |||||
| Изделие | 1кв | |||||
| Изделие | 2кв | |||||
| Изделие | 2кв | |||||
| Изделие | 1кв | |||||
| Изделие | 3кв | |||||
| Изделие | 1кв | |||||
| Изделие | 2кв | |||||
| Изделие | 3кв | |||||
| Изделие | 3кв |
3. Вычислить:
§ Дефицит. Если План поставок, шт больше суммарной величины планов выпуска Цеха1 и Цеха2, то подсчитать их разницу, в противном случае вывести цифру 0 (нуль).
§ План поставки, руб = План поставок, шт * Стоимость. Стоимость изделий получить с помощью функции ПРОСМОТР. Учесть в формуле, что в 3кв стоимость увеличилась на 5%.
4. На листе Итоги по каждому изделию получить суммарную величину Плана выпуска Цеха1, Плана выпуска Цеха2 и дефицита.
5. На основе итогов построить «нормализованную гистограмму с накоплением», отражающую суммарные величины Плана выпуска Цеха1, Плана выпуска Цеха2 и Дефицита по каждому Изделию. Дать название диаграмме «Анализ обеспеченности плана поставок», подписать значения.
6. На листе Фильтр с помощью расширенного фильтра выбрать из исходной таблицы данные, где План поставок превышает 1000 шт., но меньше 2000шт. Отфильтрованные записи поместить в новую таблицу, включающую столбцы:
| Изделие | Период | План поставок,шт. |
7. Построить Сводную таблицу, в которой по изделиям получить суммарные планы выпуска по каждому периоду.
8. Подготовить и оформить средствами WORD рассылку служебных записок соответствующего содержания по каждому случаю выявления дефицита на основе исходных данных Дефицит.
Задание 17
1. На листе Справочники создать таблицу.
| Предприятия | Амортизация |
| Ветерок | 4000,00р. |
| Огонек | 4000,00р. |
| Уют | 5000,00р. |
2. На листе БюджетРасходов оформить таблицу согласно образцу. Ячейке, содержащей 1,60%, дать имя ПроцПеремен.
Подготовить именованные блоки на листе Справочники для ввода данных на лист БюджетРасходов. Столбцы Предприятие и Месяц (6;7;8;9) заполнить в виде списка.
Бюджет коммерческих расходов холдинг "МИР ТЕПЛА"
| Процент переменных расходов: | 1,60% |
| Предприятия | Месяц | Планируемые продажи | Переменные расходы | Постоянные расходы | Общий план расходов |
| Ветерок | 3279 035р. | ||||
| Уют | 1595 630р. | ||||
| Огонек | 2802 685р. | ||||
| Уют | 2081 088р. | ||||
| Ветерок | 2 837 399р. | ||||
| Огонек | 4 261 210р. | ||||
| Ветерок | 3 354 650р. | ||||
| Уют | 3 757 380р. | ||||
| Огонек | 3 861 304р. | ||||
| Уют | 4 117 310р. | ||||
| Ветерок | 1 968 460р. | ||||
| Огонек | 5 565 225р. |
3. Вычислить:
§ Переменные расходы = Планируемые продажи * ПроцПеремен. Переменные расходы больше на 10%, если планируются 6 или 7 месяц.
§ Общий план = Переменные + Постоянные – Амортизация;
Значение амортизации, соответствующее предприятию, задать с помощью функции ВПР.
4. На листе Итоги для предприятий получить суммы расходов.
5. По результатам итогов построить круговую диаграмму, отображающую расходы предприятия «Уют».
6. На листе Фильтр с помощью расширенного фильтра выбрать строки, в которых общий план расходов больше планируемых продаж. Результат вывести в новой таблице:
| Предприятия | Месяц | Переменные расходы | Постоянные расходы |
7. Построить Сводную таблицу, в которой вывести суммы постоянных, переменных и общих расходов по месяцам. Обеспечить выбор данных по предприятиям.
8. Средствами Word подготовить и оформить рассылку писем предприятиям с данными результатов фильтрации.
Задание 18
1. На листе Справочники создать таблицы.
| Наименован. оборудования | Отдел | Коэффициент амортизации | |
| ПК | Технический | 2% | |
| Принтер | Экономический | ||
| Телефон | Производственный | ||
| Сканер | Охрана | Дата ведомости | |
| Факс | Технический | 07.06.07 |
2. На листе Оборудование создать таблицу. Отформатировать данные так, как показано в таблице. Стоимость представить в денежном формате.
Подготовить именованные блоки на листе Справочники для ввода данных на лист Оборудование. Поля Наименован. оборудования и Номер комнаты (200;201;202;203;204) заполнить в виде списков.
Поле Отдел заполнить с помощью функции ПРОСМОТР.
