Changing Time Scale from Quarterly to Monthly in existing module

[PrevContributor]

Hello, When we built a model recently it was all setup with Quarterly data. Now the business is going to change to monthly data. So I need to change my module to include the months. When I do this I still have the quarterly totals, but now I would like to basically take that quarterly total and divide it by 3 or put 3 equal amounts into each month to add up to the quarter. Is there an easy way to do this?

[cecilia_peterss]

Hi Brett, I'm not sure how you set your module to quarterly as I can only see Year/Month/Day in the Line Item Time Scale (would be interesting to find out), but I've built a small scenario to transfer year to month that you should be able to use for quarters instead. I first created a simple yearly module to simulate the old setup and then a monthly module with the same line items as the yearly one. (A bit of a pain if you have many line items, but you should be able to export your old module to Excel and import or copy the line items to your new module.) Then I wrote a formula that would pick up the aggregated value from the old model and divide by 3. As you can see, I've written it for Jan, Feb and Mar and then been lazy and just set one Else, but of course you would retrieve different source quarters for Apr-Dec as well. Hope it helps! /Cecilia

[PrevContributor · Accepted solution]

Hello Brett, I think an easier way to do this would be the following. Instead of nesting ifs, I'd add two line items:

• First line item: time period formatted (quarter). This one would pick up the parent of the month.
• Second line item: number formatted. This line item would contain the formula to make the split from each quarter to the corresponding months.

This way, there's no need to create long formulas, and the split is automatically calculated for existing and future time periods.