A simple Phasing Template that lets you phase whole Integer values by percentage (%) across 12 Weeks and prevent loss of values by implementing a Largest Remainder method. You can Copy & Paste the data into a Table which will expand all of the Formula accordingly and calculate the results. The results can then be copied out directly from the template with the option to copy zero or blank Cells

For an example consider you have a Product that you want to add Market Intelligence to of 49 (the first Product in the image above). You want to phase this value by Phasing '35%, 25%, 20%, 10%, 5%, 3%, 2%, 1%' across 8 Weeks, so you do a normal calculation that results in '17, 12, 10, 5, 2, 1, 1' (after rounding whole integers) which gives 7 weeks of values but has a loss of 1. Doing the same using my Phasing Template results in '17, 12, 10, 5, 2, 1, 1, 1'. At first glance you may consider that it has merely added a '1' to the last Week but this is not the case

Looking at the second Product with a value of 21, normal Phasing gives you '7, 5, 4, 2, 1, 1, 0, 0' with a loss again of '1' whole number, whereas my Phasing Template gives the Largest Remainder back to the first number '7' to give you a Phasing of '8, 5, 4, 2, 1, 1' meaning the last 2 Weeks receive no values, similar to the normal Roundiing, but overall you can reach the conclusion the result is better and the data values still SUM to the amount that was Phased. It does this by working out the 'Total Errors' calculated which were '2' (the difference in the SUM of all of the Integer Phasing parts '19' compared to the SUM of the original data value '21'). Then it calculates the Remainder parts, giving the 2 Largest Remainder parts (the '2' Errors) back to their respective Whole Integers parts - for reference the 2 Largest Remainders are '0.35' and '0.53' which are given back to the first and sixth Integer parts, the '7' and '0' making them '8' and '1'

Excel 2010 or Excel 2013

Features include:
No loss of values due to rounding
By dynamically expanding the Table upon Pasting the data, means no dragging down of any Formula
3 Columns are allowed in case you want to Paste up to 3 levels of data, for example, Product by Location by Customer Planning Group
The Worksheet can easily be incorpurated into existing Workbooks to do the Phasing
No VBA, just Formula

Use the links above to purchase Phasing Template or read online documentation and watch a video of Phasing Template on YouTube. You can also Share this article to Twitter, Facebook or Google+ using the links at the bottom of this page