This year has been a hot market for IPOs, and Alrbnb was a recent high profile one. Valuing a company is basically a big capital budgeting exercise. Instead of forecasting the cash flows from a single project, vou forecast the cash flows for the whole company. Instead of calculating the NPV of a new product, you calculate the NPV of the cash flows of the whole company. One complicating factor is that while a product will come to an end, in principle a company will not (we hope). So, we have to make an assumption at some point about how the cash flows of the company grow in the long-run (more on this below). The typical approach to valuing a company is to start by specifically forecasting the cash flows for the next 5 to 10 years and then make a simplifying assumption beyond that. 1. Start with ABNB xlsx from Canvas. 2. The sheet lists some assumptions about the year-over-year (Yon growth of revenues and certain expenses for 2021-2030. These assumptions are derived from current analyst reports and management statements about Airbnb. Most analysts expect bumpy growth over the next few years, so that is why the YoY growth estimates do not follow a smooth pattern at first. Use these assumptions to bulid a forecast of net income (earnings) and free cash flows of Airbnb for the years 2021 to 2030 . Specifically, start with forecasting revenues for each year and then cost of revenues, etc. like we did in capital budgeting untli you eventually get to Free Cash Flows. Assume a tax rate of 21%. To simplify the analysis, ignore tax-loss carryforwards and assume that any year with negative taxable income has a tax of zero. 3. Beyond 2030 , you will need to make an assumption to handle the mature part of Airbnb's lifecycle. a. Taking the free cash flows you forecast for 2030 , assume that they will grow by 3% to 2031 and continue growing at 3% thereafter. b. Assume that the opportunity cost of capital for Airbnb is 8%. You can treat all cash flows starting in 2031 and continuine onward as a growing perpetulty with a growth rate of 3\%. By doing this, you will have what is called a "terminal value" or "continuation value" for Airbnb as of the end of 2030 (one year before the first cash flow in the growing perpetuity). 4. To avoid timing complexties, we will assume that it is now the beginning of 2021 and that the first cash flows (the 2021 FCF) will be generated exactly one-year from now, at the end of 2021. Discount all cash flows back to the beginning of 2021 using an 8% cost of capital and the "NPV" function in Excel. This is the estimated total enterprise value of Airbnb at the beginning of 2021 5. To arrive at the price per (equity) share, you must subtract Airbnb's debt from its enterprise value and add its cash reserves. a. It has $1.8 billion in debt and $5.3 billion in cash reserves. b. Airbnb has 597 million shares outstanding. Calculate the price per share. 6. Next, perform some sensitivity analysis a. Use the Upside and Downside scenarios to compute Upside and Downside stock prices. b. Using the base case, check the sensitivity of the valuation to the assumptions about Airbnb's margins. The sheet assumes that Airbnb will be able to stabilize gross margins at 32\%. Competition, higher product support expenses, or increased advertising could eat into this. Calculate Airbnb's stock price if Cost of Revenue evolves as in the "Sensitivity CoR" row in the spreadsheet. 7. As of the writing of this case, Airbnb's stock price was $200. a. Consider what it would take for your forecast to produce a valuation that equates to a $200 stock price. 1. By changing your revenue growth assumptions, find a set of YoY growth assumptions that would produce a stock price of approximately $200 ii. With these revenue assumptions in place, compute the compound annual growth rate (CAGR) in FCF from 2021 to 2030. For example, if your estimate of 2021 FCF is $1,000 and for 2030 is $10,000, then you would use the "RATE" function in Excel: = RATE (10,0,1000,10000). 8. An alternative way to value a stock is by use of multiples. a. Start by valuing Airbnb based on its 2021 projected Earnings Before Interest, Taxes, Depreciation and Amortization (EBITDA, a standard cash flow measure) and using the bookings industry average Enterprise Value/EBITDA ratio of 13.4. This will give you a total company value which you would replace your NPV of FCF in your share value calculation (you still need to subtract debt and add cash before dividing by shares outstanding to get the price per share). b. Do the same with the lodging industry average EV/EBITDA ratio of 14.3. c. Now apply the average EV/EBITDA ratio for major Internet-based companies: 30.2 . Overall, what is your estimate of what Airbnb's stock price should be and what range of valuations do you think are reasonable? Defend your conclusion by discussing and referencing the outcomes of the valuations in steps 5 to 8 . You will have arrived at a range of prices and you will need to take a stand on how to interpret this range and which prices and assumptions to weight more heavily. Your answer should take the form of a memo (maximum 2 single-spaced pages) that references spreadsheet exhibits (the exhibits do not count toward the 2-page limit). This is a group case. Submission is electronic (electronic MUST also include your Excel file ending in .xlsx). \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|} \hline Airtunb Valuation & & & & & & & & & & & \\ \hline Model assumptions based on anaydst reports & & 2021 & 2022 & 2023 & 2024 & 2025 & 2026 & 2027 & 2028 & 2029 & 2030 \\ \hline Upaide Yov Revenue Growth & & 46% & & 285 & 26% & 24K & 23x & 22% & 20% & & 20x \\ \hline Downside YoY Revenue Growth & & 30% & 30% & 20K & 16N & 145 & 13% & 12% & 11% & 10% & 10% \\ \hline Base Cane Yor Revenue Growth & & 38% & 32x & 24W & 21% & 195 & 18% & 17x & 16% & 15% & 15% \\ \hline Total Cost of Revenue (S of Rev) & & 990% & 90.0% & 85.0K & 83.0K & 8100 & 78.0% & 76.0K & 74.0K & 71.0K & 58.0x \\ \hline Depreciation (W of Rev) & & 240N & 1.80N & 1sin & 1.00% & 0.40K & 0.40x & 0.40N & Q.40N & 0.00N & 0.40K \\ \hline Caplx (X of hev) & & 1.70k & 1.30k & 0.504 & 0.50x & 0.500 & 0.50N & 0.50N & oson & 0.50x & 0.40% \\ \hline Sens con & & 99.0K & 90.0% & 90.086 & 90.0N & as.0W & 35,0W & 55.0N & 500N & a0.0N. & ao on \\ \hline Total Hevenue in 2020 & 3243 & & & & & & & & & & \\ \hline Cost of Copital (Discount litate) & nis: & & & & & & & & & & \\ \hline Long ruti erowth rete. & w & & & & & & & & & & \\ \hline At dollat figures are in millions. & & & & & & & & & & & \\ \hline & 2020 & 2021 & 2002 & 2023 & 2024 & 2025 & 2026 & 2027 & 2028 & 2929 & 2030 \\ \hline \end{tabular}