Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:
Сборник финансовых задач.doc
Скачиваний:
29
Добавлен:
17.12.2018
Размер:
7.8 Mб
Скачать

ФЕДЕРАЛЬНОЕ АГЕНСТВО ПО ОБРАЗОВАНИЮ

Государственное образовательное учреждение высшего

профессионального образования

«Нижегородский государственный университет

им. Н.И. Лобачевского»

Сборник финансовых задач

для табличного процессора EXCEL

Пособие для практических занятий

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

Нижний Новгород

2006

УДК 004.912(075)

ББК 973.2я7

С-17

Самойлова К.И. Сборник финансовых задач для табличного процессора EXCEL: Пособие для практических занятий - Нижний Новгород: Издательство Нижегородского госуниверситета, 2006. – 36 с.

Рецензент: Хворенков С.Г., к.э.н., доцент каф. экономической информатики экономического факультета ННГУ

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

Для проверки знаний студентов приводятся контрольные задания по вышеперечисленным темам, а также задание с использованием информации из сети Internet.

Пособие рекомендуется для студентов всех специальностей и форм обучения финансового факультета ННГУ им. Н.И. Лобачевского.

УДК 004.912(075)

ББК 973.2я7

© Нижегородский государственный университет им. Н.И. Лобачевского, 2006.

СОДЕРЖАНИЕ

Задание 1. Построение простой электронной таблицы 4

Задание 2. Форматирование таблицы 6

Задание 3. Использование статистических и математических функций 7

Задание 4. Ввод и обработка данных в формате ДАТА – ВРЕМЯ 11

Задание 5. Подбор параметра 13

Задание 6. Поиск решения 15

Задание 7. Консолидация данных 18

Задание 8. Сводные таблицы 20

Задание 9. Списки 22

Контрольные задания по теме

«Табличный процессор EXCEL»

Задание 1. Анализ дебиторской задолженности 26

Задание 2. Расчет возвратной суммы кредита 27

Задание 3. Консолидация по видам выпуска ГКО 28

Задание 4. Оптимизируемые модели 31

Задание 5. Расчет доходности учтенных векселей 32

Задание 6. Конвертация валют 35

Задание 1. Построение простой электронной таблицы

  1. Создайте свою папку. Запустите Excel.

  2. Начиная с клетки А1, создайте электронную таблицу по образцу (рис.1.). Сначала заполните строки 1,2 и колонку А. Все заголовки и фамилии вводите с первой позиции клетки. Колонку «Зарплата» заполните значениями в пределах от 7000 руб. до 20000 руб.

Рис. 1.

  1. При заполнении клеток C3, D3 используются формулы: C3 =B3*C$2 D3 =B3-C3

  1. Диапазоны клеток C3:C9 и D3:D9 заполняются путем копирования соответствующих формул. Для этого нужно выделить блок ячеек и вызвать операцию Правка – Заполнить – Вниз или с использованием мыши путем «протаскивания».

  2. Просмотрите формулы для всех сотрудников. Обратите внимание на автоматическое изменение некоторых адресов ячеек. Адрес какой ячейки не изменился? Почему? Обязательно найдите ответы на эти вопросы; при необходимости обратитесь к преподавателю.

  3. Клетка В11 рассчитывается по формуле =СУММ(В3:В10). В клетки С11, D11 эта формула копируется. Сохраните заполненную таблицу в своей папке под именем ZP1.XLS.

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

  5. Установите налог 15%. Сравните полученные итоговые данные с предыдущими значениями.

  6. Вставьте новые графы «Премия» и «Всего начислено» после графы «Зарплата». Самостоятельно задайте формулы для их вычисления, исходя из того, что премия составляет 40% от зарплаты, а «Всего начислено» – это «Зарплата» + «Премия». Отредактируйте все остальные формулы, руководствуясь задачей.

  7. Удалите одну строку из таблицы (сотрудник уволен). Проверьте формулы итоговой строки, обратите внимание на изменение диапазонов в формулах.

  8. Дополните таблицу еще тремя строками, включив их между, например, 5 и 6 строками (приняты три новых сотрудника). Заполните эти строки. Фамилии и зарплату введите, формулы – скопируйте.

  9. Вставьте перед колонкой «Налог» еще две колонки «Пенсионный фонд» и «Налогооблагаемая база». Установите, что в пенсионный фонд удерживается в размере 1% от начисленной зарплаты и премии. Отчисления в пенсионный фонд не входят в налогооблагаемую базу, то есть «Налогооблагаемая база» вычисляется как «Зарплата» + «Премия» – «Пенсионный фонд». Внесите все необходимые изменения в формулы.

  10. Измените алгоритм расчета подоходного налога с учетом прогрессивной шкалы налогообложения. Если налогооблагаемая база меньше определенной величины (например, 1200),то принимается ставка 12%, если больше – 20%. Формула должна использовать функцию ЕСЛИ. Как изменится формула, если шкала будет задана так: до 12000 – 12%, от 12000 до 25000 – 20%, больше 25000 – 25%?

  11. Проанализируйте полученные результаты.

  12. Вставьте перед колонкой «Фамилия» новую графу «Табельный номер» и заполните ее значениями: 100,101,102 и т.д. (Правка – Заполнить – Прогрессия).

  13. Под строкой «Итого» вставьте две строки для вычисления среднего и максимального значения начисленной и выданной зарплаты. Для этого воспользуйтесь встроенными статистическими функциями МАКС и СРЗНАЧ, которые можно вызвать при помощи Мастера функций (fx).

  14. Задайте следующие имена для диапазонов ячеек (Вставка – Имя – Присвоить):

Зарплата – для столбца с начисленными зарплатами; Премия – для столбца с премиями; Налог – для столбца с налогами; Пенсионный фонд – для столбца с отчислениями в пенсионный фонд.

  1. Выделите всю таблицу и выполните команду Вставка – Имя – Применить. Укажите Применить все имена из списка. Проверьте изменения в формулах.

  2. Вместо адресов ячеек в формулах должны появиться имена диапазонов, например формулы в столбце Всего Начислено должны иметь вид =Зарплата + Премия.

  3. Сохраните таблицу в своей папке под именем ZP1.XLS.

Задание 2. Форматирование таблицы

  1. Откройте таблицу ZP1.XLS, созданную в предыдущей работе.

  2. Установите с помощью мыши ширину колонок с учетом возможных их значений и ширины заголовков.

  3. Отцентрируйте названия колонок и значения в строках шапки таблицы. Для этого выделите блок и щелкните на инструменте «центрировать».

  4. Установите формат графы С – целое число, а всех остальных граф – дробные числа с двумя разрядами после запятой. Если при этом ширина некоторых граф окажется недостаточной – измените ее.

  5. Вставьте строку с заголовком таблицы «Ведомость начисления заработной платы» самой первой, разместите ее, начиная с колонки А. Проследите, что происходит при этом с формулами. Они остались правильными?

  6. Вставьте второй строку «за январь 2004г.»

  7. Выделите блок, состоящий из строк 1 и 2, шириной от графы А до последней графы таблицы. Отцентрируйте заголовок в пределах этого блока (использовать инструмент, на котором нанесена буква «а» со стрелками слева и справа). Установите для заголовка жирный шрифт размером 12 пунктов.

  8. Выделите «шапку» таблицы. Установите для нее шрифт размером 14 пунктов. При необходимости измените ширину некоторых граф.

  9. Для ячеек «Табельный номер», «Всего начислено», «Пенсионный фонд» задайте формат вывода текстов в несколько строк (Формат – Ячейка – вкладка Выравнивание – Переносить по словам).

  10. Запишите таблицу под новым именем ZP3.XLS.

  11. Отмените сетку на экране (Сервис – Параметры – вкладка Вид ) и сбросьте флажок у элемента “сетка”.

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

  13. Измените цвета символов итоговой строки и заголовка. Измените цвета шапки и графы «Оплатить», например, сделайте ее светло серой.

  14. Выведите вашу таблицу на экран в режиме предварительного просмотра и измените масштаб таблицы.

  15. Сохраните таблицу под прежним именем (ZP3.XLS)

Возможности автоформатирования

Для изменения внешнего вида ячеек рабочего листа можно также использовать автоформатирование:

  • Формат – Автоформат;

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

  1. Примените автоформатирование к своей таблице. Сохраните отформатированную таблицу в своей папке под именем ZP4.XLS.

Задание 3. Использование статистических и математических функций

  1. Составьте таблицу следующего вида (рис.2.). Введите в таблицу заголовок.

Рис. 2.

  1. Заполните ячейку В4. Затем протащите мышь вправо до ячейки F4 включительно (указатель мыши необходимо навести на ячейку В4 таким образом, чтобы он принял форму черного крестика). Отпустите левую кнопку мыши. Клетки автоматически заполнятся названиями месяцев.

  2. Заполните остальные ячейки согласно рис.2.

Задача 1. Рассчитать выручку от проката машин за 4 часа, 1 день, одну неделю

  1. Выделите ячейки B5:G5. Выполните щелчок на кнопке Автосумма на панели инструментов Стандартная.

  2. Скопируйте формулу из ячейки G5 в ячейки G6:G7.

  3. Выделите ячейки В5:В8. Выполните щелчок по кнопке Автосумма.

  4. Скопируйте формулу из ячейки В8 в ячейки С8:G8. Расчет процента

  5. Установите курсор на ячейку Н5. Щелкните на кнопке Процент на панели форматирование для задания формата ячейки.

  6. Наберите формулу =G5/G8 и, не нажимая клавишу Enter, нажмите клавишу F4. Формула примет вид =G5/$G$8, то есть относительный адрес G8 заменится на абсолютный $G$8 (не меняющийся при копировании формулы в другие ячейки). Нажмите клавишу Enter. Появится величина в процентах.

  7. Скопируйте формулу из ячейки Н5 в ячейки Н5:Н8 Имена ячеек

  8. Присвойте ячейке G8 имя Всего : утановите курсор на ячейку G8, выполните команду Вставка - Имя – Присвоить, введите имя ячейки Всего, щелкните по кнопке ОК.

  9. Очистите ячейки Н5:Н8 для расчета процентов по другой формуле.

  10. Введите имя ячейки в формулу расчета процента: установите курсор на ячейку Н5 и введите формулу =G5/Всего, скопируйте формулу в Н6:Н8. Контроль взаимосвязи ячеек при расчетах

  11. Выведите панель инструментов Зависимости. Для этого выполните команду Сервис – Зависимости – Панель зависимостей.

  12. Установите курсор на ячейку G8. Щелкните по кнопке Влияющие ячейки. Щелкните по кнопке Зависимые ячейки. Влияющие ячейки – это ячейки, на которые ссылается формула в текущей ячейке. Зависимые ячейки – это ячейки, содержащие формулы, которые ссылаются на текущую ячейку.

  13. Удалите все стрелки, щелкнув по кнопке Убрать все стрелки на панели Зависимости.

  14. Рабочему листу, на котором находится таблица, присвойте имя Задача 1. Следующему листу присвойте имя Задача 2 (если в рабочей книге только 1 лист, щелкните правой кнопкой по ярлычку этого листа и выполните команду Добавить – Лист).

Задача 2. Определить выручку от продажи товаров на лотках и тенденцию роста доходов

  1. На листе Задача 2 составить таблицу следующей формы (рис. 3.).

  2. Установите курсор на ячейку Е6. Введите формулу : =СУММ(D4:D6).

Рис. 3.

  1. Установите курсор на ячейку Е9. Введите формулу, используя Мастер функций: щелкните на кнопке Вставка функции на панели инструментов Стандартная, в поле Категория выбрать Математическая, в поле Функция щелкните по имени функции СУММ, нажмите на кнопку ОК. В появившемся диалоговом окне в поле Число 1 введите диапазон суммируемых чисел D7:D9, щелкните на кнопке ОК.

  2. Мастер функций можно вызвать также при одновременном нажатии клавиш SHIFT и F3. Для получения пояснения по функции щелкните по кнопке Справка (кнопка со знаком вопроса в левом нижнем углу диалогового окна)

  3. Самостоятельно подсчитайте сумму в ячейке Е12 любым способом.

Функции СРЗНАЧ, МАКС, МИН

  1. В ячейку Е14 введите формулу =СРЗНАЧ(Е6;Е9;Е12).

  2. В ячейку D15 введите формулу =МАКС(D4:D12).

  3. В ячейку D16 введите формулу =МИН(D4:D12).

  4. Очистите ячейки Е14, D15, D16 и введите формулы функций через Мастер функций.

Функция РАНГ

Функция РАНГ определяет ранг (номер) элемента в общей стоимости.

  1. В ячейку С4 введите формулу =РАНГ($D4;$D$4:$D$12), где D4 – содержит число, для которого определяется ранг, а D4:D12 – массив чисел, среди которого определяется ранг.

  2. Скопировать формулу в ячейки С5:С12.

Функция ТЕНДЕНЦИЯ

  1. Выполнить подготовительные операции: в ячейки В20:В22 введите соответственно значения из ячеек Е12, Е9, Е6, в ячейки С20:С24 введите годы: 2003 – 2007.

  2. В ячейку В23 введите формулу =ТЕНДЕНЦИЯ(В20:В22;С20:С22; С23). Скопируйте формулу из ячейки В23 в ячейку В24.

  3. Задайте в ячейках В23:В24 формат целых чисел. Для этого следует: выделить нужные ячейки; выполнить команду Формат – Ячейки; выбрать вкладку Число; выбрать категорию Числовой.

  4. Сохраните таблицу. Третьему листу рабочей книги присвойте имя Задача 3.

Задача 3. Муниципалитет города с кодом 10 ввел налог на лиц старше 18 лет в размере 10%. Определить величину налога.

  1. Составить таблицу (на листе Задача 3) следующей формы (рис. 4.)

Рис. 4.

Функция ЕСЛИ

  1. Введите в таблицу заглавие, шапку, цифровые и текстовые данные.

  2. В ячейку Е4 введите формулу =ЕСЛИ(И(В4=10;С4>18);D4*0,1;0). Формула означает, что если код города равен 10 и возраст старше 18 лет, то сумма налога определяется умножением дохода на величину налога. В противном случае сумма налога равна 0. Скопируйте формулу из ячейки Е4 в Е5:Е6.

Задание 4. Ввод и обработка данных в формате ДАТА - ВРЕМЯ

  1. В ячейки с А4 по F4 введите следующие данные («шапку» новой таблицы)

Порядко- вый номер

Фамилия

Дата рождения

Возраст

Юбилей

Премия

Рис. 5.

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

  2. Столбец Фамилия заполните фамилиями из пользовательского списка (табл. 1.)

  3. Столбец Дата рождения также заполните данными из следующей таблицы.

  4. В ячейках D5 : D18 создайте формулу для начисления возраста студентов, используя функцию СЕГОДНЯ и математическую функцию ЦЕЛОЕ, то есть в ячейке D5 нужно создать формулу: =ЦЕЛОЕ((СЕГОДНЯ()-С5)/365)

  5. В ячейках столбца Юбилей должен содержаться либо текст Юбилей, либо символ «-» в зависимости от того, какое число содержится в соседней ячейке слева. Если число кратное 5, будем считать возраст юбилейным. Формула имеет вид: =ЕСЛИ(ОСТАТ(D5;5)=0;«Юбилей»;«-»)

  6. С помощью условного форматирования (меню Формат) ячейки с текстом Юбилей оформите шрифтом курсив полужирный, синего цвета.

  7. Заполните ячейки F5 : F18 в соответствии со следующим правилом: премия начисляется только тем студентам, у которых юбилей. Сумма премии равна 50$. Примените любой из финансовых долларовых форматов для оформления ячеек F5 : F18.

  8. Используя функцию СЧЕТЕСЛИ, вычислите, сколько студентов-юбиляров, студентов моложе 24 лет, студентов старше 25 лет.

  9. Отформатируйте таблицу: добавьте заголовок, внешние и внутренние границы к ячейкам, заливку.

  10. Отсортируйте таблицу по фамилиям.

Таблица 1.

Порядковый номер

Фамилия

Дата рождения

Возраст

Юбилей

Премия

1

Соловьев

01.01.1980

26

-

-

2

Игнатов

31.03.1981

24

-

-

3

Петров

04.04.1983

22

-

-

4

Новоселов

30.12.1982

23

-

-

5

Шустов

07.07.1983

22

-

-

6

Потапова

06.10.1980

25

Юбилей

$50

7

Сидоров

13.06.1983

22

-

-

8

Ситников

12.04.1982

23

-

-

9

Зорина

23.06.1983

22

-

-

10

Колосова

24.06.1983

22

-

-

11

Амосова

25.07.1983

22

-

-

12

Горин

25.08.1984

21

-

-

13

Иванова

13.06.1983

22

-

-

14

Волков

15.07.1982

23

-

-

Количество юбиляров

1

Количество студентов моложе 24 лет

11

Количество студентов старше 25 лет

1

Задание 5. Подбор параметра

На основе представленных данных требуется рассчитать рентабельность кафе. Исходные данные (на рисунке выделены обычным шрифтом (не полужирным)):

  • Таблицу стоимости продуктов в расчете на 1 кг;

  • Раскладку продуктов по блюдам в расчете на 1 порцию;

  • Цену одного обеда;

  • Количество людей;

  • Количество рабочих дней в месяце.

Формулировка задания

  1. Подготовить лист по образцу (рис. 6).

Рис. 6.

  1. Добавить формулы для расчета промежуточных данных и требуемых результатов (ячейки с формулами выделены полужирным.). При вводе формул считать, что:

  • Стоимость продуктов в таблицах “1 блюдо”, “2 блюдо”, “3 блюдо” можно определить как произведение количества продукта в блюде на стоимость этого продукта в таблице “Стоимость продуктов”. Пример: для “1 блюда” формула выглядит так: =G3*C3, ее можно копировать во все ячейки столбца данной таблицы.

  • Итоговая стоимость каждого блюда определяется как сумма чисел в соответствующем столбце.

  • Стоимость 1-го обеда складывается из итоговых стоимостей 1-го, 2-го и 3-го блюд.

  • Доход (за месяц) получается произведением цены 1-го обеда на количество людей и количество дней работы.

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

  • Итого расходы рассчитываются путем сложения себестоимости продуктов, зарплаты персонала, аренды помещения и прочих расходов.

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

  • Рентабельность продукции находится как отношение прибыли к итоговым расходам.

  1. Руководствуясь полученными данными, решить с помощью Мастера подбора параметра следующие задачи анализа рентабельности (ответы сформировать в отдельной таблице произвольной формы):

  • Какова должна быть назначена цена обеда, чтобы рентабельность составила 20%?

  • Какое количество людей должно питаться в кафе, чтобы прибыль увеличилась на 5000 рублей?

  • Какова должна быть цена на отдельные виды продуктов, чтобы рентабельность составила 15%, 20%?

Задание 6. Поиск решения. Уменьшение затрат на перевозку грузов

ПОСТАНОВКА ЗАДАЧИ

Допустим, что ваша фирма занимается переработкой мяса на нескольких заводах, расположенных в разных районах Москвы. Мясо поставляется объединениями фермеров со складов, расположенных в нескольких городах Московской области. Стоимость мяса одинаковая, однако перевозка со склада на завод зависит от расстояния и отличается для каждого склада и завода. Потребность завода в мясе различна, и запасы на каждом складе ограничены. Требуется определить: с какого склада, на какой завод поставлять, сколько мяса для минимизации общих затрат на перевозку.

ПОРЯДОК ВЫПОЛНЕНИЯ:

Создайте на листе Транспортные расходы таблицу.

С этой целью:

Рис.7.

  • В ячейку А1 введите текст «Оптимизация транспортных потоков».

  • В ячейку В2 введите текст «Потребители->».

  • В ячейки C2:F2 введите названия мясоперерабатывающих заводов.

  • В ячейку А3 введите текст «Поставщики».

  • В ячейки А4:А8 названия складов.

  • Установите курсор в ячейку В4 и нажмите кнопку Автосумма, после чего выделите ячейки с С4 по F4. В строке формул появится формула =СУММ(С4:F4). Нажмите кнопку , расположенную слева в строке формул, и формула будет введена.

  • Скопируйте содержимое ячейки В4 в ячейки В5:В8.

  • Выделите ячейки с С4 до F8. Ведите цифру 1 и нажмите кнопку . Нажмите комбинацию клавиш CTRL+D (автозаполнение столбцов в выделенной области), а затем нажмите CTRL+R (автозаполнение строк в выделенной области). Все выделенные ячейки будут заполнены единицами. Установите формат ячеек выделенной области Числовой.

  • В ячейку В9 введите текст «Факт->».

  • В ячейку С9 введите формулу =СУММ(С4:С8). Скопируйте формулу в ячейки D9:F9.

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

Введите требуемые объемы поставок и цены поставок. С этой целью:

  • Введите в ячейку В10 текст «Запросы->». В десятой строке вводятся значения потребляемого каждым из заводов мяса в тоннах.

  • В следующие ячейки введите соответственно:

В11

300

С10

240

В12

240

D10

115

В13

170

E10

280

В14

120

F10

370

В15

320

  • Выделите ячейки с А4 по А8. Нажмите клавишу CTRL и, не отпуская ее, подведите курсор мыши к краю выделенного интервала, нажмите левую клавишу мыши и двигайте мышь. Появится серый прямоугольник размером с выделенную область. Расположите его в ячейки с А11 по А15, затем отпустите клавишу мыши и клавишу CTRL. Названия складов будут скопированы.

  • В ячейки второго столбца занесите объемы месячных запасов на различных складах в тоннах соответственно.

  • В ячейки с С11 по F15 занесите стоимость перевозки тонны мяса с конкретного склада на конкретный завод. Для этого введите в ячейки с С11 по F15 следующие данные:

  • 47000

  • 41500

  • 45000

  • 32650

  • 39000

  • 32300

  • 38000

  • 41000

  • 23650

  • 27300

  • 21000

  • 18000

  • 19500

  • 19400

  • 9000

  • 24000

  • 39000

  • 36000

  • 27500

  • 44000

  • В ячейку А16 введите текст «Всего».

  • В ячейку С16 введите формулу =С4*С11+С5*С12+С6*С13+ С7*С14+С8*С15.

В ячейке С4 находится количество мяса, перевозимого со склада в Наро-Фоминске на завод в Лужниках, а в ячейке С11 – цена перевозки тонны груза по этому маршруту. Соответственно, первое слагаемое в формуле означает полную стоимость перевозок по данному маршруту. Вся же формула вычисляет полную стоимость перевозок мяса на завод в Лужниках.

  • Скопируйте формулу из ячейки С16 в ячейки D16:F16.

  • В ячейку В16 введите формулу =СУММ(С16: F16). В данной ячейке будет вычисляться общая стоимость перевозки мяса.

  • В ячейку А18 введите текст “Всего на перевозки требуется”, а в ячейку Е18 – “млн.руб.”.

  • Для вычисления суммы в миллионах в ячейку D18 введите формулу =В16/1000000.

Выполните форматирование таблицы в соответствии с рис. 7.

Скопируйте лист Транспортные расходы (для возможного восстановления начального вида таблицы) на лист2 и переименуйте скопированный лист, дав ему название Поиск решения.

Выполните поиск решения (Сервис – Поиск решения) с целью определения минимальных затрат на перевозки при соблюдении следующих условий (рис. 8).

  • Объем поставок с конкретного склада должен быть меньше или равен запасам на складе.

  • Объем перевозок не должен быть отрицательным.

  • Запросы заводов должны быть выполнены полностью. Перевыполнение поставок допустимо, а недовыполнение – нет.

Сохраните результаты поиска решения. Проверьте правильность полученных результатов (рис. 9).

Рис. 8.

Рис. 9. Результат поиска решения.

Задание 7. Консолидация данных

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

Рис. 10.

В качестве исходных данных для консолидации используем следующую таблицу (рис. 10). Данные по колонкам в и с вводятся с клавиатуры , а по колонке d считаются по формуле.

Порядок выполнения консолидации для таблицы.

  1. Выделите ячейку, определяющую положение итоговой таблицы, которая будет соответствовать левому верхнему углу ( F4 ).

  2. Выберите меню Данные – Консолидация.

  3. В окне Консолидация в списке Функция выберите функцию Сумма.

  4. Установите курсор в поле Ссылка. Выделите первую область в исходной таблице (A4:D8). Нажмите кнопку Добавить.

  5. Повторить пункт 4 для диапазонов A10:D14, A16:D19.

  6. Установите флажок в поле В левом столбце и нажмите кнопку ОК.

В результате должна получиться следующая таблица (рис.11).

Рис. 11.

Основной недостаток таблицы: не производится автоматический пересчет в итоговой таблице при изменении данных в исходной таблице.

Задание 8. Сводные таблицы

Мастер сводных таблиц позволяет использовать еще один способ обобщения табличных данных. Одна из особенностей этого мастера заключается в том, что он удовлетворительно работает только с однородными табличными данными, поэтому исходную таблицу нужно преобразовать к виду (см. табл. 12). Для этого скопируйте исходную таблицу из предыдущего задания на лист 2 и проведите преобразования.

Рис. 12.

Для построения сводной таблицы необходимо выполнить следующие действия.

  1. Вызовите Мастера сводных таблиц (меню ДанныеСводная таблица). Откроется окно – Первый шаг. Поставьте переключатель, указывающий источник данных на В списке. Щелкнуть по кнопке Далее>.

  2. Вторым шагом указать диапазон, в котором содержатся исходные данные, то есть выделить ячейки с А1 по Е12, кн. Далее>.

Рис. 13.

  1. Третьим шагом нужно определить, как будет выглядеть новая сводная таблица. Переместите мышью кнопку Точка в область Страница, кнопку Наименование в область Строка, кнопку Цена в область Столбец, а кнопку Сумма в область Данные. Щелкнуть Далее> (рис. 13).

  2. Четвертым шагом в поле Поместить таблицу в … поставить переключатель на строку Существующий лист, щелкнуть по ярлыку листа 3 и по ячейке А1. Щелкнуть по кнопке Готово.

Открывая кнопку, находящуюся в клетке В1 (рис.14), можно выводить на экран информацию как по отдельному объекту, так и сводную по всем объектам.

Рис. 14. Сводная таблица

Задание 9. Списки

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

  • Столбцы списков становятся полями базы данных.

  • Заголовки столбцов становятся именами полей базы данных

  • Каждая строка списка преобразуется в запись данных.

  1. Создайте таблицу в соответствии с заданным образцом (табл. 2).

Таблица 2

Дата

Расход

Сумма

Получатель

01.06.03

Накладные расходы

$600

ЗАО БИН

02.06.03

Накладные расходы

$321

ТОО Надежда

04.06.03

Материалы

$16000

АО Престиж

05.06.03

Зарплата

$2000

Васильева М.Ф.

05.06.03

Зарплата

$2540

Казаков С.С.

05.06.03

Зарплата

$1890

Иванов И.И.

30.06.03

Накладные расходы

$1000

АО ИНВЕСТ

04.07.03

Накладные расходы

$600

ЗАО БИН

04.07.03

Накладные расходы

$440

ТОО Надежда

04.07.03

Материалы

$13200

АО Оргсинтез

05.07.03

Зарплата

$2000

Васильева М.Ф.

05.07.03

Зарплата

$2540

Казаков С.С.

05.07.03

Зарплата

$1890

Иванов И.И.

31.07.03

Накладные расходы

$1000

АО ИНВЕСТ

04.08.03

Накладные расходы

$600

ЗАО БИН

05.08.03

Зарплата

$2000

Васильева М.Ф.

05.08.03

Зарплата

$2540

Казаков С.С.

05.08.03

Зарплата

$1890

Иванов И.И.

04.09.03

Накладные расходы

$311

ТОО Надежда

05.09.03

Зарплата

$2000

Васильева М.Ф.

05.09.03

Зарплата

$2540

Казаков С.С.

05.09.03

Зарплата

$1890

Иванов И.И.

  1. Используя Форму данных, добавьте в список данные об АО Престиж: «30.06.03, Материалы, $800, АО Престиж». Для вывода формы на экран щелкните на любой из ячеек заглавной строки и выберите команду ДанныеФорма (рис.15.)

  2. Используя Форму данных и кнопку Критерии, просмотрите информацию о Казакове и измените сумму зарплаты за 05.09.03 на $2800.

  3. Используя Форму данных, просмотрите все данные списка о расходах на материалы, превышающих $12000.

Рис. 15.

  1. Отобразите все данные списка по АО ИНВЕСТ, используя Данные ФильтрАвтофильтр.

  2. Используя Автофильтр, отобразите все данные списка по накладным расходам, а затем только за июнь (рис. 16).

Рис. 16.

  1. По каждому расходу подведите итог по полю Сумма, воспользовавшись командой ДанныеИтоги, предварительно отменив Автофильтр и отсортировав данные списка по полю Расход.

  2. Присвойте имя (например, имя Список) диапазону ячеек, содержащему все данные списка (включая заглавную строку).

  3. Вставьте перед диапазоном со списком 11 пустых строк.

  4. В ячейки A1:D1 скопируйте шапку таблицы.

  5. Используя Расширенный фильтр отобразите все данные списка по зарплате, используя для области критериев ячейки A1:D2. Изменив область критериев, отобразите все данные списка по накладным расходам.

  6. Внесите изменения в область критериев, добавив в ее шапку еще одну ячейку с названием Сумма, так, чтобы отобразились накладные расходы только от $500 до $900 (рис. 17).

Рис. 17.

  1. Используя Расширенный фильтр, поместите в любую пустую область рабочего листа все данные списка о накладных расходах и зарплате за июль, предварительно изменив область критериев, либо создав новую.

  2. Используя Расширенный фильтр и новую область критериев, поместите данные списка только по зарплате за июнь и июль, указав дату, сумму и получателя. Для этого предварительно создайте шапку новой таблицы (3 ячейки).

  3. Измените область критериев, оставив в качестве критерия только вид расхода – зарплату.

  4. Для заданного критерия отбора вычислите общую сумму:

  • В ячейку В6 введите формулу расчета суммы, используя Мастер функции БДСУММ. Для задания диапазона базы данных укажите имя соответствующего диапазона (Список), для задания поля укажите с помощью мыши ячейку с названием поля Сумма, для задания области критериев также воспользуйтесь мышью;

  • В ячейку В5 введите текст «Сумма по заданному критерию».

  1. Используя функцию БСЧЕТ, подсчитайте в ячейке С6 количество выданных зарплат. Имя поля, указываемого в окне Мастера функций, оставьте прежним. В ячейку С5 введите текст «Количество».

18.Теперь можете сходить покурить, расслабиться, попить чайку в столовке

Контрольные задания по теме «Табличный процессор excel»

Задание 1. Анализ дебиторской задолженности

0.Спросить преподавателя :”А контрольные надо делать?”

  1. Построить таблицы 1 и 2 по приведенным ниже формам (рис. 18, рис. 19). Названия месяцев в таблицах 1 и 2 ввести, используя формат даты. Разместить каждую таблицу на отдельном листе рабочей книги, используя одновременное выделение двух листов.

  2. Присвоить каждому листу короткое имя, соответствующее названию таблицы в сокращенном виде.

  3. В таблице 1 рассчитать значение строки «Итого».

  4. В таблице 2 рассчитать значения граф 3 – 8. Графы 3 – 7 рассчитать по данным таблицы 1, причем формулу расчета процента оплаты ввести один раз, а далее скопировать ее по строкам и столбцам.

  5. Выполнить обрамление всей таблицы, убрать сетку.

Рис. 18.

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

Рис. 19.

  1. Убрать рамки у легенды и диаграммы.

  2. Ввести в нижний колонтитул индекс группы, свою фамилию, имя, дату и имя файла.

  3. Документ сохранить, организовать просмотр перед печатью.

  4. В приложении Word создать документ с заголовком диаграммы, поместить в него построенную диаграмму. Документ сохранить и организовать просмотр перед печатью.

Задание 2. Расчет возвратной суммы кредита

  1. Построить таблицу по приведенной ниже форме (рис. 20).

Рис. 20. Расчет возвратной суммы кредита

  1. Рассчитать сумму возврата кредита (гр. 6) при условии: если дата возврата фактическая не превышает договорную, то сумма возврата увеличивается на 40% от суммы кредита (гр. 3), в противном случае сумма возврата увеличивается на 40% плюс 1% за каждый просроченный день. В результате графа 6 будет иметь вид (рис. 21):

  2. Ввести название таблицы, а в строку между названием таблицы и ее шапкой ввести текущее значение даты и времени.

  3. Выполнить обрамление всей таблицы, убрать сетку.

  4. Используя Мастер рисования выделить тенью графу 2.

  5. По данным граф 1, 3 и 6 таблицы построить гистограмму с легендой, заголовком, названием осей.

  6. Убрать рамки у легенды и диаграммы.

Рис. 21.

  1. Ввести в нижний колонтитул номер группы, свою фамилию, имя, дату и имя файла.

  2. Документ сохранить, организовать просмотр перед печатью.

  3. В приложении Word создать документ, поместить в него построенную диаграмму. Документ сохранить и организовать просмотр перед печатью.

Задание 3. Консолидация по видам выпуска гко

  1. Создать рабочую книгу из трех листов. Присвоить первому листу имя «Январь», второму – «Февраль», третьему – «Итого».

  2. Выделить все листы (удерживая CTRL, щелкнуть мышкой по ярлычкам листов) и ввести шапку таблицы.

  3. Снять выделение листов и ввести названия каждой таблицы.

выпуска ГКО

Эмиссия (млрд. руб.)

Выручка (млрд. руб.)

Погашено (млрд. руб.)

Доходы бюджета (млрд.руб.)

Средняя взвешен ная цена

21000RMFS

979,69

662,95

433,90

21000RMFS

1998,00

1276,40

1250,70

22000RMFS

2440,89

1409,89

1296,50

23000RMFS

278,53

197,45

22000RMFS

162,50

118,23

54,64

ИТОГО:

Рис. 22.

  1. Заполнить данными таблицу 1 (рис. 22) на листе «Январь» и таблицу 2 (рис. 23) – на листе «Февраль».

выпуска ГКО

Эмиссия (млрд. руб.)

Выручка (млрд. руб.)

Погашено (млрд. руб.)

Доходы бюджета (млрд.руб.)

Средняя взвешен ная цена

21000RMFS

1385,83

911,78

903,10

21000RMFS

50,00

36,64

22000RMFS

320,00

224,61

98,75

23000RMFS

143,07

56,71

22000RMFS

38,76

33,14

18,43

23000RMFS

66,65

52,17

32,17

21000RMFS

150,00

60,82

23000RMFS

54,53

47,18

21000RMFS

45,18

35,72

14,22

22000RMFS

73,77

30,10

ИТОГО:

Рис. 23.

  1. Выполнить все необходимые расчеты следующим образом:

  • Графа «Доходы бюджета» = графа «Выручка» – графа «Погашено»;

  • Графа «Средняя взвешенная цена» = графа «Выручка»/графа «Эмиссия» * 100.

  1. Рассчитать суммы итогов за январь и февраль методом автосуммирования.

  2. Перейти на лист «Итого», ввести заголовок «Итоговая таблица».

  3. Для получения итогов по каждой ценной бумаге выполнить консолидацию по категориям (кроме столбца F), в качестве имен используются значения левого столбца (рис. 25).

  4. Рассчитать итоговую среднюю цену по каждому виду ценных бумаг путем консолидации данных и выбрав функцию Среднее (рис. 24).

  5. Рассчитать итоги по соответствующим графам листа «Итого» (рис. 26).

Рис. 24.

Рис. 25.

Рис. 26.

Задание 4. Оптимизируемые модели

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

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

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

  1. Создать исходную таблицу в точном соответствии с заданными адресами (рис. 27).

Рис. 27.

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

в яч. С4 ввести: =$D$2*D4+$E$2*E4+$F$2*F4 в яч. D10 ввести: =75*МАКС(D2;0)^$H$8 в яч. Е10 ввести: =50*МАКС(Е2;0)^$H$8 в яч. F10 ввести: =35*МАКС(F2;0)^$H$8 в яч. D11 ввести: =СУММ(D10:F10),

где: - числа 75, 50, 35 – единичная прибыль на разные виды продукции; - в ячейке Н8 стоит коэффициент, учитывающий фактор уменьшения прибыли при росте объема производства (что делает задачу нелинейной).

  1. Для решения задачи в меню Сервис выбрать Поиск решения. В окне Поиск решения в поле Установить целевую ячейку задать $D$11. Выберите переключатель Максимальное значение. В поле Изменяя ячейки выберите $D$2:$F$2.

  2. Далее следует задать ограничения по задаче. Щелкните по кнопке Добавить и наберите первое ограничение: $C$4:$C$8<=$B$4:$B$8 ( то есть количество используемых деталей не должно превышать наличное).

  3. Щелкните по кнопке Добавить и наберите второе ограничение: $D$2:$F$2>=0

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

  5. Скопируйте исходную таблицу на лист 2 и лист 3. В таблице на листе 2 снова запустите Поиск решения и в результирующем окне поставьте переключатель на Сохранить найденное значение. В таблице на листе 3 в окне Поиск решения задайте найти в целевой ячейке не максимальное значение, а, например, 12000. Сохраните найденное значение.

Задание 5. Расчет доходности учтенных векселей

  1. Построить две таблицы по приведенным ниже формам (рис. 28).

  2. В выделенных областях второй таблицы произвести расчеты по формулам. Ячейки из графы Дней до погашения рассчитываются по формуле: =ДНЕЙ360(«Дата обращения в банк»;«Срок до погашения»)

  3. Ячейки из графы Цена векселя рассчитываются по формуле: =«Номинал векселя»*(1-«Дней до погашения»*«Учетный процент» /360)

  4. Ячейки из графы Дисконт рассчитываются по формуле: =«Номинал векселя»-«Цена векселя»

  5. Ячейки из графы Доходность рассчитываются по формуле: =«Дисконт»*360/«Дней до погашения»/«Цена векселя»

  6. Графу Цена векселя пересчитать по логической формуле и скопировать вниз по столбцу.

  7. Задать соответствующим графам Денежный формат, Процентный формат, формат Даты. Согласно образцу расчертить таблицу.

  8. Построить диаграмму, наиболее наглядно отражающую полученные результаты.

Таблица учетных ставок

Дней до погашения

Учетный процент

До 90 дн.

36%

От 90 до 120 дн.

40%

От 120 до 150 дн.

44%

От 150 до 180 дн.

68%

Реестр учтенных векселей

Номинал векселя

Срок до погашения

Дата обращения

в банк

Дней до погаше-ния

Цена векселя

Дисконт

Доходность в % годовых

1 500,00р.

По

предъявлению

01.01.98

21.12.97

10

1 485,00р.

15,00р.

36,36%

2 000,00р.

По

предъявлению

10.02.98

19.10.97

111

1 753,33р.

246,67р.

45,63%

4 500,00р.

По

предъявлению

20.03.98

14.11.97

126

3 807,00р.

693,00р.

52,01%

6 000,00р.

По

предъявлению

10.04.98

19.11.97

141

4 966,00р.

1 034,00р.

53,16%

9 000,00р.

По

предъявлению

15.05.98

04.12.97

161

6 263,00р.

2 737,00р.

97,72%

Итого

18 274,33р.

4 725,67р.

Рис. 28.

Задание 6. Конвертация валюты

Руководство фирмы поручило вам через Центральный банк России перевести деньги в банки нескольких стран и конвертировать их в иностранную валюту, которую могли бы оперативно использовать представители вашей фирмы. Для каждого из банков выделена сумма в 1 000 000 рублей. Необходимо создать отчет о проведенных операциях, указав в нем итоговые суммы в иностранной валюте для каждой страны.

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

Порядок выполнения работы.

  1. Войдите в Internet и наберите следующий адрес Web-страницы: http://www.rbc.ru

  2. После завершения поиска появится Web-страница со сведениями, взятыми из международной информационной системы и подготовленными РИА «РосБизнесКонсалтинг». Войдите в раздел <Курсы валют ЦБ России>, найдите и выпишите (или скопируйте) данные о курсах валют для своего варианта.

  3. Создайте отчет в EXCEL, заполнив графы необходимыми данными и формулами и отформатировав надлежащим образом таблицу.

Варианты заданий (соответствие вида валюты номеру строки в таблице 3.

1 вариант - строки 9,6,10

2 вариант - строки 3,4,5

3 вариант - строки 1,2,7

4 вариант - строки 8,1,2

5 вариант - строки 4,5,6

6 вариант - строки 3,7,8

7 вариант - строки 9,10,1

Таблица 3

№ строки

Ведущие валюты мира

Условные обозначения валют

1

Австралийский доллар

AUD

2

Канадский доллар

CAD

3

Французский франк

FRF

4

Немецкая марка

DEM

5

Итальянская лира

ITL

6

Японская иена

JPY

7

Нидерландский гульден

NLG

8

Швейцарский франк

CHF

9

Британский фунт стерлингов

GBP

10

Американский доллар

USD

  1. По окончании выполнения таблицы следует проверить полученные результаты при помощи Конвертора валют. Для этого на вышеуказанном сайте найти раздел Инструменты и открыть окно Конвертор валют. Укажите исходные суммы для каждой страны и размер комиссионных, взимаемых банками. Сравнить полученные результаты с расчетами в вашей таблице.

Литература

  1. Информатика и информационные технологии. Учебное пособие / Под ред. Романовой Ю.Д. – М: Изд-во Эксмо, 2005. – 544с.

  1. Практикум по экономической информатике. Учебное пособие. Часть 1 / Под ред. Шуремова Е.Л. – М: Изд-во «Перспектива», 2000. – 300с.

  1. Гончаров А. Excel 7.0 в примерах. СПб: Питер, 1996. – 256с.

Клавдия Ивановна Самойлова

Сборник финансовых задач для табличного процессора Excel

Пособие для практических занятий

Государственное образовательное учреждение высшего профессионального образования «Нижегородский государственный университет им. Н.И. Лобачевского».

603950, Нижний Новгород, пр. Гагарина, 23.

Подписано в печать . Формат 60х84 1/16. Бумага офсетная. Печать офсетная. Гарнитура Таймс. Усл. печ. л. 2,0. Уч.-изд.л. 2,3. Заказ № Тираж 300 экз.

Отпечатано в типографии Нижегородского университета им. Н.И. Лобачевского 603600, г. Нижний Новгород, ул. Большая Покровская, 37 Лицензия ПД №18-0099 от 14.05.01

1

36

2

35

3

34

4

33

5

32

6

31

7

30

8

29

9

28

10

27

11

26

12

25

13

24

14

23

15

22

16

21

17

20

18

19