How

What are the formulas to put into excel to calculate the present value and required return on Disney stock?

Background

In 2009, the media conglomerate Disney purchased Marvel, the comic book company. This gave Disney the rights to create movies and profit from characters in the Marvel universe. Over the next decade, they released several films in The Avengers series, culminating 10 years later with the epic finale Avengers: Endgame. Over that time period, most of the main characters had been developed and the biggest blockbuster movies had been created. Afterwards, Disney would continue to profit in perpetuity through spinoffs and remakes from the marvel cinematic universe, as well as through increased subscriptions to its Disney+ streaming service.

Your task is to analyze whether Disneys acquisition of Marvel was a positive net present value acquisition.

The spreadsheet contains (fictional) information about ticket sales for the marvel cinematic universe for the decade following the Marvel acquisition. Assume that for each dollar of ticket sales, Disney receives free cash flows of 12 cents (a 12% free cash flow margin). The other 88% of ticket sales go to the movie theaters, as well as to the cost of producing the movies.

Beta Expected Market Return Risk-free rate Required Return on Disney stock 1.2 7.00% 2.00% Perpetuity Growth Rate 2% Year 1 2 3 4 2010 2011 2012 2013 1,500,000,000 $ 1,500,000,000 $ 2,500,000,000 $3,000,000,000 12% 12% 12% 12% $ Ticket Sales Free cash flow margin Free cash flow Present Value Total Present Value