Instructions
Select one of the two scenarios provided below: Healthcare Operations or Business Logistics. In this summative assignment, you will analyze time-series data in Excel, apply forecasting models, assess model accuracy, and make data-informed recommendations for operational planning. Your work should demonstrate your understanding of forecasting principles and your ability to communicate your analysis clearly and professionally.
Scenario 1: Healthcare – Emergency Department Staffing
Context:
You are a healthcare operations analyst at RiverView Medical Center. Hospital leadership is reviewing weekend staffing patterns for the Emergency Department (ED). You have been provided with historical Saturday patient visit data and are tasked with forecasting visit volumes for the next four Saturdays. Your analysis will help determine appropriate staffing levels and improve patient care outcomes.
Data Provided (past 12 Saturdays – number of patient arrivals):
92, 88, 95, 100, 104, 98, 91, 87, 90, 102, 106, 99
Scenario 2: Business Operations – Logistics Delivery Forecasting
Context:
You are a logistics planner for a national supply chain firm. Warehouse managers have reported inconsistent Monday delivery volumes, which has led to staffing inefficiencies and delayed outbound shipping. You have been given delivery volume data for the past 12 Mondays and are tasked with forecasting the number of deliveries expected in the next four weeks. Your forecast will inform labor scheduling and logistics resource planning.
Data Provided (past 12 Mondays – number of delivery orders):
145, 148, 152, 150, 157, 160, 162, 158, 165, 170, 172, 175
Tasks:
- Analyze Data Patterns (CLO5):
- Enter the weekly data into Excel and create a time-series plot.
- Describe any observed trends, cycles, or irregularities in the data.
- Explain whether the data appears appropriate for short-term forecasting and justify your reasoning.
- Apply Forecasting Models (CLO5):
- Apply two forecasting methods (e.g., 3-period moving average, exponential smoothing, linear trendline) to the historical data.
- Generate forecasts for the next four periods using both methods.
- Include Excel calculations or screenshots showing your models and forecast outputs.
- Evaluate Forecast Accuracy (CLO5):
- Choose an accuracy metric such as Mean Absolute Deviation (MAD) or Mean Squared Error (MSE).
- Compare the two forecasting models using your selected accuracy metric.
- Identify which model provides the most reliable forecast and explain why.
- Recommend and Interpret (CLO2, CLO5):
- Based on your analysis, recommend which forecast should be used for operational planning.
- Clearly explain the practical implications of your forecast in the selected scenario (e.g., adjusting staff schedules, reallocating logistics resources).
- Acknowledge any limitations in the data or model and discuss how decision-makers should account for uncertainty.
Submission Requirements:
- Your written report should be 500–750 words in length, submitted as a Word document.
- Include your Excel file with the time-series data, forecasting models, and accuracy calculations.
- Use clear, concise, and professional language appropriate for executive or operational decision-makers.
- Clearly label each section of your submission in accordance with the four tasks above.
| Criteria | Exemplary | Proficient | Developing | Needs Improvement | Not Submitted | Criterion Score |
| Analyze Data Patterns | 30 points
Explicitly completes 3 elements: (1) correct time-series plot created, (2) explicitly describes trends/cycles/irregularities, and (3) clearly explains data suitability explicitly. (30 pts) |
24 points
Clearly completes 2 of 3 elements explicitly. (24 pts) |
18 points
Clearly completes 1 of 3 elements explicitly. (18 pts) |
12 points
Vague, unclear, or minimal effort on required elements. (12 pts) |
6 points
Not addressed. |
Score of Analyze Data Patterns,
/ 30 |
| Apply Forecasting Models | 35 points
Explicitly completes 3 elements: (1) accurately applies two appropriate forecasting methods, (2) clearly generates forecasts for 4 future periods for both methods, and (3) provides Excel evidence explicitly (screenshots or calculations). (35 pts) |
28 points
Explicitly completes 2 of 3 elements clearly. (28 pts) |
21 points
Explicitly completes 1 of 3 elements clearly. (21 pts) |
14 points
Incomplete or incorrect methods applied; unclear or missing Excel evidence. (14 pts) |
7 points
Not addressed. |
Score of Apply Forecasting Models,
/ 35 |
| Evaluate Forecast Accuracy | 30 points
Explicitly completes 3 elements: (1) selects and explicitly applies appropriate accuracy metric (MAD or MSE), (2) clearly compares accuracy of two models, and (3) explicitly identifies and clearly justifies most reliable model. (30 pts) |
24 points
Explicitly completes 2 of 3 elements clearly. (24 pts) |
18 points
Explicitly completes 1 of 3 elements clearly. (18 pts) |
12 points
Minimal effort or unclear/incomplete accuracy evaluation. (12 pts) |
6 points
Not addressed. |
Score of Evaluate Forecast Accuracy,
/ 30 |
| Recommend and Interpret | 30 points
Explicitly completes 3 elements: (1) clearly recommends specific forecast model explicitly, (2) explicitly explains practical implications aligned clearly to scenario, and (3) explicitly acknowledges and describes limitations/uncertainties. (30 pts) |
24 points
Explicitly completes 2 of 3 elements clearly. (24 pts) |
18 points
Explicitly completes 1 of 3 elements clearly. (18 pts) |
12 points
Unclear, vague, or incomplete recommendation or interpretations provided. (12 pts) |
6 points
Not addressed. |
Score of Recommend and Interpret,
/ 30 |
| Professional Communication & Formatting | 10 points
Explicitly meets all 4 criteria clearly: professional language and tone, clearly labeled sections matching assignment tasks, within required word count (500–750), Excel file provided explicitly. (10 pts) |
8 points
Explicitly meets 3 of 4 criteria clearly. (8 pts) |
6 points
Explicitly meets 2 of 4 criteria clearly. (6 pts) |
4 points
Meets 1 of 4 criteria clearly; formatting or communication significantly impacts clarity. (4 pts) |
2 points
Not addressed or substantially incomplete. |
Score of Professional Communication & Formatting,
/ 10 |
| Skills | 15 points
The student skillfully and consistently integrates relevant skills into the assignment. Application is accurate, contextually appropriate, and enhances the depth or originality of the assignment. Demonstrates mastery through insightful use of skills. (15 pts) |
12 points
The student applies relevant skills accurately and appropriately. The use of skills supports key ideas or analysis and demonstrates a strong understanding. Integration is effective but may lack the depth or originality of a mastery-level response. (12 pts) |
9 points
The student applies relevant skills inconsistently or with partial success. Connections to the assignment may be unclear, generalized, or only loosely aligned with the topic. Demonstrates a basic understanding but with limited integration. (9 pts) |
6 points
The student attempts to apply relevant skills but with evident inaccuracies, oversimplifications, or weak alignment with the assignment content. Demonstrates limited awareness or understanding. |
