What is Coca-Cola actually worth?

Financial Modelling in Practice at BI is a course about one thing: building financial models in Excel that actually work. In autumn 2025, three of us worked through the group project together. It had six parts, each teaching a new tool, and the last two put all of them to use on one real company: Coca-Cola.

Every part was handed in twice, as an Excel workbook with all the calculations and as a PDF with our answers. The real work lives in the spreadsheets, so that’s mostly what you’ll see here.

Part 1: A whole life in one spreadsheet

The first task was a savings plan for an imaginary 23-year-old client. He wants to retire at 70, take out $110,000 a year from then on (rising with inflation), and still have $600,000 left when he turns 97. Along the way life happens: a boat at 28, a wedding at 30, a deposit on a house at 33, college for two children, a recession every ninth year that wipes out 9% of his savings, and a boom every seventh year that adds 5%.

The question was simple: how much does he need to put in at 23, if every later deposit grows with his salary? We built every year of his life as a row, and let Excel’s Goal Seek find the answer: $35,605.72.

The first years of the savings plan. The yellow cell is the answer Goal Seek found. The plan runs all the way to age 97 further down.

Then we tested it. Which inputs change the answer the most? Not the boat, the wedding or even a third child (that only lifts the deposit to $37,988). By far the biggest lever was the return on his savings. That idea came back later, and it’s also the heart of my article You are a cash flow.

Parts 2 and 3: Building portfolios

Next came portfolios. If you can spread your money across several stocks, which mix gives the most return for the risk you take? The answer is a curve called the efficient frontier. Every point on it is the best you can do for a given level of risk, and every single stock sits somewhere below it.

From our Part 2 workbook. The green curve is the efficient frontier, and the blue squares are the individual stocks. Each one is worse on its own than a mix on the curve.

One lesson stuck with me: when you add a stock to a portfolio, how it moves together with the rest matters more than how wild it is on its own. A risky stock that zigs when the others zag can make the whole portfolio calmer.

In Part 3 we used the Black-Litterman method, a way to add your own opinions (“I think this stock will beat that one”) to a portfolio without the maths going to extremes. Compared with portfolios built only on past returns, it gives far more modest and stable results:

Our Part 3 frontiers. The red and green curves trust past returns. The flat purple and blue curves are the Black-Litterman versions, which stay close to what the market as a whole expects.

Part 4: Teaching Excel new tricks

Part 4 was all VBA, the programming language inside Excel. We wrote our own worksheet functions, like myNPV, which works out what a series of future cash flows is worth today and can switch between yearly and continuous compounding. Checking it against Excel’s own NPV function gave exactly the same answer, 116.14:

Our own myNPV function (yellow) against Excel’s built-in NPV (green).

Behind every function is code like this, which builds the table of how a group of stocks move together:

A piece of our VBA. It took hours of debugging, but we ended up using these functions all the way through the Coca-Cola valuation.

Parts 5 and 6: What is Coca-Cola worth?

The last two parts brought it all together. The task: value Coca-Cola from its own accounts, and tell a client whether the share price makes sense.

Step 1: What return do investors expect?

To value a company, you discount its future cash flows by the return investors demand for the risk. For a company with both shareholders and lenders, that’s the weighted average cost of capital, or WACC:

$$\text{WACC} = \frac{E}{E+D}\thinspace r_E + \frac{D}{E+D}\thinspace r_D\thinspace(1 - T)$$

$E$ and $D$ are the values of the shares and the debt, $r_E$ and $r_D$ what shareholders and lenders expect, and $T$ the tax rate.

The hard part is $r_E$, because nobody tells you what shareholders expect. So we estimated it five different ways, and combined them with two different costs of debt. That gave ten estimates for the same company, ranging from 5.06% to 9.28%:

Our WACC sheet. The yellow cells are the results, and the text beside each one shows the formula behind it, so anyone can check the work.

The highest estimates came from CAPM, the classic textbook model. Coca-Cola’s beta is only 0.55, meaning its share moves about half as much as the market. But the expected market return in our data was a very high 14.21%, and that pushed CAPM’s answer up. The lowest came from a method based on share buybacks, which reflects management’s choices more than the company’s risk. We left out both extremes and landed on a WACC of 6.33%.

Step 2: Forecasting the cash

Next, we forecast Coca-Cola’s free cash flow, the cash left over after running and investing in the business, and valued it in two different ways.

The first way starts from the cash flow statement (CSCF). Coca-Cola’s free cash flow fell sharply in 2024, to $4.35 billion, which made the recent trend look like a 15% drop every year. That felt too gloomy for a company like Coca-Cola, so we assumed 4% growth for the next five years and 2% after that. The value of everything after 2029 is captured in one number, the terminal value:

$$\text{Terminal value} = \frac{FCF_{2029} \times (1 + g)}{\text{WACC} - g}$$

The cash flow valuation. From ten years of Coca-Cola’s own numbers down to a value of $19.59 per share at the bottom.

The second way, the pro forma model, builds complete forecast accounts for Coca-Cola, with an income statement and balance sheet for every year to 2029. Each line follows a ratio we chose from the history, like costs as a share of sales:

Our assumptions for the pro forma model, and how we chose each one.

The pro forma valuation, ending in a value of $35.95 per share.

Step 3: The model against the market

At the start of October 2024, a Coca-Cola share cost $71.86. That’s twice our higher estimate and more than three times the lower one.

So what would you have to believe to pay $71.86? A sensitivity table answers that by running the valuation again for many combinations of long-term growth and WACC:

Share price in dollars from the cash flow model, for different long-term growth rates (across) and WACCs (down).

To get close to the market price, both models need Coca-Cola’s cash flow to grow by about 7% a year forever, with a WACC of 8 to 9%. For a mature company that already sells drinks in almost every country on earth, that’s a big ask.

Our advice to the client was to hold or sell. On the numbers, the share looks expensive. But Coca-Cola pays a steady, growing dividend, is far calmer than the market and has one of the strongest brands in the world. Investors seem willing to pay a lot for that safety, more than a cash flow model can capture. Of the two models, we trusted the pro forma one most, because the cash flow version is thrown off by a single weak year like 2024.

Step 4: Two years later, the market still disagrees

On September 29, 2026, a Coca-Cola share closed at $86.84 on the New York Stock Exchange. That’s about 21% higher than when we did the valuation, almost two and a half times our higher estimate and more than four times the lower one. The market didn’t come round to our numbers. It moved further away from them. A client who followed our advice and sold would have missed that rise, and the dividends on top.

So is the market wrong? Our models were built on Coca-Cola’s own public accounts, the same information every investor has. If the market were efficient in the textbook sense, the price would already reflect all of it, and there would be no gap to find. There are two ways to read the gap:

• The market is inefficient. Prices are set by people, and people don’t always act rationally. Many buy what they know, and few brands are better known than Coca-Cola. When the economy looks shaky, money flows into “safe” shares almost whatever the price, and a rising price pulls in more buyers. Betting against it is hard too. A share like this can stay expensive for years, and anyone who sells it short has to pay the dividend while waiting to be proven right.
• Our model is missing something. Investors may demand a lower return than our 6.33% from a company this stable, or expect more growth from price rises and new drinks than the history suggests. As the sensitivity table shows, a small change in either moves the value a lot.

Economists call this the joint hypothesis problem. You can only call a price “wrong” compared with a model of what it should be, so every gap tests the market and the model at the same time. The gap alone can’t tell you which one is off.

As I see it, it’s both. The price reflects information, but also how people feel about that information. Investors don’t only pay for cash flows. They also pay for the calm of owning something familiar that has survived every crisis so far. That isn’t always irrational, but a cash flow model can’t see it.

Sources: closing price from Yahoo Finance, checked against Morningstar. The joint hypothesis problem is from Eugene Fama, Efficient Capital Markets: II, Journal of Finance, 1991.

What I take from it

• A valuation is a range, not a number. Ten reasonable ways to estimate Coca-Cola’s cost of capital gave answers from 5% to 9%, and the share value moves a lot with each one.
• Build the spreadsheet so others can check it. Inputs in yellow, the formula written next to every result, one idea per sheet. It made our own debugging much easier too.
• When the model and the market disagree by half, question both. Either the market is too optimistic, or the model misses something, like what a brand and a safe dividend are worth.

About the project

Thanks to Nicholas Chong (left) and Jibbe van der Schenk (right). Together we managed to get an A in the course.

This is based on our group project in Financial Modelling in Practice (FIN3616) at BI Norwegian Business School, autumn 2025. We were a group of three, Nicholas, Jibbe and me, and every calculation was done in Excel, with VBA for our own functions. The spreadsheet images here are drawn from the workbooks we handed in.