Составление сметы расходов. Подбор параметра в MS Excel. Бланк для решения конкретной задачи

Лабораторная работа № 7.

Составление сметы расходов. Подбор параметра в MS Excel.

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

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

Таким образом, подготовка соответствующего бланка является программой для решения поставленной задачи средствами EXCEL.

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

Постановка задачи.

Подготовить бланк для вычисления сметы расходов на разработку какого-либо изделия. Бланк содержит таблицу для задания исходных данных, таблицу для расчетной части и заголовок (Рис. 1)

Ячейкам С4:С9 присваиваются соответственно имена Изделие, ОЗП, КН, Компл, Комплекс программ, ФЗП.

В ячейке Е2 этого рабочего листа введите текст формулу "=СЦЕПИТЬ("Смета расходов на разработку изделия ";Изделие)"

Расчетную часть расположим в ячейках Е4:Н14 (Рис. 3)

В колонке Значение вводятся формулы,

Сохраните бланк в вашем каталоге.

Заполнение бланка.

Введите для примера исходные данные (Рис. 4):

После ввода исходных данных на экране представлена готовая смета.

Подбор параметра.

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

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

Активизируйте ячейку, в которой вычисляется общий объем сметы – 482094. Выберите команду Сервис à Подбор параметра (Рис. 5).

Установите те значения, которые указаны на рис. 5.

После щелчка по кнопке ОК на экране должно появиться диалоговое окно Результат подбора параметра (Рис.6).

Из информации диалогового окна видно, что решение найдено. После этого можно щелкнуть кнопку ОК и посмотреть, какие значения оказались в смете.

Если полученная Заработная плата покажется вам недостаточной, и Вы считаете необходимым, чтобы она равнялась 75000, даже за счет уменьшения коэффициента прибыли, поставьте ОЗП=75000, выделите новый полученный объем сметы и еще раз обратитесь к опции Подбор параметра, изменяя ячейку Коэффициент прибыли.