Phasing Templates
Version 2
Introduction
- Purchase : Phasing Templates
- Software Name : Phasing Templates · Phase Integer Values across 12 Weeks using a Largest Remainder Method. Split Monthly buckets into Weekly buckets
- Software Version : v 2
- Website : airportal.sellfy.store/
If you have already purchased this Software, let me take a moment to thank you for being a loyal customer
You are entitled to free lifetime updates for ALL future builds
This documentation is to help you understand the Software and to give you a more meaningful insight into what it can do. Please go through the documentation and read it carefully · Basic Excel skills will be required
Requirements
You will need the following Excel Version & Prerequisite to use this Software
- Excel 2010, 2013 & 2016 (32bit or 64bit)
- Basic Excel skills
- Windows PC · NOT a Mac!
No support is provided for customization or development of this Software
About Phasing Templates #back to top
Phase and split buckets of data: 2 Phasing Templates will phase Integer values using Formula across 12 Weeks and prevent loss of Integers by implementing a Largest Remainder method. You can Copy & Paste the data into a Table which will dynamically expand all of the Formula. You can Phase each Week by a Fraction ie. 23.7% and omit Weeks if you want. The Formula will then calculate the results ensuring that only whole Integer values are returned. The results can be copied out directly from the template with the option to copy zero or blank Cells. This template can be useful when Phasing Promotional Volumes
Phase and split Monthly buckets of data into Weekly buckets of data: The Month Into Week Split Template willl split Monthly buckets of data into Weekly buckets by apportioning amounts that lay between 2 Months into the respective ISO Weeks by a daily pro-rata, dividing the Month total by the number of days in the Month. There are 2 example Worksheets, the first with a classic layout for a single set of Monthly data and the second for a more useful cascading horizontal layout that you can add into your projects. The concept and formula were built upon from ALphaFrog. You can see his work here: converting-data-from-monthly-to-weekly
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
- * An additional Phasing Worksheet is included with the download that has all of the Formula but doesn't use a Table
Getting Started #back to top
The download Zip File
Unzip the PhasingTemplates.zip File and open the 'Phasing Template.xlsx' File, Delete all of the Rows apart from the first 2 Rows in the Table (Important!). Copy & Paste your data to be split including one or more Headers (up to 3 max) and the values to be split into Columns "B:E". If you enter one line at a time scroll to the right and in the last Table Cell press the {TAB} Key to begin a new line in the Table. Adjust the Phasing percentages (%) at the top of the Table to change how the Phasing is spread
For clarity, the data to add should be copied into Columns "B:D" and the actual values to be Phased should be copied into Column "E". The Formula starts in Column "F". The final values are detailed in Columns "BF:BQ" - these can be copied including any zeros or without zeros
An additional File is included with the download that has all of the Formula but doesn't use a Table. This is called 'Phasing Worksheet.xlsx'. You can easily combine this into your own Worksheets or Projects - simply copy in the Worksheet
A File called 'Month Into Week Split Template.xlsx' can be used to implement a Weekly split of Monthly data without loss of values across the Year
The Phasing Headers #back to top
The Phasing Headers are in Columns "F:Q". You can edit these to build your own Phasing. You can omit any of the Headers. In order for the Formula to work correctly the Phasing Headers must add up to 100. A message will appear in Cell "BC3" if the Phasing Headers do NOT add up to 100
Copying the Results #back to top
The results can be copied from Columns "BF:BQ". You can copy the values and then Paste them as Special values. You can copy the values including the zeros or you can omit the zeros by toggling the "TRUE", "FALSE" value in Cell "BI2". If you include the zeros, then any data that you copy will include zeros as Blank results
How it all Works #back to top
Example
For an example consider you have a Product that you want to add Market Intelligence to of 49. 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 another 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'
You can see how it all works by examining the Formula
Screen Shots #back to top
This is a Screen Shot of the Phasing Template reduced to 2 Table Rows
I Phased '999' and '998' across 8 Weeks as 35%, 25%, 20%, 10%, 5%, 2.5%, 1.5% & 1% - you can see the results below
This is a Screen Shot of the Phasing Worksheet phasing lots of different values for a range of Products
I Phased these values across 4 Weeks as 35%, 25%, 20% & 20% - you can see the results below. I also set 'Display Zero as Blank' to 'FALSE', meaning that when I Copy and Paste the data as Special Values, the data will include Zeros (0)
FAQ #back to top
My Formula is giving me a #NUM! Error?
This is more than likely due to the fact that you deleted all but 1 of the Rows in the Table - the Array Formula in the hidden Columns "AQ:BB" needs at least 2 Rows in order to work correctly otherwise it may break when new Table Rows are added using the {TAB} Key. Revert to a backup Copy of the Phasing Template or use the Phasing Worksheet which doesn't use Table Formula
Support #back to top
Please remember you have purchased very affordable Software and you have not paid for a full-time Software design agency - I am but one man. Occasionally I may help with small tweaks, but these requests will be put on a much lower priority due to their nature. You have not PAID for Support, Support is 100% optional and I provide it for your convenience, so please be patient, polite and respectful
Support (limited) for my Software includes:
* Responding to questions or problems regarding the Software and its features* Fixing valid (replicated) bugs and reported issues for the VERSION I HAVE WRITTEN
Software support does not include:
* Customization and installation services* Support for third party software or ANY kind of development whatsoever
Before seeking support, please...
* Make sure your question is a valid Software Issue and not a customization request* Make sure you have read through the documentation and any related video guides before asking support on how to accomplish a task
* Make sure to double check the Software FAQs or online documentation
* Ensure that you access to the VBOM is allowed and that Macros can run in Excel
* Make sure to provide 'proof of purchase' and state the name / version of the Software that you are having issues with when requesting support by Email or via Facebook
How to get Support
Contact Mark Kubiszyn on the Email address provided when you purchased the Software, including the Order NumberContact Kubiszyn.co.uk via our Facebook Page - remember to be patient, if there has been an issue with your download, I will always respond within 48 hours and will Email you the File directly if neccessary or via Messanger. For other issues the response time may be considerably longer and I may choose to respond to specific questions only (as is my right), depending on what has been asked
Version History (Changelog) #back to top
You can find the version history in the Code Module for any Macro-enabled Software or read more information below. The latest Version is always shown first
Changelog
19.02.2021 - (Version 1.1). Added in a Template to perform Monthly data split into Weekly data
04.10.2017 - (Version 1) released