Сборник инструкционно-технологических карт по дисциплине Финансово-экономические расчеты в электронных таблицах

ГБПОУ ВО «Лискинский аграрно-технологический техникум»
РАССМОТРЕНО
на заседании
ЦМК экономических дисциплин
Протокол № _____
от «_____» ___________ 201___г.
Председатель ЦМК _____ С. Н. Литовская
Утверждаю
Зам. Директора по УР
____________ И. М. Гайдай
«___» __________ 20___ г.
Сборник
ИНСТРУКЦИОННО-ТЕХНОЛОГИЧЕСКИХ КАРТ
по дисциплине
ОП.11 Финансово-экономические расчеты в электронных таблицах
для студентов специальности
38.02.01 «Экономика и бухгалтерский учет (по отраслям)»
Преподаватель:
Сазанова А. А.
Лиски 2016

ИНСТРУКЦИОННО – ТЕХНОЛОГИЧЕСКАЯ КАРТА
на выполнение практической работы №1
по дисциплине «Финансово-экономические расчеты в электронных таблицах»
Наименование работы: Настройка интерфейса электронной таблицы.
Цель работы: Настроить интерфейс электронной таблицы Microsoft Excel.
Норма времени: 2 часа.
Оборудование, приборы, аппаратура, материалы: ПК с установленным лицензионным программным обеспечением.
Задание
Задание 1. Работа с интерфейсом программы.
1. Запустите табличный процессор Microsoft Excel.
2. Разверните окно Excel на весь экран и внимательно рассмотрите его.
3. Удалите панели инструментов, строку формул и строку состояния, используя меню Вид.
4. Восстановите панели инструментов, используя контекстно-зависимое меню (щелкните правой кнопкой мыши в верхней части экрана), добавьте строку состояния и строку формул, используя меню Вид.
По умолчанию открытая книга состоит из трех электронных листов.
5. Щелкните по ярлыку Лист1 и переименуйте его в Задание1; Лист2 – в Задание2; Лист3 – в Задание3.
6. Добавьте новый лист, используя контекстно-зависимое меню, или меню Вставка. Присвойте новому листу имя Примеры.
7. Скопируйте лист примеры, удерживая нажатой клавишу Ctrl.
8. Удалите лист Задание2.
Каждый лист – совокупность ячеек, образованных 256 столбцами и 65536 строками.
Столбцы именуются латинскими буквами (от А до IV), строки – цифрами.
Это позволяет адресовать ячейки. Например, левая верхняя ячейка листа имеет адрес А1, а правая нижняя – IV65536.
9. Сделайте текущей ячейку C8, щелкнув в нее мышью. Обратите внимание, в поле имени отразилось имя выделенной ячейки.
10. Переместитесь с помощью стрелок на клавиатуре в ячейку А1.
11. Используя поле имени в строке формул перейдите к ячейке АB2
(Напечатайте имя латинскими буквами и нажмите Enter).
12. Используя поле имени, выделите диапазон ячеек АB2:AC15.
13. Выделите диапазон ячеек А1:E8, используя мышь.
14. Выделите несмежные диапазоны ячеек А1:B4; D2; Е3:G18, удерживая нажатой клавишу Ctrl.
15. Выделите строки с 1 по 6, нажимая на имя строки. Выделите столбцы А, С, G.
16. Выделите содержимое всего листа, нажав на маленький квадратик между именем первой строки и первого столбца.
17. Напечатайте в диапазоне ячеек А1:А5 любые цифры и примените к ним различные форматы (меню Формат – Ячейки – Число).
18. В ячейке B1 напечатайте слово Информатика. Выполните автоподбор ширины столбца (двойной щелчок мышью на границе столбцов В и С или выполните команду Формат – Столбец – Автоподбор ширины).
19. Использую кнопки панели инструментов Форматирование, примените к ячейке полужирное начертание, добавьте границу и заливку.
20. В ячейке С1 напечатайте Экономический факультет. Выполните команду Формат – Ячейки – Выравнивание – Переносить по словам. Слово факультет должно расположиться под словом экономический. Отмените команду, используя стрелку отменить.
21. Выполните перенос по словам другим способом. Напечатайте слово Экономический и нажмите комбинацию клавиш (Alt+Enter), далее печатайте слово факультет.
22. В ячейке D1 напечатайте словосочетание Информационные технологии.
Выделите диапазон ячеек D1:H1 и объедините их, используя кнопку Объединить и поместить в центре.
23. В ячейке B3 напечатайте слово Понедельник и выполните автозаполнение вниз до Воскресения, потянув вниз маленький крестик в правом нижнем углу ячейки.
24. Скопируйте полученный список, вставьте в соседние ячейки столбца С.
Выделите список столбца В и перенесите в диапазон ячеек D3:D9.
25. Удалите столбец В, очистите содержимое столбца А. Добавьте перед столбцом B новый столбец.
26. Используя полученные знания на листе Примеры создайте таблицу:
АКТ
О порче товарно-материальных ценностей
от 25 декабря 2013 г. № п/п Наименование
товара Ед.
измер. Кол-во Стоимость Дата поступления
розн. опт. 1 Стол шт. 4 2340р. 1800р. 03.09.2013
2 Скатерти шт. 26 130р. 100р. 26.10.2013
3 Шторы шт. 10 715р. 550р. 15.04.2013
4 Двери шт. 7 1560рю 1200р. 23.10.2013
5 Калькулятор шт. 5 156р. 120р. 18.11.2013
27. Сохранить работу в своей папке под именем ПР-1 Фамилия
Содержание отчета
Отчет должен содержать:
1. Наименование работы.
2. Цель работы.
3. Результат выполнения задания.
4. Вывод по работе.
Контрольные вопросы
1. Что представляет собой лист электронной таблицы?
2. Сколько столбцов и строк находятся на листе электронной таблице?
3. Для чего необходимо автозаполнение?
Литература
Трусов А.Ф. Excel 2007 для менеджеров и экономистов. Логистические, производственные и оптимизационные расчеты. / А. Ф. Трусов. - Издательство: ООО «Питер Пресс», 2009. – 256 с.
Мицкевич А. А. Сборник заданий по экономике. / А. А. Мицкевич. – М.: «Вита-Пресс», - 1998. – 144 с.: ил.
Гаращенко А.Б. Экономические и финансовые расчеты в Excel. / А. Б. Гаращенко, В. И. Пикуза. - Издательство: БХВ Петербург, 2007. – 278 с.
Просветов Г.И. Анализ данных с помощью Excel. Задачи и решения. / Г. И. Просветов. - Альфа-Пресс, 2009. – 352 с.
Аверчев И.В. Excel 2007 в учете и менеджменте. Пошаговый самоучитель по бухгалтерскому учету на компьютере Эксмо-Пресс, 2007. – 256 с.
Михеева Е. В., Тарасова Е. Ю. Практикум по информационным технологиям в профессиональной деятельности экономиста и бухгалтера. / Е. В. Михеева, Е. Ю. Тарасова. – М.: Издательский центр «Академия», 2014. – 352 с.

ИНСТРУКЦИОННО – ТЕХНОЛОГИЧЕСКАЯ КАРТА
на выполнение практической работы №2
по дисциплине «Финансово-экономические расчеты в электронных таблицах»
Наименование работы: Организация формул и форматирование данных в таблицах, содержащих экономическую информацию.
Цель работы: Произвести работу с формулами при обработке экономической информации.
Норма времени: 2 часа.
Оборудование, приборы, аппаратура, материалы: ПК с установленным лицензионным программным обеспечением.
Задание
Задание 1.
1. Создайте таблицу по образцу.
2. Формулы для расчета:
Всего = Безналичные платежи + Наличные платежи
Выручка от продажи = Цена * Всего.
Анализ продаж фирмы "Эврика" за сентябрь 2013 г.
Наименование продукции Цена (руб.) Продажи Выручка от продажи (руб.)
Безналичные платежи (шт.) Наличные платежи (шт.) Всего (шт.) Радиотелефон 4300 237 230    
Телевизор 9500 110 104    
DVD-плеер 5700 69 50    
Видеокамера 13790 60 58    
Домашний кинотеатр 48500 11 9    
Игровая приставка 6350 28 36    
DVD-диски 130 540 350    
mp3-плеер 2800 70 240    
Итого:          
       
Максимальные продажи:        
Минимальные продажи:        
Таким образом, в ячейку Е5 следует занести формулу = С5+D5, а в ячейку F5 =B5*E5.
3. Скопируйте формулы в диапазоны Е6:Е12 и F6:F12 соответственно, используя функцию автозаполнение.
4. Рассчитайте Итого, используя кнопку Автосумма.
5. Для вычисления максимальных и минимальных продаж, следует воспользоваться встроенными функциями процессора Excel. Для этого необходимо выполнить команду Вставка – Функция – МИН (МАКС).
Данные функции относятся к статистическим.
6. Сохраните работу в своей папке под именем ПР-2 Фамилия.
Содержание отчета
Отчет должен содержать:
1. Наименование работы.
2. Цель работы.
3. Результат выполнения задания.
4. Вывод по работе.
Контрольные вопросы
1. Каким образом можно вызвать мастер функций?
2. Для чего используется мастер функций?
3. С чего начинается ввод формул в ячейку электронной таблицы?
Литература
Трусов А.Ф. Excel 2007 для менеджеров и экономистов. Логистические, производственные и оптимизационные расчеты. / А. Ф. Трусов. - Издательство: ООО «Питер Пресс», 2009. – 256 с.
Мицкевич А. А. Сборник заданий по экономике. / А. А. Мицкевич. – М.: «Вита-Пресс», - 1998. – 144 с.: ил.
Гаращенко А.Б. Экономические и финансовые расчеты в Excel. / А. Б. Гаращенко, В. И. Пикуза. - Издательство: БХВ Петербург, 2007. – 278 с.
Просветов Г.И. Анализ данных с помощью Excel. Задачи и решения. / Г. И. Просветов. - Альфа-Пресс, 2009. – 352 с.
Аверчев И.В. Excel 2007 в учете и менеджменте. Пошаговый самоучитель по бухгалтерскому учету на компьютере Эксмо-Пресс, 2007. – 256 с.
Михеева Е. В., Тарасова Е. Ю. Практикум по информационным технологиям в профессиональной деятельности экономиста и бухгалтера. / Е. В. Михеева, Е. Ю. Тарасова. – М.: Издательский центр «Академия», 2014. – 352 с.

ИНСТРУКЦИОННО – ТЕХНОЛОГИЧЕСКАЯ КАРТА
на выполнение практической работы №3
по дисциплине «Финансово-экономические расчеты в электронных таблицах»
Наименование работы: Анализ форм бухгалтерской отчетности в системе электронных таблиц.
Цель работы: Проанализировать бухгалтерскую отчетность в системе электронных таблиц Microsoft Excel.
Норма времени: 2 часа.
Оборудование, приборы, аппаратура, материалы: ПК с установленным лицензионным программным обеспечением.
Задание
Задание 1. Бухгалтерская отчетность – система данных, характеризующая состояние хозяйственной деятельности, а также итоги работы организации за отчётный период; составляется по данным финансового учета.
Рыночная цена за ед. Р (р.) 25р.
Общие постоянные издержки TFC(р.) 17р.
Объем продаж, доход, издержки и прибыль фирмы
Объем продаж Q (ед.) Общий доход TR (р.) Предельный доход MR (р.) Общие переменные издержки TVC (ед.) Общие издержки TC (ед.) Предельные издержки MC (р.) Средние издержки ATC (р.) Прибыль П (р.)
0 ? ? 5р. ? ? ? ?
1 ? ? 27р. ? ? ? ?
2 ? ? 42р. ? ? ? ?
3 ? ? 53р. ? ? ? ?
4 ? ? 61р. ? ? ? ?
5 ? ? 68р. ? ? ? ?
6 ? ? 76р. ? ? ? ?
7 ? ? 87р. ? ? ? ?
8 ? ? 101р. ? ? ? ?
9 ? ? 120р. ? ? ? ?
10 ? ? 144р. ? ? ? ?
11 ? ? 177р. ? ? ? ?
12 ? ? 218р. ? ? ? ?
13 ? ? 270р. ? ? ? ?
Максимальная прибыль: ?
Формулы расчета:
TR=QP
MRi=TRi- TRi-1
TC=TVC+TFC
MCi=TCi- TCi-1
ATC=
П=TR-TC
Заполните ячейки исходных данных:
Введите текст в ячейки Е1 и Е2: Рыночная цена за ед. Р (р.) и Общие постоянные издержки TFC (р.) (соответственно);
Введите числа в ячейки Н1 и Н2: 25 и 17 (соответственно);
Введите в ячейки таблицы формулы:
В ячейку В5 введите формулу =A5*$H$1;
Ячейки В6:В18 заполните Маркером заполнения;
Формула =B6-B5 рассчитывает значение в ячейке С6;
Ячейки С7:С18 заполните Маркером заполнения;
Составьте и введите самостоятельно такую формулу для ячейки Е5, чтобы остальные ячейки этого столбца заполнились Маркером заполнения.
Составьте формулу для ячейки F6 и введите ее самостоятельно;
Остальные ячейки этого столбца заполните Маркером заполнения.
Формула =E6/A6 рассчитывает значение в ячейке G6;
Ячейки G7:G18 заполните Маркером заполнения;
Составьте и введите самостоятельно такую формулу для ячейки Н5, чтобы остальные ячейки этого столбца заполнились Маркером заполнения.
В ячейку Н19 введите формулу, которая выбирает максимальное значение из всех ячеек диапазона Н5:Н18: =МАКС(H5:H18).
Отформатируйте таблицу.
Сохраните работу в своей папке под именем ПР-3 Фамилия.
Содержание отчета
Отчет должен содержать:
1. Наименование работы.
2. Цель работы.
3. Результат выполнения задания.
4. Вывод по работе.
Контрольные вопросы
1. Что представляет собой бухгалтерская отчетность?
2. Что включает в себя форматирование таблицы?
3. Как рассчитывается прибыль предприятия?
Литература
Трусов А.Ф. Excel 2007 для менеджеров и экономистов. Логистические, производственные и оптимизационные расчеты. / А. Ф. Трусов. - Издательство: ООО «Питер Пресс», 2009. – 256 с.
Мицкевич А. А. Сборник заданий по экономике. / А. А. Мицкевич. – М.: «Вита-Пресс», - 1998. – 144 с.: ил.
Гаращенко А.Б. Экономические и финансовые расчеты в Excel. / А. Б. Гаращенко, В. И. Пикуза. - Издательство: БХВ Петербург, 2007. – 278 с.
Просветов Г.И. Анализ данных с помощью Excel. Задачи и решения. / Г. И. Просветов. - Альфа-Пресс, 2009. – 352 с.
Аверчев И.В. Excel 2007 в учете и менеджменте. Пошаговый самоучитель по бухгалтерскому учету на компьютере Эксмо-Пресс, 2007. – 256 с.

ИНСТРУКЦИОННО – ТЕХНОЛОГИЧЕСКАЯ КАРТА
на выполнение практической работы №4
по дисциплине «Финансово-экономические расчеты в электронных таблицах»
Наименование работы: Оформление результатов финансово-экономического анализа с использованием офисных технологий.
Цель работы: Оформить результаты финансово-экономического анализа с использованием электронных таблиц Microsoft Excel.
Норма времени: 2 часа.
Оборудование, приборы, аппаратура, материалы: ПК с установленным лицензионным программным обеспечением.
Задание
Задание 1. Учет затрат предприятия.
Наименование материала Цена (Ц) Количество Сумма на конец месяца (С) Доля в общем объеме (Д)
На начало месяца (НМ) На конец месяца (КМ) Израсходовано (И) Краска 12,50 90 50 ? ? ?
Лак 28,30 60 10 ? ? ?
Бензин 2,00 140 60 ? ? ?
Керосин 1,80 90 30 ? ? ?
Эмаль 16,30 75 80 ? ? ?
Ацетон 11,80 60 15 ? ? ?
Итого ? ? ? ? ? ?
Средняя сумма расхода ?
Минимальная доля в общем объеме ?
Формулы расчета:
И=КМ-НМ
С=Ц*КМ
Д=С/(Итого С)*100
Расчеты в таблице производятся по следующим формулам Еxcel со ссылками на ячейки:
-   Израсходовано (И): Е6 =D6-C6;
-   Сумма на конец месяца (С): F6 =B6*D6;
-   Доля в общем объеме (Д): G6 =(B6/F6)*100;
Сохраните работу в своей папке под именем ПР-4 Фамилия.
Содержание отчета
Отчет должен содержать:
1. Наименование работы.
2. Цель работы.
3. Результат выполнения задания.
4. Вывод по работе.
Контрольные вопросы
1. Какие формулы используются для учета затрат предприятия?
2. Какая функция используется для расчета среднего значения?
3. Какая функция используется для поиска минимального значения?
Литература
Трусов А.Ф. Excel 2007 для менеджеров и экономистов. Логистические, производственные и оптимизационные расчеты. / А. Ф. Трусов. - Издательство: ООО «Питер Пресс», 2009. – 256 с.
Мицкевич А. А. Сборник заданий по экономике. / А. А. Мицкевич. – М.: «Вита-Пресс», - 1998. – 144 с.: ил.
Гаращенко А.Б. Экономические и финансовые расчеты в Excel. / А. Б. Гаращенко, В. И. Пикуза. - Издательство: БХВ Петербург, 2007. – 278 с.
Просветов Г.И. Анализ данных с помощью Excel. Задачи и решения. / Г. И. Просветов. - Альфа-Пресс, 2009. – 352 с.
Аверчев И.В. Excel 2007 в учете и менеджменте. Пошаговый самоучитель по бухгалтерскому учету на компьютере Эксмо-Пресс, 2007. – 256 с.
Михеева Е. В., Тарасова Е. Ю. Практикум по информационным технологиям в профессиональной деятельности экономиста и бухгалтера. / Е. В. Михеева, Е. Ю. Тарасова. – М.: Издательский центр «Академия», 2014. – 352 с.

ИНСТРУКЦИОННО – ТЕХНОЛОГИЧЕСКАЯ КАРТА
на выполнение практической работы №5
по дисциплине «Финансово-экономические расчеты в электронных таблицах»
Наименование работы: Обработка списков: поиск, сортировка, фильтрация.
Цель работы: Произвести работу со списками: искать и сортировать необходимую информацию.
Норма времени: 2 часа.
Оборудование, приборы, аппаратура, материалы: ПК с установленным лицензионным программным обеспечением.
Задание
Задание 1.
1. Создайте в своей рабочей папке новую электронную книгу с именем Зарплата.
2. На листе 1 создайте таблицу по образцу:
Ведомость начисления заработной платы
за октябрь 2013г. Табельный номер Фамилия И.О. Оклад (руб.) Премия (руб.) Всего начислено (руб.) Удержания (руб.) К выдаче (руб.)
27% 13% 10 Анищенко Г.А. 5700,00 ? ? ? ?
11 Григорьева Т.В. 4850,00 ? ? ? ?
12 Дорофеева А.Н. 7300,00 ? ? ? ?
13 Жидков П.П. 6250,00 ? ? ? ?
14 Мартынова Е.С. 5800,00 ? ? ? ?
15 Николаенко Т.В. 6400,00 ? ? ? ?
16 Потапова И.Л. 4500,00 ? ? ? ?
17 Степанова Т.Г. 6950,00 ? ? ? ?
18 Сабельников Р.Б. 9350,00 ? ? ? ?
19 Шилов С.С. 8700,00 ? ? ? ?
20 Штормов С.П. 6520,00 ? ? ? ?
Всего: ? ? ? ? ?
Максимальный доход ? Минимальный доход ? Средний доход ? 3. Произведите расчеты во всех столбцах таблицы, а также подсчитайтемаксимальный, минимальный и средний доход.
4. Присвойте листу 1 имя Зарплата октябрь. Сохраните.
5. Скопируйте содержимое листа Зарплата октябрь на лист 2.
6. Присвойте листу 2 имя Зарплата ноябрь.
7. Поменяйте название месяца в заголовке.
8. Установите премию – 32%.
9. Между столбцами Премия и Всего начислено вставьте новый столбецДоплата (5%).
10. Измените формулу для колонки Всего начислено.
11. К ячейке Е3 добавьте примечание «Премия пропорциональна окладу».
12. Скопируйте данные с листа Зарплата ноябрь на лист 3 и присвойте ему имяЗарплата декабрь.
13. Установите премию – 46 %, а доплату – 8 %.
14. Сохраните работу в своей папке под именем Зарплата.
Содержание отчета
Отчет должен содержать:
1. Наименование работы.
2. Цель работы.
3. Результат выполнения задания.
4. Вывод по работе.
Контрольные вопросы
1. Каким образом происходит расчет премии и доплат сотрудникам?
2. Каким образом рассчитывается минимальный и максимальный доход?
Литература
Трусов А.Ф. Excel 2007 для менеджеров и экономистов. Логистические, производственные и оптимизационные расчеты. / А. Ф. Трусов. - Издательство: ООО «Питер Пресс», 2009. – 256 с.
Мицкевич А. А. Сборник заданий по экономике. / А. А. Мицкевич. – М.: «Вита-Пресс», - 1998. – 144 с.: ил.
Гаращенко А.Б. Экономические и финансовые расчеты в Excel. / А. Б. Гаращенко, В. И. Пикуза. - Издательство: БХВ Петербург, 2007. – 278 с.
Просветов Г.И. Анализ данных с помощью Excel. Задачи и решения. / Г. И. Просветов. - Альфа-Пресс, 2009. – 352 с.
Аверчев И.В. Excel 2007 в учете и менеджменте. Пошаговый самоучитель по бухгалтерскому учету на компьютере Эксмо-Пресс, 2007. – 256 с.
Михеева Е. В., Тарасова Е. Ю. Практикум по информационным технологиям в профессиональной деятельности экономиста и бухгалтера. / Е. В. Михеева, Е. Ю. Тарасова. – М.: Издательский центр «Академия», 2014. – 352 с.

ИНСТРУКЦИОННО – ТЕХНОЛОГИЧЕСКАЯ КАРТА
на выполнение практической работы №6
по дисциплине «Финансово-экономические расчеты в электронных таблицах»
Наименование работы: Связанные таблицы. Расчет промежуточных итогов.
Цель работы: Выполнить задания со связанными таблицами и рассчитывать промежуточные итоги.
Норма времени: 2 часа.
Оборудование, приборы, аппаратура, материалы: ПК с установленным лицензионным программным обеспечением.
Задание
Задание 1.
Откройте файл Зарплата из своей рабочей папки.
2. Скопируйте содержимое листа Зарплата за декабрь на новый лист.
3. Присвойте листу имя Итоги за квартал. Измените название таблицы соответственно.
4. Отредактируйте лист Итоги за квартал согласно образцу:
Ведомость начисления заработной платы
за 4 квартал 2013г. Табельный номер Фамилия И.О. Подразделение Всего начислено (руб.) Удержания (руб.) К выдаче (руб.)
10 Анищенко Г.А. Отдел кадров 11 Григорьева Т.В. Плановый отдел 12 Дорофеева А.Н. Бухгалтерия 13 Жидков П.П. Отдел снабжения 14 Мартынова Е.С. Плановый отдел 15 Николаенко Т.В. Отдел снабжения 16 Потапова И.Л. Склад 17 Степанова Т.Г. Бухгалтерия 18 Сабельников Р.Б. Склад 19 Шилов С.С. Отдел снабжения 20 Штормов С.П. Склад 5. Произведите расчет квартальных начислений, удержаний и суммы к выдаче.
6. В ячейке D4 формула будет иметь вид:
='Зарплата октябрь'!E5+'зарплата ноябрь'!F5+' зарплата декабрь'!F5
Чтобы вставить в формулу адрес или диапазон ячеек с другого листа,следует во время ввода формулы щелкнуть по ярлыку этого листа ивыделить на нем нужные ячейки.
Вставляемый адрес будет содержать название этого листа.
7. Аналогично произведите квартальный расчет столбцов Удержания и Квыдаче.
8. Для расчета промежуточных итогов проведите предварительную сортировкупо подразделениям, а внутри подразделений – по фамилиям.
9. Для этого выделите весь список или одну ячейку списка и активируйтекоманду Сортировка меню Данные.
10. Рассчитайте промежуточные итоги по подразделениям, используя формулусуммирования. Для этого выделите всю таблицу А3:F14 и выполните командуДанные – Итоги.
11. Задайте параметры подсчета итогов:
При каждом изменении в – Подразделение;
Операция – Сумма;
Добавить итоги по – Всего начислено, Удержания, К выдаче;
Отметить галочкой операции – Заменить текущие итоги и Итоги под данными.
12. Изучите полученную структуру. Научитесь сворачивать и разворачиватьструктуру до разных уровней (кнопками «+», «-»).
Под структурированием понимается многоуровневая группировка строки столбцов таблицы и создание элементов управления, с помощьюкоторых легко можно скрывать и раскрывать эти группы.
13. Защитите лист Итоги за квартал от изменений. Для этого выполните командуСервис – Защита – Защитить лист. Задайте пароль, подтвердите. Убедитесь,что удаление и изменение данных невозможно.
14.Для снятия защиты с листа необходимо выполнить команду Сервис – Защита– Снять защиту с листа.
15. Сохраните файл Зарплата с произведенными изменениями.
Содержание отчета
Отчет должен содержать:
1. Наименование работы.
2. Цель работы.
3. Результат выполнения задания.
4. Вывод по работе.
Контрольные вопросы
1. Каким образом происходит расчет промежуточных итогов?
2. Основное предназначение связанных таблиц в MicrosoftExcel.
Литература
Трусов А.Ф. Excel 2007 для менеджеров и экономистов. Логистические, производственные и оптимизационные расчеты. / А. Ф. Трусов. - Издательство: ООО «Питер Пресс», 2009. – 256 с.
Мицкевич А. А. Сборник заданий по экономике. / А. А. Мицкевич. – М.: «Вита-Пресс», - 1998. – 144 с.: ил.
Гаращенко А.Б. Экономические и финансовые расчеты в Excel. / А. Б. Гаращенко, В. И. Пикуза. - Издательство: БХВ Петербург, 2007. – 278 с.
Просветов Г.И. Анализ данных с помощью Excel. Задачи и решения. / Г. И. Просветов. - Альфа-Пресс, 2009. – 352 с.
Аверчев И.В. Excel 2007 в учете и менеджменте. Пошаговый самоучитель по бухгалтерскому учету на компьютере Эксмо-Пресс, 2007. – 256 с.
Михеева Е. В., Тарасова Е. Ю. Практикум по информационным технологиям в профессиональной деятельности экономиста и бухгалтера. / Е. В. Михеева, Е. Ю. Тарасова. – М.: Издательский центр «Академия», 2014. – 352 с.

ИНСТРУКЦИОННО – ТЕХНОЛОГИЧЕСКАЯ КАРТА
на выполнение практической работы №7
по дисциплине «Финансово-экономические расчеты в электронных таблицах»
Наименование работы: Возможности фильтрации. Использование формы. Создание сводной таблицы.
Цель работы: Применить фильтрацию и сортировку в таблицах Microsoft Excel.
Норма времени: 2 часа.
Оборудование, приборы, аппаратура, материалы: ПК с установленным лицензионным программным обеспечением.
Задание
Задание 1.
1. Открыть рабочую книгу Зарплата и скопировать лист Итоги за квартал вновую книгу. Для этого щелкнуть правой кнопкой мышки на ярлык листаИтоги за квартал и из контекстно-зависимого меню выбрать командуПереместить/ Скопировать. В открывшемся окне установить параметры –новая книга и галочку Создать копию.
2. Сохранить Книгу1 под именем ПР-7 Фамилия.
3. Откройте Лист Итоги за квартал. Переименуйте в Список.
4. Выделите данные на листе и выполните команду Данные – Итоги –Убратьвсе.
5.Добавьте в книгу три новых листа и присвойте имена Автофильтр,Расширенный фильтр, Форма.
6. Скопируйте список на все три листа.
Автофильтр1. Откройте лист Автофильтр, выделите список и выполните команду Данные –Фильтр – Автофильтр.
2. Выберите из списка записи, используя критерии:
Подразделение – отдел реализации, отдел менеджмента;
Зарплата к выдаче – больше 20000 тысяч.
Расширенный фильтр
1. Откройте лист Расширенный фильтр.
2. Скопируйте имена полей списка и вставьте под таблицу, пропустив свободными 2-3 строки, чтобы данные не сливались.
3. Сформулируйте в области условий отбора (скопированные имена полей)следующие критерии:
Подразделение – Отдел реализации;
Всего начислено - > 20000тысяч;
К выдаче - < 30000 тысяч.
4. Проведите фильтрацию записей, выполнив команду Данные – Фильтр –Расширенный фильтр.
5. Укажите в открывшемся окне исходный диапазон, диапазон условий, сделайтеактивной опцию Поместить результат в диапазон (укажите свободнуюячейку под условиями отбора, также пропустив 2-3 строки).
Форма
1. Откройте лист Форма.
2. Просмотрите записи списка с помощью формы. Для этого выполните командуДанные – Форма.
3. Используя кнопку Критерии найти:
Сотрудников, у которых зарплата > 30000 тысяч рублей;
Сотрудника с табельным номером 12.
4. Сохраните работу в своей папке.
Содержание отчета
Отчет должен содержать:
1. Наименование работы.
2. Цель работы.
3. Результат выполнения задания.
4. Вывод по работе.
Контрольные вопросы
1. Каким образом происходит фильтрация в электронных таблицах?
2. Для чего используется автофильтр?
3. Для чего используется форма?
Литература
Трусов А.Ф. Excel 2007 для менеджеров и экономистов. Логистические, производственные и оптимизационные расчеты. / А. Ф. Трусов. - Издательство: ООО «Питер Пресс», 2009. – 256 с.
Мицкевич А. А. Сборник заданий по экономике. / А. А. Мицкевич. – М.: «Вита-Пресс», - 1998. – 144 с.: ил.
Гаращенко А.Б. Экономические и финансовые расчеты в Excel. / А. Б. Гаращенко, В. И. Пикуза. - Издательство: БХВ Петербург, 2007. – 278 с.
Просветов Г.И. Анализ данных с помощью Excel. Задачи и решения. / Г. И. Просветов. - Альфа-Пресс, 2009. – 352 с.
Аверчев И.В. Excel 2007 в учете и менеджменте. Пошаговый самоучитель по бухгалтерскому учету на компьютере Эксмо-Пресс, 2007. – 256 с.
Михеева Е. В., Тарасова Е. Ю. Практикум по информационным технологиям в профессиональной деятельности экономиста и бухгалтера. / Е. В. Михеева, Е. Ю. Тарасова. – М.: Издательский центр «Академия», 2014. – 352 с.

ИНСТРУКЦИОННО – ТЕХНОЛОГИЧЕСКАЯ КАРТА
на выполнение практической работы №8
по дисциплине «Финансово-экономические расчеты в электронных таблицах»
Наименование работы: Графическое представление данных различных экономических процессов.
Цель работы: Представить данные в электронных таблицах с помощью графиков и диаграмм.
Норма времени: 2 часа.
Оборудование, приборы, аппаратура, материалы: ПК с установленным лицензионным программным обеспечением.
Задание
Задание 1.
1. Откройте Excel и создайте рабочую книгу ПР-8 Фамилия.
2. Создайте таблицу по образцу:
Финансовая сводка за неделю (тыс. руб.)
Дни недели Доход Расход Финансовый результат
понедельник 3245,5 3628,5 ?
вторник 4572,5 5320,5 ?
среда 6251,66 5292,1 ?
четверг 2125,2 3824,3 ?
пятница 3896,6 3020,1 ?
суббота 5452,3 4262,1 ?
воскресенье 6050,6 4369,5 ?
ср. значение ? ? ?
Общий финансовый результат за неделю: ?
3. Произведите необходимые расчеты. Финансовый результат = Доход – Расход.
4. Постройте диаграмму (линейчатого типа) изменения финансовых результатов
по дням недели.
5.Для этого выделите диапазон ячеек D4:D10 и выполните команду Вставка –Диаграмма – тип Линейчатая.
6. Подписи оси Х укажите интервалячеек с днями недели А4:А10.
7. Отформатируйте диаграмму, чтобы она имела вид:

8. Произведите фильтрацию значений дохода, превышающих 4200 рублей.
9. Проследите, как изменилась построенная диаграмма.
10. Сохраните работу.
Задание 2.
С помощью Гистограммы отобразите данные о численности населения России(млн. чел) за 1970 – 2005 гг.
1. На новом листе создайте таблицу с исходными данными:
Численность населения
Год 1970 1977 1984 1991 1998 2005
Численность (млн. чел.) 130,1 137,6 147,4 148,3 148,1 140,1
2. По данным таблицы постройте гистограмму.
3. Активируйте построенную диаграмму и добавьте Линию тренда. На вкладкеМакет– Линия тренда – Дополнительные параметры линии трендавыберите Полиномиальная со степенью 4, название кривой – Линия тренда, прогноз – вперед на 1 период.

4. Установите подписи данных: Макет – подписи данных – У вершины, снаружи.
5. Поменяйте заливку столбцов: Макет - Форматвыделенного фрагмента – Заливка – Разноцветные точки
6. Сохраните измененные данные.
Содержание отчета
Отчет должен содержать:
1. Наименование работы.
2. Цель работы.
3. Результат выполнения задания.
4. Вывод по работе.
Контрольные вопросы
1. Какие виды диаграмм используются в системе электронных таблиц?
2. Какие шаги необходимо выполнить для построения диаграммы?
Литература
Трусов А.Ф. Excel 2007 для менеджеров и экономистов. Логистические, производственные и оптимизационные расчеты. / А. Ф. Трусов. - Издательство: ООО «Питер Пресс», 2009. – 256 с.
Мицкевич А. А. Сборник заданий по экономике. / А. А. Мицкевич. – М.: «Вита-Пресс», - 1998. – 144 с.: ил.
Гаращенко А.Б. Экономические и финансовые расчеты в Excel. / А. Б. Гаращенко, В. И. Пикуза. - Издательство: БХВ Петербург, 2007. – 278 с.
Просветов Г.И. Анализ данных с помощью Excel. Задачи и решения. / Г. И. Просветов. - Альфа-Пресс, 2009. – 352 с.
Аверчев И.В. Excel 2007 в учете и менеджменте. Пошаговый самоучитель по бухгалтерскому учету на компьютере Эксмо-Пресс, 2007. – 256 с.
Михеева Е. В., Тарасова Е. Ю. Практикум по информационным технологиям в профессиональной деятельности экономиста и бухгалтера. / Е. В. Михеева, Е. Ю. Тарасова. – М.: Издательский центр «Академия», 2014. – 352 с.

ИНСТРУКЦИОННО – ТЕХНОЛОГИЧЕСКАЯ КАРТА
на выполнение практической работы №9
по дисциплине «Финансово-экономические расчеты в электронных таблицах»
Наименование работы: Решение задач с использованием инструмента «Подбор параметра».
Цель работы: Решить экономические задачи при помощи инструмента «Подбор параметра».
Норма времени: 2 часа.
Оборудование, приборы, аппаратура, материалы: ПК с установленным лицензионным программным обеспечением.
Задание
Задание 1.
Рассчитайте какой должна быть годовая процентная ставка сумма на счете составит 175 тыс. рублей, если 80 тыс. рублей положены на 5 лет при начислении процентов каждые полгода.

Откройте MS Excel.
Заполните таблицу исходными данными.
В ячейку В6введитеформулу по образцу.
Отформатируйте таблицу.
Найдите решение задачи с помощью Подбора параметра. В меню Сервис выберите пункт Подборпараметра;
Установить в ячейке – указать адрес ячейки В6;
Значение – набрать 175 000;
Изменяя значение ячейки – указать адрес ячейки В2.
Ок.

Оставьтеоткрытымфайл для выполнения следующего упражнения.
Задание 2.
Предполагается вклад в сумме 50 000 руб. положить в банк на 3 месяца с ежемесячным начислением сложных процентов. Годовая ставка по вкладам 30 %. Определите, при каком уровне инфляции в месяц вклад не будет убыточным с точки зрения покупательной способности («Не будет убыточным» - это значит реальный доход равен 0).

Формулы расчета:
J=(1+)n

R=S-Начальный вклад
Откопируйтелист на котором решали предыдущую задачу.
На откопированном листе измените исходные данные.
Откорректируйте формулу в ячейке В6.
Дополните таблицу новыми данными.
В ячейки В7:В9 введите формулы: (=(1+B5)^B3; =B6/B7; =B8-B1)
Отформатируйте таблицу.
С помощью Подбора параметра определите допустимый уровень инфляции на период вклада. В меню Сервис выберите пункт Подборпараметра;
Установить в ячейке – указать адрес ячейки В9;
Значение – набрать 0;
Изменяя значение ячейки – указать адрес ячейки В5.
Ок.
Закройте MS Excel, сохранив файл в своей папке под именемПР-9 Фамилия.
Содержание отчета
Отчет должен содержать:
1. Наименование работы.
2. Цель работы.
3. Результат выполнения задания.
4. Вывод по работе.
Контрольные вопросы
1. Для чего используется инструмент «Подбор параметра»?
2. Каким образом используется инструмент «Подбор параметра»?
3. Какой пункт меню содержит инструмент «Подбор параметра»?
Литература
Трусов А.Ф. Excel 2007 для менеджеров и экономистов. Логистические, производственные и оптимизационные расчеты. / А. Ф. Трусов. - Издательство: ООО «Питер Пресс», 2009. – 256 с.
Мицкевич А. А. Сборник заданий по экономике. / А. А. Мицкевич. – М.: «Вита-Пресс», - 1998. – 144 с.: ил.
Гаращенко А.Б. Экономические и финансовые расчеты в Excel. / А. Б. Гаращенко, В. И. Пикуза. - Издательство: БХВ Петербург, 2007. – 278 с.
Просветов Г.И. Анализ данных с помощью Excel. Задачи и решения. / Г. И. Просветов. - Альфа-Пресс, 2009. – 352 с.
Аверчев И.В. Excel 2007 в учете и менеджменте. Пошаговый самоучитель по бухгалтерскому учету на компьютере Эксмо-Пресс, 2007. – 256 с.
Михеева Е. В., Тарасова Е. Ю. Практикум по информационным технологиям в профессиональной деятельности экономиста и бухгалтера. / Е. В. Михеева, Е. Ю. Тарасова. – М.: Издательский центр «Академия», 2014. – 352 с.

ИНСТРУКЦИОННО – ТЕХНОЛОГИЧЕСКАЯ КАРТА
на выполнение практической работы №10
по дисциплине «Финансово-экономические расчеты в электронных таблицах»
Наименование работы: Возможности использования «Подбора параметра» при наличии нескольких решений.
Цель работы: Решить экономические задачи при помощи инструмента «Подбор параметра» при наличии нескольких решений.
Норма времени: 2 часа.
Оборудование, приборы, аппаратура, материалы: ПК с установленным лицензионным программным обеспечением.
Задание
Задание 1.
Наблюдения показали, что функция предложения имеет вид Qs(Р)=20Р-140, а функция спросаQd(Р)=. Найдите равновесные цену и объем.

Откройте MS Excel.
Заполните таблицу исходными данными.
Введитеформулы.
Отформатируйте таблицу.
Найдите решение задачи с помощью Подбора параметра.
Закройте MS Excel, сохранив файл в своей папке под именемПР-10 Фамилия.
Задание 2.
Определить, при какой ежемесячной процентной ставке можно за год накопить 5 тыс. р., внося каждый месяц платеж на 10% больше предыдущего, начав с первого платежа 100 р.

Формулы расчета:
Pi=Pi-1·110%;
Di= Di-Di-1;
Откройте MS Excel.
Заполните таблицу исходными данными:
В ячейки А1, А3:Е3, А16 наберите соответствующие текстовые комментарии.
Ячейки А4:А15 введите данные с помощью маркеразаполнения:

Аналогично заполните ячейки С4:С15.
В ячейки С1 и В4 введите числа по образцу.
Организуйте формулы:
При вводе формулы в ячейку В5 необходимо применение функции округлениядовторого десятичного знака и ссылкиотносительного типа, поскольку каждая формула использует данные из ячейки выше нее. Таким образом в ячейку введите формулу =ОКРУГЛ(B4*110%;2);
Вячейки В6:В15 введите эту формулу маркером заполнения;
При расчете значения в ячейке Е4 используйте финансовую функцию БС:
Ставканаходится в ячейке $C$1;
Кпер в ячейкеC4;
Плт хранится в ячейкеD4;
Пс в ячейке-$B$4;
Вячейки Е5:Е15 введите эту формулу маркером заполнения;
ВячейкуD4 составьтеи введите формулу самостоятельно;
ЯчейкиD5:D15 заполните маркером заполнения;
Введите формулы в ячейки В16 и Е16.
Отформатируйте таблицу.
Найдите решение задачи с помощью Подбора параметра.
Закройте MS Excel, сохранив файл в своей папке под именемПР-10 Фамилия.
Задание 3.
Вклад в сумме 100 000 руб. размещается в банк на 3 года с ежемесячным начислением сложных процентов. Предполагаемый уровень инфляции 1,5% в месяц. Определите с помощью подбора параметра какая годовая ставка по вкладам обеспечит доход от вклада 10 000 руб. с точки зрения покупательной способности.

Формулы расчета:
J=(1+)n

R=S-Начальный вклад
Откройте файл «ПР-10 Фамилия» из своей папки.
Заполните таблицу новыми данными.
Откорректируйте формулу в ячейке В6.
С помощью Подбора параметра определите годовуюставку, которая обеспечит реальныйдоход от вклада 10 000 руб.
Закройте MS Excel, не сохраняя изменений в файле.
Содержание отчета
Отчет должен содержать:
1. Наименование работы.
2. Цель работы.
3. Результат выполнения задания.
4. Вывод по работе.
Контрольные вопросы
1. Для чего используется инструмент «Подбор параметра»?
2. Каким образом используется инструмент «Подбор параметра»?
3. Какой пункт меню содержит инструмент «Подбор параметра»?
Литература
Трусов А.Ф. Excel 2007 для менеджеров и экономистов. Логистические, производственные и оптимизационные расчеты. / А. Ф. Трусов. - Издательство: ООО «Питер Пресс», 2009. – 256 с.
Мицкевич А. А. Сборник заданий по экономике. / А. А. Мицкевич. – М.: «Вита-Пресс», - 1998. – 144 с.: ил.
Гаращенко А.Б. Экономические и финансовые расчеты в Excel. / А. Б. Гаращенко, В. И. Пикуза. - Издательство: БХВ Петербург, 2007. – 278 с.
Просветов Г.И. Анализ данных с помощью Excel. Задачи и решения. / Г. И. Просветов. - Альфа-Пресс, 2009. – 352 с.
Аверчев И.В. Excel 2007 в учете и менеджменте. Пошаговый самоучитель по бухгалтерскому учету на компьютере Эксмо-Пресс, 2007. – 256 с.
Михеева Е. В., Тарасова Е. Ю. Практикум по информационным технологиям в профессиональной деятельности экономиста и бухгалтера. / Е. В. Михеева, Е. Ю. Тарасова. – М.: Издательский центр «Академия», 2014. – 352 с.

ИНСТРУКЦИОННО – ТЕХНОЛОГИЧЕСКАЯ КАРТА
на выполнение практической работы №11
по дисциплине «Финансово-экономические расчеты в электронных таблицах»
Наименование работы: Разработка математической модели задачи оптимизации. Построение модели в электронных таблицах.
Цель работы: Разработать математическую модель задачи оптимизации и построить ее в системе электронных таблиц Microsoft Excel.
Норма времени: 2 часа.
Оборудование, приборы, аппаратура, материалы: ПК с установленным лицензионным программным обеспечением.
Задание
Задание 1.
Цех молокозавода выпускает эскимо и другой вид мороженого (назовем его просто «мороженое»).Эскимо в 2 раза дороже мороженого. За одну минуту выпускается 90 порций мороженого или 30 порций эскимо, возможен одновременный выпуск двух видов продукции. Из-за ограничения срока реализации продукции и недостаточного объема холодильных камер в течение часа на хранение может быть принято не более 3600 шт. изделий.Определить наибольшую стоимость выпускаемой продукции (прибыль) и оптимальный план выпуска мороженого и эскимо за одну минуту.
Построим математическую модель решения данной задачи.
Пусть одновременно выпускается 2 вида продукции. Обозначим число выпускаемых за 1 мин. эскимо – х, мороженого – у.
Пусть:
t1 – время, необходимое для производства одного эскимо,
t2 - время, необходимое для производства одного мороженого.
Из условия задачи следует, что за 1 мин. производится 90 порций мороженого или 30 порций эскимо, т.о. времени на производство одного эскимо затрачивается в 3 раза больше, чем на производство одного мороженого: t1=3 t2
За 1 мин. соотношение времени при одновременном выпуске каждого из двух видов продукции х и у составит:
t1х + t2у≤1
или, подставляя t1=3 t2
получим 3t2х + t2у≤1
вынесем t2 за скобки и разделим на него левую и правую части уравнения, т.о.3х+у≤1/t2
Но величина 1/t2 – это максимальный выпуск мороженого за 1 мин., т.е. она равна 90.
Итак, возможности производства определяет условие:
3х+у≤ 90
Еще одно условие - ограниченная емкость холодильника. В течение 1 часа холодильник может принять 3600 шт. продукции, т.е. за одну минуту
3600/60=60 порций:
х+у≤60
Обозначив цену 1 эскимо - с1 (руб), а цену мороженого – с2 (руб), можно записать в соответствии с условием задачи следующее соотношение цен на продукцию с1=2с2
Общая стоимость продукции, выпускаемой цехом за 1 минуту:
S= с1х+ с2у,
заменяя с1=2с2
получимS=2с2х+ с2у= с2(2х+у)
Поскольку с2 – заданная положительная константа, то для упрощения задачи можно принять с2=1.
По условию задачи необходимо найти наибольшую возможную стоимость выпускаемой продукции. Т.о. следует добиваться максимального значения целевой функции S=2х+у.
Обязательным условием решения задачи является условие неотрицательности величин х и у. Следует также подчеркнуть, что в целом ряде задач, в т.ч. и нашей, необходимо ввести еще одно ограничение: решение должно быть целочисленным.
Итак, учитывая все условия задачи, приходим к ее математической модели: среди целочисленных решений системы линейных неравенств

найти такое, при котором достигается максимизация линейной функции: S=2х+у.
Решение задачи на компьютере.
Запустите MSExcel.
728345213995В новой рабочей книге оформите лист.
В описанной модели необходимо максимизировать значение в ячейке В11. В качестве начальных значений х и у принимаются нули.
Ограничения задачи:
Условие Ячейки
Количество эскимо не должно превышать заданного значения $B$4<=$C$4
Количество мороженого не должно превышать заданного значения $B$5<=$C$5
Ограничение по объему холодильной установки $B$8<=$C$8
Ограничение по объему производства $B$9<=$C$9
Количества произведенного эскимо и мороженого не могут быть отрицательными числами $B$4:$B$5>=0
Количества произведенного эскимо и мороженого должны быть целыми числами $B$4:$B$5-целое
Дальнейшее решение задачи будем осуществлять с помощью надстройки «Поиск решения».
Выделите ячейку с оптимизируемым значением В11.
Выберите надстройку «Поиск решения». Загрузится надстройка и появляется диалоговое окно «Поиск решения».
Рисунок 2

В поле «Установить целевую ячейку» уже находится ссылка на выделенную на предыдущем шаге ячейку (при необходимости эту ссылку можно изменить).
Установить переключатель «Равной» максимальному значению (ищется максимальное значение целевой ячейки В11).
Перейдите в поле «Изменяя ячейки:» и укажите диапазон ячеек, которые должны изменяться в процессе поиска наилучшего решения. В данном примере это ячейки $B$4:$B$5.
Щелкните по кнопке «Добавить», чтобы ввести первое ограничение задачи. Откроется диалоговое окно «Добавление ограничения».

Введите первое ограничение $B$4:$B$5>=0
Щелкните по кнопке «Добавить», введите следующее ограничение и т.д. из таблицы на предыдущей странице.
Примечание: для задания целочисленности значений ячеек В4 и В5 из второго раскрывающегося списка выберите «цел», при этом в поле «Ограничение» автоматически появится «целое».
После ввода последнего ограничения нажмите ОК.
Рисунок 4

Нажав на кнопку «Параметры можно проверить, и при необходимости, изменить условия и варианты поиска решения, что в нашей задаче не требуется.
Нажмите кнопку «Выполнить». По окончании поиска решения появится диалоговое окно результатов.
Рисунок 5

Установите переключатель «Сохранить найденное решение», чтобы сохранить предложенные значения. С помощью этого диалогового окна можно также сформировать отчет.
1407160299085Нажмите ОК. Получится решение.
Сохраните решение задачи в своей папке под именем ПР-11 Фамилия.
Содержание отчета
Отчет должен содержать:
1. Наименование работы.
2. Цель работы.
3. Результат выполнения задания.
4. Вывод по работе.
Контрольные вопросы
1. Из каких этапов состоит разработка математической модели для решения задачи оптимизации в экономике?
2. Для чего применяется «Поиск решения» в построении математической модели задачи оптимизации в системе электронных таблиц?
Литература
Трусов А.Ф. Excel 2007 для менеджеров и экономистов. Логистические, производственные и оптимизационные расчеты. / А. Ф. Трусов. - Издательство: ООО «Питер Пресс», 2009. – 256 с.
Мицкевич А. А. Сборник заданий по экономике. / А. А. Мицкевич. – М.: «Вита-Пресс», - 1998. – 144 с.: ил.
Гаращенко А.Б. Экономические и финансовые расчеты в Excel. / А. Б. Гаращенко, В. И. Пикуза. - Издательство: БХВ Петербург, 2007. – 278 с.
Просветов Г.И. Анализ данных с помощью Excel. Задачи и решения. / Г. И. Просветов. - Альфа-Пресс, 2009. – 352 с.
Аверчев И.В. Excel 2007 в учете и менеджменте. Пошаговый самоучитель по бухгалтерскому учету на компьютере Эксмо-Пресс, 2007. – 256 с.
Михеева Е. В., Тарасова Е. Ю. Практикум по информационным технологиям в профессиональной деятельности экономиста и бухгалтера. / Е. В. Михеева, Е. Ю. Тарасова. – М.: Издательский центр «Академия», 2014. – 352 с.

ИНСТРУКЦИОННО – ТЕХНОЛОГИЧЕСКАЯ КАРТА
на выполнение практической работы №12
по дисциплине «Финансово-экономические расчеты в электронных таблицах»
Наименование работы: Решение задачи оптимального плана перевозок.
Цель работы: Разработать математическую модель задачи оптимального плана перевозок и построить ее в системе электронных таблиц Microsoft Excel.
Норма времени: 2 часа.
Оборудование, приборы, аппаратура, материалы: ПК с установленным лицензионным программным обеспечением.
Задание
Задание 1.
На предприятии X осуществляются транспортные перевозки с помощью четырех компаний до пяти населенных пунктов. Руководство компании решило распределить все количество перевозок между поставщиками транспортных услуг (транспортными компаниями) в определенной пропорции, выраженной в процентном соотношении — удельный вес в общем количестве перевозок. Известны также тарифы транспортных компаний за одну ездку и количество плановых перевозок до каждого населенного пункта в планируемом периоде.
Необходимо оптимально распределить ездки в населенные пункты между транспортными компаниями таким образом, чтобы транспортные расходы были минимальными.
1. Откройте Excel и создайте рабочую книгу ПР-12 Фамилия.
2. Создайте таблицу по образцу:

На рисунке расположены две таблицы: с исходными данными и расчетными данными. В ячейках D8:H11 расположены тарифы за 1 ездку в разрезе транспортных компаний до пунктов назначения, в ячейках D12:H12 — плановое количество ездок за период до пунктов назначения, в ячейках I8:I11 — удельный вес перевозок каждой транспортной компании в общем количестве планируемых перевозок за период. Эти ячейки для удобства не раскрашены. В ячейках J8:J12 и Н13 рассчитано число ездок по каждой транспортной компании и в целом за период. Формулы в этих ячейках выглядят следующим образом:
Ячейка Н13: =СУММ(D12:H12),
Ячейка J8: =I8*$H$13.
Данную формулу из ячейки J8 протаскиваем (копируем) в ячейки J9, J10, J11.
Ячейка J12: =СУММ(J8:J11).
Следующая таблица на листе посвящена расчету и называется «Расчет». Ячейки D19:Н22 предназначены для распределения количества ездок до пунктов назначения между транспортными компаниями. На рисунке в ячейках дано такое распределение, заполненное вручную. В ячейках D23:I27 рассчитаны суммы расходов на транспортные перевозки в разрезе транспортных компаний, оказывающих транспортные услуги, и пунктов назначений, а также итоги.
Значения в ячейках D24:Н27 получены перемножением количества ездок (ячейки D19:Н22) на тарифы (ячейки D8:Н12). В ячейку D24 запишем формулу:
=D19*D8.
Протащим (скопируем) формулу в ячейки D25:D27 и E24:Н27.
В ячейках D23:I23 формируются итоговые суммы транспортных услуг в разрезе пунктов назначения. Запишем в ячейку D23 формулу:
=СУММ(D24:D27).
Протащим (скопируем) эту формулу в ячейки Е23:I23.
В ячейках I24:I27 формируются итоговые суммы транспортных услуг в разрезе компаний, оказывающих эти услуги. Запишем в ячейку I24 формулу:
=СУММ(D24:H24).
Протащим (скопируем) ее в ячейки I25:I27.
Таким образом, стоимость транспортных расходов по компании в целом формируется в ячейке I23. В первоначальном расчете, представленном на рис. 1, данная сумма равна 35 790 руб.
3. Решите задачу оптимизации:
Скопируйте данный лист в эту же книгу. Далее необходимо приступить непосредственно к оптимизации. Задача — подобрать в ячейках D19:Н22 такие значения, чтобы в ячейке I23 была рассчитана минимальная сумма расходов на транспорт. Для этого воспользуемся инструментом «Поиск решений».
Для начала надо выбрать оптимизируемую ячейку (I23). Затем вызовем диалоговое окно «Поиск решения».
По умолчанию в Excel надстройка «Поиск решения» отключена. Чтобы активизировать ее в Excel 2007, щелкните значок Кнопка Microsoft Office , щелкните Параметры Excel, а затем выберите категорию Надстройки. В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти. В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК. После установки надстройка появится во вкладке «Данные»

В строке «Оптимизировать целевую функцию» будет стоять адрес оптимизируемой ячейки, в данном случае — $I$23. Выберем цель, поставив флажок «Минимум». В строке «Изменяя ячейки переменных» помещаются адреса ячеек, которые необходимо будет подобрать для достижения желаемого результата ($D$19:$Н$22). В поле запишем ограничения в соответствии с ограничениями. Для этого воспользуемся кнопкой «Добавить», которая откроет окно «Добавить ограничения». Введем одно из ограничений:
$D$19:$H$22 = целое,
$D$12:$H$12 = $D$18:$H$18,
$J$8:$J$11 = $I$19:$I22.
Чтобы добавить следующее ограничение, в этом же окне нажмите на кнопку «Добавить». Результатом этого действия будет добавление текущего ограничения в список ограничений, а поля окна «Добавить ограничения» будут очищены для ввода следующего ограничения. После того как введено последнее из ограничений, необходимо нажать на кнопку «ОК».
Порядок ввода ограничений не имеет значения. Главное — не забыть ни одно из ограничений.
Далее следует выбрать метод решения. Данная модель является линейной, т.к. связи в ней между данными для расчета и результирующим показателем можно описать линейными функциями.
Вернемся к диалоговому окну «Параметры поиска решений». Далее нажимаем кнопку «Найти решение», в результате чего появится окно с результатом поиска решения. Так как нам необходимо сохранить найденный результат, то ставим флажок «Сохранить найденное решение», в результате чего на нашем листе сохранится найденное решение. Нажмем кнопку «ОК».
В ячейках $D$19:$Н$22 появляются подобранные системой значения, при которых в ячейке I23 формируется минимальное значение стоимости транспортных услуг — 35 000 руб.
В данном случае отклонения от подобранного нами вручную результата составляют лишь 2,2 %, или 790 руб., но это означает лишь то, что мы вручную удачно подобрали решение.

Для того чтобы использовать ссылки на ячейки в составе сценария, необходимо сохранить этот сценарий, нажав на кнопку «Сохранить сценарий» в окне «Результат поиска решения», введя имя сценария и нажав кнопку «ОК». При этом исходные данные сохраняются.
4.Покажите результат преподавателю.
5. Сохраните изменения
Содержание отчета
Отчет должен содержать:
1. Наименование работы.
2. Цель работы.
3. Результат выполнения задания.
4. Вывод по работе.
Контрольные вопросы
1. Из каких этапов состоит решение задачи оптимального плана перевозок?
2. Для чего используется «Поиск решения» в задачи оптимального плана перевозок?
Литература
Трусов А.Ф. Excel 2007 для менеджеров и экономистов. Логистические, производственные и оптимизационные расчеты. / А. Ф. Трусов. - Издательство: ООО «Питер Пресс», 2009. – 256 с.
Мицкевич А. А. Сборник заданий по экономике. / А. А. Мицкевич. – М.: «Вита-Пресс», - 1998. – 144 с.: ил.
Гаращенко А.Б. Экономические и финансовые расчеты в Excel. / А. Б. Гаращенко, В. И. Пикуза. - Издательство: БХВ Петербург, 2007. – 278 с.
Просветов Г.И. Анализ данных с помощью Excel. Задачи и решения. / Г. И. Просветов. - Альфа-Пресс, 2009. – 352 с.
Аверчев И.В. Excel 2007 в учете и менеджменте. Пошаговый самоучитель по бухгалтерскому учету на компьютере Эксмо-Пресс, 2007. – 256 с.
Михеева Е. В., Тарасова Е. Ю. Практикум по информационным технологиям в профессиональной деятельности экономиста и бухгалтера. / Е. В. Михеева, Е. Ю. Тарасова. – М.: Издательский центр «Академия», 2014. – 352 с.

ИНСТРУКЦИОННО – ТЕХНОЛОГИЧЕСКАЯ КАРТА
на выполнение практической работы №13
по дисциплине «Финансово-экономические расчеты в электронных таблицах»
Наименование работы: Решение задачи определения оптимального плана производства.
Цель работы: Разработать математическую модель задачи определения оптимального плана производства и построить ее в системе электронных таблиц Microsoft Excel.
Норма времени: 2 часа.
Оборудование, приборы, аппаратура, материалы: ПК с установленным лицензионным программным обеспечением.
Задание
Задание 1.
Необходимо найти оптимальное соотношение объемов выпуска различных сортов конфет, так чтобы общая прибыль от реализации была максимальной. Конфеты можно производить в любых количествах (сбыт обеспечен), но запасы сырья ограничены: сахара 1000 кг, какао 700 кг, наполнителя 400 кг, ароматизатора 600 кг, сухого молока 250 кг. Каждому сорту конфет соответствует своя норма прибыли: от 1 кг конфет «Старт» 37 рублей, «Ириса» - 40 рублей, «Му-Му» - 52 рубля, «Ария» - 72 рубля, «Фея» - 65 рублей. Нормы расхода сырья на производство 1 кг конфет каждого вида приведены в таблице:
Расход сырья по сортам (на 1 кг конфет)
  Сахар Какао Наполнитель АроматизаторСухое молоко
1 2 3 4 5
1 Старт 0,360 г.u/ г. 0,120 0,120 0,250 0,000
2 Ирис 0,490 uг. 0,000 0,000 0,250 0,200
3 Му-му 0,350 г. 0,000 0,200 0,350 0,120
4 Ария 0,370 г. 0,450 0,210 0,200 0,150
5 Фея 0,340 г. 0,320 0,230 0,210 0,100

Формулы расчета:
Прибыль считается по формуле:
,
где с1 стоимость 1 кг конфет Старт, с21 кг конфет Ирис, с31 кг конфет Муму, с41 кг конфет Ария, с51 кг конфет Фея.
х1 план производства конфет Старт, х2 конфет Ирис, х3 конфет Муму, х4 конфет Ария, х5 конфет Фея.
При этом должны обеспечиваться ограничения по сырью:

где аij расход iго типа сырья на 1 кг конфет jго вида(например: а11 расход сахара на 1 кг конфет Старт, а12 на 1 кг конфет Ирис, а13 на 1 кг конфет Муму, а14 на 1 кг конфет Ария, а15 на 1 кг конфет Фея, а21 расход какао на 1 кг конфет Старт, а22 на 1 кг конфет Ирис, а23 на 1 кг конфет Муму, а24 на 1 кг конфет Ария, а25 на 1 кг конфет Фея, а31 расход наполнителя на 1 кг конфет Старт и т.д.)
b1 запас сахара, b2 запас какао, b3 наполнителя, b4 ароматизатора, b5 сухого молока.
По смыслу задачи:

Ход выполнения:
Заполните таблицу данными по образцу:
В ячейки А1:Н2, А3:А7, А8:В10 наберите соответствующие текстовые комментарии.
Ячейки В3:G7, C8:G9 заполните числами.
Организуйте в таблице формулы:
Общий расход каждого сырья на производство всех конфетскладывается из суммы произведений плана производства конфет, соответствующего видана расход данного сырья:Вызовите функцию СУММПРОИЗВ из категорииматематических в ячейкуН3;Определите ее аргументы;Для использования полученной формулы маркером заполнения один из диапазонов в окне Аргумента функции переведите в абсолютный вид (с помощью клавиши F4);

Ячейки Н4:Н7 заполните маркером заполнения.
Доход от производства всех конфет одного сорта определяется как произведение плана производства конфет этого сорта на доход от производства 1 кг конфет этого сорта:Активизируйте ячейку С10;Составьте и наберите формулу;
ЯчейкиD10:G10 заполните маркером заполнения.
Самостоятельно заполните ячейку Н10, в которой рассчитывается доход от всего производства конфет (это сумма средств, полученных от производства конфет разного вида).
Командой Сервис/ Поискрешения вызовите диалоговое окно Поиска решения:

Опишите целевуюячейку:Щелчком вызовите ссылку на ячейку $Н$10;Определите её характер – максимальное значение.
Перетаскиванием выделите изменяемые ячейки: $C$8:$G$8;
Организуйте ограничения:Значения всех изменяемых ячеек – это положительные числа, т. е. первое ограничение: $C$8:$G$8≥0:

Нажмите кнопку Добавить окна Поиск решения;В левой части окна Добавление ограничений выделением определите ссылки на ячейки, на которые накладываются ограничения: $C$8:$G$8;Через раскрывающийся список определитевид ограничения: ≥;В правой части окна Добавление ограничений наберитезначение ограничения: 0;Нажмите кнопку Добавить окна Добавление ограничений.
Расход сырья не может превышать его наличие, т. е. второе ограничение: $Н$3:$Н$7≤$В$3:$В$7:В левой части окна определите ссылки на ячейки, на которые накладываются ограничения: $Н$3:$Н$7;Через раскрывающийся список определитевид ограничения: ≤;В правой части окна наберитезначение ограничений: $В$3:$В$7;Нажмите кнопку Добавить.
Значения всех изменяемых ячеек – это целые числа, т. е. третье ограничение: $C$8:$G$8=целое:Определите ссылки на ячейки, на которые накладываются ограничения: $C$8:$G$8;Выберитевид ограничения: ЦЕЛ;Нажмите кнопку Ок, т. к. это последнее вводимое ограничение.
Запустите поиск, нажатием кнопки Выполнить в окне Поиска решения.
Нажмите кнопку Ok, чтобы сохранить результат поиска решения в одноимённом окне.
Закройте MS Excel, сохранив файл в своей папке под именемПР-13 Фамилия.
Содержание отчета
Отчет должен содержать:
1. Наименование работы.
2. Цель работы.
3. Результат выполнения задания.
4. Вывод по работе.
Контрольные вопросы
1. Из каких этапов состоит решение задачи оптимального плана производства?
2. Для чего используется «Поиск решения» в задачи оптимального плана производства?
Литература
Трусов А.Ф. Excel 2007 для менеджеров и экономистов. Логистические, производственные и оптимизационные расчеты. / А. Ф. Трусов. - Издательство: ООО «Питер Пресс», 2009. – 256 с.
Мицкевич А. А. Сборник заданий по экономике. / А. А. Мицкевич. – М.: «Вита-Пресс», - 1998. – 144 с.: ил.
Гаращенко А.Б. Экономические и финансовые расчеты в Excel. / А. Б. Гаращенко, В. И. Пикуза. - Издательство: БХВ Петербург, 2007. – 278 с.
Просветов Г.И. Анализ данных с помощью Excel. Задачи и решения. / Г. И. Просветов. - Альфа-Пресс, 2009. – 352 с.
Аверчев И.В. Excel 2007 в учете и менеджменте. Пошаговый самоучитель по бухгалтерскому учету на компьютере Эксмо-Пресс, 2007. – 256 с.
Михеева Е. В., Тарасова Е. Ю. Практикум по информационным технологиям в профессиональной деятельности экономиста и бухгалтера. / Е. В. Михеева, Е. Ю. Тарасова. – М.: Издательский центр «Академия», 2014. – 352 с.

ИНСТРУКЦИОННО – ТЕХНОЛОГИЧЕСКАЯ КАРТА
на выполнение практической работы №14
по дисциплине «Финансово-экономические расчеты в электронных таблицах»
Наименование работы: Управление оборотным капиталом.
Цель работы: Разработать математическую модель задачи управления оборотным капиталом и построить ее в системе электронных таблиц Microsoft Excel.
Норма времени: 2 часа.
Оборудование, приборы, аппаратура, материалы: ПК с установленным лицензионным программным обеспечением.
Задание
Задание 1.
Требуется с наибольшей доходностью разместить дополнительные средства в 1-, 2- и 6-месячных депозитах, учитывая собственные потребности в средствах (и гарантийный резерв).
Доход Срок
1-мес. депозит: 1% 1
3-мес. депозит: 4% 3
6-мес. депозит. 9% 6
Депозиты по месяцам:
1, 2, 3, 4, 5 и 6
1 и 4
1
Доход по процентам составляет всего 7700 р.
Одной из задач сотрудника или управляющего финансового отдела является управление средствами и краткосрочные вложения с максимальной прибылью при сохранении достаточного резерва для покрытия расходов. Более доходными могут оказаться долгосрочные депозиты, однако краткосрочные депозиты предоставляет более гибкие возможности управления финансовыми средствами.
В данной модели конечная сумма рассчитывается исходя из начальной (из прошлого месяца), плюс погашаемые депозиты, минус новые депозиты и с учетом ежемесячных потребностей самого предприятия(=СУММ(H7:H9)-СУММ(H20:H23)).
Необходимо определить девять сумм: ежемесячные суммы для 1-месячных депозитов; суммы депозитов 1 и 4 месяца для квартальных депозитов; сумму шестимесячного депозита в 1 месяце.

Параметры задачи:
Результат: Н4 Цель – получение наибольшего дохода по процентам
Изменяемые данные: В10-G10
B11, E11, B12 Сумма по каждому типу депозита
Ограничения: B10:G10>=0
B11:E11>=0
B12>=0
B14:H24>=100 000р. Сумма каждого депозита не может быть меньше нуля
Конечная сумма не должна быть меньше 100 000р.
Выделенные ячейки показывают изменяемый параметр,а затемненные – ограничения.

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

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

Вычисления и результаты решения задачи.
Для запуска оптимизатора нажмите кнопку Выполнить в окне Поиск решения. Программа начинает работать, в строке сообщений (слева внизу листа) появляется сообщение Постановка задачи.Ваша таблица с моделью и параметрами алгоритма автоматически приводится к стандартам постановки задач математического программирования. Это преимущество Excel. В других пакетах Вам пришлось бы оторваться от экономической сути задачи и заниматься формальной математической постановкой задачи. После этапа постановки решается задача. Чтобы прервать поиск решения, нажмите клавишу ESC. Microsoft Excel пересчитает лист с учетом найденных значений влияющих ячеек.
Найденное оптимальное решение предполагает получение дохода по процентам в размере 18 585р. при вложении максимально возможных сумм в шести- и трехмесячные депозиты, с последующим возвратом к одномесячным. Данное решение удовлетворяет всем поставленным ограничениям.
Если не была включена функция Показывать число итераций, то в результате нахождения оптимального решения можно автоматически создать отчет по результатом, пределам или устойчивости. К сожалению, эти отчеты очень неудобны. Они перегружены плохо читаемыми абсолютными ссылками со знаками доллара.
Ручной поиск оптимального плана.
Напомним условие задачи:Требуется с наибольшей доходностью разместить дополнительные средства в 1-, 2- и 6-месячных депозитах, учитывая собственные потребности в средствах (и гарантийный резерв).
Следовательно, нужно размещать такую сумму денег на депозит, чтобы конечная сумма была максимальная, при этом учитывая ежемесячные расходы, погашенные депозиты ( для ежемесячных – каждый месяц, для 3-м –раз в тои месяца).
Например, рассмотрим для первого месяца:
Месяц 1-й месяц 2-й месяц 3-й месяц 4-й месяц 5-й месяц 6-й месяц Конец
Нач. сумма 400 000р. 205 000р. 216 000р. 237 000р. 158 000р. 109 000р. 125 400р.
Пошаг. деп. 100 000 100 000 110 000 100 000 100 000 120 000
Проценты 1 000 1 000 1 400 1 000 1 000 2 300
1-м. депозит 100 000 100 000 100 000 100 000 100 000 100 000 3-м депозит 10 000 0 0 10 000 6-м депозит 10 000 Расходы 75 000 -10 000 -20 000 80 000 50 000 -15 000 60 000
Кон. сумма 205 000р. 216 000р. 237 000р. 158 000р. 109 000р. 125 400р. 187 700р.
Конечная сумма представляет разность начальной суммы и средств, затраченных на депозиты и расходы в каждом месяце:
400 000 – (100 000 + 10 000 + 10 000 + 75 000) = 205 000р.
Следовательно, в следующем месяце мы будем располагать суммой денег равной 205 000 р. и процентами с одномесячного депозита, однако к этой сумме прибавится денежная сумма в размере 10 000 от затраченных расходов на производство в прошлом месяце. И так будет повторяться в следующие шесть 6 месяцев, в исключении только каждый третий и шестой месяц.
В 3-м месяце к процентом от депозитов прибавиться доход с трехмесячного депозита, а в 6-м месяце процент с трехмесячного и шестимесячного депозитов.
Мы имеем определенные ограничения: конечная сумма в каждом месяце не должна быть меньше 100 000 р., т.к. наши максимальные расходы составляют 80 000 р. + непредвиденные расходы 30 000 р., так же мы не имеем права использовать больше той суммы денег, которая лежит на депозите.
По истечению максимального срока одного из депозитов необходимо подвести итог. Итоговая сумма будет включать в себя конечную сумму за шестой месяц, затраченную сумму денег первом месяце с трех депозитов, сумму процентов с трех депозитов с условием вычета окончательных затрат.
Решение:
Так как шестимесячные депозиты имеют самый большой процент дохода, то попробуем положить определенную сумму на такой депозит и рассчитаем доход. Допустим, что сумма вклада составляет 100 000 р., тогда конечная сумма будет составлять 9 000 р..
Попробуем разместить 20 000 р. на трехмесячный депозит, в итоге наш доход по истечению шести месяцев увеличивается на 1600р. Если мы увеличиваем сумму этого вклада до 50 000р., мы получаем напряженные ситуации в пятом и шестом месяцах, так как денежная сумма, преходящая из предыдущего месяца меньше 100 000р. и не покрывает необходимые расходы.
Оставляем 20 000р. на трехмесячном депозите и размещаем 10 000р. на одномесячном, получаем доход 11 200р. но не удовлетворение ограничению в пятом месяце.
Месяц 1-й месяц 2-й месяц 3-й месяц 4-й месяц 5-й месяц 6-й месяц Конец
Нач. сумма 400 000р. 195 000р. 205 000р. 225 000р. 146 000р. 96 200р. 111 300 р.
Пошаг. деп. 10 000 10 000 30 000 10 000 10 000 130 000
Проценты 100 100 900 100 100 9 900
1-м. депозит 10 000 10 000 10 000 10 000 10 000 10 000 3-м депозит 20 000 0 0 20 000 6-м депозит 100 000 Расходы 75 000 -10 000 -20 000 80 000 50 000 -15 000 60 000
Кон. сумма 195 000 р. 205 100 р. 225 200 р. 146 100 р. 96 200 р. 111 300 р. 191 200 р.
Весь расчет занимает достаточно много времени, и нельзя быть уверенным в том что вы точно найдете оптимальное решение, так как для того что бы были учтены все необходимые условия требуется составление математическом модели. Простой подбор чисел может дать положительный результат в очень редких случаях.
Как говорилось выше, для руководителя важно, чтобы решение было принято в очень короткий срок и с глубокой точностью, особенно учитывая то, что почти все руководители не владеют знанием математических моделей, а увеличение штата работников приведет к дополнительным расходам.
Содержание отчета
Отчет должен содержать:
1. Наименование работы.
2. Цель работы.
3. Результат выполнения задания.
4. Вывод по работе.
Контрольные вопросы
1. Из каких этапов состоит решение задачи оптимального плана производства?
2. Для чего используется «Поиск решения» в задачи оптимального плана производства?
Литература
Трусов А.Ф. Excel 2007 для менеджеров и экономистов. Логистические, производственные и оптимизационные расчеты. / А. Ф. Трусов. - Издательство: ООО «Питер Пресс», 2009. – 256 с.
Мицкевич А. А. Сборник заданий по экономике. / А. А. Мицкевич. – М.: «Вита-Пресс», - 1998. – 144 с.: ил.
Гаращенко А.Б. Экономические и финансовые расчеты в Excel. / А. Б. Гаращенко, В. И. Пикуза. - Издательство: БХВ Петербург, 2007. – 278 с.
Просветов Г.И. Анализ данных с помощью Excel. Задачи и решения. / Г. И. Просветов. - Альфа-Пресс, 2009. – 352 с.
Аверчев И.В. Excel 2007 в учете и менеджменте. Пошаговый самоучитель по бухгалтерскому учету на компьютере Эксмо-Пресс, 2007. – 256 с.
Михеева Е. В., Тарасова Е. Ю. Практикум по информационным технологиям в профессиональной деятельности экономиста и бухгалтера. / Е. В. Михеева, Е. Ю. Тарасова. – М.: Издательский центр «Академия», 2014. – 352 с.

ИНСТРУКЦИОННО – ТЕХНОЛОГИЧЕСКАЯ КАРТА
на выполнение практической работы №15
по дисциплине «Финансово-экономические расчеты в электронных таблицах»
Наименование работы: Расчет экономической эффективности инвестиций в проекты. Поиск решения.
Цель работы: Рассчитать экономическую эффективность инвестиций в проекты при помощи инструмента электронных таблиц «Поиск решения».
Норма времени: 2 часа.
Оборудование, приборы, аппаратура, материалы: ПК с установленным лицензионным программным обеспечением.
Задание
Задание 1. Имеются два инвестиционных проекта – «Проект 1» (краткосрочный проект) и «Проект 2» (долгосрочный проект). В оба проекта предполагается сделать инвестиции в сумме 1 000 тыс. руб. А в следующие периоды (годы) ожидается сделать инвестиции неравными денежными потоками. При этом в «Проект 1» эти поступления осуществляются более быстрыми темпами, а в «Проект 2» более медленными. Цена капитала каждого проекта (процентная ставка) равно 10% (0,1). Следует определить, какой проект должен быть отвергнут как менее выгодный.
Период «Проект 1» (краткосрочный проект), тыс. руб. «Проект 2» (долгосрочный проект), тыс. руб.
0 -1 000 -1 000
1 +500 +100
2 +400 +300
3 +300 +400
4 +100 +600
Необходимо рассчитать чистую дисконтированную стоимость «Проект 1».
Ход выполнения:
Откройте табличный процессор MS Excel и сохраните книгу под названием ПР-15 Фамилия.
Введите исходные данные для решения задачи из таблицы.

Произведите расчеты чистой дисконтированной стоимости проекта (доход проекта) = Денежный поток/(1+Процентная ставка) Период . Т. е. в ячейку С6 введите формулу =С4/(1+С5)^С2.
Скопируйте эту формулу в ячейки с D6 по G6. Ячейка H5 остается пустой.
В ячейку H6 введите формулу =СУММ(C6:G6).
Переименуйте лист в «Проект 1»
В итоге капитал (чистая дисконтированная стоимость) «Проекта 1» равен 79 тыс. руб .
Задание 2. Рассчитать чистую дисконтированную стоимость «Проект 2». Оценить инвестиции «Проекта 2» по критерию чистой дисконтированной стоимости.
Для создания таблицы для расчета «Проекта 2» сделайте копию листа «Проект 1». Переименуйте полученную копию листа , присвоив ему имя «Проект 2».
Измените заголовок таблицы и введите данные по денежному потоку «Проекта 2» из исходной таблицы. Перерасчет формул произойдет автоматически.
В итоге капитал «Проекта 2» равен 49 тыс. руб.
Задание 3. Дисконтированные сроки окупаемости проектов. Определить, через сколько лет будут возмещены инвестиции, изначально сделанные в проекты.
Переименуйте Лист 3, присвоив ему имя «Срок окупаемости» и создайте на этом листе таблицу с названием «Дисконтированные денежные потоки проектов». Столбец Доход заполняется данными с листов «Проект 1» и «Проект 2» строки Доход проекта.

В ячейки С4 и Е4 введите сумму начальной инвестиции по проектам 1 000 тыс. руб. со знаком минус (-).
Введите формулы для расчета таблицы: в ячейке С5 наберите формулу =С4+В5 и скопируйте ее вниз в ячейки С6:С8.
В ячейке Е5 наберите формулу =Е4+D5 и скопируйте ее в ячейки Е6:Е8.
Определите ячейки, в которых накопленный денежный поток становится положительным и выделите эти ячейки цветом (С6 и В7 по «Проекту 1», Е7 и D8 по «Проекту 2».
В ячейку А10 введите текст – Срок окупаемости (в годах).
В ячейку С10 введите формулу расчета срока окупаемости «Проекта 1» =А6+ABS(c6/B7). В ячейку Е10 введите формулу расчета срока окупаемости «Проекта 2» =A7+ABS(E7/D8).
Рассчитанный срок окупаемости по проектам: «Проект 1» - 2,95 г., «Проект 2» - 3,88 г. Если в компании срок окупаемости ограничен тремя годами, то «Проект 1» будет принят, а «Проект 2» будет отвергнут. Если проекты были бы альтернативными, то «Проект 1» будет предпочтительнее, потому что имеет более короткий срок возврата денег. Таким образом, при прочих равных условиях, чем короче срок окупаемости, тем проект ликвиднее. Поскольку долгосрочный проект рассматривается как более рискованный по сравнению с краткосрочным, то срок окупаемости проекта дает приблизительную оценку рискованности проекта.
Задание 4. Дисконтная ставка. Определить при какой дисконтной ставке доходность по проекту будет нулевой, но не убыточной.
Установить курсор в ячейку H6 листа «Проект 1».
На вкладке «данные» в группе «анализ» щелкните «поиск решения». В открывшемся окне введите следующие данные: целевая ячейка $H$6; переключатель установите в положение 0; изменяя ячейки $C$5:$G$5. Нажмите кнопку «выполнить». В открывшемся окне «результаты поиска решения» установите переключатель «сохранить найденное решение» и нажмите «ок». Найденное решение удовлетворяет всем условиям и ограничениям.
Для расчета средней дисконтированной ставки установите курсор в ячейку I5 и введите формулу расчета = СРЗНАЧ(С5:G5). Для «Проекта 1» дисконтированная ставка равна 13%.
Для расчета дисконтной ставки «Проекта 2» для ячейки H6 листа «Проект 2» повторите действия. Дисконтная ставка «Проекта 2» равна 11%.
Для обоих проектов цена капитала не ниже 10% и поэтому они должны быть приняты, поскольку дисконтные ставки обеспечивают большую доходность, чем цена капитала.
Содержание отчета
Отчет должен содержать:
1. Наименование работы.
2. Цель работы.
3. Результат выполнения задания.
4. Вывод по работе.
Контрольные вопросы
1. Каким образом рассчитывается экономическая эффективность инвестиций в проекты?
Литература
Трусов А.Ф. Excel 2007 для менеджеров и экономистов. Логистические, производственные и оптимизационные расчеты. / А. Ф. Трусов. - Издательство: ООО «Питер Пресс», 2009. – 256 с.
Мицкевич А. А. Сборник заданий по экономике. / А. А. Мицкевич. – М.: «Вита-Пресс», - 1998. – 144 с.: ил.
Гаращенко А.Б. Экономические и финансовые расчеты в Excel. / А. Б. Гаращенко, В. И. Пикуза. - Издательство: БХВ Петербург, 2007. – 278 с.
Просветов Г.И. Анализ данных с помощью Excel. Задачи и решения. / Г. И. Просветов. - Альфа-Пресс, 2009. – 352 с.
Аверчев И.В. Excel 2007 в учете и менеджменте. Пошаговый самоучитель по бухгалтерскому учету на компьютере Эксмо-Пресс, 2007. – 256 с.
Михеева Е. В., Тарасова Е. Ю. Практикум по информационным технологиям в профессиональной деятельности экономиста и бухгалтера. / Е. В. Михеева, Е. Ю. Тарасова. – М.: Издательский центр «Академия», 2014. – 352 с.