Forecast with Multiple Arrays

[rhoneyman]

I'm trying to find an elegant way of developing a discrete forecast of future sales for a startup. There are three factors:

- Addition of distributors over time (say, one per period for x periods)
- Addition of resellers by each new distributor over y periods
- Sales of units by by each new reseller over z periods

Ex.

New distributors added per period
M1 - 1
M4 - 1
M6 - 1
M7 - 1
M8 - 2

New resellers added per period by each distributor
M1 - 1
M3 - 2
M4 - 3
M5 - 6
M7 - 4

Units sales ramp by new resellers
M1 - 1
M2 - 4
M3 - 8
M4 - 20
M5 - 30

I can muscle my way to a result, but it's tedious, time-consuming, and ugly. Is there some way of running such calculations without putting together separate x by x grids? If this has been answered elsewhere, please point the direction. I really don't know how to describe this in a search. Thanks.

[MrShorty]

I can muscle my way to a result, but it's tedious, time-consuming, and ugly. Is there some way of running such calculations without putting together separate x by x grids?

Short but useless answer -- Yes, I am sure it is possible.

In order to begin to help with the programming in more detail, we probably need a better idea on how you "muscle your way to a result" -- in other words, the algorithm you are using to perform this forecast. Are you using a simple linear regression type algorithm (that can be computed in Excel using the LINEST() function)? Or are you using some more elaborate regression/forecasting algorithms?

[rhoneyman]

I've attached a file that shows what I'm trying to do.

[MrShorty]

A worked example would help us help you. It also might be nice to see how you have been doing it in the past to illustrate what constitutes a "time consuming, tedious, and ugly" algorithm so we have a better idea where to suggest improvements.

Are you familiar with the SUMPRODUCT() function? https://support.office.com/en-us/art...c-4d2145a2fd2e I am not good at nesting multiple levels of functions together, but I expect that the programming heart of this problem is going to be the SUMPRODUCT() function.

The other half of developing the algorithm is figuring this out algebraically. I started by observing that the first row simplifies to =a1*b1*sum(ci) for i=1 to n
row 2: =a1*b2*sum(ci) for i=1 to n-1
on down to: a1*bn*sum(ci) for i=1 to 1

I then notice that, if I had the ci's inverted (so cn is in column C and c1 is in column H -- maybe in row 4), then I could add a simple =SUM($C4:C4) into row 5 (copy across -- note the mix of relative and absolute references to make copying easy), and get all of those different summations (which all seem to show up in the a(j) calculations below the a1 calculations). (To simplify later description, I called this array of partial sums di)

Now, looking at all of the a1 calculations, it looks like the a1 total should be something like =a1*sumproduct(bi,di) (remember that di represents the partial sums of the ci's)

A similar analysis of the a2 computation concludes that the a2 computation =a2*sumproduct(bi,d(i+1)) where the bi is the same array as before, but the di array shifts to the right with the ai value.
This suggests that, for any aj, the group of aj computations will simplify down to =aj*sumproduct(bi,d(i+j-1))

If I had a helper row with the sumproduct(bi,d(i+j-1)) calculations (and call them ei), then it looks like the entire calculation will become another sumproduct =sumproduct(ai,ei). We are down to row 6, if we put the partial sums of ci in row 5. Something like =SUMPRODUCT($C2:$I2,C5:I5) (again note the combination of relative and absolute references), should provide an array of these ei values. Then, the final formula becomes =SUMPRODUCT(C1:I1,C6:I6) (I left the references relative, because I don't know what we would want the copy behavior of this to be).

I don't have a worked example to test on, so I am not certain that all of that exactly matches your calculations, but it should illustrate the use of the SUMPRODUCT() function and how it is at the heart of the calculation. I don't know if that is less tedious or less ugly than what you have been doing, but it should be less time consuming (once all of the details are worked out -- especially if I missed something in the development).

[rhoneyman]

I've attached the actual case.

[MrShorty]

It's not obvious to me which values in this sheet are the ai's, bi's and ci's, nor can I identify the final calculation. What are we supposed to see in this latest example? What are the "ugly, time consuming, and tedious" parts that you are seeking to improve on?

[rhoneyman]

The end result is the line "Units." The source assumptions/series are Distributor ramp, Reseller ramp, and Units per reseller (rows 24 - 26). The first set of calculations take place in the array defined by N96:BU155. This section calculates the number of units generated by a single distributor and is the product of Reseller ramp and Units per reseller. The first row calculates the first cell in the Reseller series (N25) by the Units series (N26 through BU26). The second row calculates the second cell in the Reseller series (O25) by the Unit series. As this is a five year monthly ramp, the resulting array is 60X60.

The result I need is in row 95. I call this the Unit ramp per distributor. This feeds into the array above it in my spreadsheet defined by N32:BU91. This array is calculated similarly to the previously described array, and results in Units sold through the distributor ramp. The first row multiplies the first cell in the Distributor series (N24) by the Unit ramp per distributor series (cells N95 through BU95). The second row uses the second month's value in the Distributor ramp (cell O24) by the Unit ramp per distributor.

The columnar sum of each month yields the monthly units forecast.

The (to me) ugly, time consuming, tedious parts are the 60X60 arrays that are built a row at a time (not really all the time consuming, but hey). Managing absolute and relative references ($ or no $) makes copying and pasting trivial. But it seems to me there must be a more elegant/interesting approach. Perhaps not. Th

The last array on the sheet is a similar calculation to develop the number of resellers and is only used for reporting purposes, and is not a driver in the financial forecast. This case is similar to the development of the total units forecast, replacing the Units ramp per distributor row with the Reseller ramp series.

I hope this is clear.

[MrShorty]

The (to me) ugly, time consuming, tedious parts are the 60X60 arrays that are built a row at a time (not really all the time consuming, but hey). Managing absolute and relative references ($ or no $) makes copying and pasting trivial.

The thing I was trying to do in mentioning and describing the SUMPRODUCT() is that it seems to me that a well designed SUMPRODUCT() function should be able to eliminate those 60x60 blocks of cells where you are finding the individual products of individual elements. The goal of this part of the programming exercise is to figure out how to replace this 60x60 block with a row of SUMPRODUCT() functions.
The next thing I noticed is that the rows under row 95 are simple bi*cj functions (if c were inverted/read from right to left instead of left to right). So, I added a row to invert row 26. In N27, I entered =INDEX($N$26:$B$26,COLUMNS($N$26:N$26)) and copied across.
Looking at BU96:BU155, I saw that BU95 should be a simple =SUMPRODUCT($N$25:$BU$25,$N$27:$BU$27) (row 27 has my inversion of row 26). Looking at how this should copy across, I noticed that the "movement" through row 27 would be "backwards", so i decided to use an OFFSET() function for the second array. OFFSET($M$27,0,COLUMNS(BU$27:$BU$27),60) should return the correct array for each column. Nest them together =SUMPRODUCT($N$25:$BU$25,OFFSET($M$27,0,COLUMNS(BU$27:$BU$27),60)) copied across (in row 94 so I could compare to row 95). This formula appears to replicate row 95, without needing the rows 96:155 beneath it. I expect that, if I thought about it further, I could see a way to do this without the OFFSET() function.

I did not finish the task, but it looks like row 31 should be a similar SUMPRODUCT() function, which should allow you to get rid of the 60x60 block beneath row 31. That should eliminate 2 sets of 60x60 blocks and reduce to two rows.

I don't know if that is classified as more elegant or interesting.

[rhoneyman]

Thanks. I'll take a look at it in the morning.