1

I am working on a financial model that contains:

  • Months in columns (Jan-2020 to Dec-2025)
  • Number of subscribers per month (column) per package in rows
  • Price of each package in rows

Doing cash flow is easy as [monthly subscribers (column) x per package (row) x package price (row), however, I am stuck in doing income statement because the subscription revenue should be spread over package tenure (which is 2 years).

see example worksheet

In this example, contract starting in Jan should be divided by 24 months then topped up with the contract in Feb and so on... this should continue until contract starting in Jan reaches 24th month.

It seems revenue calculation for each month and package is only possible using an array function but I can't figure it how.

Thank you.

Máté Juhász
  • 22,071
Tee
  • 33
  • 6

2 Answers2

1

@Tee, in case you are still around, I'm posting an answer to your question. It took quite a while to clearly understand the problem you're facing, and I'm still not sure I have it exactly correct.

So let me state the problem that I have solved, and I hope to give you enough information to modify the solution if my understanding of your problem is incorrect.

Problem: You want to calculate a running sum of 35/36ths of each of the numbers that start in G17 of your spreadsheet and continue beyond AP17. The tricky part is that once there are 35 terms in your sum, the start of the range must move to the right (i.e H17, I17 etc.), as the formula is filled to the right.

The following discussion will show how to calculate the sum, and the final formula will be multiplied by 35 and divided by 36.

Solution: To calculate the sum, a formula like this is required:

=SUM(INDEX(reference,row_num,[column_num]):INDEX(reference,row_num,[column_num])

The "reference" form of INDEX() can be used to return a cell reference, and here, the first INDEX() calculates the start of the range to be summed, while the second INDEX() calculates the end of the range.

The sum starts with G17 (column 7), for all columns less than column AP (column 42). Beginning with column AP, the starting cell moves one column to the right as the formula is filled to the right. So the first INDEX() is:

INDEX($17:$17,1,IF(COLUMN()<42,7,COLUMN()-34))

As an example, in column AP, the sum range starts with H17. Column 42-34 = 8 = column H.

The end of the range to be summed is just the current column. So the second INDEX() is:

INDEX($17:$17,1,COLUMN())

Now the sum is:

SUM(INDEX($17:$17,1,IF(COLUMN()<42,7,COLUMN()-34)):INDEX($17:$17,1,COLUMN()))

And the final formula is:

=35*(SUM(INDEX($17:$17,1,IF(COLUMN()<42,7,COLUMN()-34)):INDEX($17:$17,1,COLUMN())))/36

A portion of your spreadsheet with the calculation is shown in the picture below. Please comment if you are still visiting here. Best regards.

enter image description here

Bandersnatch
  • 3,641
0

You can do it in two steps:

  1. First you calculate New monthly income for each Cash received in your example with simple dividing with 24. For example:

    a. Put =D14/24 in D19 (new monthly income for Package 1 sales from Jan)

    b. Copy this formula to D20:D21 (packages 2 and 3) and then to all other columns from E19:E21 onward

  2. Second, you sum up all New monthly income but up to 24 months back.

    a. Put =SUM($D19:D19) in D24

    b. Copy this formula to E24:AA24 (2nd to 24th month)

    c. In AA24, remove $ sign to get formula: =SUM(D19:AA19), and then copy it to AB24 onwards. Now it is fixed to sum last 24 months.

    d. Copy row 24 to rows 25 and 26 (to get sums for packages 2 and 3)

Here are pictures of this solution: picture_of_solution picture2 picture3

Mario J.
  • 36