Introduction

Squid_v3

A Multi-period Seasonal Forecasting Tool for Excel 2013/2016 (32bit & 64bit)

Squid_v3 is built upon 2 earlier versions of a Mothly Forecasting tool for S&OP. Squid provides a simple, Seasonal 2-Year Forecast by Business Quarter, Month and Week. Each period is interchangeable using Linked Tables. The data used is stored and updated using SQL Batch Updates that can be configured on the 4.0 Time Series Updating Section on the Options Sheet. It requires a full Historical dump of Sales data as the inital input to drive the model and then regular Quarterly, Monthly and Weekly Sales updates to derive the Trend. The Trend will be based upon a 2 Year Moving Window so as to capture recent Trends. The Trend will then be used along with the Seasonal Factors to calculate the Seasonal Forecast (SF). We want to be able to adjust the Seasonal Forecast at multiple Periods for example, we may want to lift the entire Christmas Period for the current Year or just lower the January Forecast for next Year. Supposing we Forecast Guinness Stout, then we may want to raise Week 11 even more than last Year for St. Patricks Day as we anticipate selling more Guinness this Year for this big event. We also want to be able store other Time Series data values including the Baseline Forecast as the Forecast (or Final Forecast) on a Rolling basis to analyse this against the new Baseline Forecast each Period. Other Time Series may include a Budget Sales Forecast, an S&OP Forecast or another Third Party Forecast so we will add in this capability to Squid as we go. Please Note: some Time Series may not require any Calculations - they may just be used to store data values to overlay Lines onto the Charts. Squid has some complex, bespoke, yet flexible SQL driven Reporting that can be configured in the 6.0 SQL Reporting Section on the Options Sheet. Squid also features a modified version of Msg, my alternative MsgBox Dialog Software for Excel, which displays various notifications, warnings and error messages where appropriate using CSS/Javascript/HTML

Prerequisites

  • Excel 2013/2016 Client, (32bit & 64bit) is supported
  • Moderate Excel skills. Moderate VBA Skills and SQL skills if you want to build upon my work. Please Note: Due to the very low selling Price, I CANNOT SUPPORT MODIFICATIONS OF ANY KIND
  • Please - Windows PC · NOT a Mac!!

Features

  • Multiple Time Series - Sales, Seasonal Forecast (or Stat. Forecast), Rolling Forecast, Budget, S&OP Forecast etc.
  • Fast SQL Batch Updates (automatically remove zeros when updating)
  • Multiple Linked Tables that can be Filtered using Slicers
  • Seasonal Model using average Period Sales
  • Moving Window Trend (adjustable on the Options Sheet)
  • Interchangeable Periods, Business Quarter, Month and Week
  • Configurable Options Sheet with Node Toggle (Expand & Collapse Sections), built from my Treelist Software - more info
  • Bespoke Reporting using SQL
  • Features a modified version of Msg, a MsgBox replacement for Excel using CSS/Javascript/HTML - more info

Installation

Squid_v3.xlsb comes ready with some Products and stored values to enable you to test and view the results of the Seasonal Forecasting - another Copy of Squid_v3 (Blank).xlsb come ready for you to setup yourself. You need to setup Squid to meet your own requirements as they may be slightly from mine and will certainly require you to change some Headers for the SQL Batch Updates. You may also want to rename some Table Headers too. When you are ready to begin setting up Squid for your own Forecasting, follow the 'Build Your Squid' Section, ensuring that you read all of the information. A Quick Guide Section is available before the FAQ Section for anyone wanting to dive straight in with the Blank Squid File mentioned earlier but I urge you to read through all of the online documentation beforehand to familiarise yourself with everything that Squid has to offer

Usage

The Options Sheet

The Options Sheet is built using my Treelist Software. I designed a simple 2-level Node layout using SVG Icons for the Toggle Nodes. This forms the basis of all of the Options/Info/Help/Processes for Squid. The beauty of using this type of Options layout is that it is very neat and compact. You can easily Copy & Paste additional Rows and the Code will recognise the extra Rows and Toggle them accordingly. Included in the Options Sheet are Checkbox Cells that you can Toggle On/Off by Checking/Ticking them. I will run through all of the availabe Options but first, here is a vary early Image of the Options Sheet with the start layout I designed using Treelist (Day 1 of writing the Software)

1.0 Information

This Section provides an overview of Squid and an online link to an About Page of the Author

2.0 Legend (Key Symbols, Definitions & Formatting)

This Section provides an overview of the symbols, definitions and some of the mixed Formatting used in Squid

2.1 Symbols & Definitions
This part provides details of the Symbols and Definitions used ie. Σ (Sigma Code, meaning to SUM or Total)

2.2 UI Cell Formatting
This part shows you the different Cell Formats used and explains their narative ie. Calculation Cell Format, meaning to show where a Calculation takes place

2.3 Custom Cell Formatting
This part shows you the various Custom Cell Formats (Right-click->Format Cells->Number->Custom) used throughout Squid ie. a Custom Number Format using '#,##0;-#,##0;"—"' for '1024' will give '1,024' in a Cell and for zero (0) will give '—' in a Cell

2.4 Conditional Formatting
This part shows you the type of Conditional Formatting used throughout Squid ie. a Pink Cell Fill highlights Forecast values between 0.00000000000001 and 0.99999999999999 which is useful if you are Forecasting in HL (Hectolitre) for example - usually we will be using PC or Each as the BoM (Base Unit of Measure) for this Tool

2.5 Colour Coding Sections & Parts
This part shows you the type of Colour Coding used throughout Squid for Calculations and Times Series, for example Totals on the Calculations Sheet 'QC' are shaded in Orange and Sales Cleaning on the Quarterly Sales Sheet, 'Quarter' is shaded Grey. This just allows you to focus on the relevant Section quicker and forms part of a differentation between Calculations or Time Series data values

3.0 Setup

This Section is used to enable you to set up Squid for Forecasting

3.1 Dates & Time Series Settings
This part outlines the 4-Year Settings required to drive, perform analysis and derive output in Squid, 2-Years in the past will be used for History and 2-Years in the future will be used for the Foreast. Please Note: in Total then 4 Full Years will be available in Quarters, Months and Weekly Buckets

3.2 SQL Settings
This part allows you to enter the Headers for your combinations and Select your Unique Columns using the Drop-down Combo boxes. A Maximum of 11 is allowed with the last slot being used for a New line Flag. Use a separate Master Data Sheet if you need to include more attributes as opposed to including them on the Options Sheet - then simply use an Index/Match Formula to grab them

3.3 Date & Time Series Formats (used to build the Time Series Periods used in the Update, Data Sheets and Squid Dashboard
This part is the setup for the Periods used on the Update and Data Sheets for all of the Time Series. It also includes the Prefixes used for the Time Series (these are editable, but do not change the Prefixes unless you have a sound grasp of how the Time Series are setup

3.4 Chart Series Names
This part contains all of the Editable Chart Series Names - changes below will apply to the QCS, MCS & WCS Chart Sheets. Edit the Series Name of your choice

3.5 Summary Names
This part contains all of the Editable Summary Names - changes below will apply to the Quarter, Month & Week Sheets. Edit the Summary Name of your choice

3.6 Link Quarter, Month & Week Table Field Filtering and Slicers
This part allows you to choose whether or not to link all of the Table Filtering and Slicers for Squid. The default is 1, On or Yes. Depending on the amount of data being used in Squid, you may prefer/have to turn this off by selecting zero (0), do not link Table Filtering and Slicers. When using this setting, you need to specify the Table Fields that you want to link together using a String of Comma-delimited numbers ie. 1,2,5,10 etc. These numbers enable the Code to link the relevant Table Fields/Columns together, including the Slicers attached to them. Remember, the fewer Fields/Columns/Slicers, the faster the Code will execute. If it becomes too slow to operate the linked Table Fields, Filtering and Slicing, then reduce the number of Fields being Sliced or turn the setting off completely and use the Tables without any linking

4.0 Time Series Updating

This Section is used to enable you to perform SQL Batch Updates for any of your Time Series

4.1 Toggle Time Series Updating Sheets
This part allows you to Hide/Unhide the 2 SQL Time Series Updating Sheets. Check the Checkbox to Display the Sheets or uncheck the checkbox to Hide the Sheets

4.2 Setup Time Series for an Update
This part allows you to Select the Type of Time Series that you wish to Update. Once you have Selected it ie. QuarterlySales, then the 2 Drop-down Combo Boxes will allow you to Select 2 Dates, a From Date until a To Date. This will be the Range that is presented to you on the Update Sheet and will form the Range that will ultimately be Updated with new values. These Drop-down Boxes refer to the Range for the Time Series taking the Headers for the Time Series from the Data Sheet. 3 Series are allowed without modification. Time Series are fairly easy to Setup and add

4.3 Batch Update Options
This part allows you to choose different Batch Update Options when Updating a Time Series. The first two settings allow you to choose whether to remove Negative values and/or Zero values when doing an Update. The third setting allows you to choose whether or not to convert Numeric values - sometimes for General Cell Formats, through ODBC Excel outputs an Apostrophe as a prefix before a Numeric value and this can cause issues when using SubTotals as the number is not recognised and cannot be summed. Set any ofthese Options to 1 for Yes and zero (0) for No

5.0 Rolling Processes (Quarter, Month & Week)

This Section is used to enable you to perform rolls following a Time Series update so that the Trend can be calculated for the last 2 Years by Quarter, Month and Week

5.1 Toggle Quarter, Month & Week Calculation Sheets
This part allows you to Hide/Unhide the 3 Quarter, Month & Week Calculation Sheets

5.2 Rolling the Quarter Sales to derive a Trend based upon a Moving Window
This part allows you to Select the Start Period and End Period for both Years to derive the Moving Window Trend for the Quarterly Sales. This enables us to capture and derive the Seasonal Forecast using the latest Sales data values

5.3 Rolling the Monthly Sales to derive a Trend based upon a Moving Window
This part allows you to Select the Start Period and End Period for both Years to derive the Moving Window Trend for the Monthly Sales. This enables us to capture and derive the Seasonal Forecast using the latest Sales data values

5.4 Rolling the Weekly Sales to derive a Trend based upon a Moving Window
This part allows you to Select the Start Period and End Period for both Years to derive the Moving Window Trend for the Weekly Sales. This enables us to capture and derive the Seasonal Forecast using the latest Sales data values

6.0 SQL Reporting

This Section is used to setup the SQL Headers required to extract Report data values from the Quarter, Month & Week Sheets

7.0 System

This Section is used to store System Settings and Formula. It is not a Setup Section and should not be edited by anyone

7.1 System Formula
This part contains specific System Formula used to build Squid functionality, be it through any of the Sheets or the SQL

The Sales History Dump

When you first set up Squid, you will need to do an initial Sales dump of Historic values. These can be pre-cleaned but this is not really neccessary as Squid allows both Historic and Future Cleaning of Sales, useful if your Sales updates are kept to a minum of Weeks in the past. You can and should do a Sales dump for each of the Forecasting levels, Quarter, Month and Week. This is because dissagregation from Month to Week and / or Quarter to Month ideally requires Seasonality Factors to be built in order to produce a more accurate Seasonal Forecast. Sure, you could just dissaggregate from one to another and if you were only Forecasting by say Month, then this would be fine, you could easily aggregate to Business Quarter, but we want to do a much better job than just dissaggregate or aggregate between levels. We want to derive the best Seasonal Forecasts for each of the levels and then overlay each of these over another so as to decide on which one we want to use for any combination. Doing the Sales dump sounds like a lot of work, but it is fairly straightforward and then you will only need to do regular Quarter Sales updates once every 3 Months, regular Monthly Sales updates every Month and regular Weekly Sales updates every Week. The reason I say that Cleaning is not neccesary here, is that you may want to keep updating the last 8 Weeks Sales for Week and any Cleaning would get overwritten or modified. Another Cleaning Series is added into Squid to allow both Sales and then Final Cleaned Sales (Historic and Future) to be overlaid onto the Charts. Only Final Cleaned Sales will be used for the Seasonal model, but real Sales will still be visible, sound good? Okay so let's get on with it

I am going to start with a Quarterly Sales dump as this is how I began when writing the Software. So with a Blank Data Sheet in Squid you can simply Copy & Paste in the initial Quartelry Sales dump. Make sure that all of the combination Headers are as your settings in 3.2 SQL Settings. Here are my Settings and my inital Quarterly Sales dump - you will notice that I have removed all Zeros and Negative values using CTRL+F, Find & Replace Zero with Blank and Find & Replace Negatives with Blank (-*). Sales Updates will do this automatically but History dumps should be done manually as this is a one-off exercise. I have also blurred out the Product Names



Quarter, Calcs & Chart Settings

The Quarter Sheet and the Quarter Calc or 'QC' Sheet are intrinsically linked. They both contain Tables, 'QTable' on the Quarter Sheet and 'QCTable' on the QC Sheet. The QTable contains the Sales data values and the Seasonal Forecasts (and any other required Series for the Quarter Chart). It also contains some Master Data values that formed part of the initial Sales dump by Quarter. The QCTable contains all of the calculations that are required to derive a Seasonal Forecast from the Sales data values. You have free will to change some of these Table Fields, especially for the Master Data values like Product, Category etc. or you can lookup some other Master Data values from a separate Master Data Sheet. Here is the basic Quarter Table layout used to bring in what I need for my Forecasting together with the Fields that will be used for Percentage (%) adjustments and Volume adjustments for the next 2 Year Seasonal Forecast. It consists of my Product, my Category, a New combination Indicator, a Type Flag to indicate the aggrgation/dissaggregation level to adopt (Quarter to Month, Month to Quarter etc.), an On/Off Flag to switch the Forecast on or off, a manual Trend (%), a Trend adjustment (%), the initial Trend derived by the model and then 2 Years of Percentage (%) and Volume adjustments for each Quarter. You will notice that I have Concatenated the Product and Description Fields from my Data Sheet. You will also notice that I have added a Formula Row above the Table to build my Headers so that future adjustments in the Options Sheet will pull through. I have Grouped this Row, so that you can hide it if you prefer, but it is neccessary as Table Headers cannot be Formula. The Percentage (%), Volume adjustments and the Seasonal Forecast are then Grouped using a thick Border (the second Year is Grouped so that you can toggle it in and out of view). The Calcs and Chart Settings Sheets can be hidden



The QC Sheet containing all of the Quarterly calculations is Colour-coded thus:

Totals - Orange
AVG (Averages) - Green
Trend - Blue
Period AVG (Period Averages) - Yellow
Period Factors - Grey

For the Totals, we SUM both Year 1 and Year 2 from the Quarter Sheet ie. =SUM(QTable[@[S Q1 Yr1]:[S Q4 Yr1]]). For the AVG, we Average through both Year 1 and Year 2 from the Quarter Sheet if the Seasonal Forecast is turned on. If it is turned off, then the Average is set to zero (0) ie. =IF(QTable[@[On/Off]]="On",AVERAGE(QTable[@[S Q1 Yr1]:[S Q4 Yr2]]),0). For the Trend we use the settings in 5.1 Rolling the Quarter Sales to derive a Trend based upon a Moving Window on the Options Sheet to derive the Sales Moving Window, which we divide Year 2 by Year 1, minus 1 (if an Error occurs, then this is set to zero (0)). For the Period Averages, we Average through each Quarter of Year 1 and Year 2 ie. =AVERAGE(QTable[@[S Q1 Yr1]],QTable[@[S Q1 Yr2]]) for both Q1 Periods. For the Period Factors, we divide the Period Average by the 2 Year Average Sales ie. =IFERROR([@[S Q1 ∓]]/QCTable[@[S 2Yr ∓]:[S 2Yr ∓]],0)

A number of Columns are left with Misc1, Misc2, Misc3 Headers in case you need them for anything going forward. Here is an Image of the Quarter Calculations on the QC Sheet



The QCS Sheet contains all of the values required to produce and update the Quarter Chart. The Chart Series comprose of Sales 2018, Sales 2019, Current Sales 2020, Seasonal Forecast 2020, Seasonal Forecast 2021, Rolling Forecast 2020, Budget 2020. Checkboxes are available on the Chart to Toggle some of the Series On/Off. The available Checkbox Toggle Series are Seasonal Forecast 2021, Rolling Forecast 2020, Budget 2020. Checkboxes Labels cannot have Formula connected - edit the Label Text whenever a Year changes on the Quarter Sheet. Below is an Image of the Quarter Chart Settings Sheet and the Chart that is added to the Quarter Sheet





Month, Calcs & Chart Settings (MARK TO DO !!!!!!!!!!!!)

The Month Sheet and the Month Calc or 'MC' Sheet are intrinsically linked. They both contain Tables, 'MTable' on the Month Sheet and 'MCTable' on the MC Sheet. The MTable contains the Sales data values and the Seasonal Forecasts (and any other required Series for the Month Chart). It also contains some Master Data values that formed part of the initial Sales dump by Month. The MCTable contains all of the calculations that are required to derive a Seasonal Forecast from the Sales data values. You have free will to change some of these Table Fields, especially for the Master Data values like Product, Category etc. or you can lookup some other Master Data values from a separate Master Data Sheet. Here is the basic Month Table layout used to bring in what I need for my Forecasting together with the Fields that will be used for Percentage (%) adjustments and Volume adjustments for the next 2 Year Seasonal Forecast. It consists of my Product, my Category, a New combination Indicator, a Type Flag to indicate the aggrgation/dissaggregation level to adopt (Quarter to Month, Month to Quarter etc.), an On/Off Flag to switch the Forecast on or off, a manual Trend (%), a Trend adjustment (%), the initial Trend derived by the model and then 2 Years of Percentage (%) and Volume adjustments for each Month. You will notice that I have Concatenated the Product and Description Fields from my Data Sheet. You will also notice that I have added a Formula Row above the Table to build my Headers so that future adjustments in the Options Sheet will pull through. I have Grouped this Row, so that you can hide it if you prefer, but it is neccessary as Table Headers cannot be Formula. The Percentage (%), Volume adjustments and the Seasonal Forecast are then Grouped using a thick Border (the second Year is Grouped so that you can toggle it in and out of view). The Calcs and Chart Settings Sheets can be hidden



The MC Sheet containing all of the Monthly calculations is Colour-coded thus:

Totals - Orange
AVG (Averages) - Green
Trend - Blue
Period AVG (Period Averages) - Yellow
Period Factors - Grey

For the Totals, we SUM both Year 1 and Year 2 from the Month Sheet ie. =SUM(QTable[@[S Q1 Yr1]:[S Q4 Yr1]]). For the AVG, we Average through both Year 1 and Year 2 from the Quarter Sheet if the Seasonal Forecast is turned on. If it is turned off, then the Average is set to zero (0) ie. =IF(QTable[@[On/Off]]="On",AVERAGE(QTable[@[S Q1 Yr1]:[S Q4 Yr2]]),0). For the Trend we use the settings in 5.1 Rolling the Quarter Sales to derive a Trend based upon a Moving Window on the Options Sheet to derive the Sales Moving Window, which we divide Year 2 by Year 1, minus 1 (if an Error occurs, then this is set to zero (0)). For the Period Averages, we Average through each Quarter of Year 1 and Year 2 ie. =AVERAGE(QTable[@[S Q1 Yr1]],QTable[@[S Q1 Yr2]]) for both Q1 Periods. For the Period Factors, we divide the Period Average by the 2 Year Average Sales ie. =IFERROR([@[S Q1 ∓]]/QCTable[@[S 2Yr ∓]:[S 2Yr ∓]],0)

A number of Columns are left with Misc1, Misc2, Misc3 Headers in case you need them for anything going forward. Here is an Image of the Quarter Calculations on the QC Sheet



The MCS Sheet contains all of the values required to produce and update the Month Chart. The Chart Series comprose of Sales 2018, Sales 2019, Current Sales 2020, Seasonal Forecast 2020, Seasonal Forecast 2021, Rolling Forecast 2020, Budget 2020. Checkboxes are available on the Chart to Toggle some of the Series On/Off. The available Checkbox Toggle Series are Seasonal Forecast 2021, Rolling Forecast 2020, Budget 2020. Checkboxes Labels cannot have Formula connected - edit the Label Text whenever a Year changes on the Quarter Sheet. Below is an Image of the Quarter Chart Settings Sheet and the Chart that is added to the Quarter Sheet





Seasonal Forecast Formula

The Seasonal Formula is a Table Formula built by multiplying the 2 Year Average Sales by the Period Factor for the relevant Period PLUS the Trend that is derived/chosen by the user. The Seasonal Formula also adds in any Volume and/or Percentage (%) adjustments made. If we take the Quarter Forecasting as an example, calculations are all stored on the 'QC' Sheet for the Quarter. The basis for the Calculation is as follows:

The Seasonal Forecast Formula (Yr3 and Yr4 are the next 2 Years)
=(QCTable[@[SPF Q1]]*@QCTable[@[S 2Yr ∓]:[S 2Yr ∓]]*(1+@QTable[@[Trend]:[Trend]])+[@[VQ1 Yr3]])*(1+[@[%Q1 Yr3]])

So for Year 3, 2020, as our initial first Year Seasonal Forecast, this simple Formula says take the pre-calculated Period Factor from the 'QC' Table Sheet and multiplies that by the 2 Year Average Sales on the 'QC' Table Sheet. We then raise or reduce this amount by the Trend part on the 'Quarter' Table Sheet '*(1+@QTable[@[Trend]:[Trend]])'. Then add in any Volume '+[@[VQ1 Yr3]]'. Finally we raise or reduce by a Percentage (%) calculation '*(1+[@[%Q1 Yr3]])'

The next Year, Yr4 or 2021 in our example will be built exactly the same as Yr3, 2020 using the same initial Trend or adjusted Trend. You can then choose to adjust this Year as you wish. At the moment there is no Yr2 dampener or additional Trend capability, but this is not too difficult to add in and may be addressed in future builds. I used this before, but to be honest did not find it that helpful

The Boolean 3-way Switch Logic used in Squid for Compact Multiple Formula Decisions
Squid needs to utilise the Formula above but build into it the ability to switch on the letters Q, M & W, Quarter, Month and Week. We need to do this to allow us the option to override the default Month Seasonal Forecast with a Dissaggregated Quarterly Forecast or an Aggregated Weekly Forecast. Using IF is okay, but we would like to try to keep this Formula as compact as possible. One way that I found over the Years was to use an alternative Boolean Switch (you can find this on the Excel Page of my Website). This then allows us to use the CHOOSE Function for our switching. The order of the Formula is backwards, where the last letter "W" is the first switch, the second letter "Q" is the second switch and then the default letter "M" is the third switch. So, here is the basis for the Boolean Switch Seasonal Forecast Formula - notice the Type Field is the Field containing a Drop-down Combo Box with our 3 Letters, Q, M & W and the 2 zeros (0) are Placeholders for the other 2 Formula that we will create and also that the missing Letter M for Month is always the default letter or switch decision, Nice ;)

The Seasonal Forecast Formula used on the Month Sheet
=CHOOSE((([@Type]<>"Q")*1+([@Type]<>"W")*2),0,0,(MCTable[@[SPF Jan]]*@MCTable[@[S 2Yr ∓]:[S 2Yr ∓]]*(1+@MTable[@[Trend]:[Trend]])+[@[VJan Yr3]])*(1+[@[%Jan Yr3]]))

So now that we have our Switch Formula, we can add in the Formula to take the Dissaggregated Quarterly Forecast (after any Changes) as our new Month Forecast that we can further adjust if neccessary using the Formula below - this is only chosen if we set the Type Drop-down Combo Box to 'Q' as opposed to 'M' on the Month Sheet:

The Seasonal Forecast Formula Dissaggregated Quarter Formula (second switch, third line down)
=CHOOSE(((@MTable[@[Type]:[Type]]<>"Q")*1+(@MTable[@[Type]:[Type]]<>"W")*2),
0,
([@[DQF Jan Yr3]]+[@[VJan Yr3]])*(1+[@[%Jan Yr3]]),
(MCTable[@[SPF Jan]]*@MCTable[@[S 2Yr ∓]:[S 2Yr ∓]]*(1+@MTable[@[Trend]:[Trend]])+[@[VJan Yr3]])*(1+[@[%Jan Yr3]]))

Time Series Batch Updating

All of the data storage is performed using SQL to Batch Insert/Update the data values from an Update Sheet into a Data Sheet according to a variety of Unique and abstract Fields or Attributes. A line of Unique values and Attributes is called a Combination. A Combination will be a set Row or line in the Data Sheet, that will not move after any one of your initial Sales Data dumps (Copy & Paste into the Data Sheet). An Update is really both an Insert and an Update of values. An Update will change insert/update data values in the Grouped Column Window/Range Section of the Data Sheet - a free area of temporary storage used to parse data values only in and out of the relevant Time Series. A Maximum of 106 Periods (2 Years, Weeks 1-53) which can be Quarters, Months or Weeks is set on any Update for this Grouped Column Window/Range. Whenever a New Combination is encountered based upon your choice of Unique values, then the Combination is added to the Last Row in the Data Sheet. This Row or Combination will never change. Only the Attributes of the Combination will change as they are Non-unique, hence they will get overwritten. This is useful as things like Master Data may need the latest information rather than a static dumped Text

The reason that we use a temporary storage to copy data values in and out of their relevant Time Series is due to the fact that the Excel ODBC Driver has a 255 Column limit, which is more than enough for the Quarter and Month Time Series, but not nearly enough for the Weekly Time Series. In essence we are updating the Time Series but using a Window of parsed data values so we never really exceed a Maximum of 106 Periods whenever we perform an Update

Aggregation & Dissaggregation

Squid allows Aggregation and Dissaggregation between Quarter, Month and Week for any combination. If you Forecast a combination at Quarter level and then wish to see the Seasonal Forecast dissaggregated to Month level, then Toggle the Type Field to 'Q' for the combination in the Month Sheet. The Trend will be set to zero (0) as the Trend is coming from the Final Cleaned Sales in the Quarter Sheet. Any Manual Trend adjustments or Trend Adjustments cannot be made when Aggregating or Dissagregating between Periods. You may still make further adjustments to the Percentage (%) adjustment or Volume adjustment Periods

Please Note: when using Aggregation and Dissaggregation between Quarter, Month and Week Sheets, if you attempt to use Month from Quarter, Week from Month and then Quarter from Week you will receive a Circular reference as you are trying to square a circle! Think about what you are trying to achieve. You may Forecast purely to a Sales Budget at Quarter level and just want to see the results at Month and Week, fine this can be done, you just can't make the Quarter Seasonal Forecast equal the Week Seasonal Forecast when you have already made the Week Seasonal Forecast equal the Quarter Seasonal Forecast - make sense? Here are a couple of Screen Shots to demonstrate using a Month to Week Seasonal Forecast Dissaggregation:









Filtering UDF

Squid uses a Volatile Filter UDF (User Desinged Function) to display what is currently on the Chart for the Quarter, Month and Week Sheets. The Function is in the 'mdSquid' Code Module and uses the following Formula '="Filtering: "&AutoFilteredHeaders($B$20:$D$20)'. Other versions of this Function are available - search 'Filter' key term at kubiszyn.co.uk/archive/ a 32/64 bit version is available named 'Display what is being Autofiltered (32/64bit)'

Build Your Own Squid



FAQ

Q. Can I insert extra Rows into the Quarter, Month and Week Sheets?

A. Yes, but ensure that you unhide the 3 Calculation Sheets and then Group them all together so that the first Row of the Formula, Table Headers and values are all aligned. Now insert any extra Rows


Q. How can I Hide and Unhide the Time Series Update and Calculations Sheets quickly?

A. You can Toggle both the Time Series Updating Sheets and the Calculations Sheets quickly in the Options Sheet. To toggle the Time Series Update Sheets go to 4.1 Toggle Time Series Updating Sheets and Check/UnCheck the Checkbox. To toggle the Calculations Sheets go to 5.1 Toggle Quarter, Month & Week Calculation Sheets and Check/UnCheck the Checkbox


Q. Can I clean my Sales in the past and future?

A. Yes, you can clean both past and future Sales. Cleaning the past will have a direct impact on the Seasonal Forecast. Cleaning the future will raise the Final Cleaned Sales Key Figure but will have no direct impact on the Seasonal Forecast unless you change the Rolling Trend Window on the Options Sheet (5.0 Rolling Processes (Quarter, Month & Week)) whereby the cleaned Sales will then affect the Trend, which in turn will effect the Seasonal Forecast (Seasonality is ALWAYS built from Yr1 and Yr2 Sales at the moment). Scroll right on any Quarter, Month and Week Sheet. Use the Sales Cleaning KF to clean Sales


Q. Can I change the Sheet Names for Quarter, Month and Week or the Table Names?

A. Yes, you can change all of the Sheet Names in Squid, including the Calulation and Chart Setting Sheets. If you change a Table Name the other Sheets will update their Formula accordingly


Screen Shots

XXXX

Video

Here I run the Demos that ship with the Lightbox Software

Support

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

  • Make sure your question is a valid Software Issue and not a customization request
  • Make sure you have read through the FAQ's, online documentation and any related video guides before asking support on how to accomplish a task
  • 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
...or Contact Mark Kubiszyn via our Facebook Page - remember to be patient, if there has been an issue with your download, Mark will always respond within 48 hours and will Email you the File directly if neccessary or via Messenger. 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

Changelog

You can find the version history in the Code Module for any Macro-enabled Software or read more information on the status of each release
- the latest Version including a description of any changes made is always shown first

TBC, (Version 3) release