Упражнение № 6 Сводные таблицы

Цель работы:

1. Освоить процедуру анализа информации таблицы методом построения сводных таблиц.

2. Получить навыки определения полей сводной таблицы,

Задание на самоподготовку:

Изучить материал по построению сводной таблицы и составить алгоритмы выполнения операций:

1. Построению сводной таблицы, задания ее полей;

2. Смены полей сводной таблицы;

3. Построения диаграммы по результатам сводной таблицы.

Выполнение работы:

1. Создать таблицу поставки стройматериалов на объект с графами: дата, поставщик, материал, стоимость единицы материала, стоимость поставки и ввести 35-40 записей поставки бетонных блоков, щебня, раствора.

2. ввести формулы расчета разовых поставок.

3. Построить по результатам созданной таблицы сводную таблицу. В качестве полей задать по строкам – поставщик, по столбцам – материалы, результатами должны быть стоимости поставок. Провести оценку поставки материалов отдельными поставщиками.

4. Изменить поля, введя в строки материалы, а в столбцы – поставщиков. Как измениться информация таблицы? Какая часть поставки окажется подчеркнутой?

5. Удалить из сводной таблицы информацию об одном из поставщиков, используя ниспадающий список в столбце.

6. Восстановить сводную таблицу. Добавить в столбцы даты поставок. Как изменится таблица?

7. Убрать из сводной таблицы информацию о поставщиках. Что теперь покажет сводная таблица?

Упражнение №7 Поиск решения. Анализ деятельности предприятия

Цель работы:

Закрепить навыки создания и заполнения таблиц по словесному описанию их содержания.

2. Освоить программу Поиска решения, задания условий поиска, сохране6ния результатов поиска в качестве сценария.

Задание на самоподготовку:

Изучить материал по теме создания таблиц, заполнения их данными, поведения расчетов в них и поиска решения. Составить алгоритмы процедур:

1. Создания таблицы;

2. Заполнения таблицы данными с использованием копирования данных;

3. Создание и копирование формул в таблицах с использованием относительных и абсолютных адресов;

4. Определение условий поиска, зависимых ячеек и ячеек результата;

5. Сохранение результатов поиска в качестве сценария.

Выполнение работы

1. Создать таблицу, отображающую результаты хозяйственной деятельности предприятия

В строках таблицы разместить следующие показатели: сезонный фактор, объем сбыта, доход от оборота, себестоимость реализованной продукции, валовая прибыль, затраты на зарплату, затраты на рекламу, накладные расходы, валовые издержки, прибыль, коэффициент прибыльности, цена, себестоимость.

В столбцах таблицы разместить значения этих показателей для каждого квартала, а также суммарные значения за год.

Формулы и константы для расчетов:

Объем сбыта=35*Сезонный фактор *Затраты на рекламу+3000.

Доход от оборота=Объем сбыта*Цена.

Себестоимость реализованной продукции=Объем сбыта*Себестои-мость.

Валовая прибыль=Доход от оборота*Себестоимость реализованной продукции.

Накладные расходы=15% дохода от оборота.

Валовые издержки=затраты на зарплату+затраты на рекламу+накладные расходы.

Прибыль=Валовая прибыль–Валовые издержки.

Коэффициент прибыльности=Прибыль/Доход от оборота.

Сезонный фактор: для I квартала– 0,9; для II – 1,1; для III – 0,8; для IV квартала – 1,2.

Затраты на зарплату: для I квартала – 8000 р.; для II – 8000 р.; для III – 9000 р.; для IV квартала –9000 р.

Затраты на рекламу для каждого квартала – по 10000 р.

Цена – 40 р.; себестоимость – 25 р.

2. Отформатировать таблицу: ячейкам, содержащим денежные величины, назначить денежный формат; ячейкам строки Коэффициент прибыльности назначить процентный формат; расчертить таблицу линиями.

3. С помощью программы Поиск решения определить величину затрат на рекламу, обеспечивающую максимальную прибыль в 1 квартале. Сохранить установки задачи в виде модели. После просмотра результата восстановить исходные значения. Объяснить полученный результат.

4. Определить величину затрат на рекламу для каждого квартала, обеспечивающую максимальную прибыль за год. Сохранить установки задачи в виде модели. После просмотра результата восстановить исходные значения.

5. Определить величину затрат на рекламу для каждого квартала, обеспечивающую максимальную прибыль за год при ограничении суммарной величины расходов на рекламу за год 40000 р. Объяснить полученный результат. Сохранить установки задачи в виде модели. После просмотра результата восстановить исходные значения.

6. Изменить ограничение, установив верхний предел расходов та рекламу за год 50000 р. Сохранить установки задачи в виде модели. После просмотра результата восстановить исходные значения. Объяснить причины не схождения результатов поиска в двух последних случаях.

7. Сохранить в качестве сценария первоначальные значения величин затрат на рекламу в каждом квартале. Установить сезонный фактор каждого квартал равным 1 и провести поиск решения. Почему в последнем случае решение удалось найти, а в двух предпоследних случаях решения не оказалось?

10. Восстановить первоначальные значения с помощью первого сценария.