Прогнозирование в MS Excel

Прогнозирование как предвидение результатов развития хозяйственной структуры и перспективное планирование. Использование электронных таблиц MS EXCEL для решения экономических задач. Учет затрат на вспомогательные материалы. Этапы построения диаграмм.

Рубрика Экономико-математическое моделирование
Вид курсовая работа
Язык русский
Дата добавления 16.04.2014
Размер файла 720,1 K

Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже

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

Размещено на http://www.allbest.ru

Введение

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

Систематизированное научно обоснованное прогнозирование развития социально-экономических процессов на основе специализированных осуществляется с первой половины 50-х годов, хотя некоторые методики прогнозирования были известны и ранее. К ним относятся: логический анализ и аналогия, экстраполяция тенденций, опрос мнения специалистов и ученых.

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

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

Прогнозирование

Иногда нам хочется знать, "что будет" заранее. Это облегчает принятие предстоящих решений в свою пользу. Как принято говорить, "подстелить соломку".

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

ПРОГНОЗ = f(x),

где х - некий момент времени, то ПРОГНОЗ будет не что иное, как значение f(x) в некоторый наперед заданный момент времени х.

Частотный анализ

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

Этот вопрос не является праздным. Ответив на него. можно выработать правильную линию поведения в будущем. Например, спланировать объем выпуска продукции фабрики верхней одежды на основе анализа распределения населения некоторого региона по росту.

Заполните данными рабочий лист электронной таблицы, как показано ниже.

Используя функцию ЧАСТОТА(данные; интервалы), где данные - это множество значений блока A3:D10, а интервалы - блока E3:E9, определим число людей в группах.

Поскольку этих групп на одну больше числа интервалов, то:

выделите блок F3:F10;

наберите формулу

=ЧАСТОТА(A3:D10;E3:E9);

введите ее, нажав комбинацию клавиш Ctrl+Shift+Enter.

Результат анализа будет следующим:

Он показывает, например, что в данном регионе все люди выше 140 см. Людей ростом от 140 см до 150 см - четверо и т.д.

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

Использование электронных таблиц MS EXCEL для решения экономических задач

Задание №1. Создание таблиц, расчет по формулам, построение диаграмм

Вариант А. Учет затрат на вспомогательные материалы

Наименование материала

Цена (Ц)

Количество

Сумма на конец месяца (С)

Доля в общем объеме (Д)

На начало месяца (НМ)

На конец месяца (КМ)

Израсходовано (И)

Краска

12,50

90

50

Лак

28,30

60

10

Бензин

2,00

140

60

Керосин

1,80

90

30

Эмаль

16,30

75

80

Ацетон

11,80

60

15

Итого

Х

Х

Х

Х

Средняя сумма расхода

Х

Минимальная доля в общем объеме

И=КМ-НМ С=Ц*КМ Д=С/(Итого С) * 100

Расчеты в таблице производились по следующим формулам Еxcel со ссылками на ячейки:

Израсходовано (И): Е6 =D6-C6;

Сумма на конец месяца (С): F6 =B6*D6;

Доля в общем объеме (Д): G6 =(B6/F6)*100;

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

выберем пункт меню Вставка - Функция, откроется окно «Мастер функций» (рис.1).

Рисунок 1. - Мастер функций

из списка функций выберем СРЗНАЧ и нажмем кнопку «Ок». В поле «Число 1» укажем диапазон ячеек, среди значений которых нужно найти среднее. Формула будет иметь вид: G13=СРЗНАЧ(F6:F11)

Аналогично рассчитаем минимальную долю в общем объеме, воспользуясь функцией МИН. Формула будет иметь вид: G14= =МИН(G6:G11)

Таблица с результатами расчетов:

Наименование материала

Цена (Ц)

Количество

Сумма на конец месяца (С)

Доля в общем объеме (Д)

На начало месяца (НМ)

На конец месяца (КМ)

Израсходовано (И)

Краска

12,5

90

50

-40

625

2,00

Лак

28,3

60

10

-50

283

10,00

Бензин

2

140

60

-80

120

1,67

Керосин

1,8

90

30

-60

54

3,33

Эмаль

16,3

75

80

5

1304

1,25

Ацетон

11,8

60

15

-45

177

6,67

Итого

Х

Х

Х

Х

2563

Средняя сумма расхода

427,17

Минимальная доля в общем объеме

1,25

Этапы построения диаграмм

Для построения диаграммы выполним следующие действия:

укажем диапазон ячеек (F5:G10), по которым нужно построить диаграмму, выберем пункт Вставка - Диаграмма Откроется окно «Мастер диаграмм» (рис.2);

Рисунок 2. - Мастер диаграмм

выберем тип Гистограмма и нажмем кнопку Далее. В закладке Ряд в поле Ряд напишем названия рядов 1 и 2 и нажмем кнопку Готово;

нажмем правую кнопку мыши, удерживая курсор мыши наведенным на простроенную диаграмму;

из пунктов меню выберем Параметры диаграммы, откроется окно (рис.3);

выберем закладку Заголовки и укажем название, наименование строк и столбцов диаграммы.

Рисунок 3. - Параметры диаграммы

Построенная гистограмма будет иметь вид:

Рисунок 4. - Гистограмма

Для построения круговой гистограммы выберем пункт меню Вставка - Диаграмма и тип Круговая, после чего нажмем кнопку Готово. Аналогичным образом укажем название круговой диаграммы (рис.5).

Рисунок 5. - Круговая диаграмма

Задание №2. Подведение динамических итогов с использованием сводных таблиц

Вариант Б.

Поставляемое изделие

Тип транспорта

Расстояние

Стоимость перевозки

Нефть

Морской

1000-5000

14000

Зерно

ж/д

до 1000

7000

Агрегат

воздух

свыше 5000

3100

Нефть

ж/д

до 1000

10000

Зерно

морской

1000-5000

5400

агрегат

воздух

свыше 5000

15600

2. Этапы построения сводной таблицы:

выберем пункт Данные - Сводная таблица. Появится окно Мастер сводных таблиц;

укажем диапазон, содержащий исходные данные из таблицы (B4:D9);

укажем «Поместить таблицу в новый лист» и нажмем кнопку Макет.

Макет сводной таблицы в первоначальном виде (рис.6):

Рисунок 6. - Макет сводной таблицы

Макет измененной сводной таблицы (рис.7):

Рисунок 7. - Макет измененной сводной таблицы

Структуру сводной таблицы изменим путем перетаскивания мышкой полей таблицы, которые располагаются справа, в нужные области диаграммы. Потом нажмем кнопку «Ок».

Измененная сводная таблица

Сумма по полю Стоимость перевозки

Поставляемое изделие

Расстояние

Агрегат

Агрегат

Всего

Зерно

Зерно Всего

Нефть

Нефть Всего

Общий итог

Тип транспорта

свыше 5000

1000-5000

до 1000

1000-5000

до 1000

воздух

18700

18700

18700

ж/д

7000

7000

10000

10000

17000

Морской

5400

5400

14000

14000

19400

Общий итог

18700

18700

5400

7000

12400

14000

10000

24000

55100

Задание № 3. Использование процедуры «Поиск решения»

Вариант В.

Наименование

Расходы, гр/шт. (Р)

Кол-во, шт. (К)

Всего расходов, гр. (ВР)

Процент прибыли (ПП)

Прибыль (П)

Товар 1

780

6200

9%

Товар 2

3200

500

22%

Товар 3

160

3800

15%

Товар 4

1100

9100

13%

Товар 5

4500

800

33%

Товар 6

200

5600

23%

Итого

Х

Х

Предельные значения

36000

2700000

Х

Х

ВР=В*К П=ПП*ВР

Для расчета в таблице значений «Всего расходов» использовалась формула: =B4*C4 и далее аналогично по остальным видам товаров. Для расчета в таблице значений «Прибыль» использовалась формула: =E4*D4 и далее аналогично по остальным видам товаров. Для расчета суммарных значений количества товаров, расходов и прибыли использовалась функция СУММ(): Общее количество товаров: =СУММ(C4:C9).

Для определения оптимального плана производства выберем пункт Сервис - Поиск решения и в открывшемся диалоговом окне укажем необходимые ссылки (рис.8).

прогнозирование excel таблица учет

Рисунок 8. - Поиск решения

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

Наименование

Расходы, гр/шт. (Р)

Кол-во, шт. (К)

Всего расходов, гр. (ВР)

Процент прибыли (ПП)

Прибыль (П)

Товар 1

780

0

0,00

9%

0,00

Товар 2

3200

0

0,00

22%

0,00

Товар 3

160

0

0,00

15%

0,00

Товар 4

1100

0

0,00

13%

0,00

Товар 5

4500

600

2700000,00

33%

891000,00

Товар 6

200

0

0,00

23%

0,00

Итого

Х

600

2700000

Х

891000

Предельные значения

36000

2700000

Х

Х

Задание № 4. Регрессионный анализ данных

Вариант Г. Вид функции: z1=f(x1)

Исходная таблица

X1

70

72

75

68

68

71

69

71

69

68

68

69

75

83

73

71

82

69

73

73

72

Z1

471

492

506

464

457

478

475

490

480

457

470

468

515

578

508

493

556

463

497

502

498

Уравнение эмпирической зависимости вида y = ax + b для функции z1 = f(x1) решим методом наименьших квадратов.

Формулы для оценок параметров имеют следующий вид:

; де ; ;

,

Заполним таблицу

i

X1

Z1

2

1

70

471

221841

32970

2

72

492

242064

35424

3

75

506

256036

37950

4

68

464

215296

31552

5

68

457

208849

31076

6

71

478

228484

33938

7

69

475

225625

32775

8

71

490

240100

34790

9

69

480

230400

33120

10

68

457

208849

31076

11

68

470

220900

31960

12

69

468

219024

32292

13

75

515

265225

38625

14

83

578

334084

47974

15

73

508

258064

37084

16

71

493

243049

35003

17

82

556

309136

45592

18

69

463

214369

31947

19

73

497

247009

36281

20

73

502

252004

36646

21

72

498

248004

35856

1509

10318

5088412

743931

;

;

Таким образом, искомая эмпирическая формула имеет вид z = 7,99x + 0,13.

Построим диаграмму для функции z = 7,99x + 0,13:

выделим диапазон значений функции (G4:H24) выберем пункт меню Вставка - Диаграмма;

выберем тип Точечная и нажмем кнопку Готово

Добавим линию тренда:

выберем курсором мыши линию точек функции и нажмем правую кнопку и выберем пункт Добавить линию тренда (рис. 9);

Рисунок 9. - Добавление линии тренда

выберем закладку Параметры и установим флажок на поле «Показать уравнение на диаграмме».

Диаграмма имеет вид:

Рисунок 10. - Добавление линии тренда

Заключение

Для обеспечения полного и своевременного прогнозирования потенциального спроса, издержек и цен необходимо использовать программы для автоматизации бухгалтерского учета, например 1С: Бухгалтерию, а также различные программы для анализа и прогнозирования, среди таких программы выделяют, например Пакет анализа MS Excel который обеспечивает не только простой ввод данных для анализа и прогнозирования, но и самым процесс анализа и прогнозирования, таким образом, обеспечивая формирование прогнозируемых показателей в наиболее приемлемой форме, обеспечивающей быстроту и правильность расчетов.

При этом автоматизация решает следующие основные задачи:

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

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

осуществляет контроль над издержками путём их учёта по видам и центрам затрат;

накапливает статистику о доходах и расходах предприятия в определённом разрезе и выявляет общие тенденции;

осуществляет планирование и контролирует выполнение бюджета как отдельными центрами затрат, так и бизнесом в целом, включая совокупность юридических лиц;

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

Так, например, наиболее быстро и легко позволяет ввести и систематизировать данные для осуществления прогнозирования прибыли и других показателей деятельности предприятия электронная таблица, в нашем случае Excel. Для создания электронной таблицы достаточно выполнить команду Пуск - Программы - MS Excel, после чего можно вводить данные. Построение функций(графиков) осуществляется благодаря команде Вставка - Функция (Диаграмма). На основании введённых данных можно осуществлять анализ, определять динамику, строить графики, также можно осуществлять их группировку и сортировку с помощью команды Данные - Фильтр - Автофильтр.

Список используемой литературы

Ашманов С А. Линейное программирование. М.: Наука, 2001.

Бешелев С.Д., Гурвич Ф.Г. Математико-статистические методы экспертных оценок. -М.: Статистика, 2000. - 263 с.

Бешелев С.Д., Гурвич Ф.Г. Экспертные оценки в принятии плановых решений. - М.: Экономика, 2006. - 287 с.

Герасенко В.П. Прогностические методы управления рыночной экономикой . Ч.1. Гомель.,2007. - 320с.

Добров Г.М., Ершов Ю.В., Левин Е.И., Смирнов Л.П. Экспертные оценки в научно-техническом прогнозировании. - Киев: Наукова думка, 2004. - 263 с.

Евланов Л.Г., Кутузов В.А. Экспертные оценки в управлении. - М.: Экономика, 2008. -133 с.

Справка MS Excel

Microsoft Office Excel 2003. Учебный курс / В. Кузьмин, - СПб.: Питер: Издательская группа BHV, 2004. - 493 с.

Excel 2003. Эффективный самоучитель / В.В. Серогородский, - СПб.: Наука и техника, 2005. - 400 с.

Excel: Сборник примеров и задач / С.М. Лавренов, - М.: Финансы и статистика,2003. - 336 с.

Размещено на Allbest.ru


Подобные документы

  • Использование электронных таблиц MS EXCEL для расчета затрат на вспомогательные материалы, прибыли, построение диаграмм. Подведение динамических итогов с применением сводных таблиц. Регрессионный анализ данных. Проведение финансового анализа в Excel.

    контрольная работа [607,9 K], добавлен 29.03.2010

  • Методика и этапы построения экономических моделей с помощью программы Microsoft Excel. Определение оптимальной структуры производства консервного завода на основании имеющихся статистических данных. Нахождение условного экстремума функции в Excel.

    контрольная работа [1,4 M], добавлен 01.06.2009

  • Общее понятие о прогнозировании, методы. Абсолютные, сравнительные и качественные показатели оценки качества прогноза. Метод наименьших квадратов. Модели линейного роста. Новшества программы Excel 5.0. Пример решения задачи по прогнозу объема кредита.

    курсовая работа [1,1 M], добавлен 07.08.2013

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

    дипломная работа [300,4 K], добавлен 26.03.2009

  • Обзор основных инструментов, применяемых в прогнозировании. Характеристика базовых методов построения прогнозов социально-экономических систем при помощи программного обеспечения MS EXCEL. Особенности разработки прогнозных моделей на 2004, 2006 и 2009 гг.

    лабораторная работа [218,4 K], добавлен 04.12.2012

  • Программный пакет Microsoft Office и табличный процессор Excel. Задачи и основные функции в Microsoft Excel. Формулы в Microsoft Excel. Общие сведения об алгоритмах. Метод половинного деления. Понятие оптимизационных задач и оптимизационных моделей.

    курсовая работа [333,4 K], добавлен 17.03.2008

  • Розв'язання економічних задач з інформаційного менеджменту за допомогою програми Excel. Створення таблиці "Фірма" з інформацією про працівників фірми. Визначення кількість чоловіків та жінок на фірмі. Обчислення терміну погашення кредитів підприємства.

    контрольная работа [102,0 K], добавлен 30.07.2008

  • Определение коэффициента полных затрат, вектора валового выпуска, межотраслевых поставок продукции. Расчет матрицы алгебраических дополнений и полных затрат. Отрицательные коэффициенты в индексной строке. Сервис "поиск решения" в программе MS Excel.

    контрольная работа [118,2 K], добавлен 06.05.2013

  • Примеры задач, решения которых найдено путем использования метода экспертных оценок и линейное прогнозирование (симплекс-метод). Определение структуры комплекса оборудования и получения максимальной выгоды при наличии ограниченных исходных данных.

    контрольная работа [54,7 K], добавлен 07.07.2010

  • Построение математической модели и решение задачи математического программирования в средах MathCad и MS Excel. Решение систем с произвольными векторами свободных коэффициентов. Определение вектора невязки. Минимизация и максимизация целевой функции.

    отчет по практике [323,5 K], добавлен 01.10.2013

Работы в архивах красиво оформлены согласно требованиям ВУЗов и содержат рисунки, диаграммы, формулы и т.д.
PPT, PPTX и PDF-файлы представлены только в архивах.
Рекомендуем скачать работу.