Задание по MS Excel

Тема работы: Копирование формул. Фильтры. Сортировка данных.

Задание 1. Создайте таблицы по образцу; заполните пустые ячейки таблицы 2 (в формулах свяжите ячейки обеих таблиц); оформите таблицы; отсортируйте данные таблицы 2 по фамилиям в порядке возрастания; выберите из таблицы 2 тех, чья доля составляет больше 40 %; результат работы сохраните в файле.

Задание 2. Сравнить доходную часть городского бюджета в 2005 и 2006 году.

Задание 3. Рассчитать поступление и расходование денежных средств избирательных фондов зарегистрированных кандидатов в депутаты на должность главы администрации.

Задание 4. Рассчитать доход от реализации колбасных изделий.

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

Примечание 1. Восстановительная полная стоимость объекта (ВП) и восстановительная остаточная стоимость объекта (ВО) вычисляются по балансовой стоимости (БС) и износу объекта (ИО):

ВП = БС * К ;   ВО = ОС * К, 

где К=3,0 ,  если БС>500; К=2,0, если БС<=500.

Примечание 2. Для заполнения столбцов использовать функцию ЕСЛИ.

Задание 6. Рассчитать структуру розничной цены продукции исходя из следующих данных:

Задание 7. Рассчитать оклад в тенге, премию в %, премию в тенге, итог, налог, и к выдаче по следующим формулам:

ü Оклад (тг) рассчитывается исходя из оклада в долларах и курса доллара.

ü Процент премии рассчитывается так: если работник отработал все рабочие дни, то получает 100 % премии, если больше 14, но не 22, то 50%, во всех остальных случаях 25%.

ü Премия в тенге рассчитывается как оклад в тенге, умноженный на % премии.

ü Итого в тенге получается по формуле: (отработка * оклад / количество рабочих дней) + сумма премии.

ü Налог: если итог получается меньше 200 000  тенге, то 13% от итога, иначе 25%.

ü К выдаче: Итог-ИПН.

Задание 8. Завод изготавливает три вида продукции: соки, йогурты, мороженое. Затраты на изготовление этих продуктов составляют 70, 30, 20 тенге соответственно. Прибыль от реализации одного вида изделий составляет 20, 10, 5 тенге. Было изготовлено соков – 12546 л., йогуртов – 65234 кг., мороженого – 52623 кг.

При помощи таблицы рассчитать:

1.     Доход от реализации каждого вида изделий;

2.     Общую прибыль;

3.     Долю в процентах каждого вида изделий от общей прибыли;

4.     Какое изделие приносит больший доход предприятию. Использовать функцию MAX;

5.     Построить диаграмму по доходу от реализации одного вида изделия. Подписать диаграмму и значения;

6.     Отформатировать таблицу: задать границы, цвет, размер столбцов и строк.

Задание 9. Рассчитать:

ü Дивиденды, объявленные на год составляют Номинал акций * 450%.

ü Доходность по дивидендам составляет Дивиденды, объявленные на год / Цена продажи.

ü Среднюю цену продажи акций;

ü Максимальную цену продажи акций;

ü Минимальную цену продажи акций;

ü Максимальную доходность по дивидендам.

Построить диаграмму по доходности каждого из банков. Подписать диаграмму и значения.

Задание 10. На предприятии работники имеют следующие оклады : начальник отдела – 1000 руб., инженер 1 кат. – 860 руб., инженер – 687 руб., техник – 315 руб., лаборант – 224 руб. Предприятие имеет два филиала: в средней полосе и в условиях крайнего севера. Все работники получают надбавку 10% от оклада за вредный характер работы, 25% от оклада ежемесячной премии. Со всех работников удерживают 20% подоходный налог, 3% профсоюзный взнос и 1%  в пенсионный фонд. Работники филиала, расположенного в средней полосе, получают 15% районного коэффициента, работники филиала, расположенного в районе крайнего севера, имеют 70% районный коэффициент и 50% северной надбавки от начислений.

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

Требуется:

ü при помощи электронной таблицы рассчитать суммы к получению каждой категории работников;

ü построить две диаграммы, отражающие отношение районного коэффициента (районной и северной надбавки) и зарплаты для всех сотрудников обоих филиалов.

Задание 11. На книжную базу поступили 3 наименования книг: словари, книги по кулинарии и пособия по вязанию. Они были распределены по трем магазинам: «Книжный мир», «Дом книги» и «Глобус».

В «Книжный магазин» поступило словарей – 10400 экземпляров, кулинарных книг – 23650 экземпляров, пособий по вязанию – 1500 экземпляров; в «Дом книги» - 10300 словарей, 22950 кулинарных книг и 1990 пособий по вязанию; в «Глобус» соответственно 9100, 23320 и 2500 экземпляров.

В первом магазине было продано словарей – 8945 экземпляров, кулинарных книг – 19865 экземпляров, пособий по вязанию – 873 экземпляра; во втором магазине было продано словарей – 9300 экземпляров, кулинарных книг – 21900 экземпляров, пособий по вязанию – 1020 экземпляра; в третьем магазине соответственно было продано 8530, 18100 и 2010 экземпляров.

Требуется:

ü при помощи электронной таблицы рассчитать:

Ø общее количество книг каждого наименования поступивших на книжную базу;

Ø процент продажи каждого наименования книг в каждом магазине;

Ø количество книг, оставшихся после реализации;

ü построить диаграмму по распределению книг в магазинах.  

Задание 12. Рассчитать ежедневный предполагаемый доход от деятельности киносети.

Задание 13. Рассчитать среднюю стоимость 1 кв. м. общей площади жилых помещений в городах Казахстана.

Задание 14. Рассчитать и показать на графике структуру кредитных вложений коммерческого банка в зависимости от их обеспечения.

Для решения задачи используется следующая входная информация:

ü ссуды под залог ценных бумаг

ü ссуды под залог товарно-материальных ценностей

ü ссуды под залог валютных ценностей

ü ссуды под залог нематериальных активов

ü ссуды под залог долговых требований

ü гарантированные ссуды

ü застрахованные ссуды

ü ссуды без обеспечения

В результате решения задачи необходимо сформировать следующий входной документ:

Структура кредитных вложений коммерческого банка

1.     Формулы для расчета выходных показателей имеют следующий вид:

SS = SUM(S(I)), где S(I) – сумма i-ой ссуды;

U(I) = S(I) / SS, U(I) – удельный вес i-ой ссуды;

I = [1,N], где N – количество видов предоставляемых ссуд.

2.     Выполнить сортировку документа по возрастанию объектов вложения коммерческого банка.

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

4.     Построить на отдельном листе смешанную диаграмму, в которой суммы объемов каждого вида ссуды были бы представлены в виде гистограмм, а их удельные веса в виде линейного графика на той же диаграмме. Вывести легенду и название графика «Анализ кредитных вложений банка».

5.     Сформировать новый выходной документ, содержащий только те кредитные вложения банка, объем ссуд которых больше среднего значения этого показателя по всей таблице (использовать функцию СРЗНАЧ, выполнить фильтрацию, результаты скопировать). Выходной документ должен иметь следующий вид:

6.     На основании исходного документа Структура кредитных вложений коммерческого банка сформировать следующий документ:

7.     Подготовить результаты расчетов и диаграммы к выводу на печать.