LLM-as-analyst: three exercises

I'm giving a short presentation to my MBA class. Attached: glp1_medicaid_nc_sc.csv and glp1_medicaid_nc_sc_codebook.md. Read the codebook first. Each row is one state in one quarter, 2023Q1 to 2025Q3. The outcome is rx_per_1000_enrollees: Medicaid prescriptions for obesity-labeled GLP-1 drugs (Wegovy, Zepbound, Saxenda) per 1,000 Medicaid enrollees. Spending columns are gross amounts, before manufacturer rebates.

Background: North Carolina's Medicaid program began covering these drugs for weight loss on August 1, 2024 (2024Q3), and South Carolina's on November 1, 2024 (2024Q4). The seven states with group = "Comparison" did not cover them. Question: how much did coverage increase prescriptions and spending?

Analysis. Use Python. Print every number you use, and put no number about the data in the deck that you did not print.
1. Plot rx_per_1000_enrollees by quarter for North Carolina, South Carolina and the simple (unweighted) average of the seven comparison states. Mark each start date.
2. North Carolina vs the comparison states. "Before" = 2023Q3 to 2024Q2, "after" = 2024Q4 to 2025Q3. Leave out 2024Q3, when coverage ran for only part of the quarter. Compute the four averages and the difference-in-differences. Then run the regression rx_per_1000_enrollees = a + b·NC + c·after + d·NC×after on the same rows and show that d equals the difference-in-differences.
3. Gap chart: for each quarter, North Carolina minus the simple average of the seven comparison states, shifted so the gap is zero in 2024Q2. Is the gap flat before coverage started?
4. Fake-treatment check: pretend each comparison state started coverage on August 1, 2024. Compute its difference-in-differences against the other six, with the same quarters as step 2. Show the seven fake effects next to North Carolina's real one in one chart.
5. Repeat step 2 for South Carolina, with "before" = 2023Q4 to 2024Q3 and "after" = 2025Q1 to 2025Q3. Compare it with North Carolina.
6. Repeat step 2 with spend_per_enrollee_usd. Also add up North Carolina's gross_spend_usd for 2024Q4 to 2025Q3.
Ignore the standard errors in the regression output. They assume many states were treated independently; with one treated state, the fake-treatment check in step 4 is the better test.

Deck (.pptx, 16:9, 10 slides):
1. Title: the question in one line
2. Context: what GLP-1 drugs are, what they cost, and why state Medicaid programs argue about covering them for weight loss
3. The data and the objective: what the file contains, where it comes from, what it leaves out, and what we want to learn
4. Step 1 chart
5. Step 2: the table of four averages and the regression check
6. Step 3 gap chart
7. Step 4: is the effect real?
8. North Carolina vs South Carolina
9. Dollars
10. Bottom line: the effect, how sure we are and why, and one other thing that changed in North Carolina or nationally in 2024 to 2025 that could explain part of it

Context slides: for outside facts (prices, policy history, market size), use web search if you have it and put the source and year on the slide. If you cannot check a fact, leave it out.

Charts:
- Make every chart in Python (matplotlib) and insert it as an image at 200 dpi or higher.
- One message per chart, stated in the slide title.
- Label both axes, with units.
- When a chart has four or fewer lines or groups, label them directly on the chart instead of using a legend.
- Give each state or group the same color on every slide.
- Mark key dates with a thin vertical line and a short label.
- No pie charts, 3-D effects or charts with two y-axes.
- Size text so it reads at 14 pt or larger when the slide is shown full screen.
- Put a one-line source under each chart.
- Look at each chart image before inserting it and fix overlapping labels or cut-off text.

Slides: titles are full sentences that state the finding. At most three bullets per slide. Speaker notes on every slide: what to say in one minute, and the hardest question a classmate could ask about it, with a short answer.

When you're done, give me the .pptx file and list in the chat any choice you made that I didn't specify.

I'm giving a short presentation to my MBA class. Attached: pew_social_media.csv and pew_social_media_codebook.md. Read the codebook first. Pew Research Center surveyed 5,123 U.S. adults from February 24 to March 2, 2025; the file keeps the 5,076 who answered all four platform questions, one row per person.

Question: what are the main segments of U.S. social media users, and which one should an AI assistant app target?

Analysis. Use Python. Print every number you use, and put no number about the data in the deck that you did not print. Weight every percentage with the weight column.
1. Check the file. Report rows and columns, then the weighted percent of adults who ever use YouTube, Facebook and TikTok (score above 0). Pew published 84%, 71% and 37%. If you are off by more than one point, find out why before going on.
2. Segment. Run k-means on the raw 0-5 scores facebook_score, youtube_score, x_score and tiktok_score, with no rescaling and no sample weights in the fit, k = 4, n_init=50, random_state=0. Report each segment's unweighted average scores (the cluster centers) and its weighted share of adults. Then run k = 3 and k = 5 and say in one sentence each which segments merge or split.
3. Name each of the four segments from its average scores. Then test each name: report one number that could contradict it (for example, if you call a segment "light users", report the share of that segment who use YouTube every day).
4. Profile. Make one table with the four segments as columns plus an "All" column, showing the weighted percent of people in each category of age_group, gender, education, income_tier, race_ethnicity, chatgpt_use and tiktok_ban. Each column should add to 100 within a variable.
5. Rule check. Write a rule of at most three if-then steps on the score columns that puts people into the same four segments. Report the weighted percent of people the rule assigns to the same segment as k-means.
6. Brief. An AI assistant app wants to win over adults who have heard of ChatGPT but never used it. Pick one segment. Leaving out blank chatgpt_use answers, report the weighted percent of that segment who answered "Heard of it, has not used"; the size of the opportunity, meaning people in that segment with that answer as a weighted percent of all respondents; and the platform you would advertise on, with the share of the target group who use it daily (score 4 or 5).

Deck (.pptx, 16:9, 10 slides):
1. Title: the question in one line
2. Context: how many Americans use each major platform, how that has changed, and why marketers segment audiences instead of targeting "social media users"
3. The data and the objective: who Pew surveyed and how, what the weights do, what the file leaves out (for example, Instagram), and what we want to learn
4. Data check against Pew
5. The four segments: average scores and shares, as a chart
6. Why four: what merges at k = 3 and splits at k = 5
7. Who is in each segment: profile highlights
8. A simple rule gets nearly the same segments
9. Recommendation for the AI assistant brief
10. What these data cannot tell us before we spend money

Context slides: for outside facts (platform trends, AI adoption, ad spending), use web search if you have it and put the source and year on the slide. If you cannot check a fact, leave it out.

Charts:
- Make every chart in Python (matplotlib) and insert it as an image at 200 dpi or higher.
- One message per chart, stated in the slide title.
- Label both axes, with units.
- When a chart has four or fewer lines or groups, label them directly on the chart instead of using a legend.
- Give each segment the same color on every slide.
- No pie charts, 3-D effects or charts with two y-axes. For the segment profiles, use bars or a heatmap, not a wall of numbers.
- Size text so it reads at 14 pt or larger when the slide is shown full screen.
- Put a one-line source under each chart.
- Look at each chart image before inserting it and fix overlapping labels or cut-off text.

Slides: titles are full sentences that state the finding. At most three bullets per slide. Refer to segments by name, never by cluster number. Speaker notes on every slide: what to say in one minute, and the hardest question a classmate could ask about it, with a short answer.

When you're done, give me the .pptx file and list in the chat any choice you made that I didn't specify.

I'm giving a short presentation to my MBA class. Attached: zip_month.csv, data_dictionary.csv and README.md. Read the dictionary and README first. Each row is one ZIP code in one month, January 2019 to September 2026: 31 Manhattan and 25 Brooklyn ZIP codes. Rent is Zillow's typical asking rent on a new lease; home value is Zillow's typical home value (not a sale price); the mortgage rate is Freddie Mac's 30-year fixed rate.

Question: a new graduate is deciding whether to rent or buy in Manhattan or Brooklyn. Would buying in January 2021 have paid off, and does buying make sense today?

Cost model (use exactly this):
- The mortgage rate column is in percent: 2.74 means 2.74% a year.
- Owning costs, per year, (mortgage rate + 2) ÷ 100 × home value, minus the gain in home value. The extra 2 points cover maintenance, property tax and insurance.
- Renting costs 12 × the monthly rent per year.
- Break-even appreciation, in percent per year = mortgage rate + 2 − 100 × 12 × rent ÷ home value. If home values rise faster than this, owning costs less.

Analysis. Use Python. Print every number you use, and put no number about the data in the deck that you did not print. Borough figures are simple averages across ZIP codes.
1. Rents and home values since COVID. For each ZIP code, index rent and home value to February 2020 = 100, then average by borough. Plot the four lines. How far did each fall by January 2021, and where did each end in September 2026?
2. The January 2021 decision. For each ZIP code, compute the price-to-rent ratio, home value ÷ (12 × rent), and the break-even appreciation at the January 2021 mortgage rate. Report borough averages and the range across ZIP codes, and plot every ZIP code's break-even on one chart, grouped by borough.
3. What happened next, January 2021 to January 2026. Renting = the January asking rent × 12, for each year 2021 to 2025, added up. Owning = 5 × (January 2021 mortgage rate + 2) ÷ 100 × January 2021 home value, minus (January 2026 home value − January 2021 home value). For each ZIP code, also compute (renting − owning) ÷ its January 2021 home value, in percent; positive means owning cost less. For each borough, report the average renting cost, the average owning cost, the average of that percent across ZIP codes, and the number of ZIP codes where owning cost less. Repeat the count with 1 and 3 in place of 2.
4. Scatter: each ZIP code's break-even in January 2021 (horizontal axis) against its actual yearly change in home value from January 2021 to January 2026 (vertical axis, compound annual rate: (January 2026 value ÷ January 2021 value) to the power 1/5, minus 1, × 100, in percent per year), with a 45-degree line. Use different markers for the two boroughs.
5. Today. Recompute the price-to-rent ratio and break-even for September 2026 at that month's mortgage rate. Plot the price-to-rent ratio by borough and the mortgage rate since 2019 as two stacked panels sharing the time axis.
6. For a graduate deciding today, what yearly rise in home values would make buying worth it in each borough, and how does that compare with the borough average of the step 4 yearly rates?

Deck (.pptx, 16:9, 10 slides):
1. Title: the question in one line
2. Context: what happened to New York City rents, home prices and mortgage rates from 2020 to 2026, and how people usually think about renting versus buying
3. The data and the objective: what the file contains, where it comes from, the cost model in one line, and what it leaves out
4. Step 1 chart
5. The January 2021 decision. End the slide by asking the class which borough they would have bought in.
6. What happened: renting vs owning over five years
7. Step 4 scatter
8. Today
9. Recommendation for the graduate
10. What the cost model leaves out (transaction costs, lease renewals and rent-stabilized apartments, the different homes behind the rent and value indexes, the return on a down payment, tax deductions), and whether each one favors renting or owning

Context slides: for outside facts (rent records, rate history, NYC policy), use web search if you have it and put the source and year on the slide. If you cannot check a fact, leave it out.

Charts:
- Make every chart in Python (matplotlib) and insert it as an image at 200 dpi or higher.
- One message per chart, stated in the slide title.
- Label both axes, with units.
- When a chart has four or fewer lines or groups, label them directly on the chart instead of using a legend.
- Give each borough the same color on every slide.
- Mark February 2020 and January 2021 with thin vertical lines and short labels.
- No pie charts, 3-D effects or charts with two y-axes.
- Size text so it reads at 14 pt or larger when the slide is shown full screen.
- Put a one-line source under each chart (Zillow Research; Freddie Mac).
- Look at each chart image before inserting it and fix overlapping labels or cut-off text.

Slides: titles are full sentences that state the finding. At most three bullets per slide. Speaker notes on every slide: what to say in one minute, and the hardest question a classmate could ask about it, with a short answer.

When you're done, give me the .pptx file and list in the chat any choice you made that I didn't specify.