Project Hallowell — Consumer Subscription Valuation
A 2-hour Consumer Internet / Sell-Side Valuation case study with a complete model answer
Modeled After
Qatalyst Partners
Sell-side valuation materials for a consumer subscription business: a historical and projected subscriber-count build with multi-period CAGRs as the substitute for a revenue build, NTM revenue and EBITDA multiples over time with historical cohort tables, a ten-quarter performance-versus-guidance beat/miss grid, an illustrative dilution factor sensitivity quantifying share-count growth as an argument for selling, and two-cohort comparables split between online media and eCommerce
Structure and exhibit set are modeled after Qatalyst Partners. The company, the financials and every figure in this case are entirely our own.
The Situation
Hallowell Heritage, Inc. (Nasdaq: HLWL) sells an annual membership to a digitized archive of historical records — census returns, parish registers, immigration and military files — used to build and maintain family trees.
Hallowell Heritage, Inc.
- Sector
- Consumer internet — a single annual subscription to a digitized historical-records archive, sold direct to consumers and renewed annually
- Size
- Geography
- United States; Nasdaq-listed
- Ownership
- Situation
The Prompt
You are the financial advisor to the Board of Directors of Hallowell Heritage, Inc. 75 per share in cash.
Supporting Materials
What you are handed at the start of the case, in the format a real process would use.
Blank modeling template
XLSXUnlock
What You Have to Produce
The deliverables, in the order the committee will read them. The exercise runs 120 minutes.
PART 1
Transform the cohort panel out of calendar space
PART 2
Find the edge of the panel and price what lies past it
PART 3
Establish that the age profile of the book is a different object from the curve
PART 4
Value the existing membership base on its own, under no new sales
PART 5
Price the same book as if retention were a rate rather than a curve
PART 6
Lifetime value, and the cost of acquiring one member
PART 7
Bridge to a value per share, and solve every market price back to the tail
PART 8
Reconcile to the comparables, and tell the Board what to do
How to Approach It
The order a strong candidate works in, and why. This is the shape of the answer — the finished answer deck and Excel model are in the solution set below.
- 01
Do the transform before you do anything else
- 02
Count the observations as well as the rates
- 03
Separate the installed base from the acquisition machine, early
- 04
State the basis and the horizon in one sentence, and never move them
- 05
Compute the flattering version, print it once, and use it nowhere
- 06
Run the exercise backwards at every price you have
- 07
Treat the comparables as a cross-check, not as the answer
- 08
Derive both bounds from rules and say the uncomfortable half
Key Concepts
The ideas this case is built on. Know these cold and the case becomes a question of execution rather than knowledge.
Retention is a curve, not a rate
In a consumer subscription the members most likely to leave leave first, so the population that survives is not the population that started. That means the renewal rate of the surviving base is a function of tenure rather than a property of the business, and a single blended rate applied to everybody for ever is wrong in a direction that depends entirely on which part of the book it was sampled from. Sample it from a base full of new joiners and you understate; sample it from an aged base and you overstate. The only honest treatment is to carry the rate by tenure and let the blend fall out.
Calendar space and tenure space
A cohort panel arrives indexed by calendar period because that is how the data was collected, and in that arrangement every column mixes tenures: one vintage's first year sits beside another's fifth. Re-indexing to periods since acquisition — putting every vintage's first year in the same column — is the operation that makes retention visible at all. It is mechanical, it takes two minutes in formulas, and skipping it is why so much cohort analysis produces numbers that are individually correct and collectively meaningless.
The age profile of a book is not its retention curve
Splitting today's base by membership year produces a tempting table: seven buckets, and a ratio between each adjacent pair. Those ratios are not renewal rates. They are the joint product of retention and of how many members the company acquired in each of the last seven years, and if acquisition was lumpy — a pandemic year, a promotional year — the ratios say far more about the acquisition history than about the members. The cheapest test is to look for a ratio above one hundred percent, which no renewal rate can be and which a lumpy acquisition history produces routinely.
The edge of the panel, and why the tail is the valuation
A panel observes what it has had time to observe. If the oldest vintage is seven years old, then nothing in the data says anything about the eighth year or the eightieth, and in a business where the surviving members are the durable ones that unobserved region carries a great deal of value. The discipline is to identify the edge explicitly, to derive scenarios past it from the shape of the curve the panel does show rather than from preference, and to carry every scenario through to a value per share so the reader can see how much of the answer rests on the part nobody has measured.
The installed base as a floor, and everything above it as a bet
The value of the members a subscription business already has, under no new sales, is the most defensible number available: it asks only that people who have already demonstrated a renewal behavior keep demonstrating it. Everything above that floor is a claim that the company will keep acquiring members at a stated cost, which is a claim about a market and a marketing department rather than about a cohort. Separating the two and telling a board which part of the price it is being asked to underwrite is most of what a sell-side valuation of this kind is for.
Lifetime value on a stated basis
A lifetime value is only meaningful with three things attached: the margin basis it is struck on, the discount rate, and the horizon. Struck on gross margin after the variable costs a member actually causes, discounted at a stated rate, over a stated finite number of years, it is a number a board can use. Struck undiscounted and run to infinity it is a marketing figure, and it is routinely two or more times the honest one. Print both, say which is which, and use only one.
Cost per gross addition, and what the ratio to lifetime value does not tell you
Setting lifetime value against the cost of acquiring a member gives a ratio everybody quotes, and it is worth computing — but it is a ratio between one number that is observed and one that is mostly assumed. A ratio comfortably above one on a short horizon is a strong statement; the same ratio produced by extending the horizon into the region the panel never observed is not. The undiscounted payback in membership years is a useful companion because it is almost entirely inside the observed window.
Solving a market price back to its implied assumption
When a valuation turns on one unobserved parameter, a share price becomes a statement about that parameter. Inverting the model — asking what value of the assumption makes the build reproduce the traded price — converts a disagreement about value into a disagreement about a single number, which is a far more productive conversation for a board to have. Say how the inversion was done, because an interpolation on a scenario ladder and a proper solve give slightly different answers, and say which way the method errs.
Two cohorts of comparables on a balance-sheet criterion
Consumer internet does not trade as one group, and the split that matters for a subscription business is often a balance-sheet fact rather than an income-statement one: how much of next year's revenue has already been collected and sits in deferred revenue. A business that collects in advance carries a large balance; a transactional one carries almost none. Declaring the test and the threshold before naming any constituent is what makes the split testable, and showing the threshold falls in a gap in the observed data rather than through a cluster is what turns a choice into a measurement.
When the criterion and the market disagree about placement
A declared criterion can place a company in one cohort while the multiple it actually trades at sits with the other. That is a finding rather than a flaw in the criterion, and it is usually the most useful page in the book. The resolution is never to pick the flattering side without saying so. It is to print both, to look for the reason in the operating record — a growth rate the cohort has and the company does not, a guidance history the market has been reacting to — and to say which of the two facts the recommendation relies on and why.
Guidance credibility as a valuation input
A plan that shows an inflection is worth exactly what management's record of delivering inflections is worth. Charting guided against actual over ten quarters on two measures at once is the cheapest way to establish that record, and the interesting case is when the two measures disagree — revenue held by price while the underlying unit count is missed. A reader who looks only at the revenue line concludes the plan is credible; the unit line is the one the cohort model depends on.
The dilution factor as an argument about timing
A share count that grows every quarter means a holder who does not sell owns less of whatever the company becomes, before any change in what it is worth. Quantifying that — the compound growth, the factor over the forecast horizon, and the equity value the company would have to reach for a holder to be no worse off than accepting today — is an argument about timing rather than about value, and no discounted cash flow captures it. It belongs on one axis and not in a grid: value per share is equity value divided by share count, so a grid of the two would produce many distinct answers while testing a single composite.
What Makes It Hard
The specific traps in this case — the places candidates lose the assessment without noticing.
Check Your Answer
Type the figures you produced and find out how many are right before you open the worked answer. You get a verdict and, where you are off, a pointer to the part of the build to re-check — never the number itself. Everything you type stays on this device.
How to type a figure. Digits, with an optional unit: 1,234.5, $1,234.5, 2.6x, 21.4%. For a negative use (20.0) or -20.0. Enter as many decimals as you carried — precision is never penalised.
What the Case Asked For
The arithmetic is only half of it. Tick off what you actually produced — this half is yours to score, because nothing can grade a written recommendation from a checkbox.
The Model Answer
The worked answer in full: answer deck and Excel model, built the way a banker would actually build them. It is a reference, not a submission — a strong answer under the clock is far shorter.
What the Solution Covers
- —Subscriber count build with multi-period CAGRs
- —NTM revenue and EBITDA multiples over time
- —Performance versus guidance beat/miss grid
- —Illustrative dilution factor sensitivity
- —Two-cohort comparable construction
- —Bid range float chart
Answer Deck
Full model answer, banker-formatted
Upgrade to Diamond
Sign up and upgrade to Diamond to unlock the answer deck, the Excel model and the audio walkthrough.
Get StartedThe Excel Model
The model is linked and tied out end to end — every schedule, every formula and every check, in the file itself.
Downloads are available to Diamond members
Excel Model and PowerPoint Deck and Answer Deck (PDF) — yours to open, edit and rebuild
Walkthrough
A conversational walkthrough of how to approach the case under time pressure — where to start, what to cut, and how the recommendation gets defended.
Audio Walkthrough
How to approach Project Hallowell — Consumer Subscription Valuation
60-second preview — upgrade to Diamond for the full walkthrough
Frequently Asked Questions
Why transform the panel at all — can't I just use the ratios between the tenure buckets of the current book?
No, and testing that is worth two minutes of your time because it is the assumption most people make. The buckets of today's base were filled by different acquisition years of different sizes, so the ratio between two adjacent buckets is the product of retention and of how many members were bought in each of those years. If acquisition was lumpy — and in this sector it usually was, because a single strong year can be twice its neighbor — the ratios are dominated by the acquisition history. The quickest proof is to look for a ratio above one hundred percent. Retention cannot do that; a small vintage followed by a large one can, routinely.
How do I decide what happens past the last tenure the panel observes?
Not by picking a number. Establish what the panel does show about how the rate changes from step to step, and then write each scenario as one sentence of arithmetic on that: continue the observed improvement for one more year and stop, continue it for several more, give back most of it. Each of those is a claim a reader can check against the same table you checked it against, which is what separates a scenario from a preference. Then carry all of them all the way to a value per share, because the spread between them is the honest measure of how much of this valuation is unobserved.
Why value the existing base separately when the company is obviously a going concern?
Because the two halves of the answer are supported by completely different evidence and a board is entitled to know which is which. The value of the members who are already here rests on a panel; the value of the members the company has not yet acquired rests on a forecast of how many it will buy and what it will pay. Netting them into one enterprise value hides that distinction. Separating them lets you say, in one sentence, how much of the price on the table is a claim about people who exist and how much is a claim about a marketing budget.
What discount rate should I use, and do I need to build a cost of capital?
State a rate, justify it in a sentence, sensitize it, and move on. A beta page is a page about the market rather than about the members, and on a case like this the tail assumption moves the answer by considerably more than any defensible movement in the discount rate does — which is itself worth demonstrating rather than asserting. Spend the time you save on the cohort work, which is where the exercise is graded.
Is it acceptable to run a valuation with no terminal value at all?
It is, provided you say so and say what it costs you. A finite horizon with no continuing value understates by whatever the years past the horizon are worth, and that is the direction a floor should err in — but only if the reader is told. What is not acceptable is a terminal value bolted onto a cohort model, because a perpetuity growth rate applied to a business whose retention curve you have just spent an hour building reintroduces every assumption you removed.
How much of the 120 minutes should the comparables get?
Less than you think, and last. They are a cross-check on a number you can build from this company's own members, not the source of the answer. Do them properly when you get to them — declare the criterion before you name anybody, screen on facts the extract carries, split into two labeled cohorts and print the dispersion inside each before claiming any difference between them — but do not open the workbook with them. A candidate who spends forty minutes on comparables and hands in no run-off has answered a different question.
The indication is a range. Do I evaluate both ends?
Yes, and separately. An indication of interest is not an offer, and the two ends of a range frequently imply materially different views of the business. Compute the premium at each end, the implied multiple at each end, and — most usefully — the value of your own unobserved assumption that each end implies. If the low end sits close to your base case and the high end requires something nobody has observed, that is the sentence the Board needs, and it is worth more to them than any single point estimate.
What should the recommendation actually contain?
A price to counter at, a price below which you recommend against proceeding, and the rule that produced each, applied to the same bridge as every other number in the book. Round each in the direction that keeps its own claim true — an ask rounds up, a floor rounds up. Then give the answer in both directions in one paragraph: what your own work supports about the indication as well as what it does not, plus the timing argument the share count makes. A recommendation that says only the encouraging half is easier to write and far easier for a buyer to dismantle.
About This Consumer Internet / Sell-Side Valuation Case Study
Consumer Internet / Sell-Side Valuation case study for investment banking interviews. 120-minute format covering subscriber count build with multi-period cagrs, ntm revenue and ebitda multiples over time, performance versus guidance beat/miss grid. Includes the full prompt, a model answer deck, a tied-out Excel model and an audio walkthrough.
This case study sits in Investment Banking, under Technology. Every case ships with the full prompt, the supporting materials, a complete model answer and an audio walkthrough of the judgment behind the recommendation.
120-Minute Format
The time limit a real assessment would give you
Answer Deck
Included in the model answer
Excel Model
Included in the model answer
Audio Walkthrough
How to approach the case under time pressure
200K+ students have used IB Vine to help land offers at top firms.
Moelis & Co.
“I loved IB Vine. I’m going into Private Capital Advisory (PCA); you have the lessons on there for PCA and that was actually a game changer. I went into those interviews and knew about the core PCA concepts like net asset value, primary fundraising vs. secondary advisory, etc. I swear I was on IB Vine like a few hours a day. The opportunity to have that learning section and go through the questions people submit and everything — there’s no better tool out there. I loved the guides/lessons. The user interface is amazing. The way you’ve simplified it is so spectacular — it’s just so much easier to digest. It’s fun to use too; I’d rather use IB Vine than scroll through a guide. To be honest, I think the product is perfect. I genuinely owe you a big thanks.”
Moelis & Co.
“IB Vine is a tool we really love to use in the club I’m a part of, and there’s really no other resource like it. You guys do a phenomenal job with the question bank. I recruited specifically for Energy banking, and IB Vine was my most used resource for generalist questions (which were about 50% of my interview questions; the rest being Energy-specific); the majority of such questions I saw in interviews were at least similar (if not the same) to the ones on IB Vine.”
Perella Weinberg
“Once I read through the BIWS learning guides, I really didn’t refer to them again. I didn’t even really run through the 400 question guide once I found IB Vine, which I heard about through one of my classmates. We even get a free subscription (like most business schools) to Wall Street Prep, and if I’m being completely honest, I never even logged in to WSP. IB Vine is pretty much the only tool I used (along with our club question bank & mock interviews with peers) and it was invaluable for recruiting.”
Cantor Fitzgerald
“What a platform, made such a huge difference. I did superdays at Evercore, PJT, M. Klein and Barclays among others from a non-target school and did not miss a single technical in any interview process through prepping with IB Vine.”
Barclays
“Very accurate questions and all of the solutions are easy to follow. At least 10 of the questions I studied through this platform appeared in my Round 1 or Superday interviews.”
TD Securities
“THANK YOU SO MUCH IB VINE, I COULD NOT HAVE DONE THIS WITHOUT YOU, SERIOUSLY!!! IB Vine was the best website ever. I spent at least two hours on this daily (seriously) from October through I get my offer in February.”
Citadel
“I’m doing public equities this summer and next. I know the name is “IB Vine” but at the undergrad level a lot of the technicals across public equities are the same as investment banking. I recommend your software to all my friends!”
Piper Sandler
“I loved this site! 1000% this is the best resource I used in the process.”
Lazard
“This was the greatest tool ever. I genuinely enjoyed running through the technicals/behaviorals and it was very helpful!”
Houlihan Lokey
“IB Vine was the most helpful resource I had during recruiting. I will continue to promote it to other students at WashU and elsewhere.”
Jefferies
“Awesome product, helped me crush my technicals in my interviews and land a great role. Thank you, seriously was a huge help.”
Morgan Stanley
“Very helpful to get real-life questions unlike the 400 guide, especially for merger math. Built deeper understanding of key concepts.”
Houlihan Lokey
“Extremely helpful study tool that carried me through the recruitment process from start to end.”
Evercore
“You guys are doing great work over there with IB Vine. Absolute staple for interview prep.”
Rothschild
“IB Vine was incredibly helpful and I am forever thankful for all the help.”
RBC
“IB Mock was amazing - I used it for multiple hours. Also the flashcards and the lessons features on IB Vine were fantastic. Thank you!”
Dragoneer
“IB Vine is such a great platform, really impressive. There is so much value in this. The audio podcasts / mock interview library are pretty incredible.”
Case Study Preparation
Explore All Case Studies
100+ case studies, each with the full prompt, supporting materials and an audio walkthrough; most also ship a model answer deck and a tied-out Excel model.
Every case has a public page like this one. The member library is the signed-in index members work through.