Обработка данных с помощью средств MS Excel (стр. 9 из 12)
9. Добавить на диаграмму данные по 4-му кварталу.
10. По данным строки «Валовая прибыль» построить гистограмму. Оформить заголовки и легенды[3] диаграмм, расположить диаграммы рядом под таблицей.
| Номер п/п | Показатели | Год | Итого за год | |||
| 1 кв. | 2 кв. | 3 кв. | 4 кв. | |||
| Продано единиц | 3592 | 4390 | 3192 | 4789 | ||
| Торговые доходы | 143662 | 175587 | 127700 | 191549 | ||
| Торговые расходы | 89789 | 109742 | 79812 | 119712 | ||
| Валовая прибыль | ||||||
| Расходы на зарплату | 8000 | 8000 | 9000 | 9000 | ||
| Расходы на рекламу | 10000 | 10000 | 10000 | 10000 | ||
| Накладные расходы фирмы | 21549 | 26338 | 19155 | 28732 | ||
| Общие затраты | ||||||
| Производственная прибыль | ||||||
| Удельная валовая прибыль | ||||||
Рис. 1. Структура доходов и расходов фирмы
11. Изменить числовое значение за 4-й квартал по торговым расходам фирмы. Проследить зависимость графических данных в диаграммах от числовых в таблице.
12. На отдельном листе построить нормированную гистограмму со столбцами в виде цилиндров по данным всей таблицы (исключая графу «Итого за год»).
Вариант 6
Создать новую рабочую книгу, ввести и оформить таблицу как на рис. 1.
На основании приведенных в таблице статистических данных за ряд лет сделать прогноз на 2001 год, используя различные методы прогнозирования. Провести анализ с целью определения, какой из примененных методов дает более точный результат.
 |
Рис. 1. Прогнозирование прибыли предприятия
1. На основе исходных данных, приведенных в таблице построить диаграмму со значениями, соединенными сглаживающими линиями.
2. Оформить диаграмму: ввести нужные заголовки и форматы, разместить на отдельном рабочем листе.
3. Добавить линию тренда[4] (щелкнуть правой кнопкой мыши по линии графика, для вывода контекстного меню).
4. В диалоговом окне Линия тренда, вкладка Тип выбрать линейную аппроксимацию[5].
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. Рассчитать стоимость расходных материалов (т.е. стоимость требуемых компонент) для изготовления требуемого количества препаратов по каждому наименованию и общую стоимость требуемых компонент по всем наименованиям с учетом сделанных ранее закупок компонентов.
Исходные данные:
1. Процентные весовые отношения компонент для изготовления препаратов и вес каждого препарата в условных весовых единицах (таблица 1). Имя этой таблицы — КОМПОНЕНТЫ.
2. Наименования компонентов, цена условной весовой единицы компоненты, срок хранения, предлагаемые фирмой - поставщиком (таблицы 2,3,4). Имена этих таблиц — ФИРМА 1,ФИРМА 2, ФИРМА 3.
Требуется создать электронные таблицы заданных форматов, позволяющих определять:
1. Наименование фирмы – поставщика и цены для каждой компоненты при максимальном сроке хранения. Имя этой таблицы – ЗАКУПКИ.
2. Стоимость расходных материалов (т. е. стоимость требуемых компонент) для изготовления требуемого количества препаратов (в штуках) по каждому наименованию и общую стоимость требуемых компонент по всем наименованиям с учетом сделанных ранее закупок компонентов. Создается электронная таблица заданного формата (таблицы 5). Имя этой таблицы — РАСЧЕТ.
Таблица 1
| КОМПОНЕНТЫ | ||||||
| Наимено- вание | вес | компонента1 | компонента2 | компонента3 | компонента4 | компонента5 |
| Препарат1 | 3 | 15% | 0 | 35% | 50% | 0 |
| Препарат2 | 5 | 0 | 27% | 28 | 0 | 45% |
| Препарат3 | 12 | 5% | 0 | 30 | 47% | 18% |
Таблица 2
| ФИРМА 1 | ||
| Наименование | Цена | Срок хранения |
| компонента1 | 25 | 7 |
| компонента2 | 17 | 5 |
| компонента3 | 31 | 4 |
| компонента4 | 18 | 8 |
| компонента5 | 22 | 3 |
Таблица 3
| ФИРМА 2 | ||
| Наименование | Цена | Срок хранения |
| компонента1 | 15 | 5 |
| компонента2 | 22 | 3 |
| компонента3 | 12 | 6 |
| компонента4 | 24 | 2 |
| компонента5 | 24 | 5 |
Таблица 4
| ФИРМА 3 | ||
| Наименование | Цена | Срок хранения |
| компонента1 | 32 | 3 |
| компонента2 | 22 | 5 |
| компонента3 | 11 | 2 |
| компонента4 | 19 | 6 |
| компонента5 | 20 | 4 |
Таблица 5
| ЗАКУПКИ | ||
| Наименование | Цена | Фирма |
| компонента1 | ||
| компонента2 | ||
| компонента3 | ||
| компонента4 | ||
| компонента5 | ||
Таблица 6
| РАСЧЕТ | ||
| Наименование | Количество (шт). | Стоимость расх. материалов |
| Препарат1 | 4357 | |
| Препарат2 | 2256 | |
| Препарат3 | 2987 | |
| Общая стоимость расх. материалов | ||
Вариант 8
1. Создайте таблицу как на рис. 1.
| Дата | Расход | Сумма | Получатель |
| 01.06.99 | Накладные расходы | $600 | ЗАО ВИН |
| 02.06.99 | Накладные расходы | 5321 | ТОО Надежда |
| 04.06.99 | Материалы | $16000 | АО Престиж |
| 05.06.99 | Зарплата | $2 000 | Васильева М.Ф. |
| 05.06.99 | Зарплата | §2 540 | Казаков С. С. |
| 05.06.99 | Зарплата | 51 890 | Иванов И. И. |
| 30.06.99 | Накладные расходы | $1 000 | АО ИНВЕСТ |
| 04.07.99 | Накладные расходы | 8600 | ЗАО БИН |
| 04.07.99 | Накладные расходы | 5440 | ТОО Надежда |
| 04.07.99 | Материалы | $13 200 | АО Оргсинтез |
| 05.07.99 | Зарплата | $2 000 | Васильева М.Ф. |
| 05.07.99 | Зарплата | $2 540 | Казаков С. С. |
| 05.07.99 | Зарплата | 51 890 | Иванов И. И. |
| 31.07.99 | Накладные расходы | $1 000 | АО ИНВЕСТ |
| 04.08.99 | Накладные расходы | $600 | ЗАО БИН |
| 05.08.99 | Зарплата | $2000 | Васильева М.Ф. |
| 05.08.99 | Зарплата | $2540 | Казаков С. С. |
| 05.08.99 | Зарплата | $1 890 | Иванов И. И. |
| 04.09.99 | Накладные расходы | $311 | ОО Надежда |
| 05.09.99 | Зарплата | $2000 | Васильева М.Ф. |
| 05.09.99 | Зарплата | $2540 | Каза ков С. С. |
| 05.09.99 | Зарплата | $1 890 | Иванов И. И. |
Рис. 1. Список расходов
2. Используя форму данных, добавьте в список данные об АО Престиж: «30.06.97. Материалы. $800, АО Престиж».
3. Используя форму данных, просмотрите информацию о Казакове и измените сумму зарплаты за 05.09.99 на $2800.
4. Используя форму данных, просмотрите все данные списка о расходах на материалы, превышающих $12000.