13. Создание сценариев

Рассмотрим пример процедуры пользователя для анализа сценариев ([2]). Создадим процедуры Вариант 1, Вариант 2 и Вариант 3. При запуске этих процедур в ячейки будут введены значения, соответствующие этим вариантам. На рис. 13.1 приведен пример рабочей таблицы, обрабатывающей простую производственную модель с пятью переменными:

Стоимостьуслуг Стоимостьматериал а 1 Стоимость материала 2 Количество материал а 1 Количество материала 2

Ячейка В1 Ячейка В2 Ячейка ВЗ Ячейка В5 Ячейка В6

Рис. 13.1. Запрограммированные сценарии

Предположим, что компания производит изделие, для изготовления которого нужно затратить определенное количество рабочего времени и материалов. Себестоимость изделия вычисляется в ячейке В7 по формуле

=СУММПР0ИЗВ(В1:ВЗ;В4:Вб).

Отпускная цена вычисляется в ячейке В9 по формуле

=В7* (1+В8) .

Прибыль на одно изделие вычисляется в ячейке В10 по формуле

=В9-В7.

Суммарная прибыль вычисляется в ячейке В12 по формуле

=В10*В11.

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

Таблица 13.1. Возможные варианты

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

Sub Вариант 1()

Range("Стоимость услуг") = 23

Range("Стоимость материала 1"

Range("Стоимость материала 2",

Range("Количество материала 1")

Range("Количество материала 2") End Sub

Sub Вариант 2()

Range("Стоимость услуг") =29

Range("Стоимость материала 1")

Range("Стоимость материала 2")

Range("Количество материала 1")

Range("Количество материала 2") End Sub

= 62 ) = 32 = 5 = 3

= 55 = 24 = 3 = б

Sub Вариант 3()

Range("Стоимость услуг") = 31

Range("Стоимость материала 1") = 59

Range("Стоимость материала 2") = 27

Range("Количество материала 1") = 3

Range("Количество материала 2") = 4 End Sub

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

Sub Товар (а, р1, р2 As Double, nl, п2 As Integer)

Range("Стоимость услуг") = a

Range("Стоимость материала 1") = pi

Range("Стоимость материала 2") = p2

Range("Количество материала 1") = nl

Range("Количество материала 2") = п2 End Sub

Sub Вариант 1()

Товар 23, 62, 32, 5, 3 End Sub

Sub Вариант 2()

Товар 29, 55, 24, 3, б End Sub

Sub Вариант 3()

Товар 31, 59, 27, 3, 4 End Sub

Рассмотрим еще один пример составления сценариев - начисление комиссионных ([2]).

Пусть в некотором магазине продавцам начисляют комиссионные по следующему правилу:

• если объем продаж не менее 100 ООО руб., то комиссионные равны р1 процентов от объема продаж;

• если объем продаж от 50 ООО до 100 ООО руб., то комиссионные равны р2 процентов от объема продаж;

• если объем продаж менее 50 ООО руб., то комиссионные равны рЗ процентов от объема продаж.

Приведенные ниже процедуры вычисляют комиссионные для двух сценариев: р1 = 4 %, р2 = 3 %, рЗ = 2 % и р1 = 6 %, р2 = 4 %, рЗ = 2%(рис. 13.2),