Basics of Company Forecasting and Valuation

In this article, I go through a discounted cash flow (DCF) model for Moderna completed several months ago. This is not intended as investment advice, please consult an investment professional before making any decisions. The full model can be downloaded below.

Moderna Analysis.xlsx

Download XLSX • 41KB

Moderna is a large-cap biotech company. A few things readers should be aware of: the information is likely stale, the model is several months old; the projections are 'quick and dirty' in that they rely on management forecasts for the period- management forecasts are prone to both over- and under-estimation (depending on how they are trying to misrepresent themselves, or if they're just inaccurate); and there's plenty of other things you should do aside from forecasting, you'll see these in some of our larger models but they include qualitative analyses, industry, company product line assessments, etc, for Moderna I have the peer group analysis removed but some of the data included, and I looked at Moderna's product lines at the time. Also- I don't explain the three financial statements here, but you probably don't need it anyways.

There are also other models valuators should be aware such as the residual income model and comparable companies analysis. They all have different use cases with pros and cons that modelers should be aware of.

Anyways- top down overview of DCFs. Basically, we'll project revenues, maybe some other things based on assumptions, then work down to EPS, get our statement of cash flows linked, then get an estimate of free cash flow to the firm (FCFF; a more general measure than FCFE (to equity)), we'll discount that back to today, accounting for the cost of equity. Then sum and divide by shares outstanding to get the price. We should account for debt payments if we're doing an equity valuation. Moderna appears to have no debt outstanding however on the balance sheet. We're also going to retroactively apply scenarios, and, if everything is linked properly, that should be easy.

However, I'm going to discuss the scenarios first. We have three cases forecasted, worst, base, and best. There's a drop box that is attached to the number underneath (I moved it so you can see). The numbers change and are linked to the assumptions (messy model- the assumptions are linked on the sheets... which is a terrible practice and everyone should check out our booklet for clean models with separated assumptions). The projected prices are manually inputted here but you would need to find a workaround if you intend to revise and reuse the model. Scenarios are super important and allow us to account for uncertainty in the assumptions.

You can see, I didn't forecast the balance sheet here as it was largely unnecessary and forecasted balance sheets don't always seem to balance. I did want the ratios below however, which tell us about the business' stability with respect to debt and expenses. We can see the company is reasonably stable up until about 2023 and we would probably want to see the cash runaway improve in 2024. Biotech companies are pretty volatile and most projects fail, so it can be important to have a large cash buffer.

The income statement was forecasted 7 years out. We start with revenue and move down, linking assumptions to follow from the increases in revenue (because that is our only assumption). Go with diluted EPS (which accounts for any additional stock issuance that could occur). In terms of margins, the company is not doing well in the actual historical period- this is common with biotech companies, but not a good sign for our intrinsic valuation. Management projected 22% growth for the next three years and I assumed it would declining linearly from there. Growth of that magnitude isn't uncommon in biotech companies but, as you can see, it is coupled with massive drops from research failures. However, a better way to do this and the industry standard in biotech is to use bottom-up projections per projection with probabilities of success assigned to each line of research- which is what I feature in my analysis of Prothena for a shorter horizon.

Here's our statement of cash flows. We want to work down and calculate the FCFF, which is basically the cash available for debt and equity investor distributions. For an equity valuation, we would typically use FCFE, but here, because Moderna doesn't carry debt (interest expense = 0; borrowings = 0), they will be the exact same. We project FCFF for our horizon, and discount using our cost of equity (estimated with the capital asset pricing model (CAPM) here). Those numbers (present value of FCFF) are summed and divided by the shares outstanding to get the price per share. The idea is that the intrinsic or fundamental value of the stock is based on the the amount of cash available to the shareholders today per share. The other idea is that investors will move the price towards the fundamental valuation of the company.

Anyways, DCFs are most appropriate if the FCF has a correlation with share price, the FCF is generally positive and preferably, reasonably low volatility (to make it more predictable). The other models have caveats you should know before using and you should generally use more than one model. So, aside from this DCF, a relative valuation could also be used.

If you found this helpful or enjoyable, please consider subscribing to our newsletter for more.  

You can reach out to us here for consulting services or here to discuss booking a workshop to learn useful skills and help you get more familiar with us.