Часть 2. Анализ и управление данными. Работа с массивами

Часть 1. Создание, редактирование, форматирование таблицы. Построение диаграмм

Создание таблицы

1. Создать таблицу оценки работы предприятия за год в соответствии с образцом (рис. 16). Ввести в таблицу следующие названия столбцов: Месяц, Объем реализации (шт.), Постоянные затраты на объем реализации, Переменные затраты на ед. продукции, Критический объем реализации, Выручка от реализации, Порог рентабельности, Запас финансовой прочности, Маржа безопасности (шт.), Показатель состояния предприятия.Ввести 10 записей.

Рис. 16

2. Столбец Месяцзаполнить названиями месяцев с января по декабрь с помощью маркера заполнения.

3. Ввести исходные данные в столбцы Объем реализациии Переменные затраты на ед. продукции.

4. За пределами таблицы ввести константы: Постоянные затраты на ед. продукции =100р., Цена ед. продукции = 386р.

5. Значения остальных столбцов рассчитать по формулам:

- Постоянные затраты на объем реализации = Постоянные затраты на ед. продукции * Объем реализации;

- Критический объем реализации = Постоянные затраты на объем реализации / (Цена ед. продукции - Переменные затраты на ед. продукции);

- Выручка от реализации = Цена ед. продукции * Объем реализации;

- Порог рентабельности = Цена ед. продукции * Критический объем реализации;

- Запас финансовой прочности = Выручка от реализации - Порог рентабельности;

- Маржа безопасности = Объем реализации - Критический объем реализации;

- В ячейки столбца Показатель состояния предприятияввести текст «прибыль» или «убыток», используя функцию ЕСЛИ. Предприятие прибыльно, если имеется запас финансовой прочности.

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

Форматирование таблицы

7. Отформатировать таблицу:

- для ячеек первой строки («шапки») таблицы задать вертикальную ориентацию текста, длинные заголовки столбцов перенести в ячейке по словам;

- изменить шрифт первой строки на жирный, выполнить заливку бледно-голубым цветом с использованием узора, расположить текст по центру;

- используя условное форматирование, в столбце Показатель состояния предприятия значение «убыток» вывести красным цветом полужирным начертанием, значение «прибыль» - синим цветом курсивным начертанием;

- присвоить ячейкам, содержащим денежные величины, денежный формат;

- добавить две - три строки для заголовка таблицы и ввести заголовок как объект WordArt c текстом «Оценка работы предприятия»;

- отменить вывод на экран сетки таблицы. Расчертить таблицу линиями: внешнюю рамку таблицы – жирной синей линией, «шапку» таблицы подчеркнуть двойной черной линией, столбцы и строки – тонкими черными линиями;

- в ячейку с названием одного из месяцев, когда предприятие было убыточно, добавить примечание «Работа с убытком»;

- присвоить листу имя Оценка_работы.

Построение диаграмм

8. На отдельном листе построить объемный вариант обычной гистограммы. В качестве рядов использовать данные из столбцов Выручка от реализациииПорог рентабельности, в качестве категорий – данные из столбца Месяц. Добавить в диаграмму ряд Запас финансовой прочности посредством указания нового диапазона данных.

9. Ввести в диаграмму стандартные тексты: легенду, заголовки, подписи данных для отрицательных значений столбцаЗапас финансовой прочности. Выполнить заливку стенок диаграммы, выбрав в качестве заливки текстуру «Полотно». Присвоить листу имя Диаграмма_Результаты. Изменить цвет ярлычка листа.

10. Создать внедренную объемную разрезанную круговую диаграмму, отображающую распределение выручки от реализации по месяцам. Задать подписи данных в долях. Легенду расположить слева. Выполнить градиентную диагональную заливку области диаграммы.

11. Сохранить файл под именем Фамилия_№ задания.xlsx.

Часть 2. Анализ и управление данными. Работа с массивами

Подбор параметра

12. Используя инструмент Подбор параметра, рассчитать:

- при каком значении переменных затрат запас финансовой прочности в июне достигнет 10000р;

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

Таблица подстановки

13. При помощи таблицы подстановки данных проанализировать зависимость запаса финансовой прочности в июне от переменных затрат на ед. продукции (подставить значения 240, 260, 280, 300, 320р.). Исходные значения выделить цветом.

14. Сохранить файл под именем Фамилия_№ задания_2.xlsx.