Финансовый анализ в MS Excel - Контрольная работа

Задания:

1. Сформировать отчетную ведомость продаж сети магазинов. Для этого необходимо рассчитать:

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

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

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

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

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

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

а также использовать следующие данные (сумма указывается в тыс. руб.):

 Комиссионное вознаграждение i=4% при сумме выручки > 1100, иначе i=2 %

 Премия за 1 место - 3%, 2 место - 1,5% , 3 место - 1%

 Диапазоны выручки 950 , 1300, 2500

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

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

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

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

Анализ этой ведомости показывает, что наибольшую выручку за рассматриваемый период принесли магазины 4, 2 и 1, которые соответственно получили рейтинг 1, 2 и 3. Средняя выручка каждого из магазинов составила соответственно: Магазин 4 – 2 548 424,00; Магазин 2 – 1 238 486,00 руб.; Магазин 1 – 1 194 662,00 руб. и Магазин 3 – 1 025 872,00.

Наибольшая общая выручка сети магазинов была получена в августе и составила 2 259 088,00 руб.

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

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

– если продукции продано на сумму более 1 100 000 руб., то комиссионные составляют 4 % от суммы выручки, иначе 2 %;

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

Как видно из таблицы 2 доход (общая сумма вознаграждения) составил: для 1-го магазина – 59 733,10 руб., для 2-го магазина – 68 116,73 руб., для 3-го магазина – 20 517,44 руб., для 4-го магазина – 178 389,68 руб.