Расход=Аренда + Услуги + Выплаты

↑

⇐ ПредыдущаяСтр 20 из 24Следующая ⇒

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

Сначала решим простую задачу по математике: найдём численное решение уравнение .

В ячейке B4 вводим начальное значение х, в B5 =exp(-B4)-B4. Чтобы в B4 оказался корень уравнения, в B5 должен быть 0. Вкладка Данные/Анализ «что-если»/Подбор параметра. В открывшемся диалоговом окне указываем целевую ячейку B5 (Поле Установить в ячейке), Изменяя значение ячейки B4. В поле Значение вводим 0. Всё. Задача решена:х=0,567138

1. Теперь более сложная задача. На Лист1 введите данные калькуляции цены книги, приведенные в табл. 1. Константами должны быть:

количество экземпляров;

проценты накладных расходов;

затраты на зарплату;

затраты на рекламу;

цена продукции;

себестоимость продукции

(в таблице эти значения показаны на сером фоне жирным шрифтом). Остальные данные должны быть представлены в виде расчетных формул:

Доход= Цена продукции ^x Количество экземпляров;

Себестоимость реализованной продукции= Себестоимость продукции ^x Количество экземпляров;

Валовая прибыль= Доход – Себестоимость реализованной продукции;

Накладные расходы= Доход ^x Проценты накладных расходов;

Валовые издержки= Накладные расходы + Затраты на зарплату + Затраты на рекламу;

Прибыль от продукции= Валовая прибыль – Валовые издержки.

Введите формулы и сверьте результаты расчета по ним с данными, приведенными в табл. 1.

Калькуляция     Таблица 1

А

В

Количество экземпляров (шт.)

20 000   

Доход (руб.)

3 000 000

Себестоимость реализованной продукции (руб.)

1 000 000 

Валовая прибыль (руб.)

2 000 000

Проценты накладных расходов

30%

Затраты на зарплату (руб.)

125 000

Затраты на рекламу (руб.)

25 000

Накладные расходы (руб.)

900 000

Валовые издержки (руб.)

1 050 000

Прибыль от продукции (руб.)

950 000

Цена продукции (руб.)

Себестоимость продукции (руб.)

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

3. Подберите такую цену книги, чтобы прибыль от продукции составила 1500 000 руб.

   Для этого:

· выполните команду меню Данные/«анализ что-если»/Подбор параметра;

· в диалоговом окне Диспетчер сценариев в поле Установить в ячейке укажите целевую ячейку, содержащую значение прибыли от продукции ($B$11), в поле Значение укажите то значение, которое должно быть достигнуто (1500 000) и в поле Изменяя ячейку введите абсолютную ссылку на ячейку, содержащую значение цены ($B$14);

· щелкните кнопку OK.

4. Ознакомьтесь с результатами выполнения операции подбора параметра в окне Результат подбора параметра и щелкните кнопку OK для изменения значений ячеек таблицы в соответствии с найденным решением.

5. Вернитесь к исходному состоянию таблицы, используя описанный в пунктах 3, 4 способ подбора параметра.

6. Самостоятельно определите, каков должен быть показатель процентов накладных расходов, чтобы прибыль за продукцию составила 1000 000 рублей.

7. Выполните подбор параметров для данных, представленных в виде диаграммы.

Для этого:

· с помощью мастера диаграмм на основании таблицы Калькуляцияпостройте обычную (плоскую) гистограмму;

· разместите диаграмму справа от таблицы и увеличьте ее размер;

· щелкните левой клавиши мыши по одному из столбцов диаграммы, тем самым выделив весь ряд значений;

· щелкните левой клавишей мыши при нажатой клавише CTRL по столбику диаграммы, соответствующему показателю прибыли от продукции, и растяните вверх этот столбик;

· в диалоге Подбор параметра заполните необходимые окна для получения новых значений.

Построение сценариев

8. По данным рабочего листа Лист2постройте сценарии решения задачи расчета значения прибыли за продукцию путем изменения параметров «Цена» и «Проценты накладных расходов».

9. Для построения каждого сценария необходимо:

· выполнить команду Данные/Анализ «что-если»/Сценарии;

· в диалоговом окне Диспетчер сценариев нажать кнопку Добавить;

· в окне Добавления сценария ввести в поле Название сценария имя (например, «Изменение цены1»);

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

· нажать кнопку OK;

· в окне Значения ячеек сценария ввести значение изменяемого параметра (например, для цены 175).

10. Повторите указанные в пункте 9 действия для добавления в список сценариев еще трех сценариев расчета прибыли, изменяя параметры «Цена» (200) и «Проценты накладных расходов» (20% и 40%);

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

12. Для создания отчета по сценарию в диалоговом окне Диспетчер сценариев нажмите кнопку Отчет.

13. В окне Отчет по сценарию выберите тип отчета Сводная таблица, установите абсолютную ссылку на ячейку со значением результата (Прибыль за продукцию) и нажмите ОК.

14. Перейдите на новый рабочий лист и введите таблицу с упрощенным бюджетом предприятия на 2000 год и выполните прогнозирование бюджета на 2001, 2002 и 2003 годы, манипулируя темпами роста различных показателей. Подготовьте 4 сценария с различными прогнозами роста и создайте итоговый сравнительный отчет. 

Бюджет предприятия на 2000 г. приведен в таблице:

А

B

C

D

E

2000 г.

2001 г.

2002 г.

2003 г.

Объем продаж

Размер прибыли в %

25%

Общая прибыль

Аренда

Услуги

Выплаты

Расход

Чистая прибыль

Прогнозируемый ежегодный рост отдельных показателей приведен в таблице:

А

В

Объем продаж

4%

Размер прибыли

2%

Аренда

5%

Услуги

3%

Выплаты

5%

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

·    присвойте имена ячейкам В13-В17 в соответствии с названиями показателей в столбце А. Для этого последовательно устанавливайте курсор на каждую ячейку и назначайте имя, вводя его слева от строки формул. Например, щёлкаете по В13, заменяете В13 слева от строки формул на Рост_объёма_продаж;

· присвойте имена ячейкам результата С11, D11, E11 – «Прибыль_2001», «Прибыль_2002», «Прибыль_2003»;

· Введите расчетные формулы для вычисления показателей в ячейках С2:Е11:

Общая прибыль= Объем продаж * Размер прибыли в %

Познавательные статьи:



Техника прыжка в длину с разбега

Организация работы процедурного кабинета

Области применения синхронных машин

Оптимизация по Винеру и Калману