Assignment 4: Capstone¶

The course's final project, worth a quarter of your grade. For an electronics company about to sign a five-year manufacturing contract, you turn five focus-group comments into a demand multiplier in Python, build a five-year profit model for two suppliers in Excel, chart the choice in Tableau, and write the board a recommendation that weighs profit against ethics.

Adapted from the DATA 300 GAP course, Post-Class Module, Assignment 4: Final Comprehensive Assignment (Capstone). Written in our own words; follow GAP for the official wording, figures and rubric.

1. What and why¶

Use GAP's figures for the five-year base demand, the selling price, the fixed cost and the two suppliers' unit costs. All money is in US dollars, as on GAP. Read GAP's capstone rubric before you start: everyone works from the same inputs, so the quality of your reasoning is what sets your report apart.

The studio afternoon (Day 7, periods 5–8)¶

Use the afternoon to get every tool step done and checked while help is in the room, and leave the writing for the post-class week. Each checkpoint is something you can show your facilitator.

After Day 7, aim for a full draft by the middle of the post-class week, so you have time to cut it down and check every number against your workbook before Sunday 29 November.

2. Route A: Try JupyterLab, Excel and Tableau (as on GAP)¶

Part 1: Text mining in Try JupyterLab¶

1. Open Try JupyterLab, start a Python (Pyodide) notebook and rename it with the file name GAP gives (your last name, the course code and "Capstone", as .ipynb).
2. Paste GAP's single code block into a cell and run it. In outline, it:
   1. installs pandas and NLTK into the browser session with %pip;
   2. tries to download NLTK's VADER lexicon with nltk.download and create the VADER analyser, and if anything goes wrong, quietly switches to a small keyword-based stand-in scorer that gives one of three fixed scores depending on a few words;
   3. puts the five focus-group comments in a DataFrame;
   4. scores each comment and stores the compound score;
   5. averages the five scores and turns the average into a demand multiplier: 1, plus the average score times 0.20, so a fully positive average would add at most 20% to demand;
   6. shows the table and prints the average and the multiplier to three decimal places.
3. Add one cell under GAP's cell and run print(type(analyzer).__name__). It prints the name of the scorer that actually ran: SentimentIntensityAnalyzer is VADER; anything else is the stand-in. Keep this cell in your PDF.
4. Write the multiplier down exactly as printed (three decimals).
5. Export: File → Print → Save as PDF, with GAP's file name for the notebook PDF.

Part 2: The financial model in Excel¶

1. Open a blank workbook and save it with GAP's file name (your last name, the course code and "Capstone", as .xlsx).
2. Headers. Type GAP's six headers in A1:F1: Year, Base_Demand, Colab_Multiplier, Adjusted_Demand, Standard_Profit, Ethical_Profit. (The third header is a leftover from an older version that used Google Colab; see section 5.)
3. Years and base demand. Type 1 to 5 in A2:A6 and GAP's five base-demand figures in B2:B6.
4. Multiplier. Type your multiplier, as printed, into C2:C6 (the same value in all five cells).
5. Adjusted demand. In D2, type GAP's formula: base demand times the multiplier, rounded to a whole unit with ROUND(…, 0). Fill it down to D6.
6. Profit. In E2 and F2, type GAP's two profit formulas: adjusted demand times that supplier's margin per unit (the selling price minus that supplier's unit cost), minus the yearly fixed cost. Fill both down to row 6.
7. Optionally format E2:F6 as Currency (Home → Number Format). Save, then close Excel before you open Tableau.

Part 3: The chart in Tableau¶

1. Open Tableau Desktop. Under Connect, choose Microsoft Excel and open your saved workbook. Drag the sheet onto the canvas if it is not there already, then click the Sheet 1 tab at the bottom.
2. Drag Year to Columns. Tableau makes it a green SUM(Year) pill; right-click it and choose Dimension.
3. Drag Measure Values (at the bottom of the Data pane) to Rows.
4. On the Measure Values card below the Marks card, remove every measure except the sums of Standard Profit and Ethical Profit.
5. On the Marks card, choose Line or Bar, and drag Measure Names onto Color.
6. Format the vertical axis as currency (see section 5 for the menu path).
7. Worksheet → Export → Image…, save the image and insert it into a blank Word document.

Part 4: The managerial report¶

In the Word document that holds your chart, write a short executive summary: a brief introduction, then your answers to GAP's two questions.

1. Strategic and ethical recommendation (CLO 5). GAP asks you to assume that the focus groups showed the target customers care a great deal about a company's carbon footprint, and to recommend one supply chain to the board, weighing how large the profit difference is against the ethical context and the long-term value of the brand.
2. The value of an integrated BI chain. GAP asks why moving from text mining to a spreadsheet model to a visual story gives a sounder, more realistic plan than a static spreadsheet alone.

Use the structure from Day 6, Part 14 and Day 7, Part 6: answer first, the evidence with its numbers, the trade-off stated in plain units, the conditions and limits.

Part 5: Submit¶

See section 7.

3. Route B: offline in Anaconda JupyterLab¶

Use this route if %pip or the NLTK download fails in Try JupyterLab, or if you prefer your own laptop. It scores the same five comments with real VADER, reading the lexicon from the course folder (data/nltk_data), and it stops with an error instead of switching to a stand-in scorer if the lexicon cannot be found. Create a notebook inside the DATA300_GAP folder (next to data), named as in Route A. The five comments are bundled in data/ecovate_focus_group.csv.

# Route B for the capstone, Part 1: VADER offline, no downloads and no silent fallback.
from pathlib import Path

import nltk
import pandas as pd

NLTK_DIR = Path("data/nltk_data").resolve()
nltk.data.path.insert(0, str(NLTK_DIR))
from nltk.sentiment.vader import SentimentIntensityAnalyzer

analyzer = SentimentIntensityAnalyzer()     # raises an error if the lexicon is missing: no silent fallback
print("Scorer used:", type(analyzer).__name__, "| NLTK", nltk.__version__)

# The five focus-group comments from GAP, bundled with the course
df = pd.read_csv("data/ecovate_focus_group.csv")
assert len(df) == 5, "expected five comments"

# Score each comment: the compound score runs from -1 (very negative) to +1 (very positive)
df["Sentiment_Score"] = df["Review_Text"].apply(lambda text: analyzer.polarity_scores(text)["compound"])

# GAP's rule: baseline demand 1.0, plus up to 0.20 for a fully positive average
avg_sentiment = df["Sentiment_Score"].mean()
demand_multiplier = 1.0 + avg_sentiment * 0.20

pd.set_option("display.max_colwidth", 100)
print(df[["Review_ID", "Review_Text", "Sentiment_Score"]].to_string(index=False))
print(f"\nAverage sentiment score: {avg_sentiment:.3f}")
print(f"Demand multiplier (type this, as printed, into Excel C2:C6): {demand_multiplier:.3f}")

In JupyterLab you may replace the print(... .to_string(index=False)) line with display(df) for a nicer table. Build the Excel workbook by hand as in Route A, Part 2: Route B covers only the text-mining step. Export the notebook with Kernel → Restart Kernel and Run All Cells, then File → Print → Save as PDF.

4. Checks you can make¶

These tell you whether your work is right without giving any answers away.

Notebook

• The table has five rows, one per comment, and every score lies between −1 and +1.
• Your PDF shows which scorer ran (VADER's SentimentIntensityAnalyzer or a stand-in).
• The multiplier lies between 0.8 and 1.2, as GAP's rule allows, and equals 1 + 0.20 × the printed average (check it on a calculator).
• Every score and the average are visible in the PDF, not cut off.

Workbook

• Row 1 holds six headers; rows 2 to 6 hold years 1 to 5 and GAP's five base-demand figures.
• C2:C6 all hold the same multiplier, exactly as your notebook printed it.
• Every adjusted demand is a whole number, and equals base demand × multiplier rounded.
• Each profit = adjusted demand × that supplier's margin − the fixed cost. Check one row by hand for each supplier.
• Clicking D2, E2 or F2 shows a formula in the formula bar, not a typed number.

Chart

• Five years on the horizontal axis, two series, and a legend that names the two profit measures; nothing else on the Measure Values card.
• The vertical axis shows dollar signs.
• The values in the chart match your workbook for at least two years you checked (hover over a mark).

Report

• The introduction says what was decided and how, in two or three sentences.
• Your answer to question 1 names one supply chain, quotes numbers from your workbook, states how big the profit difference is in plain terms, and weighs it against the ethical and brand context.
• Your answer to question 2 explains what each tool added that a static spreadsheet could not, not just what each tool is.
• You say which scorer produced your multiplier, and you note at least one limit of scoring five short comments.

5. Known pitfalls and fixes¶

6. Beyond the capstone¶

Optional, not graded. Do these in copies of your notebook and workbook, after the graded files are finished.

1. Read the comments yourself. Score the five comments by hand on a scale from −1 to +1, then compare your scores with VADER's. Which comment would you score differently, and which words explain the difference (Day 3, Part 7)?
2. How much does the 0.20 matter? GAP caps the sentiment effect at 20%. In a copy of the workbook, put the 0.20 in its own input cell and build a one-way Data Table (Day 5, Part 4) with values from 0.10 to 0.30. Describe how the adjusted demand responds.
3. Uncertain demand. Base demand is a forecast, not a fact. Replace it with =ROUND(NORM.INV(RAND(), B2, 0.15*B2), 0) in a copy, simulate 500 trials of each year's two profits with a Data Table (Day 5, Part 9), and describe each supplier's range and chance of a loss.
4. A dashboard. In Tableau, add a second sheet showing adjusted demand by year as bars, and combine both sheets in a dashboard with a message title.
5. A one-page memo. Rewrite your answer to question 1 as a one-page memo in the structure of Day 6, Part 14, labelling each statement as a fact, an estimate or a judgement, and adding one condition that would change your recommendation.

7. Submission¶

1. Run the notebook from the top (Route B: Kernel → Restart Kernel and Run All Cells) and export it with File → Print → Save as PDF, Background graphics ticked.
2. Insert the Tableau image into the Word document before writing around it, and save the report from Word with File → Save As → PDF.
3. If you attach the notebook PDF to the end of the report, combine the files offline (Combining PDFs without internet), then open the result and check that every page is there.
4. Check that every number in the report matches your workbook and that the workbook is the final version.
5. Upload the files to Assignment 4 on GAP and submit before Sunday 29 November, 23:59 China Standard Time (how to submit).