Информационные технологии в экономике

Выполните команду меню ВидДиаграмма Ганта. В поле Название задачи введите название задачи Предварительное экономическое обоснование проекта и нажмите клавишу Tab. MS Project сам вводит примерную длительность задачи 1день? Задайте Длительность 1нед? Нажмите клавишу Enter.

Введите остальные задачи согласно таблице 4.1.

Для задачи Согласование и утверждение технического проекта добавьте заметку Оплата заказчиком 25% проектной стоимости (в поле Название задачи выберите задачу и нажмите кнопку Заметки задачи ).

Задача с нулевой длительностью автоматически помечается как веха (например, завершающая работа 17), но сделана вехой может быть любая задача. На диаграмме Ганта веха отображается символом . Чтобы пометить задачи 2, 7 и 17 как вехи, выберите соответствующую задачу в поле Название задачи. Нажмите кнопку Сведения о задаче , выберите вкладку Дополнительно и установите флажок Пометить задачу как веху.

Рис. 4.2. Окончательный вид диаграммы Ганта для создаваемого проекта

Таблица 4.1

№	Название задачи	Длитель-ность
1	Предварительное экономическое обоснование проекта	1неделя?
2	Разработка технического задания	1неделя?
3	Изготовление макета счетчика тепловой энергии	30 дней?
4	Изготовление макета электросчетчика	20 дней?
5	Проведение испытаний на макете счетчика тепловой энергии	15 дней?
6	Проведение испытаний на макете электросчетчика	10 дней?
7	Согласование и утверждение технического проекта	2 дня?
8	Разработка рабочих и сборочных чертежей счетчика тепловой энергии	40 дней?
9	Разработка рабочих и сборочных чертежей электросчетчика	35 дней?
10	Изготовление опытного образца счетчика тепловой энергии	40 дней?
11	Изготовление опытного образца электросчетчика	20 дней?
12	Испытание опытного образца счетчика тепловой энергии	25 дней?
13	Испытание опытного образца электросчетчика	10 дней?
14	Устранение конструктивных недостатков счетчика тепловой энергии, доработка конструкции на технологичность	1 неделя?
15	Устранение конструктивных недостатков электросчетчика, доработка конструкции на технологичность	1 неделя?
16	Подготовка технической документации для передачи заказчику	1 неделя?
17	Подписание акта приемки работ	0 дней?

Для создания повторяющейся задачи в поле Название задачи выберите пустую строку. Выполните команду меню ВставкаПовторяющаяся задача. В окне Сведения о повторяющейся задаче введите Название задачи Еженедельные совещания, Длительность 1 час?, Повторять еженедельно по понедельникам. В группе Пределы повторения введите Начало Т, Окончание Т+6 месяцев. Календарь Стандартный. Нажмите кнопку OK.

Структурирование, создавая иерархию, позволяет объединить задачи в более удобные для управления фазы (суммарные задачи). Начальная и конечная дата суммарной задачи определяется начальной и конечной датами первой и последней ее подзадач. Для структурирования задач служат кнопки структуры: На уровень выше , На уровень ниже , Показать подзадачи , Скрыть подзадачи .

Выберите задачу Предварительное экономическое обоснование проекта, которую требуется сделать подзадачей. Выполните команду меню ВставкаНовая задача и введите название новой суммарной задачи Предварительная фаза проекта. Выделите задачи с 1-й по 2-ю согласно табл. 4.1. Нажмите кнопку На уровень ниже .

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

Чтобы показать все подзадачи, нажмите кнопку и затем кнопку .

Самостоятельно создайте суммарные задачи Технический проект (задачи с 3_й по 7-ю согласно табл. 4.1) и Рабочий проект (задачи с 8-й по 17-ю согласно табл. 4.1).

4. Создание взаимосвязей между задачами

Одним из наиболее надежных способов планирования задач является установление взаимосвязей между ними, т.е. зависимостей задач. После того как задачи связаны, изменение дат задачи-предшественника влияет на изменение дат задач-последователей. В MS Project по умолчанию создается зависимость задач типа «Окончание-начало». Для реального моделирования проекта тип связи можно изменить на «Начало-начало», «Окончание-окончание» или «Начало-окончание».

Свяжите задачи 1 и 2 (согласно табл. 4.1), образующие Предварительную фазу проекта.

Чтобы связать две или более задач друг с другом, выберите их в поле Название задачи, причем в том же порядке, в котором они должны быть связаны. Чтобы выбрать несколько задач подряд, нажмите клавишу Shift и, удерживая ее, щелкните первую и последнюю задачи. Чтобы выбрать несколько задач вразброс, нажмите клавишу Ctrl и, удерживая ее, щелкните поочередно нужные задачи. Нажмите кнопку Связать задачи .

Свяжите задачи, входящие в фазу Технический проект, которые связаны с разработкой счетчика тепловой энергии (задачи 3, 5 и 7 согласно табл. 4.1). Свяжите задачи, входящие в фазу Технический проект, которые связаны с разработкой электросчетчика (задачи 4, 6 и 7 согласно табл. 4.1).

Свяжите задачи, образующие фазу Рабочий проект, которые связаны с разработкой счетчика тепловой энергии (задачи 8, 10, 12, 14, 16 и 17 согласно табл. 4.1). Свяжите задачи, образующие фазу Рабочий проект, которые связаны с разработкой электросчетчика (задачи 9, 11, 13, 15, 16 и 17 согласно табл. 4.1).

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

Измените тип связи задач Разработка рабочих и сборочных чертежей счетчика тепловой энергии и Изготовление опытного образца счетчика тепловой энергии. на Начало-начало, Запаздывание 30 дней?

Чтобы изменить связь задач, дважды щелкните линию связи между задачами, которую требуется изменить. Будет открыто окно Зависимость задач. Если будет открыто окно Стили отрезков, вы щелкнули не линию связи; необходимо закрыть это окно и снова дважды щелкнуть связь задач. В поле со списком Тип выберите нужный тип связи между задачами, задайте если нужно Запаздывание и нажмите кнопку OK.

Чтобы разорвать связь между задачами, выберите эти задачи в поле Название задачи и нажмите кнопку Разорвать связи задач .

Измените тип связи задач Разработка рабочих и сборочных чертежей счетчика электросчетчика и Изготовление опытного образца электросчетчика. на Окончание-окончание, Запаздывание 3 недели?

MS Project автоматически рассчитывает даты начала и окончания задач и фаз на основе их длительностей и взаимосвязей. Если необходимо, можно привязать задачу или фазу к определенной дате. Чтобы задать дату для задачи (вехи) Подписание акта приемки работ, выделите эту задачу и нажмите кнопку Сведения о задаче . В окне Сведения о задаче выберите вкладку Дополнительно и задайте Тип ограничения Окончание не позднее, Дата ограничения Т+6 месяцев. Если откроется окно Мастер планирования с предупреждением о конфликте планирования, выберите переключатель Продолжить. Будет установлено ограничение Окончание не позднее и нажмите кнопку ОК.

Добавим для задачи Согласование и утверждение технического проекта параметр Крайний срок Т+3 месяца (в окне Сведения о задаче на вкладке Дополнительно). Установка крайнего срока не влияет на планирование задач. MS Project только отображает индикатор крайнего срока на диаграмме Ганта, оповещая о том, что задача имеет крайний срок окончания.

Самостоятельно добавьте Крайний срок Т+6 месяцев для задачи Подписание акта приемки работ.

5. Назначение ресурсов

Для создания списка ресурсов выполните команду меню ВидЛист ресурсов. Также выполните команду меню ВидТаблицаВвод. В поле Название ресурса введите Инженер-конструктор. Задайте Тип Трудовой, Группа Инженеры и техники, Макс. единиц 500%, Стандартная ставка 90,00 р./ч, Ставка сверхурочных 135,00 р./ч.

Microsoft Project использует два типа ресурсов: трудовые ресурсы - это сотрудники или оборудование; материальные ресурсы - это расходные материалы или сырье.

Для трудовых ресурсов в поле Макс. единиц вводят в процентах максимальное число единиц, доступных для этого ресурса. Например, значение 300% указывает, что доступны три единицы полной занятости определенного ресурса (три инженера-конструктора).

Для материальных ресурсов в поле Единицы измерения материалов вводят единицы измерения материального ресурса, например тонны.

Задайте другие ресурсы согласно таблице 4.2.

Таблица 4.2

№	Название ресурса	Тип	Единицы измерения материалов	Группа	Макс. единиц	Стандарт-ная ставка	Ставка сверх-урочных	Затраты на использование
1	Инженер-конструктор	Труд		Инженеры и техники	500%	90 р./ч	135 р./ч
2	Техник	Труд		Инженеры и техники	200%	50 р./ч	75 р./ч
3	Инженер-испытатель	Труд		Инженеры и техники	150%	80 р./ч	120 р./ч
4	Руководитель проекта	Труд			100%			80000 р.
5	Лаборатория	Труд			100%			120000 р.
6	Комплектующие для счетчика тепловой энергии	Матер	р.	Материалы и комплектующие				20 000 р.
7	Комплектующие для электросчетчика	Матер	р.	Материалы и комплектующие				12 000 р.

Каждый ресурс имеет график работы (по умолчанию это стандартный календарь). Чтобы изменить график работы ресурса Лаборатория, выделите этот ресурс и выполните команду меню ПроектСведения о ресурсе или нажмите кнопку Сведения о ресурсе . На вкладке Рабочее время задайте поле Базовый календарь Две смены. Нажмите кнопку ОК. Аналогично задайте календарь Две смены для инженера-испытателя.

Чтобы назначить задачам ресурсы, выполните команду меню ВидДиаграмма Ганта. Выделите задачу Предварительное экономическое обоснование проекта и нажмите кнопку Назначить ресурсы . В поле Название ресурса укажите ресурс Руководитель проекта и Единицы 10%. Нажмите кнопку Назначить. Сделав все назначения, нажмите кнопку Закрыть.

Если не нажимать кнопку Закрыть, то окно Назначение ресурсов остается открытым.

Чтобы назначить неполную занятость ресурса, в поле Единицы следует задать значение меньше 100%. Если нужно назначить несколько одинаковых ресурсов, то задают значения 200%, 300% и т.д.

Чтобы назначить несколько разных ресурсов, выберите названия этих ресурсов, удерживая клавишу Ctrl.

Назначьте остальные ресурсы согласно таблице 4.3.

Таблица 4.3

№	Название задачи	Название ресурса	Единицы
1	Предварительное экономическое обоснование проекта	Руководитель проекта	10%
2	Разработка технического задания	Инженер-конструктор	200%
3	Изготовление макета счетчика тепловой энергии	Инженер-конструктор Техник Лаборатория Комплектующие для счетчика тепловой энергии	250% 100% 10% 14 000р.
4	Изготовление макета электросчетчика	Инженер-конструктор Техник Лаборатория Комплектующие для электросчетчика	150% 100% 7% 7 500р.
5	Проведение испытаний на макете счетчика тепловой энергии	Инженер-конструктор Инженер-испытатель Техник Лаборатория	100% 75% 100% 20%
6	Проведение испытаний на макете электросчетчика	Инженер-конструктор Инженер-испытатель Техник Лаборатория	100% 75% 100% 15%
7	Согласование и утверждение технического проекта	Руководитель проекта Инженер-конструктор	40% 50%
8	Разработка рабочих и сборочных чертежей счетчика тепловой энергии	Инженер-конструктор	400%
9	Разработка рабочих и сборочных чертежей электросчетчика	Инженер-конструктор	300%
10	Изготовление опытного образца счетчика тепловой энергии	Инженер-конструктор Техник Лаборатория Комплектующие для счетчика тепловой энергии	350% 100% 30% 6 000р.
11	Изготовление опытного образца электросчетчика	Инженер-конструктор Техник Лаборатория Комплектующие для электросчетчика	250% 100% 25% 4 500р.
12	Испытание опытного образца счетчика тепловой энергии	Инженер-конструктор Инженер-испытатель Техник Лаборатория	200% 100% 100% 20%
13	Испытание опытного образца электросчетчика	Инженер-конструктор Инженер-испытатель Техник Лаборатория	150% 100% 100% 15%
14	Устранение конструктивных недостатков счетчика тепловой энергии, доработка конструкции на технологичность	Инженер-конструктор	400%
15	Устранение конструктивных недостатков электросчетчика, доработка конструкции на технологичность	Инженер-конструктор	300%
16	Подготовка технической документации для передачи заказчику	Руководитель проекта Инженер-конструктор	40% 500%
17	Подписание акта приемки работ	Руководитель проекта	5%

Для проверки и изменения назначений ресурсов выполните команду меню ВидИспользование ресурсов. Если название ресурса выделено красным жирным шрифтом, это означает, что ресурс перегружен.

6. Просмотр календарного плана и его подробных сведений

Для отображения всего проекта на экране служит команда меню ВидДиаграмма Ганта. Затем выполните команду ВидМасштаб, выберите параметр Весь проект и нажмите кнопку OK.

Чтобы изменить шкалу времени диаграммы Ганта, служат кнопки Увеличить и Уменьшить .

Для просмотра начальной и конечной даты проекта, а также общих трудозатрат, общих затрат и процента выполнения проекта выполните команду ПроектСведения о проекте. Нажмите кнопку Статистика. В столбце Затраты строки Текущие просмотрите общие плановые затраты проекта 731674,03 руб.

Для определения критического пути (задач, не имеющих резервов времени) выполните команду меню ВидДиаграмма Ганта и затем нажмите кнопку Мастер диаграмм Ганта (команда меню ФорматМастер диаграмм Ганта). Чтобы отформатировать задачи критического пути, следуйте инструкциям мастера диаграмм Ганта. В результате на диаграмме некоторые задачи (задачи Рабочего проекта, связанные с проектированием электросчетчика) приобрели красный цвет, т.е. они являются критическими.

Подведите курсор к индикатору крайнего срока (задача Подписание акта приемки работ) и переместите его влево к индикатору вехи . Убедитесь, что, перемещая индикатор , мы влияем на критический путь.

Чтобы отобрать для отображения только критические задачи, выполните команду ПроектФильтрКритические задачи. Чтобы снова отобразить все задачи, выполните команду ПроектФильтрВсе задачи.

В MS Project разработано множество различных представлений календарного плана и проекта в целом. Для смены представления служит меню Вид, где можно задать представление (Диаграмма Ганта, Подробная диаграмма Ганта, Сетевой график, Сетевой график с описанием, Календарь, График ресурсов, Использование задач и т.д.). Опробуйте все указанные здесь представления.

Если необходимое представление отсутствует в меню Вид, выполните команду ВидДругие представления, а затем выберите нужное представление и нажмите кнопку Применить.

Для отображения только определенных задач можно воспользоваться фильтром (команды ПроектФильтр или ПроектФильтрДругие фильтры или ПроектФильтрАвтофильтр). С помощью фильтра покажите задачи с крайними сроками.

Задайте представление Диаграмма Ганта и выполните команду ПроектФильтрАвтофильтр (кнопка Автофильтр ). Рядом с Названием задачи, Длительностью и т.д. появится значки . Щелкните на значке рядом с полем Длительность и задайте условие <=1 недели. Чтобы отключить автофильтр, повторно выполните команду ПроектФильтрАвтофильтр.

Чтобы просмотреть затраты на задачи, выполните команду ВидДругие представленияЛист задач.

Чтобы просмотреть затраты на ресурсы, выполните команду ВидЛист ресурсов или лучше команду ВидТаблицыСуммарные данные. В полученной таблице красным цветом выделен ресурс Инженер-конструктор, что свидетельствует о его перегруженности (Пиковая загрузка 1050%). В MS Project ситуация, когда ресурсу назначено больше задач, чем он может выполнить в доступное рабочее время, называется превышением доступности.

7. Корректировка календарного плана

Чтобы избавиться от превышения доступности, необходимо откорректировать календарный план для снижения пиковой загрузки конструкторов. Простейший вариант решения проблемы - принять на работу еще 6 конструкторов (1 конструктор=100%). Для этого выполните команду ВидЛист ресурсов и укажите для ресурса Инженер-конструктор значение Макс. единиц 1100%.

Попробуем поискать другие выходы их данной ситуации. Перед корректировкой создайте резервную копию проекта с помощью команды ФайлСохранить как… Верните значение Макс. единиц 500%. Выполните команду ВидИспользование ресурсов. Щелкните по значку рядом с красной строкой Инженер-конструктор. С помощью кнопки Увеличить задайте для окна справа ежедневный масштаб представления. Убедитесь, что перегрузка будет наблюдаться с 28 марта по 30 мая и с 9 июня по 17 июня. В данном представлении не видно, сколько дополнительных конструкторов требуется. Выполните команду ВидГрафик ресурсов и вы увидите, сколько конструкторов требуется в каждый момент времени. Пиковая загрузка - с 14 апреля по 28 апреля. Вернитесь к представлению Диаграмма Ганта и попытайтесь, перемещая задачи на более поздние сроки и перераспределяя ресурсы между задачами, добиться осуществимости проекта за счет увеличения длительности проекта (без увеличения численности работников и без увеличения затрат!). Увеличив длительность проекта примерно на 30-40 дней, можно добиться поставленной цели. Проверьте, как изменились затраты на проект.

Чтобы переместить задачу, подведите курсор к задаче (курсор примет вид ) и, нажав левую кнопку мыши, перемещайте задачу. Если появится окно Мастер планирования с предупреждением о конфликте планирования, то установите переключатель Продолжить. Конфликт планирования допускается и нажмите кнопку ОК.

Чтобы перераспределить ресурсы, назначенные задаче, используйте кнопку Назначить ресурсы .

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

С помощью команды ВидТаблицыСуммарные данные убедитесь, что пиковая загрузка уменьшилась, но превышение доступности сохранилось. Чтобы просмотреть результаты выравнивания, выберите представление Диаграмма Ганта с выравниванием и посмотрите дату окончания проекта после автоматического выравнивания. Сравните результаты автоматического выравнивания и выравнивания вручную, выполненного вами ранее. Завершите работу с копией проекта и вернитесь в исходный проект.

8. Отслеживание хода выполнения проекта

Перед началом процесса отслеживания рекомендуется сохранить базовый план. Базовый план (до 11 на проект) представляет снимок календарного плана на момент сохранения и содержит всю информацию о задачах, ресурсах и назначениях. С помощью базового плана можно отслеживать ход выполнения, просматривать отклонения и вносить необходимые исправления. Чтобы сохранить базовый план выполните команду СервисОтслеживаниеСохранить базовый план... Нажмите кнопку ОК.

Мастер отслеживания помогает создать таблицу, в которой удобно обновлять сведения о ходе выполнения задач. На панели инструментов Консультант нажмите кнопку Отслеживание. В боковой области выберите ссылку Подготовка к отслеживанию хода работы над проектом. На шаге 1 выберите режим отслеживания: вручную (укажите Нет). На шаге 2 укажите Всегда отслеживать путем указания процента завершения по трудозатратам. В результате в текущем представлении MS Project появился дополнительный столбец %завершения по трудозатратам, а на диаграмме Ганта появились соответствующие данные. Задайте для задачи Предварительное экономическое обоснование проекта процент завершения 70%.

Для сравнение фактических и плановых трудозатрат для задач выполните команды ВидДиаграмма Ганта и ВидТаблицаТрудозатраты. Сравните значения в полях Трудозатраты, Базовые, Фактические и .Оставшиеся.

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

9. Печать и публикация проекта

Чтобы просмотреть, как будет выглядеть текущее представление проекта при печати, нажмите кнопку Предварительный просмотр .

В MS Project предлагается более 20 встроенных отчетов. Выполните команду ВидОтчеты. Выберите категорию Обзорные и обзорный отчет Сводка по проекту. Также просмотрите отчет Дела по исполнителям (категория Назначения).

В категории Настраиваемые представлены все стандартные отчеты. В окне Настраиваемые отчеты можно создать новые отчеты (кнопка Создать), изменить существующие (кнопка Изменить), скопировать в шаблон (из шаблона) Global.MPT и т.д. В окне Настраиваемые отчеты выберите отчет Использование трудовых ресурсов и нажмите кнопку Изменить. На вкладке Определение замените Недели на Месяцы .На вкладке Подробности укажите Формат даты Январь 2002. Нажмите кнопку ОК. Просмотрите отчет (кнопка Просмотр).

Для публикации сведений о проекте в Internet можно сохранить их в формате HTML. Выполните команду ФайлСохранить как веб-страницу. Введите имя экспортируемого файла в поле Имя файла и нажмите кнопку Сохранить. В первом окне Мастера экспорта нажмите кнопку Далее. Во втором окне установите переключатель Использовать существующую схему и нажмите кнопку Далее. В следующем окне Выберите схему для данных Сводная таблица задач и ресурсов и нажмите кнопку Далее. В следующем окне установите флажок Экспорт на основе шаблона HTML. Для выбора шаблона HTML нажмите кнопку Обзор. В окне Обзор выберите любой шаблон и нажмите кнопку ОК. В окне Мастер экспорта-параметры схемы нажмите кнопку Готово. Просмотрите созданный файл в формате HTML.

Лабораторная работа №5. Планирование работ средствами Microsoft Excel

Цель. Изучить некоторые возможности Microsoft Excel (условное форматирование, проверка ввода, работа со ссылками и массивами и т.д.), приобрести навыки использования этих инструментов для решения задач планирования работ в проектной организации.

Задание

1. Ввести данные на рабочие листы Исходные данные, Распределение, Диаграмма Ганта и Зарплата согласно заданию.

2. Осуществить распределение проектировщиков по проектам.

3. Составить ведомость на выплату заработной платы.

Основные сведения

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

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

Рассмотрим следующую ситуацию. Проектной организации, где работает 6 конструкторов и 4 технолога, поручили выполнить 6 проектов (Проект А, Проект Б и т.д.). Работа над каждым проектом включает два этапа: 1) этап конструкторской подготовки производства (КПП) и 2) этап технологической подготовки производства (ТПП). Необходимо распределить проектировщиков по проектам, назначить даты начала этапов, рассчитать даты завершения этапов. Для простоты планирование осуществляется только на один месяц - май 2005 года.

Накладываемые ограничения.

1. Этап ТПП может начаться только после завершения предыдущего этапа КПП.

2. Над одним проектом может работать не более 4 конструкторов и не более 3 технологов.

3. Все проекты должны завершиться не позднее заданных сроков.

4. Один проектировщик может участвовать в нескольких проектах, но одновременно может работать только над одним проектом.

Технология работы

1. Создание рабочего листа Исходные данные

Запустите на выполнение программу Microsoft Excel, создайте рабочую книгу с именем Планирование работ(<ФИО студента>).xls. Переименуйте лист Лист1 с помощью команды меню ФорматЛистПереименовать лист. Задайте новое имя Исходные данные.

Другой способ переименовать лист - двойной щелчок левой кнопкой мыши по имени листа.

Введите данные на лист Исходные данные согласно рис. 5.1 и приведенным ниже указаниям.



Рис. 5.1. Рабочий лист Исходные данные

Для обеспечения проверки вводимых значений в ячейку C1 выполните команду ДанныеПроверка. В окне Проверка вводимых значений на вкладке Параметры задайте Тип данных Список. В поле Источник введите текст

Январь;Февраль;Март;Апрель;Май;Июнь;Июль;Август;Сентябрь;Октябрь;Ноябрь;Декабрь

На вкладке Сообщение для ввода задайте Заголовок Месяц и Сообщение Выберите месяц, для которого создается план работ. Нажмите кнопку ОК.

Для ячейки D1 самостоятельно задайте проверку ввода, указав в качестве Источника текст 2004;2005;2006;2007;2008;2009;2010

Чтобы разместить текст в нескольких ячейках (например, в ячейках D3:F3) необходимо выделить эти ячейки и нажать кнопку Объединить и поместить в центре .

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

Чтобы разместить текст в ячейке (ячейках) по центру с переносом слов (например, в ячейке D15 или в ячейках В4:В5), выполните команду Формат Ячейки… и на вкладке Выравнивание задайте по горизонтали по центру, по вертикали по центру. Установите флажок переносить по словам. Нажмите кнопку ОК.

Для диапазона ячеек В6:В11 укажите Отступ 1 (команда меню Формат Ячейки, вкладка Выравнивание).

Чтобы автоматизировать ввод числовых рядов (1, 2, 3,…), введите числа 1 и 2 в соседние ячейки, затем выделите эти две ячейки, и с помощью мыши протяните в нужном направлении.

Для удобства дальнейшей работы рекомендуется создавать имена для ячеек и диапазонов ячеек. Чтобы быстро создать имя для диапазона ячеек Н5:Н13, выделите эти ячейки и щелкните левой кнопкой мыши по полю Имя (слева от строки формул), введите имя Праздники и нажмите клавишу Enter.

ВНИМАНИЕ! Имена вводятся БЕЗ пробелов!

ВНИМАНИЕ! Ввод имени завершается нажатием клавиши ENTER!

Самостоятельно создайте имена: СпецКонструктор для ячейки В16, СпецТехнолог: для ячейки В17, ЧислоКонструкторов для ячейки D16, ЧислоТехнологов для ячейки D17, ВсегоПроектировщиков для ячейки D18 и Специальность для ячеек С22:С31.

Для ячеек С22:С31 задайте проверку вводимых значений (Тип данных Список, Источник =$В$16:$В$17). Введите данные в таблицу Список сотрудников-проектировщиков.

Для автоматизации подсчета числа конструкторов в ячейку D16 введите формулу =СЧЁТЕСЛИ(Специальность;СпецКонструктор)

Для ввода имен удобно использовать клавишу F3.

В ячейку D17 формулу введите самостоятельно.

В ячейке D18 подсчитайте сумму.

Создайте лист Распределение и введите данные на этот лист согласно рис. 5.2 и приведенным ниже указаниям.

Рис. 5.2. Рабочий лист Распределение

Чтобы не копировать данные с рабочего листа Исходные данные в диапазон ячеек А3:С12 лист Распределение, введите в ячейку А3 формулу

='Исходные данные'!A22

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

Скопируйте эту формулу в ячейки диапазона А3:С12.

Чтобы скопировать формулу из ячейки А3 в ячейки А3:С12, подведите курсор к черному квадратику в правом нижнем углу ячейки А3, чтобы курсор превратился в черный крестик. Нажав левую кнопку мыши, «протащите» курсор по ячейкам А3:С12.

Чтобы заполнить ячейки D2:I2, можно применить два способа (заполните ячейки D2:I2 двумя способами):

Способ 1. На листе Исходные данные выделите ячейки В6:В11 и скопируйте их в буфер обмена. Затем щелкните правой кнопкой мыши по ячейке D2 на листе Распределение и в контекстном меню выберите команду Специальная вставка. В окне Специальная вставка установите флажок транспонировать и нажмите ОК.

Способ 2. Выделите ячейки D2:I2 на листе Распределение. В строке формул введите формулу

=ТРАНСП('Исходные данные'!B6:B11)

Функция ТРАНСП(массив) находится в категории Ссылки и массивы.

Функция ТРАНСП() должна быть введена как формула массива. Для этого необходимо одновременно нажать клавиши Ctrl, Shift и Enter. В результате в строке формул введенная формула будет заключена в фигурные скобки.

Для проверки ввода в диапазон D3:I12 задайте проверку данных с параметрами Тип данных Список, Источник 0;1

Для ячейки J2 создадим примечание. Щелкните правой кнопкой мыши по ячейке J2 и выберите команду Добавить примечание. Введите примечание Количество проектов, в которых участвует работник.

В ячейках J3:J12 подсчитайте сумму по соответствующей строке.

В ячейку D13 введите формулу

=СУММЕСЛИ($C3:$C12;СпецКонструктор;D3:D12)

В остальные ячейки диапазона D13:I14 формулы введите самостоятельно.

Чтобы облегчить ввод данных в диапазон ячеек D3:I12, необходимо конструкторов и технологов сгруппировать отдельно. Применим сортировку таблицы на листе Распределение. Выделите диапазон ячеек А2:К12 и выполните команду меню ДанныеСортировка. В окне Сортировка диапазона в поле Сортировать по задайте Специальность. Нажмите кнопку ОК.

Заполните диапазон ячеек D3:I12 согласно рис. 5.2 (с учетом накладываемых ограничений).

Формулы для ячеек К3:К12 введем позднее. Самостоятельно отформатируйте лист Распределение, чтобы он соответствовал рис. 5.2.

Создайте рабочий лист Диаграмма Ганта. Введите данные на этот лист согласно рис. 5.3 и приведенным ниже указаниям.

Рис. 5.3. Рабочий лист Диаграмма Ганта

Чтобы автоматизировать заполнение ячеек В3:В14, ни один из ранее рассмотренных способов не подходит. Введите в ячейку В3 формулу

=СМЕЩ('Исходные данные'!B$6;$A3-1;0)

Размножьте эту формулу в диапазоне ячеек В3:В14.

Найдите и прочитайте описание функции СМЕЩ() (категория Ссылки и массивы).

Самостоятельно введите формулы в ячейки С3:С14.

Не забудьте задать для ячеек С3:С14 Числовые форматы Дата, Тип 14.03.99.

В ячейку Е3 введите формулу =СМЕЩ('Исходные данные'!$D$6;A3-1;0)

В ячейку Е4 введите формулу =СМЕЩ('Исходные данные'!$D$6;A3-1;1)

Растяните эти формулы по столбцу Е.

В ячейку F3 введите формулу =СМЕЩ(Распределение!$D$13;0;A3-1)

В ячейку F4 введите формулу =СМЕЩ(Распределение!$D$13;1;A3-1)

Растяните эти формулы по столбцу F.

В ячейку G3 введите формулу =ОКРУГЛВВЕРХ(E3/F3;0)

Растяните эту формулу по столбцу G.

В диапазон Н3:Н14 введите даты начала работ.

Чтобы рассчитать день завершения этапа, используем функцию РАБДЕНЬ(). Она возвращает дату, отстоящую на заданное количество рабочих дней вперед или назад от даты Нач_дата. Рабочими днями не считаются выходные дни и дни, определенные как праздничные. Функция РАБДЕНЬ() используется, чтобы исключить выходные дни или праздники при вычислении даты завершения этапа.

Синтаксис функции РАБДЕНЬ(Нач_дата;Количество_дней;Праздники)

Нач_дата - это начальная дата.

Количество_дней - это количество рабочих дней до или после Нач_дата. Положительное значение аргумента Количество_дней означает будущую дату; отрицательное значение - прошедшую дату.

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

Чтобы найти день завершения этапа в ячейку I3 введите формулу

=РАБДЕНЬ(H3;G3-1;Праздники)

Растяните формулу по столбцу I.

В ячейку J2 введите формулу

=ДАТАЗНАЧ("1"&'Исходные данные'!C1&'Исходные данные'!D1)

Функция ДАТАЗНАЧ() возвращает числовой формат даты, представленной в виде текста.

Синтаксис функции ДАТАЗНАЧ(Дата_как_текст)

Дата_как_текст - это текст, представляющий дату (например, 30.01.1998).

Оператор & позволяет объединить две текстовые строки в одну строку.

В ячейку К2 введите формулу =J2+1 и размножьте ее по строке.

Отформатируем ячейку J2, чтобы кроме даты, был виден день недели. Подходящего встроенного формата не существует. Чтобы создать его, выполните команду ФорматЯчейки На вкладке Число выберите Числовые форматы (все форматы), в поле Тип задайте ДД.ММ.ГГ ДДД

Шаблон ДДД отображает день недели в виде Пн, Вт, …, Вс.

Чтобы отформатировать диапазон J2:AN2, скопируйте формат из ячейки J2 в остальные ячейки диапазона

Чтобы скопировать формат из ячейки J2 в диапазон J2:AN2, выделите ячейку J2, нажмите кнопку Формат по образцу . Рядом с курсором появится знак кисти. Выделите диапазон J2:AN2.

Чтобы выделить цветом выходные и праздничные дни, воспользуемся условным форматированием. Выделите ячейку J2 и выполните команду ФорматУсловное форматирование… Задайте данные согласно рис. 5.4.

Условие 1 задает формат для выходных дней (с помощью кнопки Формат задайте желтый цвет заливки ячеек). Условие 2 задает формат для праздничных дней (задайте красный цвет заливки ячеек).

При вводе формул в окне Условное форматирование удобнее не вводить формулы, а вставлять их из буфера обмена, предварительно набрав и отладив в какой-либо ячейке. Для копирования формулы выделите ячейку, затем В СТРОКЕ ФОРМУЛ выделите формулу и скопируйте ее в буфер обмена (кнопка Копировать ). В окне Условное форматирование в нужном месте выполните команду Вставить (кнопка Вставить ).

Чтобы добавить еще одно условие, служит кнопка .

Скопируйте созданный формат из ячейки J2 в остальные ячейки строки.

Рис. 5.4. Окно Условное форматирование для диапазона J2:AN2

Чтобы на диаграмме Ганта были представлено число проектировщиков, участвующих в проекте на данном этапе, в ячейку J3 введите формулу

=ЕСЛИ(И(J$2>=$H3;J$2<=$I3);$F3;"")

Найдите и прочитайте описание функции И() (категория Логические).

Размножьте формулу на диапазон J3:AN14.

Чтобы выделить цветом дни, когда ведется работа над проектом, а также выделить требуемый день завершения проекта, воспользуемся условным форматированием. Для ячейки J3 задайте условное форматирование согласно рис. 5.5.

Рис. 5.5. Окно Условное форматирование для диапазона J3:AN14

Условие 1 задает формат для дней работы над проектом и для последнего допустимого срока (задайте красную границу ячейки и желтый цвет заливки). Условие 2 задает формат для дней работы над проектом (задайте серый цвет заливки для этапов КПП, зеленый для этапов ТПП). Условие 3 задает формат для последнего допустимого срока (повторите формат для Условие 1).

Скопируйте созданный формат из ячейки J3 в диапазон J3:AN3, а также в диапазоны J5:AN5, J7:АN7, J9:АN9, J11:AN11, J13:AN13.

Задайте условное форматирование для ячейки J4 и скопируйте созданный формат в диапазон J4:AN4, а также в диапазоны J6:AN6, J8:АN8, J10:АN10, J12:AN12, J14:AN14.

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

Для активизации мастера суммирования выполните команду меню СервисНадстройки. В окне Надстройки установите флажок напротив строки Мастера суммирования. Нажмите кнопку ОК.

Выполните команду СервисМастерЧастичная сумма. На шаге 1 укажите, где находится таблица для суммирования 'Диаграмма Ганта'!$D$2:$AN$14. Нажмите кнопку Далее. На шаге 2 задайте Суммировать 01.05.05 Вс , Столбец Этап, Оператор =, Значение КПП и затем нажмите кнопку Добавить условие. Нажмите кнопку Далее. На шаге 3 нажмите кнопку Далее. На шаге 4 выберите ячейку J15 и нажмите кнопку Готово. В результате в ячейке J15 находится формула массива

{=СУММ(ЕСЛИ($D$3:$D$14="КПП";1;0))}

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

{=СУММ(ЕСЛИ($D$3:$D$14="КПП";J$3:J$14;0))}

Чтобы отредактировать формулу массива, после редактирования нажмите одновременно клавиши Ctrl, Shift и Enter.

Для ячейки J15 задайте условное форматирование согласно рис. 5.6.

Рис. 5.6. Окно Условное форматирование для диапазона J15:AN15

Условие 1 задает красный цвет заливки, Условие 2 - желтый и Условие 3 - зеленый.

Самостоятельно задайте формулы и форматирование для остальных ячеек диапазона J15:AN16.

В ячейке J17 найдите сумму ячеек J15 и J16. Задайте условия форматирования.

Для построения план-графика работы каждого сотрудника введите данные в диапазон D19:AN26 согласно следующим указаниям.

Создайте имя Сотрудники для диапазона 'Исходные данные'!B22:B31.

Для ячейки F20 задайте проверку вводимых значений (Тип данных Список, Источник =Сотрудники. В ячейку F21 введите формулу

=ВПР(F20;'Исходные данные'!B22:C31;2;0)

Функция ВПР() позволит по заданной ФИО проектировщика (ячейка F20) установить его специальность, просмотрев таблицу 'Исходные данные'!B22:C31.

В ячейку I20 введите формулу

=ВПР($F$20;Распределение!$B$3:$I$12;G20+2;0)

Она позволяет извлечь информацию об участии проектировщика в конкретном проекте (0 - не участвует, 1 - участвует).

В ячейку J20 введите формулу

=ЕСЛИ($I20=1;СМЕЩ(J$3;ЕСЛИ($F$21=СпецКонструктор;2*($G20-1);2*$G20-1);0);"")

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

Рис. 5.7. Окно Условное форматирование для диапазона J20:AN25

В ячейку J26 введите формулу =СЧЁТ(J20:J25), подсчитывающую число проектов, в которых участвует сотрудник в этот день. Задайте условное форматирование, сигнализирующее красным цветом ячеек, что число проектов больше 1.

Размножьте введенные формулы по соответствующим диапазонам.

Вернемся к формуле в ячейке I3. Если дата начала работ равна 01.05.05, то на диаграмме Ганта возникает ошибка - при длительности работы в пять дней, на диаграмме работа занимает четыре рабочих дня. Ошибка связана с особенностями работы функции РАБДЕНЬ(). Введите в ячейку I3 «подправленную» формулу =РАБДЕНЬ(H3-1;G3;Праздники) и размножьте ее по столбцу.

При работе с MS Excel пользователи довольно часто по ошибке портят созданные ранее формулы. Чтобы избежать несанкционированного изменения формул можно защитить ячейки, листы или всю книгу в целом. Чтобы защитить лист Исходные данные перейдите на него и выполните команду СервисЗащитаЗащитить лист. В окне Защитить лист введите пароль (можно не вводить).

ВНИМАНИЕ! Если вводите пароль - обязательно сохраните его!

Чтобы защитить лист Распределение за исключением ячеек D3:I12, в которые будут вводиться данные, выделите диапазон ячеек D3:I12 и выполните команду ФорматЯчейки. На вкладке Защита сбросьте флажок Защищаемая ячейка. Нажмите кнопку ОК. Затем защитите лист Распределение.

Самостоятельно защитите лист Диаграмма Ганта за исключением ячеек H3:H14.

На основе полученного плана работ рассчитаем заработную плату каждого работника согласно формуле

Зарплата работника = Объем работ в днях * Дневная тарифная ставка

Чтобы рассчитать объем работ в днях, перейдите на лист Распределение и создайте вспомогательную таблицу согласно рис. 5.8.

Не забудьте снять защиту листа командой СервисЗащитаСнять защиту листа

Рис. 5.8. Таблица длительностей этапов проектов

В ячейку О3 введите формулу

=СМЕЩ('Диаграмма Ганта'!$G$3;2*(M3-1);0)

В ячейку Р3 введите формулу

=СМЕЩ('Диаграмма Ганта'!$G$3;2*M3-1;0)

Размножьте формулы по таблице.

Создадим имя для диапазона О3:О8. Выделите ячейки О2:О8. Выполните команду ВставкаИмяСоздать, и в окне Создать имена укажите переключатель в строке выше. Нажмите кнопку ОК.

В результате автоматически будет создано имя Этап_КПП.

Самостоятельно создайте имя Этап_ТПП для диапазона Р3:Р8.

В ячейку К3 введите формулу

=МУМНОЖ(D3:I3;ЕСЛИ(C3=СпецКонструктор;Этап_КПП;Этап_ТПП))

Размножьте формулу по столбцу.

Для расчета зарплаты введите данные на лист Исходные данные согласно рис. 5.9.

Рис. 5.9. Тарифная сетка

Для ячейки Е33 создайте имя ДневнаяТарифнаяСтавка.

В ячейку D36 введите формулу =C36*ДневнаяТарифнаяСтавка и размножьте ее по столбцу.

Создайте лист Зарплата. Введите данные согласно рис. 5.10.

В ячейку А1 введите формулу

="Ведомость на выдачу зарплаты за "&'Исходные данные'!C1&" "&'Исходные данные'!D1

В ячейку Е3 введите формулу

=ВПР(B3;Распределение!$B$3:$K$12;10;0)

В ячейку F3 введите формулу

=E3*ВПР(D3;'Исходные данные'!$B$36:$D$53;3;1)

Размножьте формулы по столбцам.

Рис. 5.10. Ведомость на выдачу зарплаты за май 2005 года

Полученное решение не удовлетворяет условиям задачи на странице 50. Например, Петров С.И. одновременно участвует в проектах Г, Д и Е; в отдельные дни (6 мая и с 12 по 16 мая) будет не хватать конструкторов. Поэтому необходимо скорректировать разработанный план работ.

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

Изменяйте данные только в диапазонах ячеек Распределение!D3:I12 и 'Диаграмма Ганта'!H3:H14. Для проверки того, что план-график работы сотрудника удовлетворяет заданным ограничением, используйте ячейку J20.

Для упрощения распределения сотрудников разбейте их на группы по 2-4 человека и переводите эту группу с одного проекта на другой.

Лабораторная работа №6. Бизнес-планирование средствами Microsoft Excel

Цель. Изучить финансовые функции Microsoft Excel, связанные с принятием инвестиционных решений, а также провести анализ чувствительности бизнес-плана с помощью сценариев.

Задание

1. Сформировать основные документы бизнес-плана - Отчет о прибылях и убытках и Отчет о движении денежных средств.

2. Рассчитать показатели эффективности бизнес-плана.

3. Выполнить анализ чувствительности бизнес-плана с помощью сценариев.

Основные сведения

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

Технология работы

1. Общие сведения и начало работы

Скопируйте файл Бизнес-план.xls (уточните у преподавателя местонахождение файла) в свою папку. Откройте этот файл.

На рабочих листах Общие данные, Инв. план, Осн. фонды, Сбыт и Издержки введены основные сведения об инвестиционном проекте Производство фильтроэлементов для автомобилей. Горизонт планирования составляет 5 лет. В сентябре 2006 заканчивается инвестиционный этап проекта и с октября начинается выпуск продукции. На листе Сбыт приведены прогноз цены и объемов продаж.

...