CCEI Gaps Analysis: Suitability of CCE infrastructure for current population

[adamdewey]

Background Information

UNICEF Global have requested a tool to identify gaps in a country's cold storage infrastructure.

The pain points in this Epic are:

• There is no easy way to assess whether the current CCE infrastructure is adequate to serve the current population.

Problems to solve:

• A way for central users to view aggregate metrics on the ability of the CCE infrastructure to cater for the demand introduced by immunization programs for the current population
• A way for central users to apply filters to those metrics to narrow down the data they are interested in.

Describe the solution you'd like 🎁

Documents

• User stories background

Mockups: https://docs.google.com/spreadsheets/d/1ow2Jervsw0D51ynHHYGV7GA_XcUIIH_hU-cCSeI0ySc/edit#gid=0

User stories

• C.2.1
  • Overview of facility cold storage capacity surplus or deficit for each temperature range: +5 °C, -20 °C, -70 °C
• C.2.2
  • Overview of facility CCE hosting capability: On-grid / off-grid
  • Overview of facility CCE intervention requirements: No intervention, Replacement, Expansion, Extension

DASHBOARD 1 - National forecasting overview

Filters

• Facility level (multi-select)
• Facility type (multi-select)
• Facility ownership (multi-select)
• Facility name (multi-select)

Cold storage capacity

Formula

• For each temperature storage range (+5 °C, -20 °C and -70 °C):
  • For each supply level:
    • For each facility:

Displayed	Datapoint	Formula
Yes	Installed capacity	Sum of (storage volume) for all CCE instances)
Yes	Forecast peak demand (litres)	((Population served * Total cold storage volume required per capita per year) / Supply interval) + Buffer stock
	Total cold storage volume required per capita per year	Vaccine cold storage volume required per capita per year + bundled items cold storage volume required per capita per year
	Vaccine cold storage volume required per capita per year	Sum of (volume required for each vaccine course per year per individual * National number of individuals that require that vaccine course per year) for all vaccine courses / (General population) <<- CAREFUL! Vaccine temperature ranges are not necessarily the same as each other!
	Volume required for each vaccine course per year per individual	Volume required per dose * number of doses per year / (1 - Vaccine wastage rate)
	Volume required per dose	Vaccine item packaging volume / Number of doses per packaging
	Vaccine item packaging volume	Packaging type volume - Packaging type (Primary, Secondary, Tertiary) dictated by facility's supply level
	Number of doses per packaging	Packaging type doses - Packaging type (Primary, Secondary, Tertiary) dictated by facility's supply level
	Number of doses per year	Vaccine course total dose number - Heuristic: all course doses delivered within 1 year
	National number of individuals that require that vaccine course per year	General population * Demographic group
	Bundled items cold storage volume required per capita per year	Sum of (volume required for bundled items for each vaccine course per year per individual * National number of individuals that require that vaccine course per year) for all vaccine courses / (General population) <<- CAREFUL! Vaccine and bundled item temperature ranges are not necessarily the same as each other!
	Volume required for bundled items for each vaccine course per year per individual	Sum of (Vaccine volume required per dose * Vaccine course total dose number * Bundling ratio * Packaging ratio) for all bundled items per vaccine course
	Packaging ratio	Bundled item packaging type volume / Vaccine item packaging type volume
	Buffer stock	Total cold storage volume required per capita per year * % of year to keep a buffer for * Population served
	% of year to keep a buffer for	Buffer months / 12 months
Yes	Forecast surplus / deficit (litres)	Installed capacity - Forecast demand
Yes	Forecast surplus / deficit (%)	(Installed capacity - Forecast demand) / Installed capacity

Detail view

TBD

Moneyworks Jobcode 🧰

CCEIDEV - development
CCEIDSN - design
CCEITEST - testing
CEEIDOC - documentation

[jmbrunskill]

Looks great Adam.

Do we need some kind of list of all facilities where forecast demand is greater than expected capacity?

[adamdewey]

Looks great Adam.

Do we need some kind of list of all facilities where forecast demand is greater than expected capacity?

Yes definitely, sorry - there will be a few graphs needed in each ticket, I'll slowly work my way through adding them!

[adamdewey]

@EthanMcQ-TMF - this would be the priority to get in place for the QA team to be able to work against 🙏

[adamdewey]

@Mai21 I had a quick compare of the WHO IGA spreadsheet to our dashboard and couldn't arrive at the same numbers.

Would be good to understand the difference

[adamdewey]

This is a copy of the WHO IGA spreadsheet that I've been using:

UAT_cce_inventorygapanalysis_tool_2023.xlsx

[Mai21]

This is a copy of the WHO IGA spreadsheet that I've been using:

UAT_cce_inventorygapanalysis_tool_2023.xlsx

@adamdewey I can not find these stores in this excel files. Is it the same file? Sheet is Structures, right?

[adamdewey]

@Mai21 - sorry Mai, I attached the wrong version, here is the correct one:
UAT_cce_inventorygapanalysis_tool_2023.xlsx

[adamdewey]

@Mai21 Ok, so I arrived at the same number as WHO IGA using my own workings, see this spreadsheet:

https://docs.google.com/spreadsheets/d/1Hb5TgIWQBSPsaX3tS8EwOncfbYS2-MjjqnYAa1zjb-Q/edit?gid=2107228839#gid=2107228839

The green fields in the spreadsheet have been populated in the GAPS UAT test server (https://uat.msupply.org/)

There is 1 facility (Regional Warehouse 1) that is using 1 vaccine course (Polio bivalent) at -20 °C.

However the dashboard isn't returning the value I would expect based on that data.