Лабораторная работа 18 Комплексные задания

Лабораторная работа 18 Комплексные задания

Упражнение 1. Книжные магазины

1. Загрузите Excel.

2. Добавьте к имеющимся трем еще один лист.

3. Переименуйте листы следующим образом:

Лист 1 — В Выручка;

Лист 2 — В Себестоимость;

Лист 3 — В График;

Лист 4 — В Прибыль.

4. На лист Выручка занесите и оформите первую таблицу (рис. 1). Произведите следующие расчеты:

в столбце Всего за 6 лет, долл. — суммирование по строке;

в столбце Доля в общей выручке — вычисление доли каждого магазина в общей выручке в %;

в столбце Ранг — расстановка магазинов по местам, с помощью функции РАНГ;

Рис. 1. Выручка сети книжных магазинов

• в столбце Тенденция за последние 3 года должны быть следующие слова:

О Стабильный рост, если выручка за шестой год больше выручки за пятый, а выручка пятого больше, чем четвертого;

О Стабильное снижение, если выручка за шестой год меньше выручки за пятый, а выручка пятого меньше, чем четвертого;

О Неизменное состояние, если выручка на протяжении последних трех лет неизменна;

О Нестабильные изменения — во всех остальных случаях;

• в строке Итого — просуммируйте соответствующие столбцы.

5. На лист Себестоимость занесите и оформите вторую таблицу (рис. 2).

Рис. 2. Себестоимость

Произведите следующие расчеты:

в столбце Всего за 6 лет — суммирование по строке;

в столбце Среднегодовая себестоимость — вычислите среднее значение;

в столбцах Максимальная себестоимость и Минимальная себестоимость воспользуйтесь функциями МАКС и МИН;

в строке Итого — просуммируйте соответствующие столбцы.

6. На листе График постройте график выручки по годам для всех магазинов (рис. 3).

Рис. 3. График выручки магазинов

7. На лист Прибыль занесите и оформите третью таблицу (рис. 4).

Рис. 4. Прибыль магазинов

"Прибыль" рассчитывается по формуле: "Выручка" — "Себестоимость";

"Всего" и "Долю" рассчитайте аналогично первой таблице.

• На этом же листе расположить две диаграммы:

О круговую объемную — отражающую долю магазинов в общей при­были;

О объемную нормированную гистограмму для отражения прибыли магазинов по годам.

8. Сохраните работу в своей папке под именем Books.xls.

Сдайте работу преподавателю

Упражнение 2. Работники бюджетной сферы

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

Для выполнения задания строго следуйте нижеприведенному алгоритму.

1. Заработная плата работников бюджетной сферы исчисляется на основе Единой тарифной сетки, утвержденной Правительством Российской Федерации

2.

На листе 2 электронных таблиц Excel расположите вышеприведенную таблицу, а также таблицу с константами, которые будут необходимы для расчета заработной платы (табл. 1).

Таблица 1. Константы

Лист 2 переименуйте, назвав его константы. Перейдите на лист I и переименуйте его, назвав Зарплата, остальные листы книги удалите.

3. На листе Зарплата сформируйте таблицу Расчет заработной платы, состоящую из столбцов, имеющих заголовки:

· № п/п;

· Фамилия, инициалы работника;

· Должность;

· Разряд;

· Количество ставок;

· Процент доплат;

· Начисленная зарплата с районным коэффициентом;

· Количество льгот по подоходному налогу;

· Подоходный налог;

· Удержания в Пенсионный фонд;

· Заработная плата к выдаче;

· Доплаты из социальных фондов.

4. Заполните составленную таблицу на основании следующих данных:

Федоров А. В., терапевт, имеет 13-й разряд, работает на 1,5 ставки, установлена доплата 15% за выполнение общественных обязанностей,1 льгота по подоходному налогу;

Терехина В. М., повар, работает по 2-му разряду, имеет 2 ставки, доплат не имеет, 1 льгота по подоходному налогу;

Иванова А. В., санитарка, имеет 7-й разряд, 1,25 ставки, установлена
доплата в размере 15% за вредные условия труда, 1 льгота по подоходному налогу;

Борщев А. Н., программист, имеет 11-й разряд, 1 ставка, доплата в размере 12% за работу с излучающей аппаратурой, 2 льготы по подоходному налогу.

5. В заполненной таблице рассчитайте путем задания формул:

· начисленную зарплату с районным коэффициентом: (Тарифный коэффициент соответствующего разряда (с листа Константы) х Ставка первого разряда (с листа Константы) х Количество ставок + Величина доплат) х 1,15 (умножение всей суммы на 1,15 дает поправку на районный коэффициент, который составляет 15%);

· подоходный налог: (Начисленная зарплата с районным коэффициентом — Минимальная зарплата {с листа Константы) х Количество льгот по подоходному налогу) х 0,13;

· удержания в Пенсионный фонд: 1% от Начисленной зарплаты с районным коэффициентом;

· заработная плата к выдаче: Начисленная зарплата с районным коэффициентом - Подоходный налог - Удержаний в Пенсионный фонд;

6. Столбец Доплаты из социальных фондов заполните по условию:

· если Зарплата к выдаче меньше прожиточного минимума (с листа Константы), то Доплата из социальных фондов равна: Прожиточный минимум - Зарплата к выдаче;

· если наоборот, то Доплата из социальных фондов равна нулю.

7. К ячейкам столбца Зарплата к выдаче примените условное форматирование: Если Зарплата к выдаче меньше прожиточного минимума, то ячейка должна закрашиваться в красный цвет.

8. В построенной таблице подсчитайте общую сумму платежей по Подоходному налогу, общую сумму удержаний в Пенсионный фонд и общую величину Зарплаты к выдаче.

9. Отформатируйте ячейки, содержащие цифровые данные, измеряемые в денежном выражении, с точностью до 0,01 (до двух знаков после запятой).

10. По данным заполненной таблицы постройте столбиковую диаграмму, которая бы отображала фамилии сотрудников и величину заработной платы к выдаче.

11. Выполненную работу сохраните, назвав файл Money.xls.

Сдайте работу преподавателю

Упражнение 3. Директор кинотеатра "Аврора"

Предположим, вам предлагают стать директором кинотеатра "Аврора", но, сколько денег вы будете зарабатывать, говорить не спешат. Тогда вы решае­те провести свое маркетинговое исследование (рис. 5.).

1. Заполните зал (13 рядов по 25 мест в каждом) случайными цифрами от 1 до 4, кроме последнего ряда, который заполните цифрами 5. Цифры 1 и 2 будут обозначать посетителей, на которых распространяются скидки (С). Цифры 3 и 4 — обычных посетителей (О), а цифры 5 — "места для поцелуев" (Л, от англ. Loveseat). Сделайте так, чтобы после заполнения в ячейках рассадки зала остались только сами цифры, а не функция СЛЧИС().

Рис. 5. Кинотеатр "Аврора"

2. Формулы ф1— фЗ при помощи функции СЧЕТЕСЛИ подсчитывают в зале:

количество посетителей 1 и 2, т. е. количество льготников;

количество 3 и 4, т. е. обычных посетителей;

количество 5, т. е. влюбленных.

Формула 4 суммирует полученные числа, чтобы получить контрольную цифру 325.

Формулы ф5—ф9, исходя из известной стоимости билетов и подсчитанного количества зрителей, вычисляют выручку соответствующего сеанса. Предполагаем, что рассадка зала на всех сеансах одинакова.

Формула ф10 вычисляет выручку за день, формула ф11 — за месяц (принимаем, что в месяце 30 дней).

Исходя их выручки в рублях за месяц и процентов расходов по статьям, формулы ф12—ф17 вычисляют денежные расходы на соответствующие статьи затрат.

Формулы ф18—ф23 показывают, сколько эти суммы будут значить в долларах при известном заранее курсе.

Формула ф24 определяет, что если директор в результате получает больше 3000 долларов в месяц, то мы согласны быть директором, т. е. в ячейке А26 должно появиться слово "Да" или "Нет". Это и будет итогом работы.

3. Постройте столбиковую объемную диаграмму расходов. Столбики должны быть в виде цилиндров.

4. Сохраните работу под именем Director.xls.

Сдайте работу преподавателю

Упражнение 4. Антреприза

Задание, аналогичное Упражнение 3, но более самостоятельное.

Исходные данные:.

Московский театр "Ленком" привез в Санкт-Петербург антрепризный спек­такль Олега Меншикова "Ревизор". Гастроли проходят в помещении БДТ.

Спектакль идет два вечера.

Цены на билеты:

· Партер — 1500 руб.

· Места за креслами — 1000 руб.

· Бельэтаж — 800 руб.

· 2-й ярус — 500 руб.

· 3-й ярус — 300 руб.

1. Представьте зал в виде квадрата 10x10, заполненного случайными цифрами от 0 до 9, которые обозначают:

цифры 1, 2, 3, 4 и 5 — зрителей партера;

цифра 6 — места за креслами;

7 и 8 — бельэтаж;

9 — 2-й ярус;

0 — 3-й ярус;

Каждая цифра обозначает 10 зрителей (всего их должно быть 1000). Курс доллара примите равным 31 руб. за 1 доллар. Курс евро примите равным 27 руб. за 1 евро.

2. Вычислите выручку за оба вечера в рублях, евро и долларах.

3. Произведите расчет отчислений в рублях, евро и долларах, если известно,

что:

· на аренду помещения уходит 5% выручки;

· на организацию гастролей 25%;

· на рекламу 10%;

· на налоги 13%;

· на зарплату актерам 20%;

· на зарплату обслуживающему персоналу 7%;

· транспортные расходы 3%;

· непредвиденные расходы 2%;

· продюсеру— 15%.

4. Оформите работу в соответствии с рекомендациями.

5. Аналогично, используя функцию ЕСЛИ, согласитесь быть продюсером
только при получении дохода более 5000 долларов.

6. Постройте круговую объемную диаграмму затрат.

7. Сохраните документ под именем Bdt.xls.

Сдайте работу преподавателю

Дата добавления: 2015-08-29