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

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

2. Пользуясь командой " Сервис" -" Подбором параметра", определить:

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

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

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

Рис.П.18.5

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

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

Рис.П.18.6

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

Рис.П.18.7

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

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);

· суммарную (годовую) прибыль (P);

· квартальные значения Qi;

· суммарные годовые расходы на рекламу (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). Сделать выводы относительно возможности использования инструмента " подбор параметра".

14. Распечатать результаты и отчет по выполненной работе.