Question

1 Approved Answer

Posted on Oct 04, 2024

Goal: Build a financial model that values the investment opportunity described below, solving for (1) cumulative 5-year revenue, (2) 5-Year NPV, and (3) 5-Year IRR.

Goal: Build a financial model that values the investment opportunity described below, solving for (1) cumulative 5-year revenue, (2) 5-Year NPV, and (3) 5-Year IRR. Investment Opportunity - Background: Imagine Comcast has been approached by a property development company, Arctic Land \$ \\mathrm{CO}_{0} \$, in Seattle, WA to build a \"fiber neighborhood\" in a new residential housing community. Arctic Land Co. estimates that there will be 1,500 homes built in the new community and wants to partner with Comcast to sponsor the new community. At a formal presentation, Arctic Land Co. offers Comcast a \"premium packaging\" option with the proposed deal, featuring three products per home that activates Comcast services. The products will be Video (\"TV), Data ('Internef), and Xfinity Home Security. Following the presentation from Arctic Land Co., Comcast Division President, John Doe, approaches your team about valuing the investment and providing a recommendation for whether or not Comcast should agree to build the \"fiber neighborhood\" and sponsor the new community. Below are some estimates to help build the financial model to value the investment: - 1,500 homes to be built/completed in community ( 500 in Year 1,500 in Year 2,500 in Year 3) - Comcast will construct network connections to all homes, regardless if the homes activate Comcast services - Assume homes are completed by Jan \$ 1^{\\text {II }} \$ of each year and Comcast receives revenue from Comcast-serviced homes starting on Jan \$ 1^{\\text {st }} \$ of each year - \60 of cumulative homes built activate Comcast's \"premium\" 3-Product package (Video, Data, XF Home) - ARPU's (Average Revenue per Unit per Month) in Year 1 \\[ \\begin{array}{l} \\text { Video }=\\$ 44.00 \\\\ \\text { Data }=\\$ 32.00 \\\\ \\text { Xfinity Home }=\\$ 33.00 \\end{array} \\] - ARPU Growth in each year from Year 2 to Year 5 \beginarrayltextVideo=2.0 - Operating Expense Assumptions for Years 1.5 Tech/Eng \=10 of Total Annual Revenue - Customer Service \=2 of Total Annual Revenue - Sales \\& Marketing \=5 of Total Annual Revenue, plus \$ \\$ 50,000 \$ per year in Years \$ 1-5 \$ to sponsor community - Programming \=30 of Total Annual Revenue - Construction Costs will be incurred in the year prior to the homes being completed at \$ \\$ 750 \$ per home - Equipment Capex costs will be incurred in the same year customers activate Comcast service at \$ \\$ 102 \$ per Comcast home (equipment costs are only incurred in the year of new home activation, not afterwards) - Discount Rate \=8.0 Goal: Build a financial model that values the investment opportunity described below, solving for (1) cumulative 5-year revenue, (2) 5-Year NPV, and (3) 5-Year IRR. Investment Opportunity - Background: Imagine Comcast has been approached by a property development company, Arctic Land \$ \\mathrm{CO}_{0} \$, in Seattle, WA to build a \"fiber neighborhood\" in a new residential housing community. Arctic Land Co. estimates that there will be 1,500 homes built in the new community and wants to partner with Comcast to sponsor the new community. At a formal presentation, Arctic Land Co. offers Comcast a \"premium packaging\" option with the proposed deal, featuring three products per home that activates Comcast services. The products will be Video (\"TV), Data ('Internef), and Xfinity Home Security. Following the presentation from Arctic Land Co., Comcast Division President, John Doe, approaches your team about valuing the investment and providing a recommendation for whether or not Comcast should agree to build the \"fiber neighborhood\" and sponsor the new community. Below are some estimates to help build the financial model to value the investment: - 1,500 homes to be built/completed in community ( 500 in Year 1,500 in Year 2,500 in Year 3) - Comcast will construct network connections to all homes, regardless if the homes activate Comcast services - Assume homes are completed by Jan \$ 1^{\\text {II }} \$ of each year and Comcast receives revenue from Comcast-serviced homes starting on Jan \$ 1^{\\text {st }} \$ of each year - \60 of cumulative homes built activate Comcast's \"premium\" 3-Product package (Video, Data, XF Home) - ARPU's (Average Revenue per Unit per Month) in Year 1 \\[ \\begin{array}{l} \\text { Video }=\\$ 44.00 \\\\ \\text { Data }=\\$ 32.00 \\\\ \\text { Xfinity Home }=\\$ 33.00 \\end{array} \\] - ARPU Growth in each year from Year 2 to Year 5 \beginarrayltextVideo=2.0 - Operating Expense Assumptions for Years 1.5 Tech/Eng \=10 of Total Annual Revenue - Customer Service \=2 of Total Annual Revenue - Sales \\& Marketing \=5 of Total Annual Revenue, plus \$ \\$ 50,000 \$ per year in Years \$ 1-5 \$ to sponsor community - Programming \=30 of Total Annual Revenue - Construction Costs will be incurred in the year prior to the homes being completed at \$ \\$ 750 \$ per home - Equipment Capex costs will be incurred in the same year customers activate Comcast service at \$ \\$ 102 \$ per Comcast home (equipment costs are only incurred in the year of new home activation, not afterwards) - Discount Rate \=8.0