Capstone project for Data Analysis with Excel, Python & AI
Find the stores that lose the most sales to empty shelves
Mangrove Market's head of stores wants to know which stores lose the most sales to out-of-stock items, why, and what to do first. Build a dataset of at least 2,000 rows with one row per store, article and day: store, city, article, category, units sold, price, and whether the item was out of stock at the morning count. You can adapt a public retail dataset or generate one (an AI assistant can help, as in the AI lesson), but plant at least four quality problems in the raw file on purpose: duplicated rows, numbers stored as text, inconsistent store names and blank values that are not zeros. Turn the request into a question, a metric and a plan; clean the data in a spreadsheet with a cleaning log; estimate lost sales (for example the item's normal daily sales on the days it was out of stock) and summarise them with pivot tables; repeat the cleaning and the summary in a pandas notebook and check the two give the same totals; ask an AI assistant for one part of the analysis and record what you checked and what it got wrong; then write the one-page answer for the head of stores with two charts at most.
What you hand in
- An analysis plan: the decision, the question in one sentence, the metric with its exact definition, the comparison and the data checks
- The raw file, the cleaned spreadsheet with pivot tables, and a cleaning log with row counts before and after each step
- A pandas notebook that repeats the cleaning and summary from the raw file and matches the spreadsheet's totals
- A short AI log: the prompt you gave, the code or answer it returned, the checks you ran and what you corrected
- A one-page answer with the conclusion first, at most two charts, the recommendation, its cost and expected impact, and how it will be measured
Check your work
Tick each item when your project meets it. Be strict: this is the rubric the reviewer uses.
- The question is one sentence tied to a decision, and the lost-sales metric has an exact definition: what counts, the period, the grain and what is excluded
- Every planted quality problem is found and handled, the cleaning log records each step with row counts, and the raw data is kept untouched
- The summaries compare like with like and show which stores and categories explain most of the lost sales, with sums and rates computed correctly (no average of averages)
- The notebook runs from the raw file to the result and its totals match the spreadsheet to the cent, or the difference is explained
- The AI log shows at least two concrete checks of the assistant's work (a row count, a number recomputed by hand, a join or filter checked) and what was corrected
- Each chart answers one question, is titled with its finding and uses honest axes
- The one-pager leads with the answer, and the recommendation follows from the numbers with a cost, an expected impact, an owner and a follow-up measure
0 of 7 checked
Model answer
The model answer opens once your capstone passes the review, so you can compare your work with an outline of a strong submission.
Hand in your capstone
Your links are checked automatically, then the work is scored against the rubric above, item by item, with comments. A pass counts toward the course certificate and shows on your profile.
Loading…