Introduction

Introducing Cassandra for Excel (v4, v6 build 1.6)

Cassandra, the power of prophecy for Excel 2016 and APO

Cassandra is an exponential smoothing, weekly, seasonal forecast model for Excel that derives a level together with a rise and run trend from 12 weeks or less of actuals to predict the future. I currently use Cassandra daily to model Products in APO for SAP and / or to generate the Cleaning data required in APO to forecast products. You can use Actuals or Final Cleaned Actuals, the choice is yours but cleaning of any input data is not neccessary. Cassandra is a lot easier to design and integrate into your own Company requirements being a lot less complex than my Jellyfish Software. You can create a forecast from 1 week of data or by adding a level for any combinations

Cassandra 4 consists of a basic Formula template *.xlsx, that you can choose to use as-is or develop yourself and then a feature-rich VBA template *.xlsm (Macro-enabled), that you can use together with APO (either with a Paste of data for Actuals from BI/BW, BEx Analyzer embedded Grid (32 bit Excel) or on 64 bit Excel via Citrix, Power Query, Power BI Pivot or just a simple Paste from any system). Cassandra is built with a lovely GUI using a background image from https://lstore.graphics/ (search for: paaterns to locate and download all of the images) and features a scrolling Chart that uses a Slider from my Awesome Sliders Software. When creating a forecast, Cassandra uses my Lightbox Icon Software to 'Lock' out user input and dim the lights

Cassandra 6 is a step above its predecessor using a rolling MAT of Actuals and the Forecast from another system for analysis ie. APO. Cassandra now boasts many different Forecasting Options including standard Forecast, Solver and Low-level Solver Forecasts, Cassandra Forecast, Chloe Forecast (Cassandra's apprentice) and Cassandra AI Forecast. Version 6 is more complex and as such I will include all previous versions in the download should she prove 'too hot to handle'. I also include a Template Workbook so that you can begin to setup Cassandra in your own Company by adding your rolling MAT of Actuals in a way that makes sense to Cassandra until you feel confident enough to integrate a BEx Grid or Business Object / Power BI Query as the direct source of Actuals in Excel. You can of course simply Paste any data from a BI Portal or any other system

You do not need APO to use Cassandra but it has been specifically designed to make a Demand Planners life easier when modelling Products following their launch into a Business and to close the gap on the lost data following COVID where we won't really have a proper full year of Actuals until the end of 2023 to create Seasonality by Product Category. Once a forecast has been generated, you can take out the Actuals at any level from APO and then turn this into Cleaning data that you can then Paste back into APO to allow a Product to have full 3 Years of Sales History Cleaning

Cassandra v6 is an exponential smoothing, weekly, seasonal forecast model for Excel that derives a level together with a rise and run trend from a rolling MAT of Actuals to predict the future. Cassandra now boasts many different Forecasting Options including standard, Solver, Cassandra herself and Cassandra AI

If you love Cassandra, you may also like Jellyfish. If you want a great CVC Generator then you may love my CVC Generator (an update is in the pipe-line for this Project)

Kraken · Jellyfish · CVC Generator · Lightbox Icon · Awesome Sliders

Prerequisites

Microsoft Excel 2016, 32bit or 64bit (where you derive your Actuals data from will be down to you)
Basic to moderate Excel Skills
Windows PC · NOT a Mac!
No support is provided for customization of this Software

Features

Cassandra v4

Seasonality Index Process File to see how I created Cassandra Seasonality so that you can replicate if you want for your own Business requirements
Exponential Smoothing Algorithm to clean Actuals that deviate from the Mean by an Alpha Coefficient
Manual Overriders for Level and / or percentage (%) per combination
Forecast at at various low-levels ie. Product by Plant by Customer Planning Group or by Product by Plant. Forecast at a National or aggregate level ie. by a single Product
Model Products following their launch into a Business using a Paste from APO and then a Paste back to APO. Obviously sound cleaned 12 weeks Actuals are a bonus, however, no specific amount of Actuals are required for this task as you can just specify a level ie. 10 and the Seasonality will forecast at this level ongoing
Actuals cleaning is not neccessary unless the most recent weeks are complete outliers
Cassandra can use any Seasonality, but is built to use a Generic Category Seasonality for now due to COVID. Future builds will include Product Seasonality and how to correctly maintain Product Seasonality Indexes
When using Cassandra with APO the data for Actuals is automatically cleaned at the lowest-level and as such no further cleaning is neccessary in APO itself. Cassandra also correctly splits values for the correct Calendar weeks ready to Paste back into APO
Cassandra automatically creates a Forecast using the Formula and then changes the calculations into Special Values for speed and to compact the Workbook
3 Actions are required to clean and Forecast any Product in APO with zero or limited sales data - a Paste out of the Total Shipments, a Paste back into the Detailed or Aggregate Cleaning Key Figures and finally a Manual Univariate with any of the standard Seasonal, or Seasonal and Trend models (Forecast Profile together with a Univariate Profile)
If you are using APO, the whole process takes about 2 minutes and you will have a Product forecasting nicely in APO
A Scrolling Chart gives you the option to view either 26 weeks or 75 weeks at any time, which includes the 12 week Actuals and the 12 week Smoothed Actuals. This Chart uses an awesome SLiding Button which is part of my Awesome Sliders Software
See what the next 3 weeks average Forecast looks like for each combination against the last 3 weeks Actuals average
A stunningly beautiful GUI using a fantastic image called 'Rangitikei.png' from https://lstore.graphics/ and search 'Paaatterns'. The image used by Cassandra forms part of a complete pack of great graphix. These can be downloaded in PNG and SVG formats and to use with Illustrator - please do not contact me for these images as I am unable to distribute them!
Cassandra Actions are all available in the 'Power Query Options' Group and the 'Forecast Options' Group of the Cassandra Tab on the Ribbon
When creating a forecast, Cassandra uses my Lightbox Icon Software to 'Lock' out user input and dim the lights
Use with up to 1,000 Rows of data without Formula or VBA Code modifications
With some development you could turn this into a Monthly forecast model but that is down to you
Once we have enough data for 1 full Year of data, following COVID, the Seasonality Indexes will pick up on the individual shapes of combinations much better and I will add a Seasonality Builder to this Software for a future update so that we can use either a combination (ie. Product or Product by Plant) Seasonality if we think the COV (Coefficience of Variance) is sound enough to do so or switch back to our Generic Seasonality as a fallback
Built-in 3WK AI to force the output for the next 3 weeks (short horizon) into a tolerance specified on the 'Setup' Sheet
A CVC Report that allows various options for aggregating the Forecast including selecting the different CVC's ie. Material, Material Description or Material, Material Description and Plant, Plant Description together with options for the time horizon of data and how it is rounded for output

Cassandra v6
Uses a rolling MAT of Actuals to build its own Seasonality Indexes at the lowest level before using a variety of fallback Seasonality Indexes to ensure the combinations have a decent shape throughout the year
In Cassandra_v6 I improved the Seasonality Indexes by allowing a full fallback of Seasonal Indexes from lowest level, SKU x Plant x CPG through to Category. Cassandra_v6 now builds her own Seasonality Indexes whenever full buckets are present for the combinations and COV is below some threshold ie. 20% or 30%
Force Seasonality through the use of a Drop-down to pick one of the 5 Seasonality settings, N = none, try to use the best including a low-level Seasonality, SKUxPLANT, SKU, CATEGORYxPACKTYPE and CATEGORY
Seasonality Coefficient of Variance setting on the Setup Sheet to only allow Indexes that meet a specific percentage of variance for the very lowest level of Seasonality which is actually the combination shape itself
Toggle the last 3 or 4 wek Actuals to be either Actuals, Default = system-based Midpoint solution or Expo, use Cassandra's Exponentially Smoothed averages
MAD, MSE and MAPE calculations for both Cassandra and the existing Forecast Model that you use ie. APO
Slicers for the main Forecasting Selections ie. Product, Plants, CPG's and Seasonal Indexes selected by the system or user
Analysis Grid detailing the Cassandra Ex. Post Forecast (how she would have forecast), Last Year Actuals, Actuals, Exponential Smoothed, APO Forecast (or your own Model) and the Cassandra Forecast
Full ODBC Reporting to the lowest level for 3 years and with SharePoint fix for the Workbook Filepath
A handful of different Scenarios to play with ie. Only consider the last 2 week average, Lower 3 week average by an amount, say 5% and If last 3 week Actuals = zero, try last 4 week Actuals
You can choose wther or not to change Formula to Special Values when using the Solver and AI Solutions
A Setup Sheet with lots of configurable Options ie. Roundup Plants >= 0.5 but less than 1, a Plant-level AGGR. Corr. Applied by SKU by Plant to ensure that the SUM of all of the CPG's for a Plant should try to forecast at least 1 PC (best practice when providing Plant cleaning values that are Plant specific) and Roundup the Final Forecast for anything >= 0.5 but less than 1 to 1, for example, for 10 combinations at 0.6 would be 6 PC but rounded up would equal 10 PC. This can inflate Forecasts artifically but may be useful to ensure a 1 PC forecast in most circumastances when considering these tiny values that fall just outside of 1
3 Charts, the main Chart, a long horizon Chart and then one you can play around with, I have set up to view the Bank Holidays in the next year in the example Workbook
Turn on and off Ex. Post Cassandra Forecast, Last Year Actuals and Exponential Smoothed Series on the main Chart
An Alpha coefficient on the Setup Sheet for the Exponential Smoothing algorithm. The lower the Alpha, the smoother the result. This is copied underneath the Solver Parameters on the Cassandra Sheet prior to a normal Forecast
A Negative Bias Correction percentage on the Setup Sheet used by the 3WK ∓ Exp. S. algorithm to prevent any inherent, positive forecasting bias within the model
A Trend Dampening percentage on the Setup Sheet will smooth out the Trend applied from the beginning of the Ex. Post Forecast through to the last Forecast week
Multiple Forecasters, choose from Cassandra or her apprentice, Chloe
Roundup by SKU allows you to forecast at the SKU aggregate level. If the SUM of all of the lower combinations are between Min and Max parameters, then this will be rounded up to 1 to ensure a Forecast of at least 1 PC. This can be toggled on/off in the Setup Sheet

Installation

Cassandra v4

Right-click on the Cassandra.zip file and Extract all of the contents to your PC or Laptop Folder. Open 'Seasonality Index Process.xlsx' to see how I created the Seasonality Indexes used with Cassandra. Open 'Cassandra_v4.xlsx' and check out the default Formula Template. Open the 'Cassandra_v4.xlsm' file and test out the default Forecast combinations. Please take some time to examine how Cassandra works, then, read all of the info on all of the Sheets in the Workbook and in this online documentation before you begin to edit and create your own Forecasts using any of the Cassandra files or before turning to Support

Cassandra v6

Right-click on the Cassandra.zip file and Extract all of the contents to your PC or Laptop Folder. Cassandra_v6 Files are stored in the Cassandra_v6 Folder. Open the 'Cassandra_v6.xlsb' binary file and test out the default Forecast combinations. Use the 'Template.xlsx' file to see how I added the default Forevast combinations and for the format of how to take out your Actuals data. Please take some time to examine how Cassandra works, then, read all of the info on all of the Sheets in the Workbook and in this online documentation before you begin to edit and create your own Forecasts using any of the Cassandra files or before turning to Support

Usage

Cassandra v4 (Scroll further down for later Versions ...)

Cassandra_v4.xlsx

Open the Cassandra_v4.xlsx file. This is a simple formula-based template that you will need to manually drag down the formula to forecast combinations as opposed to the main Macro-enabled Cassandra_v4.xlsm file

I will go through all of the Sheets for the main Cassandra_v4.xlsm file in a minute, the two are very similar in that there is a Setup Sheet for any settings or paramaters required by the Software, a main Cassandra Sheet used for forecasting, a generic Seasonality Sheet used for the Seasonal Indexes, a Seasonality Sheet for future development for the time when we will be able to use a full year of data to build individual Product Seasonality and some spare Sheets, also for any future development

Click on the Cassandra Sheet. Autofilter a Plant or a Customer Planning Group to view the forecast. There are 21 Columns where you can place your own data. Obviously, as your 12 weeks of data, Week Numbers or Date Headers change, then Cassandra will adapt to the changes and the current forecast week will alsways be last known week +1. There are some caveats without redesigning the Workbook straight away, that I will address below in much more detail, whereby you will need to add your data in such a way that the Category for your combnations can use the Generic Seasonality Sheet (add this to Column "K" ie. "SPIRITS", "ALE" etc.) and that your weeks to start with adhere to the format "1.2022", "2.2022" etc. Once you have added some of your own data to test out the forecasting, then you can begin to turn this into a model that is geared up to your own Company requirements as I have done. In the detailed walk-through below, you can read more information on how to design your own Seasonality and change the Week Numbers / Date Headers in the first 21 Columns to read your own Company-specific week number / date Headers and where you may bring your Actuals data from

Cassandra_v4.xlsm

Open the Cassandra_v4.xlsm file. This is the fully-featured Macro-enabled file with scrolling Chart, Slicer Table and Ribbon commands that you can also link to a Power Query

Cassandra has a Setup Sheet for any settings or parameters required by the Software, a main Cassandra Sheet used for forecasting, a generic Seasonality Sheet used for the Seasonal Indexes, a Seasonality Sheet for future development for the time when we will be able to use a full year of data to build individual Product Seasonality (hidden), a 'APO In (Total Shipments)' Sheet for a Paste out of APO (optional), a 'APO Out (Detailed Cleaning)' Sheet for a Copy & Paste back into APO (optional), a Report Sheet 'CVCReport1' used for outputting the Forecast at various aggregation levels and some spare Sheets, also for any future development

On the Ribbon, Cassandra has a Group called 'Power Query Options' and a Button tha you can link to a Power Query called 'Refresh Query', a Group called 'Forecast Options' with Buttons, 'Create Forecast' used to create the latest estimate, 'Copy Manual Adjustments' used to copy the first Row of Manual Adjustments down the Range, 'Reset Manual Adjustments' used to reset the Manual Adjustments back to their default settings, a Group called 'AI Options', with a Button 'Run 3WK AI' used to run some AI on the combinations and a Group called 'Report Options' with a Button 'Run CVC Report 1' used to run an aggregate Forecast Report by different CVC's ie. Material and Material Description or Material, Material Description and Plant, Plant Description for specific time horizons

Take some time to look at all of the Sheets. Use the Slicers and / or Autofilters to view the forecasts for different combinations and check out the formulas for the Software. Test out the 3WK AI and run an aggregate Report. Here is an image of Cassandra with the scrolling Chart and Ribbon commands:

The Setup Sheet

Click on the Setup Sheet. Here you will find the Editable Configuration & System Settings with links to licensing and the background image used as the Cassandra Theme. In the 'General Settings' section you have the following option settings and their respective descriptions. The Alpha coefficient is used by the exponential smoothing algorithm to control how harsh the Actuals are smoothed as the input for the forecast calculation, I have found that a level of 0.4 makes some pretty decent forecasts, but you can tweak this if you like. The AI tolerance will force any combination to forecat within the tolerance bounds for the next 3 weeks only. The allowed forecast output minimum number and the number to set the forecast to if the output for any combination is less than the allowed forecast output minimum work together, so for example the defaults are zero, but if you forecast by just Material (Product) level you may wish to set anything that is forecasting an output of less than 1 to 1 ie. 0.1 or 0.5 would be set to 1 so that every Material has a value of 1 PC for their Forecast, pretty straightforward really

[0.4] - Alpha coefficient for the Exponential Smoothing algorithm.  The lower the Alpha, the smoother the result, however smaller buckets may be increased slightly
[30%] - 3WK AI tolerance.  If 3WK AI is ran the forecast for any combination will be constrained within these bounds to try to improve the short term forecast
[0] - Enter an allowed forecast output minimum number for any combination
[0] - Enter a number to set the forecast to if the output for any combination is less than the allowed forecast output minimum

Then we have the 'Report Settings' section. Here you have the following option settings and their respective descriptions. Currently there is only a single report option which is an aggregated option for any of the 3 CVC's. So first of all you can choose the CVC's (Key / Item pairs running left to right for the first 6 columns) to create an aggregated report by, for example, to create a report by just the Material and Material Description (CVC1) you can leave the default settings in situ. To create a report by the Plant and Plant Description (CVC2), you would swap the '1' in the first editable box to '2' and leave the '-' hyphen in the second box as-is. To create a report by Material, Material Description and Plant, Plant Description you would set the first editable box to '1' and the second editable box to '2'. Try adjusting the combinations to view the results at different aggregations levels. Next is the time horizon allowed for the report. The SQL using ODBC will only allow 255 Columns to be used as output for any query, so we do have some limitations on this, however in the most part the length should be adequate and the aggregation instant. Typically you would want the next week as the first forecast week until some end date ie. the last week of the year. The third setting is to allow you to specify how you want the forecast to be rounded, ie. how many decimal places you want the output. The default of 3 would allow an output to 3 decimal places ie. 128.468

[1 BY -] - Choose the CVC's required for the Aggregate CVCReport1.  These are Key / Item pair(s) ie. Material and Material Description
[43-22 UNTIL 52-24] - Choose the weeks to start and end the output for the Aggregate CVCReport1
[3] - Enter the number of decimal places to Round the output for the Aggregate CVCReport1

Then we come to the 'System Settings' section where you can set up Cassandra's calendar weeks as per below. Enter the first Monday of the year so that the ISOWEEKNUM() function can determine week 1. The calendar weeks are 418 Rows in Cassandra from 2018 until 2026 and clicking on the first week and selecting the entire Rangeweek will give you the Named Range 'Cassandra.Weeks'. Doing the same with the dates will give you the Named Range 'Cassandra.Dates'. Everything thereafter will be built around these dates for the internal calendar weeks. You can adjust this annually so that when using APO for example your Planning Book horizon can be kept within these bounds, as for pasting cleaning data, you will need to be able to paste into specific ranges within your Planning Book

[31/12/2018] - Enter the start date for Cassandra.  You would typically pick the first Monday date of an ISO Week 1 and this can be rolled each Year

Here is the Setup Sheet:

The 'Setup' Sheet. Here you will find the Editable Configuration & System Settings with links to licensing and the background image used as the Cassandra Theme

The 'Setup' Sheet. Here you will find the Editable Configuration & System Settings with links to licensing and the background image used as the Cassandra Theme

The Cassandra Sheet

Click on the Cassandra Sheet. This is where you will either Copy and Paste your Forecasting 'Actuals' data or overlay a BEx Grid or Power Query or some other method that allows you to quickly bring up new data. I still prefer to add a BEx Grid and then I simply refresh my data using BEx via Citrix (Citrix supports 32bit Office which is required for BEx as it is no longer supported by Microsoft for 64bit Office). For the Download File i have used a Text extract only to demonstrate the type of data you need to add and have added a single Product '22516A Rose BOT 6X0,7 Gin', a 'SPIRIT' with data at the lowest level by Product by Plant by Customer Planning Group. You should attempt to layout your data in a similar way as I have done but you MUST have the Prodct Category populated for the Seasonality. Press the Create Forecast Button on the Ribbon to create a new forecast for the Product at all levels based upon the last 12 Week Actuals. The lights will dim and an Icon will popup to tell you Excel is busy calculating like this:

Use the Slicers to view the Forecast at all of the levels. The Cell "Q31" will tell you the Next 3wk ∓ forecast vs. previous 3wk ∓ actuals as a percentage (%) and a Triangle Icon will show you whether the Forecast is Up (more positive) or Down (less positive) moving forward. You can enter a '% Adjustment' in the first Cell in the '% Adj.' Column and then press the Copy Manual Adjustments Button on the Ribbon to Copy this value all of the way down the Actuals data Range. Now press the Create Forecast Button on the Ribbon again to create a new Forecast with your increased or decreased percentage adjustment, try it! You can also add a 'Level' adjustment in the 'Level Adj.' Column - this will tell Excel that for the combination it must create a Forecast based upon this level * 52 Weeks, so for example if you st this to 6 for the first Cell and then press the Create Forecast Button on the Ribbon the Final Forecast will be the SUM of 6 * 52 and then split by Seasonality for the Product (which for this Product is the SPIRITS Index). Again, try it and test out the result! For this adjustment you may not want to Copy this all of the way down. Press the Reset Manual Adjustments Button on the Ribbon to clear/reset both 'Level Adj.' and '% Adj.' Columns back to their defaults

If you are wanting to use Cassandra to spit out a Forecast that you can use elsewhere as opposed to using it for APO, for Cleaning history, then you can also use the built in AI. Press the Run 3WK AI Button on the Ribbon to take the Tolerance from the Setup Sheet and run AI on the current combinations. Again the lights will dim and a different Icon will be displayed. It will attempt to coerse every combination within the bounds of the Tolerance ie. 20% (default). This will improve the Short Term Forecast, for example you may want to use Cassandra to create Forecasts for your Products each week and also try to ensure that you put out a Forecast that never exceeds some strict bounds. This may have ramifications upon Seasonality but the option is there and you can tweak the Tolerance setting on the Setup Sheet. I ran the AI on the dummy Product and it now only deviates '3.85%' in its next 3 Week Forecast compared to the last 3WK Actuals - you can see how Cassandra has pulled down the next few weeks in the image below:

Press the Run 3WK AI Button on the Ribbon to take the Tolerance from the Setup Sheet and run AI on the current combinations. Again the lights will dim and a different Icon will be displayed. It will attempt to coerse every combination within the bounds of the Tolerance ie. 20% (default)

Press the Run 3WK AI Button on the Ribbon to take the Tolerance from the Setup Sheet and run AI on the current combinations. Again the lights will dim and a different Icon will be displayed. It will attempt to coerse every combination within the bounds of the Tolerance ie. 20% (default)

If you want to view more of the Forecast Horizon in the Chart then you can Drag the Slider Button to the right. The Maximum amount of buckets is 75 and the Minimum amount of buckets is 26. Here I have dragged the Slider to extend the Horizon to its Maximum amount of buckets:

The CVCReport1 Sheet

If you want, Cassandra allows you to grab the Forecast output at various aggregation levels and for a variable Week Range. On the Cassandra Sheet press the Run CVC Report1 Button on the Ribbon to create the report that I set up as a demo for the Download File. I set this from WK6 2023 until WK52 2024 and chose to use a Plant aggregation level (setting number 2) underneath 'Report Settings' on the Setup Sheet. The 3 aggregation Key/Pairings are Product/Plant/Customer Planning Group. For a Plant level aggregated report I chose 2 and left the second setting blank. I also set the number of decimal places to 3. Cassandra will navigate to the CVCReport1 Sheet and you will see the results of the report. You can experiment with the various settings to create your report data how yu want it

On the Cassandra Sheet press the Run CVC Report1 Button on the Ribbon to create the report that I set up as a demo for the Download File. I set this from WK6 2023 until WK52 2024 and chose to use a Plant aggregation level (setting number 2) underneath 'Report Settings' on the Setup Sheet. The 3 aggregation Key/Pairings are Product/Plant/Customer Planning Group. For a Plant level aggregated report I chose 2 and left the second setting blank. I also set the number of decimal places to 3. Cassandra will navigate to the CVCReport1 Sheet and you will see the results of the report

The Generic Seasonality Sheet

Okay so this is without question THE MOST important Sheet! This dictates the shape of any combination that is Forecast by Cassandra and will only create perfect results once it is setup to index at the lowest level ie. Product by Plant by CPG. For now though the Download File has Generic Seasonality at a Product Category only level - this means that for any combination the same Seasonality is given (or the same Shape) for the Forecast. This is mainly due to COVID and until we have some sound Annual data I am unable to progress this any further - it is in the Future Builds Section to complete at some stage in the future and considering I use this as part of my job you can rest assured that I will eventually update this Software with complete methods for Seasonality by:

Product
Product by Plant
Product by Plant by Customer Planning Group
Product by Pack Type
Product Category (Generic Seasonality)

Anyhow for now I have created some Generic Indexes based upon Sales data from 2019 in my Business using a separate process and then Copied the Indexes across to use in Cassandra. I have included my File in the Download with Cassandra (sensitive data has been removed and Product numbers no longer resemble Product numbers within our Business). This way you can see how I achieved my Category Indexes. You can do this any way you like and you may already have data that you can use and/or integrate into Cassandra. Here are my Generic Indexes for my Product Category's - you can see where I have tweaked WK14 up for Easter this Year (2023):

For now I have created some Generic Indexes based upon Sales data from 2019 in my Business using a separate process and then Copied the Indexes across to use in Cassandra. You can do this any way you like and you may already have data that you can use and/or integrate into Cassandra. Here are my Generic Indexes for my Product Category's - you can see where I have tweaked WK14 up for Easter this Year (2023)

The Seasonality Process File

I use a process for Seasonality whereby I take previous Actuals, compute a Z-score, Sort the Z-score and examine the Coefficience of Variance of a combination. If it is sound I use those values for my Index. This File is included in the Downolad with Cassandra. Here is a Screen Shot of my Index process. I have highlighted the ALE Category where you can see I have used a Chart to examine the Shape across the main UK Bank Holidays and then tweaked them. When using in APO, APO may average through your Cleaning (depending on the Model chosen and the combinations) and the results may not quite meet your Index expectations but they will be pretty close!

I use a process for Seasonality whereby I take previous Actuals and examine the Coefficience of Variance of a combination. If it is sound I use those values for my Index. Here is a Screen Shot of my Index process

We have seen a major shift in how Christmas is delived post-COVID and this is down to how our Business operates and Customer expectations and so Christmas is not just a large Spike in a single week nowadays and is spread more across the main two weeks of the festive season, therefore these Indexes need recreating! I have another Software Product that you can use in APO that can help Shape combinations and especially the Christmas period after Cleaning has been done - it is called kraken

Cassandra v6

Open the Cassandra_v6.xlsb file. This is the main Macro-enabled file. Click onto the Cassandra Sheet. This is where you can Copy & Paste your Actuals or insert them using a BEx Grid or via Business Objects. A Template file is included with Cassandra_v6 that details how you should bring in your Actuals and the APO Forecast for comparisons (if required)

How does Cassandra v6 work?

Cassandra uses a rolling MAT of Actuals data to build an annual level through the last 12 weeks of Exponentially Smoothed values with the emphasis on the last 3 or 4 weeks values. It then divides the annual level using either current Seasonality (if detected) or via the various fallback Seasonality specifcally created for the Actuals that you are using. A Trend is applied to the new volumes by analysing the next 2 weeks trend following a 12 week analysis. If a trend is detected either positive or negative it is built into Cassandra's Forecast such that for every 12 weeks past or future the direction and strength is included. This means that it will never drive volumes to zero or off the scale either, it will be a slow trend upwards or downwards accordingly

The Setup Sheet

The Setup Sheet contains many editble parameters. Lets take a look through them

Level Settings:

The Alpha coefficient [default 0.4]. An Alpha coefficient for the Exponential Smoothing algorithm. The lower the Alpha, the smoother the result. This is copied underneath the Solver Parameters on the Cassandra Sheet prior to a normal Forecast. When using the Solver Forecast, this setting is then corrected to give the best fit overall for all of the combinations

The Negative Bias Correction Percentage [default 0.0%]. (Experimental) A Negative Bias Correction percentage used by the 3WK ∓ Exp. S. algorithm to prevent any inherent, positive forecasting bias within the model

The Low Alpha coefficient [default 0.1]. A Low Alpha coefficient for the Exponential Smoothing algorithm for smaller 12WK ∓ Values <=. If the values are tiny we swap to this coefficient for the Exponential Smoothing to ensure small numbers are not smoothed out too much

The method for which Cassandra treats negatives as zero [default Y]. By default, Cassandra treats negatives as zero (0) using Max(number,0) which may impact the Exponential Smoothing. To avoid this, you can switch this off (N) and then choose to check and replace negatives with Blanks prior to each Forecast is generated

The Plant-level AGGR. Corr. [default Y]. Plant-level AGGR. Corr. Applied by SKU by Plant to ensure that the SUM of all of the CPG's for a Plant should try to forecast at least 1 PC (best practice when providing cleaning values that are Plant specific)
Roundup Plants >= [default 0.5] and <= [default 1] to 1 ie. For 10 combinations underneath a Plant 0.6 would be 6 PC but rounded up would equal 10 PC. Useful to ensure a 1 PC Forecast at a Plant. The multiplier used to raise the Largest Share by CPG by Plant can also be adjusted here [default 2]

Forecast Settings:

Using Cassandra with APO (All Versions ie. v4, v6 etc.)

Cassandra has been designed to be used with APO, to take out 'Total Shipments' (or similar Key Figure in APO) and to then Paste back 'Detailed Cleaning' (or similar Key Figure in APO) at various aggregation levels. From there you can run a standard Model in APO via a Manual Univariate or a System Background Job, that does the same thing. For this reason there are many Sheets in Cassandra that allow you to Paste back Cleaning by, 'all Plants by all CPG's (Customer Planning Groups)', 'all Plants by a single CPG' or 'all CPG's by a single Plant'. This means that however your Product is setup in APO you should be able to Clean it back and get it forecasting just how you want it to. Your Forecasting Profile in APO will pickup Trend and Seasonality slightly different, however whatever Cassandra Forecasts for Cleaning will give a similar shape and level (I use a Forecast Profile No. '35 a simple Linear / Seasonal Profile')

Standard Method - Use Cassandra to Clean Combinations in APO, Forecast (Univariate) and then Finish off with Kraken Tool

Pick a problematic Product that you want to model in APO. I will pick a well known Energy Drink Product variant, Red Bull. Open it in the Planning Book at an aggregate level in a Selection Group that you typically use for modelling and clear down the Cleaning data for any Cleaning Key Figures - it may look something like this:

Pick a Product that you want to model in APO. I will pick a well known Energy Drink Product variant, Red Bull. Open it in the Planning Book at an aggregate level and clear down the Cleaning data for any Cleaning Key Figures - it may look something like this

Pick a Product that you want to model in APO. I will pick a well known Energy Drink Product variant, Red Bull. Open it in the Planning Book at an aggregate level and clear down the Cleaning data for any Cleaning Key Figures - it may look something like this

Now drill down the Shipment Key Figure by Plant and then by CPG. You may use Sales or Actuals - it may look something like this:

Copy out this data as Cassandra will then build a new Cleaning Extract for you to Paste back into APO. Paste this data into Cassandra on the 'APO In (Total Shipments) Sheet into Cell "E11"

Alternative Method - Use the Final Forecast and Add it Manually to APO 'Baseline Forecast Adjustment' or Similar Key Figure in APO

Screen Shots

Cassandra v4 (Scroll further down for later Versions ...)

Cassandra is an exponential smoothing, seasonal forecast model for Excel that derives a level together with a rise and run trend from 12 weeks of actuals to predict the future. You can use Actuals or Final Cleaned Actuals, the choice is yours but cleaning is not neccessary. This is a Screen Shot of the Macro-enabled *.xlsm file with its Ribbon commands

Cassandra at 80% Zoom detailing a longer horizon on the Chart using the scrolling slider

The 'Setup' Sheet. Here you will find the Editable Configuration & System Settings with links to licensing and the background image used as the Cassandra Theme

Here is a Screen Shot of Cassandra Creating a New Forecast - the lights will dim and an Icon will popup to show that Excel is busy calculating the Forecast

Cassandra v6

Cassandra v6 Screen Shot detailing the main Cassandra Sheet where all the fun happens, the Charts, Data Grid and Ribbon functionality

Cassandra v6 CVC Report output by SKU x Plant from 40-23 to 39-25 with rounding to 3 decimal places

FAQ

* There are currently no FAQ for Cassandra

Videos

This is a video of Cassandra in action. Music is Born a Rockstar - NEFFEX. View this Cassandra 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

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

Future Builds

* In Cassandra_v6 I improved the Seasonality Indexes by allowing a full fallback of Seasonal Indexes from lowest level, SKU x Plant x CPG through to Category. Cassandra_v6 now builds her own Seasonality Indexes whenever full buckets are present for the combinations and COV is below some threshold ie. 20% or 30%

Bug Fixes

* There are no bugs currently identified for Cassandra

Changelog

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


16.02.2023 - (Version 6)

Cassandra version 1.6, lots of new features; a powerful forecasting tool for Excel that can be used in conjunction with BEx Analyzer or Business Objects


16.02.2023 - (Version 4)

General release of Cassandra