Geek Documentation Logo

Phasing Template

Version 1


Introduction


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

  1. Excel 2010, 2013 & 2016 (32bit or 64bit)
  2. Basic Excel skills
  3. Windows PC · NOT a Mac!

No support is provided for customization or development of this Software






About Phasing Template #back to top

Phasing Template 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

Phasing Template Images

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 PhasingTemplate.zip File and open the 'PhasingTemplate.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. You can easily combine this into your own Worksheets or Projects - simply copy in the Worksheet

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

Phasing Template Example Image 1000px x 654px

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)

Phasing Worksheete Example Image 1000px x 654px




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 Number
Contact 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

04.10.2017 - (Version 1) released