Расчета доходов от оказания охранных услуг

1. Постановка задачи

 

1. Условие задачи

 

Охранное предприятие ООО «Радон» осуществляет охрану объектов в соответствии с заключенными договорами и представляет клиентам в аренду дополнительное оборудование. Стоимость охраны 1 кв.м. помещений без учета дополнительного оборудования составляет 105 руб.  
Данные для выполнения расчетов представлены в табл. 1, 2.  
1. Постройте табл. 1, 2 в MS Excel.  
2. Организуйте межтабличные связи для автоматического формирования общей стоимости охраны объектов, включая аренду дополнительного оборудования, используя функции ВПР или ПРОСМОТР.  
3. Сформируйте таблицу и заполните ее данными по расчету доходов от оказания охранных услуг за текущий месяц по каждому объекту и по всем организациям в целом(табл. 3).  
4. Результаты расчетов доходов от оказания охранных услуг представьте в графическом виде, проведите их анализ.

                                                                                 Таблица 1

                            Характеристика охраняемых помещений

Наименование организации	Общая площадь охраняемых помещений, кв.м.	Дополнительное оборудование	Количество оборудования,шт.
ООО «Скорпион»	102,55	Оптико-электронные оповещатели	7
ООО  «Водолей»	96,27	Оптико-волоконные оповещатели	6
ООО «Андромеда»	84,69	Вибрационные извещатели	5
ООО  «Стрелец»	77,13	Световые оповещатели	4
ООО  «Козерог»	101,34	Оптико-волоконные оповещатели	7
ООО «Альтаир»	68,46	Оптико-электронные оповещатели	4

                                                                                             Таблица 2

Данные о стоимости аренды дополнительного охранного оборудования

Наименование оборудования	Стоимость аренды 1 шт. в месяц руб.
Оптико-электронные оповещатели	256
Оптико-волоконные оповещатели	278
Вибрационные извещатели	212
Световые оповещатели	195

 

 

                                                                                             Таблица 3

                            Расчет доходов от оказания охранных услуг

 

№ п/п	Наименование организации	Стоимость охраны помещений,руб.	Стоимость аренды дополнительного оборудования,руб.	Коэффициент риска	Общая стоимость охраны,руб.
	ООО «Скорпион»			1,2
	ООО «Водолей»			1,5
	ООО «Андромеда»			1,0
	ООО «Стрелец»			1,8
	ООО «Козерог»			1,6
	ООО «Альтаир»			1,0
Итого стоимость  охраны,руб

 

 

2. Цель решения задачи

 

  Охранное  предприятие  ООО «Родон» осуществляет охрану объектов в соответствии с заключенными договорами и предоставляет клиентам в аренду дополнительное оборудования. Предприятие заинтересованно в максимилизации своей прибыли, для этого необходимо выявить наиболее и наименее выгодную организацию в сфере предоставления охранных услуг. Для этого было принято отслеживать фактическое оказание охранных услуг с помощью нескольких ведомостей, содержащих сведенья о площади охраняемых объектов, о стоимости охраны помещения, о стоимости аренды дополнительного оборудования, коэффициент риска. Данные будут отражаться в учете только после полного оказания услуг. Задача, которая будет решаться в программной среде MS Excel ежемесячно, называется «Расчет доходов от оказания охранных услуг».

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

 

 

 

 

 

 

 

 

 

 

 

 

2. Математическая модель решения  задачи.

 

  Для получения ведомости «Расчет доходов от оказания охранных услуг» необходимо рассчитать такие показатели, как:

1. Стоимость охраны помещений за месяц по каждому объекту;
2. Стоимость аренды дополнительного оборудования по каждому объекту;
3. Общую стоимость охраны помещения по каждому объекту;
4. Общую стоимость охраны помещений по всем объектам вместе.

Расчеты выполняются по следующим  формулам:

Ос= ∑Со+∑Са

Где Ос- общая стоимость охраны;

  Со-стоимость охраны помещений;

  Са- стоимость аренды дополнительного оборудования.

Показатели, определяющие содержание резу

 

 

 

 

 

 

 

 

 

 

 

 

 

3. Технология решения задачи в среде MS Excel

 

1. Вызовите MS Excel:

 -нажмите кнопку Пуск;

- выберите в главном  меню команду Программы;

- в меню Microsoft Office выберите MS Excel.

 2.  Переименуйте  Лист 1 в Характеристику охраняемых помещений:

-установите курсор на ярлык Лист 1 (нижняя часть экрана) и нажмите правку кнопку мыши;

-наберите на клавиатуре  Характеристика охраняемых помещений;

-нажмите Enter.

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

-сделайте ячейку А3 активной ( установите курсор на пересечении столбца А и 3 и нажмите левую кнопку мыши);

- наберите на клавиатуре  Характеристика охраняемых помещений;

- нажмите галочку в строке формул.

4. Отформатируйте заголовок:

-выделите ячейки А3-D3 ( сделайте активной ячейку А3, затем нажмите левую кнопку мыши и, удерживая ее, переместите курсор на ячейку D3)

- на панели инструментов  в закладке Главная выберите раздел Выравнивание, нажмите кнопку «Объединить и поместить в центре».

5. Отформатируйте ячейки А5-D5 под ввод длиных заголовков:

- выделите ячейки А5-D5

- выполните команду Выравнивание  в разделе Формат ячеек меню  Главная на панели инструментов;

-выберите закладку Выравнивание;

- в группе опций Отображение  установите флажок в опции  переносить по словам (рис.1)

 

              Рис.1. Перенос слов при вводе  в ячейку длинных предложений.

6. Введите в ячейки    А5-D5  информацию представленную на рисунке 2. Причем вводить будем начиная с организаций с наименьшей площадью и заканчивая наибольшей(рисунок 3).

Наименование организации

Общая площадь охраняемых помещений, кв.м.

Дополнительное оборудование

Количество оборудования,шт.

     Рис.2. Имена  полей таблицы « Характеристика  охраняемых  помещений».

      

                Рис.3. Таблица «Характеристика охраняемых помещений» на                   рабочем     листе  MS Excel.

 

8. Переименуйте Лист 2 в Оборудование ( аналогично действию в пункте 2).

9. Создайте таблицу «Данные  о стоимости аренды дополнительного  охранного оборудования». ( аналогично действиям в пунктах 3-5) (рис.4).

                    

      Рис.4. Вид  таблицы «Данные о стоимости  аренды дополнительного охранного  оборудования»

 

10. Переименуйте Лист 3 в Расчет доходов от оказания платных охранных услуг. ( аналогично действиям пункта 2.).

11 . Создать таблицу  « Расчет доходов от оказания охранных услуг» и внести исходные данные из таблице 3.

11. Заполните графу стоимость охраны помещения:

- сделайте ячейку С2 активной;

-воспользуйтесь командой Вставить функции меню Функцию;

- выбираем в категории  строчку ссылки и массивы, а  в выборе функций находим ПРОСМОТР;

- в аргументах выбираем строку искомое значение; просматриваемый вектор; вектор результатов (рисунок 5).                

                            

     Рис.5  Аргументы  функции.

- в искомых значениях выбираем ячейку B2 с названием первой организацией ООО « Скорпион»;

- в просматриваемом векторе выбираем  из листа характеристика охраняемых  помещений диапазон с А6 по А11;

-в векторе результатов  выбираем из листа характеристика охраняемых помещений диапазон с В6 по В11;

-так как стоимость  охраны помещений рассчитывается  как произведение общей площади  охраняемого помещения к  стоимости одного квадратного метра, то полученную формулу  =ПРОСМОТР(B2;'Хар-ка охраняемых помещений'!A6:A11;'Хар-ка охраняемых помещений'!B6:B11)  умножаем на ячейку В11.

-так как В11 величина  постоянная ,то ограничиваем ее  знаками $В$11

- получается =ПРОСМОТР(B2;'Хар-ка охраняемых помещений'!A6:A11;'Хар-ка охраняемых помещений'!B6:B11)*$B$11

- для автоматического расчета строки, просто тянем строку маркера от ячейки С2 до С 7 (рисунок 6).

                          

                            Рис.6 Автоматический расчет ячеек.

12. Заполните графу стоимость  аренды дополнительного оборудования:

-Заполняется аналогичным  образом как и графа стоимости охраны помещения

- в искомых значениях выбираем ячейку B2 с названием первой организацией ООО « Скорпион»;

- в просматриваемом векторе  выбираем  из листа характеристика  охраняемых  помещений диапазон  с А6 по А11;

-в векторе результатов  выбираем из листа характеристика охраняемых помещений диапазон с D6 по D11;

-так как стоимость аренды дополнительного оборудования рассчитывается  как произведение количества оборудования в штуках на стоимость аренды 1 штуки в месяц - получается =ПРОСМОТР(B2;'Хар-ка охраняемых помещений'!A6:A11;'Хар-ка охраняемых помещений'!D6:D11)*оборудование!B2 и т.д со всеми остальными ячейками.

13. Заполните колонку общей  стоимости охраны:

- делаем активной ячейку F2;

-выбираем функцию СУММ, в ячейку число1 вставляем С2, а в число2 вставляем D2;

-для автоматического подсчета делаем ячейку F2 активной и маркером доводим до ячейки F7.

14. для подсчета итоговой  стоимости охраны выделяем ячейки  сF2 по F8 активной и нажимаем значок суммы (рисунок 7).

       

                                  Рис. 7 Полученная таблица.

15. Представьте наглядно  результаты расчетов, создав диаграмму  по таблице расчетов доходов  от оказания охранных услуг;

- переименуйте лист 4 в диаграмму;

-выберите  команду Гистограмма в меню вставка

- в разделе работа с  Гистограммой, выбираем макет и  стиль диаграммы;

- в разделе выбрать данные вводим имеющиеся данные.

-переименуйте гистограмму  в Расчет доходов от оказания  охранных услуг (рисунок 8).

 

Рис.8 Гистограмма «Расчет доходов  от оказания охранных услуг».

  

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Заключение

 

Таким образом, формирование таблиц на основе «Расчета доходов от оказания охранных услуг» позволяет решить поставленные задачи:

1. Выявить  получаемый доход от оказания охранных услуг;
2. Выявить получаемый доход от  сдачи в аренду дополнительного оборудования;
3. Определить наиболее выгодную организация для оказания охранных услуг.

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