2015-10-22
1175
Работу по подготовке таблицы рекомендуется выполнять в следующей последовательности:
1. в табличном процессоре MS Excel самостоятельно построить таблицу и занести в нее исходные данные в соответствии с вариантом;
2. оформить таблицу в соответствии со своим вкусом, но обязательно должны быть границы и заливка ячеек;
3. текст таблицы должен быть набран шрифтом Times New Ronan, размер 12 пт. Выравнивание текста в ячейках таблицы должно быть по центру, как по горизонтали, так и по вертикали;
4. с помощью справки выяснить назначение и правила создания относительных и абсолютных ссылок на ячейки таблицы (раздел «Ссылки на ячейки и диапазоны ячеек») и при дальнейших вычислениях использовать их в формулах;
5. отобразить расчетную таблицу в режиме отображения формул;
6. все расчеты в основной и вспомогательных таблицах автоматизированы и осуществляются с помощью формул и функций, например СУММ(), ЕСЛИ(), СРЗНАЧ(), МАХ(), МИН() и т.п.;
7. все ячейки, в которых производятся вычисления по формулам необходимо выделить желтым цветом (не должно совпадать с общим цветом ячеек таблицы);
|
|
|
8. выделить ячейки, в которых производятся вычисления, и задать имя диапазона – «вычисления»;
9. на втором листе рабочей книги создать диаграмму. У диаграммы должны присутствовать: название, подписи осей, X и Y, подписи данных, легенда;
10. оформить диаграмму следующим образом: цвет области диаграммы отличный от белого, цвет области построения – контрастен цвету области диаграммы, размещение легенды – внизу, способ заливки рядов данных – узор;
Вариант 1.
В таблице представлены данные о реализуемых товарах:
| Наименование | Категория | Срок реализации (дни) | Кол-во продаваемой продукции | [ 1 ] | [ 2} |
| Товар 1 | |||||
| Товар 2 | |||||
| Товар 3 | |||||
| Товар 4 | |||||
| Товар 5 | |||||
| Товар 6 | |||||
| Товар 7 | |||||
| Товар 8 | |||||
| В среднем |
| Цена товара 1 категории | 2500р. |
| Цена товара 2 категории | 1250р. |
Порядок расчета:
[ 1 ] – Стоимость продаваемой продукции (CПП) = Цена * Количество (использовать абсолютную адресацию ячеек);
[ 2 ] – Скидка на срок реализации составляет 7% от CПП, если срок реализации менне 5 дней, 3% если срок реализации от 5 до 30 дней, и скидка не предоставляется, если срок реализации более 30 дней. Используется логическая функция ЕСЛИ
· Заполнить пустые строки и столбцы
· Построить гистограмму для сравнения скидок на разные товары, по оси Х – наименования товаров, по правой оси У – срок реализации. Выделить точки графика, соответствующие максимуму и минимуму особым маркером.
|
|
|
Вариант 2.
В таблице представлены разряды по должностям:
| Должность | Разряд | Тарифная ставка |
| Директор | ||
| Шеф-повар | ||
| Повар | ||
| Официант | ||
| Уборщица | ||
| В среднем | ||
| Максимум | ||
| Минимум |
| Разряд | |||||
| Тарифный коэффициент. | 1,3 | 1,69 | 1,91 | 2,26 | 3,36 |
| Минимальная з/плата | 200р. |
Порядок расчета:
Тарифная ставка = Тарифный коэффициент *4*Минимальная з/плата
Используется логическая функция ЕСЛИ
· Заполнить пустые строки и столбцы
· Построить гистограмму для сравнения тарифных ставок по всем видам должностей. Выделить точки графика, соответствующие максимуму и минимуму особым маркером.
Вариант 3.
В таблице представлен расчет стоимости товаров:
| Наименование фирмы | Цена товара без скидки | Сорт | Скидка | Цена товара со скидкой |
| Фирма1 | ||||
| Фирма 2 | ||||
| Фирма 3 | ||||
| Фирма4 | ||||
| Фирма 5 | ||||
| В среднем | ||||
| Максимум | ||||
| Минимум |
Порядок расчета:
Скидка = 10% от цены, если сорт =1; 15%, если сорт =2; 20%, если сорт = 3
Используется логическая функция ЕСЛИ
Цена товара со скидкой = Цена товара без скидки – Скидка
· Заполнить пустые строки и столбцы
· Построить диаграмму, отражающую цену товара со скидкой и без скидки. Выделить точки графика, соответствующие максимуму и минимуму особым маркером.
Вариант 4.
В таблице представлен расчет операций с акциями:
| Наименование акции | Стартовая цена | Количество акций | Добавочная стоимость | Цена акции |
| Акции 1 | ||||
| Акции2 | ||||
| Акции3 | ||||
| Акции4 | ||||
| Акции5 | ||||
| В среднем | ||||
| Максимум | ||||
| Минимум |
Порядок расчета:
Добавочный коэфф. = 0,5 если количество акций <40 или стартовая цена <150;
0,6 в прочих случаях.
Используется логическая функция ЕСЛИ
Добавочная цена = Стартовая цена * Добавочный коэффициент
Цена акции = Стартовая цена + Добавочная цена
· Заполнить пустые строки и столбцы
· Построить диаграмму по выбору, отражающую стартовую цену и добавочную цену акций.
Вариант 5.
В таблице представлен расчет цены выпускаемых товаров:
| Наименование товара | Стоимость материалов | Коэфф. затрат труда | Стоимость работы |
| Товар 1 | |||
| Товар 2 | |||
| Товар 3 | |||
| Товар 4 | |||
| Товар 5 | |||
| В среднем | |||
| Максимум | |||
| Минимум |
| Амортизация оборудования товара 1,2,3 | |
| Амортизация оборудования товара 4,5 |
Порядок расчета:
Коэфф. затрат труда = 0,8, если стоимость материалов <150 и 0,9 в прочих случаях.
Используется логическая функция ЕСЛИ
Стоимость работы = (Стоимость материалов + амортизация оборудования)*коэфф.затрат труда
· Заполнить пустые строки и столбцы
· Построить диаграмму по выбору, отражающую стоимость материалов, стоимость работы, отметить на диаграмме максимальные и минимальные значения.
Вариант 6.
Проанализировать динамику поступления товаров от поставщиков:
| Поставщики | 2009 г. (млн.руб) | 2010 г. (млн.руб) | Превышение (млн.руб) | В % к 2009г. | Уд. вес в 2009г. | Уд. вес в 2010г. | Изменение уд. веса |
| СП «Изотоп» | 16,6 | 16,9 | |||||
| АОЗТ «Чипы» | 23,4 | 32,1 | |||||
| ООО «Термо» | 0,96 | 1,2 | |||||
| АО «Роника» | 7,5 | 6,4 | |||||
| СП «Левел» | 16,7 | 18,2 | |||||
| Всего |
Порядок расчета:
Изменение удельного веса определяется исходя из следующего:
|
|
|
«равны», если уд.вес 2010 г.равен уд.весу 2009 г.
«больше», если уд.вес 2010 г.больше уд.весу 2009 г.
«меньше», если уд.вес 2010 г.меньше уд.весу 2009 г.
Для заполнения столбца Изменение удельного веса используйте функцию ЕСЛИ из категории Логические
· Заполнить пустые строки и столбцы
· Построить объемную гистограмму динамики удельного веса поступления товаров в 2009-2010гг.по поставщикам.
Вариант 7.
Провести анализ поступления средств во вклады коммерческих банков:
| Банк | Группа | Наименование банка | Остаток на конец года млн. | Остаток на начало года млн. | Поступлен. во вклады млн. руб. | Уровень оседания средств, % |
| Банк 1 | ||||||
| Банк 2 | ||||||
| Банк 3 | ||||||
| Банк 4 | ||||||
| Банк 5 | ||||||
| В среднем | ||||||
| Максимум | ||||||
| Минимум |
Порядок расчета:
Уровень оседания = (Остаток на конец – Остаток на начало) /Поступления.
Результат вычислений отобразить в процентах
Наименование банка = «Промышленный»,если Группа =1;
«Сбербанк», если Група =2;
«Строительный», если Группа =3
Для заполнения столбца Наименование банка используйте функцию ЕСЛИ из категории Логические
· Заполнить пустые строки и столбцы
· Построить графическую диаграмму, отражающую Уровень оседания средств каждого банка в %.
Вариант 8.
Заполнить таблицу формирования цен:
| Артикул товара | Оптовая цена (руб) | Розничная цена (руб) | Цена со скидкой (руб) | Ценовая категория |
| 23456А | ||||
| 78543В | ||||
| 98457С | ||||
| 125888А | ||||
| 45126А | ||||
| В среднем | ||||
| Максимум | ||||
| Минимум |
| Коэффициент опта | 0,1 |
| Коэффициент скидки | 0,15 |
Порядок расчета:
Розничная цена = Оптовая цена*Коэффициент опта
Цена со скидкой = Розничная цена*Коэффициент Скидки
Ценовая категория определяется исходя из следующего:
|
|
|
«нижняя», если розничная цена ниже 2000;
«средняя», если розничная цена находится в пределах от 2000 до 5000;
«высшая», если розничная цена выше 5000;
Для заполнения столбца Ценовая категория используйте функцию ЕСЛИ из категории Логические
· Заполнить пустые строки и столбцы
· Построить объемную гистограмму, отражающую оптовые и розничные цены по каждому виду товаров.
Вариант 9.
Рассчитать сумму вклада с начисленным процентом. Результаты округлить до 2-х знаков:
| № лицевого счета | Вид вклада | Остаток входящий (тыс.руб) | Приход (тыс.руб) | Расход (тыс.руб) | Остаток исходящий (тыс.руб) | Остаток вклада с начисленным % |
| S3445 | Срочный | |||||
| F7892 | Праздничный | |||||
| I4877 | До востребования | |||||
| B1285 | Срочный | |||||
| H3322 | Праздничный | |||||
| Итого | ||||||
| Максимум | ||||||
| Минимум |
Порядок расчета:
Остаток вклада с начисленным % рассчитывается исходя из следующего:
• Остаток исходящий + 2% от Остатка исходящего, доя вклада до востребования;
• Остаток исходящий +5% от Остатка исходящего, для вклада праздничный;
• Остаток исходящий + 3% от Остатка исходящего, для вклада срочный.
Для заполнения столбца Остаток вклада с начисленным % используйте функцию ЕСЛИ из категории «Логические».
· Заполнить пустые строки и столбцы
· Постройте объемную гистограмму изменения суммы вкладов.
Вариант 10
1. Рассчитать начисленную заработную плату сотрудникам малого предприятия.
| №п/п | Ф.И.О. | Дата поступления на работу | Стаж работы | Надбавка (руб.) | Премия (руб.) | Всего 1 начислено (руб.) |
| 1. | Моторов А.А. | 10.04.91 | ||||
| 2. | Унтура О.И. | 12.06.98 | ||||
| 3. | Дискин Г.Т. | 02.03.93 | ||||
| 4. | Попова С.А. | 17.02.92 | ||||
| 5. | Скатт О.И. | 15.01.99 | ||||
| Итого | ||||||
| Максимум | ||||||
| Минимум |
| Зарплата | 10000р. |
Порядок расчета:
| (Текущая дата - Дата поступления на работу) / 365. |
Стаж работы (полное число лет)= Результат округлите до целого.
Надбавка рассчитывается исходя из следующего:
• 0% если стаж работы меньше 5 лет;
• 5% от зарплаты, если стаж работы от 5 до 10 лет..
• 10% от зарплаты, если стаж работы больше 10 лет.
Для заполнения столбца Надбавка используйте функцию ЕСЛИ из категории «Логические».
Премия = 20% от (Зарплата + Надбавка).
· Заполнить пустые строки и столбцы
· Постройте объемную гистограмму начисления зарплаты посотрудникам.
Вариант 11
Заполните ведомость по налогам сотрудников предприятия.
| №п/п | ФИО | Всего начислено, руб. | Пенсионный фонд, руб. | Налогооблагаемая база, руб. | Налог, руб. |
| Иванов А.Л. | |||||
| Иванов СП. | |||||
| Дутова О.П. | |||||
| Карпов А.А. | |||||
| Клыков О.Н. | |||||
| Львов Г.В. | |||||
| Миронов A.M . | |||||
| Итого | |||||
| Максимум | |||||
| Минимум |
Порядок расчета:
Пенсионный фонд = 1% от «Всего начислено».
Налогооблагаемая база = Всего начислено - Пенсионный фонд.
Итого = сумма по столбцам Всего начислено, Пенсионный фонд и Налог.
Налог определяется исходя из следующего:
• 12% от налогооблагаемой базы, если налогооблагаемая база меньше 1000 руб.;
• 20% от налогооблагаемой базы, если налогооблагаемая база больше 1000 руб.
Для заполнения столбца Налог используйте функцию ЕСЛИ из категории «Логические».
· Заполнить пустые строки и столбцы;
· Постройте объемную круговую диаграмму начислений по сотрудникам
Вариант 12
Объем реализации товара
| № магазина | Товар 1 | Товар 2 | Товар 3 | Объем реализации, тыс, руб | Комиссионные, тыс. руб | Удельный вес,% |
| Магазин № 15 | ||||||
| Магазин № 47 | ||||||
| Магазин № 30 | ||||||
| Магазин № 45 | ||||||
| Магазин № 56 | ||||||
| Итого | ||||||
| Среднее значение |
Порядок расчета:
Объем реализации = СУММ (Товар 1: Товар3)
Удельный вес = Объем реализации каждого магазина / Итого объема (формат ячеек процентный)
Комиссионные определяются исходя из следующего:
2%, если объем реализации менее 300 тыс. руб.
5%, если объем реализации более 300 тыс. руб.
Для заполнения столбца Комиссионные используйте функцию ЕСЛИ из категории «Логические».
· Заполнить пустые строки и столбцы
· Построить диаграмму по выбору, отражающую № магазина и его удельный вес.
Вариант 13
5 крупнейших компаний России по объему реализации продукции в 2000г.
| Компания | Объем реализации, млн.руб | Прибыль после налогообложения, млн.руб | Уровень рентабельности, % | Характеристика рентабельности |
| НК «Лукойл» | 26807,0 | 30795,0 | ||
| ОАО «Сургутнефтегаз» | 80827,0 | 30931,9 | ||
| РАО «Норильский никель» | 66819,0 | 36716,4 | ||
| НК «Юкос» | 52013,0 | 6265,3 | ||
| АвтоВАЗ | 47999,0 | 1686,6 | ||
| Средний уровень рентабельности | ||||
| Максимальная прибыль |
Порядок расчета:
Уровень рентабельности = Прибыль после налогообложения / Объем реализации (процентный формат ячеек)
Характеристика рентабельности:
«средняя», если Уровень рентабельности, до 30%
«высокая», если Уровень рентабельности выше 30%
Для заполнения столбца Характеристика рентабельности используйте функцию ЕСЛИ из категории «Логические».
· Заполнить пустые строки и столбцы
· Построить диаграмму по выбору, отражающую основные показатели компаний.
Вариант 14
Некоторые крупнейшие компании России по рыночной стоимости (капитализации) на 1 сентября 2003г.
| ОАО «Сургутнефтегаз» | Капитализация компании, млн руб. | Цена (котировка) обыкновенной акции, долл. | Число обыкновенных акций, шт. | Оценка котировки |
| НК «Лукойл» | 0,3865 | |||
| ОАО «Газпром» | 16,852 | |||
| НК «Юкас» | 1,5944 | |||
| Мобильные телесистемы | 1,4258 | |||
| Ростелеком | 0,3825 | |||
| Аэрофлот | 2,6978 | |||
| Максимальная цена, долл. | ||||
| Средняя цена, долл | ||||
| Минимальная цена, долл |
| Курс ЦБ на 01.09.2000г. | 27,75р. |
Порядок расчета:
Капитализация компании = Число обыкновенных акций / Цена* Курс ЦБ/ 1000000
Оценка котировки акций:
«спад», если цена котировки устанавливается ниже отметки 1;
«подъем», выше отметки 10;
«стабильно – на отметке от 1 до 10.
Для заполнения столбца Оценка котировки акций, используйте функцию ЕСЛИ из категории «Логические».
· Заполнить пустые строки и столбцы
· Построить диаграмму, отражающую уровень капитализации компаний.
·
Вариант 15
Выполнить анализ основных показателей финансово-экономической деятельности промышленных предприятий по данным, приведенным в таблице.
| Классы предприятий по основным фондам, млрд руб. | Предприятия, кол-во | Товарная продукция, объем,млрд руб. | Численность, тыс.чел. | Место по объему товарной продукции |
| 52,895 | 4,359 | |||
| 488,22 | 22,64 | |||
| 360,25 | 78,25 | |||
| 680,25 | 45,11 | |||
| 711,78 | 69,22 | |||
| 812,36 | 88,21 | |||
| 1068,33 | 12,77 | |||
| Максим. значение | ||||
| Миним. значение | ||||
| Итого |
Порядок расчета:
Количество предприятий будет равным:
57, если Классы предприятий по основным фондам, (млрд.руб) меньше или равно 10
28, если Классы предприятий по основным фондам, (млрд.руб) больше 10, но меньше 100
8, если Классы предприятий по основным фондам, (млрд.руб) больше 100
Место каждого предприятия по объему товарной продукции:
1 место, если объем больше 1000 млрд.руб;
2 место, если объем от 600 до 1000 млрд.руб.
1 место, если объем меньше 600 млрд.руб.
· Заполнить пустые строки и столбцы
· Построить диаграмму, отражающую объемы товарной продукции по классам.
