Marketing analytics data-driven techniques with Microsoft Excel

1. 1Define the purpose first: name the product and the decision the demographics will inform (for example, where to advertise). The source frames it this way: before recommending where to advertise, the analyst must understand the type of person likely to purchase the product.
2. 2Get a record-level sample with one row per customer and one column per attribute: age, gender, income, location. The ESPN file holds a randomly chosen sample of 1,024 subscribers. Random selection matters because every percentage you compute inherits the sample's bias.
3. 3Build the age profile in an Excel PivotTable. Drag Age to Row Labels and Age to Values. Excel defaults to Sum of Age, so double-click the heading and change it to Count of Age. Then group the ages into 10-year bins (8-17, 18-27, and so on) and set Value Field Settings to show the count as a percentage of the total.
4. 4Build the gender profile. Drag Gender to Column Labels and Gender to Values, then use Value Field Settings > Show Value As > % of Row Total to get the share of each gender.
5. 5Repeat the same count-and-percentage process for location (rural, suburban, urban) and for income, binning income the way you binned age.
6. 6Read each profile as a distribution, not an average. Name the segment holding the largest share, such as ages 18-27 and 28-37, and state its share explicitly.
7. 7Translate the profile into a media or targeting action. Match the segment to channels that reach it (a TV show whose audience skews toward the same age and gender). Avoid channels that reach the wrong group, as in the heavy-metal-magazine-on-Golden-Girls example.
8. 8Where you need to sort or compare, cross-tabulate two attributes (for example age by gender) in the PivotTable. Keep each cell large enough to trust given the sample size.

The espn.xlsx file contains demographic data on 1,024 randomly chosen subscribers to ESPN: The Magazine. The first record is a 72-year-old male in a rural location with a family income of $72,000. To profile age, you put Age in Row Labels and Values, switch to Count of Age, group into 10-year bins, and show as a percentage. The result: 8-17 is 8.20%, 18-27 is 28.81%, 28-37 is 28.81%, 38-47 is 17.58%, 48-57 is 8.01%, 58-67 is 2.64%, 68-77 is 3.22%, 78-87 is 0.78%, 88-97 is 1.17%, 98-107 is 0.20%, and 108-117 is 0.59%. Ages 18-37 account for about 57.6% of subscribers, so the book concludes that most subscribers are 18-37 and ESPN should advertise on TV shows targeting that age group. The gender PivotTable (Count of Gender as % of row total) shows about 79.69% of one gender versus 20.31% of the other, per the source's Figure 1-24. Because the split is lopsided, an ad on a show like Project Runway would be a poor use of the budget.

• If one bin or group holds a large share of customers (ages 18-37 here, over half), then target media and messages at that group rather than at the overall population.
• If the gender split is lopsided (about 80/20 here), then do not buy placements on programming aimed at the minority gender unless you have evidence of a separate opportunity there.
• If a candidate advertising channel's audience demographic differs from your customer profile, then reject it as inefficient (heavy-metal fan magazine on a retiree-oriented show).
• If you compare groups, then show percentages (% of row or column total) rather than raw counts so group sizes do not distort the comparison.
• If the sample is not random or is small, then treat the profile as provisional and check it against a larger or randomly drawn sample before committing budget.

• Accepting Excel's default Sum of Age instead of Count of Age: the table shows meaningless totals like thousands of years rather than subscriber counts.
• Reading raw counts instead of percentages, so a large group looks important only because the sample is large; the tell is a table with no share column.
• Profiling from a non-random or convenience sample, so the demographic picture reflects who was easy to reach; the tell is no documented sampling method.
• Describing the 'average' subscriber instead of the distribution, which hides a long tail (the ESPN data has subscribers aged 98-117) and the real concentrated segments.
• Stopping at description: the profile is produced but never tied to a channel or targeting decision, so no advertising choice changes.

• ▸Product and decision: name the product and the decision (e.g., where to advertise) this profile supports.
• ▸Data source and sample size: record where the data came from, how it was sampled (random?), and the number of records.
• ▸Age bins and % share: list 10-year bins with the percentage of customers in each.
• ▸Gender split: list the % of each gender (Count of Gender as % of row total).
• ▸Location split: list % rural / suburban / urban.
• ▸Income bins and % share: define income bands and the % of customers in each.
• ▸Dominant segment: name the attribute combination holding the largest share and state its percentage.
• ▸Mismatch channels to avoid: list media whose audience demographic differs from the dominant segment.
• ▸Action chosen: state the channel or targeting decision and the profile evidence that justifies it.

1. 1Define the decision first: which choice are you trying to predict (carpet cleaner purchase, hotel selection, a price point)? Only attributes that plausibly drive that choice go in. The carpet-cleaner study chose its five attributes because researchers believed they drove choices, not just because they were measurable.
2. 2List attributes and levels explicitly. The source example uses package design (A, B, C), brand (1, 2, 3), price ($1.19, $1.39, $1.59), Good Housekeeping approval (yes/no) and guarantee (yes/no). Each level must be a real option you could offer.
3. 3Count the full factorial (3 x 3 x 3 x 2 x 2 = 108 profiles) and recognize that no respondent ranks 108 cards. Select a manageable subset of profiles (the source generates them with Evolutionary Solver; its regression range spans 18 profile rows).
4. 4Have each consumer rank the profiles from best (#1) to worst, then rescale so that a higher number means a more preferred profile. The source regresses on this 'rescaled rank'.
5. 5Code each attribute as dummy variables, leaving one level per attribute as the all-zeros baseline. Run a multiple regression of rescaled rank on the dummies, one regression per consumer.
6. 6Check fit before reading any coefficient. In the source, all p-values are below .05, R² is 0.98, and the standard error is 0.95, meaning predicted ranks are accurate within about 1.9 about 95 percent of the time. If R² is low or the error is high, you omitted an attribute the consumer cares about, or preferences involve interactions or nonlinearities. Test those with the Chapter 10 techniques.
7. 7Read each coefficient relative to its baseline level, and compare attribute swings. Price from $1.59 to $1.19 is worth +7.67 rank points, and a guarantee is worth +4.5. Those swings are your measure of relative importance.
8. 8Validate predictions by computing predicted ranks with TREND across all profiles and checking close agreement with actual ranks. Then feed the part-worths into a simulator or demand estimate. When you price off subjective demand estimates, keep the minimum and maximum prices consistent with consumer preferences.

Green and Wind's carpet-cleaner study asks a consumer to rank product profiles built from five attributes: design (A/B/C), brand (1/2/3), price ($1.19/$1.39/$1.59), Good Housekeeping approval, and guarantee. The regression for one consumer fits almost perfectly: R² = 0.98, standard error 0.95, all p-values under .05. The equation is Predicted Rescaled Rank = 4.833 − 4.5A + 3.5B − 1.5(Brand 1) − 2(Brand 2) + 7.667($1.19) + 4.83($1.39) + 1.5(Approved) + 4.5(Guarantee). Every coefficient is relative to the all-zeros level. A $1.19 price ranks 7.67 higher than $1.59 and 2.83 higher than $1.39. Brand 3 ranks 1.5 above Brand 1 and 2 above Brand 2. Approval adds 1.5 and a guarantee adds 4.5. Reading across attributes, this consumer values a guarantee (4.5) more than the drop from $1.39 to $1.19 (2.83), and price is the dominant attribute overall because the full $1.59-to-$1.19 swing is 7.67. Approval (1.5) is the weakest lever. The implication is that adding a guarantee can protect a $1.39 price better than cutting to $1.19. Note that the source's prose on design (C '3.5 lower than B') does not match the sign of the printed B coefficient, so verify the design coding in your own data before reading those contrasts.

• If the conjoint regression's R² is low or the standard error is high, then add missing attributes or test interaction and nonlinear terms before trusting any coefficient.
• If one attribute's level swing (such as price, 7.67 points) exceeds another's (such as approval, 1.5), then spend effort on the larger swing first and treat the smaller one as a tie-breaker.
• If the full factorial is large (108 profiles for 5 attributes), then rank a designed subset of profiles instead of asking the consumer to rank everything.
• If you cannot trust a linear or power demand curve, then estimate demand at the lowest, middle, and highest reasonable prices, fit a quadratic, and optimize with Solver. Keep the price range within what customers consider realistic.
• If a product has a tie-in (razor and blades), then price against total profit including the tie-in. Customer preference for owning the primary product can justify a price below cost, as in the $1.00 razor.

• Omitting an attribute customers care about. The tell is a low R² and a high standard error in the conjoint regression.
• Misreading dummy coefficients as absolute values rather than relative to the baseline level. The tell is claims like 'Brand 3 scores 0', when it only means 'Brand 3 is the reference'.
• Choosing attributes because they are easy to measure rather than because they drive choice. The tell is a model with a good fit but no actionable lever.
• Using price ranges that customers would not consider realistic when building a subjective quadratic demand curve. The tell is an optimal price at the edge of the range or a sales force that balks at the figures.
• Optimizing the primary product's price alone and ignoring tie-in profit. The tell is an 'optimal' razor price above cost while blades are the real profit source.

• ▸Decision being predicted: state the single customer choice this study must explain.
• ▸Attributes and levels: list each attribute with its levels (e.g., price $1.19/$1.39/$1.59; guarantee yes/no).
• ▸Full factorial count: multiply the level counts to get total possible profiles.
• ▸Profile subset chosen: record how many profiles respondents rank and how they were generated.
• ▸Baseline level per attribute: name the all-zeros level used in dummy coding.
• ▸Fit check: record R², standard error, and whether all p-values are below .05.
• ▸Coefficient table: list each dummy's coefficient, read relative to its baseline.
• ▸Largest swing per attribute: note the highest-minus-lowest level coefficient to rank importance.
• ▸Action implication: state the one trade-off the swings support (e.g., guarantee vs. price cut).

1. 1Define the behavior as one measurable event before you model it. Use a binary outcome (subscribed = 1, did not = 0; responded to the mailing = 1; churned = 1) for likelihood to purchase. Use units sold per period for quantity. Use a promotion flag for promotion response. Pick one per model.
2. 2Describe who buys before you model. Build a PivotTable on the customer file: drag the field (for example Age) to Row Labels and to Values, change Sum to Count, then set Value Field Settings to % of Column Total. For the ESPN: The Magazine file (1,024 randomly sampled subscribers), this shows the buyer's demographic profile and tells you which attributes belong in the model as predictors.
3. 3Rank candidate drivers with cross-tabs before regression. In the station wagon file (family size, income, bought yes/no), compute the share of buyers in each of the four cells (high/low income x large/small family). The cell with the largest buyer share, and the attribute that separates cells most, is your first hypothesis.
4. 4Estimate quantity-versus-price as a demand curve D(p), the units demanded at price $p. Fit the two standard forms, linear and power, using the Excel Trend Curve for a back-of-the-envelope fit. Record the unit variable cost UC, because you need both D(p) and UC to find a profit-maximizing price with Solver.
5. 5Separate price from everything else that moves sales. For weekly data like the 3M tape file (units, price, ad campaign flag, end-cap flag) or quarterly cranberry data (own price, competitor price, seasonality, trend), put price, promotion flags, and competitor price together in a multiple regression. Do not read a price effect off a raw price/sales scatter.
6. 6Model binary outcomes with logistic regression, not linear regression. Binary dependent variables violate the linear-regression assumptions. Fit ln(p/(1-p)) = b0 + b1x1 + ... + bnxn by maximum likelihood, then convert back with p = e^(b0+b1x1+...)/(1+e^(b0+b1x1+...)). For direct mail, typical predictors are recency, frequency, and monetary value.
7. 7If your data are grouped, use count data. In StatTools, select the Number column as the raw Count, then the dependent variable and the independents (Young, LowInc, MidInc). Check that each p-value shows the predictor is significant. Compute each group's predicted probability and compare it with the actual group probability. The book reports they are close.
8. 8Judge a promotion on net benefit. Count its costs against the benefits. If it brings in new customers, add their long-run (lifetime) value to the benefit side. If costs exceed benefits, do not run it.

Take the subscription model fitted to grouped count data. Its predictors are Young, LowInc, and MidInc dummies. Each coefficient is added to the exponent only when the dummy equals 1, so each one multiplies the odds of subscribing by e^coefficient. LowInc has a coefficient of 1.487, so its odds multiplier is e^1.487 ≈ 4.4. MidInc has a coefficient of 1.005, so its multiplier is e^1.005 ≈ 2.7. Holding other factors equal, a low-income group has roughly 4.4 times the subscription odds of the baseline group, and a mid-income group roughly 2.7 times. The odds are not the probability, so convert the full exponent with e^x/(1+e^x) before quoting a likelihood. The book then checks each group's predicted probability against its actual probability and finds them close. That check is the validation step. Before building such a model, use the ESPN profile as a screen. The first subscriber in the file is a 72-year-old male in a rural location with a $72,000 family income. A PivotTable of age shares tells you whether the readership matches the media you plan to buy. A heavy-metal fan magazine advertised on a retiree show like Golden Girls would waste budget. (The scan garbles the Young coefficient and intercept, so work from the readable coefficients above.)

• If the outcome is 0/1 (buy, respond, churn), use logistic regression. If it is units per period, use demand-curve or multiple regression with price, promotion, and competitor-price terms.
• If a promotion's costs exceed its benefits, do not run it. If it acquires new customers, include their lifetime value in the benefits before deciding.
• If you need a fast price answer, fit the linear and power demand curves with Trend Curve and Goal Seek. If you need the profit-maximizing price, feed D(p) and UC to Solver.
• If a predictor's p-value shows it is not significant, drop it or do not use it to target. If it is significant, compare predicted and actual group probabilities before trusting it.
• If prices fall over time for a product with falling unit cost, check for a learning curve before blaming demand. Costs typically drop 10-30% each time cumulative production doubles.

• Using linear regression on a 0/1 purchase outcome. The tell is predicted probabilities that fall outside 0 to 1 and residuals that violate regression assumptions.
• Reading price response off raw sales data while ignoring ads, end-cap displays, seasonality, and competitor price. The tell is a price coefficient that changes sign or size sharply once the flag columns (ad, end cap) are added.
• Judging a promotion on first-period lift only. The tell is a promotion report with costs and incremental units but no line for the lifetime value of the customers it acquires.
• Treating the demand curve as fixed. A firm's demand curve is constantly changing and depends on factors outside its control. The tell is a price recommendation built on an old fit that is never refreshed.
• Targeting without profiling the buyer. The tell is an advertising placement that does not match the demographic profile of the product's actual purchasers.

• ▸Behavior to explain: state the one outcome (binary purchase, units per period, promotion response).
• ▸Data file and unit of observation: rows, time span, and sample size.
• ▸Buyer profile (PivotTable): top demographic splits as % of column total.
• ▸Candidate drivers: list price, competitor price, ad flag, end-cap flag, season, demographics, recency/frequency/monetary value.
• ▸Model type: logistic regression for 0/1, multiple regression for units; justify in one line.
• ▸Demand curve and unit cost: fitted form (linear or power) and UC.
• ▸Significant predictors: those that pass the p-value test, with the sign and size of each effect.
• ▸Predicted vs. actual check: predicted and actual probability by group, and the gap.
• ▸Promotion net benefit: cost, incremental benefit, and lifetime value of any new customers.

1. 1Define the period and the event. Pick one period (the source uses annual for cable TV and cars, weekly for orange juice). Count a customer as retained only if they buy again in the next period. Retention rate = customers retained ÷ customers at the start of the period.
2. 2Estimate two rates, not one, when purchases are discrete choices. Using the purchase history (the OJ file codes 0 = bought from competition, 1 = bought from you), compute P(buy from you next | bought from you last) and P(buy from you next | bought from a competitor last). The GM Malibu case has this shape: 70% repeat, 25% switch in from the competition.
3. 3Convert retention into a multiplier. With per-period discount rate i and retention r, and $1 of profit at the start of each period from the surviving customers (1, r, r², … discounted by 1/(1+i)), the multiplier is (1+i)/(1+i−r). It reproduces the source figures: 2.2 at r=60%, 3.667 at 80%, 5.5 at 90%, all at i=10%.
4. 4Value the customer: lifetime value = profit per period × multiplier. This holds only if per-period profit does not depend on how long the person has been a customer. If it does, build the year-by-year cash flows explicitly (Markov chain) instead of using the shortcut.
5. 5Build a sensitivity grid. Put retention rates on one axis and discount rates on the other, using Excel's What-If Analysis > Data Table, with the multiplier as the output cell. Read how steeply value rises per retention point before you spend anything.
6. 6Price the retention lever. Write down what it costs: a fixed spend (Verizon adding in-store service reps), a margin cut (OJ quality improvement costs 30 cents per gallon), or a giveaway (GM's $1,000 incentive, Ford's free oil changes). Compare the value gained from higher retention with that cost.
7. 7Treat the retention uplift as a range, not a point. Where the effect is unknown, model it as a uniform distribution (OJ: 0–10% retention gain; GM: 6–13%; Ford: 2–15%). Run about 10,000 Monte Carlo iterations and read the probability that the lever beats the status quo, for example 'What is the chance the free oil changes increase the value of this customer?'
8. 8Balance retention against acquisition (Blattberg and Deighton). Spending $400 to acquire a customer worth $300 in long-term profit loses money. Compare marginal dollars on retention and on acquisition against customer value, and shift spending toward whichever gives the higher return.

A cable TV firm has an 80% annual retention rate, a 10% discount rate and a $300 annual profit per subscriber. The multiplier is (1.10)/(1.10−0.80) = 3.667, so a subscriber is worth $300 × 3.667 = $1,100. Run the same table at 10% discount. Retention of 60% gives a multiplier of 2.2, so the subscriber is worth about $660. Retention of 90% gives 5.5, so about $1,650. A 10-point gain from 80% to 90% lifts value by about $550, and a 20-point loss cuts it by about $440. The source's own comment on the table is 'This shows the importance of increasing the retention rate!' It adds that Reichheld observed that for a credit card company, moving annual retention from 80% to 90% could double the multiplier. The acquisition test follows directly. Paying $400 to win a subscriber worth $1,100 clears the bar; paying $400 for a customer worth $300 does not.

• If you plan to spend on acquisition, then first compute lifetime value from retention and margin. Do not spend more to acquire a customer than the customer's long-term profit (the $400 vs. $300 case).
• If retention is below roughly 60–70% at a 10% discount rate, then expect a small multiplier (2.2 at 60%) and test a retention lever first. Each added retention point is worth more as the rate climbs (3.667 at 80%, 5.5 at 90%).
• If a retention initiative has an uncertain effect, then do not decide on its average uplift. Model the uplift as a range, run the Monte Carlo simulation, and approve only if the probability of beating the status quo is acceptable.
• If the lever reduces unit margin (OJ's 30 cents per gallon), then net the lost margin against the extra expected lifetime value before approving it.
• If per-period profit changes with customer tenure, then abandon the simple profit × multiplier formula and model the cash flows period by period.

• Treating retention as a fixed given. The tell: retention appears as a constant in the model and no spending lever is attached to it, although the source says a firm can raise retention by spending money.
• Mixing cash-flow timing conventions. The tell: your multiplier differs from a published one by a factor of (1+i). State whether profit lands at the start or the end of each period, and use (1+i)/(1+i−r) for start-of-period profit.
• Using one retention rate for everyone. The tell: no separate rate for 'bought from us last time' versus 'bought from a competitor last time', so you cannot value a customer won from the competition (the Ford 20-year question).
• Using the point estimate of the loyalty gain. The tell: a yes/no verdict from one scenario (for example, assuming exactly +10% retention) with no probability of success attached.
• Valuing retention gains while ignoring their cost. The tell: the model credits higher retention but never subtracts the 30 cents per gallon, the $1,000 incentive, or the extra service cost.

• ▸Period length: state the period (week, year) and how a 'retained' customer is counted.
• ▸Retention rate r (current): retained customers ÷ starting customers; add the competitor-to-you rate separately if known.
• ▸Discount rate i: per-period rate applied to future profits.
• ▸Profit per customer per period: margin before the lever; confirm it does not depend on tenure.
• ▸Multiplier (1+i)/(1+i−r): compute it, and record r and i for the grid.
• ▸Baseline customer value: profit per period × multiplier.
• ▸Lever cost: fixed spend, per-unit margin loss, or incentive per customer.
• ▸Retention uplift range (min–max): the low and high retention gain you believe, such as 0–10%.
• ▸Value with the lever (at low, mid and high uplift): recompute the multiplier at each new r and net out the lever cost.
• ▸Decision: simulated probability that the lever beats baseline (about 10,000 draws); approve only above your threshold.

1. 1Define the period and the revenue unit before you touch the data. Revenue is money from sales of goods or services over a specific period, so fix the date window and decide whether each row is one transaction line, one day, or one store.
2. 2Confirm the data has one row per sale with a Price field (the amount paid), plus the descriptors you want to slice by: Store, product, date, and customer demographics. Revenue can only be sliced by fields that exist on the row.
3. 3Build a PivotTable on that data. Drag the slicing field (for example Store) to the Row Labels box so revenue is listed by store. Leave Column Labels empty unless you want a second breakdown across the top row.
4. 4Drag Price to the Values box. Excel guesses the calculation, and for Price it guesses Sum. For total revenue, Sum is the correct choice. Check the header says 'Sum of Price' and not 'Count of Price'.
5. 5Use Value Field Settings (or double-click the data field) only when you want a different question answered: Average for typical sale size, Count for number of sales. Keep a separate table for each so a count is never read as dollars.
6. 6Add a second dimension to explain the number. Use the sales-by-store layout, then a time view such as the bakery sales analysis, then demographics as in the chapter on how demographics affect sales. Each cut shows where revenue comes from.
7. 7For conditional totals outside a PivotTable, use SUMIF or SUMIFS with the criteria that define the segment (store, product, period). Use COUNTIF/COUNTIFS and AVERAGEIF/AVERAGEIFS to get units and average sale for the same segment.
8. 8For a recurring report, pull figures from the PivotTable with GETPIVOTDATA so the end-of-week sales report refreshes from the pivot and not from hand-typed cell links. Use a chart that updates automatically when new data is added.

The source's own example is True Colors Hardware. The goal is total revenue by store. You create a PivotTable from the sales data and drag the Store field to Row Labels, so each store gets its own row. You drag Price to Values. Excel guesses that Prices should be summed, and because you want total revenue, the guess is right. The result is one revenue total per store. To check it, double-click the data field, switch to Average or Count, and read the same table as average sale size or number of transactions. The passages give no figures for this example, so none are shown here. The method then extends to a time view (the bakery example) and to demographic cuts, which show which customer groups drive each store's total. The passages say sales forecasts drive production schedules, inventory management, and manpower planning, so this revenue-by-segment baseline is the input those decisions start from.

• If the Values box says 'Count of Price', then change it to Sum before reading anything as revenue.
• If you need revenue for one segment and period without rebuilding a pivot, then use SUMIFS with the segment and date criteria.
• If you want to know why revenue differs between stores or periods, then split it into count (COUNTIFS) and average sale (AVERAGEIFS) before adding more explanations.
• If the report is repeated weekly, then drive it with GETPIVOTDATA and auto-updating charts so new data flows in without manual edits.
• If revenue is being used to plan production, inventory, or staffing, then treat the historical segmented totals as the forecast input and check their period definitions first.

• Counting instead of summing: the pivot shows transaction counts that get quoted as dollars. The tell is totals that are small whole numbers.
• Mixed or undefined periods: totals compare a full period to a partial one. The tell is a sudden drop or jump in the latest period only.
• Reading total revenue as performance without its parts: a change in revenue is explained by 'sales are up' when only the number of sales or the average price moved. The tell is no count or average beside the total.
• Hand-copied report numbers: weekly figures are typed or linked to cells that shift when the pivot changes. The tell is a report that no longer matches the pivot after a refresh (GETPIVOTDATA avoids this).
• Charts or ranges that do not extend to new data: new rows are left out of the totals. The tell is revenue that stops growing at the old last row.

• ▸Period covered: start and end dates of the revenue window.
• ▸Revenue field and unit: the column summed (e.g., Price) and the currency.
• ▸Slice dimension: the field in Row Labels (Store, product, month, demographic).
• ▸Calculation check: confirm the pivot shows Sum, not Count or Average.
• ▸Total revenue by slice: the summed value for each segment.
• ▸Count of sales by slice: from COUNTIFS or a Count pivot.
• ▸Average sale by slice: from AVERAGEIFS or an Average pivot.
• ▸Report link method: GETPIVOTDATA cell or SUMIFS formula used for the recurring report.
• ▸Downstream decision: the production, inventory, or staffing choice this revenue informs.

1. 1Fix the unit of analysis first. Define one customer (or one customer segment) and one period (month or year), and keep them identical across margin, retention, and cost inputs. Mixing monthly retention with annual margin is the most common source of a wrong CLV.
2. 2Set the per-period margin as profit, not revenue. Subtract the cost to serve and to retain from revenue, because the definition is the net present value of profit. If margins change with tenure, enter them by period instead of using one flat number (the source's 'Varying Margins' step).
3. 3Set the retention rate for each period, meaning the chance a customer is still active next period. Estimate it from your own cohort data, not from industry averages, and keep a separate figure for each segment you plan to act on.
4. 4Choose a discount rate and discount each future period's expected profit back to today. Expected profit in a period is margin times the probability the customer is still active. Sum the periods to get lifetime value. A constant margin and retention rate collapse this sum into a single multiplier on first-period margin (the source's 'explicit formula for the multiplier').
5. 5Subtract acquisition cost to get net customer value, then compare CLV to acquisition cost for each channel. The source's test is blunt: if you pay more to acquire a customer than that customer's long-term profit, you are on a path to going out of business.
6. 6Run sensitivity analysis with two-way tables. Vary retention against margin, or retention against discount rate, and look at which input moves CLV most. Spend measurement effort on that input first.
7. 7Check the 'is this customer still active?' question for non-contractual customers. Where cancellation is not observed, estimate the probability a customer is still active from their purchase history instead of assuming they remain.
8. 8Move beyond the basic model only when the basic one is decision-limiting. Use a Markov chain when customers move among states (for example active, lapsed, reactivated), and Monte Carlo simulation when you need a range of CLV outcomes rather than a single number. Then use the CLV to split budget between acquisition and retention.

The source's own example: spending $400 to acquire a customer who will generate $300 in long-term profits is 'a sure recipe for going out of business.' Run that through the CLV test. Net customer value is $300 minus $400, which is -$100 per customer, and the CLV-to-acquisition-cost ratio is 0.75. Every customer acquired destroys $100, so scaling the campaign scales the loss. Your levers are the three drivers in the definition. You can cut acquisition cost by more than $100, raise margin, or raise retention so that lifetime profit exceeds $400. Use a two-way table (retention against margin) to see which combination clears $400 with the smallest change. The passages give no DIRECTV figures or multiplier formula, so this example uses only the $400 and $300 stated there.

• If CLV is below acquisition cost for a channel, then stop or reprice that acquisition spend before optimizing anything else.
• If the sensitivity table shows CLV moves most with retention, then shift budget toward retention before buying more customers.
• If margins differ by period or segment, then model them by period or segment instead of using the flat-margin multiplier.
• If customer churn is not directly observed, then estimate the probability a customer is still active rather than treating every past buyer as a current one.
• If one point estimate will drive a large spend decision, then simulate the uncertainty (Monte Carlo) and decide on the range, not the mean.

• Using revenue instead of profit margin. The tell: CLV comfortably exceeds acquisition cost for every channel, including ones finance says lose money.
• Mismatched time units between retention, margin, and discount rate. The tell: CLV changes severalfold when you restate the same data monthly versus annually.
• Applying one average retention rate to all customers. The tell: high-value segments churn differently from the average, and the CLV-based budget misallocates spend.
• Counting lapsed non-contractual customers as active. The tell: projected lifetime profit never materializes in later cohort results.
• Presenting a single CLV number as certain. The tell: the acquisition decision flips under a modest change in retention, which a sensitivity table would have exposed.

• ▸Segment / channel: name the one customer group this sheet covers.
• ▸Period length: month or year; use this unit for every field below.
• ▸Margin per period (profit, not revenue): note any change by tenure.
• ▸Retention rate per period: from cohort data; probability the customer is still active next period.
• ▸Discount rate per period: the rate used to bring future profit to present value.
• ▸Time horizon (periods): how long you project before cutting off.
• ▸Computed CLV: sum of discounted, retention-weighted margins over the horizon.
• ▸Acquisition cost per customer: all-in cost for this channel.
• ▸Net value and ratio: CLV minus acquisition cost, and CLV divided by acquisition cost.
• ▸Sensitivity note: which input (retention, margin, discount rate) moves CLV most, and the break-even value that makes net value zero.

1. 1Fix the definition before you model: market share = your sales divided by total market sales, for one named period. Write down the market boundary (for example, all luxury sedans, or all catsup) and the time window, because both change the number.
2. 2Treat share as an outcome, not an input you can set. Ask what drives it. The passages point to price, display or promotion status, product attributes, and recent share (the catsup neural-network study used price, displays, and prior share as predictors).
3. 3To predict share under changed conditions, build a conjoint market simulator. Collect consumer rankings of product profiles, estimate attribute importance with multiple regression, then simulate how share shifts if an attribute changes or a new product enters.
4. 4Run the simulator for each scenario you care about: a price change, an attribute change, a new entrant. Record the predicted share for each against the baseline so the shifts are comparable.
5. 5When you use share as an input, as in sales quotas, plug in the current share and pair it with a regression forecast of per capita spending. Quota per capita = share × predicted per capita spending. Population then scales the total.
6. 6Check the prediction range before trusting any regression behind the share-based calculation. Do not predict for independent-variable values far outside those used to fit the equation (the Ivory Coast GNP warning).
7. 7Compare competing predictors of share on a holdout basis, using R² and MAPE. The catsup neural network beat other statistical techniques on both, so test alternatives rather than assume one method.

The source passage sets a fair sales quota for a French province. Per capita GNP is $50,000, 10 percent of GNP goes to education, and your company holds a 30 percent market share. The regression predicts per capita computer spending as -38.48 + 0.001723 × 50,000 + 15.30974 × 10. You multiply that prediction by 0.30. The result is a quota of $60.23 per capita. The share here works as a scaling factor: a territory where you hold a larger share gets a higher quota, and a richer territory gets a higher quota through the regression term. The quota formula is population × company market share × predicted per capita spending. The book also warns that a country like the Ivory Coast, with per capita GNP of $1,140, sits far outside the fitted data. Its prediction, and any quota built on it, is unreliable.

• If you need to know how share responds to a product change, use a conjoint market simulator rather than extrapolating from past share.
• If the territory's drivers (such as per capita GNP) fall outside the range of your fitted data, then do not apply the regression-based quota; find a better-matched model.
• If two territories differ in your market share, then set different quotas, since quota = population × share × predicted per capita spending.
• If you compare share-forecasting methods, then pick the one with the higher R² and lower MAPE on data held out from fitting.
• If share is reported without a market definition and period, then treat it as incomplete and do not compare it across sources.

• Vague market boundary: share swings depending on what counts as the total market. The tell is two reports of the same company with different shares and no stated denominator.
• Blind extrapolation: applying a regression-based share or quota calculation to a territory far outside the fitted data. The tell is an input value (like $1,140 GNP) far below your data set's minimum.
• Treating share as a lever: setting a share target without modeling the drivers (price, display, attributes). The tell is a target with no scenario tied to a driver change.
• Ignoring attribute trade-offs: changing price or an attribute without simulating the share effect. The tell is a change made with no simulated before/after share.
• Using stale share in quotas: the quota formula uses current share, so an outdated figure misstates fairness. The tell is a quota gap between territories that does not match recent performance.

• ▸Market definition: state exactly which products or competitors form the total market.
• ▸Time period: state the start and end dates for the sales figures.
• ▸Company sales: enter your sales in the market for the period.
• ▸Total market sales: enter all sales in the defined market for the same period.
• ▸Market share %: company sales divided by total market sales.
• ▸Scenario change: describe the price, attribute, or new-product change to simulate.
• ▸Simulated share: enter the share the conjoint simulator predicts under the scenario.
• ▸Territory population and predicted per capita spending: enter the population and the regression prediction, and confirm the inputs lie within the fitted data range.
• ▸Quota: population × market share × predicted per capita spending.

1. 1Split firm profit into two layers: per-period profit (revenue minus costs) from the pricing and marketing levers, and enterprise value from the customer base. Chapters 4–8 cover the first layer, and Chapters 19–20 cover the second. Name which layer each decision touches before you model it.
2. 2Set price from profit, not from volume. Gather the variable cost per unit (UC) and fit a demand curve D(p), linear or power, using the Excel Trendline or Goal Seek for a rough fit. Then set up profit = (p − UC) × D(p) and let Solver find the maximizing price.
3. 3Check the demand fit before you trust the price. Compute the standard error of the regression (SER) and count how many points fall within one SER (about 68% expected) and within two SER (about 95% expected). Treat any point beyond two SER as an outlier and look for its cause. If you can repeat a high-demand outlier or prevent a low one, profit rises.
4. 4Guard against overfitting. Hold back about 20% of the data as a validation set and fit the model on the other 80%. Compare the standard deviation of errors on both sets. If they are close, you can trust the forecast accuracy that feeds your profit model.
5. 5Build a customer lifetime value template: per-period margin per customer, the chance the customer is still active, and the resulting stream of profit. Run two-way sensitivity tables on the key drivers, and test varying margins instead of one flat margin. Where the data allow, estimate the chance that a customer is still active rather than assuming it.
6. 6Run an acquisition test before spending. Compare the cost to acquire a customer with that customer's lifetime profit. Do not acquire customers whose long-term profit falls below the acquisition cost.
7. 7Value the firm from its customers. Estimate the value of the current customer base, add the value of customers you expect to acquire, and use that total as an estimate of the firm's market value. Run a one-way sensitivity table on the most uncertain input, such as retention or margin.
8. 8Where outcomes are uncertain, move from point estimates to a distribution. Chapter 21 uses a Markov chain model of customer value and Monte Carlo simulation. Chapter 22 then splits the budget between acquisition and retention.

The source gives two small cases. (1) Acquisition economics: spending $400 to acquire a customer who will generate $300 in long-term profit is 'a sure recipe for going out of business.' The net value of that customer is $300 − $400 = −$100, so the acquisition fails the test. It passes only if lifetime profit exceeds $400, through higher margin, longer retention or a lower acquisition cost. That is why customer value (Chapter 19) comes before acquisition spending. (2) Pricing a software program for a Fortune 500 customer with demand q = 400 − p. Rewrite it as p = 400 − q. The value of the i-th unit is the price at which demand equals i − 0.5, so the first unit is worth $399.50. Profit is (p − UC)(400 − p). Maximizing it algebraically gives p = (400 + UC)/2, which is my derivation, not a figure from the source. Solver finds the same answer without algebra and works for power curves, where algebra is harder. The outcome is that one price is set from cost and demand, and the acquisition test then decides whether the customer is worth winning.

• If a customer's lifetime profit is less than the cost to acquire them, then do not acquire; fix margin, retention or acquisition cost first.
• If you need a profit-maximizing price, then you need both UC and a demand curve; without both, do not claim an optimal price.
• If an observation sits more than two SER from the fitted line, then investigate its cause. If the cause is repeatable and good, replicate it; if bad, prevent it.
• If the validation-set error is much larger than the test-set error, then the model is overfit. Do not use it to drive profit or valuation forecasts.
• If a firm valuation depends on one uncertain input such as retention or margin, then show a one-way sensitivity table (two-way for two inputs) rather than one number.

• Maximizing revenue instead of profit. The tell: the chosen price ignores UC.
• Treating a fitted demand curve as truth. The tell: no SER, no outlier check and no validation set, and R² is quoted alone.
• Overfitting with a high-degree polynomial. The tell: R² near 1 on a handful of points but poor forecasts on new data.
• Using a flat margin and assumed retention in lifetime value. The tell: the value changes sharply when you vary margins or the chance a customer is still active, yet no sensitivity table was run.
• Spending on acquisition without comparing it to customer value. The tell: acquisition cost is tracked but lifetime profit per customer is never computed.
• Reading the valuation as a single number. The tell: a firm value is reported with no range and no sensitivity analysis.

• ▸Variable cost per unit (UC): enter the cost to produce one unit.
• ▸Demand curve form and fit: linear or power, with its parameters and R².
• ▸SER and outlier count: standard error, share of points within 1 and 2 SER, and the outliers to investigate.
• ▸Validation error comparison: error standard deviation on the 80% fit set versus the 20% held-out set.
• ▸Profit-maximizing price (from Solver): the price that maximizes (p − UC) × D(p), and the resulting profit.
• ▸Acquisition cost per customer: the total cost to win one customer.
• ▸Lifetime profit per customer: margin stream times the chance the customer is still active, with the margin assumption stated.
• ▸Acquire or not: lifetime profit minus acquisition cost, and the decision.
• ▸Firm value estimate and sensitivity: customer-base value, plus the one-way or two-way table range on the riskiest input.

1. 1Write down the unit cost of each product first. In the source's bundling examples cost is set to $0 so revenue equals profit. In real cases, such as the exercise with a $4 hardcover and a $2 paperback, profit is price minus unit cost, and you maximize profit, not revenue.
2. 2Estimate demand before choosing a price. Fit a linear or power demand curve to observed price and quantity data. If you have no data, build a subjectively estimated demand curve from managers' judgments at several price points.
3. 3Set up the optimization in Excel. The price is the changing cell, profit = (price − unit cost) × demand at that price is the target cell, and Solver maximizes it. For several products, run SolverTable to see how the optimal prices move as an input changes.
4. 4Test the single-price answer against a table of what each customer or segment would pay. List each customer's valuation for each product, then compute revenue at every candidate single price. The Disney example shows the method: revenue at $10, $8, $6, $4 and $2 per ride.
5. 5Check whether bundling beats separate pricing. Compare each customer's valuations across products. If valuations are negatively correlated (one customer values product A highly and B low, another the reverse), pure bundling extracts more. Price the bundle at the sum a customer will pay, not at the sum of the separate prices.
6. 6For mixed or multi-bundle menus, use Evolutionary Solver to find bundle prices. Add segment sizes and costs, as in the home subscription and web access exercise, where costs are $15 and $1 and segments are 25%, 40% and 35%.
7. 7Test nonlinear schedules where cost per unit does not scale linearly. For a quantity discount, define a cutoff (CUT), a high price (HP) and a low price (LP). Example: $10 per unit below 1,000 units and $8 per unit on all units above it. Let Solver choose CUT, HP and LP.
8. 8Choose a time pattern last. Decide whether to skim, dropping prices over time, or to run sales. For perishable capacity, use revenue management (segment demand, handle uncertainty) and markdown pricing.

The source's Disney World case: one customer values Space Mountain-style rides so that at $10 per ride they take 1 ride, at $8 they take 2, at $6 they take 3, at $4 they take 4, and at $2 they take 5. Revenue per customer is $10, $16, $18, $16 and $10, so the best single per-ride price is $6, which earns $18. Selling a five-ride ticket for $30 earns $30, a 67 percent increase, because the customer values all five rides at $30. The bundle extracts consumer surplus that per-ride pricing leaves behind. A second case shows why correlation matters. Customer 1 values a computer at $1,000 and a monitor at $500. Customer 2 values them at $500 and $1,000. With cost at $0, pricing separately at $1,000 each earns $2,000 (each customer buys only the product they value at $1,000). A $1,500 bundle sells to both and earns $3,000. Negatively correlated valuations drive the gain.

• If customers value products in opposite directions (negative correlation), then test a bundle priced near the sum of what each customer will pay; the computer/monitor bundle earned $3,000 against $2,000 separately.
• If you have demand data on a single product, then fit a demand curve and let Solver maximize (price − cost) × demand; don't set price by margin rules.
• If valuations are heterogeneous across several products or segments, then use Evolutionary Solver to search bundle prices rather than solving by hand.
• If cost per unit is not proportional to quantity, or buyers differ in volume, then test a nonlinear schedule (cutoff, high price, low price) against a flat unit price.
• If you have little or no demand data, then use subjectively estimated demand curves and re-run Solver as estimates change.

• Maximizing revenue when unit costs are positive. Tell: the chosen price ignores cost, such as the $4 hardcover and $2 paperback costs.
• Pricing each product separately when valuations are negatively correlated. Tell: separate pricing yields $2,000 where a bundle yields $3,000.
• Assuming one customer represents the market. Tell: results hinge on a single valuation row; the source notes that identical customers are not required, so build segments with sizes (such as 25%, 40%, 35%).
• Taking a Solver result from a poorly fitted demand curve as truth. Tell: the 'optimal' price sits outside the range of prices you observed.
• Treating a price schedule as a single number. Tell: you quote a per-unit price without a cutoff, even though the discount applies above 1,000 units to all units.

• ▸Product(s) and unit cost: list each product with its cost per unit (use $0 only if truly negligible).
• ▸Customer/segment valuations: table of each customer or segment's willingness to pay for each product and for combinations.
• ▸Segment sizes: percentage or count of each segment.
• ▸Demand curve type and source: linear, power, or subjective; record the data or judgments behind it.
• ▸Best single price per product: Solver result and resulting profit.
• ▸Bundle option(s): bundle composition, price, and profit versus separate pricing.
• ▸Nonlinear option: cutoff (CUT), high price (HP), low price (LP), and resulting profit.
• ▸Time pattern: skimming, sale, or markdown plan, with timing.
• ▸Chosen strategy and profit uplift: which structure won and its profit gain over best single price.

1. 1Start with who already buys. Build a crosstab of customers by age and income in an Excel PivotTable: Age in Column Labels, Income in Row Labels and Value Labels. Group age in 10-year bands and income in $25,000 bands. Set Value Field Settings to Show Value As > % of Row Total so each income row sums to 100%.
2. 2Read the table twice. Find the cells where customers concentrate; these are the places to buy media. Find the cells that are empty or near zero; these are the places not to spend. The book's rule is that crosstabs show where to invest and where firms should not.
3. 3Match media to the concentrated cells. Before booking any channel, ask which cell of your crosstab its audience falls into. Cut any placement whose audience sits in a near-empty cell.
4. 4For mailings, score every customer on Recency, Frequency and Monetary value, using terciles (three categories each). Terciles keep enough observations in every R-F-M cell to estimate an average response rate reliably. Finer splits leave cells too thin to trust.
5. 5Compute the response rate for each R-F-M cell. Apply conditional formatting with a formula (the book uses =$AF14>=0.05, a 0.05 response-rate cutoff) to highlight the cells that clear your profitability threshold. Mail only the highlighted cells.
6. 6Measure promotions against a baseline. Add a promotion flag column to daily sales data (the bakery file uses 'none' versus 'promotion'). Account for weekday, seasonality and trend first, then judge the promotion's effect. Otherwise a promotion that happens to fall on a strong weekday gets credit it did not earn.
7. 7Measure advertising with a model, not intuition. Chapter 34 uses the ADSTOCK model to estimate ad effectiveness, and also covers pulsing versus continuous spending. Chapter 34 requires Chapter 31 (SCAN*PRO) first, so promotion and price effects are handled before ad effects.
8. 8Allocate the budget across outlets with Chapter 35's media models: a linear media allocation model, one that handles quantity discounts, and a Monte Carlo simulation (this one needs the Palisade Decision Tools Suite). For online ads, use Chapter 36's PPC profitability model and Google's Bid Simulator to set the bid.

The book's ESPN: The Magazine example shows how a crosstab steers ad placement. Subscriber data is cross-tabbed by age (10-year bands) and income ($25,000 bands), with values shown as % of row total. The table shows 28.13% of subscribers in the $54,000-$78,000 income bracket are aged 28-37. Across all subscribers, the 28-37 and 38-47 age bands each hold 28.81%, and the 12-27 band holds 8.20%. At the other end, hardly any subscribers are 78 or older or have household income above $229,000. The conclusion is concrete: do not place ads on TV shows heavily watched by wealthy retirees, and weight spend toward the 28-47 core. Two cautions apply. Some high-income rows are built on very few subscribers, so their percentages can swing to 0% or 100% on a handful of people. The crosstab also shows who subscribes, not what an ad caused; Chapter 34's ADSTOCK model is the tool for that question.

• If a demographic cell has near-zero customers (for example age 78+ or income above $229,000 in the ESPN data), then do not buy media aimed at it.
• If an R-F-M cell's response rate is at or above your profitability threshold (0.05 in the book's formatting example), then mail it; otherwise suppress it.
• If R-F-M cells hold too few customers to estimate a stable response rate, then collapse to terciles for R, F and M.
• If you are judging a promotion, then control for weekday, seasonality and trend before attributing the sales change to the promotion.
• If a media vendor offers quantity discounts or your inputs are uncertain, then use the quantity-discount allocation model or the Monte Carlo media simulation instead of a flat per-unit comparison.

• Judging a promotion by raw before/after sales without controlling for weekday or season. The tell: promotion days always look strong because they fall on weekends or peak months.
• Using many fine R-F-M buckets. The tell: response rates swing wildly between adjacent cells and highlighted cells fail to repeat next campaign.
• Reading a thin crosstab row as a real pattern. The tell: a row showing 100% in one age band when it holds only a few customers.
• Spending on a medium because its audience is large rather than because it sits in a concentrated cell. The tell: reach is high but few subscribers or buyers come from that audience.
• Repeating Wanamaker's problem by never measuring ad effect. The tell: no model such as ADSTOCK is applied, and you cannot say which half of the spend is wasted.

• ▸Target segment (age band x income band): the crosstab cell you are buying.
• ▸Segment share, % of row total: from the PivotTable, Show Value As % of Row Total.
• ▸Customer count in cell: a low count means the percentage is unreliable.
• ▸Channel or media outlet: the placement whose audience matches this cell.
• ▸Planned spend and any quantity-discount breakpoint: the inputs to the media allocation model.
• ▸Response-rate threshold: the minimum response that makes a mailing or promotion profitable (for example 0.05).
• ▸Observed response rate (R-F-M tercile cell): the measured rate for the group you plan to contact.
• ▸Baseline controls applied (weekday, season, trend): confirm you adjusted before crediting a promotion.
• ▸Decision (fund / suppress / test) and measurement plan: state the action and how you will measure effect afterwards, for example ADSTOCK.

1. 1Write the product as a list of attributes, not a feature pile. The source's car example names five: price, brand, engine horsepower, styling, fuel economy. Tangible attributes (horsepower, styling) and intangible ones (brand) go on the same list, because the customer weighs them together.
2. 2Give every attribute 2-4 discrete levels, following the 'Products, Attributes, and Levels' structure of conjoint analysis. Styling on an SUV, for example, needs named styling options so you can ask which one is most preferred. Price is an attribute with levels too.
3. 3Pick the question each attribute must answer. The source poses two: how important is the attribute overall (attribute ranking), and which level within it is preferred (level ranking). Record both questions per attribute before collecting any data.
4. 4Choose the method to match the decision. Conjoint analysis (full profile, with Evolutionary Solver used to generate product profiles) ranks attribute importance and level preference from rated profiles. Discrete choice analysis, built on random utility theory, models what customers pick, and can incorporate price and brand equity. Use conjoint for design trade-offs and discrete choice when price and brand must sit inside the choice model.
5. 5Collect preference data on profiles, meaning combinations of levels across attributes. Do not ask customers to rate attributes one at a time, since that hides the trade-offs the model is meant to estimate.
6. 6Estimate the model and read two outputs: the relative importance of each attribute, and the ranking of levels inside each attribute. Rank the attributes first, then the levels, because levels only matter in attributes that move choice.
7. 7Build a conjoint simulator, as the source's chapter outline does, to test candidate offerings. Compare designs by predicted preference rather than by internal opinion.
8. 8Check every winning design against customer economics. The source's rule: spending $400 to acquire a customer who generates $300 in long-term profit is a recipe for going out of business. A preferred design that costs more than the value it creates still fails.

The source frames a brand manager's question for a car: what matters most in a consumer's choice among price, brand, engine horsepower, styling, and fuel economy? You set up five attributes. For SUV styling you list the specific styling options under test as levels, and you do the same for the other four attributes. Respondents rate or choose among profiles that combine one level from each attribute. The analysis then returns two things: which attribute carries the most weight, and which styling level is most preferred. The passages do not give numerical results, so what follows is the decision logic. If brand outranks horsepower, spending on engine upgrades is the wrong lever. If one styling level clearly wins, you design to that level. You then run candidate designs through the simulator and pass only those that clear the customer-value test ($400 to acquire against $300 of lifetime profit fails).

• If you need to know which attributes drive sales and which levels win, then run conjoint analysis; if you need to model actual choices with price and brand equity included, then use discrete choice analysis.
• If an attribute has low importance in the results, then stop investing design effort there, however much the engineering team likes it.
• If a styling or feature level wins preference but its added cost pushes unit economics below customer value, then reject it; preference without profit does not justify the design.
• If a candidate design cannot be described as a set of attribute levels, then redefine it until it can, because the models only score defined levels.
• If the outcome is binary, such as whether a person subscribes, then use logistic regression on customer characteristics rather than conjoint.

• Listing only tangible specs (horsepower, styling) and omitting brand. The tell: the model explains little of the observed choice, and brand preference shows up later as 'unexplained' loyalty.
• Defining levels vaguely, such as 'better styling'. The tell: respondents cannot distinguish profiles and the level rankings are unusable.
• Treating price as an afterthought. The tell: the preferred design is one nobody can price profitably; the source's discrete choice material incorporates price and brand equity for this reason.
• Ranking attribute importance without checking customer economics. The tell: the 'best' design wins preference tests but acquisition cost exceeds long-term profit per customer.
• Asking about attributes in isolation instead of in combined profiles. The tell: every attribute is rated 'important' and nothing separates the trade-offs.

• ▸Product or offering: name the product and the decision the design must inform.
• ▸Attributes (5-7): list tangible and intangible attributes, including price and brand.
• ▸Levels per attribute: write 2-4 concrete, distinguishable levels for each attribute.
• ▸Method chosen: conjoint or discrete choice, with a one-line reason.
• ▸Profiles to test: list the level combinations respondents will rate or choose between.
• ▸Attribute importance ranking: after analysis, rank attributes from most to least influential.
• ▸Preferred level per attribute: record the top-ranked level for each attribute.
• ▸Candidate designs for the simulator: list 2-3 level combinations to compare on predicted preference.
• ▸Customer-value check: record acquisition cost and long-term profit per customer for each design; reject any where cost exceeds profit.

1. 1Name the finite resource and the competing uses before modeling anything. The source frames three cases: shelf space split between products, sales force effort split across accounts or territories, and budget split between acquisition and retention. Write down the total available and each use's lower and upper bound.
2. 2Price the customer first. The source says companies cannot spend sensibly on acquisition until they know customer value (Chapters 19-21). Compute lifetime customer value, and use the Monte Carlo approach (Chapter 21) if value is uncertain. This sets the most you can afford to spend per customer.
3. 3Estimate the response relationship for each use. Chapter 32 starts by 'Identifying the Sales to Marketing Effort Relationship' and then 'Modeling the Marketing Response to Sales Force Effort'. Chapter 22 starts by 'Modeling the Relationship between Spending and Customer Acquisition and Retention'. Fit one curve per use that links input (dollars, hours, shelf units) to output (sales, customers acquired, customers retained).
4. 4Choose a curve shape that shows diminishing or S-shaped returns. For shelf space, the source uses the Gompertz curve. Do not assume linear returns, because a linear model pushes everything into one use.
5. 5Set up the optimization on the fitted curves. Maximize total profit (or sales) across uses, subject to the total-resource constraint and the bounds from step 1. For acquisition versus retention, follow the 'Basic Model for Optimizing Retention and Acquisition Spending', then the 'Improvement to the Basic Model'. For sales effort, follow 'Optimizing Allocation of Sales Effort'. For awkward nonlinear problems, the book uses Excel's Evolutionary Solver (Chapter 30).
6. 6Read the optimum as marginal logic. At the best split, one more dollar, hour, or shelf unit yields about the same gain in every use. If one use still returns clearly more, move resource toward it and re-solve.
7. 7Test sensitivity before committing. Vary the fitted curve parameters and customer value to see whether the split holds. Where relevant, check that it fits store-layout logic, such as complementary products near each other (Chapter 29, lift).
8. 8Run the allocation, then re-estimate the curves with the new data. Keep the allocation as a hypothesis the next period's results can correct.

The source's own illustration is the acquisition limit: 'spending $400 to acquire a customer who will generate $300 in long-term profits is a sure recipe for going out of business.' Apply it as a filter before any optimization. Net value per acquired customer is $300 - $400 = -$100. Acquiring 1,000 customers at that cost would lose $100,000, so acquisition spending at that cost per customer should be cut or rebuilt, however good the acquisition response curve looks. Now suppose the cost per customer falls below lifetime value. The Chapter 22 approach then fits acquisition and retention response to spending and finds the split of the fixed budget that maximizes profit. The passages give the chapter structure but not the book's formulas or numbers, so you reproduce the fitting steps in Excel yourself rather than from this summary. The same sequence works for sales force effort and shelf space: customer or product value, fitted response curve, constrained optimization.

• If cost to acquire a customer exceeds lifetime customer value, then do not add acquisition spend, and fix cost or value first.
• If the response curve shows diminishing returns, then split resource across uses until marginal returns equalize rather than concentrating it in one.
• If the response curve is S-shaped (the Gompertz curve for shelf space), then check the threshold region: a use below its tipping point may deserve zero rather than a token share.
• If customer value is uncertain, then simulate it (Monte Carlo) and optimize against the range, not a single estimate.
• If the optimizer swings the whole budget to one use after a small input change, then treat the fitted curves as unreliable and re-estimate before acting.

• Optimizing acquisition without customer value. The tell: cost per acquired customer is never compared to lifetime profit per customer.
• Assuming linear response. The tell: the model recommends putting 100% of the budget or shelf in one product or channel.
• Treating acquisition and retention as separate budgets. The tell: each team plans its own spend with no shared constraint or shared customer value.
• Fitting response curves on thin or stale data and treating the optimum as exact. The tell: allocations change sharply with each refit and nobody ran a sensitivity check.
• Ignoring interactions between uses. The tell: shelf space is allocated product by product, though complementary products (lift) affect each other's sales.

• ▸Resource and total available: state what is being split (budget, sales hours, shelf units) and the fixed total.
• ▸Competing uses: list each product, territory, or activity (e.g., acquisition vs. retention).
• ▸Lower/upper bounds per use: record minimum and maximum allowed for each use.
• ▸Value per unit of outcome: enter lifetime customer value or profit per sale for each use.
• ▸Response curve type and fit: note the shape chosen (e.g., Gompertz) and the data it was fit on.
• ▸Current allocation and result: record today's split and the output it produces.
• ▸Optimized allocation and expected result: record the solver's split and projected profit.
• ▸Sensitivity range tested: list which parameters were varied and whether the split held.
• ▸Review date and re-estimate trigger: set when you refit the curves with new results.

1. 1Separate the four forces in a dated table before modeling anything: underlying trend, seasonality, competitor actions, and one-time events. Winston treats trend and seasonality (Ch. 12), special events (Ch. 11) and competitive dynamics (discrete choice) as separate problems, so keep them in separate columns.
2. 2Build the base series in a PivotTable. Make sure row 1 holds headings such as Product, Month, Store and Price. Insert > PivotTable, drag Month to Row Labels, the store or product field to Column Labels, and the sales measure to Values. Read the month-by-month shape before fitting anything.
3. 3Flag every one-time event (a viral video, a launch, a promotion) as its own marker in the data. Forecast with the event explicitly represented, as in 'Forecasting in the Presence of Special Events', so a spike is not read as trend or seasonality.
4. 4Estimate the trend and seasonal pattern on the cleaned series (Ch. 12-13: trend and seasonality, ratio-to-moving-average). Forecast from that, not from raw sales.
5. 5Before modeling competitor reactions, test whether price matters to choice. Run the full discrete-choice model and a no-price model, then compute Delta = 2 x (LL full − LL no-price) and CHIDIST(Delta, 1). Model price reactions only if price is clearly significant.
6. 6Set up each competitor's cost and current price in a competitive-dynamics sheet (the Xbox3.xls layout). Compute each firm's choice probability, unit sales, revenue, cost and profit (profit = revenue − cost).
7. 7Move one competitor's price at a time to its best response, then repeat. Two outcomes are possible: prices keep undercutting each other (a price war), or they settle where no one gains by changing price (a Nash equilibrium).
8. 8Plan around the equilibrium or war outcome, not around your first-move price. Treat the competitor's response as part of the forecast.

Winston's video-game-console example in Xbox3.xls asks what happens after Xbox sets the $207 price recommended earlier. PS and Wii would probably react, so the sheet carries costs of $180 (Xbox), $160 (PS) and $155 (Wii). In the displayed state, prices are Xbox $207.46, PS $215.00 and Wii $190.00. Unit sales are 17.86, 27.43 and 18.27. Revenue is $3,706.13, $5,896.97 and $3,471.96, and cost is $3,215.56, $4,388.44 and $2,832.39. Subtracting cost from revenue gives Xbox about $490.57, PS about $1,508.53 and Wii about $639.57. The profit row in the extracted figure does not match these, so recompute profit yourself and do not trust a pasted row. First confirm price matters: LL(Full) = −996.85 and LL(no price) = −1045.03 give Delta = 96.37, and the CHIDIST p-value is effectively zero. Then iterate each firm's price. Either prices keep undercutting (a price war) or they stop at a Nash equilibrium where nobody gains by moving. Your forecast should use that end state, not the $207 starting price.

• If a spike lines up with a one-time event (e.g., Old Spice's viral video: 6.7 million views in 24 hours, 23 million in 36), then tag it as an event and exclude or separately model it before estimating trend or seasonality.
• If the price-significance test (CHIDIST on Delta) is not clearly significant, then do not build a price-reaction model; look at other drivers such as brand.
• If competitors can see and match your price, then forecast with a dynamic reaction model, not a static one.
• If iterated best responses converge, then plan for that equilibrium price set; if they keep undercutting, then treat it as a price war and test whether the lower prices still earn a profit.
• If a new product's sales rise from imitation (adopters telling non-adopters) rather than outside influence, then fit a diffusion model such as Bass and do not read the rise as seasonality.

• Static pricing logic: you recommend a price as if rivals will not respond. The tell is a forecast that never mentions competitor prices or a reaction step.
• Event contamination: a one-time spike gets absorbed into trend or seasonal indices. The tell is seasonal factors that jump in the month of a launch or promotion.
• Skipping the significance test: you model price dynamics without checking price matters. The tell is no LL comparison or CHIDIST result anywhere in the workbook.
• Trusting pasted summary rows: profit or revenue figures that do not reconcile with price, cost and units. The tell is revenue − cost ≠ the profit row.
• Reading diffusion as seasonality: treating an imitation-driven S-curve as a calendar pattern. The tell is growth that follows cumulative adopters, not the month.

• ▸Period and metric: the date range and sales measure you are analyzing.
• ▸Trend read: direction of the underlying sales trend after removing events and seasonality.
• ▸Seasonal pattern: the months or periods that run high or low in the PivotTable view.
• ▸One-time events: each dated event (launch, viral spike, promotion) and whether it was excluded or modeled.
• ▸Competitor prices/promotions: each rival's current price and any recent promotion.
• ▸Costs by firm: each firm's unit cost, used to compute profit.
• ▸Price-significance test: LL(full), LL(no price), Delta and CHIDIST result.
• ▸Reaction outcome: price war or Nash equilibrium, with the end-state prices.
• ▸Forecast adjustment: how the forecast changes after applying trend, seasonality, events and competitor reaction.