Introducing Cassandra for Excel

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. 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 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 (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

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

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


  • 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


  • 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 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


Right-click on the 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



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


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 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:

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

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

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!

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

Using Cassandra with APO

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

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:

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


* There are currently no FAQ for Cassandra


This is a video of Cassandra in action. Music is Born a Rockstar - NEFFEX. View this Cassandra video on YouTube


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

* Improve the Seasonality Indexes and allow Seasonality at any level instead of just Product Category, so the option to choose should be given in Cassandra prior to creating the Forecasts. I may also make some improvements to the CVC Report section, for example, I may add a Sort Direction

Bug Fixes

* There are no bugs currently identified for Cassandra


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

General release of Cassandra