Задание № 18 (Часть 2). Подбор параметра.

Цель:Использование инструментов "Подбор параметра" для решения
типовых задач.

Темы: Нахождение и анализ данных с помощью «Подбора параметров».

1. Создайте таблицу, приведенную справа на рис.18.5, воспользовавшись расчетными формулами из левой таблицы. Эта таблица представляет гипотетическую смету расходов на выполнение некоторых работ, где отдельные статьи сметы связаны между собой определенными зависимостями. При создании таблицы (сметы) исходными данными являются величины Мк и Зп, а Q - задаваемое значение. Анализ сметы сводится к сопоставлению отдельных статей расходов, общих расходов и прибыли с общей стоимостью работ.

2. Пользуясь командой Данные – Работа с данными – Анализ «что-если» - Подбор параметра, определите:

· при какой общей стоимости работ прибыль достигнет величины 500 т.р.;

· при какой общей стоимости работ прибыль достигнет величины 1000 т.р.;

· при какой общей стоимости работ отношение прибыли к общей стоимости достигнет величины 50%.

Рис.18.5

3. Изменяя значения общей стоимости работ (Q) и копируя результаты на новый лист (A1:G3), постройте таблицу, отражающую зависимость величин (П/Q)% и прибыли (П) от общей стоимости работ (Q). Пример такой таблицы - на рис.18.6.

4. Постройте графики зависимостей (П/Q)% и прибыли (П) от общей стоимости работ (Q) аналогично графикам приведенным на рис.18.6.

Рис.18.6

5. Создайте таблицу, приведенную на рис.18.7. В этой таблице представлены гипотетические поквартальные сведения о сбыте некоторых товаров, себестоимости продукции, доходах от реализации и величине прибыли. Объем сбыта зависит от некоторого сезонного коэффициента (Кi), а также не линейно зависит от затрат на рекламу продукции при прочих фиксированных факторах. При создании таблицы исходными данными являются величины Кi, Qi, C и R. Остальные величины вычисляются по формулам, приведенным в комментариях к таблице (диапазон A14:D17). Создав таблицу, убедитесь, что полученные результаты расчетов по формулам совпадают с приведенными в таблице рис.18.7. Точность представления данных в таблице – два десятичных знака после запятой, для процентных величин – младший разряд целой части числа.

6. Определите характер зависимостей Vi=f(Qi), Di=f(Qi), Pi=f(Qi), введя несколько значений Qi (в диапазоне от 1000 до 100000). Постройте вручную или используя средства Excel примерные графики названных зависимостей.

Рис.18.7

7. Пользуясь "подбором параметра", определите:

· можно ли получить в IV квартале прибыль P₄=100000 т.р., изменяя расходы на рекламу Q₄;

· величину расходов на рекламу в IV квартале (Q₄), необходимую для получения прибыли P₄=150000 т.р.;

· пользуясь последовательным подбором параметра, максимальное значение прибыли P₄ с точностью до единиц целой части числа.

8. Сделайте выводы о возможностях использования "подбора параметра" и "правилах" применения данного инструмента, выполнив следующий эксперимент.

8.1. Задайте величину затрат на рекламу Q₄=2000 т.р

8.2. Подбирая параметр Q₄, определите, при каком значении Q4 будет достигнута величина прибыли P₄=30000 т.р.

8.3. Задайте величину затрат на рекламу Q₄=60000 т.р

8.2. Подбирая параметр Q4, определите, при каком значении Q4 будет достигнута величина прибыли P₄=32000 т.р.

9. Найдите близкие к максимальным значения:

- прибыли для четырех кварталов (Pi);	- квартальные значения Qi;
- суммарную (годовую) прибыль (P);	- суммарные годовые расходы на рекламу (Q);
- долю расходов на рекламу в общем доходе от реализации продукции (Q/D).

Результат разместите в ячейках F11:G11. Для определения названных величин рекомендуется последовательно воспользоваться несколько раз подбором параметра.

10. Пользуясь расчетными формулами (A14:D17), постройте на отдельном листе таблицу, отражающую зависимости величин V₄, D₄, P₄ от величины Q₄ (для значений Q₄ меняющихся от 10000 до 100000 с шагом 10000).

11. Постройте на отдельном листе два графика (подобных представленным на рис.18.6), на которых будут отображены зависимости V₄=f(Q₄) для первого графика и D₄=f(Q₄) и P₄=f(Q₄) для второго графика.

12. Точность подбора параметра.

12.1. Запишите в ячейках С2, С3 и С4 (рис.18.8) исходные данные и формулу для вычисления произведения двух чисел Y=A*X. Исходные значения сомножителей: А=0,5 и Х=2,35.

12.2. Выполните следующие действия:

· скопируйте исходные данные и формулу (С2:С4) в диапазоны Е2:Е4, G2:G4 и I2:I4;

· для столбцов E, G и I установите разрядность отображаемых значений равную соответственно 4, 6 и 16 разрядов после десятичной запятой, как показано на рис.18.8.

12.3. Выполните "подбор параметра" для нахождения первого сомножителя (А), расположенного в ячейке С2 при С3=2,35, искомом значении целевой ячейки С4=4,3758 и начальном значении С2=0,5.

12.4. Повторите "подбор параметра" для той же формулы, записанной в диапазонах Е2:Е4, G2:G4 и I2:I4.

Рис.18.8

12.5. Выполните вручную умножение для данных в ячейках Е2:Е3, G2:G3 и I2:I3 с заданной разрядностью и запишите результаты в ячейки Е6, G6 и I6 соответственно.

12.6. Сравните данные, полученные в результате ручных вычислений и "подбора параметров". Оцените величину и знак погрешности вычислений.

13. Создайте таблицу, в которой выполняется возведение числа Х в степень Y по образцу, приведенному на рис 18.9. Формула Z=X^Y, обеспечивающая вычисления, записана в ячейке D13, а исходные данные Х=2 и У=2 - в ячейках D11 и D12 соответственно.

13.1. Пользуясь "подбором параметра", выполните поиск такого значения Х (при неизменном Y=2), которое обеспечит Z=4. Результат поиска должен быть представлен в ячейках В11:В13.

Рис.18.9

13.2. Аналогично п. 13.1 выполните поиск значения Y (при неизменном Х=2), которое обеспечит Z=4. Результат поиска должен быть представлен в ячейках С11:С13.

13.3. Сравните результаты непосредственного вычисления (D11:D13) и результаты двух "подборов параметров" (B11:B13 и C11:C13). Сделайте выводы относительно возможности использования инструмента "подбор параметра".