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

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

Введение

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

Систематизированное научно обоснованное прогнозирование развития социально-экономических процессов на основе специализированных осуществляется с первой половины 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. Создание таблиц, расчет по формулам, построение диаграмм

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вариант Б.

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

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

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

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

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

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

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

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

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

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

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

Вариант В.

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

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

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

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

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

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

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

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

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

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

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

;  де ; ;

,

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

;    

;      

Таким образом, искомая эмпирическая формула имеет вид 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 ExcelOffice Excel 2003. Учебный курс / В. Кузьмин, - СПб.: Питер: Издательская группа BHV, 2004. - 493 с.2003. Эффективный самоучитель / В.В. Серогородский, - СПб.: Наука и техника, 2005. - 400 с.: Сборник примеров и задач / С.М. Лавренов, - М.: Финансы и статистика,2003. - 336 с.