Для решения прикладных задач

Д.А.РЕПКИН

 

 

ПРИМЕНЕНИЕ MS EXCEL

ДЛЯ РЕШЕНИЯ ПРИКЛАДНЫХ ЗАДАЧ

В ЭКОНОМИКЕ

 

Учебно-методическое пособие

 

 

Киров

УДК 004.422.636.7:У(07)

P413

 

Рекомендовано к изданию методическим советом

факультета ПМТ ФГБОУ ВПО «ВятГУ»

 

Допущено редакционно-издательской комиссией методического совета ФГБОУ ВПО «ВятГУ» в качестве учебного пособия для студентов направления подготовки 080100 «Экономика» всех профилей подготовки заочной и заочно-вечерней форм обучения.

 

 

Рецензент:

кандидат технических наук, зав. кафедрой бизнес-информатики ФГБОУ ВПО «ВятГУ» Голованов А.А.

 

Репкин Д.А.

 

Применение MS EXCEL для решения прикладных задач в экономике: учебное пособие для студентов направления 080100 «Экономика» всех профилей подготовки, всех форм обучения/ Д.А.Репкин. – Киров: ПРИП ФГБОУ ВПО «ВятГУ», 2012. – xxx c.

 

В издании представлены практические и теоретические основы изучения дисциплины «Информатика». Теоретическая часть курса изложена в форме вопросов и подробных ответов на экзаменационные вопросы по курсу «Информатика». В практическую часть вошли задания на лабораторные работы.

 

 

УДК 004.422.636.7:У(07)

P413

 

ã ФГБОУ ВПО

«ВятГУ», 2012

Введение

Цель настоящего пособия – помощь студентам в изучении курса «Информационные технологии в экономике», предусмотренного федеральным государственным образовательным стандартом специальности «Экономика и управление на предприятии (машиностроение)».

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

Изучение дисциплины «Информационные технологии в экономике» базируется на информации, полученной в ходе изучения курса «Информатика». Знания, умения и навыки, полученные при изучении настоящего пособия, могут быть применены при решении прикладных профессиональных задач по дисциплинам «Логистика», «Управление проектами», «Экономическая оценка инвестиций», «Управление производством» и других.

Основные теоретические сведения по курсу

«Информационные технологии в экономике»

 

История развития IT-сферы. Технические

Средства информатизации

 

Исторически сфера информационных технологий (создание новой информации) развивалась следующим образом: в 1880 году в сфере IT работало 5 % населения и создавалось примерно 5 % ВВП, в 1946 году – 25 %, в 1980 году – 45 %, в 2010 году – 55 %. К работникам сферы IT относятся все инженерные и экономические специальности, все уровни управления обществом, образовательная деятельность. Часть человеческого общества, не относящаяся к IT-сфере, создаёт материальные ценности либо работает в сфере услуг. Приведённые статистические данные являются усредненными по всему населению Земли и могут существенно отличаться в разных странах. Момент, когда ВВП поровну формировался из материальных и информационных ресурсов, принято называть информационной революцией.

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

- 1875 г. – изобретение телефона – устройства для передачи речевой информации на большие расстояния по проводным каналам связи;

- 1895 г. – изобретение радио – передача информации стала возможна по беспроводным каналам связи с помощью электромагнитных волн;

- 1922 г. – изобретение иконоскопа сделало возможным создание телевидения – передачи видеозвуковой информации на большие расстояния. Иконоскоп – прообраз электронно-лучевой трубки – мог работать и для приёма информации, как видеокамера, и для передачи – как электронно-лучевая трубка;

- 30.06.1945 г. – Джоном фон Нейманом были опубликованы основные принципы работы ЭВМ. Первый принцип - двоичная система обработки информации, второй – принцип хранимой программы (принцип программного управления). Двоичная система счисления, работающая всего лишь с двумя цифрами - 0 и 1, оказалась наиболее удобна как с точки зрения помехозащищённости электронных устройств, так как электрические уровни этих цифр оказались максимально разнесены друг от друга, так и с точки зрения стоимости и принципа действия электронных приборов, ввиду того что значения 0 и 1 сопоставили самым устойчивым состояниям – «выключено» и «включено» соответственно. Принцип хранимой программы, записанной заранее в память ЭВМ, является основным отличием ЭВМ от других счётных устройств, например, калькулятора, и позволяет существенно уменьшить время решения задач за счёт многократного запуска одной и той же программы с различными исходными данными. Принцип хранимой программы можно распространить на ввод исходных данных и вывод результатов: исходные данные могут быть заранее представлены в виде файла или получены с разнообразных устройств ввода информации, вывод результатов также возможен в файл или на одно из устройств вывода. Принципы фон Неймана не устарели до настоящего времени, используются и будут использоваться в обозримом будущем в персональных и офисных ЭВМ. Архитектура (устройство) таких машин является классической и называется фон-неймановской;

- 1956 г. – появление вычислительных компьютерных сетей. Вычислительная сеть – способ объединения нескольких компьютеров для наращивания вычислительных ресурсов с целью ускорения решения прикладных задач. Исторически сети связи (телефон, телеграф, телекс, радио и телевидение) развивались независимо от чисто компьютерных (вычислительных) сетей. С середины 1990 годов сети связи и вычислительные сети интегрируются в единые инфотелекоммуникационные системы и сети, способные передавать любой тип информации: изображение, данные, звук и т.д.;

- 1979 г. – создание персонального компьютера (ПК, PC). До конца 1970-х годов компьютеры были малочисленными, энергоёмкими, дорогостоящими, имели значительные габаритные размеры и принадлежали в основном крупным организациям и государственным структурам, для их обслуживания требовался штат квалифицированных специалистов. Персональный компьютер изначально разрабатывался как доступный и по цене, и по простоте освоения для всех слоёв населения вне зависимости от уровня доходов и образования, поэтому самыми удачными определениями ПК являются следующие: 1) ПК – компьютер, цена которого не превышает $1000 (Указанная сумма актуальна для всего периода с 1979 года по настоящее время); 2) ПК – ЭВМ, предназначенная для широкого круга пользователей, не разбирающихся в вычислительной технике. Доступности работы с персональным компьютером способствовали появление операционных систем (ОС) PC DOS (1979 г., с 1981 г. – MS DOS), а позднее и линейки ОС Windows. Таким образом, массогабаритные характеристики персонального компьютера не являются определяющими это понятие, т.е. персональным является и настольный компьютер (Laptop), и мобильный (так называемый notebook), и планшетный, и даже коммуникатор.

Для выполнения базовых операций над информацией необходим ограниченный набор электронных устройств, совокупность которых необходима и достаточна для создания ЭВМ. Это так называемые устройства ядра вычислительной системы: процессор, основная память (оперативная и BIOS) и системная шина. Все остальные устройства ЭВМ не являются необходимыми, поэтому получили название внешних, или периферийных. Для персонального компьютера как дружественного пользователю устройства функциональные возможности, в том числе возможности общения с человеком, определяют именно периферийные устройства. Стоимость общепринятого набора периферийных устройств современного ПК составляет от 50 до 80 % его стоимости. К периферийным устройствам относят устройства связи с внешней средой и внешнюю память.

Все технические средства информационных технологий (аппаратуру) функционально делят на 4 класса устройств:

1. Вычислители, или процессоры. Выполняют математические расчёты и управляют передачей информации между всеми модулями ЭВМ. Однако даже при настоящем высоком уровне производительности процессоров следует отметить отсутствие у них каких-либо интеллектуальных способностей: процессор умеет выполнять только некоторое количество простейших операций, а все результаты его работы определяется последовательностью таких операций, называемой программой. Программы способен писать только человек, поэтому все интеллектуальные возможности в современный ПК заложены человеком; лишённый программ процессор справедливо называют «камнем» (во-первых, создан из кремния, во-вторых – сопоставимый с камнем по интеллекту), а всю аппаратную часть ЭВМ – «железом». Основная характеристика процессора – производительность – количество операций, выполняемых в единицу времени. Операции с целыми числами менее трудоёмкие, чем операции с дробными числами, поэтому производительность одного процессора может измеряться в разных единицах измерения – IPS (Integer per Second – целочисленных операций в секунду) или FLOPS (Floating points per second – операций с плавающей точкой (с дробными числами) в секунду).

2. Устройства памяти, или накопители информации. Виды, устройство, характеристики и принцип действия накопителей рассматриваются в дисциплине «Информатика». Главный параметр любого накопителя – ёмкость. Она измеряется в байтах или их производных, характеризует способность накопителя хранить определённое количество информации.

3. Средства связи. Основной параметр средств связи – скорость передачи информации. Она измеряется в битах в секунду или байтах в секунду, характеризует способность устройства безошибочно или с заданной минимальной вероятностью ошибки получать или передавать некоторое количество информации в единицу времени. По расстоянию, на которое передаётся информация, средства связи можно разделить на три группы:

- системные шины – имеют длину порядка 10 см, не выходят за пределы системного блока, обладают наивысшей пропускной способностью (скоростью передачи информации). Примеры – процессорная шина FSB, системные шины AGP, PCI-Express;

- внешние интерфейсы – имеют длину до 10м, соединяют системный блок с внешними устройствами, вынесенными за его пределы: принтером, модемом, flash-памятью. Управляющим узлом в этих интерфейсах является единственной и необходимый управляющий компьютер (Master), внешние устройства выступают в роли подчинённых (Slave). Примеры внешних интерфейсов – USB (универсальный), SATA (для накопителей), DVI (для монитора), RS-232 (или COM-порт – для технологического оборудования);

- компьютерные сети – имеют геометрический размер от 10 м до 10000 км, соединяют несколько компьютеров, являющихся самостоятельными равноправными устройствами. Примеры сетевых технологий – EtherNet, Wi-Fi, WWW.

4. Устройства ввода и вывода информации. Виды, устройство, характеристики и принцип действия данных периферийных устройств рассматриваются в дисциплине «Информатика».

 

 

В экономике

 

Информация, получаемая посредством применения информационных технологий, используется для управления производством, обществом, техническими объектами. Система, состоящая из объекта управления (ОУ) и устройства управления (УУ), называется системой управления (СУ) (рис. 1.2). Согласно основным положениям теории управления, лучшими качественными характеристиками обладает система управления с обратной связью, позволяющая корректировать принятые решения в зависимости от полученного результата. В итоге выходная величина такой системы с течением времени достигает заданного значения, а основными критериями качества системы становятся точность и быстродействие.

 

Рис. 1.2. Типовая структура системы управления

В настоящее время в качестве управляющего устройства СУ во всех прикладных областях используют ЭВМ. Различают для типа систем управления – автоматические (САУ) и автоматизированные (АСУ). Система автоматического управления не включает в контур управления человека, сама принимает решения и является полностью автономной. САУ применяются обычно при управлении техническими системами – станками, механизмами, технологическими линиями. Автоматизированная система управления в отличие от САУ включает в свой состав человека, или лицо, принимающее решение. Управляющее устройство (ЭВМ) в данном случае выступает в роли советчика, подсказчика, помощника в обработке больших объёмов информации, но окончательное решение принимает человек. АСУ применяют в более ответственных областях, чем САУ: при управлении финансами, предприятиями, социальными группами.

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

При управления предприятием информационные технологии могут быть использованы по меньшей мере в следующих областях:

- управление производством;

- управление финансами;

- управление кадрами;

- управление качеством;

- управление запасами, транспортом и сбытом (логистика);

- сфера учёта и отчётности;

- планирование и прогнозирование.

Приведённый перечень областей приложения информационных технологий в экономике фактически повторяет перечень дисциплин специальности «Экономика и управление на предприятии».

Для успешного внедрения информационных технологий как части системы управления на предприятии необходимо наличие следующих видов обеспечения:

- математическое обеспечение – даёт необходимые расчётные алгоритмы для решения прикладных задач;

- программное обеспечение – оформляет математические алгоритмы и расчётные формулы в виде прикладных программ с дружественным пользователю интерфейсом и профессиональной терминологией пользователя;

- аппаратное обеспечение – все технические средства информационных технологий, в первую очередь реализующее прикладное программное обеспечение;

- информационное обеспечение – отвечает за структуризацию, надёжное и безопасное хранение информации;

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

 

 

Задания к лабораторным работам

 

И графическом виде

Цель работы: изучение возможностей MS Ехсеl по отображению экономической информации: создание сводных таблиц и диаграмм.

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

По одной таблице данных

Исходные данные – перечень банковских вкладов физических лиц – приведены на рис. 2.1.1.

 

	А	B	C	D	E
	Фамилия	Тип вкл	Размер	Отделение	Примечание
	Тронина	Текущий		Северное	Выехал
	Югова	Депозит		Северное
	Золотухина	Текущий		Центральное
	Иванов	Текущий		Западное
	Клюкин	Текущий		Северное
	Казаков	Депозит		Центральное
	Рябов	Депозит		Центральное
	Попов	Депозит		Западное
	Огородов	Текущий		Северное
	Сухих	Депозит		Северное	Перевел в др. отд
	Панфилова	Текущий		Северное
	Корякова	Текущий		Северное
	Гончарова	Текущий		Центральное
	Корсакова	Депозит		Центральное	Сменил адрес
	Русских	Депозит		Центральное
	Жевлаков	Текущий		Западное
	Пинегин	Текущий		Центральное
	Красовский	Депозит		Центральное
	Потанин	Текущий		Центральное

 

Рис. 2.1.1. Исходные данные для задачи 1

 

Порядок выполнения задания

 

1. Введите исходные данные на рабочем листе во вновь созданном файле MS Excel. Присвойте данному листу имя «Вклады» (на ярлыке листа Excel, находящемуся внизу окна рабочей книги, выполните двойной щелчок мышью, после чего старое имя листа замените на требуемое).

2. Определение количества вкладов, хранящихся в различных отделениях банка (первый способ построения сводных таблиц).

2.1. Запустите Мастер сводных таблиц командой Данные®Сводная таблица. В появляющихся последовательно трёх диалоговых окнах выберите следующие настройки (рис. 2.1.2). В последнем диалоговом окне Шаг 3 из 3 нажмите кнопку «Макет».

Рис. 2.1.2. Последовательность настроек

для создания сводной таблицы

 

2.2. В окне Макета справа в виде кнопок представлены заголовки полей исходной базы данных (заголовки столбцов исходной таблицы), по центру – «заготовка» сводной таблицы, которая путём перетаскивания кнопок приобретает необходимую структуру, в данном случае кнопку «Отделение» перетащите в поле строк, а кнопку «Размер вклада» – в поле Данные макета сводной таблицы.

2.3. В поле данных после перетаскивания появляется кнопка Сумма по полю раз, нажатием на которую можно выбрать способ обработки данных для сводного отчёта, в данном случае Кол-во значений по полю Размер.

2.4. После выхода из окна Макет и завершения работы Мастера сводных таблиц (кнопка «Готово») в окне MS Excel появляется сводная таблица, изображённая на рис. 2.1.3.

 

Кол-во значений по полю Размер
Отделение	Всего
Западное
Северное
Центральное
Общий итог

 

Рис. 2.1.3. Результаты отображения

количества вкладов по отделениям банка

 

3. Определение суммарного размера по каждому виду вкладов по отделениям банка (второй способ построения сводных таблиц).

3.1. Запустите заново Мастер создания сводных таблиц. На предложение Excel создать новый отчёт на основе уже существующего ответьте «Нет», т.е. создайте независимый отчёт. Окно Макет не открывайте, а сразу завершите диалог Мастера сводных таблиц командой Готово. При этом на рабочем столе появится новый пустой макет сводной таблицы, а в панели инструментов «Сводные таблицы» будет выведен список полей таблицы исходных данных (рис. 2.1.4).

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

 

3.2. Заполнение сводной таблицы выполняется аналогично первому способу перетаскиванием кнопок из панели «Сводные таблицы» в соответствующие поля заготовки таблицы на листе Excel. Преимуществом данного способа является интерактивное заполнение сводной таблицы, которое можно интерактивно откорректировать (перетаскиванием полей сводной таблицы на другие поля или, для удаления поля, на любое свободное от заготовки таблицы место на рабочем листе). В одно поле сводной таблицы могут быть при необходимости перемещены несколько полей из таблицы исходных данных (см. рис. 2.1.5 – поля «Отделение» и «Тип вклада» вместе размещены в поле строк сводной таблицы). Кроме того, Excel не требует заполнять все поля заготовки сводной таблицы, необходимо лишь наличие двух полей, одно из которых – поле Данных.

3.3. Результатом работы по п. 3 должны быть таблица, изображённая на рис. 2.1.5.

Рис. 2.1.5. Результат отображения объёма различных типов вкладов

по отделениям банка

 

4. Измените структуру сводной таблицы, полученной в п. 3, так, как это показано на рис. 2.1.6. Допускается как создание новой сводной таблицы по любому из изученных алгоритмов, так и корректировка формы отображения сводной таблицы, полученной в п. 3.

 

Рис. 2.1.6. Результат отображения объёма различных типов вкладов

по отделениям банка

 

4.1. Двойной щелчок по ячейке сводной таблице вызывает автоматическое создание новой сводной таблицы, обобщающей сведения о данной ячейке. Ознакомьтесь с данной функцией Excel.

4.2. При изменении таблицы исходных данных Excel не гарантирует автоматическое обновление сводных таблиц и диаграмм. Для актуализации данных сводных таблиц выберите в контекстном меню сводной таблицы или на панели инструментов «Сводные таблицы» функцию «Обновить данные». Проверьте работоспособность данной функции.

 

Задача 2. Изучение технологии создания сводных таблиц

по нескольким однотипным таблицам исходных данных

Исходные данные – сведения об объёмах продаж распространителей-работников торговой фирмы за 3 месяца. Структура таблицы с месячным объёмом продаж изображена на рис. 2.1.7.

 

	Чингисханов	Батыев	Узбеков	Тимуров	Тохтамышев
Парфюмерия
Быт. Товары
Продовольствие
Топливо
Расх. Мат-лы
Электроника

 

Рис. 2.1.7. Структура таблицы исходных данных

 

Порядок выполнения задания

 

1. Создайте на новом листе структуру таблицы продаж по образцу (рис. 2.1.7). Оформите таблицу по своему усмотрению.

2. Присвойте рабочему листу с таблицей имя «Продажи январь».

3. Скопируйте лист «Продажи январь» два раза, назовите полученные листы «Продажи февраль» и «Продажи март». Щёлкните правой кнопкой мыши по ярлычку «Продажи январь». В контекстном меню выберите команду Переместить/Скопировать. В открывшемся диалоге установите флажок Создать копию и нажмите кнопку ОК. В рабочей книге появится копия листа «Продажи январь» с названием «Продажи январь (2)».

4. Заполните таблицы рабочих листов «Продажи январь», «Продажи февраль», «Продажи март» числовыми значениями по своему усмотрению. Желательно выбирать численные значения одного порядка, значения объёмов продаж на соседних листах должны различаться.

5. Создайте общую сводную таблицу по рабочим листам «Продажи январь», «Продажи февраль», «Продажи март». Выполните команду Данные ® Сводная таблица. В первом диалоговом окне укажите, что таблица создаётся на основе данных, расположенных в нескольких диапазонах консолидации, а вид создаваемого отчёта – сводная таблица. Во втором диалоговом окне укажите, что необходимо создать одно поле сводной таблицы. В следующем диалоговом окне укажите диапазоны ячеек, где находятся исходные данные для таблицы: выделите мышью диапазон ячеек А1:F7 на листе «Продажи январь» и щёлкните кнопку Добавить. Такие же диапазоны с листов «Продажи февраль» и «Продажи март» добавьте в список диапазонов исходных данных аналогично. В последнем диалоговом окне Мастера создания сводных таблиц поставьте переключатель в положение, соответствующее созданию сводной таблицы на новом листе. Здесь же дайте этому листу имя «Сводная таблица». Структура получившейся сводной таблицы должна быть такой же, как на рис. 2.1.8.

 

 

Страница1	(Все)
Сумма по полю Значение	Столбец
Строка	Батыев	Тимуров	Тохтамышев	Узбеков	Чингисханов	Общий итог
Быт. Товары
Парфюмерия
Продовольствие
Расх. Мат-лы
Топливо
Электроника
(пусто)
Общий итог

 

 

Рис. 2.1.8. Структура сводной таблицы для задачи № 2

 

6. Поменяйте местами методом перетаскивания заголовки Строка и Страница 1 в полученной сводной таблице (ячейки А1 и А4), при этом в ячейках А6 – А8 появятся надписи Объект1, Объект2 и Объект3. Вручную в строке формул переименуйте их в названия месяцев, по которым составлялась сводная таблица, в соответствии с численными значениями объёмов продаж, расположенными в соответствующих строках.

7. Переименуйте заголовки в сводной таблице: имена заголовков столбцов, строк и страниц выберите в соответствии с их содержимым самостоятельно.

8. Постройте сводную диаграмму по Вашей сводной таблице. Для этого однократно щёлкните левой кнопкой мыши по любой из ячеек данных сводной таблицы и выполните команду Вставка ® Диаграмма.

9. Покажите полученную рабочую книгу MS Excel преподавателю.

 

Подготовка шаблона для решения задачи

Фрагмент рабочего листа MS Excel с введёнными исходными данными и формулами изображён на рис. 2.2.1.

 

	А	B	C
	Технико-экономическое обоснование торговой операции
	Закупка
	тип товара	сахар
	Ед. измерения	тонны
	Цена за единицу, руб.
	Размер партии
	Цена всей партии, руб.	=B7*B8
	Кредит
	% по кредиту в год	0.2
	Срок кредита, мес.
	Страховка кредита	0.1	=B16*(1+B13/12*B14)*B15
	Необходимая сумма кредита, руб.	=(B9+B21+B22+C24+C25+C26+C27)/(1-(B15+B23)-B13/12*B14*(B15+B23))
	% по кредиту в месяц, руб.	=B16*B13/12
	Накладные расходы
	Транспортные расходы
	Непредвиденные расходы
	Страхование в пути	0.02	=B23*B9
	Акциз	0.1	=B24*B9
	НДС	0.1	=B25*(B9+C23+C24+C26+C27)
	Таможенный сбор	0.01	=B26*B9
	Таможенный налог	0.15	=B27*B9
	Реализация товара и возврат кредита
	Цена реализации за ед., руб.
	Цена реализации за партию, руб.	=B31*B8
	Возврат кредита с %, руб.	=B16+B17*B14
	Прибыль до вычёта налогов, руб.	=B32-B33

 

Рис. 2.2.1. Шаблон MS Excel для решения задачи

Параметра»

 

2.1. С заданными исходными данными значение прибыли до вычета налогов, рассчитанное в ячейке B34, является отрицательным. Если нужно обеспечить безубыточность операции, т.е. нулевое значение прибыли, меняют цену реализации (цену реализации в ячейке В31 измените на 27000 и убедитесь, что значение прибыли (убытка) в В34 изменилось). Можно изменять значение в ячейке В31 вручную, значение прибыли в ячейке В34 будет меняться, но такой вариант требует значительных затрат времени, не точен, а следовательно, неэффективен. Значение цены реализации подбирают автоматически. Для этого вызовите надстройку Подбор параметра командой Сервис ® Подбор параметра и заполните в открывшемся окне поля так, как изображено на рис. 2.2.2. После нажатия на кнопку ОК убедитесь (по сообщению в открывшемся окне), что решение найдено.

 

 

Рис. 2.2.2. Пример заполнения окна Подбор параметра

 

2.2. Решите по аналогии с п. 2.1 следующую задачу: получите прибыль X руб. за счёт изменения параметра Y. Номер варианта задания выберите в соответствии с номером компьютера. Варианты заданий приведены в табл. 2.2.1.

 

Таблица 2.2.1

Варианты заданий для самостоятельного решения

к лабораторной работе № 2

Номер варианта	Значение прибыли X, руб.	Название варьируемого параметра Y
	10 000 000	Закупочная цена (B7)
	3 000 000	% по кредиту (В13)
	500 000	Страхование в пути (В23)
	2 000 000	Акциз (В24)
	2 500 000	НДС (В25)
	300 000	Таможенный налог (В27)
	8 000 000	Срок кредита (В14)
	800 000	Страхование кредита (В15)
	2 500 000	Транспортные расходы (В21)
	200 000	Непредвиденные расходы (В22)
	1 750 000	Таможенный сбор (В26)

Покажите полученную рабочую книгу MS Excel преподавателю.

 

2.3. Лабораторная работа № 3. Многовариантный

Подготовка исходных данных

 

Для автоматизации процедуры оценки чувствительности NPV создайте шаблон в MS Excel (рис. 2.3.1). Присвойте листу имя «Анализ чувствительности NPV».

 

	A	B	C	D
	Анализ чувствительности NPV
	Количество	0.00	Начальные инвестиции	0.00
	Цена	0.00	Постоянные расходы	0.00
	Переменные расходы	0.00	Амортизация	0.00
	Норма дисконта	0.00	Остаточная стоимость	0.00
	Срок реализации	0.00	Налог на прибыль	0.00
	Чистые платежи	0.00		значение NPV
			варьир. параметр	0.00

 

Рис. 2.3.1. Шаблон MS Excel

для решения задачи анализа чувствительности

 

Значения параметров в шаблоне заполните наиболее вероятными значениями, взятыми из табл. 2.3.1.

Ячейкам, в которых располагаются параметры проекта, для улучшения визуализации формул, а также для оптимизации условий их копирования-перемещения присвойте символьные имена. Например, ячейка B2 должна именоваться не адресом на листе «Анализ чувствительности NPV» B2, а символьным именем «Количество». Последовательность присвоения имени: выделите ячейку B2, выполните команду Вставка ® Имя ® Присвоить, согласитесь с именем, предлагаемым MS Excel – нажмите кнопку OK. Если имя, предлагаемое MS Excel по умолчанию, Вас не устраивает, введите имя с клавиатуры; при этом не должно появиться совпадающих имен, а также имя ячейки не должно содержать пробелов. Символьные имена для ячеек, а которых располагаются параметры проекта, приведены в табл. 2.3.2.

 

Таблица 2.3.2

Символьные имена для ячеек в шаблоне MS Excel

Адрес ячейки	Имя переменной (ячейки)	Адрес ячейки	Имя переменной (ячейки)
В2	Количество	D2	Начальные_инвестиции
В3	Цена	D3	Постоянные_расходы
В4	Переменные_расходы	D4	Амортизация
В5	Норма_дисконта	D5	Остаточная_стоимость
В6	Срок_Реализации	D6	Налог_на_прибыль
B9	Чистые_платежи	D10	NPV

В ячейки B9 и B10 вводятся следующие формулы:

	(2.3.2)
	(2.3.3)

где ПС (ставка; кпер; выплата; бз; тип) – финансовая функция MS Excel, возвращающая текущий объём вклада. Текущий объём – это общая сумма, которую составят будущие платежи. Например, когда деньги берутся взаймы, заимствованная сумма и есть текущий объём для заимодавца. Более подробную информацию об аргументах функции можно получить в справочной системе MS Eхcel. В пакете MS Office 2000 эта функция носит имя ПЗ.

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

После ввода формул (2.3.2) и (2.3.3) ячейки B9 и D10 должны принять значения 1460 и 3658 руб. соответственно. Формат ячеек Денежный при желании можно задать соответствующей командой (Формат ячейки ® Число).

 

Построение диаграммы

 

По результатам анализа чувствительности функции NPV к значению параметров Цена изделия и Объём выпуска изделий строится объёмная гистограмма:

- выделите диапазон ячеек D11:H15;

- вызовите команду Вставка ® Диаграмма. В открывшемся окне последовательно выберите Объёмную гистограмму: во вкладке Ряд окна Исходные данные в поле «Подписи оси Х» внесите диапазон ячеек D10:H10 (выделите диапазон мышью); в окне Размещение диаграммы желательно установить переключатель в режим «На имеющемся листе», остальные настройки диаграммы выполните произвольно.

 

Порядок решения задачи

Подготовка исходных данных

1.1. Введите на рабочем листе Excel следующие исходные данные (рис. 2.4.1).