Blood Bank Inventory Management: Blood Types, Shelf Life & Shortage Risk

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Split the weekly red-cell collections of a fictional regional blood service by blood group, compare them with hospital demand and find the group that runs short.
• Build the simplified ABO/RhD red-cell compatibility matrix with one formula, count the donor groups each recipient can receive and the weekly supply that fits each recipient.
• Turn units on hand into days of supply per blood group, set red, amber and green alert levels with a nested IF, calculate the units needed to reach target and colour the red rows.
• Give every red-cell unit its expiry date and days left, count the units per group expiring within 7 days with COUNTIFS and find the next unit to issue under FIFO with MINIFS and INDEX/MATCH.
• Calculate the monthly and half-year outdate rate of a hospital blood bank, the cost of the expired units and the months above the target rate.
• Work backwards from the units each group needs to the donors who must attend, the appointments to book and the number of donation sessions to run.
• Plan a week of platelet orders for a hospital: expected outdates under oldest-first use, an order-up-to level from the forecast, the daily order and the outdate share of the week.
• Build the daily stock dashboard of the blood service: days of supply per group, units to order to reach each target, the overall days of supply and a column chart against target.