Planning Analytics

 View Only

Tips and tricks: The power of data spreading

By Trish Smith posted Tue July 10, 2018 11:41 AM


One of the most powerful and complex features of TM1® is data spreading. You can apply predefined data spreading methods to distribute numeric data to cells in a cube view. Presto! you've written large amounts of data into your TM1 database in the blink of an eye. You can also use data entry shortcuts directly in the cell that you are editing for more magic in Planning Analytics Workspace!

One of the most common ways to begin a new plan is to base it on a previous plan. For example, when you are projecting expenses based on last year's actual results, you can use data spreading to move last year's results, plus a small percentage, into this year's projected budget. Or if you are introducing a new product, you may already have an idea of a similar product whose sales pattern it might match. This technique gives you a baseline for projecting expenses for the next fiscal period.

Let's try this out by right-clicking a cell or range of cells and then clicking Spread data.

In this example, we’ve introduced a new Phablet. We have an idea of how many we’d like to sell and how those sales should be distributed over the various sales channels and geographies as well as over time. Entering the values manually would be time consuming and error prone, so we’ll use a spreading method, selecting the yearly total and choosing Spread data from the context menu.

Screen shot showing the Spread data option.

In this case, we believe that the new Phablet will have a sales pattern similar to the 3G 64Gb phone. This is the relative part of Relative proportional spread. In this case, we’re basing our new product on an existing product’s Budget for the same year. Another common technique is to base a plan on the Actual for a Previous year.


While we expect the new Phablet to sell in the same proportions in each geography and channel and follow the same pattern over time, we only expect to sell half as many. That’s the proportional part of this spreading technique. So while the reference value, the value for the 4G phone is 10,000, we’ll enter 5,000 as our expected total for the Phablet. In this case, there are no existing numbers so we’ll keep the default and replace. There are situations where you want to add or subtract this spread total to existing values.


Click Apply and with one action we’ve set the budget for an entire year across multiple channels and locations.


Relative proportional spread is just one of the many spreading methods available in Planning Analytics. Other methods include:

Don't worry, you can edit TM1 data by using data spreading in Planning Analytics for Microsoft Excel too.

And if you are an expert, you could try data spreading with the TM1 REST APIs. Spread the magic!