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 Monthly Forecasting tool for S&OP. Squid provides a simple, Seasonal 2-Year Forecast by Business Quarter, Month and Week. Seasonal Forecast Periods are interchangeable and Table Combinations can be linked/Sliced throughout all of the Quarter, Month and Week Sheets. 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. Squid requires a full Historical dump of Sales data at one of these levels as the inital input to drive the model and then regular Quarterly, Monthly and Weekly Sales updates to derive the Trend. The Trend is based upon a 2 Year Moving Window so as to capture recent volumes. The Trend will then be used along with the Seasonal Factors to calculate the Seasonal Forecast (SF). 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 flexible, powerful Multiplex and SQL Reporting that can be configured in the 6.0 Multiplex & 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. If only one level of Forecasting is required Squid can be broken down or dissasembled into either Quarter, Month or Week versions of the Tool

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 only · 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)
  • Toggle Chart Label Series and Series Values
  • Interchangeable Periods, Business Quarter, Month and Week
  • Configurable Options Sheet with Node Toggle (Expand & Collapse Sections), built using the Skeleton Quick Builder, part of my Treelist Software - more info and purchase
  • Powerful, aggregate Reporting using SQL
  • Flexible, cross-period Fields Reporting using my Multiplex Class (Maximum 2048 Columns 2^11)
  • 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 Own Squid' Section, ensuring that you read all of the information. I do, however, recommend that you 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

3.7 Week 53
This part deals with the extra Week 53 for the Week Period. You can choose to apportion the entire Week 52 or a Percentage (%) of Week 52 for Week 53 ie. 80%

3.8 Aggregation & Dissaggregation
This part deals with the fact that the first Month to Week and vica versa split may lose some Days/Volume, therefore you can choose to use a 7 Day coefficient to raise the Volume. Toggle the Setting to 1 to turn it On or zero (0) to switch it off

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, Chart & Technical Period 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 Multiplex & SQL Reporting

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

6.1 Toggle Report Sheets
This part allows you to Hide/Unhide each of the Quarter, Month & Week Report Sheets

6.2 Multiplex Cross-period Reports
This part allows you to Select one of 3 configurable Multiplex Reports, 'Report 1', 'Report 2' or 'Report 3'. The first Report, 'Report 1' is already set in the first Drop-down Combo Box. Then there are a number of Optional Settings that you can apply when creating the Report. These Settings are 1=On/True or zero (0)=Off/False. You can choose to Clear Formats as well as Contents whenever the Report Sheet is Deactivated. You can choose whether to Auto-fit Columns after the Report is generated. You can also choose whether or not to set the first Row to Bold and/or set the first Row as Text Format. Text Fromatting can be used to force Dates into Text ie. Jan 20 would be changed into a Date 'Jan-20' and have a Custom Format normally, whereas if you set this to 1 and then use an aposthrophe (') it will become Text ie. 'Jan 20'. Please Note: if you want to combine different Formats ie. General and Numbers and Text, then set this to zero (0), Format the Row yourself and then use Apostrophe (') for Dates ie. change the Row Fields you want as Text and then add 'Jan 20

Once you have decided on which Multiplex Report you want to configure and execute, you need to follow the 3 Steps, 1. Select a Period Sheet, 2. Select the Fields for the Report and 3. Enter a Field Name of your choice if you don't want to use the ones in the Tables - blank entries will be ignored. So for Step 1. Select Quarter, Month or Week. Step 2. pick one of the Fields for the Sheet that you have just Selected. Continue this until you have all of the Sheets and all of the Fields that you require for your Report. Step 3. (Optional) reinterpret or change the name of the Field by entering the Text that you would like to see for the Field/Column Header. That's it, now click on the Report Sheet. If it is not visible unhide it in the 6.1 Toggle Report Sheets part

6.3 SQL Reports
This part allows you to Select 2 configurable Reports for each of the Quarter, Month or Week Sheets. The first Report, 'Report 1' is already set in the first Drop-down Combo Box. Then there are a number of Optional Settings that you can apply when creating the Report. These Settings are 1=On/True or zero (0)=Off/False. You can choose to Clear Formats as well as Contents whenever the Report Sheet is Deactivated. You can choose whether to Auto-fit Columns after the Report is generated. You can also choose whether or not to set the first Row to Bold and/or set the first Row as Text Format. Text Fromatting can be used to force Dates into Text ie. Jan 20 would be changed into a Date 'Jan-20' and have a Custom Format normally, whereas if you set this to 1 and then use an aposthrophe (') it will become Text ie. 'Jan 20'. Please Note: if you want to combine different Formats ie. General and Numbers and Text, then set this to zero (0), Format the Row yourself and then use Apostrophe (') for Dates ie. change the Row Fields you want as Text and then add 'Jan 20

Once you have decided on which SQL Report you want to configure and execute, you need to follow these Steps. Step 1. Choose the Fields for the SELECT Statement. These Fields come from the Table of the Period you are running the Report for. There are 12 available Columns. You will notice the SELECT Statement Formula changing as you pick your Fields. Step 2. Select the Aggregate Fields for the AGGREGATE Statement from the Drop-down Combo Boxes. For example if you are configuring a Quarter Report then the Drop-down Combo Boxes will be populated with Quarter Table Field Names. Continue this until you have all of the Fields that you require to aggregate/SUM for your Report. Step 3. (Optional) set the Drop-down Combo Box to 1 to reinterpret or change the name of the Field when the Report is created. Do this by entering the Text that you would like to see for the Field/Column Header underneath the Period Fields that you have already chosen. Step 4. (Optional) add a WHERE Statement, for example WHERE [Product Category] = 'ALE' would restrict the output to only Records where the Product Category is ALE. Step 5. Leave the GROUP BY Statement as is. Step 6. (Optional) add a ORDER BY Statement, for example ORDER BY [Category] DESC would force a Sort on the Category Field in a Descending Sort order. That's it, now click on the Sheet pertaining to your Report ie. if it is a Quarter Report, click on the 'QReport' Sheet. If it is not visible unhide it in the 6.1 Toggle Report Sheets part

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

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(MTable[@[S Jan Yr1]:[S Dec Yr1]]). For the AVG, we Average through both Year 1 and Year 2 from the Month Sheet if the Seasonal Forecast is turned on. If it is turned off, then the Average is set to zero (0) ie. =IF(MTable[@[On/Off]]="On",AVERAGE(MTable[@[S Jan Yr1]:[S Dec 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 Month of Year 1 and Year 2 ie. =AVERAGE(MTable[@[S Jan Yr1]],MTable[@[S Jan Yr2]]) for both Jan Periods. For the Period Factors, we divide the Period Average by the 2 Year Average Sales ie. =IFERROR([@[S Jan∓]]/MCTable[@[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



Week, Calcs & Chart Settings

The Week Sheet and the Week Calc or 'WC' Sheet are intrinsically linked. They both contain Tables, 'WTable' on the Month Sheet and 'WCTable' on the WC Sheet. The WTable contains the Sales data values and the Seasonal Forecasts (and any other required Series for the Week Chart). It also contains some Master Data values that formed part of the initial Sales dump by Week. The WCTable 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 Week 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 (Month to Week, Week to Month 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 Week. 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 WC Sheet containing all of the Weekly 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 Week Sheet ie. =SUM(WTable[@[S 1 Yr1]:[S 53 Yr1]]). For the AVG, we Average through both Year 1 and Year 2 from the Week Sheet if the Seasonal Forecast is turned on. If it is turned off, then the Average is set to zero (0) ie. =IF(WTable[@[On/Off]]="On",AVERAGE(WTable[@[S 1 Yr1]:[S 53 Yr2]]),0). For the Trend we use the settings in 5.1 Rolling the Week 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 Week of Year 1 and Year 2 ie. =AVERAGE(WTable[@[S 1 Yr1]],WTable[@[S 1 Yr2]]) for both WK1 Periods. For the Period Factors, we divide the Period Average by the 2 Year Average Sales ie. =IFERROR([@[S 1 ∓]]/WCTable[@[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 WCS Sheet contains all of the values required to produce and update the Week 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 Week Sheet. Below is an Image of the Week 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

Master Data

A Master Data Sheet (MD) is included. It has been Hidden. Unhide the Sheet and then clear it down. Add your Master Data to this Sheet. Insert Columns into your Table by INSERTING COLUMNS (highlight a FULL COLUMN and then INSERT) so as to keep all of the Column Widths for the Table Fields and any Text I may have added above intact - otherwise the Table will add a Column and will be out of sync with the Calculation headers. Now use an INDEX/Match Formula to lookup the data

Adding A New Time Series

You can add a new Time Series into Squid like this. Click on the Options Sheet. First of all you need to add a new Time Series Prefix into the 3.3 Date & Time Series Formats (used to build the Time Series Periods used in the Update, Data Sheets and Squid Dashboard Part of the 3.0 Setup Section on the Options Sheet. To do this select an existing Row. Copy the Row. Right-click on the Row Header and Insert Copied Cells. Type your new Prefix, usually one or two letters that have relevance to the Time Series that you are adding ie. 'SAndOP' (without the apostrophes) for a new S&OP Forecast Time Series - it is not wise to use an Ampersand (&) or any characters like Slashes (/\) or Periods/Full Stops (.). Now Name that Cell (Defined Name) like the other Prefixes ie. 'Squid.TimeSeriesSAndOPPrefix' (without the apostrophes) by entering a Name in the Name Box, Top Left. The Time Series should be either Quarterly, Monthly or Weekly buckets as it will use one of the preset Series Ranges from the Options Sheet

Now click on the Data Sheet. You can either insert new Columns at the end of an existing Period Range on the Data Sheet or you can do it this way. For my new Weekly S&OP Time Series, Scroll to the last Period of the Sheet (Top Right) and add the Formula '=Squid.TimeSeriesSAndOPPrefix&" "&Options!B96&" "&Options!B95' (without the apostrophes). Now drag the Formula right for as many buckets as you wish. You can also Copy the Format that indicates a new Time Series (a Yellow Cell Fill Format) after you have finished dragging the Formula. Highlight the full Range that you have just added and give the Range a distinct, short Name in the Name Box, Top Left ie. 'WeeklySAndOP' (without the apostrophes). Click back on the Options Sheet

Go to the 4.2 Setup Time Series for an Update Part of the 4.0 Time Series Updating Section and click on the Drop-down Combo Box used to select a Time Series. Click Data Validation on the Data Tools Group on the Data Tab of the Ribbon and add the same name that you gave your Range into the Source separated by a single comma ie. ',WeeklySAndOP' (without the apostrophes). Click OK. Now you should be able to select the Time Series from the Drop-down Combo Box. You should also now be able to select the buckets Range for the Start and End buckets ie. from 'SAndOP 1 18' until 'SAndOP 53 19'. That's it, you have added the new Time Series, however you also need to ensure that you have inserted the Columns into your Period Sheet, so for my Weekly S&OP Time Series, I would need to insert 212 Columns into the Week Sheet, give them all unique Header Names and then reference the first Cell underneath the inserted Columns to the first Cell in the Data Sheet so that it will pick up the data values when I do an Update

Technical Periods

A Technical Period is a Monday Week Date that enables the Aggregation and Dissaggregation of Seasonal Forecast values between Quarter, Month and Week. Technical Periods are used to split the Seasonal Forecast. Technical Periods run for a full 2 Years for Year 3 and Year 4. The first Technical Period will always match an IOS Week 1

The first Technical Period begins 1 Week prior to the first Monday of the Year, for example in 2020 this Date is 06/01/2020 and TP A 1 begins on 30/12/2019. The link to the Formula trail following the first Technical Period is shown in a Green Cell Fill from light to dark for Yr3 and in a Blue Cell Fill from light to dark for Yr4. Dates are built for each new ISO Week ongoing and Technical Periods are split whenever a full Week has Dates that are in one Month and then the next Month. Week 5 demonstates this, splitting the last 5 Days of January 2020 and the first 2 Days of February 2020 into two Technical Periods

2 Tables are used for Technical Period calculations. Table1 is built that contains the number of Days for each Quarter, Month and Week based upon the 2 Years Technical Periods. These numbers are used for dissaggregation and aggregation between Period Sheets. For Quarter and Month Periods, these Days will always SUM to either 365 or 366 depending on whether or not one of the Years is a Leap Year (a Year in which there is an extra Day in February). Table2 is the Technical Periods used for Week to Month Aggregation and Month to Week Dissaggregation. Table2 always ensures that an ISO Week 1 is aligned with the first Technical Period to make conversions from the main Month and Week Sheets easier

Here is a Screen Shot of the Technical Periods detailing the split from January into February for the ISO Week 5. You can see that the last 5 Days of January 2020 and the first 2 Days of February 2020 are split between two Tecnical Periods. You can also see the split for February into March and the SUM of these Days per Quarter split further below:



Handling Week 53

In some Years as you will be aware in Week, there is an extra Week 53. This affects the Sales History and the future Seasonal Forecast. For Years where the last 2 Years have no Week 53, then we can deal with this in a couple of ways. You can add Volume per combination for the extra Week 53 but this means going through every combination. Another way is to adjust the Apportionment setting in part 3.7 Week 53 underneath the 3.0 Setup Section on the Options Sheet. This setting allows you to place a part in Percentage (%) of Week 52 Volumes, so you could add 50%, 80%, 100% or 150% if you like - this setting applies to every combination. To switch it off, simply use 0%

For Years where the Sales will actually create a Week 53 Seasonal Forecast ie. having a Week 53 in the last Year, you will need to clean off the Sales History as the setting in part 3.7 Week 53 underneath the 3.0 Setup Section on the Options Sheet will not work for this eventuality. I may revisit this in due course to see if any other options are available

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

Aggregation and Dissagregation possibilities depend on the Period Sheet that you are on and are as follows: Aggregation - Week to Month and Month to Quarter, Dissaggregation - Quarter to Month and Month to Week. The Month Period Sheet allows both Aggregation and Dissagregation of Quarter and Week

A couple of considerations when using aggregation or dissaggregation, are, for example when using Month to Week, that a Period like Christmas will have an even apportionment across all of the December Weeks, meaning that your Seasonal Forecast spikes are not in-line with last Year Sales at the Aggregated or Dissaggregated level. Also the split for the first Week or Month of the Seasonal Forecast cannot use last Years Seasonal Forecast as it doesn't exist and therefore may lose a couple of Days - you can fix this by adjusting the Setting 3.8 Aggregation & Dissaggregation Part in the 3.0 Setup Section on the Options Sheet to 1, which means use a 7 Day Coefficient, thereby raising the Forecast

Please Note: when using aggregation and dissaggregation between Quarter, Month and Week Sheets, if you attempt to use Month to Week for a Combination in Month view and then Week to Month in Week view, you will receive a Circular reference as you are trying to square a circle and Excel will not let you do this. Here are a few Screen Shots to demonstrate using a Month to Week Seasonal Forecast dissaggregation. The first Image is a Month Seasonal Forecast. The second Image is the normal Week Seasonal Forecast and the Third Image is the dissaggregated Month Seasonal Forecast being used as the Week Seasonal Forecast:









Highlighting Small & Negative Values

Squid uses Conditional Formatting to highlight numbers that are very small and/or negative. For example you may be using Hectolitre for low selling CASK/KEG where tiny volumes need to be Forecast and you want to ensure a tiny Seasonal Forecast is identified for an adjustment up to 0.5 Hectolitre in a Month Forecast. Squid will automatically highlight these tiny values between 0.00000000000001 and 0.99999999999999 in a Fawn/Beige Cell Fill. Also you will want to change any negative Forecasts as we don't especially want a negative Seasonal Forecast. Squid should not Forecast negative values if the inputs are positive and/or the Trend is sound, but there may be occurances when this will happen, especially as I say if you are taking input as Sales in very small values with a drop in Sales for a sustained period. If this happens then Squid will highlight these negative values in a Red Cell Fill so that you can adjust them up by Trend, by Manual Trend or by Manual volumes adjustments

Aligning Table Slicers

Squid uses Code to align Table Field Slicers that are added into the Quarter, Month and Week Sheets, so that whenever you slice a Product or a Category, all 3 Sheets will display the same outputs in terms of the Product and Categorys Sliced. It doesn't have to be Product or Category and more than 2 Slicers can be connected to the Quarter, Month and Week Sheets. Linking is turned on by default. Whenever creating your own Squid, it may be best to turn this feature off until you are ready to align everything. Anyhow, the feature can be turned on in the 3.6 Link Quarter, Month & Week Table Field Filtering and Slicers Part of the 3.0 Setup Section on the Options Sheet by changing the Setting to 1. To switch the feature Off, change the Setting to a zero (0). In order to tell Squid how/what Field Slicers to link, add the Column Numbers to the Setting Cell separated by a Comma - the fewer the Columns, the faster the linked code will execute. For example, entering '1,2' will mean that the first 2 Table Fields will be linked, including any associated Slicers

When you add new Table Slicers, they should have their Move Property set to 'Move but don't size with cells' (Right-click->Size & Properties) and Move them to the Front so that they cover the Chart (Right-click->Bring to Front). You can also 'Hide items with no data' for the Slicer (Right-click->Slicer Settings). By default I have added 3 Slicers on each of the Quarter, Month and Week Sheets. As these are linked using Code, selecting any combination will result in all 3 Sheets displaying the same information in the Tables

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)'

Reporting

Squid has very flexible and powerful reporting capability. Reporting is triggered automatically by clicking on the appropriate QReport, MReport, WReport or Report Sheet and then cleared again automatically by selecting any other Sheet - this allows the Filesize to remain as small as possible. Reporting comes in two flavours, Multiplex Reporting and SQL Reporting - see the Sections below for more information

Multiplex Reporting

Multiplex Reporting doesn't allow any aggregation, but it is fast and very flexible. In fact you can build your report and then extract the data for ANY of the Fields in your Quarter, Month or Week Tables in any order you like, for example you could extract your Product, Category and the next 2 Years Seasonal Forecast by Month alongside your Budget in grouped pairs ie. Product, Category, SF Jan 20, B Jan 20, SF Feb 20, B Feb 20 etc. Multiplex Reporting uses the Report Sheet as the output for all of its reports. You can toggle this On/Off in the 6.1 Toggle Report Sheets part of the 6.0 Multiplex & SQL Reporting Section on the Options Sheet. Multiplex Reporting allows up to a Maximum 2048 Fields/Columns or 2^11. Please Note: Multiplex Reporting cannot lookup across Sheets, by Product or Category, it merely allows you to build a Report in one Sheet from the data in any of the Tables in a contiguous manner. For example you could pull out a Quarter, Month and Week Seasonal Forecast all into the Report Sheet

3 Reports are available for you to change as you wish in the 6.2 Multiplex Cross-period Reports part. Each Report has 3 lines of editable Drop-down Combo Box Cells up to a Maximum of 2048 Cells. The first line enables you to Select the Sheet from which you wish to pick any Fields ie. Quarter, Month or Week. The next line allows you to pick any corresponding Field from the Sheet. Fields are the actual Table Headers on the Sheet ie. Product, Category, SF Jan Yr3, SF Feb Yr3 etc. (Please Note: the Fields are NOT the Calculation Fields). The third line allows you to interpret these Field Names (I call this reinterpreted Naming). In a nutshell, you can change these Cells to any Text including Periods (.), ampersands (&), forward Slash etc. Please Note: you can simply over-type any of these Cells as long as you type a valid Drop-down Combo Box available Text. You can interpret the Fields as Jan 20 by adding a suffix (') like this 'Jan 20 and to prevent Excel thinking it is a Date the first Row on the Report Sheet is formatted as TEXT - you can manually change any of the Row output Fields to other types if you wish ie. Date, Number etc. Another feature of Multiplex Reporting is that it will report on Fields that may be hidden and/or Grouped in any of the Period Sheets. Here are two Screen Shots of the Report 1 Selections and the Report output:







SQL Reporting

SQL Reporting has the added benefit of aggregation, Grouping and Sorting. It can take a Field such as a Category Field and aggregate all of your Products up for your Seasonal Forecast, Grouping them and then spitting them out in a specific Sort order ie. Ascending or Descending. SQL Reporting can also use the WHERE Statement to only return specific Items. For example after an Update you could build an SQL Report to spit out any New Products. SQL Reporting is specific to the Period that you are Reporting on, so each Period Sheet, Quarter, Month and Week has 2 Reports that can be configured and Selected so that they are triggered whenever you click on the QReport Sheet, the MReport Sheet or the WReport Sheet. You cannot Cross-Query each type of Period Sheet and you MUST stay within the Excel ODBC Driver Column limit of 255. You have 12 Select Fields and then the ability to add 243 more of your choosing. You can, in fact extend the Select Fields - I will supply more information at a later Date on this capability, however the limit of 255 will still remain

There are 2 Reports in the 6.3 SQL Reports part for Quarter, Month and Week. Let's look at the Quarter Reports. First of all you need to Select a Report to execute for the Quarter Sheet from the Drop-down Combo Box ie. 'Report 1'. Now Choose the Fields for the SELECT Statement ie. Product, Category (or whatever your own Fields are) from the 12 Drop-down Combo Boxes. Then choose the Fields for the AGGREGATION or SUM AS Statement ie. SF Q1 Yr3, SF Q2 Yr3, SF Q3 Yr3, SF Q4 Yr3 etc. You can add a WHERE Statement (this is optional) ie. WHERE [Category] = 'ALE'. The GROUP BY Statement is built automatically, you can write this yourself, but I am not going to cover that here, so leave it as per the Formula. Lastly, you can add an ORDER BY Statement to perform a Sort ie. ORDER BY [Category] DESC. Whenever you add any SQL yourself, make sure a) that the Field is a genuine Field in the Table you are referring to and b) wrap the Field in Brackets []. Here are two Screen Shots of the SQL Quarter, Report 1 Selections and the Report output:





Build Your Own Squid - Setup Historic Data

To demonstrate Squid, we will build a Squid to Forecast a handful of Products (tins of Paint) at a National level (Squid can produce Forecasts at Depot and Customer level if required). You can follow this article and use your own data if you like to get a feel of how to setup Squid and what it can do

First of all Open the Squid_v3 (Blank).xlsb File. This File has been left Blank - all of the data in the Data Sheet has been cleared down using Clear->Clear->All on the Editing Group of the HOME Tab of the Ribbon. Always use this method when clearing down data here. Okay, good, so now we need to decide on how we want to Forecast. Obviously we will need a Product and a Description so this takes care of our first 2 Columns of Historic data required. You have 9 more Columns left to use for Historic data (remember you can add Master data to the hidden MD Sheet and look it up into more Columns on the Quarter, Month and Week Sheets later if required). The Column 'New' is required to flag up new combinations. You can use Depot or Customer to Forecast at lower levels and then aggregate. For my example I will simply take some data with a Product Category. So my data will be Product, Description and Category. I will go and retrieve at least 2 Years plus as much from the current Year as I can. I will Forecast tins of Paint Sales

First of all I will update the settings in readiness for my Historic data capture. Go to the Options Sheet and expand the 3.0 Setup section. Underneath the 3.2 SQL Settings section, set the Pack Type Header to '—' so that only the Product Code and the Category are Fields that Unique, meaning that the SQL will use these 2 Fields to perform all Batch Updates - see below:



I want to Forecast at all 3 levels, Week, Month and Business Quarter. So I will add the Weekly Historic data first as a simple Paste and then add the Month and Quarter data using the SQL Batch Update - Please Note: if you already have the data exactly the same for Week, Month and Business Quarter, then you can simply Copy into the Data Sheet underneath the respective Headings. Anyhow, take your Weekly Sales History data and Go to the Data Sheet. Now Copy the Product, Description and Category as Special Values into the Data Sheet into Cell "B2" like this:



Now scroll across to the Weekly Sales which will be "KZ2", 'S 1 18' our first Year of History set in the Options Sheet. Paste your actual Sales History into this Cell. You should Paste at least 2 Years data and leave a Blank space for Week 53! Here is my data, 2 Years from 01.2018 through to 22.2020:



So that's the Historic Weekly data. Now to add the Monthly Historic data go to the Options Sheet and expand the 4.0 Time Series Updating section. Change the 4.2 Setup Time Series for an Update to MonthlySales. Change the Start Bucket to 'S Jan 18' and the End Bucket to 'S May 20' like this:



Go to the Update Sheet. The Cell in "N2" should now start at 'S Jan 18' our first Montly Bucket for the batch Update. We will be doing an Update for the Month and Quarter Sales that will match the Weekly data already Copied. If you already have the data exactly the same for Week, Month adn Business Quarter, then you can simply Copy into the Data Sheet, but if you have differences in your data then you can do the Batch Updates. It is also good to show you this technique in readiness for any future Batch Updating that you may do as new Sales come in for example. Paste your Product, Description and Category into Cell "B2". Then Paste your Monthly Historic data to be updated into the Data Sheet into Cell "N2" and press the UPDATE Button to perform the Update. You will receive a prompt and then a notification of the success of the Batch Update This was my data before the update:



This is my data after my update - all of the Monthly and Weekly data have been aligned and now reside in the Data Sheet:



Perform the same for the Business Quarter by first changing the 4.2 Setup Time Series for an Update to QuarterlySales and the Start Bucket to 'S Q1 18' and the End Bucket to 'S Q2 20' which will cover May 2020 Sales. Do the Batch Update as before by Copying your Quarterley historic data into the Update Sheet and then check the Data Sheet to see that it has transferred. Going forward every new Week, Month and Quarterly Sales need to be Batch Updated like this!

Build Your Own Squid - Forecasting

Now you have some Historic Data Squid will begin Forecasting. You will need to extend the Rows of all of the Tables so that the Formula work properly. With Squid it is recommended that you extend the Tables many extra Rows down so that going forward new Products (or combinations) are picked up following any SQL Batch Updates. In our Example we have 3 Tins of Paint but the Blank Workbook is only Setup for 2 Products or Rows or Combinations (depending on your Aggregation levels). Important! before doing anything ensure that all of the Slicers used are set to not Filter

You should do this one Sheet at a time to ensure Table Formatting and Calculations are copied down correctly. I will go through one type of Sheet so that you can understand - I will use the Quarter Forecasting Sheets and calculations for this example. Click on the Quarter Sheet. Scroll right, until the end of the Table. Now I find that sometimes Formatting may not be copied down the Table if you just click the tiny Handle and drag down, so I select the entire last Row of the Table and then I drag down 1 Row using the Handle - this copies the Formulas and the underlying Table Formatting down also. Things will look a little strange as the Table has picked up the extra Product but the Formulas cannot calculate correctly as you need to extend the Quarterly Calculations as well. Click on the QC Sheet. Repeat the operation that you have just done. That's better we now have some better information if we check the Quarter Sheet. Repeat this for the Month Sheet, the MC Sheet, the Week Sheet and the WC Sheet. All is good, however we need to finally extend the TP (Technical Periods) Sheet if we want to use any kind of Aggregation or Dissaggregation between Quarterly, Monthly and Weekly Forecasting, so do the same for the TP Sheet. Now you should have Forecasts for all of your Products and checkiing my example I have my 3 Tins of Paint and all of the Table Formulas and Formatting have been copied down like this (Sales will be down in Q2 on the last 2 Years, as we do not have full Q2 Sales yet for the current Year):



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


Q. Can I Toggle the Series On/Off on the Charts?

A. Yes, you can toggle the following Series On/Off on the Charts: Seasonal Forecast 2021, Rolling Forecast 2020, Budget 2020, Sales 2018 and Sales 2019. You will need to update the Lables yourself and any other Series will need to be added by yourself if required. Please do not contact me regarding adding more toggling for Chart Series - you need to do any amends yourself!


Q. Can I Toggle the Series Labels On/Off on the Charts?

A. Yes, you can toggle the following Series Labels On/Off on the Charts using the 2 Checkboxes: Final CLeaned Sales 2020 and Seasonal Forecast 2020. Please do not contact me regarding adding more toggling for Chart Series - you need to do any amends yourself!


Future Builds

To look at the posibilty of producing FA & Bias Sheets

To produce a Colour Theme Changer into the options Sheet to change the Chart Series Colours and the Summary Colours

To revisit the Week 53 issue, based upon Customer feedback

To create a list of Saved Reports that can be accessed via a Drop-down Combo Box and then executed. Output can be Saved from the Normal Reporting for Quarter, Month and Week and given a Name or you can write your own SQL and execute it

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

01.07.2020 (Version 3) released