Отчетная ведомость продаж сети магазинов

МИНИСТЕРСТВО ФИНАНСОВ РОССИЙСКОЙ ФЕДЕРАЦИИ

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

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

Академия бюджета и казначейства

Омский филиал

Лебедев В.М., Мещерякова Н.А., Распутин А.П., Федоров С.В.

ФИНАНСОВЫЙ АНАЛИЗ И ПОИСК РЕШЕНИЯ

В MS EXCEL

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

ДЛЯ ВЫПОЛНЕНИЯ КУРСОВОГО ПРОЕКТА ПО ИНФОРМАТИКЕ

Омск – 2009

УДК 681.3

ББК 32.973

Л 33

 

Рецензенты:

зав. кафедрой информатики и вычислительной техники ОмГПУ,

д-р пед. наук, проф. З.В. Семёнова;

доцент кафедры информационных технологий СибАДИ,

канд. техн. наук, доцент А.В. Кондратьев

 

 

Лебедев В.М., Мещерякова Н.А., Распутин А.П., Федоров С.В.

Л 33 Финансовый анализ и поиск решения в MS EXCEL: Методические указания. – Омск: Полиграфический центр КАН, 2008. - 76 с.

 

 

Методические указания содержат общие требования, пример выполнения и варианты заданий для выполнения курсового проекта по теме MS EXCEL по дисциплине «Информатика».

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

 

Методические указания удовлетворяют требованиям учебных программ по информатике Академии бюджета и казначейства Министерства Финансов РФ и предназначены для студентов экономических специальностей вузов.

 

 

ISBN 978-5-9931-0030-2

УДК 681.3

ББК 32.973

 

©Лебедев В.М., Мещерякова Н.А., Распутин А.П., Федоров С.В.

©Омский филиал АБиК МФ РФ, 2008

СОДЕРЖАНИЕ

 

1. ТРЕБОВАНИЯ К ЗАДАНИЮ НА MS EXCEL.........................................................................4

ПРИМЕР ВЫПОЛНЕНИЯ ЗАДАНИЯ ДЛЯ ФИРМЫ «ТОВАРЫ ДЛЯ РЕМОНТА»

С ПОМОЩЬЮ СРЕДСТВ MS EXCEL…….………………………………………....………..4

2.1. Постановка задач………………………………………………….……………………...4

2.2. Выполнение задания…………………………………………………………………….5

2.2.1. Исходные данные…………………………………………………………….....5

2.2.2. Отчётная ведомость продаж сети магазинов…………………………………6

2.2.3. Диаграмма структуры суммарной выручки сети магазинов………………...6

2.2.4. Построение уравнения регрессии……………………………………………..6

2.2.5. Расчёт плана производства…………………………………………………….9

2.2.6. Финансирование проекта расширения производства………………………11

 

ВАРИАНТЫ ЗАДАНИЙ НА EXCEL

3.1. Компания «Парфюм-сервис» (вариант 1-10) ………….……………….……………. 14

3.2. Компания «Comp-2002» (вариант 11-20) …………………………….……………… 35

3.3. Компания «Офис-мебель» (вариант 21-30) …………………………….…………… 56

 

ЛИТЕРАТУРА…………………………………………………………………………….…....……77

 

Требования к заданию на MS Excel

 

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

Для всех вариантов задание включает в себя следующие пункты:

1. Сформировать отчетную ведомость продаж сети магазинов компании, включающей расчет финансовых показателей магазинов, их рейтингов, инвариантный расчет комиссионных.

2. Построить диаграмму структуры суммарной выручки сети магазинов.

3. При помощи средства «линия тренда» на основе заданного ряда наблюдений одного из магазинов обосновать и построить уравнение регрессии (функцию) для суммы выручки, зависящей от периода времени. На основе этого уравнения найти плановую сумму выручки указанного магазина на будущий период времени и сделать вывод по полученному прогнозу.

4. Решить задачу об оптимальном производстве продукции при помощи средства «Поиск решения».

5. При заданных параметрах и величине начального взноса ссуды рассчитать величину общей суммы выплат и сумму периодичных взносов по ссуде (кредиту). При помощи средства «Подбор параметра» найти необходимый размер начального взноса при заданной величине общей суммы процента по ссуде или периодичных взносов.

 

Пример выполнения финансового анализа деятельности фирмы «Товары для ремонта» с помощью средств MS Excel

Постановка задач

Фирма «Товары для ремонта» занимается изготовлением и продажей товаров для ремонта жилья. Структурно она состоит из цеха изготовления красок и сети магазинов.

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

Рассмотрим задачи, решаемые разными подразделениями фирмы.

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

1. Составляет отчетную ведомость, которая включает следующие показатели:

□ выручку за каждый месяц периода (функция СУММПРОИЗВ), среднюю (функция СРЗНАЧ) и суммарную выручки каждого из магазинов за отчетный период;

□ суммарную выручку всех магазинов за каждый месяц отчетного периода;

□ рейтинг (функция РАНГ) каждого из магазинов в зависимости от их суммы выручки;

□ долю каждого из магазинов в суммарном объеме выручки;

□ количество магазинов (функция ЧАСТОТА), суммарная выручка каждого из которых попадает в заданный интервал;

□ величину комиссионных, премии и общий доход каждого магазина по итогам его работы за отчетный период по заданному правилу (функция ЕСЛИ).

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

3. Для каждого магазина на основе временного ряда сумм выручки выявляет и исследует тенденцию их изменения. Для одного из магазинов обосновывает и строит с помощью средства «линия тренда» уравнение регрессии (функция) для суммы выручки, зависящей от периода времени. На основе этого уравнения рассчитывает плановую сумму выручки указанного магазина на будущий период времени и делает вывод по полученному прогнозу.

II. Менеджер и экономист цеха изготовления красок, в частности, решают следующие задачи:

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

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

 

Выполнение задания

Исходные данные

В таблице 1. приведены цены товаров сети магазинов фирмы «Товары для ремонта». В таблице 2 приведены результаты деятельности сети магазинов компании за летний период.

 

Таблица 1. Цены товаров сети магазинов фирмы

Наименование товара	Единица изм.	Цена (рубл.)
Краска для наружных работ	банка (3 кг)
Краска П-115 (для внутренних работ)	банка (3 кг)
Олифа	литр
Обои	рулон
Клей обойный	пачка
Клей для плитки	мешок
Ротгипс	мешок
Шпатлевка	банка
Алебастр	мешок
Уголки	метр
Профиль	метр
Гипсокартон	лист
Ветонит	мешок

 

 

Таблица 2. Количество реализованных товаров за летний период

Товар	магазин 1	магазин 2	магазин 3
Июнь	Июль	Август	Июнь	Июль	Август	Июнь	Июль	Август
Краска для нар. работ
Краска для внутр. работ
Олифа
Обои
Клей обойный
Клей для плитки
Ротгипс
Шпатлевка
Алебастр
Уголки
Профиль
Гипсокартон

 

Таблица 2. Количество реализованных товаров за летний период (продолжение)

Товар	магазин 4	магазин 5	магазин 6
Июнь	Июль	Август	Июнь	Июль	Август	Июнь	Июль	Август
Краска для нар. работ
Краска для внутр. работ
Олифа
Обои
Клей обойный
Клей для плитки
Ротгипс
Шпатлевка
Алебастр
Уголки
Профиль
Гипсокартон
Ветонит

Отчетная ведомость продаж сети магазинов

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

Анализ этой ведомости показывает, что наибольшую выручку за летний период принесли магазины 4, 5 и 6, которые соответственно получили рейтинг 2, 1 и 3. Средняя выручка каждого из этих магазинов составила соответственно 702,67 тыс. руб., 715,33 тыс. руб. и 693,33 тыс. руб. Наибольшая общая выручка сети магазинов была получена в июле месяце и составила 3882 тыс. руб.

Количество магазинов, имеющих суммарную выручку до 1000 тыс. руб., равно 0, от 1000 тыс. руб. до 1500 тыс. руб. – 2, от 1500 тыс. руб. до 2000 тыс. руб. – 1 и свыше 2000 тыс. руб.. – 3.

Комиссионные и премия каждого магазина по итогам работы за отчетный период были найдены с использованием функции «ЕСЛИ» по следующему правилу:

§ если продукции продано не меньше чем на 2000 тыс. руб., то комиссионные составляют 2% от суммы его выручки, иначе 0,5 %;

§ за первое место дополнительно начисляется премия в размере 2 % от суммы его выручки, за второе место —1,5 % и за третье место – 1%.

Как видно из таблицы 3 указанный доход составил: для 1-го магазина – 6.465 тыс. руб., для 2-го магазина – 7.17 тыс. руб., для 3-го магазина – 8.03 тыс. руб., для 4-го магазина – 73.78 тыс. руб., для 5-го магазина – 85.84 тыс. руб., для 6-го магазина – 62.4 тыс. руб.