close

Вход

Забыли?

вход по аккаунту

?

Информатика, физика

код для вставкиСкачать
Объекты презентации
Интернет-ресурсы:
Ресурсы созданные в редакторах Macromedia Flash MX и Photoshop
Скрин-шоты
Моделирование в
электронных таблицах
Информатика и экономика
Автор:
Каракулова С.С.
Цель
Дидактические:
изучить моделирование как наглядный способ исследования
объекта;
углубить знания учащихся при работе с Microsoft Excel;
Развивающие:
развить у учащихся навыки компьютерного моделирования;
развивать у учащихся познавательные умения при решении
задач;
Воспитательные:
выработать чувства коллективизма и товарищеской
взаимопомощи, воспитывать чувство трудолюбия.
План работы
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
Организационный момент
Мотивация учебной деятельности
Инструктаж
Моделирование
Формы представления моделей
Системный подход в моделировании
Типы информационных моделей
Основные этапы разработки и исследования моделей на
компьютере
Биологические модели развития популяций
Оптимизационное моделирование в экономике
Закрепление материала
Подведение итогов
Домашнее задание
Инструктаж
1. Вводный инструктаж (правила ТБ) – 2
мин. (запись в тетради учета рабочего
времени)
2. Текущий инструктаж по ходу работы –5
мин.
4. Моделирование
Человечество в своей деятельности постоянно создает и использует
модели окружающего мира.
Модели позволяют представить в наглядной форме объекты и
процессы, недоступные для непосредственного восприятия (очень
большие или очень маленькие объекты, очень быстрые или очень
медленные процессы и др.).
В курсе географии первые представления о нашей планете Земля мы
получаем, изучая ее модель – глобус.
4. Моделирование
В курсе физики изучаем работу двигателя внутреннего сгорания по его
модели.
4. Моделирование
В химии при изучении строения вещества используем модели
молекул и кристаллических решеток.
4. Моделирование
В биологии изучаем строение человека по анатомическим муляжам и
др.
4. Моделирование
Модели играют чрезвычайно важную роль в проектировании и
создании различных технических устройств, машин и механизмов,
зданий, электрических цепей и т. д. Без предварительного создания
чертежа невозможно изготовить даже простую деталь, не говоря уже
о сложном механизме.
В процессе проектирования зданий и сооружений кроме чертежей
часто изготавливают макеты. В процессе разработки летательных
аппаратов поведение их моделей в воздушных потоках исследуют в
аэродинамической трубе.
Разработка электрической схемы обязательно предшествует
созданию электрических цепей и так далее.
4. Моделирование
Развитие науки невозможно без создания
теоретических моделей (теорий, законов,
гипотез и пр.), отражающих строение,
свойства и поведение реальных объектов.
Создание новых теоретических моделей
иногда коренным образом меняет
представление человечества об
окружающем мире (гелиоцентрическая
система мира Коперника, модель атома
Резерфорда-Бора, модель расширяющейся
Вселенной, модель генома человека и пр.).
Адекватность теоретических моделей
законам реального мира проверяется с
помощью опытов и экспериментов.
4. Моделирование
Все художественное творчество фактически является процессом
создания моделей. Например, такой литературный жанр, как басня,
переносит реальные отношения между людьми на отношения между
животными и фактически создает модели человеческих отношений.
Более того, практически любое литературное произведение может
рассматриваться как модель реальной человеческой жизни.
Моделями, в художественной форме отражающими реальную
действительность, являются также живописные полотна, скульптуры,
театральные постановки и пр.
4. Моделирование
Моделирование – это метод познания,
состоящий в создании и исследовании
моделей.
4. Модель
Каждый объект имеет большое количество различных свойств. В
процессе построения модели выделяются главные, наиболее
существенные для проводимого исследования свойства. В процессе
исследования аэродинамических качеств модели самолета в
аэродинамической трубе важно, чтобы модель имела геометрическое
подобие оригинала, но не важен, например, ее цвет. При построении
электрических схем – моделей электрических цепей – необходимо
учитывать порядок подключения элементов цепи друг к другу, но не
важно их геометрическое расположение друг относительно друга и так
далее.
4. Модель
Разные науки исследуют объекты и процессы под разными углами
зрения и строят различные типы моделей. В физике изучаются
процессы взаимодействия и изменения объектов, в химии – их
химический состав, в биологии – строение и поведение живых
организмов и так далее.
Возьмем в качестве примера человека: в разных науках он
исследуется в рамках различных моделей. В рамках механики его
можно рассматривать как материальную точку, в химии – как объект,
состоящий из различных химических веществ, в биологии – как
систему, стремящуюся к самосохранению, и так далее.
4. Модель
Модель – это некий новый объект, который
отражает существенные особенности
изучаемого объекта, явления или процесса.
4. Модель
География, военное дело, судоходство и пр. невозможны без
информационных моделей поверхности Земли в виде карт. Различные
типы географических карт (политические, физические и пр.)
представляют информационные модели, отражающие различные
особенности земной поверхности, то есть один и тот же объект
отражают несколько моделей.
С другой стороны, разные объекты могут описываться одной
моделью. Так, в механике различные материальные тела (от планеты
до песчинки) могут рассматриваться как материальные точки.
4. Модель
Никакая модель не может заменить сам объект. Но при решении
конкретной задачи, когда нас интересуют определенные свойства
изучаемого объекта, модель оказывается полезным, а подчас и
единственным инструментом исследования.
Один и тот же объект может иметь множество
моделей, а разные объекты могут описываться
одной моделью.
5. Формы представления моделей
Все модели можно разбить на два больших класса:
•модели предметные (материальные)
•модели информационные.
Предметные модели воспроизводят геометрические, физические и
другие свойства объектов в материальной форме (глобус,
анатомические муляжи, модели кристаллических решеток,
макеты зданий и сооружений и др.).
Информационные модели представляют объекты и процессы в образной
или знаковой форме.
Образные модели (рисунки, фотографии и др.) представляют
собой зрительные образы объектов, зафиксированные на
каком-либо носителе информации (бумаге, фото- и кинопленке
и др.).
Широко используются образные информационные модели в образовании и
науках, где требуется классификация объектов по их внешним признакам (в
ботанике, биологии, палеонтологии и др.).
5. Формы представления моделей
Знаковые информационные модели строятся с использованием
различных языков (знаковых систем). Знаковая информационная
модель может быть представлена в форме текста (например,
программы на языке программирования), формулы (например,
второго закона Ньютона F= та, таблицы (например, периодической
таблицы элементов Д. И. Менделеева) и так далее.
Иногда при построении знаковых информационных моделей
используются одновременно несколько различных языков.
Примерами таких моделей могут служить географические карты,
графики, диаграммы и пр. Во всех этих моделях используются
одновременно как язык графических элементов, так и символьный
язык.
Первые информационные модели создавались в форме наскальных
рисунков, в настоящее же время информационные модели обычно
строятся и исследуются с использованием современных
компьютерных технологий.
6. Системный подход в моделировании
Понятие о системе. Окружающий нас мир состоит из множества различных
объектов, каждый из которых имеет разнообразные свойства, и при этом
объекты взаимодействуют между собой
Планеты входят в состав более крупного объекта – Солнечной системы, а
Солнечная система – в состав нашей галактики «Млечный путь». С другой
стороны, планеты состоят из атомов различных химических элементов, а
атомы – из элементарных частиц. Можно сделать вывод, что практически
каждый объект состоит из других объектов, то есть представляет собой
систему.
Важным признаком системы является ее целостное функционирование.
Система является совокупностью взаимосвязанных объектов, которые
называются элементами системы.
7. Типы информационных
моделей
Информационные модели отражают различные типы систем
объектов, в которых реализуются различные структуры
взаимодействия и взаимосвязи между элементами системы. Для
отражения систем с различными структурами используются
различные типы информационных моделей: табличные,
иерархические и сетевые.
7.1. Табличные информационные модели
Одним из наиболее часто используемых типов информационных
моделей является прямоугольная таблица, которая состоит из
столбцов и строк. Такой тип моделей применяется для описания ряда
объектов, обладающих одинаковыми наборами свойств. С помощью
таблиц могут быть построены как статические, так и динамические
информационные модели в различных предметных областях. Широко
известно табличное представление математических функций,
статистических данных, расписаний поездов и самолетов, уроков и так
далее.
В табличной информационной модели перечень однотипных
объектов или свойств размещен в первом столбце (или строке)
таблицы, а значения их свойств размещаются в следующих столбцах
(или строках) таблицы.
8. Основные этапы разработки и
исследования моделей на
компьютере
Процесс разработки моделей и их исследования на компьютере
можно разделить на несколько основных этапов:
Этап 1
На этапе исследования объекта или процесса обычно строится
описательная информационная модель. Такая модель выделяет
существенные с точки зрения целей проводимого исследования
параметры объекта, а несущественными параметрами пренебрегает.
Этап 2
На втором этапе создается формализованная модель, то есть
описательная информационная модель записывается с помощью
какого-либо формального языка. В такой модели с помощью формул,
уравнений, неравенств и пр. фиксируются формальные соотношения
между начальными и конечными значениями свойств объектов, а
также накладываются ограничения на допустимые значения этих
свойств.
8. Основные этапы разработки и
исследования моделей на
компьютере
Этап 3
На третьем этапе необходимо формализованную информационную
модель преобразовать в компьютерную модель, то есть выразить ее
на понятном для компьютера языке.
Два пути построения компьютерной модели
1. построение алгоритма решения задачи и его кодирование на одном
из языков программирования;
2. построение компьютерной модели с использованием одного из
приложений (электронных таблиц, СУБД и пр.).
В процессе создания компьютерной модели полезно разработать
удобный графический интерфейс, который позволит визуализировать
формальную модель, а также реализовать интерактивный диалог
человека с компьютером на этапе исследования модели.
8. Основные этапы разработки и
исследования моделей на
компьютере
Этап 4
Этап исследования информационной модели состоит в проведении
компьютерного эксперимента. Если компьютерная модель
существует в виде программы на одном из языков программирования,
ее нужно запустить на выполнение и получить результаты.
Если компьютерная модель исследуется в приложении, например в
электронных таблицах, можно провести сортировку или поиск данных,
построить диаграмму или график и так далее.
8. Основные этапы разработки и
исследования моделей на
компьютере
Этап 5
Пятый этап состоит в анализе полученных результатов и
корректировке исследуемой модели. В случае различия результатов,
полученных при исследовании информационной модели, с
измеряемыми параметрами реальных объектов можно сделать вывод,
что на предыдущих этапах построения модели были допущены
ошибки или неточности. Например, при построении описательной
качественной модели могут быть неправильно отобраны
существенные свойства объектов, в процессе формализации могут
быть допущены ошибки в формулах и так далее. В этих случаях
необходимо провести корректировку модели, причем уточнение
модели может проводиться многократно, пока анализ результатов не
покажет их соответствие изучаемому объекту.
9. Биологические модели развития популяций
В биологии при исследовании развития биосистем строятся
динамические модели изменения численности популяций различных
живых существ (бактерий, рыб, животных и пр.) с учетом различных
факторов. Взаимовлияние популяций рассматривается в моделях
типа «хищник-жертва».
9. Формальная биологическая
модель развития популяций.
Динамика численности популяций
Изучение динамики численности популяций естественно начать с
простейшей модели неограниченного роста, в которой численность
популяции ежегодно увеличивается на определенный процент.
Математическую модель можно записать с помощью рекуррентной
формулы, связывающей численность популяции следующего года с
численностью популяции текущего года, с использованием
коэффициента роста а:
xn+1=a*xn
Например, если ежегодный прирост численности популяции
составляет 5%, то а – 1,05.
9. Формальная биологическая
модель развития популяций
Модель ограниченного роста
В модели ограниченного роста учитывается эффект
перенаселенности, связанный с нехваткой питания, болезнями и так
далее, который замедляет рост популяции с увеличением ее
численности. Введем коэффициент перенаселенности b, значение
которого обычно существенно меньше а (b<<а). Тогда коэффициент
ежегодного увеличения численности равен (а – b*хn) и формула
принимает вид:
xn+1=(a-b*xn)*xn
9. Формальная биологическая
модель развития популяций
Модель ограниченного роста с отловом
В модели ограниченного роста с отловом учитывается, что на
численность популяций промысловых животных и рыб оказывает
влияние величина ежегодного отлова. Если величина ежегодного
отлова равна «с», то формула принимает вид:
xn+1=(a-b*xn)*xn-c
9. Формальная биологическая
модель развития популяций
Взаимодействие популяций
Популяции обычно существуют не изолированно, а во
взаимодействии с другими популяциями. Наиболее важным типом
такого взаимодействия является взаимодействие между жертвами и
хищниками (например, караси-щуки, зайцы-волки и так далее). В
модели «хищник-жертва» количество жертв хn и количество хищников
уn связаны между собой. Количество встреч жертв с хищниками можно
считать пропорциональным произведению количеств жертв и
хищников, а коэффициент f характеризует возможность гибели
жертвы при встрече с хищниками. В этом случае численность
популяции жертв ежегодно уменьшается на величину
f * хn * уn и формула для расчета численности жертв принимает вид:
xn+1=(a-b*xn)*xn-c-f*xn*yn
9. Формальная биологическая
модель развития популяций
Численность хищников в отсутствии жертв
Численность популяции хищников в отсутствие жертв (в связи с
отсутствием пищи) уменьшается, что можно описать рекуррентной
формулой:
yn+1=d*yn
где значение коэффициента d < 1 характеризует скорость уменьшения
численности популяции хищников.
Численность хищников за счет жертв
Увеличение популяции хищников можно считать пропорциональной
произведению собственно количеств жертв и хищников, а
коэффициент «е» характеризует величину роста численности
хищников за счет жертв. Тогда для численности хищников можно
использовать формулу:
yn+1=d*yn+e*xn*yn
9. Компьютерная биологическая
модель развития популяций
Построение компьютерной модели в электронных таблицах
Построим в электронных таблицах компьютерную модель,
позволяющую исследовать численность популяций с использованием
различных моделей: неограниченного роста, ограниченного роста,
ограниченного роста с отловом и «хищник – жертва».
9. Компьютерная биологическая
модель развития популяций
Построение компьютерной модели
1) В ячейки В1 и В6 внести начальные значения
численности популяций жертв и хищников.
В ячейки В2:В5 внести значения коэффициентов а,
b, с и f, влияющих на изменение численности жертв.
В ячейки В7 и В8 внести значения коэффициентов d
и е, влияющих на изменение численности
хищников.
В столбце D будем вычислять численность
популяции в соответствии с моделью
неограниченного роста, в столбце Е –
ограниченного роста, в столбце F – ограниченного
роста с отловом, в столбцах G и Н – «хищникжертва».
9. Компьютерная биологическая
модель развития популяций
Построение компьютерной модели
В ячейки D1, E1, F1 и G1 внести значения начальной численности
популяций жертв, в ячейку H1 – хищников. В ячейку D2 внести
рекуррентную формулу неограниченного роста =$B$2*D1.
В ячейку Е2 внести рекуррентную формулу ограниченного роста
=($В$2-$В$3*Е1)*Е1.
В ячейку F2 внести рекуррентную формулу ограниченного роста с
отловом =($B$2-$B$3*F1)*F1-$B$4. В ячейку G2 внести рекуррентную
формулу изменения количества жертв
=($B$2-$B$3*G1)*G1-$B$4-$B$5*G1*H1. В ячейку Н2 внести
рекуррентную формулу изменения количества хищников
=$B$7*H1+$B$8*G1*H1.
Скопировать внесенные формулы в ячейки столбцов командой
[Правка-Заполнить-Вниз]. В ячейках столбцов ознакомиться с
динамикой изменения численности популяций.
См.следующий слайд
9. Компьютерная биологическая
модель развития популяции
X1=
a=
b=
c=
f=
Y1=
d=
e=
1.5
1.1
0.03
0.03
0.04
1
0.9
0.05
1.5
1.65
1.815
1.9965
2.1962
2.4158
2.6573
2.9231
1.5
1.5825
1.6656
1.749
1.8321
1.9146
1.9961
2.0762
1.5
1.5525
1.6054
1.6587
1.712
1.7653
1.8183
1.871
1.5
1.4925
1.4867
1.4826
1.48
1.4789
1.4792
1.4808
4
5
6
1
0.975
0.9503
0.9259
0.9019
0.8785
0.8556
0.8333
4
3
2
1
0
1
2
3
7
Неограниченный рост
Ограниченный рост
Ограниченный рост с отловом
хищник-жертва
хищник-жертва
8
10. Оптимизационное
моделирование в экономике
В сфере управления сложными системами (например, в экономике)
применяется оптимизационное моделирование, в процессе которого
осуществляется поиск наиболее оптимального пути развития
системы.
Критерием оптимальности могут быть различные параметры;
например, в экономике можно стремиться к максимальному
количеству выпускаемой продукции, а можно к ее низкой
себестоимости. Оптимальное развитие соответствует экстремальному
(максимальному или минимальному) значению выбранного целевого
параметра.
Развитие сложных систем зависит от множества факторов
(параметров), следовательно, значение целевого параметра зависит
от множества параметров. Выражением такой зависимости является
целевая функция
К = F(X1,X2,...,Xn),
где К – значение целевого параметра;
Х1,Х2,...,Хп – параметры, влияющие на развитие системы.
10. Оптимизационное
моделирование в экономике
Цель исследования состоит
в нахождении экстремума этой функции и определении значений
параметров, при которых этот экстремум достигается. Если
целевая функция нелинейна, то она имеет экстремумы, которые
находятся определенными методами.
Однако часто целевая функция линейна и, соответственно,
экстремумов не имеет. Задача поиска оптимального режима при
линейной зависимости приобретает смысл только при наличии
определенных ограничений на параметры. Если ограничения на
параметры (система неравенств) также имеют линейный характер, то
такие задачи являются задачами линейного программирования.
(Термин «линейное программирование» в имитационном
моделировании понимается как поиск экстремумов линейной
функции, на которую наложены ограничения.)
Рассмотрим в качестве примера экономического моделирования
поиск вариантов оптимального раскроя листов материала на
заготовки определенного размера.
10. Оптимизационное
моделирование в экономике
Содержательная постановка проблемы.
В ходе производственного процесса из листов материала получают
заготовки деталей двух типов А и Б тремя различными способами, при
этом количество получаемых заготовок при каждом методе
различается.
Тип
заготовки
Количество заготовок
Способ 1 раскроя
Способ 2 раскроя
Способ 3 раскроя
А
10
3
8
Б
3
6
4
Необходимо выбрать оптимальное сочетание способов раскроя, для
того чтобы получить 500 заготовок первого типа и 300 заготовок
второго типа при расходовании наименьшего количества листов
материала.
10. Оптимизационное
моделирование в экономике
Формальная модель.
Параметрами, значения которых требуется определить, являются
количества листов материала, которые будут раскроены различными
способами:
Х1 – количество листов, раскроенное способом 1;
Х2 – количество листов, раскроенное способом 2;
Х3 – количество листов, раскроенное способом 3.
Тогда целевая функция, значением которой является количество
листов материала, примет вид:
F = Х1+ Х2 + Х3.
10. Оптимизационное
моделирование в экономике
Ограничения определяются значениями требуемых количеств
заготовок типа А и Б, тогда с учетом количеств заготовок, получаемых
различными способами, должны выполняться два равенства:
10X1 + 3X2 + 8Х3 = 500;
ЗХ1 + 6Х2 + 4Х3 = 300.
Кроме того, количества листов не могут быть отрицательными,
поэтому должны выполняться неравенства:
Х1 > 0;
Х2> 0;
Х3 > 0.
Таким образом, необходимо найти удовлетворяющие ограничениям
значения параметров, при которых целевая функция принимает
минимальное значение.
10. Оптимизационное
моделирование в экономике
Компьютерная модель
Ячейки В2, С2 и D2 выделить для хранения значений параметров X1,
Х2 и ХЗ.
В ячейку В4 ввести формулу вычисления целевой функции:
=B2+C2+D2. В ячейку В7 ввести формулу вычисления количества
заготовок типа А: =10*В2+ 3*С2 + 8*D2 .
В ячейку В8 ввести формулу
вычисления количества
заготовок типа Б: =3*В2+ 6*С2 +
4*D2 .
10. Оптимизационное
моделирование в экономике
Исследование модели
Для поиска оптимального набора
значений параметров, который
соответствует минимальному значению
целевой функции, воспользоваться
надстройкой электронных таблиц Поиск
решения.
Для активизации надстройки ввести
команду [Сервис -Надстройки...]. На
диалоговой панели установить флажок
перед элементом списка Поиск решения.
10. Оптимизационное
моделирование в экономике
Ввести команду [Сервис-Поиск решения...]. На появившейся
диалоговой панели Поиск решения установить:
-адрес целевой ячейки;
-вариант оптимизации значения целевой ячейки (максимизация,
минимизация или подбор значения);
-адреса ячеек, значения которых изменяются в процессе поиска
решения (в которых хранятся значения параметров);
-ограничения (типа « = »
для ячеек, хранящих
количество деталей, и
типа «>» для
параметров).
10. Оптимизационное
моделирование в экономике
Результат вычислений
Таким образом, для изготовления 500 деталей А и 300 деталей Б
требуется 70 листов материала, при этом 20 листов необходимо
раскроить по первому, 20 листов – по второму и 30 листов – по третьему
варианту.
11. Закрепление материала
Оптимизация решений в Excel
Задача
Пусть предприятие выпускает продукцию – столы. Данные о реализации
продукции и затратах на ее производство представлены в таблице №1.
Таблица № 1. Исходные данные для оптимизации решений методом
подбора параметра.
При этом прибыль составит
1972 млн.тенге. Допустим, что
мы поставили себе цель
повысить уровень прибыли до
2 млрд.тенге. Это можно
осуществить тремя
способами:
1)повысить цену;
2)увеличить объем
производства и
3)снизить затраты, и прежде
всего заработную плату.
Попробуем найти такую цену
реализации, чтобы прибыль
была 2 млрд.тенге.
11. Закрепление материала
Решение
Меню «Сервис – Подбор параметра». В результате откроется диалоговое
окно «Подбор параметра». Вставим соответствующие поля адрес $C$10,
2000, $C$3 и нажмем ОК. В результате мы получим сообщение, что
решение найдено, и, нажав еще раз «ОК», в исходной таблице получим
значение прибыли 2 млрд.тенге, в случае, если цену реализации повысим
до 871,556
млн.тенге.
В первом поле
необходимо
установить адрес
целевой ячейки, во втором - ее значение.
В третьем поле необходимо указать адрес изменяемой ячейки, т.е. той ячейки, в
которой будет осуществляться подбор параметра. Однако не все задачи могут
быть решены путем подбора параметра. Решение не будет найдено, если
изменяемая и целевая ячейка логически не связаны. При такой сходимости в
окне «Результат подбора параметра» можно установить «Шаг» и «Пауза» и с их
помощью осуществлять процесс подбора параметра.
11. Закрепление материала
Подбор параметра по диаграмме. Для этого необходимо:
•На основе табличных данных построить диаграмму;
•Выделить один из столбиков на диаграмме, в результате чего он будет
обрамлен восемью маленькими квадратиками;
•Поставить курсор на верхний средний квадратик и при помощи левой
клавиши мыши увеличивать или уменьшить высоту столбика.
Как только клавиша будет отпущена, то при первом выполнении данной
операции откроется диалоговое окно «Подбор параметра», а затем –
«Результат подбора параметра». При этом, данные в целевой ячейке
также будут изменяться. В последующем подбор параметра с помощью
диаграмм будет происходить без открытия диалоговых окон.
11. Закрепление материала
Подбор параметра по диаграмме.
Постройте диаграмму. Какая будет цена реализации, если прибыль нужна
в сумме 1 млрд.тенге?
11. Анализ накладных расходов
Цель работы
Приобрести практические навыки разделения накладных расходов на
переменные и постоянные с помощью электронных таблиц EXCEL.
Краткое описание работы
Наиболее сложной для планирования и контроля статьей издержек
являются накладные расходы. Входя в себестоимость продукции,
накладные затраты могут существенно снизить вложенный доход и
прибыльность продаж предприятия. Сложность заключается в том, что
почти все накладные расходы – смешанные, т. е. имеют постоянную и
переменную часть. Качественное планирование и контроль затрат, расчет
себестоимости в формате вложенного дохода и анализ безубыточности
возможны только после разделения всех статей накладных расходов на
переменные и постоянные. На сегодня наиболее точным способом
разделения затрат признан линейный регрессионный анализ методом
наименьших квадратов. В ходе лабораторной работы слушателю
предстоит самостоятельно произвести анализ трех статей накладных
расходов (см. Таблицу 1), выбрав в качестве параметра базовой
активности объем товарной продукции.
11. Анализ накладных расходов
Работа выполняется в книге Lab3.xls с использованием возможностей
табличного процессора Excel.
В процессе выполнения работы слушатель должен:
•ответить на ряд теоретических вопросов, относящихся к теме
работы;
•изучить возможности табличного процессора Excel для построения
точечных диаграмм;
•изучить использование статистических функций Excel для разделения
издержек;
•произвести разделение издержек двумя способами, сравнить
результаты и сделать вывод о предпочтительности методов.
11. Анализ накладных расходов
Январь
Февраль
Март
Апрель
Май
Июнь
Июль
Август
Сентябрь
Октябрь
Ноябрь
Декабрь
Энергия
технологическая,
тыс. грн.
18,7
0,7
16,0
15,0
0,1
12,6
23,2
11,2
21,0
37,1
26,2
28,2
Содержание
оборудования,
тыс. грн.
22,9
20,4
19,9
23,8
19,4
20,2
19,9
18,5
39,5
35,1
31,5
32,8
Цеховые расходы,
тыс. грн.
Товарная продукция,
тыс. грн.
48,2
38,8
34,8
28,2
28,0
32,7
32,3
28,8
72,0
59,3
51,0
52,9
180,25
31,81
388,00
259,78
1,15
194,30
322,52
291,51
469,79
736,98
553,45
601,79
11. Анализ накладных расходов
Подготовительная часть работы
Изучить содержание примера (используя текст программы, который
появляется на экране компьютера после открытия книги Lab3.xls) и
ответить на следующие вопросы:
1. Какова цель разделения издержек на переменные и постоянные?
2. Что может служить в качестве параметра базовой активности
при разделении издержек?
3. Каким образом можно произвести разделение издержек, какой
способ является более точным и почему? Каковы преимущества
графического метода разделения затрат?
4. Как в дальнейшем используются результаты анализа (переменные
и постоянные издержки)?
11. Анализ накладных расходов
Выполнение работы
Работа состоит из двух этапов, которые необходимо выполнять в
следующей последовательности:
Этап 1
1. Открыть книгу Lab3.xls.
2. Разделение издержек при помощи диаграмм Excel. Используя
мастер диаграмм, построить точечную диаграмму зависимости
одной статьи издержек, например, цеховых расходов, от объема
производства товарной продукции.
3. Добавить на точечной диаграмме линию тренда, уравнение линии
тренда, а также величину достоверности линейной
аппроксимации. Пример построения диаграммы приведен в
Приложении 1.
4. Повторить п.п. 2 и 3 для остальных статей издержек.
11. Анализ накладных расходов
Этап 2
1. Разделение издержек при помощи функций Excel. Используя мастер
функций, ввести в столбце "Переменные расходы" функцию
НАКЛОН() и рассчитать размер переменных затрат для каждой
статьи издержек.
2. Ввести в столбце "Постоянные расходы" функцию ОТРЕЗОК() и
рассчитать размер постоянных затрат для каждой статьи
издержек.
3. Ввести в столбце "Коэффициент достоверности" функцию
КВПИРСОН() и рассчитать величину достоверности линейной
аппроксимации для каждой статьи издержек.
4. Проанализировать результаты работы и сохранить файл под своим
именем.
11. Анализ накладных расходов
Эн ерг и я техн олог и ческ а я, ты с. г рн .
Разделение издержек на переменные и постоянные при помощи
точечной диаграммы
40.00
35.00
30.00
25.00
20.00
15.00
y = 0.0446x + 2.5045
10.00
2
R = 0.8674
5.00
0.00
0.00
100.00
200.00
300.00
400.00
500.00
600.00
700.00
800.00
Уравнение линии
линейной регрессии
имеет вид: У = аХ + в,
где
У – размер
издержек, тыс. грн.;
Х – выпуск товарной
продукции, тыс. грн.;
а – коэффициент
переменных расходов;
в – постоянные расходы.
Тов арн ая п родукц и я, ты с. грн .
Величина R2 показывает, насколько хорошо линия линейной регрессии
описывает зависимость между издержками и параметром базовой активности.
Чем ближе этот показатель к 1, тем сильнее линейная зависимость, тем
достовернее результаты анализа.
11. Закрепление материала
Решении экономических задач методом компьютерного моделирования
Задача 1. «Реализация компьютеров по кварталам»
Сформируйте таблицу. Изделий – не менее 5, кварталов – 4.
Рассчитайте среднеквартальную реализацию в рублях каждого
компьютера. Цену компьютера укажите в рублях и в условных
единицах. Пересчет в рубли – автоматический по приведенному в
таблице курсу (ввести формулу). Вычислите итоговые показатели по
каждому кварталу. Предусмотрите подпись экономиста. Сохраните
таблицу под именем задача 1 в своей папке.
11. Закрепление материала
Задача 2. «Анализ продажи товаров по магазину №3»
Сформируйте таблицу со следующими реквизитами:
-№,
-Товарная группа,
-Соответствующий период прошлого года,
-Текущий год,
-Отклонение в сумме,
-Отклонение в процентах.
В произвольной форме введите данные по 4-5 товарным группам.
Графы отклонений в сумме и процентах вычислите по формулам.
Рассчитайте итоговые показатели по всем графам, кроме первых
двух. Предусмотрите подпись начальника планового отдела.
Сформируйте диаграмму, показывающую продажу товаров по
каждой товарной группе за прошлый и текущий год. Сохраните
таблицу.
Документ
Категория
Презентации
Просмотров
104
Размер файла
633 Кб
Теги
1/--страниц
Пожаловаться на содержимое документа