Course Outcome Alignment: CO-6 (Microsoft Excel) and CO-7 (Healthcare IT)
Week 1: Propose the Idea • Week 2: Spread the Word • Week 3: Train the Volunteers • Week 4: Track the Donors
This is the grand finale! You have communicated, advertised, trained, and tracked. Now it is time to tell the story with data.
Dr. Elena Martinez has been invited to present the blood drive results to the Board of Directors at their quarterly meeting. She needs a data-driven summary complete with charts, formulas, and a polished dashboard. Your job is to build it for her using the donor tracking workbook you created in Week 4.
Create a pie chart showing how donors are distributed across blood types.
Tip: Before building the pie chart, use COUNTIF to create a summary table that counts donors by blood type. This summary becomes the chart's data source.
Create a clustered column chart comparing donation outcomes across time slots.
Tip: Use COUNTIFS to build the summary — one criterion for time slot and a second for donation status (Successful / Deferred).
Choose any chart type that reveals an interesting data story from the blood drive dataset. Your goal is to find something worth sharing with the Board.
Suggestions:
Include a descriptive title, appropriate labels, and a one-sentence explanation of what the chart reveals.
Add the following formulas to demonstrate Excel's analytical power:
Create a Lookup sheet with two components:
Build a reference table listing all 8 blood types with columns for Can Donate To and Can Receive From:
| Blood Type | Can Donate To | Can Receive From |
|---|---|---|
| A+ | A+, AB+ | A+, A−, O+, O− |
| A− | A+, A−, AB+, AB− | A−, O− |
| B+ | B+, AB+ | B+, B−, O+, O− |
| B− | B+, B−, AB+, AB− | B−, O− |
| AB+ | AB+ | All Types (Universal Recipient) |
| AB− | AB+, AB− | A−, B−, AB−, O− |
| O+ | A+, B+, AB+, O+ | O+, O− |
| O− | All Types (Universal Donor) | O− |
Create a lookup section where a user enters a Donor ID and the workbook automatically retrieves:
Use VLOOKUP or XLOOKUP. Test with 3 or more Donor IDs to confirm accuracy.
Create a dedicated Dashboard sheet that gives Dr. Martinez a single-screen overview for the Board. Include:
| Component | Points |
|---|---|
| Pie Chart — Blood Type Distribution (all 8 types, title, % labels, colors) | 20 |
| Column Chart — Donations by Time Slot (clustered, Successful vs. Deferred, labels/title/legend) | 20 |
| Additional Chart of Your Choice (appropriate type, title, labels, one-sentence insight) | 10 |
| Advanced Formulas (SUMIF, COUNTIF, AVERAGEIF, Sparklines) | 15 |
| Donor Information Lookup (compatibility table, VLOOKUP/XLOOKUP, tested with 3+ IDs) | 10 |
| Executive Summary Dashboard (key metrics, summary table, 2+ charts, professional formatting) | 15 |
| Total | 90 |
Submit your completed .xlsx file with all sheets (Donor Data, Charts, Lookup, Dashboard) by the end of Week 5.
File name: LastName_Week5_BloodDrive.xlsx
Over 5 weeks, you used Outlook, Word, PowerPoint, and Excel to bring Sunnydale's community blood drive from an idea to a board-ready report. You proposed the event, spread the word, trained volunteers, tracked every donor, and now you are presenting the results with professional data visualizations. That is the full lifecycle of a healthcare IT project — and you did it all.