1 Занести исходные данные в ячейки электронной таблицы.
2 Разработать макет таблицы. Для изменения ширины столбца потянуть с помощью мыши границу столбца в заголовочной части.
3 Для создания сетки таблицы выделить блок ячеек, для которых требуется разметить сетку и нажать кнопку «Обрамление» на панели инструментов.
Рис. 6 Занесение исходных данных и форматирование таблицы
4 Занести расчетные формулы в ячейки таблицы. Установить курсор в необходимую ячейку. Для ввода функции воспользоваться кнопкой «Мастер функций» на панели инструментов или пунктом меню Вставка → Функция…
5 На первом шаге работы мастера функций выбирается категория функции (Финансовые) и необходимая функция (для расчета амортизации линейным методом это функция АПЛ, а методом суммы чисел лет – АСЧ).
6 По выбранной функции можно получить справку, щелкнув по ссылке «Справка по этой функции» в окне мастера функций.
7 Для перехода ко второму шагу работы мастера функций нажать кнопку ОК.
Рис. 7 Окно мастера функций
8 На втором шаге работы мастера функций задаются аргументы выбранной функции. Для функции АПЛ аргументами являются начальная стоимость, остаточная (ликвидационная) стоимость и срок эксплуатации. Для функции АСЧ дополнительно указывается период для которого ведется расчет амортизации.
9 Вводить аргументы можно либо непосредственно в полях ввода окна мастера (например, константы), либо установить курсор в соответствующее поле ввода и щелкнуть по ячейке таблицы, значение которой будет использовано в качестве аргумента. Если окно мастера перекрывает область таблицы, можно свернуть его, нажав на кнопку «Свернуть окно» рядом с полем ввода, и окно свернется до строки ввода. После выбора аргумента надо нажать на кнопку «Развернуть окно» рядом с полем ввода для восстановления окна мастера.
|
|
10 Завершить ввод аргументов, нажав на кнопку ОК в окне мастера. Формула будет занесена в выбранную ячейку таблицы.
11 Так как формулы для всех расчетных периодов будут аналогичными, можно воспользоваться копированием введенной формулы в другие ячейки. Но для этого в формуле надо изменить ссылки на ячейки, которые не должны изменяться при копировании, на абсолютные. Для задания абсолютной ссылки надо добавить символ $ к адресу столбца и/или строки ячейки. Так как в нашем случае копирование будет производиться по строкам, достаточно сделать абсолютной только ссылку на строку. Это можно было сделать на этапе ввода аргументов в мастере функций или отредактировать уже введенную формулу. (Обратите внимание, что ссылка на ячейку D3 в функции АСЧ остается относительной, так как расчетный период должен изменяться.)
Рис. 8 Задание аргументов функции АПЛ
12 Для редактирования содержимого ячейки надо войти в режим редактирования «по месту» двойным щелчком по этой ячейке, или установить курсор в редактируемую ячейку и перейти в строку формул.
Рис. 9 Редактирование и копирование формул
13 Для копирования формулы надо подвести курсор мыши к правому нижнему углу ячейки (курсор примет вид перекрестия), нажать левую кнопку мыши и, удерживая ее нажатой, растянуть рамку на область копирования. Для копирования также можно воспользоваться кнопками «Копировать» и «Вставить» на панели инструментов или соответствующими пунктами меню Правка.
|
|
14 Сохранить результаты работы в файле. Воспользоваться пунктом меню Файл → Сохранить.
ДОПОЛНИТЕЛЬНЫЕ ЗАДАНИЯ
1 Используя справочную систему MS Excel познакомьтесь с финансовыми функциями начисления амортизации.
2 Познакомьтесь с финансовыми функциями для анализа потоков платежей.
3 Познакомьтесь с финансовыми функциями для анализа облигаций.
4 Познакомьтесь с финансовыми функциями для анализа операций с ценными бумагами.
5 Познакомьтесь со статистическими функциями для характеристики среднего.
6 Познакомьтесь со статистическими функциями корреляционного и регрессионного анализа.
ЗАДАНИЯ ПО ВАРИАНТАМ
1 Имеются данные об общехозяйственных расходах предприятия и объемах производства продукции:
| Месяц | Объем производства продукции, шт. | Общехозяйственные расходы, руб. | Месяц | Объем производства продукции, шт. | Общехозяйственные расходы, руб. |
| Январь | Июль | ||||
| Февраль | Август | ||||
| Март | Сентябрь | ||||
| Апрель | Октябрь | ||||
| Май | Ноябрь | ||||
| Июнь | Декабрь |
Определите постоянную и переменную составляющие общехозяйственных расходов предприятия, используя методы регрессионного анализа.
Пояснения: используйте статистические функции ОТРЕЗОК и НАКЛОН для нахождения коэффициентов уравнения y=ax+b.
2 Имеются данные о денежных потоках двух инвестиционных проектов, предлагаемых к внедрению:
| Год | Первый проект | Второй проект | ||
| Доходы, тыс.руб. | Расходы, тыс.руб. | Доходы, тыс.руб. | Расходы, тыс.руб. | |
Выберите наиболее выгодный проект при ставке дисконтирования 5%, используя для сравнения показатель NPV (чистый дисконтированный доход).
|
|
Пояснения: используйте финансовую функцию ЧПС. Опишите формат функции, особенности ее применения.
3 Предполагается зависимость между месячными затратами на рекламу и соответствующими объемами продаж:
| Месяц | Расходы на рекламу, тыс.руб. | Объем продаж, тыс.руб. | Месяц | Расходы на рекламу, тыс.руб. | Объем продаж, тыс.руб. |
| Январь | 4,1 | 15,6 | Июль | 5,0 | 15,8 |
| Февраль | 6,2 | 16,8 | Август | 7,2 | 17,0 |
| Март | 5,8 | 15,9 | Сентябрь | 8,4 | 16,9 |
| Апрель | 7,9 | 16,6 | Октябрь | 10,6 | 18,2 |
| Май | 8,6 | 16,4 | Ноябрь | 11,0 | 17,5 |
| Июнь | 3,0 | 15,9 | Декабрь | 7,0 | 15,9 |
Вычислите степень корреляции между этими значениями. Постройте график разброса. Определите уравнение линейной регрессии вида y=ax+b и с его помощью оцените объем продаж при условии, что затраты на рекламу составят 2000 руб.
Пояснения: используйте статистическую функцию КОРРЕЛ для нахождения коэффициента корреляции, функции ОТРЕЗОК и НАКЛОН для нахождения коэффициентов уравнения y=ax+b.
4 Имеются данные о зависимости спроса и предложения от цены товара:
| Цена, руб. | Спрос, шт. | Предложение, шт. | Цена, руб. | Спрос, шт. | Предложение, шт. |
Используя методы регрессионного анализа, найдите функции спроса и предложения. Определите равновесную цену и количество графическим способом и аналитически.
Пояснения: используйте статистические функции ОТРЕЗОК и НАКЛОН для нахождения коэффициентов уравнения y=ax+b. Для аналитического нахождения равновесной точки воспользуйтесь операцией подбора параметра (меню Сервис → Подбор параметра).
5 Рассчитайте величину годовых амортизационных отчислений по годам срока эксплуатации основных средств, используя линейный метод начисления амортизации и метод двойного уменьшения остатка, по следующим данным:
| Наименование основных средств | Первоначальная стоимость, руб. | Годовая норма амортизации, % | Дата приобретения |
| Компьютер персональный | 20,0 | 30.09.99 | |
| Кабельная линия связи | 20,0 | 01.04.00 | |
| Здание сборочного корпуса | 5,0 | 01.05.96 | |
| Станок рельсобалочный | 10,0 | 09.05.00 | |
| Автопогрузчик | 18,0 | 01.04.00 | |
| Автомат кузнечно-прессовый | 10,0 | 01.05.00 |
Остаточную стоимость основных средств (ликвидационную стоимость) в конце периода амортизации принять равной нулю.
Пояснения: используйте финансовые функции АПЛ, ПУО и ДДОБ. Опишите формат используемых функций, особенности их применения. Сравните полученные результаты.
6 Имеются данные о денежных потоках двух инвестиционных проектов, предлагаемых к внедрению:
| Год | Первый проект | Второй проект | ||
| Доходы, тыс.руб. | Расходы, тыс.руб. | Доходы, тыс.руб. | Расходы, тыс.руб. | |
Выберите наиболее выгодный проект при ставке дисконтирования 7%, используя для сравнения показатель IRR (внутренняя норма рентабельности).
Пояснения: используйте финансовую функцию ВСД. Опишите формат функции, особенности ее применения.
7 Коммерческая организация рассматривает целесообразность приобретения новой технологической линии. Стоимость линии составляет 10 млн.долл.; срок эксплуатации – 5 лет; износ на оборудование начисляется по методу линейной амортизации; ликвидационная стоимость оборудования будет достаточна для покрытия расходов, связанных с демонтажем линии. Выручка от реализации продукции прогнозируется по годам в следующих объемах (тыс.долл.): 6800, 7400, 8200, 8000, 6000. Текущие расходы по годам оцениваются следующим образом: 3400 тыс.долл. в первый год эксплуатации линии с последующим ежегодным ростом на 3%. Ставка налога на прибыль составляет 30%. Цена авансируемого капитала (WACC) коммерческой организации – 19%. Целесообразен ли данный проект, исходя из оценки его показателя NPV (чистый дисконтированный доход)?
Пояснения: используйте финансовую функцию ЧПС. Опишите формат функции, особенности ее применения.
8 Банком выдан кредит в 10000 руб. на 5 лет под 15% годовых, начисляемых один раз в конце каждого года. По условиям договора кредит должен быть погашен равными долями в течение указанного срока, выплачиваемыми в конце каждого года. Разработать план погашения кредита для банка и представить результат в виде таблицы:
| Период | Плата по процентам | Основная плата | Остаток долга |
| … | |||
Пояснения: используйте финансовые функции ПРПЛТ и ОСПЛТ. Опишите формат функций, особенности их применения.
9 Рассчитайте величину амортизационных отчислений по годам срока эксплуатации основных средств, используя метод фиксированного уменьшения остатка и метод суммы чисел лет по следующим данным:
| Наименование основных средств | Первоначальная стоимость, руб. | Срок эксплуатации, лет | Дата приобретения |
| Здание заводоуправления | 01.02.96 | ||
| Котельная установка | 20.05.00 | ||
| Паровой турбоагрегат | 08.03.99 | ||
| Цементовод | 17.01.00 | ||
| Двигатель внутреннего сгорания | 05.02.01 | ||
| Отбойный молоток | 16.04.02 |
Остаточную стоимость основных средств (ликвидационную стоимость) в конце периода амортизации принять равной нулю.
Пояснения: используйте финансовые функции ФУО и АСЧ. Опишите формат используемых функций, особенности их применения. Сравните полученные результаты.
10 Для покупки квартиры необходима ссуда 900000 руб., которая может быть получена под р % годовых. Сколько времени потребуется для выплаты ссуды при р % = 5, 10, 15 и ежегодных взносах 150000, 200000, 300000 руб.? Как изменятся сроки выплат, если выплаты будут ежемесячными?
Пояснения: используйте финансовую функцию КПЕР. Опишите формат функции, особенности ее применения.
ЛАБОРАТОРНАЯ РАБОТА № 3
АГРЕГИРОВАНИЕ ДАННЫХ В MS EXCEL
Агрегирование данных состоит в формировании промежуточных итогов, создании сводных таблиц и консолидированных таблиц. Агрегирование в MS Excel выполняется для списков, в записях которых имеются поля с повторяющимися значениями.
ЗАДАНИЕ
Имеются данные о поступлении товаров на склад:
| Дата | Поставщик | Наименование товара | Ед. изм. | Цена, руб. | Количество | Сумма, руб. |
| 01.02 | ООО «Интра-Ф» | Макароны в/с | кг | |||
| 01.02 | ООО «Интра-Ф» | Чай Липтон | пач | |||
| 01.02 | ЧП Иванов С.А. | Макароны в/с | кг | |||
| 01.02 | ЗАО «Хлеб» | Сдоба тульская | шт | 4,3 | ||
| 02.02 | ООО «Интра-Ф» | Макароны в/с | кг | 9,5 | ||
| 02.02 | ЗАО «Хлеб» | Макароны в/с | кг | |||
| 02.02 | ЗАО «Хлеб» | Сдоба тульская | шт | 4,3 | ||
| 02.02 | ЧП Иванов С.А. | Сдоба тульская | шт |
Провести анализ поступления товаров от каждого поставщика в количественном и стоимостном выражение по датам.