Как представить бизнес-правила в финансовых моделях Excel?

Я создаю «однопериодную» финансовую модель в Excel, то есть модель, в которой формулы согласованы в каждом столбце, поэтому вы можете копировать / вставлять / расширять их по своему усмотрению.

Я разбиваю предположения модели на отдельный лист. Например, у меня есть ячейка с именем monthly_sales; именно столько новых клиентов я добавляю каждый месяц. Формулы тогда выглядят так:

Лист допущений 1.0

      A             B                  
1 | Monthly sales | 6                 |

Лист прогнозов 1.0

     A               B     C                   D                   E                  
1 |               |     | August 2009       | September 2009    | October 2009
2 | # Customers   |     | =B2+monthly_sales | =C2+monthly_sales | =D2+monthly_sales

Итак, в августе у меня будет 6 клиентов; в сентябре 12 заказчиков; в октябре 18 заказчиков. Все идет нормально. Но моим первым клиентам потребуется больше ресурсов для разработки, верно? Я лучше добавлю некоторые ограничения.

Лист допущений 2.0

      A                       B                  
1 | Monthly sales           | 6                 |
2 | Early customers         | 3                 |
3 | Early dev cycle, months | 2                 |

На английском языке: разработка каждого из первых трех клиентов займет 2 месяца. С августа по ноябрь у меня 1 клиент. В декабре добавляю второго покупателя, а в апреле - третьего. К августу 2010 года я прохожу early_customers, а затем могу начать расти на 6 в месяц.

Я знаю, как это сделать с помощью VBA; Я могу написать определяемую пользователем функцию, которая проверяет предыдущие месяцы, и меняю формулу # Customers на что-то вроде

=B2+min(max_customers_this_month(),monthly_sales)

Но я подозреваю, что есть способ декларативно представить эту концепцию в правильной формуле Excel. Здесь?

Jay Levitt 04.08.2009 источник

comment

Кажется, есть несоответствие между циклом ранней разработки в ваших выборочных данных (2) и циклом, описанным в вашем повествовании (4). - Isaac Moses 05.08.2009

Ответы (1)

arrow_upward
1
arrow_downward

Возможно, есть более лаконичный способ сделать это, но вот решение. Введите 1 в B2 и следующее в C2 (и скопируйте в следующие столбцы):

=IF(
    B2 > Early_customers,
    B2 + Monthly_sales,
    IF(
       OR(
          C1 - $B1 < Early_dev_cycle,
          OFFSET(B2, 0, MAX(-Early_dev_cycle + 1, $B1 - B1)) <> B2 
         ),
       B2,
       B2 + IF(
               B2 < Early_customers,
               1,
               Monthly_sales
              )
      )
   )

Обратите внимание, что это решение предполагает, что строка 1 заполнена числами месяцев, а не датами. Чтобы использовать даты, вам придется использовать что-то более сложное, чем простое вычитание, для двух вычитаний в предложении OR, которые включают $ B1.

Isaac Moses 05.08.2009