Реферат: Решение экономических задач с помощью VBA

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

2.3.4 Модель управления запасами

   Вводим исходные значения , т.е. значения покупки продавцом журналов, продажи этих журналов и возврата в типографию в случае не реализации товара.   Ввод всего этого производится в диалоговом окне, которое создается как UserForm со специальными кнопками и полями ввода покупки журналов, продажи, и возврата к типографию. Окно ввода выглядит так:

    Составляем таблицу состоящую из обьема реализации, числа событий, и вероятности этих событий, первые два нам даны  по условию а вероятность этих событий нужно посчитать. Вводим в ячейку D7 следующую формулу вычисления вероятностей {=D6/СУММ($D$5:$I$5)}

и растягиваем маркер до ячейки I7.

    В ячейках C10:H15 спомощью ф-ции пользователя CALC Вычисляем финансовые исходы при всевозможных вариантых событий покупки журналов и их реализации          

Function CALC(buy As Variant) As Variant

Dim Цена_продажы, Цена_покупки, Цена_возврата, NRows, i, j As Integer, Result() As Integer

NRows = buy.Rows.Count

Цена_продажы = Range("a2").Value

Цена_покупки = Range("b2").Value

Цена_возврата = Range("c2").Value

ReDim Result(NRows, NRows)

For i = 1 To NRows

For j = 1 To NRows

If i <= j Then Result(i, j) = buy(i) * (Цена_продажы - Цена_покупки)

If i > j Then Result(i, j) = buy(j) * (Цена_продажы - Цена_покупки) - (buy(i) - buy(j)) * (Цена_покупки - Цена_возврата)

Next j

Next i

CALC = Result

End Function

   В ячейках J11:J16 с помощью формулы {=МУМНОЖ(C10:H15;ТРАНСП(D7:I7))} находим ожидаемую прибыль, соответсввующую различным вариантам покупки журналов.

    В ячейке F16 спомощью формулы             =НАИБОЛЬШИЙ(J11:J16;1)

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

              =Макс(J11:J16)      

    В ячейке F17 по формуле =(ПОИСКПОЗ(НАИБОЛЬШИЙ(J11:J16;1);J11:J16;0)-1)*5

соответствующий оптимальный обьем покупок газет. Затем функция CALC выводит эти оптимальные значения в окне сообщений.

    Ф-ция наибольший возвращает К-е  наибольшее значение из множества данных . Эта ф-ция используется для того чтобы выбрать значение по его относительному местоположению. Например, фунуцию НАИБОЛЬШИЙ можно использовать для того чтобы определить наилучший, второй, третий результат в балах, показанный при тестировании. Систаксис программы такой:

      НАИБОЛЬШИЙ(массив;К) где Массив – это массив или диапазон ячеек где определяется наибольшее значение, к – позиция  (начиная с наибольшей) в массиве или диапазоне.

     Все результаты занесенные в таблицу будут выглядеть следующим образом:

2.3.5 Определение оптимальных капиталовложений

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

   Для дальнейшего решения задачи, вводим следующие обозначения:

   Пусть R(i,j) – прибыль получаемая от вложения i млн. грв. В j-тый филиал, где в соотв. С вариантом i от (0,7), а j от (0,6)

   F(A,1,2) – оптимальное распределение средств, когда А млн. грв. вкладываются в 1,2 филиалы вместе 

   F(A,1,2,3) – оптимальное распределение средств, когда А млн. грв. вкладываются в 1,2,3 филиалы вместе 

   F(A,1,2,3,4) – оптимальное распределение средств, когда А млн. грв. вкладываются в 1,2,3,4 филиалы вместе. 

   F(A,1,2,3,4,5) – оптимальное распределение средств, когда А млн. грв. вкладываются в 1,2,3,4,5 филиалы вместе. 

   F(A,1,2,3,4,5,6) – оптимальное распределение средств, когда А млн. грв. вкладываются в 1,2,3,4,5 филиалы вместе. 

    Значения I при которых достигается максимум определяют оптимальные капиталовложения в филиалы.

   Максимальные значения ожидаемых прибылей вычисляется в программе и заносится в ячейки H4:L11 и будет выглядеть следующим образом:

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

    В диапазон ячеек (B14:K22) выводится оптимальное распределение капиталовложений по филиалам. После вычислений можно увидеть что максимальныя ожидаемая прибыль составляет 1,01 млн. грв. , из таблицы видны следующие рез-ты:

     6 филиал – 2 млн.

     5 филиал – 1 млн.

 4 филиал – 1 млн.

     3 филиал – 1 млн.

     2 филиал – 1 млн.

     1 филиал – 1 млн.

   Сама таблица выглядит следующим образом:

2.3.6 Задание на нахождение оптимального раскроя

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

Например по условию в соответствии с вариантом стандартная длина раскроя равна 28 метров,

т.е. первый вариант раскроя будет сосотавлять 0 рулон дляной 4 м, 0 рулонов длиной 6м и 4 рулона длиной 9 м, рулонов длиной 11 м. не будет, что в сумме даст 27, следовательно отходы будут составлять 1 метр. Второй вариант когда 1 рулон по 6 м и два по 11 м, в этом случае остатков не будет и т.д. Всего получается 19 вариантов раскроя.

В программе это будет выглядеть таким образом:

l = 28

a1 = 4: a2 = 6

a3 = 9: a4 = 11

r = 4

m = Application.Min(a1, a2, a3, a4)

t = Application.Floor(l / m, 1)

For i1 = 0 To t

For i2 = 0 To t

For i3 = 0 To t

For i4 = 0 To t

s = 28 - a1 * i1 - a2 * i2 - a3 * i3 - a4 * i4

If s >= 0 And s < m Then

Cells(r, 1).Value = r - 3

Cells(r, 2).Value = i1

Cells(r, 3).Value = i2

Cells(r, 4).Value = i3

Cells(r, 5).Value = i4

Cells(r, 6).Value = s

r = r + 1

End If

Next i4

Next i3

Next i2

Next i1

На листе это будет выглядеть так:

Страницы: 1, 2, 3