Marketing mix modeling in Excel works by adding an adstock column for each channel (this week’s spend plus a share of last week’s adstock), a saturation column that bends it for diminishing returns, and then regressing sales on those columns plus your controls with LINEST. The coefficients give you each channel’s contribution, ROI and response curve, and Solver can both fit the decay rates and reallocate the budget.
This is how the original Vexpower MMM course started, in Excel and Google Sheets before any Python or R, and I still think it’s the best way to learn. Every number is in a cell you can click on, so nothing is hidden behind a library default, and the moment you change a decay rate you can watch the coefficients, the decomposition and the budget recommendation all move. The free template below is a complete working model on a 104-week synthetic dataset, built only with formulas, and the rest of this page explains each sheet so you can rebuild it on your own data. If you’re new to the method, what marketing mix modeling is covers the ideas first.
How to lay out data for marketing mix modeling in Excel
Put one row per week and one column per variable, with the outcome first: week, revenue, then each channel’s spend, then anything else that moves sales (promotions, price, holidays). The template’s Data sheet uses the practice dataset (week, revenue, search, social and video spend, and a promotion flag) and adds three helper columns: a trend counter t that runs 0, 1, 2 and so on, and a sine and cosine of t with a 52-week period to capture seasonality. The period lives in a yellow cell on the Inputs sheet, so you can change it to 26 if your business has two peaks a year.
The rule that saves the most pain is to keep raw data and calculations on separate sheets and never type over a formula. In my experience, most broken spreadsheet models are broken because someone pasted values into the middle of a formula column, not because the maths was wrong. The template has one Inputs sheet where every assumption lives, colored light yellow, and everything else is calculated from it. The data requirements page covers how much history you need and what to do about gaps.
Adstock in Excel: one decay cell and a formula you fill down
Adstock models carryover: some of this week’s advertising effect shows up next week and the week after. The geometric version is a single recursion, where each week’s adstock is that week’s spend plus the decay rate times the previous week’s adstock. In Excel that’s two formulas. The first data row just copies the spend, and every row after it adds the decayed value from the row above. In the template’s Transform sheet, search adstock is column B and the decay rate is in Inputs!C6:
B2: =Data!C2
B3: =Data!C3+Inputs!$C$6*B2 (fill down to B105)The dollar signs matter. They make the decay reference absolute, so when you fill the formula down to row 105 every row still points at the same decay cell while the spend and previous-row references move. Forget them and the second row will look right while the third quietly references an empty cell. If you have Excel 365, Excel 2024 or Google Sheets, you can instead write the whole column in one cell with SCAN, which carries an accumulator down a range:
=SCAN(0,Data!C2:C105,LAMBDA(prev,x,x+Inputs!$C$6*prev))Use the builder below to get the exact formulas for your own layout.
Some people prefer the normalized form, where spend is multiplied by one minus the decay before it’s added, so that the total adstock over time equals the total spend. That changes the size of the coefficient but not the fit or the ROI, and either is fine as long as you’re consistent. The builder has a toggle for it.
Saturation: a Hill column with a half-saturation cell
The next transform captures diminishing returns: the tenth thousand dollars in a week does less than the first. The template uses the Hill curve with its shape fixed at 1, which is simply adstock divided by adstock plus a half-saturation level, the spend at which the channel reaches half its maximum effect. Each channel’s half-saturation is entered on Inputs as a multiple of its average weekly spend (so 10 means “average spend is a long way below saturation”) and converted to spend units in column F.
I2: =B2/(B2+Inputs!$F$6) (hill_search, fill down)The Transform sheet ends up with a contiguous block of seven regressor columns, E to K: trend, sine, cosine, promotion, and the three Hill columns. Keeping them side by side matters, because LINEST needs all its X variables in one rectangular range. If your saturation needs a steeper S-shape, you can raise both terms to a power held in another input cell, but on 104 weeks of data I’d keep the shape fixed and spend the data on the parameters you care about.
Running the regression with LINEST
LINEST fits an ordinary least squares regression of one column on several and returns the coefficients plus a block of statistics. On the Model sheet the formula is:
=LINEST(Data!$B$2:$B$105,Transform!$E$2:$K$105,TRUE,TRUE)The two TRUE arguments ask for an intercept and for the full statistics. LINEST returns a block five rows tall and one column wider than the number of X variables, and the template enters it as an array in Model!B5:I9. In Excel 365 a LINEST formula spills into the cells it needs by itself; in older Excel you select the whole block first and press Ctrl+Shift+Enter. The first row holds the coefficients, the second their standard errors, and the third R-squared and the standard error of the estimate.
The thing that catches everyone is that LINEST returns the coefficients in reverse order, with the last X column first and the intercept at the far right. To avoid reading them off by position, the template pulls each one into a labeled row with INDEX, which also works without array entry in every version of Excel and in Google Sheets. For example, the trend coefficient (the first X column, so the seventh LINEST column) is:
=INDEX(LINEST(Data!$B$2:$B$105,Transform!$E$2:$K$105,TRUE,TRUE),1,7)R-squared is the same formula with 3,1 at the end. Fitted revenue for each week is then the intercept plus a SUMPRODUCT of the coefficient row with that week’s regressor row, and the residual is actual minus fitted.
With the template’s defaults (decay 0 and half-saturation 10 times average spend for every channel), LINEST gives an intercept of 19,478.4, a promotion coefficient of 1,802.2 and Hill coefficients of 41,134.5 for search, 16,575.2 for social and 8,804.8 for video, with an R-squared of 0.99996 and a MAPE of 0.04 percent. The fit is almost perfect because the practice data is synthetic: it was generated from a straight linear equation (1.4, 0.8 and 0.6 of revenue per dollar of search, social and video, plus a baseline, a seasonal wave and a promotion lift) with no noise beyond rounding, no carryover and no saturation; on real weekly revenue, an R-squared above 0.9 is normal, and anything this close to 1 should make you suspect overfitting. I recalculated the template in LibreOffice and fitted the same model in numpy, and the coefficients agree to every decimal place shown.
What about the Analysis ToolPak?
The Analysis ToolPak (enable it under File, Options, Add-ins) has a Regression tool under Data, Data Analysis that takes a Y range and a contiguous X range and writes a nicely labeled table of coefficients, standard errors, t-statistics and p-values. Microsoft’s documentation says the tool uses LINEST under the hood, so the coefficients are identical, and it’s easier to read the first time. However, its output is static numbers, not formulas, so it doesn’t update when you change a decay rate, and that makes it useless for the next step, where Solver needs the error to recalculate on every guess. I’d use the ToolPak once to check you understand the LINEST output, then delete it.
Letting Solver find the decay rates
You could try decay rates by hand, but Solver will search for you. The Model sheet has a sum of squared errors cell (Model!C22, =SUMSQ of the residuals), and fitting the model means finding the decay rates that make it smallest. In Solver (Data, Solver; enable it under Add-ins if you don’t see it), set the objective to Model!$C$22, choose Min, change Inputs!$C$6:$C$8, add constraints that each is at least 0 and at most 0.9, and use the GRG Nonlinear method. You can add the half-saturation cells (Inputs!$D$6:$D$8) to the changing cells too, with bounds of, say, 0.2 and 10.
On the practice data Solver should leave the decays at or very near zero and push the half-saturation multiples to their upper bound of 10, which is the same answer the Python tutorial reaches with scipy. That’s not a bug. The practice data was generated with no carryover and no diminishing returns, so the best model is one with neither, and a parameter sitting on its bound is the spreadsheet’s way of telling you the data doesn’t support any more curvature. On your own data, run Solver from two or three different starting values, because GRG Nonlinear can stop at a local minimum.
Decomposition: splitting each week’s revenue
A decomposition multiplies every regressor by its coefficient so that each week’s predicted revenue splits into parts. On the Decomposition sheet, baseline is the intercept plus the trend and seasonality terms, promotion is its coefficient times the flag, and each channel is its coefficient times its Hill column. The stacked area chart on that sheet shows them week by week, and the totals row gives each component’s share of two years’ revenue.
With the default inputs, baseline accounts for 76.0 percent of modeled revenue, search 14.5 percent, social 5.8 percent, video 3.1 percent and promotions 0.5 percent. The baseline sales page explains why a big baseline share is normal. I’d show this chart to stakeholders before any ROI table, because it’s the easiest place for someone who knows the business to spot a problem, such as a promotion spike landing in the wrong week or a channel taking credit for a seasonal peak.
ROI and marginal ROI per channel
The ROI sheet divides each channel’s total modeled contribution by its total spend. Marginal ROI, the return on the next dollar at average spend, comes from the slope of the Hill curve: the coefficient times the half-saturation, divided by the square of adstocked spend plus half-saturation, divided again by one minus decay (because a dollar of steady weekly spend becomes more than a dollar of adstock). In the template that’s:
E4: =F4*H4/((Inputs!E6/(1-G4))+H4)^2/(1-G4)With the defaults, search returns 1.54 in revenue per dollar on average and 1.40 at the margin, social 0.87 and 0.80, and video 0.65 and 0.60. Because the practice data has a known answer, you can check these: the marginal ROIs match the true effects of 1.4, 0.8 and 0.6, while the average ROIs come out about 10 percent high. That gap isn’t real diminishing returns; it’s the half-saturation bound of 10 forcing the Hill curve to bend slightly when the true relationship is a straight line (the Python tutorial shows the average ROIs dropping to within a cent of the truth when that bound is widened). These are revenue numbers, so multiply by your gross margin before comparing them with 1; the breakeven ROAS page has a calculator. The gap between average and marginal ROI is the whole argument of marginal ROAS: budget decisions should be made on the second number, not the first.
Response curves and reallocating the budget with Solver
The Response curves sheet tabulates, for each channel, the weekly contribution you’d get at 31 steady weekly spend levels from zero to three times the average, and charts them. Each point is the coefficient times the Hill function of spend divided by one minus decay. Anything to the right of the highest spend you’ve actually observed is the curve’s opinion rather than the data’s, so treat the far end of each line with suspicion.
The reallocation block at the top of the same sheet holds current weekly spend per channel, a yellow “proposed” column for Solver to change, and minimum and maximum limits set by the plus or minus 30 percent cell on Inputs. To reallocate, open Solver, maximize $C$12 (total proposed response) by changing $C$7:$C$9, subject to $C$10 = $B$10 (same total budget) and each proposed cell between its min and max. On the defaults, Solver should land on roughly 3,153 for search (its ceiling), 1,355 for social and 854 for video (its floor), worth about 474 a week in extra revenue for the same total spend; I checked that answer with a scipy optimizer on the same model. The budget allocation page discusses how to pick sensible limits.
Google Sheets differences
The template opens in Google Sheets (upload it to Drive, then open with Sheets) and the model works unchanged, because LINEST, INDEX, SUMPRODUCT and SUMSQ behave the same. A few things differ. There’s no Analysis ToolPak, so LINEST is the only regression option. Solver isn’t built in, but there’s a free Solver add-on under Extensions, Add-ons that takes the same objective, changing cells and constraints. Array formulas are entered with Ctrl+Shift+Enter, which wraps them in ARRAYFORMULA, and SCAN with LAMBDA is available for one-cell adstock columns. Charts convert, though you may want to restyle the stacked area chart. Large models with many channels and several years of data recalculate noticeably slower in Sheets than in desktop Excel, which is one of the signs it’s time to move to R or Python.
When to move beyond the spreadsheet
Excel is a superb place to learn and a reasonable place to run a small model with a few channels. It starts to struggle when you want uncertainty intervals rather than single numbers, priors from experiments, regional data or dozens of channels. That’s when the open-source libraries earn their keep, and when the same model rebuilt in Python or R becomes the bridge. If you’d like to practise the judgment calls on a messier dataset, the Vexpower MMM course uses an invented brand with the kind of data problems real projects have, and the templates page collects every spreadsheet in this library.
