Обработка данных с помощью средств MS Excel

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

  Если  номер правила равен 2, то распределение  накладных расходов производится пропорционально  зарплате, выплаченной за производство данного вида продукции, а если номер  правила равен 3, то пропорционально  прямым затратам. Расчет доли накладных  расходов, относимых на конкретный вид продукции, рассчитывается аналогично правилам, используемым при распределении  пропорционально затратам материалов, но с использованием иной расчетной  базы.

  Очевидно, что сумма долей накладных  расходов, относимых на все виды продукции в соответствии с правилами  их расчета должна быть равна 100%.

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

 

  

Вариант 5

   1. Создать новую книгу и ввести  таблицу (рис. 1):

     • шапку таблицы ввести в две  строки, задав для ячеек, содержащих названия граф, расположение по центру столбца;

• переопределить ширину столбцов («Номер п/п») и («Показатели»);

• названия граф («Номер п/п») и («Итого за год») ввести в две ячейки;

• название графы («Показатели») расположить в две ячейки сверху вниз, выполнив действия;

     • значение ячейки («Год») отцентрировать по кварталам. Расчертить таблицу, как указано на рис. 1.

   3. Автоматически пронумеровать все  показатели в столбце А, задать  для чисел столбца А выравнивание  по центру.

   4. Выполнить необходимые расчеты:

     • данные по строке «Валовая прибыль» рассчитать как разность между торговыми  доходами и торговыми расходами;

     • данные по строке «Общие затраты» получить как сумму трех предыдущих строк:

     • данные по строке «Производственная  прибыль» получить как разность между  валовой прибылью и общими затратами:

     • данные по строке «Удельная валовая  прибыль» получить как результат  деления производственной прибыли  на торговые доходы;

     • данные в колонке «Итого за год» получить суммированием квартальных  данных.

   5. Задать для строки «Удельная  валовая прибыль» Процентный  формат, а для всех остальных  строк — Формат с разделителями.

   6. На строке 1 (при необходимости  вставить строку) ввести заголовок,  задав для него более крупный  жирный шрифт и расположение  по центру всей таблицы.

   7. Зафиксировать титулы таблицы  (головку (шапку) и боковик). Перемещением  по таблице отобразить на экране  данные только трех столбцов: «Номер п/п», «Показатели». «Итого  за год». Снять закрепление областей.

   8. На листе с таблицей по данным  строки «Торговые расходы фирмы»  за первые три квартала построить  разрезанную круговую диаграмму (при выделении данных для построения диаграммы использовать и названия кварталов).

   9. Добавить на диаграмму данные  по 4-му кварталу.

   10. По данным строки «Валовая  прибыль» построить гистограмму. Оформить заголовки и легенды³ диаграмм, расположить диаграммы рядом под таблицей. 

Рис. 1. Структура доходов и расходов фирмы

   11. Изменить числовое значение за 4-й квартал по торговым расходам  фирмы. Проследить зависимость  графических данных в диаграммах  от числовых в таблице.

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

   Вариант 6

   Создать новую рабочую книгу, ввести и  оформить таблицу как на рис. 1.

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

  

  Рис. 1. Прогнозирование прибыли предприятия 

    

1. На основе исходных данных, приведенных в таблице  построить диаграмму со значениями, соединенными сглаживающими линиями.
2. Оформить диаграмму: ввести нужные заголовки и форматы, разместить на отдельном рабочем листе.
3. Добавить линию тренда⁴ (щелкнуть правой кнопкой мыши по линии графика, для вывода контекстного меню).
4. В диалоговом окне Линия тренда, вкладка Тип выбрать линейную аппроксимацию⁵.
5. Во вкладке Параметры установить флажок в поле показывать уравнение на диаграмме.
6. В данном примере уравнение линии тренда имеет вид У = 67,833Х - 133937, где X - год.
7. Ввести значения коэффициентов в таблицу, рассчитать теоретические значения прибыли по формуле.
8. В колонку D ввести формулу для расчета абсолютной разности между значениями статистическими и полученными в результате аппроксимации. Для этого воспользоваться математической функцией =АВS (В-С).
9. В ячейку D4 ввести статистическую функцию для расчета максимальной погрешности =МАКС(D3:D13). В данном случае эта величина = 52,002.
10. На основе исходных данных построить еще одну диаграмму, выбрав логарифмическую функцию. В данном случае функция будет иметь вид у = 135325Ln(х) — 1Е+06.
11. Аналогичным образом построить теоретический ряд логарифмической аппроксимации и определить максимальную погрешность.
12. Сравнить погрешность различных типов аппроксимации. Чем меньше погрешность, тем точнее прогноз.
13. Выбрать более точный тип аппроксимации, рассчитать прогноз на 2000 и 2001 гг.
14. Рассчитать прогноз прибыли на 2000 и 2001 гг. на основе функций прогнозирования ПРЕДСКАЗ, РОСТ и ТЕНДЕНЦИЯ.
15. Сделать сравнительный анализ используемых методов прогнозирования. Какой из них дает более точный прогноз?
16. Результаты оформить в виде отчета в документе MS Word, скопировав в него со связью все необходимые данные и диаграммы.

 

Вариант 7

     Три фирмы-поставщика (ФИРМА 1,ФИРМА 2, ФИРМА 3) предлагают фармпредприятию компоненты (компонента1, компонента2, компонента3, компонента4 компонента5), необходимые для изготовления трех препаратов (препарат1, препарат2, препарат3). Компоненты отличаются ценой и сроком хранения. Необходимо:

1. Выполнить закупку компонентов по максимальному сроку хранения.
2. Рассчитать стоимость расходных материалов (т.е. стоимость требуемых компонент) для изготовления требуемого количества препаратов по каждому наименованию и общую стоимость требуемых компонент по всем наименованиям с учетом сделанных ранее закупок компонентов.
3. Исходные данные:
1. Процентные весовые отношения компонент для изготовления препаратов и вес каждого препарата в условных весовых единицах (таблица 1). Имя этой таблицы — КОМПОНЕНТЫ.
2. Наименования компонентов, цена условной весовой единицы компоненты, срок хранения, предлагаемые фирмой - поставщиком (таблицы 2,3,4). Имена этих таблиц — ФИРМА 1,ФИРМА 2, ФИРМА 3.

Требуется создать электронные  таблицы заданных форматов, позволяющих  определять:

1. Наименование фирмы – поставщика и цены для каждой компоненты при максимальном сроке хранения. Имя этой таблицы – ЗАКУПКИ.
2. Стоимость расходных материалов (т. е. стоимость требуемых компонент) для изготовления требуемого количества препаратов (в штуках) по каждому наименованию и общую стоимость требуемых компонент по всем наименованиям с учетом сделанных ранее закупок компонентов. Создается электронная таблица заданного формата (таблицы 5). Имя этой таблицы — РАСЧЕТ.
3. Таблица 1

 

Таблица 2

 

Таблица 3

 

 

Таблица 4