Introduction

Introducing Jellyfish Forecasting Tool

Jellyfish is a Reactive Seasonal, Simple Linear Trend Forecasting Model, that is ran Weekly for Excel 2016 64bit

Jellyfish is a Weekly Forecasting Tool for Excel, written in VBA, featuring Ribbon Buttons for the Macros. It comprises of a Model and a Planner. It allows you to maintain a strict but editable Table of products or combinations that can be updated after an initial Actuals history dump. The Model allows cleaning, builds seasonality and then creates a forecast with a trend. It is a 'Rule-based' Model which switches its forecasting methodology depending on a set of rules around the most recent weekly buckets. It can store the rolling forecast which then takes precedent over the calculated forecast for the past. The Planner allows you to make adjustments to the forecasts, add any Promotional activity, add a percentage (%) uplift and add forecast overriders. It can be used to start forecasting New Product launches straight away, letting the Model take control of the forecast as the next actuals are updated. Due to the way it builds a reactive level and then uses the Slope function in Excel, it can be classified as a Regression Model. A separate Latest Estimate File uses various VBA techniques to retrieve a latest forecast estimate directly out of Jellyfish from a local file or a SharePoint file

The Jellyfish files are Compressed and Cleaned using VBADecompiler.exe by Orlando. The Jellyfish image show below is made by Freepik from www.flaticon.com. You can purchase my Jellyfish at FastSpring for £2.99 (Please Note: due to the intensive labour behind this Software and the initial setup complexity, a user should have some Demand Planning experience, be competent in Excel and I only offer very limited support with absolutely NO Company customisation!)

Prerequisites

• Excel 2016 version 16.0 64bit only!
• Basic Excel skills and an idea around the concepts of forecasting/demand planning
• Windows PC · NOT a Mac!
• Important! This Software is very complex and uses VBA and a large variety of Excel Formula for an incredibly low price. Therefore aside for support if it fails to work, I can NOT offer any level of configuration to Jellyfish and/or the Latest Estimate Files!

Features

• A Weekly Model that analyses the data and then creates the forecasts. A Planner that can be used to make adjustments to the forecasts, add any Promotional activity and add a percentage (%) uplift
• Forecast New Products immediately by cleaning or from a single 1 week sale
• A Dynamic 1 Year Chart with Zoom and Scroll capability for the number of Weeks displayed and the Start Week chosen. 4 other Charts for Current Year/Next Year on the Planner and Details Sheets
• Toggle between a pre-defined Short Horizon Chart view and the standard 52 Week view
• Lock in future forecast levels using forecast overriders which will not be statistically forecasted for specific periods ie. Christmas
• Forecast at many different levels, with flexible configuration for indexes when performing updates ie. choose specific fields to use as an index when updating or decide whether to overwrite other fields during the update (when using Bespoke indexes)
• Variance Formula by combination. Use and adjust these to fit your own purposes. Due to COVID, I have initially set these up to track 2021 Actuals vs. 2019 Actuals (last X Weeks), Forecast 2021 vs. Actuals 2019 (next X Weeks) and Forecast 2021 vs. Actuals 2021 (last X Weeks) where X (meaning number of Weeks) can be configured on the Setup Sheet
• The Regression Model can build seasonality and uses recent weighted averages to derive the level. It includes a Trend using the Slope function in Excel (rise and run)
• A Configurable Variance Box that can be used to go through the previous weeks variances, with 'Top X', 'Bottom X' and Criteria Filtering. The UserForms are resizable using Code by Manfred van den Noort from www.worksheetsvba.com
• A Configurable Zero Forecast Box that can be used to go through the previous weeks Zero Forecasts resizable like the Variance Box descibed above
• Switch the Forecast off using various methods, both permanently or for specific, disparate and non-contiguous weeks
• Make Planner adjustments, Promotional adjustments and MI % adjustments
• Manual Overriders to force Seasonality for NPD's and Trading for Product Launches - adjust the calculated Seasonality and Level of a combination using a Percentage (%) Overrider. Store a Reason for your Overriders in the Overrider Reason Text Field
• Features Carbon, a replacement Dialog for Excel and Multiplex for fast, array manipulation in memory (modified, minified version of the full Software)
• Create Supersessions using the Old Product (Predecessor) and the New Product (the Successor) with immediate effect or by phasing-out/phasing-in over extended periods
• Contains a 64bit Statusbar Progressbar to keep you informed during the update process
• Popup Action Messages built from Toasts, my popup toast messaging system (minified version)
• Lots of configurable settings using a Template built from Treelist, a hierarchal tree node creator (template only, not full Software)
• MAE (Mean Absolute Error), RMSE (Root Mean Squared Error) and MAPE (Mean Absolute Percentage Error) reporting by combination from a desigated starting point - adjust the Formula to begin tracking
• 2 moving Menubars (or Toolbars) to Scroll around the Planner built from Menubar, a Menubar with Popup Buttons that follows you around as you work (modified version of the full Software). Buttons are linked to Macros to aid Demand Planning
• Buttons built from my Awesomebuttons Software used on the 'Setup' Sheet (minified version) and a Toggle Button built from my Flat UI Software (minified version)
• A separate Latest Estimate file uses techniques to to retrieve a latest forecast estimate directly out of Jellyfish from a local file or a SharePoint file. You can filter this data by Pasting in Product Code lists. The Latest Estimate File is also used to create an update for the weekly forecast roll and features other bespoke conversion functionaily all via Ribbon Buttons

A Screenshot of Jellyfish v1.1

Installation

The file is called Jellyfish.zip and contains the Jellyfish (Examples).xlsb Example File, the Jellyfish.xlsb File, the Latest Estimate.xlsb File, the Jellyfish Update.xlsx File and an Optional Config.ini File (this contains my latest settings for the Variance Box and the Zero Forecast Box, but you don't have to copy this File, it will be generated automatically as you use Jellyfish). Copy the files to your PC and proceed with the configuration in the Getting Started section to start forecasting. Please read all of the info in the Setup Sheet and in this online documentation before you begin to edit and use the Jellyfish Model or before turning to Support

All of the information to configure Jellyfish is contained in this online documentation - please take the time to read the Getting Started Section and as much of the information as is possible prior to using the Jellyfish Software. Please note: I am UNABLE to configure or develop Jellyfish for everyone that purchases the Software, including modifying any of the Workbooks, Formula, Code, Layout, adding to any of the Sheets, creating Pivot Tables to aggregate data etc. You or someone in your Business must make these changes in accordance with your data and requirements

How Does Jellyfish Work

A Brief Overview of the Rule Based Model Calculation

A lot of people have asked me how Jellyfish works. Well here is an abstract overview of the fundamental Jellyfish Forecasting logic:

IF WE HAVE VALUES FOR THE LAST 3 WEEKS WE WILL USE THE WEIGHTED AVERAGE CALCULATED FROM THE LAST 3 WEEKS VALUES AND THE 3 WEIGHTED AVERAGE SETTINGS ON THE SETUP SHEET AND DIVIDE THIS BY THE LAST 3 WEEKS AVERAGE SEASONALITY INDEXES TO DERIVE AN ANNUAL LEVEL ESTIMATE. THIS ANNUAL LEVEL ESTIMATE IS THEN USED IN CONJUCTION WITH THE TREND TO CREATE THE FUTURE FORECAST

IF WE DON'T HAVE VALUES FOR ALL OF THE LAST 3 WEEKS BUT WE DO HAVE AN AVERAGE CALCULATED FROM THE VALUES FOR THE LAST X WEEKS, WHERE X IS THE NUMBER OF WEEKS TO BE CONSIDERED FOR AN AVERAGE ON THE SETUP SHEET THEN WE WILL DIVIDE THIS BY THE LAST 3 WEEKS AVERAGE SEASONALITY INDEXES TO DERIVE AN ANNUAL LEVEL ESTIMATE. THIS ANNUAL LEVEL ESTIMATE IS THEN USED IN CONJUCTION WITH THE TREND TO CREATE THE FUTURE FORECAST

IF WE DON'T HAVE VALUES FOR THE LAST 3 WEEKS AND WE DON'T VALUES FOR THE LAST X WEEKS EITHER THEN WE WILL USE THE AVERAGE CALCULATED FROM THE VALUES FOR THE LAST X TRADING WEEKS, WHERE X IS THE NUMBER OF TRADING WEEKS TO BE CONSIDERED FOR SWITCHING THE FORECAST ON OR OFF ON THE SETUP SHEET THEN WE WILL DIVIDE THIS BY THE LAST 3 WEEKS AVERAGE SEASONALITY INDEXES TO DERIVE AN ANNUAL LEVEL ESTIMATE. THIS ANNUAL LEVEL ESTIMATE IS THEN USED IN CONJUCTION WITH THE TREND TO CREATE THE FUTURE FORECAST

Other Notable Points

Seasonality is at the heart of Jellyfish and it important that you understand how and why you are using Seasonality to fully understand how the Forecasts are created

A YEAR OR NUMBER OF YEARS MUST BE DECIDED UPON FOR THE SEASONALITY AND THEN 4 FORMULAS NEED CHANGING/UPDATING MARKED IN RED ON THE MODEL SHEET IN ORDER TO OBTAIN THE SEASONALITY INDEXES

THE GENERIC SEASONALITY CALCULATIONS ALSO NEED TO BE UPDATED BY YOUR OWN CATEGORY ITEM NAMES AND THE FORMULAS HERE WILL ALSO NEED CHANGING/UPDATING SHOULD YOU CHOOSE TO USE ANY OTHER COLUMN THAN THE DEFAULT COLUMN 4, FOR YOUR GENERIC SEASONALITY

DEPENDING ON THE PERIOD OF THE YEAR THAT YOU ARE IN, THERE IS A POSSIBILITY THAT THE LAST 3 PERIODS OF SEASONALITY MAY RESULT IN A SLIGHTLY HIGHER OR LOWER ANNUAL ESTIMATE - THIS IS SOMETHING I AM STILL CONSIDERING FOR IMPROVEMENTS FOR THIS SOFTWARE

All of the information to configure Jellyfish is contained in this online documentation - please take the time to read the Getting Started Section and as much of the information as is possible prior to using the Jellyfish Software. Please note: I am UNABLE to configure or develop Jellyfish for everyone that purchases the Software, including modifying any of the Workbooks, Formula, Code, Layout, adding to any of the Sheets, creating Pivot Tables to aggregate data etc. You or someone in your Business must make these changes in accordance with your data and requirements

The Example Products

A Brief Overview of the Example Products that come with Jellyfish · Please Note: these Examples may change with new releases and are, as such purely for guidance

On opening Jellyfish you will see a couple of Category's containing a handful of Products for each Category. I have deliberately named the Products KEG/CAN etc. and given them a description that best describes their individual volume level and variance ie. STOUT KEG Medium Vol. Low Variance. (Recent Customer Exit). This just makes it easier for you to see how Jellyfish deals with each one - in reality the description would not be like this at all. I may update more Product examples in future releases once I receive any feedback on this Software. Please Note: the forecast includes the stored forecast from week 29 which is saved every week so that we have a picture of how we were forecasting. I have also included any Planner Adjustments duriing that time (Comments removed for privacy, so there is little explanation behind the adjutments, but included just so that you can view them in the Final Forecast)

STOUT

Product Code, Description
112451 STOUT KEG Medium Vol. Low Variance (Recent Customer Exit)
112452 STOUT CAN Low Vol. Erratic

The KEG Product normally ticks along nicely with good volume and very low variance aside from St. Patricks day, when it does a significant uplift, around week 11 every year. Recently there has been a large customer exit and so I have cleaned this Product right back, removing the missing customer volume to get a better shape. I forecast at a National level, but if you were forecasting by customer you could either delete the combinations, use -100% as an MI% adjustment or clean back just the customer volume being exactly the same as their actuals/sales history to give a zero forecast for that customer and leave the remaiing forecast in situ for all other customers. The CAN Product is fairly low volume but can be a little erratic even outside of COVID/Restart issues. On the 'Planner' Sheet select STOUT or enter the Product Codes to view each Product. Please Note: in the first image below the Forecast will be higher in the past as this is the forecast before the customer exit and this was stored in the 'Rolled Forecast' Key Figure - you can see this ontop of the Actuals in 2019 prior to cleaning

GAS

Product Code, Description
112500 CO2 CYL Medium Vol. Low Variance
112501 Nitrogen 1 Medium Vol. Low Variance
112502 Nitrogen 2 Medium Vol. Low Variance

GAS are cylinders of CO2 or Nitrogen used in the Trade on Post-mix like Lemonade/Cola. All three are medium volumes (depending on your classification of medium) but pretty low variance, unless there are Supplier issues or empties cannot be refilled etc. In my examples Jellyfish makes a fair job of forecasting these three Products even with the challenges of dropping actuals where Supplier issues killed the volumes going out from week 32 until week 34. This gap was not plugged by anyone and you can see how Jellyfish began to lower the forecast in week 33, finally getting pretty close in week 35/36 - variances may have occurred across the range and past forecasts may have included inflated levels/MI etc.



Only the current 2021 forecast is shown. The forecast for next year is not switched on for the Chart. Turning it on, you will see that Jellyfish doesn't capture the shift up in week 19, nor does it follow the higher peaks and lower troughs of this year, for the forecast next year, because it is only using 2019 Seasonality. Due to the COVID19 pandemic, you may have to get a full 2022 year actuals first and then you could use past/future Seasonality for your indexes ie. 2022/2023 as you enter 2023 or just use 2022 until you get another full year ie. 2022 and 2023 after which you could use 2 years for your Seasonality

ALE

Product Code, Description
113101 A New CASK Low Vol. Low Variance

All new Products will have missing Seasonality unless you clean them back to the volumes of another Product. As long as there is a single sales bucket, Jellyfish can still forecast them though with or without an launch MI. This is a new CASK ALE into the Business around week 22.2021 and in this case, no launch MI/stored forecast appears to have been added. The Product will have missing Seasonality and will take the DEFAULT Seasonality profile which will at this point be an Average of the STOUT and GAS Indexes. Obviously with more ALE Products it would take the Seasonality of the ALE Category. The powerful shift up in week 51 will probably come froom the large shift in the STOUT KEG Index. You can tell this by examining the Indexes on the 'Generic Seasonality' Sheet or simply by observing the shape of the CASK which is uplifting next year as the STOUT Category does for St. Patricks day volume. As I say, with more ALES, it would take an ALE shape which will work nicely - this is after all just some demo Products

WINE

Product Code, Description
113102 WINE Xmas Fcast for Variance Reporting - Rose Brut BOT 6x0,75
113103 WINE High Vol.

I have added 2 WINES here, 1 is a High Volume Wine with a large Xmas peak. In fact the Xmas forecast has been made off-line and loaded into the forecast to enusre enough stock is ordered as it is on a very long lead time from oversea. This Wine will drive the WINE Seasonality Index as the second Wine doesn't have its own Seasonality. The second Wine has a variance that will be flagged when you use the Variance Box to report on variances, hence why I have included the example. Pressing the Variance List Button on the Ribbon displays the single Product. Clicking on this Product will filter the Planner Sheet. The variance has been reported due to the fact that it sold 15.876 BT in week 42 (its always measured against the last week by default, lord knows why we have fractional sales in our system!) against a forecast of 1 BT giving a variance of 1,586.7%. As a demand Planner I would investigate and perhaps re-jig the entire Xmas levels off the back of the latest 3 week sales if I thought that the Xmas volumes were not sufficient. Looking at this and checking stock, we will be okay, but you could shift it up by 1 Case p/w extra (6 BT). You may notice that due to the lack of Seasonality the forecast will not rise in week 32 and 33 for next year. Switching on 2022 forecast we can see that Jellyfish plots its way through the year in a more regular fashion but rises considerably more next Xmas. This is because the forecast in situ this Xmas is a 'Locked In' forecast stored in the Rolled FOrecast Key Figure. The levels through next year are now more in-line with the last 3 weeks sales which may/may not be correct





If we wanted to, we could remove the 'Locked In' Forecast and generate a new forecast based upon the recent sales. This is what we would get if we did this and you can now see both years are in-line with the recent sales levels. The Wine now has a large Xmas peak, accentuated by the High Volume Wine Seasonality Index - if you had more Wines or the Product itself had its own Seasonality Index then this shape would of course be different and you may want this to be different! Of course you don't have to have removed the Xmas forecast and you could clean back the recent sales a little to leave the Product with a much lower forecast. There are lots of Settings that you can tweak for Variance reporting on the 'Setup' Sheet

Zero Forecast LAGER

Product Code, Description
113105 LAGER for Zero Forecast Reporting - KEG

I have added a LAGER KEG whereby the stored forecast for week 42 was zero (0) which after pressing the Zero List on the Ribbon will be displayed in the Zero Forecast Box. There are lots of Settings that you can tweak for Zero Forecast reporting on the 'Setup' Sheet

Getting Started

Choosing the Forecast Level & Master Data Headers. Making the Initial Actuals Data Dump & Setting up the Seasonality

To begin creating forecasts you will need to decide on the combination level you wish to forecast, choose the headers/master data to include for updates and to make an initial history data dump. As I go through this article, I will add numbered Action points that will require you to do specific tasks to enable Jellyfish and get you up and running with the Software

What level to forecast?

You can forecast any any level, for example, national or total level (a single combination or product) by depot or by customer by depot. Whatever you decide upon though must be maintained throughout and will be used by the Update Macro to update the stored Actuals. If you forecast at any level other than a national or total level, then any generated forecasts will be aggregated in the Planner Table when viewed/sliced at a national or total level. One reason that one level may be preferable over another may be down to the amount of disperate data (data containing empty buckets) you may have at lower levels which may not create reliable forecasts, so you should analyse your data first and come to an appropriate decision that bests suits your requirements. Another reason that may influence your decision around the type of level to forecast against, is the fact that if you choose to Round the forecast and/or RoundUp tiny forecasts, then you may well end up slightly over-forecasting for low-selling combinations due to the subsequent Rounding of very small values (you can switch this Off or On at any time). For this demo I will be forecasting only by Product with my Products being unique items or Codes ie. 12345, 12346, 12347 etc. Any other data will be master data pulled for the initial data dump and then subsequent data updates. I will set up Jellyfish to allow me to always update by unique Product Code but also to overwrite any master data as I perform updates - the rationale is that I have a Material Status master data field which may change at any time and I always want the most recent change to be in my Jellyfish update. Here is how I have added my initial data dump with the Product Code, Description, Category (used for Seasonality), Pack Type and Material Status (tells me if the product is new, live or being delisted) together with actuals for 01.01.2018, week 01.2018, through to 12.07.2021, week 28.2021:

Initial Data Dump for Forecasting by SKU (Product)

If I wanted to forecast by SKU by Depot, then I may add my initial data dump as per the image below (notice I still have Category for my Seasonality in Column "C" - you can shift this to another Column, but you will need to adjust the formula on the 'Generic Seasonality' Sheet if you do):

Initial Data Dump for Forecasting by SKU (Product) by Depot (Plant or Regional Distribution Location)

What headers or master data to include for the updates?

You have a few possible methods to consider when storing your initial data dump and then subsequently performing updates upon it. Combinations are derived by either letting Jellyfish do the work on all of the fields/headers to create indexes, or explicitly stating which fields/headers you want to use as the indexes. For example you may want to have just one single unique field as I do in my demo ie. Product Code and then include some other master data which you may need to be overwritten whenever you perform an update meaning only when a new Product Code comes down in your data update, will a new combination be generated and added to the bottom of the Model and Planner. You may want to create a combination out of all of the mMaster data every single update, including the Product Code so if any of your update data fields change when performing an update, a new combination is generated and added to the bottom of the Model and Planner. You may want to specify specific fields ie. Product Code and Depot as the indexes, so that if a combination appears that is different to any existing Product Code by Depot combination, then a new combination is generated and added to the bottom of the Model and Planner. There are many possible scenarios that you can consider - it is up to you to make this choice (please do not contact me asking me to do this for you!). One last point - the only scenario that is not possible at this current time, is to explicitly state the fields that should be overwritten, whilst simultaneously specifying the fields that will be used to detect whether a new combination is present. Phew! sounds complicated, but it isn't really, Jellyfish has been set up to allow these different scenarios for flexibility

If choosing to use all of the fields as indexes, then any chosen headers or fields used in your data dump must be the same when performing an update otherwise new lines will be added at the bottom, so this should be considered, for example if you include a product code and a product description and then the decription is changed when updating the next weeks actuals, the code will add a new product to the bottom of the data as it will not match whatever is already there (this may/may not be useful). This goes for the master data as well. If it isn't required here, then leave this part for the Planner as you can bring in master data there for your combinations. You will however require some kind of master data, be it location or customer or category, so that you can create generic seasonality that is used when Jellyfish cannot create seasonality for an individual combination. For this demo I will be using the Category ie. ALE, CIDER, LAGER etc. (the product Category never changes, hence it is a good one to include in the Headers)

Action 1: Go to the Model Sheet and type in your Headers from Columns A:K (they will then be pulled through to the Update Sheet). The New Column is required by the system and should not be changed. Pick one header that will be used for your generic seasonality and for the ease of configuration, make that header the header for Column C, so you would have say Product Code, Description and then a Category. If you want to Forecast at a lower level then add a Depot or Customer field

Making the initial history dump

Befor you do the next step, go into the 'Model' Sheet and select all of the data from underneath your headers that you have just set up, right through to the end of the Cleaning, "A7:AFL16" and press delete to clear down the Range. Once you have decided on how you want to forecast and what master data to include you need to go and fetch all of that data in a report and then Copy & Paste it underneath the headers in the Model Sheet. This initial data dump will then drive all of the calculations and ultimately the final forecast that is output by the Jellyfish Model.

Action 2: Clear down any existing data in the Actuals through to Cleaning sections. Download your actuals history together with the data that you want to match your headers and Copy & Paste as Special values into the 'Model' Sheet in Cell "A7". You can see what I have done below for my 10 example products

Clean down and Add your Initial Data Dump for Forecasting by SKU (Product)

---

Setting up your Seasonality

Before we configure the Model and run the Forecasts for the first time, we need to take a look at the Seasonality. For my 10 Products they either have their own seasonality or will use the average on the 'Generic Seasonality' Sheet. Probably the most important part of Jellyfish aside from the weighted calculations is the seasonality and the way that you, as a user approach setting this up. Seasonality will be given to any level that is calculated by Jellyfish to spread the amount over the year in accordance with some past history. Think of this as the extra hidden volume that creates the shape of your combination throughout the year if it were to be plotted on a Chart. Seasonality may be Seasonal ie. Spring, Summer, Autumn, Winter, influenced by the time of year (for example, heavier Christmas volume) or may include promotions, weather driven events, festivals (if not cleaned) or some other factor that influences the shape at specific periods of the year be they week or month. As I am using a Product that has a Category ie. ALE, CIDER, LAGER etc. then this could be either the shape of my Product using its own history or it could be the shape of my product using the history from all of my ALE Products if it was an ALE. As we are forecasting weekly, seasonality will be created weekly, but it can span seasons as previously explained, so I may have a sustained period of higher volume throughout the summer weeks for my Products in a CIDER Category. These weeks or periods of weeks need to be captured and added back to any derived level. It is up to you to decide how you want to calculate any kind of aggregated seasonality for Products that may not have their own seasonality ie. missing buckets

Action 3: On the Generic Seasonality Sheet add a unique list of your seasonality items overwriting the data in Column A underneath the header, Unique List. These will be the items from Column C in the Model Sheet and will form the basis of generic seasonality for Jellyfish to apply to combinations missing their own seasonality. Drag any formula down to match the length of your list. You can double-check that the count of the seasonality buckets is equal to 52 and that the SUM of the seasonality buckets are equal to 1 on the right-hand side formula

Update 4.09.2021 - as part of beta testing I have now changed Seasonality to look at ONLY 2019. Changes are indicated in the 'Model' Sheet by marking the Text in Red and I have also left the 2-Year 2018/2019 Formula in the Comments for reference/future changing

Please Note: due to COVID disruption I have limited the formula to only look at the years 2018 and 2019. To configure the formula to include more years in your seasonality, you will need to adjust a few formulas. The first is the formula to pickup the periods across the years. Go to the section Calculate Generic Seasonality for NPD'S on the Model Sheet. Click in first formula above the header 1. Double-click this Cell and add in the same periods for any other years that you want to include in your generic seasonality. For example to include 2018, 2019 and 2020 you would end up with =ABM4+ADM4+AFM4. This now references 3 years for your seasonality across the Final Cleaned Actuals values. The second formula to adjust is for the missing seasonality. Go to the section Missing Seasonality. Adjust the formula above the header Missing Seasonality to count the buckets across the years that you want to consider, for example to include 2018, 2019 and 2020 you would end up with =IF(COUNTIF(ABM4:AHL4,"<>0")<156,"Y","N"), notice that the Range increased to cover the extra year and that we added 52 more periods to the 104 to give 156 - 3 years of 52 weeks (at this point we are not concerned with any 53 week years). Again, this covers the Final Cleaned Actuals values. The last formula to adjust is for the seasonal and NPD average calculation. Go to the Seasonal/NPD Average section. Above the Seasonal/NPD ∓ header adjust the formula to AHL4 to include the years 2018, 2019 and 2020 - I have marked the only Range to change below:
=IFERROR(IF(AND($ARN4="N",$ARO4="N"),AVERAGEIF(ABM4:AHL4,"<>0"),(AVERAGEIFS(ABM4:APL4,Jellyfish.FinalCleanedActualsDates,">"&Jellyfish.FlagAsNPDDate,ABM4:APL4,"<>0")*52)*(1+Jellyfish.NPDLevelCoefficient)),0)

That's it for the seasonality calculation changes. It takes a little effort but it is worth it! Here is the 'Generic Seasonality' Sheet for my 10 Products:

The seasonality Calculated for my 10 Products, including the average sasonality formula in "C2" - used if we don't have seasonality for the Product and we don't have seasonality for the Category either

Further information on Seasonality

Okay so sometimes you will create the Seasonality using a Category or a Pack Type or some other Master data field and you may find that it gives a massive spike to one or more weeks throughout the year. This will more than often always be down to the fact that some Products or combinations will have extremely large history sales for specific periods, even if they are Out-phasing Products or combinations. This is because Jellyfish Seasonality uses Final Cleaned Sales Key Figure to create Seasonality and large extreme volumes will skew the Seasonality for some periods

By using Final Cleaned Sales this does give you a chance to Clean the individual Products or combinations for those periods to reduce the significance of them being picked up by the Seasonality calculations. you could run the Products or combinations though an Outlier Cleaning process, something I may consider for future updates

To use a different Column for Seasonality other than Column 'Category' in the default Jellyfish File, follow the instructions in the Section: Using Alternative Seasonality

The Setup Sheet

The 'Setup' Sheet is where all of the configurable options and settings are stored. It uses my Treelist Software, a hierarchal tree node creator for the T.O.C. (Table of Contents). The T.O.C. is fully expandable/collapsible, triggered by clicking on the tiny Nodes. There are 7 Sections, that can be viewed in the image below (you can insert entire Rows anywhere within each Section - not the Node Row itself!):

The 'Setup' Sheet

1.0 Abstract

The '1.0 Abstract' Section contains brief information about the Jellyfish 'Model' and 'Planner' Sheets. It details links to the author and any credits associated with the Software and its resources

The '1.0 Abstract' Section

2.0 Key Symbols & Definitions

The '2.0 Key Symbols & Definitions' Section contains details on symbols used throughout Jellyfish alongside their respective meanings and/or definitions

The '2.0 Key Symbols & Definitions' Section

3.0 Settings

The '3.0 Settings' Section

The '3.0 Settings' Section contains:

3.1 General settings
Laptop mode for visible Sheets - toggles between a Laptop view at 80% Zoom and a PC view at 100% [toggle switch]
Actuals start date - the start date for your actuals history storage [date]
Select the date to flag NPD'S - any combination trading after this date will be flagged as an NPD [date]
NPD trend coefficient. For SKU by CPG used a setting between 0%-2% [default 5%]
Forecast start date - this needs to be adjusted every week prior to pressing the Forecast button on the Model Options Group of the Jellyfish Tab on the Ribbon [date]
Trading weeks to be considered for forecasting - if a Product or combination has not sold within this period then it will be switched off for forecasting [the default is set as 16 weeks] [number]
Week minus 1 weighting (will give more weight for the average values in the first week measured) [default 0.5] [number to one significant place]
Week minus 2 weighting [default 0.3] [number to one significant place]
Week minus 3 weighting [default 0.2] [number to one significant place]
Average level estimate using psuedo levels [default Y] [Y/N drop-down option]
Weeks to be considered for an average if the last 3 weeks buckets are not all >0 [default 12] [number]
Set any negative forecasts to zero [default Set to 1] [Y/N/Set to 1 drop-down option]
Round up tiny values [default Y] [Y/N drop-down option]. Fine tuning: where between [0] and [1] ie. 0 and 1 or 0.5 and 1 etc.
Round the forecast to significant places [default Y] [Y/N drop-down option & number]. The Planner formula now matches this setting for any Planner adjustments, Promo and MI%
The number of weeks to consider for variance reporting formula
Date to begin forecasting. This should be chosen once you have started to forecast and will be used for Error reporting ie. MAPE [date]
Select the type of Actuals to use for the Error and MAPE Formula on the Planner and Detail Sheets. Please Note: if you have a big Customer exit and your forecast has been higher and then dropped, you may need to report using Actuals as opposed to Final CLeaned Actuals [default Actuals]
Select the type of Actuals to use for the Variance Reporting Formula on the Planner and Detail Sheets [default Final Cleaned Actuals]
Always open Jellyfish on the Planner Sheet, with an Unfiltered Table and force a Sheet Calculation [default Y]
Always scroll back to Column 1 and Row 1 whenever the Model Sheet is activated [default Y]

3.2 Update settings (double-click Tick boxes to turn settings on/off)
Jellyfish will make the assumption that any Headers containing data will be unique combinations. If you have master data that changes ie. a Description or a Material Status this would generate a new combination which may not be what you want. To setup Jellyfish to look at specific Headers for unique combinations, add a pipe-delimited string of Header numbers ie. 1|3 for the first Header and the third Header. Example: If you added 1 here then only the first Header is considered and only that Header will be updated into the Model including any values for the Time Series (if the first Header exists in any data already there, it will update values and if it doesn't it will add a new line)
Bespoke Headers. Initially set to 1 for a Product Code (If in doubt or you want all of your Headers to make the unique combination then leave this Blank!) [number or pipe-delimited string of numbers ie. 1|2|4|6 etc.]
Another feature of the Jellyfish Update functionality is that if you are using Bespoke Headers, you have the option of overwriting all of the data when performing updates. This allows you to have a single Product Code as your Bespoke Header to do a match but then to overwrite any of the rest of the data. I added this as I wanted to maintain a match on Product Codes but then also overwrite data for changes to say, Description or Material Status so that I always have the most current in our system when I perform updates and bring this into the Planner
Set this to Y to overwrite all of the data on matches. You must also be using Bespoke Headers to use this functionality [Y/N drop-down option & number]

Post Update Settings - Code will run to do any of the following after an Update is performed
Do not clear down any combinations already marked as 1 = New when performing each update. Untick this to clear all New combinations each update [default ticked] [tickbox, double-click to set on/off]
Remove zeros from the Model sheet. This functionality checks that the whole Cell meets the requirement before removing a zero [default ticked] [tickbox, double-click to set on/off]
Remove negatives from the Model sheet. This functionality checks that the whole Cell meets the requirement before removing a negative [default ticked] [tickbox, double-click to set on/off]
Exclude the zero removal process from the Rolled Forecast time series. This allows you to store zero values for a Rolled Forecast whilst still removing zeros from the Model Sheet [default ticked] [tickbox, double-click to set on/off]

3.3 Scroll & Jump settings
Planner Adjustment Scroll right - used to force a scroll or jump when you press the 'Plan Adj.' Popup Button on the Menubar (Column address) [drop-down option]
Planner Adjustment Scroll down (Row) [number]
Planner Adjustment Scroll back (Row) [number]
Promo Adjustment Scroll right - used to force a scroll or jump when you press the 'Promo' Popup Button on the Menubar (Column address)[drop-down option]
Planner Adjustment Scroll down (Row) [number]
Planner Adjustment Scroll back (Row) [number]
MI Adjustment Scroll right (Column address) - used to force a scroll or jump when you press the 'MI %' Popup Button on the Menubar [drop-down option]
MI Adjustment Scroll down (Row) [number]
MI Adjustment Scroll back (Row) [number]
Turn on Scroll/Jump for the Detail Sheet [default Y]
Detail Sheet Scroll right (Column address)
Use Sendkeys to force {Down} Keystrokes in the Planner Table. Setting this to N will use an alternative method of Jumping down the Table [default = Y]

3.4 Variances settings
Configure the settings below to enable the Variance Box to perform calculations on the variances between actuals/final cleaned actuals and the forecast
Press the Variance List button on the Variances Group of the Jellyfish Tab on the Ribbon to display the Variance Box
The default behaviour of the Variance and Zero Forecast Boxes are to Unfilter the Planner Table when they are opened
You can edit these in real time with the Variance Box open - it will refresh the results. Try it by pressing the Variance List Button on the Variances Group of the Jellyfish Tab on the Ribbon --^
Please Note: the Zero Forecasts Box also uses these settings to allow you to view any Products or combinations that have actuals but zero sales

Select your choice of how you want to compare the actuals with the forecast [default LastWeekActualsLastWeekForecast]
Consider actuals or final cleaned actuals when performing the variance calculation [default FinalCleanedActuals]
Variance percentage format. Wrap in quotes [default "#,##0.0%"]
Column widths. Wrap in quotes, you can pass in all 8 if you like [default "0.55 in; 1.4 in; 0.65 in; 0.85 in; 0.50 in; 0.55 in; 0.55 in; 0.75 in"]
Sort direction. Top or bottom list of variances [default Descending for a Top list]
Criteria to meet. Only bring back Products or combinations that meet this criteria [only used for the variances default 500%]
Number of hits to display. Leave Blank for all - if the number of calculated rows are less than the configuration requirement, Jellyfish will fallback to the calculated row count [default blank]
Save Variance Box settings after closing to the Config.ini file [default Y]
Save Zero Forecast Box settings after closing to the Config.ini file [default Y]
Unfilter the Planner Sheet whenever the Variances or Zero Forecast Boxes are closed [default Y]

Columns to include and their respective configured header names. The system will always use the first 5 Columns in the Model & Planner Sheets
A word of caution: these fields are pulled into the Planner Sheet and the Ranges are built from there, so do not rearrage the Planner Sheet set of 11 Columns!

Required Column Header [this will always be the first Column in the Model & Planner Sheet and will always be included]
Optional Column Header [this will always be the second Column in the Model & Planner Sheet and can be excluded, default ticked]
Optional Column Header [this will always be the third Column in the Model & Planner Sheet and can be excluded, default ticked]
Optional Column Header [this will always be the fourth Column in the Model & Planner Sheet and can be excluded]
Optional Column Header [this will always be the fifth Column in the Model & Planner Sheet and can be excluded,default ticked]
Required Column Header for system calculation [this will always be included]
Required Column Header for system calculation [this will always be included]
Required Column Header for system calculation [this will always be included]
Optional sort by another Column other than variance. It must be a field that has a tick next to it and be selected by its index otherwise it the sort will not be applied [default 3, sort on the third index]
Optional sort direction [default Ascending]

Column Criteria Filtering & Inversion
Here you can specify criteria to either include or exclude
You can configure these Drop-down lists as you want

Filter the Variances by this value
Invert the filter. Setting this to invert = Y will exclude the value from the filter [default = N]

3.5 Menubar Positioning
Menubar 1 positioning (Horizontal Menubar). Create your own absolute positioning for the Menubar using the settings below. Once applied click on any cell on the Planner Sheet to update the positioning of the Menubar

Fix Menubar1 in situ. Setting this to Y will mean that the Menubar never moves and will ignore any of the positioning settings below [ default N]
Menubar Row position [default 27]
Menubar Column position [default 1]
Nudge left pixels. Will nudge the left most position of the Menubar for an absolute horizontal positioning [default 1]
Nudge top pixels. Will nudge the top most position of the Menubar for an absolute vertical positioning [default 0]
Additional Nudge top pixels on scroll right. Will nudge the top most position of the Menubar even further once scrolled anywhere past the Planner Adjustment Column [default 66]

Menubar 1 Auto-settings. Select a Menubar position. Caution changing this will update the settings above. Once applied click on any cell on the Planner Sheet [2 Popup Buttons used to set the required position, Left, Bottom of the Chart or Top, Right of the Chart]

Menubar 2 positioning (Horizontal Menubar). Create your own absolute positioning for the Menubar using the settings below. Once applied click on any cell on the Planner Sheet to update the positioning of the Menubar

Fix Menubar 2 in situ. Setting this to Y will mean that the Menubar never moves and will ignore any of the positioning settings below [ default N]
Menubar Row position [default (right) 27 or (left) 5]
Menubar Column position [default 1]
Nudge left pixels. Will nudge the left most position of the Menubar for an absolute horizontal positioning [default (right) 1 or (left) 50]
Nudge top pixels. Will nudge the top most position of the Menubar for an absolute vertical positioning [default (right) 0 or (left) 1]


3.6 LE (Latest Estimate) Sheet
This allows you to create a simple extract without using the Latest Estimate File whenever you click on the 'LE' Sheet in the Jellyfish File. You have a choice of 12 week, current year, this year next year and full (4 years) but always from the current forecast week. To perform any length of extraction use the Latest Estimate File

Length of plan to display. This is always from the current forecast week. Full will be until the end of the forecast time series [default 12 Week]
Remove Planner Products that are zero in the Planner Table. This will use the first Column and purge anything that is zero [default Y]
Caution! [turning on this option will slow down your latest estimate data]. Remove all Rows where the SUM of the Row is zero. This routine works backwards following the Planner Product zero removal [default N]
Apply an Autofilter to the Range [default Y]
Autofit cell Column widths [default Y]
Sort by Product Code [default Y]
Sort direction. Used if Sort by Product Code is set to Y [default Ascending]
Sort Product Codes as Text. For example, this will allow a sort like this 15392, 3500907,56910 Instead of 15392, 56910, 3500907 when Sorted Ascending [default N]
When to clear down the Latest Estimate data. Setting this to 'on enter' will allow you to use Copy & Paste after leaving the Sheet but will add to the filesize on saving [default on exit]


3.7 Chart Settings
These settings allow you to maintain a Short Horizon view for the Chart on the Planner Sheet that can be Toggled using the Toggle Button on the Planner Sheet

This plus 1 is the start week displayed on the Planner Chart set by the Chart Scroll Index Scrollbar on the Planner Sheet [default 27]
This is the number of weeks displayed altogether on the Chart set by the Chart Zoom Index Scrollbar on the Planner Sheet [default 12]

3.8 AI Reporting & Artificial Intelligence
These settings allow you to adjust artificial intelligence settings for reporting and the Model AI. All reports are output to the REPORT Sheet. All Model adjustments are made to the AI1 Column. This Sheet can remain hidden whilst not in use

AI1 Settings (next 3 wk forecast average divided by the previous 3 wk actuals average % ERROR)
Divides the next 3 wk forecast average by the previous 3 wk actuals average to obtain a percentage. Any product or combination above or below this percentage by the threshold specified will be classified as FAIL and can be adjusted using AI1 [default 40%]
For example, your threshold is 40% and 3wk avg actuals are 6.333, 3wk avg forecast is 9 giving a percentage of 142.11% which is just over the maximum of 140% allowed (100% +/- threshold). 60% is the minimum. Adding AI1 will try to constrain the forecast to within this bounds as best it can. Please Note: it may still fail to fit perfectly, but ususlly the forecast will be better
The bigger the threshold the less products or combinations will be affected but the effect may be more benificial in reigning back on forecasts that are too high or too low using the standard model approach
The report type. You can choose to report on everything or run a report on a specific type of outcome ie. FAIL or PASS [default ALL]
Run AI using either Actuals or Final Cleaned Actuals - applies to both reporting and AI Model adjustments [default Final Cleaned Actuals]
Do not add AI adjustments if a Stored / Rolled Forecast exists for next week. Allows you to model and clean the past/future forecast correctly without AI interference to the level which may be confusing [default Y]

4.0 Updating the Model Time Series

The '4.0 Updating the Model Time Series' Section contains the options to complete a time series update. You can pick either 'Actuals' to update the latest actuals/sales values or 'Rolled Forecast' to update the last weeks forecast values. You always need to select a start and end bucket

4.0 Updating the Model Time Series
Select series to udate [drop-down option]
Select update start date [drop-down option]
Select update end date [drop-down option]

The '4.0 Updating the Model Time Series' Section

5.0 Quick Navigation Jumps

The '5.0 Quick Navigation Jumps' Section contains the links to jump or navigate quickly to specific parts of the 'Model' Sheet

5.0 Quick Navigation Jumps

Model
Manual Overriders
Identify Trading Combinations

Cleaning
Final Cleaned Actuals
Generic Seasonality for NPD'S

Rules for Forecasting
Estimate Level & Trend

The '5.0 Quick Navigation Jumps' Section

The '6.0 Misc' Section is a Blank Section left for you to configure should you need another Section

7.0 System

The '7.0 System' Section contains the options used by the system. In the main part, you should not need to edit these. There may be a time, however that you may want to include more addresses for the jump or navigation columns. You may also want to make modifications to the Jellyfish Software yourself following purchase - you can add any system-based settings to this Section

7.0 System
Time taken to run the Model
01 minute(s), 42 second(s)

Info
Forecasting for CUK using Jellyfish began in week 29 (use this to store additional information regarding Jellyfish for your company, for example when you first started to forecast)

Laptop mode for visible Sheets [system boolean]
Last trading date [date]
Last average date [date]
The week date format to locate the actuals on the Planner Sheet for the Variance Box [reformatted ISO Week number]
The week date format to locate the forecast on the Planner Sheet for the Variance Box [reformatted ISO Week number]
The week date format to locate the forecast on the Planner Sheet for the LE (Latest Estimate) [reformatted ISO Week number]

Planner Adjustment Scroll right value [number]
Promo Adjustment Scroll right value [number]
MI Adjustment Scroll right value [number]
Detail Sheet Scroll right value

Used by the Variance Reporting Formula on the Planner/Detail Sheets

The week prior to the Forecast Week [date]
The formatted past Actuals start week for the variance reporting formula [formatted date]
The formatted past Actuals end week for the variance reporting formula [formatted date]
The formatted past Forecast start week for the variance reporting formula [formatted date]
The formatted past Forecast end week for the variance reporting formula [formatted date]
The formatted future Forecast start week for the variance reporting formula [formatted date]
The formatted future Forecast end week for the variance reporting formula [formatted date]
The formatted past Actuals start week (2 years ago) for the variance reporting formula. Change this for Last Year if required [formatted date]
The formatted past Actuals end week (2 years ago) for the variance reporting formula. Change this for Last Year if required [formatted date]
The formatted future Forecast start week (2 years ago) for the variance reporting formula. Change this for Last Year if required [formatted date]
The formatted future Forecast end week (2 years ago) for the variance reporting formula. Change this for Last Year if required [formatted date]

Cell Addresses used by the Data Validation Drop-down for Planner Adjustment Scroll right (Column Address) [address/string]

The '7.0 System' Section

Running the Model

Running the Model. Introduction to the Menubar and Planner

Now we are almost ready to run the Model for the first time. Before we do this, we need to adjust a setting in the 'Setup' Sheet. Click on this Sheet. This Sheet uses my Treelist Software, a hierarchal tree node creator that allows you to store sections of Text and Images, in essence a T.O.C. (Table of Contents) for Jellyfish. Click the tiny Node in Section 1.0 Abstract and read the information there (if you haven't done already). Now click on the Node in Section 3.0 Settings. I will run over all of the settings later but for now select your Forecast start date. This needs to be adjusted every week prior to pressing the Forecast button on the Model Functions Group of the Jellyfish Tab on the Ribbon. I started forecasting for this demo in Week 29, so I selected '19/07/2021' as my Forecast Start Date. You can add Text on this Sheet as I have done - see my comment in Cell "M3". Leave all of the other settings and press the Forecast button on the Model Functions Group of the Jellyfish Tab on the Ribbon. Please Note: whenever the Model is ran, the Header Range on the 'Model' Sheet will be unfiltered if it is currently filtering and then an Autofilter will be re-applied - you can edit this Header Range in the 'Jellyfish' Code Module:

After Selecting your Forecast Start Date, press the Forecast Button on the Ribbon

Once the Forecast has been Calculated a Carbon Dialog will inform you that the process is complete - click the 'Okay, understood' Button and double-check your Generic Seasonality Calculations which will have been updated

Please Note: Running the Model will take a while for over 1,500 Products or combinations. For example, on a HP Pavilion Desktop PC, with 8 GB RAM and a 4-Core Intel 3.60 GHz processor it takes me 1 minute 40 seconds to run the Model on 1,568 Products. At the moment there is no real way of speeding this up other than partially rewriting some of the Formula - I may try to improve the Jellyfish Model performance at some later date and for a future update. The time taken to run the Model will be shown by the Carbon Dialog and it will be output to the '7.0 System' Section on the 'Setup' Sheet

---

The Planner

Click on the 'Planner' Sheet. If you need to extend the Planner Table to accomodate more combinations, then select the tiny arrow on the right-hand, bottom side of the Planner Table and drag it down the Row Range. If you need to add/remove Slicers, then it is best to reduce this Table right down, add/remove the Slicers and then drag the Table back down the Sheet again - Excel has a serious issue with Tables exceeding hundreds of Columns and adding/removing Slicers. Here is what my Planner looks like after running the Forecast Model:

My Planner Table after running the Forecast Model for my 10 Products

---

The Menubar and the Popup Buttons

Jellyfish uses my Menubar Software, a Menubar with Popup Buttons for navigation that follows you around the Sheet as you work - it's always there, waiting for you! The Menubar has 6 Buttons, 5 usable and one spare for future development. The 'Unfilter' Button unfilters the Planner Table if it has been filtered manually or via any of the Slicers. The 'Plan Adj.' Button navigates to the latest week on the Planner ready for you to make a Planner adjustment to the forecast. The'Promo' Button navigates to the latest week on the Planner ready for you to make a Promo adjustment to the forecast. The 'MI %' Button navigates to the latest week on the Planner ready for you to make a percentage (%) increase to the forecast. The 'Back' Button takes you back to the start of the Sheet and from any of the navigated locations on the Planner. The 'Spare' Button, is just that, a spare Button that you can use to run a separate Macro of your own. If you need more Buttons, just add them - it's easy! When you depress any of these Buttons, the Icon changes from Blue to Orange and the Button takes on a depressed look and feel, signaling that it is being depressed (Please Note: the 'Unfilter' Button Icon changes from Blue to Green and then back to Blue again). When you let it go, the action is performed and the Icon colour changes back to Blue. To edit this Menubar read the usage information here. You will need to Unprotect the Sheet first to select the Shapes. Protect the Sheet again by pressing the 'Unfilter' Button, which simultaneously switches on the Sheet protection

The Menubar navigation 'Jumps' can be configured on the 'Setup' Sheet. Click on the 'Setup' Sheet. Click the Node to expand the 3.0 Settings Section and scroll down to the 3.3 Menubar settings Section. Here you will find the Planner, Promo and MI % navigation Column addresses to pick. Roll these on every week to select the latest weeks. There are also specific Rows that can be selected for the 'Jump', for example, so that you always land in the Row for a Product or 'Jump' back to the exact location again. Test out some of the Buttons by clicking on them and then clicking the 'Back' Button again

To edit the Menubar, Unprotect the 'Planner' Sheet and then Ungroup the Menubar using the Selection Pane. Adjust or add more Buttons and then Group all of the Shapes again. Important! remember to set the Properties of the Grouped Menubar to 'Move but don't size with cells' otherwise it start to shrink or become unaligned as you move about

The 'Review' and 'Eye On' Buttons will both display small popup Messages built from Toasts, my popup toast messaging system (minified version) whenever you press them. The Toast messages have been configured in a 'Carbon' Dialog style to match the standard Dialog style used by Jellyfish and will appear at the bottom, middle of the screen. A blue highlighted action means that the process has gone okay whereas a red highlighted action indicates that the process was not successful. Here is a screen shot of a popup message for the 'Review' Button:

When a Product or combination is reviewed a small 'Toast' message will popup

---

The Views in the Planner - Annual or 'Year to Go' View

The main view that you land on in the Planner will be the Annual or 'Year to Go' view. This view gives you a Snapshot of the 52 weeks of the year if set up in 2021, with series of Final Cleaned Actuals 2018, Final Cleaned Actuals 2019, Final Cleaned Actuals 2020, Final Cleaned Actuals 2021 (current year), Forecast 2021 (current year), Forecast 2022 and Cleaning 2021 (current year). The weeks are shown at the bottom of the Chart together with the Month that the week lands in. These weeks are ISO week numbers. You have the options to toggle some of the years or series On/Off using the tiny Buttons at the top right-hand side of the Chart. If you click on the Slicers the Text at the top left-hand side will change to inform you what is currently being filtered. Here I selected my Spirits first and then a single Product. The filter can be single items, multiple items or multi-select items and will change accordingly. If you select 3 or more items in any of the Slicers, then the Text output will be the Slicer Name and then '[mult.]' ie. "Description [mult.]".

The Planner Annual or 'Year to Go' View demonstrating the Planner Filter

The Views in the Planner - Planner Adjustment View

Click on the 'Plan Adj.' Button to navigate over to the Planner Adjustments Section of the Planner Table. It should drop you exactly into the first week of your Planner (this may need adjusting on the set up Sheet). This view is a lot bigger having 1 week to each Column in Excel, allowing you a better 'Immediate' way of adding Planner adjustments into the forecast. Th Chart has similar series, in fact it is a copy of the Annual View and any Toggled series will also be Toggled On/Off in this view as well. Here you will also see the forecast as numbers in each Column that will match the forecast series labels on the Chart. This is shown in Bold. Above the forecast is the actuals shown in a lighter grey. Below the forecast are the Promo additions and the MI % additions.

The Planner Adjustment View

Calculations

All of the Calculations Explained

All of the Calculations are on the 'Model' Sheet. You can use use the Cursor in the first Row and press CTRL+Right Arrow to jump to each main Calculation Section in turn. Most of the Calculation Sections have comments in the Cells

Actuals History Dump
This is where you perform a one-off, Copy & Paste of your initial history data dump in order to give Jellyfish data to begin modelling your portfolio of Products or combinations. You need to run a report to extract actuals/sales history from whatever system you are using. You can of course begin without any history if you want and rely on the sales updates to trigger and begin forecasting

You should setup your Headers in the first 11 Columns of the 'Model' Sheet - do not overwrite the 'New' Header Column. Actuals should begin in Cell "M7" through to "NL7" and down the subsequent Rows. It is advised to Copy & Paste in actuals without any negatives and with zeros set to Blank - you have options in the 'Setup' Sheet to remove these whenever you are performing an update. Please Note: you do not need to 'Update' your intial data dump - this should be a one-off Copy & Paste

Stored/Rolled Forecast
This is where you can store the Forecast prior to Rolling into a new Week once you begin to Statistically Model. If you perform an update into this Time Series, then the values will be taken as the Forecast for the past. If there are no values then the Statistical Model will also calculate the past and these values may change each week - in effect, changing the past, so when you are happy to begin Forecasting, update your Forecast for the Week just gone using the Time Series Updating functionality of this Software

Cleaning Actuals
This is where you can clean any history up/down or for missing buckets to enable Seasonality. You can add values or formula here (formula will not be copied down the Range during the forecast calculation). You can add negatives here to 'Clean down' actuals or positive numbers to 'Clean up' actuals. Cleaning is shown on the 'Planner' Sheet on the Charts. Cleaning is useful to remove outliers, for example a Festival or Event where volumes may be abnormally large. Cleaning these volumes will ensure that the Model does not react to the sudden higher actuals that is will process when it is ran

Manual Overriders
Manual overriders give you the opportunity to bypass some of the Model calculations and force specific scenarios. Caution: these need to be manually maintained - for new combinations they must be manually populated!

- NPD Overrider
Allows you to toggle between an NPD and a non-NPD

- Manual Trading [set to N]
Allows you to turn a combination off for the interim. This can be useful for new launches when you can switch off all of the calculations and just add launch MI in the MI section of the Planner until you want to use the statistical forecast

- Seasonality & Level Percentages (%)
Allow you to control the amount of the Seasonality and the Level applied to the Forecast Calculations, normally set to between 0%-100%

- Overrider Reason
Allows you to specify a reason behind any of your overrider decisions. I added this to allow me to track whenever I 'Lock in' a future Rolled Forecast or overrider Forecast so that I can Slice this on the 'Planner' Sheet and I know that the future Forecast is not a system generated Forecast but one that I have loaded into the 'Rolled Forecast' Key Figure. You can of course add any Alphanumeric reason into this Field for tracking purposes

Final Cleaned Actuals
These are the cleaned Actuals that will be used in the forecast calculation - they should not be edited

Calculate Generic Seasonality for NPD'S
These formula calculate the generic seasonality used by the 'Generic Seasonality' Sheet and are calculated here on the 'Model' Sheet first (both individual weeks and total are used by the 'Generic Seasonality' Sheet). They are fully adjustable, but take care to check your formula if you do adjust them. Due to COVID I have limited this to the years 2018 & 2019 (which just adds 2 Cell Ranges and then a total at the right-hand side); for more years adapt the formula above period 1 ie. M5+BM5+DM5 (week 1 of 2018, week 1 of 2019 & week 1 of 2020)

Please Note: we use 'Final Cleaned Sales' for these calculations as they will always pick up Blanks and change them into zero and we will include cleaning also

Flag NPD'S
Flag NPD'S. We will flag a combination as an NPD if it has only traded after the FlagAsNPD Date which can be adjusted on the 'Setup' Sheet

Missing Seasonality
A check to see if we have any missing buckets across the years that we will be using for the seasonality calculations. If we have missing buckets than we cannot build a Product's own seasonality and we will mark it here as "Y" (Missing Seasonality)

Please Note: we use 'Final Cleaned Sales' for these calculations as they will always pick up Blanks and change them into zero and we will include cleaning also

Missing Generic Seasonality
A check to see if we have any generic seasonality. If we don't we will use the DEFAULT average. This means that we have no seasonality for the Product or combination, no generic seasonality for the Category (I use Category but you could use Depot) and therefore we have to use an average built on the 'Generic Seasonality' Sheet, which just averages through all of the generic seasonality. If you still have no seasonality, you can create a single line of your own seasonality in the 'Generic Seasonality' Sheet at the top

Please Note: we use 'Final Cleaned Sales' for these calculations as they will always pick up Blanks and change them into zero and we will include cleaning also

Seasonal NPD Average
Derive the seasonal average for the years that we are considering to create the Seasonality Indexes. If the combination is not an NPD and has seasonality then we will just derive a normal average for the years we are considering otherwise we will derive an average after the 'Flag As NPD Date'. All calculations exclude zeros. If the Product has its own seasonality the calculation here will simply be an average of the years we are considering in the 'Calculate Generic Seasonality for NPD'S' formula - the exclude zeros part of the formula would be irrelvant as the Product

Create Initial Seasonality Indexes
Here is where we will create the 'Seasonality Indexes' used to shape our Products or combinations. We will always create 52 weeks (we will not create a week 53) and we will total these to SUM as '1' ie. 100%

Identify Trading Combinations
Identify trading combinations. This matches the Range for a Product or combination in each Row from the Last Trading Date to the Forecast Start Date minus 1 week. If a Product or combination is Trading within this period ie. we have at least 1 bucket that is not zero (0), then we will mark it as "Y", it is Trading and if we find that there are all zeros (0) for the period, then we will mark it as "N", it isn't Trading; we have no actuals. We also consider the Manual Overrider in this calculation and if this is not Blank then the setting will take this value as its default value ie. "Y" or "N". The 'Last Trading Date' is stored in the 'Setup' Sheet in Section '7.0 System' and calculates a Date using the 'Trading weeks to be considered for forecasting [the default is set as 6 weeks]' ie. '=Jellyfish.ForecastStartDate-7*Jellyfish.TradingWeeks'. In simple terms, if the Product or combination is not found to be trading then it will be switched off at some point later in the forecast calculations

Reactive Weighted Recent Averages
The first thing we do here is to pull in the last 6 weeks of trading volumes into 6 Columns, WK-6, WK-5, WK-4, WK-3, WK-2 and WK-1

Bucket Count
This is the count of values for the last 3 weeks of Actuals and will be used when anlysing the rules that will enable us to choose the relevant average for the level of a Product or combination. So if we have the last 3 weeks of values then the Bucket Count will be 3. If we have no values then the Bucket Count will be zero

Last X Week ∓
This formula will calculate the last X weeks average for when we may not have all of the buckets for the last 3 weeks to create a recent weighted average. The last X weeks is shown adjacent by the 2 Formula that identify the Columns for the start or 'from' until the end or 'to' ie. '$AMN' to '$AMY' being 12 weeks - consider the last 12 weeks for the average. This setting is maintained in the 3.0 Settings Section in the 'Setup' Sheet and is the weeks to be considered for an average if the last 3 weeks buckets are not all >0 [default 12]. The Defined Name or Named Range used is called 'Jellyfish.AverageWeeks'. The Defined Name or Named Range that calculates this is called 'Jellyfish.LastAverageDate' and uses the Formula '=Jellyfish.ForecastStartDate-7*Jellyfish.AverageWeeks'

Trading ∓
An average calculated from the Last Trading Date using the number of Trading Weeks, meaning for example we want to consider just the last 6 weeks Trading in order to switch a forecast on/off and/or use for an average. This setting is maintained in the 3.0 Settings Section in the 'Setup' Sheet and is the Trading weeks to be considered for forecasting [the default is set as 6 weeks]. The Defined Name or Named Range is called 'Jellyfish.TradingWeeks'. The Defined Name or Named Range that calculates this is called 'Jellyfish.LastTradingDate' and uses the Formula '=Jellyfish.ForecastStartDate-7*Jellyfish.TradingWeeks'

Weighted ∓
This is the last 3 weeks weighted average calculated from the weightings settings on the Setup Sheet. This should be the most common level weighting used by Jellyfish. You can specify how much weight you want to apply to the week minus 1 (last week), the week minus 2 or the week minus 3. For example you may want to give more weighting to the last week of Actuals. Jellyfish defaults are set up to do just this, they lend 50% weight to week minus 1 (last week), 30% weight to the week minus 2 and 20% weight to the week minus 3. The Defined Names or Named Ranges that do this are 'Jellyfish.WeekMinus1Weighting', 'Jellyfish.WeekMinus2Weighting' and 'Jellyfish.WeekMinus3Weighting'

Use Rules
This is a switch based upon the Bucket Count and Last X Week ∓. This rule will ultimately be used to derive the Annual Level and will indicate which rule we are using in the code

1. We have all 3 buckets for the last 3 weeks and will use the weighted average (weighted ∓)
2. We do not have all 3 buckets for the last 3 weeks but we do have an average for the last X weeks therefore we will use that average (Last X Week ∓)
3. We do not have all 3 buckets for the last X weeks and we do not have an average for the last X weeks therefore we will use the Last Trading Date which may be 4, 6, 12, or even 26 weeks as the average (Trading ∓)

The default Last Trading Week is set at 12 weeks and there is a possibility this will still be zero (0)

Psuedo Level Last X Week ∓, Psuedo Level Trading ∓, Psuedo Level Weighted ∓
These are the initial level calculations for the 3 averages above multiplied by 52 weeks







*************************** NOT SURE YET ABOUT THIS!!!!!!!!!!!!!! Estimate Level
Here is where we derive the level estimate for the combination. If the combination is an NPD then we will give it our previously calculated NPD average, otherwise we will derive a new level. If week minus 1 is less than or equal to zero we will use the average built from the 'Last Trading Date' until the Forecast Start Date minus 1 week divided by the seasonality to get the level

XXX


XXX


XXX


XXX


XXX

Adjusting the Forecast

Making Planner, Promo & MI % Adjustments

Adjusting the Forecast - Before Modelling

There are many ways to adjust the forecast before it has been statistically calculated by the 'Model' Sheet. You can use the Manual Overriders to change the Seasonality and Level by a percentage. You can use the Manual Overriders to force the combination to act like an NPD or to switch it off from Trading. To view these options go to the 'Setup' Sheet and click the 'Manual Overriders' link in the '5.0 Quick Navigation Jumps' Section to jump to the settings on the 'Model' Sheet.

---

Adjusting the Forecast - Post Modelling

There are many ways to adjust the forecast after it has been statistically calculated by the 'Model' Sheet

To add Planner Adjustments to the forecast click on the 'Planner' Sheet. Select a Product or number of Products/combinations using the Slicer or by filtering the Planner Table and press the 'Plan Adj.' Popup Button to navigate to the current week on the Planner. Enter values into the Planner Table rows either positive or negative. You can see the results of your adjustments in the Chart above you. As you move left or right through the weeks in the Planner the Menubar will scroll alongside you. When you have finished making adjustments, to go back to the main Planner view, click the 'Back' Popup Button on the Menubar

Switching Off the Forecast

Multiple Methods to Switch Off the Forecast

Switching 'Off' the Forecast - Multiple Methods

There may be times when you want to switch off the forecasting for a number of reasons, for example when doing a Product Launch or perhaps for an interim period. In Jelly fish there are a number of different methods to achieve this depending on your aim

Let's consider a couple of scenarios. For example, say that a Product had already started selling and that Jellyfish had created a forecast but you wanted to turn this off because you had specific intel regarding its launch that you wanted to add as a Planner Adjustment or that you wanted to stop an established Product from forecasting, knowing actuals may still come through like an Out-phasing Product. Another scenario could be that you want to turn off the forecast for a set number of weeks or years

To switch off the forecast for a Product Launch or an Out-phasing Product, use the navigation in the 'Setup' Sheet, 5.0 Quick Navigation Jumps to jump to the 'Manual Overriders' Section on the 'Model' Sheet. Once there you can either set the 'Trading' Column for the combination to 'N' (Y is not currently used, the default is Blank, but Y will have the same result as Blank) or you can adjust the percentage in the 'Level' Column to zero 0%. Running the Model again (press the Forecast Button on the Ribbon), will result in the Product not having a forecast. Jelly fish will always turn off the entire forecast both past and future, hence you should always roll and store the forecast each week if you want to keep the forecast history for a Product/combination - this should be done as part of demand planning, regular weekly tasks

A Launch Product that has just began Trading will get a Forecast after a single sale

We Switch it Off by Setting the Trading Overrider to N (or Level to 0%)

And this is the Result - No Forecast ready to Add MI (a launch estimate from commercial)

The Final Launch Product in Plan View with 12BT per week added. This will no longer Statistically Forecast until you tell it to by removing the Trading Overrider Flag

To switch off the forecast for a set number of weeks or years click on the 'Planner' Sheet and select a Product or number of Products/combinations and press the 'MI %' Popup Button on the Menubar to jump to the MI % Section of the Planner. For the Weeks that you want to exclude a forecast, enter -100 for any weekly buckets that you want to Blank - notice I have not used actual percentages here, this is a personal choice, you could format the Cells and then use -100 which will then default to -100% which will do the same thing but will be visible as a percentage - that's up to you! Running the Model again (press the Forecast Button on the Ribbon), will result in the combination not having a forecast for the required number of weekly buckets

Another method to switch off the forecast would be to clean back the actuals to zero by adding negative cleaning values in the 'Model' Sheet and run the Model again, although the methods already outlined above are the easiest and most final way of preventing a forecast from being calculated by Jellyfish

Setting Up Products

Various Methods used to Setup Products for Forecasting

Setting up New Products - Adding the Products & Manually Adding Projected Volumes

To set up new Products, either use the Update process to add the new combination automaically when it has actuals or manually add the new Product into the bottom of the 'Model' Sheet and set the 'New' Column to 1 - this will then Flag the Product as New in the New Slicer on the Planner Sheet. Type or Copy & Paste as Special Values the relevant Master data underneath the Headers that you set up for Jellyfish (I use a Master data report in the Latest Estimate.xlsb File that has a helper Column to give me my Master data whenever I receive new Products to set up. I Copy & Paste this directly into the 'Model' Sheet). Go to the Manual Overriders Section in the 'Model' Sheet and drag the Range for these down (only the Manual Overriders, as these will not be updated by the modelling). Leave the Overriders Blank or as 100% by default. If you do not want jellyfish to do any modelling whilst the Product is in its Launch phase, then you can either set the Level Overrider to '0%' or you can set the Trading Overrider to 'N'. Now press the 'Forecast' Button to run the modelling. Add your Launch volumes in the 'Planner' Sheet as you would when making regular Planner adjustments. Typically I add around 12 weeks maximum, but you can add for an entire year if you have these projections

---

Setting up New Products - Adding the Products & Using Cleaning to Create a Forecast (ie. Creating Making a Supersession)

Okay so add your Products or combinations as above. We will now use another functionality of Jellyfish to create a forecast. We will use the Cleaning. So go into the 'Model' Sheet and locate the Cleaning Section or go into the 'Setup' Sheet and use the 'Cleaning' link in the '5.0 Quick Navigation Jumps' Section to jump directly to the Section in the @model' Sheet. Scroll along and add your volumes into the past. You can also add Cleaning volumes to the future and then when jellyfish forecasts from that point, these will also be taken into consideration. This is a pretty quirky, but efficient way of creating a forecast and you can use this to make a Supersession to Copy & Paste history from a like Product into a New Product (successor) as Cleaning to emulate the predecessor. Then you can switch off the predecessor completely or phase it out using the MI % Section of the Planner. I cleaned back this Launch Product for the weeks 21.2021 until 28.2021 (past) and then from 30.2021 until 31.2021 (future). Here is the result after modelling - the Product will not have its own seasonality, nor will it have any Generic Seasonality, but it will instead use an average of all of the current Products to build its seasonality. This will result in a higher forecast for 2022 (the next year) as it is still acting like an NPD:

Cleaning Results to Create a Forecast - we are Forecasting for Week 29

Of course if you clean this Launch Product right back from 2018, it wil then build its own seasonality and creat Generic Seasonality via the Category it is in and will not need to use the average seasonality. So the new Chart after cleaning back 2018 and 2019 looks like this - this will not result in a higher forecast for 2022 (next year) as it is no longer considered an NPD. You can also see 2018 and 2019 Actuals on the Chart:

Cleaning Results to Create a Forecast after Cleaning Right Back from 2018 through to 2019 (our Seasonality Calculation Years) - we are Forecasting for Week 29

Another method to Clean Products is to add the weekly level for the last 3 weeks to the 'Model' Sheet. If someone gives you a weekly level or a fixed Annual level, then you can add the weekly level for 3 weeks and then you can let the system forecast the Product ongoing without having to worry about a launch process (albeit, it would still require monitoring). For an Annual amount, divide it by 52 weeks and do the same add the weekly volume for 3 weeks. If the Product belongs to an existing Category, then it will receive the seasonality for that Category (or combination). Run the Forecast modelling again and take a look at the results. Here I added 100 per week and it generated a forecast for the next 4 years at or around this level but using the average seasonality - obviously using seasonality means that some weeks the forecast will be stronger than other weeks and this may or may not be what you want for a New Product launch. You can also see 2022 forecast as I have switched this on using the Toggle Buttons:

Cleaning Results to Create a Forecast (Adding 3 Weeks Level to the Past of 100 per week) - we are Forecasting for Week 29

Typically you would anticipate a New Product Launch outstripping demand, especially if it goes really well. If you like you add a percentage ontop of the launch Cleaning or Planner adjustments to phase it up higher to start with. If you use a technique that considers the Product to be an NPD then it will make the next year forecast increase by a set amount that is configured on the 'Setup' Sheet. The setting is called the 'NPD trend coefficient' and can be found on the '3.1 General settings' Section on the 'Setup' Sheet. It is set by default to 5%

Overriders

There are two kinds of Overriders that can be used with Jellyfish, 'Manual Overriders' and 'Forecast Overriders'

Manual Overriders

Manual Overriders are system-based settings in the 'Model' Sheet that allow you to overrider normal settings used by the Model. You can use the Manual Overriders to force a Product or number of combinations to act like an NPD or to switch them off from Trading. You can also tweak the seasonality and level via percentage (%) overriders. To view these options go to the 'Setup' Sheet and click the 'Manual Overriders' link in the '5.0 Quick Navigation Jumps' Section to jump to the settings on the 'Model' Sheet. Caution: these need to be manually maintained - for new combinations they must be manually populated!

The Manual Overriders that can be adjusted in the 'Model' Sheet

NPD Overrider

Allows you to toggle between an NPD and a non-NPD

Trading Overrider

Allows you to turn a combination off for the interim. This can be useful for new launches when you can switch off all of the calculations and just add launch MI in the MI section of the Planner until you want to use the statistical forecast

Seasonality & Level Percentage (%) Overrider

Allows you to control the amount of the Seasonality and the Level applied to the Forecast Calculations, normally set to between 0%-100%. Adding 110% to the Seasonality Overrider would increase the strength of the Seasonality by a further 10%. Setting the Level Overrider to 90% would reduce the annual level used by the system by 10%, thereby reducing the forecast level ongoing

---

Forecast Overriders

Forecast Overriders lock in forecasts for the future. They can be added to the 'Rolled Forecast' time series to determine exactly what is forecast for future buckets. Consider the scenario where you wanted to ensure stock was ordered correctly for the festive period on over 200 WINES that have long lead times. Sure you could add in MI on top of the levels, but what if actuals dropped a little coming into the peak time - your overall forecast would also drop down, stock would not be ordered as to anticipated levels and this may not be what you want to happen. You want to know that precise volumes are ordered, regardless of actuals - you would also put in a review process at some point and then adjust the forecast overriders or switch back to statistically modelling

Adding a Forecast Overrider to the Rolled Forecast Time Series - we will add overriders to weeks 48 to 52

The result in the Planner View - overriders are now in situ for weeks 48 to 52

Rolling Jellyfish

Rolling Jellyfish each week to Store the Forecast as History and then Complete the Actuals Update

Store the Forecast History or 'Rolled Forecast'

Every week you need to perform a 'Rolled Forecast' Update in Jellyfish if you want to store the forecast put out for the current week as history. The way that Jellyfish works is to forecast automatically for past and future, then each week the forecast for the current week needs to be stored and that becomes a past forecast that will never change. Go to the 'Setup' Sheet and in the '4.0 Updating the Model Time Series' Section, next to 'Select series to udate' select the Time Series to update which will be 'RolledForecast'. Then select the start and end date weeks ie. RF 29-2021, RF 29-2021 (meaning update start and end for week 29, 2021. If you want to store zeros for the Rolled Forecast, remember to tick the 'Exclude the zero removal process from the Rolled Forecast time series'. This allows you to store zero values for a Rolled Forecast whilst still removing zeros from the Model Sheet [default ticked]. If you don't tick this option, then zeros will be removed during any update if you also have the 'Remove zeros from the Model sheet' option ticked and a Blank will be left. If there is a Blank, then the statistical forecast calculated at any time will be used instead of the stored Rolled Forecast

Choosing the Roll Weeks

Click on the 'Setup' Sheet

Tick the 'Exclude the zero removal process from the Rolled Forecast time series' option if you have the 'Remove zeros from the Model sheet' option ticked

Click on the 'Update' Sheet. It should now be primed for the update for the chosen weeks

Priming the Update Sheet

Enter your Products and any Master Data and then press the 'Update' Button on the 'Model Functions' Group of the 'Jellyfish' Tab on the Ribbon to update the time series for the rolled forecast. Check and then click through any Dialogs that are presented to complete the process

Prmopt for the Forecast Roll Process

Confirmation that the Forecast Roll Process is Complete

That's it, now you have stored the Rolled Forecast for the last week. Roll the 'Forecast start date' Drop-down on one week and you are ready to re-run the Model when you have taken your latest actuals and updated those - the 29 shown below at the left-hand side of the Drop-down will change to 30 in my example

Roll the 'Forecast start date' Drop-down on one week

---

Completing the Actuals Update

Complete the update for the latest Actuals

To complete the actuals update, first run whatever report you ran to get your initial data dump of historic actuals but for the last week or last weeks . Please Note: whenever the Update is ran, the Header Range on the 'Model' Sheet will be unfiltered if it is currently filtering and then an Autofilter will be re-applied - you can edit this Header Range in the 'Jellyfish' Code Module

Variance Box

Using the Variance Box to detail Variances

Jellyfish allows you to configure the 'Setup' Sheet to use a 'Modeless' resizeable Variance Box to display variances between the forecast and actuals in either largest or lowest order. The Variance Box can be pushed to the side and used to select and filter Products or combinations by rank, so that you can clean or adjust them. Jellyfish offers you the option of Select your choice of how you want to compare the actuals with the forecast [default LastWeekActualsLastWeekForecast]. You can also choose between 'ThisWeekActualsThisWeekForecast' and 'ThisWeekActualsNextWeekForecast'. Depending on the number of Products or combinations and the configuration, you can use the arrow keys whilst on the Variances Box to go up and down the list faster than a manual Mouse Selection. The Variance Box will persist until you close Jellyfish and allows you to switch between Sheets, for example you can go onto the 'Detail' Sheet and Select a Product from the Box to drill down the Product and Chart on this Sheet - the 'Detail' Sheet shows all of the Cleaning for the current year (and possibly the next year, this is still in development)

The Variance Box configuration allows you to choose specific Master data fields. These can only be built from the first 5 Columns of the Model/Planner Sheet Master data. You can tick the optional Headers/Fields to include the Master data that you want to display within the Variance Box. You also have the options to filter by specific criteria ie. 'ALE' for the Category field or invert the filter, ie. to exclude 'ALE' by selecting 'Y' or 'N'

The Resizable Variance Box

The Resizable Variance Box expanded to detail the Configuration with a Sort by Variance and by Category (Optional Sort Field) showing Variances > 500% with the Top Product Selected in the Planner

Please Note: You can set up the Variances Box using all of the settings in the Section 3.4 Variances & Zero Forecasts settings on the 'Setup' Sheet. As well as the options to choose how you want to compare the actuals with the forecast, you can also choose whether to use Actuals or Final Cleaned Actuals in the calculations, the variance percentage format, the Column widths of each Header (Wrap in quotes, you can pass in all 8 if you like [default "0.55 in; 1.4 in; 0.65 in; 0.85 in; 0.50 in; 0.55 in; 0.55 in; 0.75 in"]), the Sort direction (Top or bottom list of variances) and the Criteria to meet (Only bring back Products or combinations that meet this criteria), the Number of hits to display (Leave Blank for all - if the number of calculated rows are less than the configuration requirement, Jellyfish will fallback to the calculated row count). You can also decide whether to Save the Variance Box settings into a Config.ini File and whether to unfilter the Planner Sheet whenever the Variances Box is closed. Please Note: the Criteria to meet is a very useful setting as if you have a lot of variances, say because Festival volumes were not included in your statistical forecast, then you can set this to '500%' or '1000%' to restrict the variances brought back in the Variances Box

There is a Section on which Columns to include in the Variance Box and their respective configured header names. The system will always use the first 5 Columns in the Model & Planner Sheets. You can overtype these, for example instead of 'Description' you could enter 'Product Details' and this will be used for the second Header when the Variances Box is displayed. 4 of the Columns are optional and can be set by ticking the tiny Tickboxes next to the settings. You have the option to sort by another Column other than variance. It must be a field that has a tick next to it and be selected by its index otherwise it the sort will not be applied [default 3, sort on the third index] - the indexes are shown on the left-hand side of the settings. Alongside the optional sort by another Column option, you also have an optional sort direction for this Column that can be set to either Ascending or Descending

Finally, other settings include 'Column Criteria Filtering & Inversion'. Here you can specify criteria to either include or exclude. Please Note: You can configure these Drop-down lists as you want because they are not taken from any Defined Names or Named Ranges. Use the Data Validation on the 'Data Tools' Group of the 'Data' Tab on the Ribbon to create your lists. These lists will be set to Blank in the release of Jellyfish but the lists will remain in situ for an example. Basically, these options allow you to restrict a Variance Box by say, 'ALE' Category variances only and the 'Inversion' setting allows you to exclude 'ALE' - so in other words bring back variances for every other Category except ALE. This is useful is you have a lot of variances to go through

Zero Forecast Box

Using the Zero Forecast Box to detail Zeo Forecast Products

Jellyfish allows you to configure the 'Setup' Sheet to use a 'Modeless' resizeable Zero Forecast Box to display actuals actuals versus the forecast in either largest or lowest order. The Variance Box can be pushed to the side and used to select and filter Products or combinations by rank, so that you can clean or adjust them. Jellyfish offers you the option of Select your choice of how you want to compare the actuals with the forecast [default LastWeekActualsLastWeekForecast]. You can also choose between 'ThisWeekActualsThisWeekForecast' and 'ThisWeekActualsNextWeekForecast'. Depending on the number of Products or combinations and the configuration, you can use the arrow keys whilst on the Zero Forecast Box to go up and down the list faster than a manual Mouse Selection. The Zero Forecast Box will persist until you close Jellyfish and allows you to switch between Sheets, for example you can go onto the 'Detail' Sheet and Select a Product from the Box to drill down the Product and Chart on this Sheet - the 'Detail' Sheet shows all of the Cleaning for the current year (and possibly the next year, this is still in development)

The Zero Forecast Box configuration allows you to choose specific Master data fields. These can only be built from the first 5 Columns of the Model/Planner Sheet Master data. You can tick the optional Headers/Fields to include the Master data that you want to display within the Zero Forecast Box. You also have the options to filter by specific criteria ie. 'ALE' for the Category field or invert the filter, ie. to exclude 'ALE' by selecting 'Y' or 'N'

The Resizable Zero Forecast Box

The Resizable Zero Forecast Box expanded to detail the Configuration with an Optional Sort by Category

Please Note: You can set up the Zero Forecast Box using all of the settings in the Section 3.4 Variances & Zero Forecasts settings on the 'Setup' Sheet. As well as the options to choose how you want to compare the actuals with the forecast, you can also choose whether to use Actuals or Final Cleaned Actuals in the calculations, the Column widths of each Header (Wrap in quotes, you can pass in all 8 if you like [default "0.55 in; 1.4 in; 0.65 in; 0.85 in; 0.50 in; 0.55 in; 0.55 in; 0.75 in"]), the Sort direction (Top or bottom list of zero forecasts) and the Criteria to meet (Only bring back Products or combinations that meet this criteria), the Number of hits to display (Leave Blank for all - if the number of calculated rows are less than the configuration requirement, Jellyfish will fallback to the calculated row count). You can also decide whether to Save the Zero Forecast Box settings into a Config.ini File and whether to unfilter the Planner Sheet whenever the Zero Forecast Box is closed. Please Note: the Number of hits to display is a very useful setting if you have a lot of zero forecasts because you can restrict the number of hits returned to say, 20

There is a Section on which Columns to include in the Zero Forecast Box and their respective configured header names. The system will always use the first 5 Columns in the Model & Planner Sheets. You can overtype these, for example instead of 'Description' you could enter 'Product Details' and this will be used for the second Header when the Zero Forecast Box is displayed. 4 of the Columns are optional and can be set by ticking the tiny Tickboxes next to the settings. You have the option to sort by another Column other than variance. It must be a field that has a tick next to it and be selected by its index otherwise it the sort will not be applied [default 3, sort on the third index] - the indexes are shown on the left-hand side of the settings. Alongside the optional sort by another Column option, you also have an optional sort direction for this Column that can be set to either Ascending or Descending

Finally, other settings include 'Column Criteria Filtering & Inversion'. Here you can specify criteria to either include or exclude. Please Note: You can configure these Drop-down lists as you want because they are not taken from any Defined Names or Named Ranges. Use the Data Validation on the 'Data Tools' Group of the 'Data' Tab on the Ribbon to create your lists. These lists will be set to Blank in the release of Jellyfish but the lists will remain in situ for an example. Basically, these options allow you to restrict a Zero Forecast Box by say, 'ALE' Category zero forecasts only and the 'Inversion' setting allows you to exclude 'ALE' - so in other words bring back zero forecasts for every other Category except ALE. This is useful is you have a lot of zero forecasts to go through

Deleting Rows

Deleting Rows in the Model & Planner - Info

Due to the fact that you can add Comments, Manual Planner Adjustments, Promotional volumes and percentage MI changes, the best way to ensure alignment of the 'Model' and 'Planner' Sheets following the removal of a Product or range of Products/combinations, is to use one of the methods below to filter both Sheets by the same Products or combinations and then delete the entire Rows on both Sheets

Filtering a List of Products and Deleting the Rows in the Model & Planner (2 quick, fool-proof methods)

Create your list of Products or combinations to be deleted from Jellyfish in a separate Sheet or Filter them on either the 'Model' Sheet or 'Planner' Sheet. If you created a list on a separate Sheet select the Products and Copy them to the Clipboard. Then filter both the 'Model' Sheet and 'Planner' Sheet using the Buttons, 'Filter Model by List' and 'Filter Planner by List' on the 'Filter' Group of the 'Jellyfish' Tab on the Ribbon by pasting in your Products and pressing 'Filter'. Now both Sheets are filtered, select all of the filtered Rows on each Sheet and delete the entire Rows - you should not need to run the Forecast Generation, but you can do so if you wish. Close Jellyfish and then reopen it

An even quicker way is to filter your Products any way that you like on either the 'Model' Sheet or the 'Planner' Sheet (using the Slicers if you like) and then Copy the filtered Products to the Clipboard. Now using any of the two Buttons, 'Filter Model by List' or 'Filter Planner by List' on the 'Filter' Group of the 'Jellyfish' Tab on the Ribbon, paste in your Products and press 'Filter' in the Dialog for the Sheet that you didn't originally filter your Products or combinations on. Again select the entire Rows on both Sheets in turn and delete them - you should not need to run the Forecast Generation, but you can do so if you wish. Close Jellyfish and then reopen it

The Menubar

A Menubar is used by Jellyfish for all of its navigation on the Planner Sheet and for specific tasks that may be repetitive or needed wherever you are in the Planner. The Menubar will be with you at all times, whereever you go. Try moving around with the cursor or using the Scrollbars and then selecting a cell - the Menubar will move itself neatly into a position that doesn't get in the way of your work, but will be right there when you need it. It is glued to the left-hand side of the Sheet - if you want to adjust this go into the 'shPlanner' Code Module and adjust the values for the Menubar's left and top positions '.left = ActiveWindow.VisibleRange(27, 1).left + 1' and '.top = ActiveWindow.VisibleRange(27, 1).top'. Changing the 27 to another number will change the Row position or vertical position and changing the 1 to another number will change the Column position or horizontal position. I added in '1' to the left position just to align it right next to the Excel Sheet Window

Click this link to read more about my Menubar

The Menubar used by Jellyfish

Unfilter

This Button unfilters the Planner Table on the 'Planner' Sheet if it is currently being filtered manually or via any of the slicers

Plan Adj.

This Button navigates to the 'Planner Adjustment' section for the latest week on the 'Planner' Sheet. The Column and Row to navigate can be adjusted each week in the '3.3 Menubar settings' Section of the '3.0 Settings' Section on the 'Setup' Sheet

Promo

This Button navigates to the 'Promo' section for the latest week on the 'Planner' Sheet. The Column and Row to navigate can be adjusted each week in the '3.3 Menubar settings' Section of the '3.0 Settings' Section on the 'Setup' Sheet

MI %

This Button navigates to the 'MI %' section for the latest week on the 'Planner' Sheet. The Column and Row to navigate can be adjusted each week in the '3.3 Menubar settings' Section of the '3.0 Settings' Section on the 'Setup' Sheet

Back

This Button navigates back to the main Planner View on the 'Planner' Sheet. The Row to navigate can be adjusted each week in the '3.3 Menubar settings' Section of the '3.0 Settings' Section on the 'Setup' Sheet

Reviewed

This Button marks an individual Product or combination as reviewed on the 'Planner' Sheet - it will be set to "Y" and available to slice. I use this for my daily reviews of over-selling or under-selling Products. Once done, this field is pulled into the Latest Estimate.xlsb file and I can filter and submit details about the changes I have made for the day. As we currently use a Third Party provider for forecasting some of the network I may need to send them overriders for total volumes - this is how I do it. You can clear this list using the 'Clear Review List' Button on the 'Review Lists' Group of the 'Jellyfish' Tab on the Ribbon

Eye On

This Button marks an individual Product or combination as eye on on the 'Planner' Sheet - it will be set to "Y" and available to slice. I find this useful for when I am going through a list of Products and come across something I think well let's adjust for the next couple of weeks as it's overselling a fair bit (I may not have time to check what outlets are taking more than usual), but I want to be able to come back to it in a week or so to review. I may find a Product coming to the end of its 12 week launch (if I have explicitly loaded a launch adjustment and prevented it from statistically modelling). Of course then, once reviewed, it can be manually cleared in the Column on the right-hand side of the Jellyfish Planner Table. You can clear this list using the 'Clear Eye On List' Button on the 'Review Lists' Group of the 'Jellyfish' Tab on the Ribbon

The 'Review' and 'Eye On' Buttons will both display small popup Messages built from Toasts, my popup toast messaging system (minified version) whenever you press them. The Toast messages have been configured in a 'Carbon' Dialog style to match the standard Dialog style used by Jellyfish and will appear at the bottom, middle of the screen. A blue highlighted action means that the process has gone okay whereas a red highlighted action indicates that the process was not successful. Here is a screen shot of a popup message for the 'Review' Button:

When a Product or combination is reviewed a small 'Toast' message will popup

Ribbon Commands

Ribbon Commands are Buttons that appear underneath the 'Jellyfish' Tab on the Ribbon in Excel. They can be used as Action Buttons to perform a number of Daily and Weekly Tasks when using Jellyfish

Model Functions - Update

Performs a time series update for either the latest 'Actuals' weekly values or the stored 'Rolled Forecast' values depending on the Start and End Weeks specified in the '4.0 Updating the Model Time Series' Section on the 'Setup' Sheet. A prompt animation using my Carbon Software will be displayed asking you whether or not you are happy updating the time series according to the settings in the 'Setup' Sheet and a further Carbon dialog will be presented back to you informing you that the update process is complete and that any New combinations are now flagged as 1 underneath the 'New' Header in the 'Model' Sheet and sliced by the 'New' Slicer on the 'Planner Sheet'

Model Functions - Forecast

Performs the Model calculations that are used by Jellyfish to statistically forecast by copying all of the Model formula down the entire range in the 'Model' Sheet. It also updates some of the Planner Table formula by copying the Defined Name or Named Range 'Jellyfish.PlannerFormula1' down the Planner Table each time the process is ran, changing the Formula to Special Values so as to save memory and reduce the file size

Filter - Filter By List (Model Table & Planner Sheet)

You can filter the Planner Table and the Model Sheet by a list of Product Codes using this Ribbon Button (or whatever is in the first Field of the Planner Table). Click the 'Filter Model By List' or the 'Filter Planner By List' Button on the 'Filter' Group of the 'Jellyfish' Tab on the Ribbon and Copy & Paste a list of Product Codes by selecting/copying them to the Clipboard and then pressing the 'Paste' Button, followed by the 'Filter' Button in the Filterlist Dialog. Pressing the 'Unfilter' Popup Button on the Menubar will unfilter the Table again. If using this technique on the Model Sheet, the Autofilter that filters the Model Sheet will be filtered by the Products that you Paste in

The Filter By List Commands - press either of these 'Filter Model By List' or 'Filter Planner By List' Buttons on the Ribbon to Filter by a list of Product Codes

The Filter By List Dialog - press the 'Filter' Button on the Dialog to complete the process for either the Model Sheet AutoFilter or Planner Table

The Filtered Planner Table - an example of Filtering the Planner Table by 2 Product COdes using the Filter By List Buttons

Review Lists - Clear Review List

You can clear the Planner reviewed combinations list for anything that has been marked using the 'Reviewed' Popup Button on the Menubar. Click the 'Clear Review List' Button on the 'Review Lists' Group of the 'Jellyfish' Tab on the Ribbon. All Clear Lists Buttons now have a Prompt that must be satisfied before clearing any list data

The Clear Review List Command - press the 'Clear Review List' Button on the Ribbon

Acknowledgement that the Review List has been cleared

Review Lists - Clear Eye On List

You can clear the Eye On combinations list for anything that has been marked using the 'Eye On' Popup Button on the Menubar. Click the 'Clear Eye On List' Button on the 'Review Lists' Group of the 'Jellyfish' Tab on the Ribbon. All Clear Lists Buttons now have a Prompt that must be satisfied before clearing any list data

The Clear Eye On List Command - press the 'Clear Eye On List' Button on the Ribbon

Acknowledgement that the Eye On List has been cleared

Variances - Variance List

You can display the Variances List (or Box) by clicking the 'Varinace List' Button on the 'Variances' Group of the 'Jellyfish' Tab on the Ribbon

The Variance List Command - displays the Variance Box in accordance with the settings on the 'Setup' Sheet

The Detail Sheet

Introducing the Detail Sheet - used for Key Figure Drill-down, Error & MAPE Reporting

The 'Detail' Sheet has been added as an alternative planner view, so that you can see more information on the various Time Series including the Cleaning by week in a more compact view. It will also feature full Error Reporting including MAPE in a future build. It is a development Sheet whereby, you can tweak and make it look how you want it to. It can be used in conjuction with the 'Variance Box' to view more details on overselling and underselling Products or combinations. All of the Time Series on here comes from the 'INTERFACE' Sheet

For now, let's have a look at what information is currently available in this View

The Detail Sheet

You have:
Final Cleaned Actuals 2019 - you can change this to be any that is featured on the 'INTERFACE' Sheet
Actuals 2021
Cleaning 2021
Final Cleaned Actuals 2021
Planner Adj.
Promo
Forecast 2021
Error
%

Creating a Supersession

How to create a Supersession (replacement) - Method 1, Cleaning the Successor & Switching off the Predecessor Immediately

A Supersession is basically just a New Product (the Successor) that replaces an Old Product (the Predecessor) so that the New Product (the Successor) takes on the Forecast of the Old Product (the Predecessor). This method demonstrates how to create a Supersession that ends the Predecessor and starts the Successor immediately by Cleaning

First of all we will Clean the Successor. Ensure that the New Product or combination is in the Model and Planner Table. If it is not then add it to the bottom of the 'Model' Sheet together with any Master data. To demonstrate this, I will add my New Product 99999 to the 'Model' Sheet like this (I filtered by the Old Product 99998 first):

Add the New Product to the 'Model' Sheet

Okay now I will scroll along to the Actuals for the Old Product (the Predecessor) and copy the history from 01.2018 until the current week into the New Product (the successor) Cleaning Section like this:

Copy the Actuals for the Predecessor into the Cleaning for the Successor

Then I will scroll back to the Manual Overriders Section and set the 'Trading' Manual Overrider to 'N' for the Old Product (the predecessor) like this - by the way you will need to Copy & Paste the Manual Overriders down for the New Product as they are not copied by the Model when it is ran:

Set the Manual Overrider Trading flag to N on the Predecessor

That's it, now run the Model by pressing the Forecast Button on the 'Model Functions' Group of the Jellyfish Tab on the Ribbon. There is no need to unfilter the Autofiltered Range as this will be done via the Code

Run the Model

Go to the 'Planner' Sheet and select the Old Product (the Predecessor) - you will see that it is switched off imeediately and there is no Forecast going out for it anymore (because I only started Forecasting in week 29 for this Model I only have week 29 until week30 as the Rolled Forecast or Forecast History - anything previously generated by the Model for the Forecast, has now vanished:

The Predecessor 99998 is Switched Off Immediately

Select 99999 the New Product (the Successor) and you will see that it is now Forecasting immediately as per the Predecessor from the current week onwards

The Successor 99999 is Forecasting Immediately

You can also copy the Rolled Forecast for the Predecessor if you like but I do not do this. I then mark the Products as 'Supersession' on the Menubar so that I know what has been replaced if I need to revisit them at some point. Only the Cleaning for 2021 is shown on the Chart and the Cleaning for the past years becomes historic Actuals

How to create a Supersession (replacement) - Method 2, Cleaning the Successor Phase-in 12 Weeks & Switching off the Predecessor Phase-out 12 Weeks

To create a Supersession whereby you phase-out the Predecessor and phase-in the Successor over 12 weeks, repeat all of the steps for Method 1 but copy the next 12 weeks Forecast for the Old Product (the Predecessor) into a Blank Sheet or Workbook first. Then to phase-in the Successor, simply select the Product - I chose 99999 and press the 'MI %' Popup Button on the Menubar to jump to the Section. Enter values to phase-in the Successor by using negative phasing over the 12 weeks, reducing the size of the negative phasing as you approach the established final weeks of the phase-in, like this:

The Successor 99999 phased-in over 12 Weeks using reducing negative MI % Key Figure

And here is the result of the phase-in:

The Successor 99999 phased-in over 12 Weeks

For the phase-out on the Old Product (the Predecessor) you need to take the 12 week Forecast from the Old Product (the Predecessor) that you copied into a Blank Sheet or Workbook and paste this into the Rolled Forecast for the Old Product (the Predecessor) like this and then run the Model:

The Predecessor 99998 Copy the 12 Week Forecast into the Rolled Forecast Key Figure

Now press the 'MI %' Popup Button on the Menubar to jump to the MI % Section and add increasing negative MI adjustments to phase-out the Product like this:

Adjust the Predecessor 99998 using Increasing Negative MI % Adjustments

And here is the result of the phase-out over 12 weeks:

The result of the phase-out for Predecessor 99998 over 12 weeks

Using Alternative Seasonality (New Jellyfish Setup)

How to Setup Jellyfish for a Sales History Extract that uses an Alternative Seasonality Column than the Standard

So I have to set up Jellyfish for another part of the Business at work. To do this I first of all clear down Jellyfish. I take a Copy of Jellyfish and rename it (for this example I have copied Jellyfish into a new Folder and used its original name). I then open the file and on the 'Model' Sheet I select from "A7" through to Cell "AFOXXX" where XXX is the last Row down. I then simply press delete. I also clear down the Overrider Reasons Column by selecting underneath the Header and the entire Range down and then pressing delete. This will clean all of the data including Actuals History, Cleaning and any Manual Overriders specific to Products etc. I then Copy the '100%' Overriders down from Cells "AFO7:AFP7" so that they all read '100%'. At this point in time I don't worry about the extra Rows down the Range or the Planner Sheet yet. If there are any AI adjustments in the 'AI1' Column these can also be deleted

Next I adjust any Columns on the Model Sheet so that they have the correct naming conventions. In this case I over-typed the 'Misc2' Column with 'Brand' as I want to also bring the Brand into Jellyfish. I then clicked the top of the Cell to Autofit the Cell Width so that I can see my modified 'Brand' Header. So now I go an get my Actuals History (or Sales History) and then Copy & Paste this as Special Values underneath the respective Model Headers and Weeks, starting in Cell "A7" on the 'Model' Sheet. Remember that you should never have a Week 53! Jellyfish does not calculate any Week 53. So now that the Actuals have been copied into the 'Model' Sheet I then Delete any Rows on the 'Model' Sheet underneath my last Product Row. Select all the empty Rows right down the Sheet if you like and delete them. So now the 'Model' Sheet should look something like this (I used Freeze Panes here, but you can remove this if you like):



So far, so good! I have left any negatives and zeros as I have both settings ticked in the 'Setup' Sheet to remove these once we do an update. You can remove them if you like. Now you will see the Pack Type Column. This is the Column that I want to use for my Seasonality, therefore I will need to make some significant changes to a couple of Formulas and build a unique set of Pack Types. Let's begin by creating a unique list of Pack Types. I use the Advanced Filter as shown below in a new Sheet I added to get my list (Copy the 'Pack Type' Column to another Sheet first). Copy this list into the 'Generic Seasonality' Sheet underneath the 'DEFAULT' Header. You can also Sort the list if you like so it is a specific order. Here is the method to create a unique list which will be output to Cell "D1":



And then here is the list I Copied into the 'Generic Seasonality' Sheet. Notice that the Formulas will not currently pick up the values for the Seasonality. I also deleted the last Row which contained an old Category value. I sometimes have issues with Copy & Paste in Excel with Freeze Panes on, so you can remove Freeze Panes in the 'Generic Seasonality' Sheet if you like. Notice that I have Sorted my Pack Type list ;)



Change the Formula in Cell "B2" and then "B3" on the 'Generic Seasonality' Sheet to '=SUMIF(Model!$D:$D,'Generic Seasonality'!$A3,Model!AVQ:AVQ)' swapping the Column 'C' for 'D' in the Formula and flick this down. Change the Formula in Cell "C3" on the 'Generic Seasonality' Sheet to '=IFERROR(SUMIF(Model!$D:$D,'Generic Seasonality'!$A3,Model!ATQ:ATQ)/$B3,0)' swapping the Column 'C' for 'D' in the Formula and Copy across to Column "BB" and then down the Range. Check that the Seasonality Indexes add up to 1 like this:



Next, go into the 'Model' Sheet and in Cell "AVW4" change the reference to '$C4' to '$D4' as per the image below (Please Note, this Formula may have moved a Column or so, check the Formula matches, but it may have slightly different references!) - drag this across to Cell "AXV4":



In Cell "AVU4" change the reference to '$C4' to '$D4' as per the image below:



That's it. Now run the Model by pressing 'Forecast' on the 'Model Functions' Group of the 'Jellyfish' Tab on the Ribbon. On the 'Planner' Sheet reduce or extend the Planner Table as required. If you have any Planner adjustments left over from a previous version, then you need to clear down these values along with any comments that you may also have left for a previous version. Select Table Cells and press Delete to clear the contents. Right-click and Delete Comments. Repeat for the Promo adjustments and MI% adjustments. If you get any '#HREF' Errors then go to the Section after the MI% Section and flick down the Formula in the first Table Row - select them from the end of the MI% Section right across until the last Formula, then flick down. The Errors should dissapear. You may also want to check and clear any Planner Adjustments, Promo Adjustments and MI % Adjustments that may be left over from the default version of Jellyfish or from a Copy that has already been used for Forecasting. Go to each Section and select across and down the Table and press delete. Right-click and select delete comments to remove comments. Run the Model again if there are any other Errors just to align everything. Lastly use the Buttons on the 'Jellyfish' Ribbon Tab to clear down any lists. Now you should have a new shiny, working Jellyfish file like this:

The LE Sheet

The LE (Latest Estimate) Sheet can be used to View the Final Forecast in Jellyfish at any time

After adding Planner adjustments or running the Model you can set up and use the 'LE' Sheet to quickly view the latest estimate (Forecast). The 'LE' Sheet has a number of different options available that you can tweak on the 'Setup' Sheet - the default options are:

Length of plan to display. This is always from the current forecast week. Full will be until the end of the forecast time series [default 12 Week]
Remove Planner Products that are zero in the Planner Table. This will use the first Column and purge anything that is zero [default Y]
Remove all Rows where the SUM of the Row is zero. This routine works backwards following the Planner Product zero removal [default N] Caution! [turning on this option will slow down your latest estimate data output]
Apply an Autofilter to the Range [default Y]
Autofit cell Column widths [default Y]
Sort by Product Code [default Y]
Sort direction. Used if Sort by Product Code is set to Y [default Ascending]
Sort Product Codes as Text. For example, this will allow a sort like this 15392, 3500907,56910 Instead of 15392, 56910, 3500907 when Sorted Ascending. This is useful for Pasting into APO [default N]
When to clear down the Latest Estimate data. Setting this to 'on enter' will allow you to use Copy & Paste after leaving the Sheet but will add to the filesize on saving [default on exit]

Press the 'LE' Sheet Tab. The Code will create the extract for you on the Sheet as you activate it, using the settings on the 'Setup' Sheet. You should see the output of your Final Forecast beginning at the current week - the Forecast Week. The Latest Estimate is not Aggregated, this means that the output is performed at the same level as the Jellyfish Planner Table combinations, so if you forecast by Product by Depot, then the output will be at this level. Aggregation SQL may be added in a later version - for now, you could build a simple Pivot Table that picks up and aggregates this data

Updating Versions

Updating all of your data into a newer version of Jellyfish

There is no standard updating Tool for when a newer version of Jellyfish is released. The process is a manual one that can be done by following the instructions below - in general, it involves copying all of the data from the first part of the 'Model' Sheet and then all of the adjustments and comments from the Planner Table on the 'Planner' Sheet, together with changing any settings that persist in your older version

Caution: if you or anyone else has modified your version of Jellyfish outside of the scope of its default behaviour and/or Worksheet content and layout, then this may not work and it is up to YOU to manage the process yourself - please DO NOT contact Support!

Updating Jellyfish - Copying the Model Data

Please Note: it is always best to start with the released version of Jellyfish which only features a minimal number of Products - this is easier to transfer from an old version into a new version

Open your original version of Jellyfish.xlsb and the latest Jellyfish.xlsb version (you will have to rename your own version to enable both files to be opened by Excel at the same time). Select the 'Model' Sheet on each Workbook. Copy all of the data from your old version from Cell "A7:AFLXXX" where 'XXX' is the Rows down and Paste as Special Values into the new version, Cell "A7". The reason that we don't copy data further is that sometimes the Manual Overriders may be modified in newer versions of Jellyfish. First drag down all of the Manual Overider Cells in the first 5 or so Columns in the newer version to match the range of Products or combinations that you just transferred. Now update any Manual Overriders taking the values for each Column in your old version and Pasting over the same Columns in the newer version as Special Values - this means that all of your data for the first part of the update is done. If there are new Manual Overriders in the newer version, remember to copy down the default Overrider values in these new Manual Overriders right down the range

You can run the Forecast Generation here if you like
Caution: if you are using alternative Seasonality for your old version you will have to also update this manually - see the Section: Using Alternative Seasonality (New Jellyfish Setup)

Refining The Model - AI

Static Forecast AI is included in Jellyfish to interogate the Model for Reporting and to adjust the Level Estimate

AI1 is a routine to send out a report on the forecast for all of your Products into the 'REPORT' Sheet and to allow you to further apply a fix to the Level Estimate if required. AI1 measures the next 3 weeks forecast average gainst the previous 3 weeks actuals average according to a tolerance ie. 40%. It spits out whether the Product passes the test, fails the test or skips the test. A skip is made due to a division by zero or by actuals and forecast being null (zero). You can use this AI to find out issues for Products or combinations forecast by the model. For example this may indicate that Jellyfish is using the incorrect Rule or that the Rule could be changed. It may mean that more cleaning is required to bring the forecast in line. It may show Products or combinations where Seasonality is really influencing the future forecast and could be lowered. To run a report on AI1, press the 'Run AI1 Report' Button on the 'Reporting' Group of the 'Jellyfish' Tab on the Ribbon

An AI1 fix can be applied to the model to adjust the level to try to bring back more of your portfolio into alignment with recent actuals. Following your regular forecast generation you can add AI1 fix to the Level Estimate by pressing the 'Forecast + AI1' Button on the 'Artificial Intelligence' Group of the 'Jellyfish' Tab on the Ribbon. After adding AI1, you should choose 'No, keep values' whenever running the forecast generation each time. When you roll a week, remove the values and then press the 'Forecast + AI1' Button again to apply a new AI fix

Using Jellyfish for Other Processes

Creating Forecasts out of Nothing

You can literally create forecasts out of thin air when using Jellyfish. For example, consider this scenario. New Business has entered your Company in a big way and a merger forces another 2K of SKUS into your portfolio. Your boss gives you just 24 weeks of sales data at the lowest level by SKU, by Plant, by Customer and with very limited Master Data. They ask you to a) Generate forecasts at the lowest level and aggegate levels for this new Business part for the next 2 Years at least, b) Setup and create all of the Forecasts on a weekly model in APO. Sound like a challenge - Jellyfish can help!

We begin by going through every Product and validating the Master Data. Once we have a defined list of SKUS, at least ONE Field we can use for Seasonality, say Pack Type ie. PET/CAN/KEG/CASK etc. or Category ie. WINE/SPIRITS/ALE/CIDER etc. and the sales, use a Blank Jellyfish and follow these steps:

1. Copy & Paste in the SKUS with the Seasonality Field in 'Column 3' in the Jellyfish Model Sheet together with the limited available sales data. See the two images below:

Copy & Paste the initial SKU data and Master Data


Copy & Paste the initial 24 Weeks Sales Data


2. Setup your Jellyfish like this (go to the Settings Sheet):
- Increase the Trading weeks to 24 to try to capture everything and not switch anything off
- Lengthen your Weeks to consider for an average if the last 3 buckets of sales data are all zero to say, 8
- Set Round up tiny values to N (Version 1.3 includes optional setting for fine tuning rounding)
- Set Round the forecast to N (Version 1.3 includes optional setting for fine tuning rounding)

Tweak the Jellyfish Settings on the Setup Sheet


3. Important! Ensure that you add Seasonality indexes that are built from similar full sales history for like Category's or Pack Types and Copy & Paste these into the Generic Seasonality Sheet

Add the Seasonality in order to Create the Forecasts


4. Now Run the Model and create all of your forecasts

The Forecasts created using the Seasonality Indexes (Annual view)


The Forecasts created using the Seasonality Indexes (Short Horizon view

Forecasting at the Lowest Level

Setting up the Rounding for Optimal Results

Special Macros Section for Admin

Setup a Blank Jellyfish

Take a copy of your Jellyfish File. Open the Jellyfish copy in the VBE (Visual Basic Editor) or do View->Macros. Go into the 'mdJellyfish' Code Module and scroll right down to the bottom. Here you will find the 'BlankJellyfish()' Macro

Run this Code to clear down any data and reset/clear default Settings in the Model Sheet and clear down any Planner adjustments, Promo and MI% in the Planner Sheet. Now begin the process of creating a new Jellyfish. All you need to do now is to add new data to the Model Sheet (extend/delete any Rows), check your Seasonality and then run the Forecast Generation. You will have to copy down the Top formula on the Right-hand side of the Planner Table back down the Table Range as this is static formula that the Code no longer touches. Oh and obviously extend/delete Table Rows to match the Model Sheet

FAQ

Q. What do I do if my level is way too high - can I adjust it or adjust the Seasonality?
A. Yes, it is likely caused by the Seasonality Indexes. If the level in general looks too high (with Seasonal uplifts matching historic uplifts) you can use the Manual Overrider 'Pick Rule' ie. 1, 2 or 3, to either change the Rule, which may fix the issue as you can lower the level using a lower average or 'Seasonality' ie. -40% which would keep the shape but bring down the levels

---

Q. If I have some one-off events that take volumes on a number of Products for a few weeks in a year, will I have to clean the Actuals back to zero so that they don't forecast or mess up the Seasonality Indexes?
A. They will have 'Missing Seasonality' therefore will not be considered into the Seasonality Indexes. You should set these type of Products or combinations to "N" using the 'Trading' Manual Overider and then they will also not forecast based upon any generic seasonality - you can still add Planning volumes, adjustments, Promo and MI% for the events without actually statistically forecasting (they will be manually planned volumes)

---

Q. Do Variances come back in the Variances Box after cleaning them?
A. It depends on what you choose from the Drop-down Combo Box for the setting 'What you choose to consider as Actuals when performing the variance calculation' in the '3.4 Variances & Zero Forecasts settings' Section on the 'Setup' Sheet. If you clean the Product or combination and you have chosen 'Actuals' then you will still see the variance even after cleaning unless you overwrite the Actuals values in the 'Model' Sheet. If you clean the Product or combination and you have chosen 'Final Cleaned Actuals' then the variance will disappear from the Variance Box once you click the 'Variance List' Button on the 'Variances' Group of the 'Jellyfish' Tab on the Ribbon again

---

Q. Do Zero Forecasts come back in the Zero Forecast Box after adding a forecast?
A. Again, it depends on the choice that you select for the setting 'Select your choice of how you want to compare the actuals with the forecast' in the '3.4 Variances & Zero Forecasts settings' Section on the 'Setup' Sheet. If you compare Last Week, you cannot change the forecast for the past, so they will still persist unless you edit your 'Rolled Forecast'. If you are comparing the current week forecast with the Actuals then yes, manually adjusting them will mean that pressing the Zero Forecasts Button on the Ribbon again will no longer display the Products or combinations as Zero Forecasts

---

Q. Can I add in more formula to the Model Sheet, for example another couple of Columns of formula?
A. Yes, the only thing you need to be aware of is that you will may to adjust the Defined Name or Named Range called 'Jellyfish.CalculateForecastFormula' if it is a Column at the start or end of the range. The Defined Name containing all of the formula is copied down the 'Model' Sheet range wheneever the Forecast Generation is ran. Here I will add in 2 Columns of formula before the Header 'Bucket Count' in the 'Model' Sheet. Scroll along to where you want to add the Columns. Enter your new formula in the Cells with a Border in Row 4. Press the 'Forecast' Button on the 'Model Functions' Group of the 'Jellyfish' Tab on the Ribbon. Here is a Screen shot of 2 new 'Average' formulas called 'New 1' and 'New 2' I added prior to running the Forecast Generation:



Please Note: if you have any spare Columns without any formula in them, the code will still work fine as long as the Columns are within the Defined Name. You can add a simple formula like ="" to the formula Cell so that it copies Blanks down the range if you wish but this is not neccessary

---

Q. The forecast level doesn't appear to follow the Seasonality for the chosen year. I set up my Seasonality for 2019 but the level doesn't fit previous Seasonality for the weeks
A. Check the 'Missing Seasonality' Column in the 'Model' Sheet. It will be most likely that the Product or combination is missing a weekly bucket - even 1 bucket set to zero (0) will force Jellyfish to use Generic Seasonality, which may not be the same shape as your Product or combinations shape. See the images below, the first shows the issue, the second shows the reason behind the forecast (a single bucket was missing for 2019 Final Cleaned Sales) and the system defaults to Missing Seasonality = Y

To fix this issue, clean back the missing bucket and run the Forecast generation again - the Product will then use its own Seasonality for 2019, the third image shows you the result of the cleaning

---

Q. I suddenly get a huge uplift on a Range of Wines for one week in the year (seems to apply to all of my Wines or most of them) - what is going on?

A. Jellyfish is only as good as the History you supply! Check and Clean back any large volumes for that week for past years considered for Seasonality (it may only be a handful of Products) as these will also affect the Seasonality calculations. When the annual level is divided by the adjusted Seasonality for those Products that you Clean, it will thebn reduce for all Products that use the Generic Wines Seasonality

---

(insert column/insert extra column/inserting columns/inserting extra columns/insert an extra column)
Q. Can I insert extra Columns in the Planner Table?

A. Yes, but add them to the end of the Table if you don't want formula copied down the Table Range or before the 'Reviewed' Table Column if you need the formula copied down the Table Range. If you need the formula copied down the Table Range then after inserting the Column before the 'Reviewed' Table Column, you will need to adjust the Defined Name or Named Range called 'Jellyfish.PlannerFormula2' to take account of the extra Column. Expand the hidden Formula and Header Names first on the Planner Sheet - the Rows are Grouped on the left-hand side. You will also have to adjust part of the Code. Go into the 'mdScrollingMenubar' Code Module and scroll down. Change the 'Const ReviewColumn As String = "BPM"' Code to the new Column for the 'Reviewed' Table Column as it will have moved on one or more Columns. Then go into the 'mdJellyfish' Code Module. Scroll down to the 'Recalculate' Subroutine and change the ':BPL' part of the Code for 2 lines, 'shPlanner.Range("BBM37:BPL37").AutoFill Destination:=shPlanner.Range("BBM37:BPL" & LastRow)' and 'shPlanner.Range("BBM37:BPL" & LastRow).Value2 = shPlanner.Range("BBM37:BPL" & LastRow).Value2' to the new last Column before the 'Reviewed' Table Column. You will also need to manually copy your formula from the formula cell and paste it into the Planner Table for the first time, after which the 'Forecast' or Model run will then copy all of the formula for inserted Columns down the Table Range. One last thing to do, is to include your new Header in both the hidden Row above the Table Column and the new Table Column itself

---

(insert row/insert extra row/inserting rows)
Q. Can I insert an extra Row in the Planner Sheet?
A. I would advise against it, but yes, the Formula are dynamic, so you can add an extra Row in the Planner Sheet. Important! Do not add any extra Columns into this Sheet as the Formula copied down refer to Column "A" which should always be the first Column in the Planner Sheet and also the first Table Column

(inserting slicer/inserting another slicer/takes ages/taking too long)
Q. Inserting another Slicer into the Planner Table takes ages?

A. Excel using very large Tables takes a long while to process an additional Slicer, especially with 2K of Product lines in the Planner Table. A workaround for this is to reduce the Table down to 2 lines or 2 Table Rows using the little handle on the bottom right-hand side of the Planner Table, insert the Slicers and then drag the Table back down to its previous Row Range. Excel will leave any Table data in the cells and then pick this back up again when you drag it back down. Now just run the Model again to check that all looks okay

---

(adding/adjustment/adding adjustment/adding an adjustment/making adjustments/making an adjustment/make a adjustment/make an adjustment/adjust/adjustments/adj.)
Q. Adding Planner or Promo Adjustments is not raising the Forecast level?

A. Check whether you are trying to enter these adjustments in the past! Jellyfish will use the Rolled Forecast if you store it and no changes will be made to the past where stored values exist

Making Planner Adjustments in the past will not change the Forecast (image shown before I add an Adjustment to week 29, I am now forecasting for week 30, not 29)

Making Planner Adjustments in the past will not change the Forecast (image shown after I try to add an Adjustment to week 29, I am now forecasting for week 30, not 29, hence no increase is made to the forecast in week 29)

Q. What is the Detail Sheet used for?

A. The 'Details' Sheet has been added to provide a fairly easy view of any time series you want against a copy of the Chart used in the 'Planner' Sheet. I added it so that I can begin to build something to view the annual volumes for Products or a range of Products. You can build upon this for your own needs. The 'Details' Sheet can be deleted or hidden if not required

(setting changed/setting change/settings change/changed setting issue)
Q. I opened up Jellyfish and adjusted the Variance Box sizes. I didn't save Jellyfish, however when I opened Jellyfish back up again, my Variance Box settings had changed?
A. This is standard behaviour by design - if you open up the Variance or Zero Forecast Boxes and you have chosen in settings to Save the settings to the Config.ini File, then this will be done regardless of whether or not you Save the Jellyfish File itself ie. every time you adjust and then close the Variance or Zero Forecast Boxes the setting will be Saved to the Config.ini File

Screen Shots

Jellyfish v1.1

A Screenshot of Jellyfish Forecasting GAS Cylinders on a Short Horizon Chart (2018 Actuals are turned off)

A Screenshot of Jellyfish Forecasting GAS Cylinders on a Full Year Horizon Chart (2018 Actuals are turned off)

A Screenshot of Jellyfish Software using a Short Chart Horizon in PC Mode at 100% Zoom

A recent Screenshot of the Detail Sheet with Variance Formula for the latest Jellyfish Software in PC Mode at 100% Zoom

A Screenshot of Jellyfish in action detailing the Resizeabe Variances Box

The Jellyfish Planner View detailing a highly Seasonal Product that only mainly sells in the Summer

The Jellyfish Planner Adjustment View

The Jellyfish Menubar - the Forecast includes MI% adjustments to lower it from Week 35 as a Key Customer Exits

The Detail Sheet - includes Cleaning, Planner Adjustments, Promo and Error Reporting that will be enhanced in future builds

Jellyfish running the Forecast Generation with a jellyfish Background. When this disappears, the Forecast Generation is complete

Videos

A first look at Jellyfish. Music Eating Concrete (Original Mix) by Reinier Zonneveld Get it Here · view this Jellyfish video on YouTube

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

Important! This Software is very complex and uses VBA and a large variety of Excel Formula for an incredibly low price. Therefore aside for support if it fails to work, I am UNABLE to offer any level of configuration or development for any of the aforementioned Files. This includes modifying any of the Workbooks, Formula, Code, Layout, adding to any of the Sheets, creating Pivot Tables to aggregate data etc. Please use your own knowledge base and support to achieve any additional goals that you may be seeking!

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

Tips

* You have the ability to toggle Chart Series on/off and alos add/exclude them in the Chart itself. For example, I like to keep the Cleaning 2021 toggled on, but I exclude it in my main Chart on the Planner Sheet. I leave it on for all of the rest of the Charts

* When updating Manual Overriders, should you want to update a large amount of Products quickly with a similar overrider value, first copy the range of Products and then press the 'Filter Model by List' Button on the 'Filter' Group of the 'Jellyfish' Tab on the Ribbon to filter the 'Model' Sheet by specific Products. Now scroll or jump to the Manual Overriders Section in the 'Model' Sheet. Press Caps Lock on and then simply go down the range usng the arrow keys pressing either "N" or "Y". For fields that take percentages (or for the Y/N fields) you can copy a Cell and then use SHIFT, Left Mouse click to select a Range and then press ENTER to Paste the Cell value down - this will work for multiple, filtered Products

* To move the Slicers about or adjust the Chart, unprotect the Drawing Objects on the 'Planner' Sheet by selecting 'Unprotect Sheet' on the 'Protect' Group of the 'Review' Tab on the Ribbon. Then move the Slicers wherever you want or adjust the Chart settings. There is a small snippet of VBA Code, a Subroutine called 'UpdateProtection()' in the 'mdJellyfish' Code Module that you can run to Protect Drawing Objects on any ActiveSheet - simply select any Sheet with Drawing Objects and run the Code

* To navigate really fast between Sections in the 'Model' Sheet you can either use the links on the 'Setup' Sheet in the 5.0 Quick Navigation Jumps & Online Help or click on the 'Model' Sheet and simply press CTRL+Right Arrow in the Cell "M1", which is 'Actuals History Dump' Section to take you to the 'Stored/Rolled Forecast' Section. Doing this back and forth enables you to jump rapidly between the main Sections on the 'Model' Sheet. I typically use both types of 'Jumping', preferring the links for Cleaning and Manual Overriders and then when interogating anything to do with the Formulas I use CTRL+Left/Right Arrow keys

* Try to get into the habit of capturing the 'Rolled Forecast' into Jellyfish each week. It is useful to know what you were forecasting in the past for any week. You don't need to do this if you are Copying and Pasting your Plan into another system that can capture the forecast for you. Whatever way you play it, document it somewhere, so that you are aware that each time the forecast generation is ran, that you understand if it is a captured forecast or a forecast from the start of your Actuals which simply overwrites everything (past and future) every week

* When forecasting by SKU by Depot or by SKU by Depot by Customer, don't apply any rounding to the forecast for each combination. This way when aggregated the volumes will not be exaggerated

* As well as using the tiny Toggle Checkboxes to turn Chart Series on/off, you can also use the inbuilt features of Excel. Unprotect the Sheet. Right-click on the Chart and choose 'Select data'. Scroll through and tick/untick any of the Series shown on the left-hand side of the Dialog and then press OK - the Series will be either hidden or not hidden. If not hidden will be displayed based upon the Toggle Checkbox status and if hidden will not be displayed at all on the Chart

* If you are unsure of the General Settings for forecasting for Jellyfish or you can't remember what you have changed in Section 3.0, then you can use the Reset Button on the'Setup' Sheet to restore the defaults. Please note: this will not change any other settings on the 'Setup' Sheet

Future Builds

* Demonstrate how you can handle a week 53 in a year
* Consider adding Outlier Correction or Cleaning to fix Seasonality for very large Product or combination variances. Perhaps a separate Cleaning Template. I am not sure yet, but will give it some thought
* Check the Code to see if it is possible to remove the Column "A" reference to make the Formula totally dynamic and allow the insert of Columns before the Planner Table
* Check the Code and correct any Hard-coded String references for the Planner Table Name or Planner Sheet Name as this prevents both being renamed by a user
* A Chart colour theme changer to quickly update the Charts and any Sheet Series colours to specific colours or styles


All of the information to configure Jellyfish is contained in this online documentation - please take the time to read the Getting Started Section and as much of the information as is possible prior to using the Jellyfish Software. Please note: I am UNABLE to configure or develop Jellyfish for everyone that purchases the Software, including modifying any of the Workbooks, Formula, Code, Layout, adding to any of the Sheets, creating Pivot Tables to aggregate data etc. You or someone in your Business must make these changes in accordance with your data and requirements

Bug Fixes

* The Levels looked incorrect for some Products, for example even after cleaning for a missing bucket, a Product underselling against 2019 sales for the last 3 weeks with 3 full weeks sales values was forecasting a lot higher than 2019 going forward (or visa versa) indicating a issue somewhere
:= FIXED 4.09.2021 - the Week Offset number being used by the Level Estimate Formula was looking at last week for the Seasonality, so if Forecasting for week 35, it was dividing any one of the 3 Averages by the Seasonality for the previous week, which was incorrect and the calculation was bringing back a higher Annual Level estimate

* The VBA Code Window was being opened by both Jellyfish and the Latest Estimate Files whenever they were opened
:= FIXED 21.08.2021 - Code was being ran by the IToast Class to restore the main VBA Window, so this has been commented out (applies to both Jellyfish and the Latest Estimate Files)

* The Variance and Zero Forecast Boxes will close whenever a List is Cleared on the Jellyfish Ribbon or an Update / Forecast Generation is ran. This is likely down to the Carbon Dialog Interface destruction sequence, although it requires proper investigation
:= FIXED 11.09.2021 - a workaround Carbon 2.4 IModal replaced the standard IModal Class Code Module - this fixes the issue but at the cost of not allowing a user to click anywhere on the Lightbox to exit a Carbon Dialog. Further investigation is ongoing, but this is my workaround for the issue

* Using the Optional Sort Field mixes up the Descriptions and Product Codes in the Variances Box (Multiplex Bubble Sort routine thought to be responsible)
:= FIXED 02.08.2021 - there was an extra Bubble Sort on the first Column, the Product Code. This has been removed

* The Code does not allow extra Rows to be inserted into the Planner Sheet
:= FIXED 21.08.2021 - the Subroutine determining the Rows for the Cleaning part of the Forumla Copy down the Planner Table has been made dynamic to allow Rows in the Planner Sheet to be inserted

* The Menubars cannot easily be fixed in situ
:= FIXED 21.08.2021 - added options to the Setup Sheet to allow the fixing of both Menubars used in Jellyfish. Once set to Y the option will prevent the Code from running the parts that move the Menubars next to your position and they will be fixed in situ

* Following a lot of changes to Version 1.1, the Variance Box and the Zero Forecast Box are picking Products listed against incorrect Category's
:= FIXED 05.09.2021 - the Multiplex Bubble Sort routine implemented was only sorting on a single Column, but not sorting each element in each slice of the Array when an item needed to be moved up or down the Array. I added a loop to do this, however there will be a hit on speed - Bubble Sort is generally a slower Sort routine on large data sets

* There is a bug in the Annual Level Estimate whereby the formula for the weighted averages are divided by the seasonal index for the forecasted period in order to obtain the annual level estimate used alongside the trend to create the forecasts. However the average at a specific time may be lower or higher than a previous year and when using it to divide by a using seasonality index to derive the estimate and in some occurances (where seasonal indexes differ greatly across the year) this results in a much higher or lower forecast. The formula needs a fix whereby a coefficient is created to allow for any variances in both averages for the seasonality years and for any variances in the seasonality indexes for the seasonality years. Further investigation indicates that this formula may in fact be giving incorrect results all round, but in a tiny way, which has not yet been easily spotted. A deep-dive has been set up to look at the issue!
:= PARTIAL FIX 5.09.2021 - I say partial because I am still not 100% happy with the latest formula for the level estimate, however I now use the last 3 periods average for the seasonality indexes as opposed to a single seasonality index to derive the Annual level estimate in conjuction with any weighted or normal weekly averages

* Opening the Variances Box and/or Zero Forecast Box again by using the Button on the Ribbon when it is already open causes an 'Autofilter method of range class' VBA error to be raised
:= FIXED 09.09.2021 - added 'If ProductCode <> vbNullString Then' to the Code prior to executing the AutoFilter

* There is a bug whereby if you are using AI1 to adjust the Level Estimate, press the 'Forecast' Button and choose 'Yes, remove values' and then choose 'No, I made a mistake, Stop!' on the next dialog, then the values are removed even if you quit the process

All of the information to configure Jellyfish is contained in this online documentation - please take the time to read the Getting Started Section and as much of the information as is possible prior to using the Jellyfish Software. Please note: I am UNABLE to configure or develop Jellyfish for everyone that purchases the Software, including modifying any of the Workbooks, Formula, Code, Layout, adding to any of the Sheets, creating Pivot Tables to aggregate data etc. You or someone in your Business must make these changes in accordance with your data and requirements

Misc Issues

* When clearing lists, the list are not validated first to check if they are populated in any way
:= FIXED 12.09.2021 - Added a validation check before prompting to clear any of the lists. Now if a list is empty, a prompt will inform the user to add items to the lists first

* The Planner Table Name or Planner Sheet Name cannot currently be changed due to the VBA Code using some Hard-coded String references


All of the information to configure Jellyfish is contained in this online documentation - please take the time to read the Getting Started Section and as much of the information as is possible prior to using the Jellyfish Software. Please note: I am UNABLE to configure or develop Jellyfish for everyone that purchases the Software, including modifying any of the Workbooks, Formula, Code, Layout, adding to any of the Sheets, creating Pivot Tables to aggregate data etc. You or someone in your Business must make these changes in accordance with your data and requirements

Changelog

Read more information on the status of each release below
- the latest Version including a description of any changes made is always shown first

23.03.2022, (Version 1.4) Minor modification to the Toggle Switch on the Planner to prevent the Grouped Object from resizing after using Zoom

---

28.10.2021, (Version 1.3) General release

---

24.10.2021, (Version 1.3) Getting ready for release

1. Added the ability to Round up tiny values between two values ie. 0 and 1 or 0.5 and 1
2. Changed the formula on the Planner Sheet to match any rounding settings on the Setup Sheet where we calculate any Planner Adjustments, Promo and MI% as this was automatically being rounded to zero by default. When forecasting at the lowest level with Round up tiny values turned off (to prevent excessive rounding aggregation and higher forecasts) we need any adjustments to match the existing rounding
3. Set the label Number format for the 2022 Forecast on the main Chart on the Planner Sheet
4. Added a SPECIAL MACROS SECTION FOR ADMIN to include a Macro to clear down data in readiness to create a Blank Jellfish

---

18.09.2021, (Version 1.2) Beta Test Release only. Changes since Version 1.1 include:

1. Adding Option Private Module to some of the Code Modules to prevent exposed Subroutines. Changed the ExpandAll() and CollapseAll() Treelist Subroutines to Private
2. Changed the setting 'Select and unfilter the Planner Sheet whenever the Variances or Zero Forecast Boxes are closed' to just 'Unfilter the Planner Sheet whenever the Variances or Zero Forecast Boxes are closed' as the Sheet Select was annoying if you close the Boxes on another Sheet; you basically just want the option to unfilter the Planner Table. I also changed the Code from 'ActiveSheet' to the direct Sheet reference, 'shPlanner'
3. Added an AI Module, allowing you to interogate the Model for both Reporting and Level Estimate adjustments - this must be ran after the weekly forecast generation once to create the adjustments
- For Reporting, code will spit out the next 3 weeks forecast vs. the previous 3 weeks actuals measured against a tolerance ie. 40% into the 'REPORT' Sheet, with PASS, FAIL and SKIP attributes
- AI adjustments will be added to the AI Column in the Model Sheet, together with a reason for the AI in the Artificial Intelligence Column
- AI adjustments will not be added if the an adjustment would force a negative or zero forecast
- AI adjustments are not added if a Stored/Rolled forecast value exists for the current forecast week
4. Added in a Seasonality Coefficient of Variance in the Spare Column. This formula measures the CV% of the Seasonality Indexes used for individual Products or combinations. Use to determine how bad the Seasonality Indexes are. You may investigate any values > 30% with an emphasis on huge Coefficience of Variance >100%, where individual Products or Combinations may require cleaning of individual weeks or switching to NPD status using the NPD Overrider
5. Added the ability to round up negative values to 1 as I found that the trend induced a forecast to either be negative or to stop completely when a negative is set to zero. Ideally I want it to behave like an SMP and send out a value of 1 until the trading length is reached whereby it would then be switched off completely

---

04.09.2021, (Version 1.1) Beta Test Release only. An internal release, specifically designed to be worked with across a variety of Business Sectors to test the Forecasting Model and all of the functionality of Jellyfish. During this period, Jellyfish will not be available to purchase. Changes since Version 1 include:

1. Code Window appearing whenever Jellyfish and/or the Latest Estimate Files are opened
2. Allow an option to fix both or the Menubars in situ if required by the user
3. Add in MI %, Promo, Plan Adj. into main Planner Table and Detail Sheet
4. Added Scroll to current week in the Detail Sheet
5. Changed the Copy Formula routine to be dynamic allowing Rows to be inserted into the Planner Sheet
6. Added a prompt to all of the Clear List routines
7. Added the ability to remove zero Rows on the LE Sheet
8. Added an alternative routine for the SendKeys routine to fix any Num-loc turning on/off issues if required
9. Added an Overrider Reason Column that allows you to specify an Overrider Reason ie. Xmas WINE Fcast added to Rolled Forecast for Weeks 37.2021 - WK4.2022
10. Added in the Variance Formula requested for tracking by the Beta test group individuals
11. Added in the option to clear the LE Sheet on enter or on exit. Setting to on exit will reduce the Filesize of Jellyfish
12. Reworked some of the Comments in the Model Sheet to better explain how the Formula work and updated the website instructions
13. Removal of the Psuedo averages used to derive the levels, which were confusing and did not work correctly for different scenarios
14. Changed the Seasonality to point to 2019 only for now! Where changes are made I have marked these and then added the original Formula in readiness for extending to multiple years after COVID has stopped affecting Seasonality
15. Changed the Level Estimate Formula to pick up and average 3 periods of seasonality indexes to use in conjuction with the weighted or normal averages to derive the Annual level estimate
16. Added another Manual Overrider, Pick Rule which allows you to override the Rules so that Jellyfish uses a different average weighting in its forecast generation
17. Added in the ability to switch between Actuals and Final Cleaded Actuals for the Variance and Zero Forecast Boxes
18. Added the abilit to select and unfilter the Planner Sheet whenever the Variances or Zero Forecast Boxes are closed
19. Bug fix on Variance and Zero Forecast Box closure issue
20. Added in all of the Actuals to the INTERFACE Sheet
21. Added in the Actuals for 2019 to the main Chart as after Cleaning I wanted to see the difference for 2019 as I am using this year for Seasonality and Variance Reporting
22. Changed the 2020 Final Cleaned Sales Chart Colour to Brown/Dark Red
23. Added a validation check before prompting to clear any of the lists. Now if a list is empty, a prompt will inform the user to add items to the lists first
24. Add the ability to Scroll back to Column 1 and Row 1 whenever the Model Sheet is activated - this will prevent Hyperlink jumps from the Setup Sheet though if you use these

---

16.08.2021, (Version 1) Beta Test Release only. An internal release, specifically designed to be worked with across a variety of Business Sectors to test the Forecasting Model and all of the functionality of Jellyfish. During this period, Jellyfish will not be available to purchase

---