如何在 Excel 财务模型中表示业务规则？

发布于 2024-07-30 01:14:48 字数 1322 浏览 2 评论 0原文

我正在 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

因此，在 8 月份，我将有 6 个客户； 9月，12位客户； 10月份，18个客户。到目前为止，一切都很好。但我最早的客户将需要更多的开发资源，对吧？我最好添加一些限制。

假设表 2.0

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

英文：前 3 个客户各需要 2 个月的开发时间。从8月到11月，我有1个客户。十二月，我添加了第二个客户，四月，添加了第三个客户。到 2010 年 8 月，我已经过了 early_customers 阶段，然后我就可以开始以每月 6 的速度增长。

我知道如何用 VBA 做到这一点；我可以编写一个用户定义的函数来检查前几个月，然后将 # Customers 公式更改为类似“

=B2+min(max_customers_this_month(),monthly_sales)

但我怀疑有某种方法可以在正确的 Excel 公式中以声明方式表示这个概念”。有没有？

原文

I'm creating a "one-period" financial model in Excel - that is, a model where the formulas are consistent in each column, so you can copy/paste/extend them as desired.

I'm breaking out the model's assumptions into a separate sheet. For instance, I have a cell named monthly_sales; that's how many new customers I add each month. The formulas then look like this:

Assumptions sheet 1.0

      A             B                  
1 | Monthly sales | 6                 |

Projections sheet 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

So in August, I'll have 6 customers; in September, 12 customers; in October, 18 customers. So far, so good. But my earliest customers will need more development resources, right? I'd better add some limits.

Assumptions sheet 2.0

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

In English: The first 3 customers will each take 2 months of development time. From August to November, I have 1 customer. In December, I add a second customer, and in April, a third. By August 2010, I'm through the early_customers, and then I can start growing by 6 per month.

I know how to do this with VBA; I can write a user-defined function that checks previous months, and I change the # Customers formula to something like

=B2+min(max_customers_this_month(),monthly_sales)

But I suspect there's some way to represent this concept declaratively in a proper Excel formula. Is there?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

茶底世界 2024-08-06 01:14:48

可能有一种更简洁的方法可以做到这一点，但这里有一个解决方案。在 B2 中输入 1，在 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 行填充了月份数字而不是日期。要使用日期，您必须对涉及 $B1 的 OR 子句中的两个减法使用比简单减法更复杂的方法。

There's probably a more concise way to do this, but here's a solution. Enter 1 into B2 and the following into C2 (and copy into the following columns):

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

Note that this solution assumes that Row 1 is populated with numbers for the months instead of with dates. To use dates, you'll have to use something more sophisticated than simple subtraction for the two subtractions in the OR clause that involve $B1.

回复收藏 0 原文

~没有更多了~